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):
Managing OS Resources Among PDBs Using PDB Perfromance Profiles – 12.2 New Feature (Doc ID 2171135.1) |
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; /