DBA Hub

📋Steps in this guide1/8

Multitenant : Resource Manager with Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 1 (12.1)

Control resource allocation between pluggable databases and within an individual pluggable database.

oracle 12cconfigurationintermediate
by OracleDba
14 views
1

Container Database (CDB)

The following sections describe how resource manager can be used to control the resource usage between pluggable databases (PDBs). Resource manager does not currently have the ability to control memory usage between PDBs.
2

Create CDB Resource Plan

A CDB resource plan is made up of CDB resource plan directives. The plan 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. CDB resource plans are managed using the package. Each plan directive is made up of the following elements: - pluggable_database : The PDB 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_TARGET 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 CBD resource plan using the procedure, then adds two plan directives using the procedure. Information about the available CDB resource plans can be queried using the . Information about the CDB resource plan directives can be queried using the view. For the rest of the article the cdb_resource_plans.sql and cdb_resource_plan_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
DECLARE
  l_plan VARCHAR2(30) := 'test_cdb_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');

  DBMS_RESOURCE_MANAGER.create_cdb_plan_directive(
    plan                  => l_plan, 
    pluggable_database    => 'pdb1', 
    shares                => 3, 
    utilization_limit     => 100,
    parallel_server_limit => 100);

  DBMS_RESOURCE_MANAGER.create_cdb_plan_directive(
    plan                  => l_plan, 
    pluggable_database    => 'pdb2', 
    shares                => 3, 
    utilization_limit     => 100,
    parallel_server_limit => 100);

  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_PLAN';

   PLAN_ID PLAN                           COMMENTS                       STATUS     MAN
---------- ------------------------------ ------------------------------ ---------- ---
     92235 TEST_CDB_PLAN                  A test CDB resource plan                  NO

SQL>

COLUMN plan FORMAT A30
COLUMN pluggable_database FORMAT A25
SET LINESIZE 100

SELECT plan, 
       pluggable_database, 
       shares, 
       utilization_limit AS util,
       parallel_server_limit AS parallel
FROM   dba_cdb_rsrc_plan_directives
WHERE  plan = 'TEST_CDB_PLAN'
ORDER BY pluggable_database;
  
PLAN                           PLUGGABLE_DATABASE            SHARES       UTIL   PARALLEL
------------------------------ ------------------------- ---------- ---------- ----------
TEST_CDB_PLAN                  ORA$AUTOTASK                                 90        100
TEST_CDB_PLAN                  ORA$DEFAULT_PDB_DIRECTIVE          1        100        100
TEST_CDB_PLAN                  PDB1                               3        100        100
TEST_CDB_PLAN                  PDB2                               3        100        100

SQL>
3

Modify CDB Resource Plan

An existing resource plan is modified by creating, updating or deleting plan directives. The following code uses the procedure to add a new plan directive to the CDB resource plan we created previously. The procedure modifies an existing plan directive. The procedure deletes an existing plan 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
DECLARE
  l_plan VARCHAR2(30) := 'test_cdb_plan';
BEGIN
  DBMS_RESOURCE_MANAGER.clear_pending_area;
  DBMS_RESOURCE_MANAGER.create_pending_area;

  DBMS_RESOURCE_MANAGER.create_cdb_plan_directive(
    plan                  => l_plan, 
    pluggable_database    => 'pdb3', 
    shares                => 1, 
    utilization_limit     => 75,
    parallel_server_limit => 75);

  DBMS_RESOURCE_MANAGER.validate_pending_area;
  DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/

SQL> @cdb_resource_plan_directives.sql TEST_CDB_PLAN

PLAN                           PLUGGABLE_DATABASE            SHARES       UTIL   PARALLEL
------------------------------ ------------------------- ---------- ---------- ----------
TEST_CDB_PLAN                  ORA$AUTOTASK                                 90        100
TEST_CDB_PLAN                  ORA$DEFAULT_PDB_DIRECTIVE          1        100        100
TEST_CDB_PLAN                  PDB1                               3        100        100
TEST_CDB_PLAN                  PDB2                               3        100        100
TEST_CDB_PLAN                  PDB3                               1         75         75
SQL>

DECLARE
  l_plan VARCHAR2(30) := 'test_cdb_plan';
