8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

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.

Related 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:

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.

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:

Hope this helps. Regards Tim...

Back to the Top.