In multitenant database, Resource Manager can be used to manage resources in two levels.

  1. CDB level Resource Plan
    • Resource Manager can manage the workloads for multiple PDBs that are contending for system and CDB resources. You can specify how resources are allocated to PDBs, and you can limit the resource utilization of specific PDBs.
  2. PDB level Resource Plan
    • Here Resoruce Manager can manage the workloads within each PDB, similar to that of a non CDB.

ie, the resources are allocated in two step.

Resource Manager first allocates a portion of the system’s resources to each PDB in the container database.

Then, in a specific PDB, it allocates a portion of system resources obtained (from previous step) to each session connected to the PDB.

From 12.1 the CDB level resource plans are available.

Using CDB level resource plans, we can limit the resource usage of specific PDBs such as :

  • Limit the CPU usage of a particular PDB
  • Limit the number of parallel execution servers that a particular PDB can use
  • Limit the memory usage of a particular PDB
  • Limit the resource usage of different sessions connected to a single PDB
  • Limit the I/O generated by specific PDBs

and so on

For multitenant container databases (CDBs) with thousands of pluggable databases (PDBs), it is cumbersome to configure memory and other Resource Manager directives for the CDB level resource plan.

The Performance Profiles and Mandatory PDB Profiles features introduced in 12.2 helps to configure CDB resource plan directives for a group of PDBs, instead of individual PDBs.

You can classify the PDBs with similar resource requirements and create a performance profile for them.

The CDB resource plan directives are then created for these performance profiles (or groups of PDBs).

The performance profile for each PDB can be specified using DB_PERFORMANCE_PROFILE initialization parameter.

The attributes such as shares, utilization_limit and parallel_server_limit can be set for the PDB performance profiles using the CREATE_CDB_PROFILE_DIRECTIVE procedure.

Oracle Exadata has additional options such as memory_limit and memory_min for controlling the Database Smart Flash Cache in the PDB performance profiles.

Steps to create a CDB Resource Plan with PDB Performance Profile:

Here, I assume the scenario to create a CDB resource plan for a CDB named newcdb.

The plan includes a directive for each PDB performance profiles named gold, silver, bronze.

These PDB performance profiles are then assigned to the PDBs in the CDB or the PDBs to be plugged-in in the future.

1. Create the pending area using the CREATE_PENDING_AREA procedure.

In the CDB:

exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

2. Create the CDB resource plan using the CREATE_CDB_PLAN procedure.

BEGIN

DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN(plan => ‘newcdb_plan’,

comment => ‘CDB resource plan for newcdb’);

END;

/

3. Create directives for the PDB performance profiles using the CREATE_CDB_PROFILE_DIRECTIVE procedure.

<plan directive for the PDB profile, gold>

BEGIN

DBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE(

plan => ‘newcdb_plan’, profile => ‘gold’, shares => 3, utilization_limit => 100, parallel_server_limit => 100);

END;

/

<plan directive for the PDB profile , silver>

BEGIN

DBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE(

plan => ‘newcdb_plan’, profile => ‘silver’, shares => 2, utilization_limit => 40, parallel_server_limit => 40);

END;

/

<plan directive for the PDB profile , bronze>

BEGIN

DBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE(

plan => ‘newcdb_plan’, profile => ‘bronze’, shares => 1, utilization_limit => 20, parallel_server_limit => 20);

END;

/

4. (Optional) Update the default PDB directive using the UPDATE_CDB_DEFAULT_DIRECTIVE procedure.

If required, the CDB resource plan directive can be updated using the UPDATE_CDB_DEFAULT_DIRECTIVE procedure.

BEGIN

DBMS_RESOURCE_MANAGER.UPDATE_CDB_PROFILE_DIRECTIVE(

plan => ‘newcdb_plan’, profile => ‘bronze’, new_shares => 1, new_utilization_limit => 10, new_parallel_server_limit => 20);

END;

/

5. (Optional) Update the default autotask directive using the UPDATE_CDB_AUTOTASK_DIRECTIVE procedure.

6. Validate the pending area using the VALIDATE_PENDING_AREA procedure.

exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();

7. Submit the pending area using the SUBMIT_PENDING_AREA procedure.

exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

8. Enable the CDB Resource Plan

(In the CDB)

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ‘newcdb_plan’ scope=both;

9. Specify Performance Profiles in the PDBs

alter session set container=PDB1;

alter system set db_performance_profile=’gold’ scope=spfile;

alter session set container=PDB2;

alter system set db_performance_profile=’silver’ scope=spfile;

alter session set container=PDB3;

alter system set db_performance_profile=’bronze’ scope=spfile;

Restart the PDBs

conn /as sysdba

alter pluggable database all close immediate;

alter pluggable database all open;

You can set the plan directives one by one and restart the PDBs individually also.

alter session set container=PDB4;

alter system set db_performance_profile=gold scope=spfile;

alter pluggable database close immediate;

alter pluggable database open;

You can even specify directives for both individual PDBs and for PDB performance profiles in the same CDB.
The directives for a PDB can be created using CREATE_CDB_PLAN_DIRECTIVE .

No two directives for the currently active plan can reference the same PDB or the same PDB performance profile.

The same value should be set for db_performance_profile parameter on all RAC instances.

To check the PDB Performance Profiles:

alter session set container=CDB$ROOT;

select inst_id, name, con_id, value, ispdb_modifiable from gv$system_parameter2

where name = ‘db_performance_profile’ order by 1,2,3,4;

To check the details of the directives set for various PDBs:

alter session set container=CDB$ROOT;

select p.name, shares, utilization_limit, profile from v$rsrc_plan r, v$pdbs p

where r.con_id = p.con_id;