Oracle 12c New Features

Online Oracle Training

LESSON 24

How to use the resource manager plans and associated directives to limit CPU resources between PDBs

Sometime you need to allocate more CPU resources to one PDB and less to another. This lesson will demonstrate how to do that.

Let us assume we need to allocate more CPU resources to the pluggable database mypdb1 (OLTP) since it need to have a high performance and less CPU resources to the pluggable database mypdb2 (Data Warehousing).

We are going to give 3 shares to the pluggable database mypdb1 and 1 share to the pluggable database mypdb2.

What is the share?

Think of the share is a unit of power. The more you give the more resource will be allocated to the source. Remember only CPU will be limited to mypdb2, if mypdb1 need to perform a task. If mypdb1 is not executing any task, then mypdb2 will utilize CPU as much it can get. So the portion of CPU allocation is not 1 to 3.

# . ./.profile

# sqlplus /nolog

SQL> connect / as sysdba

Just to make sure we are in the root container.

SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;

Session altered.

Create the resource manager plans and associated directives for mypdb1 and mypdb1 and call them "my_plan."

SQL> EXEC DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();

SQL> EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

Resource plan and associate directive for the pluggable database mypdb1

SQL> EXEC DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN ("my_plan","Three Share");

SQL> EXEC DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE ("my_plan","mypdb1", shares=>3);

Resource plan and associate directive for the pluggable database mypdb2

SQL> EXEC DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN ("my_plan","One Share");

SQL> EXEC DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE ("my_plan","mypdb2", shares=>1);

Validate it.

SQL> EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();

Submit the plan.

SQL> EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

List the resource manager plan to make sure you have created them correctly using the CDB_RSRC_PLANS view.

SQL> SELECT plan

   2   FROM cdb_rsrc_plans

   3   WHERE con_id = 1 and plan IN ("my_plan");

SQL> SELECT plan, pluggable_database, shares

   2   FROM cdb_cdb_rsrc_plan_directives

   3   WHERE con_id = 1 and plan IN ("MY_PLAN") and pluggable_database IN ("MYPDB1", "MYPDB2");

Active the plan "my_plan."

SQL> ALTER SYSTEM SET resource_manager_plan = my_plan;

System altered.

Check the plan "my_plan" is active using the V$RSRC_PLAN view.

SQL> SELECT name FROM v$rsrc_plan WHERE con_id = 1;

To remove the plan

SQL> ALTER SYSTEM SET resource_manager_plan = "";

Check the default plan using the V$RSRC_PLAN view.

SQL> SELECT name FROM v$rsrc_plan WHERE con_id = 1;

Good Luck!