DBA Hub

📋Steps in this guide1/4

Multitenant : Resource Manager PDB Performance Profiles in Oracle Database 12c Release 2 (12.2)

PDB performance profiles define the resource management for groups of PDBs in Oracle Database 12c Release 2 (12.2).

oracle 12cconfigurationintermediate
by OracleDba
14 views
1

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 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 ( initialization 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 procedure, then adds two profile directives using the procedure to represent the typical gold, silver levels of service. Information about the available CDB resource plans can be queried using the view. Information about the CDB resource plan directives can be queried using the view. Notice we use the column as well as the column. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
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;
/

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>

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>
2

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 procedure to add a new profile directive to the CDB resource plan we created previously. The procedure modifies an existing profile directive. The procedure deletes an existing profile directive from the CDB resource plan.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
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>

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>

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>
3

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 parameter to the name of the CDB resource plan, while connected to the root container. 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 parameter at the PDB level, as shown below. To switch the PDB back to using the default directive reset the parameter. To disable the plan, set the parameter to another plan, or blank it.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
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>

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>

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>

CONN / AS SYSDBA

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';

SHOW PARAMETER RESOURCE_MANAGER_PLAN

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan                string
SQL>
4

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. - : A single row per PDB, holding the last of the 1 minute samples. - : 61 rows per PDB, holding the last 60 minutes worth of samples from the view. - : Cumulative statistics since the CDB resource plan ws set. - : AWR snaphots, retained based on the AWR retention period. The following queries are examples of their usage. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
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;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!