Container Database (CDB)
The following sections describe how resource manager can be used to control the resource usage between pluggable databases (PDBs). Resource manager does not currently have the ability to control memory usage between PDBs.
Create CDB Resource Plan
A CDB resource plan is made up of CDB resource plan directives. The plan directives allocate shares, which define the proportion of the CDB resources available to the PDB, and specific utilization percentages, that give a finer level of control. CDB resource plans are managed using the DBMS_RESOURCE_MANAGER
package. Each plan directive is made up of the following elements:
- pluggable_database : The PDB the directive relates to.
- shares : The proportion of the CDB resources available to the PDB.
- utilization_limit : The percentage of the CDBs available CPU that is available to the PDB.
- parallel_server_limit : The percentage of the CDBs available parallel servers (PARALLEL_SERVERS_TARGET initialization parameter) that are available to the PDB.
PDBs without a specific plan directive use the default PDB directive.
The following code creates a new CBD resource plan using the CREATE_CDB_PLAN
procedure, then adds two plan directives using the CREATE_CDB_PLAN_DIRECTIVE
procedure.
DECLARE l_plan VARCHAR2(30) := 'test_cdb_plan'; BEGIN DBMS_RESOURCE_MANAGER.clear_pending_area; DBMS_RESOURCE_MANAGER.create_pending_area; DBMS_RESOURCE_MANAGER.create_cdb_plan( plan => l_plan, comment => 'A test CDB resource plan'); DBMS_RESOURCE_MANAGER.create_cdb_plan_directive( plan => l_plan, pluggable_database => 'pdb1', shares => 3, utilization_limit => 100, parallel_server_limit => 100); DBMS_RESOURCE_MANAGER.create_cdb_plan_directive( plan => l_plan, pluggable_database => 'pdb2', shares => 3, utilization_limit => 100, parallel_server_limit => 100); DBMS_RESOURCE_MANAGER.validate_pending_area; DBMS_RESOURCE_MANAGER.submit_pending_area; END; /
Information about the available CDB resource plans can be queried using the DBA_CDB_RSRC_PLANS
.
COLUMN plan FORMAT A30 COLUMN comments FORMAT A30 COLUMN status FORMAT A10 SET LINESIZE 100 SELECT plan_id, plan, comments, status, mandatory FROM dba_cdb_rsrc_plans WHERE plan = 'TEST_CDB_PLAN'; PLAN_ID PLAN COMMENTS STATUS MAN ---------- ------------------------------ ------------------------------ ---------- --- 92235 TEST_CDB_PLAN A test CDB resource plan NO SQL>
Information about the CDB resource plan directives can be queried using the DBA_CDB_RSRC_PLAN_DIRECTIVES
view.
COLUMN plan FORMAT A30 COLUMN pluggable_database FORMAT A25 SET LINESIZE 100 SELECT plan, pluggable_database, shares, utilization_limit AS util, parallel_server_limit AS parallel FROM dba_cdb_rsrc_plan_directives WHERE plan = 'TEST_CDB_PLAN' ORDER BY pluggable_database; PLAN PLUGGABLE_DATABASE SHARES UTIL PARALLEL ------------------------------ ------------------------- ---------- ---------- ---------- TEST_CDB_PLAN ORA$AUTOTASK 90 100 TEST_CDB_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1 100 100 TEST_CDB_PLAN PDB1 3 100 100 TEST_CDB_PLAN PDB2 3 100 100 SQL>
For the rest of the article the cdb_resource_plans.sql and cdb_resource_plan_directives.sql scripts will be used to display this information.
Modify CDB Resource Plan
An existing resource plan is modified by creating, updating or deleting plan directives. The following code uses theCREATE_CDB_PLAN_DIRECTIVE
procedure to add a new plan directive to the CDB resource plan we created previously.
DECLARE l_plan VARCHAR2(30) := 'test_cdb_plan'; BEGIN DBMS_RESOURCE_MANAGER.clear_pending_area; DBMS_RESOURCE_MANAGER.create_pending_area; DBMS_RESOURCE_MANAGER.create_cdb_plan_directive( plan => l_plan, pluggable_database => 'pdb3', shares => 1, utilization_limit => 75, parallel_server_limit => 75); DBMS_RESOURCE_MANAGER.validate_pending_area; DBMS_RESOURCE_MANAGER.submit_pending_area; END; / SQL> @cdb_resource_plan_directives.sql TEST_CDB_PLAN PLAN PLUGGABLE_DATABASE SHARES UTIL PARALLEL ------------------------------ ------------------------- ---------- ---------- ---------- TEST_CDB_PLAN ORA$AUTOTASK 90 100 TEST_CDB_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1 100 100 TEST_CDB_PLAN PDB1 3 100 100 TEST_CDB_PLAN PDB2 3 100 100 TEST_CDB_PLAN PDB3 1 75 75 SQL>
The UPDATE_CDB_PLAN_DIRECTIVE
procedure modifies an existing plan directive.
DECLARE l_plan VARCHAR2(30) := 'test_cdb_plan'; BEGIN DBMS_RESOURCE_MANAGER.clear_pending_area; DBMS_RESOURCE_MANAGER.create_pending_area; DBMS_RESOURCE_MANAGER.update_cdb_plan_directive( plan => l_plan, pluggable_database => 'pdb3', new_shares => 1, new_utilization_limit => 100, new_parallel_server_limit => 100); DBMS_RESOURCE_MANAGER.validate_pending_area; DBMS_RESOURCE_MANAGER.submit_pending_area; END; / SQL> @cdb_resource_plan_directives.sql TEST_CDB_PLAN PLAN PLUGGABLE_DATABASE SHARES UTIL PARALLEL ------------------------------ ------------------------- ---------- ---------- ---------- TEST_CDB_PLAN ORA$AUTOTASK 90 100 TEST_CDB_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1 100 100 TEST_CDB_PLAN PDB1 3 100 100 TEST_CDB_PLAN PDB2 3 100 100 TEST_CDB_PLAN PDB3 1 100 100 SQL>
The DELETE_CDB_PLAN_DIRECTIVE
procedure deletes an existing plan directive from the CDB resource plan.
DECLARE l_plan VARCHAR2(30) := 'test_cdb_plan'; BEGIN DBMS_RESOURCE_MANAGER.clear_pending_area; DBMS_RESOURCE_MANAGER.create_pending_area; DBMS_RESOURCE_MANAGER.delete_cdb_plan_directive( plan => l_plan, pluggable_database => 'pdb3'); DBMS_RESOURCE_MANAGER.validate_pending_area; DBMS_RESOURCE_MANAGER.submit_pending_area; END; / SQL> @cdb_resource_plan_directives.sql TEST_CDB_PLAN PLAN PLUGGABLE_DATABASE SHARES UTIL PARALLEL ------------------------------ ------------------------- ---------- ---------- ---------- TEST_CDB_PLAN ORA$AUTOTASK 90 100 TEST_CDB_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1 100 100 TEST_CDB_PLAN PDB1 3 100 100 TEST_CDB_PLAN PDB2 3 100 100 SQL>
Modify CDB Default Directive
In addition to creating PDB-specific plan directives, the default directive can be amended for a CBD resource plan. The following example uses the UPDATE_CDB_DEFAULT_DIRECTIVE
procedure to edit the default directive for the CDB resource plan.
DECLARE l_plan VARCHAR2(30) := 'test_cdb_plan'; BEGIN DBMS_RESOURCE_MANAGER.clear_pending_area; DBMS_RESOURCE_MANAGER.create_pending_area; DBMS_RESOURCE_MANAGER.update_cdb_default_directive( plan => l_plan, new_shares => 1, new_utilization_limit => 80, new_parallel_server_limit => 80); DBMS_RESOURCE_MANAGER.validate_pending_area; DBMS_RESOURCE_MANAGER.submit_pending_area; END; / SQL> @cdb_resource_plan_directives.sql TEST_CDB_PLAN PLAN PLUGGABLE_DATABASE SHARES UTIL PARALLEL ------------------------------ ------------------------- ---------- ---------- ---------- TEST_CDB_PLAN ORA$AUTOTASK 90 100 TEST_CDB_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1 80 80 TEST_CDB_PLAN PDB1 3 100 100 TEST_CDB_PLAN PDB2 3 100 100 SQL>
Modify CDB Autotask Directive
There is a plan directive associated with the database autotask functionality. The configuration of this can be altered using the UPDATE_CDB_AUTOTASK_DIRECTIVE
procedure.
DECLARE l_plan VARCHAR2(30) := 'test_cdb_plan'; BEGIN DBMS_RESOURCE_MANAGER.clear_pending_area; DBMS_RESOURCE_MANAGER.create_pending_area; DBMS_RESOURCE_MANAGER.update_cdb_autotask_directive( plan => l_plan, new_shares => 1, new_utilization_limit => 75, new_parallel_server_limit => 75); DBMS_RESOURCE_MANAGER.validate_pending_area; DBMS_RESOURCE_MANAGER.submit_pending_area; END; / SQL> @cdb_resource_plan_directives.sql TEST_CDB_PLAN PLAN PLUGGABLE_DATABASE SHARES UTIL PARALLEL ------------------------------ ------------------------- ---------- ---------- ---------- TEST_CDB_PLAN ORA$AUTOTASK 1 75 75 TEST_CDB_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1 80 80 TEST_CDB_PLAN PDB1 3 100 100 TEST_CDB_PLAN PDB2 3 100 100 SQL>
Enable/Disable Resource Plan
Enabling and disabling resource plans in a CDB is the same as it was in pre-12c instances. Enable a plan by setting theRESOURCE_MANAGER_PLAN
paramter to the name of the CDB resource plan, while connected to the root container.
SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'TEST_CDB_PLAN'; System altered. SQL> SHOW PARAMETER RESOURCE_MANAGER_PLAN NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ resource_manager_plan string TEST_CDB_PLAN SQL> To disable the plan, set the RESOURCE_MANAGER_PLAN parameter to another plan, or blank it. SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ''; System altered. SQL> SHOW PARAMETER RESOURCE_MANAGER_PLAN NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ resource_manager_plan string SQL>
Delete CDB Resource Plan
The DELETE_CDB_PLAN
procedure deletes CDB resource plans.
DECLARE l_plan VARCHAR2(30) := 'test_cdb_plan'; BEGIN DBMS_RESOURCE_MANAGER.clear_pending_area; DBMS_RESOURCE_MANAGER.create_pending_area; DBMS_RESOURCE_MANAGER.delete_cdb_plan(plan => l_plan); DBMS_RESOURCE_MANAGER.validate_pending_area; DBMS_RESOURCE_MANAGER.submit_pending_area; END; / SQL> @cdb_resource_plans.sql PLAN_ID PLAN COMMENTS STATUS MAN ---------- ------------------------------ ------------------------------ ---------- --- 16774 DEFAULT_CDB_PLAN Default CDB plan YES 16775 DEFAULT_MAINTENANCE_PLAN Default CDB maintenance plan YES 16776 ORA$INTERNAL_CDB_PLAN Internal CDB plan YES 16777 ORA$QOS_CDB_PLAN QOS CDB plan YES SQL>
Pluggable Database (PDB)
The use of resource manager inside the PDB is essentially unchanged compared to the pre-12c instances. Just remember, you have to be connected to the specific PDB when you set the RESOURCE_MANAGER_PLAN
parameter. You can read about how resource manager works in a PDB or in a pre-12c instance here:
- Resource Manager in Oracle 8i
- Resource Manager Enhancements In Oracle 9i
- Resource Manager Enhancements in Oracle Database 10g
- Resource Manager Enhancements in Oracle 11g
The following example shows how to create a simple resource plan for use within a PDB.
-- Connect to privileged user on PDB. CONN / AS SYSDBA ALTER SESSION SET CONTAINER = pdb1; -- Create a resource plan. BEGIN DBMS_RESOURCE_MANAGER.clear_pending_area(); DBMS_RESOURCE_MANAGER.create_pending_area(); -- Create plan DBMS_RESOURCE_MANAGER.create_plan( plan => 'hybrid_plan', comment => 'Plan for a combination of high and low priority tasks.'); -- Create consumer groups DBMS_RESOURCE_MANAGER.create_consumer_group( consumer_group => 'web_cg', comment => 'Web based OTLP processing - high priority'); DBMS_RESOURCE_MANAGER.create_consumer_group( consumer_group => 'batch_cg', comment => 'Batch processing - low priority'); -- Assign consumer groups to plan and define priorities DBMS_RESOURCE_MANAGER.create_plan_directive ( plan => 'hybrid_plan', group_or_subplan => 'web_cg', comment => 'High Priority - level 1', mgmt_p1 => 70); DBMS_RESOURCE_MANAGER.create_plan_directive ( plan => 'hybrid_plan', group_or_subplan => 'batch_cg', comment => 'Low Priority - level 2', mgmt_p1 => 20); DBMS_RESOURCE_MANAGER.create_plan_directive( plan => 'hybrid_plan', group_or_subplan => 'OTHER_GROUPS', comment => 'all other users - level 3', mgmt_p1 => 10); DBMS_RESOURCE_MANAGER.validate_pending_area; DBMS_RESOURCE_MANAGER.submit_pending_area(); END; / -- Assign users to consumer groups BEGIN DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group( grantee_name => 'web_user', consumer_group => 'web_cg', grant_option => FALSE); DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group( grantee_name => 'batch_user', consumer_group => 'batch_cg', grant_option => FALSE); DBMS_RESOURCE_MANAGER.set_initial_consumer_group('web_user', 'web_cg'); DBMS_RESOURCE_MANAGER.set_initial_consumer_group('batch_user', 'batch_cg'); END; / ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = hybrid_plan;