- Posted by Gavin Soorma
- On July 6, 2016
- 0 Comments
- 12c, CDB, dbms_resource_manager, PDB, resource manager, share
In a CDB since we have multiple pluggable databases sharing a set of common resources, we can prevent multiple workloads to compete with each other for both system as well as CDB resources by using Resource Manager.
Let us look at an example of managing resources for Pluggable Databases (between PDB’s) at the multitenant Container database level as well as within a particular PDB.
The same can be achieved using 12c Cloud Control, but displayed here are the steps to be performed at the command line using the DBMS_RESOURCE_MANAGER package.
With Resource Manager at the Pluggable Database level, we can limit CPU usage of a particular PDB as well as the number of parallel execution servers which a particular PDB can use.
To allocate resources among PDB’s we use a concept of shares where we assign shares to particular PDB’s and a higher share to a PDB results in higher allocation of guaranteed resources to that PDB.
At a high level the steps involved include:
• Create a Pending Area
• Create a CDB resource plan
• Create directives for the PDB’s
• Optionally update the default directives which will specify resources which any newly created PDB’s will be allocated or which will be used when no directives have been explicitly defined for a particular PDB
• Optionally update the directives which apply by default to the Automatic Maintenance Tasks which are configured to run in the out of the box maintenance windows
• Validate the Pending Area
• Submit the Pending Area
• Enable the plan at the CDB level by setting the RESOURCE_MANAGER_PLAN parameter
Let us look at an example.
We have 5 Pluggable databases contained in the Container database and we wish to enable resource management at the PDB level.
We wish to guarantee CPU allocation in the ratio 4:3:1:1:1 so that the CPU is distributed among the PDB’s in this manner:
PDBPROD1 : 40%
PDBPROD4 : 10%
Further for PDB’s PDBPROD3, PDBPROD4 and PDBPROD5 we wish to ensure that CPU utilization for these 3 PDB’s never crosses the 70% limit.
Also for these 3 PDB’s we would like to limit the maximum number of parallel execution servers available to the PDB.
The value of 70% means that if the PARALLEL_SERVERS_TARGET initialization parameter is 200, then the PDB cannot use more than a maximum of 140 parallel execution servers. For PDBPROD1 and PDBPROD2 there is no limit, so they can use all 200 parallel execution servers if available.
We also want to limit the resources used by the Automatic Maintenance Tasks jobs when they do execute in a particular job window and also want to specify a default resource allocation limit for newly created PDB’s or those PDB’s where a resource limit directive has not been explicitly defined.