Managing OS Resources Among PDBs Using PDB Perfromance Profiles
APPLIES TO:
Oracle Database – Enterprise Edition – Version 12.2.0.1 and later
Information in this document applies to any platform.
PURPOSE
This document explains the Performance Profiles and Mandatory PDB Profiles introduced in 12cR2.
SCOPE
This document is intended for DBAs and knowledge of multitenant and Oracle Database Resource Manager is a prerequisite
DETAILS
In multitenant database, Resource Manager can be used to manage resources in two levels.
- 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.
- 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.
You can classify the PDBs with similar resource requirements and create a performance profile for them. The CDB resource plan directives are the ncreated 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.
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.
7. Submit the pending area using the SUBMIT_PENDING_AREA procedure.
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;
CREATE_CDB_PLAN_DIRECTIVE .No two directives for the currently active plan can reference the same PDB or the same PDB performance profile.
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;
WikiDBA 