BEGIN
  DBMS_RESOURCE_MANAGER.clear_pending_area;
  DBMS_RESOURCE_MANAGER.create_pending_area;

  DBMS_RESOURCE_MANAGER.update_cdb_plan_directive(
    plan                      => l_plan, 
    pluggable_database        => 'pdb3', 
    new_shares                => 1, 
    new_utilization_limit     => 100,
    new_parallel_server_limit => 100);

  DBMS_RESOURCE_MANAGER.validate_pending_area;
  DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/

SQL> @cdb_resource_plan_directives.sql TEST_CDB_PLAN
   
PLAN                           PLUGGABLE_DATABASE            SHARES       UTIL   PARALLEL
------------------------------ ------------------------- ---------- ---------- ----------
TEST_CDB_PLAN                  ORA$AUTOTASK                                 90        100
TEST_CDB_PLAN                  ORA$DEFAULT_PDB_DIRECTIVE          1        100        100
TEST_CDB_PLAN                  PDB1                               3        100        100
TEST_CDB_PLAN                  PDB2                               3        100        100
TEST_CDB_PLAN                  PDB3                               1        100        100
SQL>

DECLARE
  l_plan VARCHAR2(30) := 'test_cdb_plan';
BEGIN
  DBMS_RESOURCE_MANAGER.clear_pending_area;
  DBMS_RESOURCE_MANAGER.create_pending_area;

  DBMS_RESOURCE_MANAGER.delete_cdb_plan_directive(
    plan                      => l_plan, 
    pluggable_database        => 'pdb3');

  DBMS_RESOURCE_MANAGER.validate_pending_area;
  DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/

SQL> @cdb_resource_plan_directives.sql TEST_CDB_PLAN

PLAN                           PLUGGABLE_DATABASE            SHARES       UTIL   PARALLEL
------------------------------ ------------------------- ---------- ---------- ----------
TEST_CDB_PLAN                  ORA$AUTOTASK                                 90        100
TEST_CDB_PLAN                  ORA$DEFAULT_PDB_DIRECTIVE          1        100        100
TEST_CDB_PLAN                  PDB1                               3        100        100
TEST_CDB_PLAN                  PDB2                               3        100        100

SQL>
4

Modify CDB Default Directive

In addition to creating PDB-specific plan directives, the default directive can be amended for a CBD resource plan. The following example uses the procedure to edit the default directive for 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
DECLARE
  l_plan VARCHAR2(30) := 'test_cdb_plan';
BEGIN
  DBMS_RESOURCE_MANAGER.clear_pending_area;
  DBMS_RESOURCE_MANAGER.create_pending_area;

  DBMS_RESOURCE_MANAGER.update_cdb_default_directive(
    plan                      => l_plan, 
    new_shares                => 1, 
    new_utilization_limit     => 80,
    new_parallel_server_limit => 80);

  DBMS_RESOURCE_MANAGER.validate_pending_area;
  DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/

SQL> @cdb_resource_plan_directives.sql TEST_CDB_PLAN

PLAN                           PLUGGABLE_DATABASE            SHARES       UTIL   PARALLEL
------------------------------ ------------------------- ---------- ---------- ----------
TEST_CDB_PLAN                  ORA$AUTOTASK                                 90        100
TEST_CDB_PLAN                  ORA$DEFAULT_PDB_DIRECTIVE          1         80         80
TEST_CDB_PLAN                  PDB1                               3        100        100
TEST_CDB_PLAN                  PDB2                               3        100        100

SQL>
5

Modify CDB Autotask Directive

There is a plan directive associated with the database autotask functionality. The configuration of this can be altered using the procedure.

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
DECLARE
  l_plan VARCHAR2(30) := 'test_cdb_plan';
BEGIN
  DBMS_RESOURCE_MANAGER.clear_pending_area;
  DBMS_RESOURCE_MANAGER.create_pending_area;

  DBMS_RESOURCE_MANAGER.update_cdb_autotask_directive(
    plan                      => l_plan, 
    new_shares                => 1, 
    new_utilization_limit     => 75,
    new_parallel_server_limit => 75);

  DBMS_RESOURCE_MANAGER.validate_pending_area;
  DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/

SQL> @cdb_resource_plan_directives.sql TEST_CDB_PLAN

