Advertisements

Using Resource Manager with CDB and PDBs

With Resource Manager we can:
– Distribute CPU time among the users and applications
– Limit the amount of PGA memory
– Limit degree of parallelism
– Set priorities of parallel statements
– Limit the number of parallel execution servers
– Create an active session pool
– Limit session idle time
– Prevent executing long time operations

For more details, you can refer (MOS):

Verify that PDB’s databases are available and opened in read/write mode.

SQL> col name format a10
SQL> SELECT NAME, OPEN_MODE FROM V$PDBS;

NAME OPEN_MODE
---------- ----------
PDB$SEED READ ONLY
PDB1 READ WRITE
PDB2 READ WRITE

Create the resource plan BALANACED_PLAN and configure the plan directives in it.
This plan gives one share to both PDB1 and PDB2. This means that they both have the same priority to receive the CPU time resource

SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;
Session altered.
SQL> EXEC DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN('BALANACED_PLAN', 'One share to PDB1 and PDB2');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE('BALANACED_PLAN', 'PDB1',shares => 1);
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE('BALANACED_PLAN', 'PDB2',shares => 1);
PL/SQL procedure successfully completed.

Create the resource plan BIASED_PLAN and configure the plan directives in it.
This plan gives four shares to PDB1 and one share to PDB2.

SQL> EXEC DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN('BIASED_PLAN', 'PDB1 is given high priority');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE('BIASED_PLAN', 'PDB1',shares => 3);
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE('BIASED_PLAN', 'PDB2',shares => 1);
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
PL/SQL procedure successfully completed.
SQL>

Make sure the plans and their associated directives were successfully created.

SQL> col plan format a20
SQL> SELECT Plan from CDB_CDB_Rsrc_Plans WHERE CON_ID = 1 AND PLAN IN ('BALANACED_PLAN','BIASED_PLAN')ORDER BY 1;

PLAN
--------------------
BALANACED_PLAN
BIASED_PLAN

SQL> col pluggable_database format a30
SQL> SELECT PLAN, PLUGGABLE_DATABASE, SHARES FROM CDB_CDB_RSRC_PLAN_DIRECTIVES WHERE CON_ID = 1 AND PLAN IN ('BALANACED_PLAN','BIASED_PLAN') ORDER BY 1, 2;

PLAN PLUGGABLE_DATABASE SHARES
-------------------- ------------------------------ ----------
BALANACED_PLAN ORA$AUTOTASK
BALANACED_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1
BALANACED_PLAN PDB1 1
BALANACED_PLAN PDB2 1
BIASED_PLAN ORA$AUTOTASK
BIASED_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1
BIASED_PLAN PDB1 3
BIASED_PLAN PDB2 1

8 rows selected.

Delete the resource plans.

begin
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.DELETE_CDB_PLAN('BALANACED_PLAN');
DBMS_RESOURCE_MANAGER.DELETE_CDB_PLAN('BIASED_PLAN');
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
end;
/
Advertisements