Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Multitenant : Resource Manager with Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 1 (12.1)

In a multitennent environment Resource Manager performs two separate tasks. At the CDB level it controls the resources allocated to each PDB, allowing you to prioritise some PDBs over others. At the PDB level it controls the resources allocated to each session connected to the PDB, allowing you to prioritise some sessions over others, just as it does in pre-12c instances.

Related articles.

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:

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 the CREATE_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 the RESOURCE_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:

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',
    cpu_p1           => 100,
    cpu_p2           => 0,
    cpu_p3           => 0);

  DBMS_RESOURCE_MANAGER.create_plan_directive (
    plan             => 'hybrid_plan',
    group_or_subplan => 'batch_cg',
    comment          => 'Low Priority - level 2',
    cpu_p1           => 0,
    cpu_p2           => 100,
    cpu_p3           => 0);

  DBMS_RESOURCE_MANAGER.create_plan_directive(
    plan             => 'hybrid_plan',
    group_or_subplan => 'OTHER_GROUPS',
    comment          => 'all other users - level 3',
    cpu_p1           => 0,
    cpu_p2           => 0,
    cpu_p3           => 100);

  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;

For more information see:

Hope this helps. Regards Tim...

Back to the Top.