PLAN                           PLUGGABLE_DATABASE            SHARES       UTIL   PARALLEL
------------------------------ ------------------------- ---------- ---------- ----------
TEST_CDB_PLAN                  ORA$AUTOTASK                       1         75         75
TEST_CDB_PLAN                  ORA$DEFAULT_PDB_DIRECTIVE          1         80         80
TEST_CDB_PLAN                  PDB1                               3        100        100
TEST_CDB_PLAN                  PDB2                               3        100        100

SQL>
6

Enable/Disable Resource Plan

Enabling and disabling resource plans in a CDB is the same as it was in pre-12c instances. Enable a plan by setting the paramter to the name of the CDB resource plan, while connected to the root container. 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
SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'TEST_CDB_PLAN';

System altered.

SQL> SHOW PARAMETER RESOURCE_MANAGER_PLAN

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

SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';

System altered.

SQL> SHOW PARAMETER RESOURCE_MANAGER_PLAN

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

Delete CDB Resource Plan

The procedure deletes CDB resource plans.

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
DECLARE
  l_plan VARCHAR2(30) := 'test_cdb_plan';
BEGIN
  DBMS_RESOURCE_MANAGER.clear_pending_area;
  DBMS_RESOURCE_MANAGER.create_pending_area;

  DBMS_RESOURCE_MANAGER.delete_cdb_plan(plan => l_plan);

  DBMS_RESOURCE_MANAGER.validate_pending_area;
  DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/

SQL> @cdb_resource_plans.sql

   PLAN_ID PLAN                           COMMENTS                       STATUS     MAN
---------- ------------------------------ ------------------------------ ---------- ---
     16774 DEFAULT_CDB_PLAN               Default CDB plan                          YES
     16775 DEFAULT_MAINTENANCE_PLAN       Default CDB maintenance plan              YES
     16776 ORA$INTERNAL_CDB_PLAN          Internal CDB plan                         YES
     16777 ORA$QOS_CDB_PLAN               QOS CDB plan                              YES

SQL>
8

Pluggable Database (PDB)

The use of resource manager inside the PDB is essentially unchanged compared to the pre-12c instances. Just remember, you have to be connected to the specific PDB when you set the parameter. You can read about how resource manager works in a PDB or in a pre-12c instance here: The following example shows how to create a simple resource plan for use within a PDB. 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
-- Connect to privileged user on PDB.
CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;

-- Create a resource plan.
BEGIN
  DBMS_RESOURCE_MANAGER.clear_pending_area();
  DBMS_RESOURCE_MANAGER.create_pending_area();

  -- Create plan
  DBMS_RESOURCE_MANAGER.create_plan(
    plan    => 'hybrid_plan',
    comment => 'Plan for a combination of high and low priority tasks.');

  -- Create consumer groups
  DBMS_RESOURCE_MANAGER.create_consumer_group(
    consumer_group => 'web_cg',
    comment        => 'Web based OTLP processing - high priority');

  DBMS_RESOURCE_MANAGER.create_consumer_group(
    consumer_group => 'batch_cg',
    comment        => 'Batch processing - low priority');

  -- Assign consumer groups to plan and define priorities
  DBMS_RESOURCE_MANAGER.create_plan_directive (
    plan             => 'hybrid_plan',
    group_or_subplan => 'web_cg',
    comment          => 'High Priority - level 1',
    mgmt_p1          => 70);

  DBMS_RESOURCE_MANAGER.create_plan_directive (
    plan             => 'hybrid_plan',
    group_or_subplan => 'batch_cg',
    comment          => 'Low Priority - level 2',
    mgmt_p1          => 20);

  DBMS_RESOURCE_MANAGER.create_plan_directive(
    plan             => 'hybrid_plan',
    group_or_subplan => 'OTHER_GROUPS',
    comment          => 'all other users - level 3',
    mgmt_p1          => 10);

  DBMS_RESOURCE_MANAGER.validate_pending_area;
  DBMS_RESOURCE_MANAGER.submit_pending_area();
END;
/

-- Assign users to consumer groups
BEGIN
  DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
    grantee_name   => 'web_user',
    consumer_group => 'web_cg',
    grant_option   => FALSE);

  DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
    grantee_name   => 'batch_user',
    consumer_group => 'batch_cg',
    grant_option   => FALSE);

  DBMS_RESOURCE_MANAGER.set_initial_consumer_group('web_user', 'web_cg');

  DBMS_RESOURCE_MANAGER.set_initial_consumer_group('batch_user', 'batch_cg');
END;
/

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = hybrid_plan;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!