8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Multitenant : Resource Manager PDB Performance Profiles in Oracle Database 12c Release 2 (12.2)
In the previous release it was possible to create a resource manager CDB resource plan to control the division of CPU and parallel execution server resources between PDBs. This required a separate plan directive for each PDB, which doesn't scale well to thousands of PDBs. In Oracle Database 12c Release 2 (12.2) it is now possible to create a resource plan based on performance profiles which defines the resource management for groups of PDBs. This can drastically reduce the amount plan directives required to handle thousands of PDBs.
Much of the resource manager CDB/PDB functionality is unchanged between 12.1 and 12.2, so some of the sections below link to the 12.1 article to save repetition.
- Create CDB Resource Plan with PDB Performance Profiles
- Modify CDB Resource Plan with PDB Performance Profiles
- Enable/Disable Resource Plan with PDB Performance Profiles
- Modify CDB Default Directive (12.1)
- Modify CDB Autotask Directive (12.1)
- Delete CDB Resource Plan (12.1)
- Manage Resources for Sessions in a Pluggable Database (PDB) (12.1)
- Monitoring CPU and Parallel Execution Server Usage for PDBs
Related Articles
- Multitenant : Resource Manager with Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 1 (12.1)
- Resource Manager Quick Links : 8i, 9i, 10g, 11gR1, 11gR2, 12cR1, 12cR2, All Articles
Create CDB Resource Plan with PDB Performance Profiles
The process for creating a CDB resource plan using PDB performance profiles is very similar to using CDB plan directives. Instead of targeting individual PDBs, the profiles define types of PDBs that have the same resource usage profiles. The profile directives allocate shares, which define the proportion of the CDB resources available to the PDB, and specific utilization percentages, that give a finer level of control. PDB performance profiles are managed using the DBMS_RESOURCE_MANAGER package. Each profile directive is made up of the following elements:
- profile : The profile the directive relates to.
- shares : The proportion of the CDB resources available to the PDB.
- utilization_limit : The percentage of the CDBs available CPU that is available to the PDB.
- parallel_server_limit : The percentage of the CDBs available parallel servers (
PARALLEL_SERVERS_TARGETinitialization parameter) that are available to the PDB.
PDBs without a specific plan directive use the default PDB directive.
The following code creates a new CDB resource plan using the CREATE_CDB_PLAN procedure, then adds two profile directives using the CREATE_CDB_PROFILE_DIRECTIVE procedure to represent the typical gold, silver levels of service.
DECLARE
l_plan VARCHAR2(30) := 'test_cdb_prof_plan';
BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;
DBMS_RESOURCE_MANAGER.create_cdb_plan(
plan => l_plan,
comment => 'A test CDB resource plan using profiles');
DBMS_RESOURCE_MANAGER.create_cdb_profile_directive(
plan => l_plan,
profile => 'gold',
shares => 3,
utilization_limit => 100,
parallel_server_limit => 100);
DBMS_RESOURCE_MANAGER.create_cdb_profile_directive(
plan => l_plan,
profile => 'silver',
shares => 2,
utilization_limit => 50,
parallel_server_limit => 50);
DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/
Information about the available CDB resource plans can be queried using the DBA_CDB_RSRC_PLANS view.
COLUMN plan FORMAT A30
COLUMN comments FORMAT A30
COLUMN status FORMAT A10
SET LINESIZE 100
SELECT plan_id,
plan,
comments,
status,
mandatory
FROM dba_cdb_rsrc_plans
WHERE plan = 'TEST_CDB_PROF_PLAN';
PLAN_ID PLAN COMMENTS STATUS MAN
---------- ------------------------------ ------------------------------ ---------- ---
83326 TEST_CDB_PROF_PLAN A test CDB resource plan using NO
profiles
SQL>
Information about the CDB resource plan directives can be queried using the DBA_CDB_RSRC_PLAN_DIRECTIVES view. Notice we use the PROFILE column as well as the PLUGGABLE_DATABASE column.
COLUMN plan FORMAT A30
COLUMN pluggable_database FORMAT A25
COLUMN profile FORMAT A25
SET LINESIZE 150 VERIFY OFF
SELECT plan,
pluggable_database,
profile,
shares,
utilization_limit AS util,
parallel_server_limit AS parallel
FROM dba_cdb_rsrc_plan_directives
WHERE plan = 'TEST_CDB_PROF_PLAN'
ORDER BY plan, pluggable_database, profile;
PLAN PLUGGABLE_DATABASE PROFILE SHARES UTIL PARALLEL
------------------------------ ------------------------- ------------------------- ---------- ---------- ----------
TEST_CDB_PROF_PLAN ORA$AUTOTASK 90 100
TEST_CDB_PROF_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1 100 100
TEST_CDB_PROF_PLAN GOLD 3 100 100
TEST_CDB_PROF_PLAN SILVER 2 50 50
SQL>
For the rest of the article the cdb_resource_plans.sql and cdb_resource_profile_directives.sql scripts will be used to display this information.
Modify CDB Resource Plan with PDB Performance Profiles
An existing resource plan is modified by creating, updating or deleting profile directives. The following code uses the CREATE_CDB_PROFILE_DIRECTIVE procedure to add a new profile directive to the CDB resource plan we created previously.
DECLARE
l_plan VARCHAR2(30) := 'test_cdb_prof_plan';
BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;
DBMS_RESOURCE_MANAGER.create_cdb_profile_directive(
plan => l_plan,
profile => 'bronze',
shares => 1,
utilization_limit => 25,
parallel_server_limit => 25);
DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/
SQL> @cdb_resource_profile_directives.sql test_cdb_prof_plan
PLAN PLUGGABLE_DATABASE PROFILE SHARES UTIL PARALLEL
------------------------------ ------------------------- ------------------------- ---------- ---------- ----------
TEST_CDB_PROF_PLAN ORA$AUTOTASK 90 100
TEST_CDB_PROF_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1 100 100
TEST_CDB_PROF_PLAN BRONZE 1 25 25
TEST_CDB_PROF_PLAN GOLD 3 100 100
TEST_CDB_PROF_PLAN SILVER 2 50 50
SQL>
The UPDATE_CDB_PROFILE_DIRECTIVE procedure modifies an existing profile directive.
DECLARE
l_plan VARCHAR2(30) := 'test_cdb_prof_plan';
BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;
DBMS_RESOURCE_MANAGER.update_cdb_profile_directive(
plan => l_plan,
profile => 'bronze',
new_shares => 1,
new_utilization_limit => 20,
new_parallel_server_limit => 20);
DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/
SQL> @cdb_resource_profile_directives.sql test_cdb_prof_plan
PLAN PLUGGABLE_DATABASE PROFILE SHARES UTIL PARALLEL
------------------------------ ------------------------- ------------------------- ---------- ---------- ----------
TEST_CDB_PROF_PLAN ORA$AUTOTASK 90 100
TEST_CDB_PROF_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1 100 100
TEST_CDB_PROF_PLAN BRONZE 1 20 20
TEST_CDB_PROF_PLAN GOLD 3 100 100
TEST_CDB_PROF_PLAN SILVER 2 50 50
SQL>
The DELETE_CDB_PROFILE_DIRECTIVE procedure deletes an existing profile directive from the CDB resource plan.
DECLARE
l_plan VARCHAR2(30) := 'test_cdb_prof_plan';
BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;
DBMS_RESOURCE_MANAGER.delete_cdb_profile_directive(
plan => l_plan,
profile => 'bronze');
DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/
SQL> @cdb_resource_profile_directives.sql test_cdb_prof_plan
PLAN PLUGGABLE_DATABASE PROFILE SHARES UTIL PARALLEL
------------------------------ ------------------------- ------------------------- ---------- ---------- ----------
TEST_CDB_PROF_PLAN ORA$AUTOTASK 90 100
TEST_CDB_PROF_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1 100 100
TEST_CDB_PROF_PLAN GOLD 3 100 100
TEST_CDB_PROF_PLAN SILVER 2 50 50
SQL>
Enable/Disable Resource Plan with PDB Performance Profiles
Enabling and disabling resource plans in a CDB is the same as it was in pre-12c instances. Enable a plan by setting the RESOURCE_MANAGER_PLAN parameter to the name of the CDB resource plan, while connected to the root container.
CONN / AS SYSDBA ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'test_cdb_prof_plan'; SHOW PARAMETER RESOURCE_MANAGER_PLAN NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ resource_manager_plan string test_cdb_prof_plan SQL>
In addition to enabling the resource plan at the CDB level, we need to consider the PDB. Each PDB will use the default directive. To change an individual PDB to an alternative profile you need to set the DB_PERFORMANCE_PROFILE parameter at the PDB level, as shown below.
CONN / AS SYSDBA ALTER SESSION SET CONTAINER=pdb1; ALTER SYSTEM SET DB_PERFORMANCE_PROFILE=gold SCOPE=SPFILE; ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE; ALTER PLUGGABLE DATABASE OPEN; SHOW PARAMETER DB_PERFORMANCE_PROFILE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_performance_profile string GOLD SQL>
To switch the PDB back to using the default directive reset the DB_PERFORMANCE_PROFILE parameter.
CONN / AS SYSDBA ALTER SESSION SET CONTAINER=pdb1; ALTER SYSTEM SET DB_PERFORMANCE_PROFILE='' SCOPE=SPFILE; ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE; ALTER PLUGGABLE DATABASE OPEN; SHOW PARAMETER DB_PERFORMANCE_PROFILE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_performance_profile string SQL>
To disable the plan, set the RESOURCE_MANAGER_PLAN parameter to another plan, or blank it.
CONN / AS SYSDBA ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ''; SHOW PARAMETER RESOURCE_MANAGER_PLAN NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ resource_manager_plan string SQL>
Monitoring CPU and Parallel Execution Server Usage for PDBs
Oracle now provides views to monitor the resource (CPU, I/O, parallel execution, memory) usage of PDBs. Each view contains similar information, but for different retention periods.
V$RSRCPDBMETRIC: A single row per PDB, holding the last of the 1 minute samples.V$RSRCPDBMETRIC_HISTORY: 61 rows per PDB, holding the last 60 minutes worth of samples from theV$RSRCPDBMETRICview.V$RSRC_PDB: Cumulative statistics since the CDB resource plan ws set.DBA_HIST_RSRC_PDB_METRIC: AWR snaphots, retained based on the AWR retention period.
The following queries are examples of their usage.
CONN / AS SYSDBA
SET LINESIZE 300
COLUMN pdb_name FORMAT A10
COLUMN begin_time FORMAT A26
COLUMN end_time FORMAT A26
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YYYY HH24:MI:SS.FF';
-- Last sample per PDB.
SELECT r.con_id,
p.pdb_name,
r.begin_time,
r.end_time,
r.cpu_consumed_time,
r.cpu_wait_time,
r.avg_running_sessions,
r.avg_waiting_sessions,
r.avg_cpu_utilization,
r.avg_active_parallel_stmts,
r.avg_queued_parallel_stmts,
r.avg_active_parallel_servers,
r.avg_queued_parallel_servers
FROM v$rsrcpdbmetric r,
cdb_pdbs p
WHERE r.con_id = p.con_id
ORDER BY p.pdb_name;
-- Last hours samples for PDB1
SELECT r.con_id,
p.pdb_name,
r.begin_time,
r.end_time,
r.cpu_consumed_time,
r.cpu_wait_time,
r.avg_running_sessions,
r.avg_waiting_sessions,
r.avg_cpu_utilization,
r.avg_active_parallel_stmts,
r.avg_queued_parallel_stmts,
r.avg_active_parallel_servers,
r.avg_queued_parallel_servers
FROM v$rsrcpdbmetric_history r,
cdb_pdbs p
WHERE r.con_id = p.con_id
AND p.pdb_name = 'PDB1'
ORDER BY r.begin_time;
-- All AWR snapshot information for PDB1.
SELECT r.snap_id,
r.con_id,
p.pdb_name,
r.begin_time,
r.end_time,
r.cpu_consumed_time,
r.cpu_wait_time,
r.avg_running_sessions,
r.avg_waiting_sessions,
r.avg_cpu_utilization,
r.avg_active_parallel_stmts,
r.avg_queued_parallel_stmts,
r.avg_active_parallel_servers,
r.avg_queued_parallel_servers
FROM dba_hist_rsrc_pdb_metric r,
cdb_pdbs p
WHERE r.con_id = p.con_id
AND p.pdb_name = 'PDB1'
ORDER BY r.begin_time;
For more information see:
- Creating a CDB Resource Plan with PDB Performance Profiles
- Multitenant : Resource Manager with Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 1 (12.1)
- Resource Manager Quick Links : 8i, 9i, 10g, 11gR1, 11gR2, 12cR1, 12cR2, All Articles
Hope this helps. Regards Tim...