Part One: Creating an Oracle 12c Container Database (CDB) Resource Plan
In a CDB since we have multiple pluggable databases
sharing a set of common resources, we can prevent multiple workloads to compete
with each other for both system as well as CDB resources by using Resource
Manager.
Let us look at an example of managing resources for Pluggable
Databases (between PDB’s) at the multitenant Container database level.
The same can be achieved using 12c Cloud Control, but
displayed here are the steps to be performed at the command line using the
DBMS_RESOURCE_MANAGER package.
With Resource Manager at the Pluggable Database level, we
can limit CPU usage of a particular PDB as well as the number of parallel
execution servers which a particular PDB can use.
To allocate resources among PDB’s we use a concept of
shares where we assign shares to particular PDB’s and a higher share to a PDB
results in higher allocation of guaranteed resources to that PDB.
At a high level the steps involved include:
·
Create a Pending Area
·
Create a CDB resource plan
·
Create directives for the PDB’s
·
Optionally update the default directives which
will specify resources which any newly created PDB’s will be allocated or which
will be used when no directives have been explicitly defined for a particular
PDB
·
Optionally update the directives which apply by
default to the Automatic Maintenance Tasks which are configured to run in the
out of the box maintenance windows
·
Validate the Pending Area
·
Submit the Pending Area
·
Enable the plan at the CDB level by setting the
RESOURCE_MANAGER_PLAN parameter
Let us look at an example.
We have 5 Pluggable databases contained in the Container
database and we wish to enable resource management at the PDB level.
We wish to guarantee CPU allocation in the ratio
4:3:1:1:1 so that the CPU is distributed among the PDB’s in this manner:
PDBPROD1 : 40%
PDBPROD2: 30%
PDBPROD3: 10%
PDBPROD4 : 10%
PDBPROD5: 10%
Further for PDB’s PDBPROD3, PDBPROD4 and PDBPROD5 we wish
to ensure that CPU utilization for these 3 PDB’s never crosses the 70% limit.
Also for these 3 PDB’s we would like to limit the maximum
number of parallel execution servers available to the PDB.
The value of 70% means that if the
PARALLEL_SERVERS_TARGET initialization parameter is 200, then the PDB cannot
use more than a maximum of 140 parallel execution servers. For PDBPROD1 and
PDBPROD2 there is no limit, so they can use all 200 parallel execution servers
if available.
We also want to limit the resources used by the Automatic
Maintenance Tasks jobs when they do execute in a particular job window and also
want to specify a default resource allocation limit for newly created PDB’s or
those PDB’s where a resource limit directive has not been explicitly defined.
Create the Pending Area
SQL SYS@PRODCDB> exec
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
PL/SQL procedure successfully completed.
Create the CDB resource plan
SQL SYS@PRODCDB> BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN(
plan => 'PRODCDB_PLAN',
comment =>
'CDB resource plan for PRODCDB');
END;
/ 2 3
4 5 6
PL/SQL procedure successfully completed.
Create directives which will
specify how resources are allocated to each particular PDB
SQL SYS@PRODCDB> BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
plan => 'PRODCDB_PLAN',
pluggable_database =>
'PDBPROD1',
shares => 4,
utilization_limit => 100,
parallel_server_limit => 100);
END;
/ 2 3
4 5 6
7 8 9
PL/SQL procedure successfully completed.
SQL SYS@PRODCDB> BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
plan => 'PRODCDB_PLAN',
pluggable_database =>
'PDBPROD2',
shares => 3,
utilization_limit => 100,
parallel_server_limit => 100);
END;
/ 2 3
4 5 6
7 8 9
PL/SQL procedure successfully completed.
SQL SYS@PRODCDB> BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
plan => 'PRODCDB_PLAN',
pluggable_database =>
'PDBPROD3',
shares => 2,
utilization_limit => 70,
parallel_server_limit => 70);
END;
/ 2 3
4 5 6
7 8 9
PL/SQL procedure successfully completed.
SQL SYS@PRODCDB> BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
2 3
plan =>
'PRODCDB_PLAN',
4 pluggable_database => 'PDBPROD4',
5 shares => 1,
6 utilization_limit => 70,
7 parallel_server_limit => 70);
8 END;
9 /
SQL SYS@PRODCDB> SQL SYS@PRODCDB> SQL
SYS@PRODCDB> 2 3
plan => 'PRODCDB_PLAN',
4 pluggable_database => 'PDBPROD5',
5 shares => 1,
6 utilization_limit => 70,
7 parallel_server_limit => 70);
8 END;
9 /
PL/SQL procedure successfully completed.
Update (if required) default
directive applies to PDBs for which specific directives have not been defined
SQL SYS@PRODCDB> BEGIN
DBMS_RESOURCE_MANAGER.UPDATE_CDB_DEFAULT_DIRECTIVE(
plan => 'PRODCDB_PLAN',
new_shares => 1,
new_utilization_limit =>
50,
new_parallel_server_limit => 50);
END;
/ 2 3
4 5 6
7 8
PL/SQL procedure successfully completed.
Update the directive (if
required) which will limit the resources available to run the Automatic
Maintenance Tasks
SQL SYS@PRODCDB> BEGIN
2
DBMS_RESOURCE_MANAGER.UPDATE_CDB_AUTOTASK_DIRECTIVE(
3 plan => 'PRODCDB_PLAN',
4 new_shares => 1,
5 new_utilization_limit => 75,
6 new_parallel_server_limit => 75);
7 END;
8 /
PL/SQL procedure successfully completed.
Validate the Pending Area
SQL exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
PL/SQL procedure successfully completed.
Submit the Pending Area
SQL exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
PL/SQL procedure successfully completed.
Enable the CDB resource plan
SQL ALTER SYSTEM SET RESOURCE_MANAGER_PLAN =
'PRODCDB_PLAN';
System altered.
If we launch Database Express (or OEM for that matter) we
can now see the resource allocation to each of the PDB’s as well as the
resource utilization limit which has been imposed on some of the PDB’s.
We can also query the DBA_CDB_RSRC_PLAN_DIRECTIVES view to obtain the same information in
some more detail
SQL SYS@PRODCDB> SQL SYS@PRODCDB> SQL
SYS@PRODCDB> SQL SYS@PRODCDB> SELECT PLAN,
2 PLUGGABLE_DATABASE,
3 SHARES,
4 UTILIZATION_LIMIT,
5 PARALLEL_SERVER_LIMIT
6 FROM DBA_CDB_RSRC_PLAN_DIRECTIVES
7 ORDER BY PLAN;
Parallel
Pluggable Utilization Server
Plan Database Shares Limit
Limit
-------------------------- -------------------------
------ ----------- --------
DEFAULT_CDB_PLAN ORA$AUTOTASK 90 100
DEFAULT_CDB_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1
100 100
DEFAULT_MAINTENANCE_PLAN ORA$AUTOTASK 90 100
DEFAULT_MAINTENANCE_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1
100 100
Parallel
Pluggable Utilization Server
Plan Database Shares Limit
Limit
-------------------------- -------------------------
------ ----------- --------
ORA$INTERNAL_CDB_PLAN ORA$DEFAULT_PDB_DIRECTIVE
ORA$INTERNAL_CDB_PLAN ORA$AUTOTASK
ORA$QOS_CDB_PLAN ORA$AUTOTASK 90 100
ORA$QOS_CDB_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1
100 100
PRODCDB_PLAN PDBPROD3 2 70 70
PRODCDB_PLAN PDBPROD5 1 70 70
PRODCDB_PLAN PDBPROD2 3 100 100
PRODCDB_PLAN PDBPROD1 4 100 100
PRODCDB_PLAN PDBPROD4 1 70 70
Parallel
Pluggable Utilization Server
Plan Database Shares Limit
Limit
-------------------------- -------------------------
------ ----------- --------
PRODCDB_PLAN ORA$AUTOTASK 1 75 75
PRODCDB_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1
50 50
20 rows selected.
|
exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN(
plan => 'PRODCDB_PLAN',
comment =>
'CDB resource plan for PRODCDB');
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
plan => 'PRODCDB_PLAN',
pluggable_database =>
'PDBPROD1',
shares => 4,
utilization_limit => 100,
parallel_server_limit => 100);
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
plan => 'PRODCDB_PLAN',
pluggable_database =>
'PDBPROD2',
shares => 3,
utilization_limit => 100,
parallel_server_limit => 100);
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
plan => 'PRODCDB_PLAN',
pluggable_database =>
'PDBPROD3',
shares => 1,
utilization_limit => 70,
parallel_server_limit => 70);
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
plan => 'PRODCDB_PLAN',
pluggable_database =>
'PDBPROD4',
shares => 1,
utilization_limit => 70,
parallel_server_limit => 70);
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
plan => 'PRODCDB_PLAN',
pluggable_database =>
'PDBPROD5',
shares => 1,
utilization_limit => 70,
parallel_server_limit => 70);
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.UPDATE_CDB_DEFAULT_DIRECTIVE(
plan => 'PRODCDB_PLAN',
new_shares =>
1,
new_utilization_limit =>
50,
new_parallel_server_limit => 50);
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.UPDATE_CDB_AUTOTASK_DIRECTIVE(
plan => 'PRODCDB_PLAN',
new_shares =>
1,
new_utilization_limit =>
75,
new_parallel_server_limit => 75);
END;
/
exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN =
'PRODCDB_PLAN';
|
Part Two: Creating an Oracle 12c Pluggable Database (PDB) Resource Plan
We have seen how a CDB resource plan allocates resources
among the various PDB’s in the container database.
The PDB resource plan will now allocate resources within
the database based on Consumer Groups – the method is in fact the same as what
we used for Non-CDB’s in earlier versions of the Oracle database.
Our requirement is to ensure certain Batch users do not
hog the system resources when batch jobs execute and we would like to enable a
PDB resource plan which will impose limits on the amount of Undo the statement
can use as well as the amount of time the statement is allowed to run and also
the amount of physical I/O resources it can use.
At a high level the steps involved are:
·
Connect to the appropriate PDB where we need to
create the PDB Resource Plan
·
Create the Pending Area
·
Create the Consumer Groups
·
Map sessions to the Consumer Groups
·
Create the PDB Resource Plan
·
Create PDB resource plan directives
·
Validate the Pending Area
·
Submit the Pending Area
·
Enable the PDB Resource Plan
Connect to the appropriate
PDB
SQL SYS@PRODCDB> alter session set container=pdbprod4;
Session altered.
Create the Pending Area
SQL SYS@PRODCDB> BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
END;
/ 2 3
4
PL/SQL procedure successfully completed.
Create the PDB Resource Plan
SQL SYS@PRODCDB> BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN =>
'pdbprod4_plan',
COMMENT => 'Resource plan at the PDB level');
END;
/ 2 3
4 5
PL/SQL procedure successfully completed.
Create the Consumer Groups
SQL SYS@PRODCDB> BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP
=> 'batch_user_group',
COMMENT => 'Resource consumer group for batch
users');
END;
/ 2 3
4 5
PL/SQL procedure successfully completed.
Create the directives which
will limit resources allocated to a particular Consumer Group
Note – 20 MB of UNDO usage is a very small value – only used to
illustrate the example!
SQL SYS@PRODCDB> BEGIN
2
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'pdbprod4_plan',
3 GROUP_OR_SUBPLAN => 'batch_user_group',
4 COMMENT=>'Cancel SQL statement after
exceeding either 20 MB of Undo, elapsed time of 3 minutes or limit i/o to 10
MB',
5 UNDO_POOL => 20000,
6 SWITCH_ELAPSED_TIME => 180,
7 SWITCH_IO_MEGABYTES => 10,
8 SWITCH_GROUP=>'CANCEL_SQL',
9 SWITCH_FOR_CALL=> TRUE);
10 END;
11 /
PL/SQL procedure successfully completed.
Here are some of the other common directives we can set
(taken from the official documentation)
·
MAX_IDLE_TIME – maxiumum session idle time in seconds
·
MAX_EST_EXEC_TIME - maximum execution time (in CPU seconds) allowed for
a call as determined by the CBO
·
SWITCH_IO_REQS - number of physical I/O requests that a session can
execute before an action is taken
·
PARALLEL_SERVER_LIMIT - maximum percentage of the parallel execution
server pool that a particular consumer group can use
·
SWITCH_IO_LOGICAL - Number of logical I/O requests that will trigger
the action specified by SWITCH_GROUP
·
ACTIVE_SESS_POOL_P1 - Specifies the maximum number of concurrently
active sessions for a consumer group.
·
MGMT_P1 - For a plan with the MGMT_MTH parameter set to EMPHASIS,
specifies the CPU percentage to allocate at the first level
·
MGMT_P2 - For a plan with the MGMT_MTH parameter set to EMPHASIS, specifies the CPU
percentage to allocate at the second level
Create a directive for the
mandatory Consumer Group OTHER_GROUPS. This will apply to all users not mapped
to the Consumer Group BATCH_USER_GROUP
SQL SYS@PRODCDB> BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN
=> 'pdbprod4_plan',
2 3
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
4 COMMENT => 'Mandatory Group covering all
users not in batch group');
5 END;
6 /
Map the user SH to the
Consumer Group BATCH_USER_GROUP and set the initial Consumer Group for the user
SH
SQL SYS@PRODCDB> BEGIN
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
ATTRIBUTE
=> DBMS_RESOURCE_MANAGER.ORACLE_USER,
VALUE
=> 'SH',
CONSUMER_GROUP => 'batch_user_group');
END;
/ 2 3
4 5 6
7
PL/SQL procedure successfully completed.
SQL SYS@PRODCDB> BEGIN
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP
( 'SH','batch_user_group',FALSE);
END;
/ 2 3
4
PL/SQL procedure successfully completed.
SQL SYS@PRODCDB> BEGIN
DBMS_RESOURCE_MANAGER.set_initial_consumer_group('SH',
'batch_user_group');
END;
/ 2 3
4
PL/SQL procedure successfully completed.
Validate the Pending Area
SQL SYS@PRODCDB> BEGIN
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
END;
/
PL/SQL procedure successfully completed
Submit the Pending Area
BEGIN
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/
PL/SQL procedure successfully completed.
Enable the PDB Resource Plan
SQL SYS@PRODCDB> alter system set
resource_manager_plan='pdbprod4_plan';
System altered.
SQL SYS@PRODCDB> show parameter resource_manager_plan
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
resource_manager_plan string pdbprod4_plan
Let us now test the PDB resource plan and see if the
directive to limit the use of Undo to 20 MB is enforced.
Initially we can see that the user SH which belongs to
the BATCH_USER_GROUP Consumer Group is not using any Undo.
SQL SYS@PRODCDB> alter system set
resource_manager_plan='pdbprod4_plan';
System altered.
SQL SYS@PRODCDB> show parameter
resource_manager_plan
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
resource_manager_plan string pdbprod4_plan
From another session let us connect as the user SH and
execute an UPDATE statement which will cause the amount of Undo usage to exceed
the 20 MB limit.
SQL SH@pdbprod4> update sh.sales set
amount_sold=1;
Back to the original session we can now Undo usage is
increasing – and then we see it has gone back to 0
This is because the session was cancelled as the resource
limit of 20 MB Undo usage was exceeded
SQL SYS@PRODCDB> /
SESS_ID
CONSUMER_GROUP CURRENT_UNDO_CONSUMPTION
MAX_UNDO_CONSUMPTION
---------- --------------------------------
------------------------ --------------------
43
BATCH_USER_GROUP 6342144 0
74
OTHER_GROUPS
0 0
SQL SYS@PRODCDB> /
SESS_ID
CONSUMER_GROUP
CURRENT_UNDO_CONSUMPTION MAX_UNDO_CONSUMPTION
---------- --------------------------------
------------------------ --------------------
43
BATCH_USER_GROUP
11872540 0
74
OTHER_GROUPS
0 0
SQL SYS@PRODCDB> /
SESS_ID
CONSUMER_GROUP
CURRENT_UNDO_CONSUMPTION MAX_UNDO_CONSUMPTION
---------- --------------------------------
------------------------ --------------------
43
BATCH_USER_GROUP
11872540 0
74
OTHER_GROUPS
0 0
SQL SYS@PRODCDB> /
SESS_ID
CONSUMER_GROUP
CURRENT_UNDO_CONSUMPTION MAX_UNDO_CONSUMPTION
---------- --------------------------------
------------------------ --------------------
43
BATCH_USER_GROUP
11872540 0
74
OTHER_GROUPS
0 0
SQL SYS@PRODCDB> /
SESS_ID
CONSUMER_GROUP
CURRENT_UNDO_CONSUMPTION MAX_UNDO_CONSUMPTION
---------- --------------------------------
------------------------ --------------------
43
BATCH_USER_GROUP 0 0
74
OTHER_GROUPS
0 0
SQL
SH@pdbprod4> update sh.sales set amount_sold=1;
update
sh.sales set amount_sold=1
*
ERROR at line
1:
ORA-56720:
I/O data limit exceeded - call aborted
If we limit the usage of Undo now by including a WHERE
clause in the UPDATE statement, we can see that the Undo usage remains below
the 20 MB imposed resource limit and the statement executes successfully this
time.
SQL SH@pdbprod4> update sh.sales set
amount_sold=1 where rownum < 50001;
50000 rows updated.
SQL SYS@PRODCDB> /
SESS_ID
CONSUMER_GROUP
CURRENT_UNDO_CONSUMPTION MAX_UNDO_CONSUMPTION
---------- --------------------------------
------------------------ --------------------
43
BATCH_USER_GROUP 9565964 0
74
OTHER_GROUPS
0 0
SQL SYS@PRODCDB> /
SESS_ID
CONSUMER_GROUP CURRENT_UNDO_CONSUMPTION
MAX_UNDO_CONSUMPTION
---------- --------------------------------
------------------------ --------------------
43
BATCH_USER_GROUP
10830200 0
74
OTHER_GROUPS 0 0
SQL SYS@PRODCDB> /
SESS_ID
CONSUMER_GROUP
CURRENT_UNDO_CONSUMPTION MAX_UNDO_CONSUMPTION
---------- --------------------------------
------------------------ --------------------
43
BATCH_USER_GROUP
12400376 0
74
OTHER_GROUPS
0 0
SQL SYS@PRODCDB> /
SESS_ID
CONSUMER_GROUP CURRENT_UNDO_CONSUMPTION
MAX_UNDO_CONSUMPTION
---------- --------------------------------
------------------------ --------------------
43
BATCH_USER_GROUP
12400376 0
74
OTHER_GROUPS
0 0
No comments:
Post a Comment