Tuesday 14 January 2014

Things to know about 12C Resource Manager

Download PDF of this article http://goo.gl/E5V7uo

As we all know that Resource manager (DBRM), allows us to manage mixed workloads which contend for system and database resources like CPU, IO's, Undo Consumption and so on..In this article we present some advance features of Resource Manager in Oracle Database 12C and we assume you are familiar with the basics of Resource Manager functionality in Oracle Database.

What Answers Does the Resource Manager (DBRM) Provide for a CDB?

1) Improper allocation of resources among PDBs
2) Improper allocation of resources within a single PDB
3) Poor performance of most important PDBs while unimportant PDBs are doing good

Before going to the pluggable databases in 12C lets understand what changes we have in terms of triggering consumer group switching. So coming to the point that we know in 11g we can switch a session from one consumer group to another based on some conditions because triggering consumer group switching becomes very handy when ad hoc queries demand high resources and at that point of time giving priority to some groups/sessions becomes necessary, with 11g we have the parameters switch_io_megabytes, switch_io_reqs switch_for_call, so whats new in 12C ?

The new parameters in 12C which are used to trigger consumer group switching are SWITCH_IO_LOGICAL, SWITCH_IO_ELAPSED. Other parameters new in 12C like shares and parallel_stmt_critical will be discussed below later but lets continue with consumer group switching.

Triggering consumer group switching becomes necessary when it comes to resource sharing and on top of that giving priorities to ones who need it most while restricting the ones who are of medium and low priority. So here in 12C in the context of consumer groups we have a new meta consumer group known as LOG_ONLY, and the use of this group is that in cases where we want to log the runaway queries without changing its consumer group or taking another action like KILL_SESSION or ABORT the call itself with CANCEL_SQL. These queries are actually runaway queries and mostly we monitor it through Real time SQL Monitor which was a new feature in 11g. So again whats new in 12C anyway ? In the context of the relation between SQL MONITOR and the Resource Manager ? Well a special new column RM_LAST_ACTION in V$SQL_MONITOR which tells us the recent action taken on this SQL by resource manager (RM) like for example KILL_SESSION, CANCEL_SQL, LOG_ONLY and so on.. My favourite column RM_LAST_ACTION_REASON which tells us why it happened like for example number of I/O's has reached threshold because the SWITCH_IO_REQS parameter is violated because this limit implicates the number of I/O requests that a session can issue before an action is taken. However Mnnn slave of MMON will always update a row in v$rsrcmgrmetric and v$rsrcmgrmetric_history every 60 seconds regardless of whether there is a resource plan active or not. Moreover in 12C, other new columns in V$SQL_MONITOR are RM_LAST_ACTION_TIME which tells the time of the most recent action that was taken on this SQL operation by Resource Manager and RM_CONSUMER_GROUP which tells the current consumer group for this SQL operation .

Some important views for the SQL MONITOR are V$SQL_MONITOR_SESSTAT, V$SQL_MONITOR_STATNAME

So lets take a switching example, The following PL/SQL code creates a resource plan directive for the OLTP group that temporarily switches any session in that group to the LOW_GROUP consumer group if the session exceeds 800 logical I/O requests. The session is returned to its original group after the offending top call is complete.

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
PLAN => 'DAY',
GROUP_OR_SUBPLAN => 'OLTP',
COMMENT => 'Its an OLTP group',
MGMT_P1 => 70,
SWITCH_GROUP => 'LOW_GROUP',
SWITCH_IO_LOGICAL => 800,
SWITCH_FOR_CALL => TRUE);
END;
/


Going further..As we all know that Resource Manager manages several types of resources like CPU, I/O , Parallel Execution Servers, Runaway Queries, Active Session Pool with Queuing,  Undo Pool, Idle Time Limit. However in our discussion related to 12C we will also talk about Parallelism in DBRM in 12C which is a very important resource in Warehouse VLDB's. A new parameter PARALLEL_SERVER_LIMIT in 12C is very interesting, it specifies the maximum percentage of the parallel execution server pool that a particular consumer group can use, for example the total number of parallel execution servers is 72, which is specified by the PARALLEL_SERVERS_TARGET initialization parameter which is a dynamic parameter. So according to the above value if in resource manager the PARALLEL_SERVER_LIMIT  attribute for the consumer group OLTP is set to 50%. This consumer group can use a maximum of 50% of 72, or in other words 36 parallel execution servers.

PARALLEL_STMT_CRITICAL is another interesting parameter it specifies whether parallel statements from the consumer group are critical or not . So when BYPASS_QUEUE is specified, parallel statements from the consumer group are critical. These statements bypass the parallel queue and are executed immediately. However when FALSE or NULL (the default) is specified, parallel statements from the consumer group are not critical. These statements are added to the parallel queue when necessary.

_________________________________________________________________________

A major change in 12C is the Pluggable databases which may be plugged into or unplugged into multitenant container database (CDB) that includes zero, one, or many customer-created pluggable databases. In other words the PDBs can be unplugged from the root of one CDB and plugged into the root of another CDB. So let us discuss some points related to pluggable databases when it comes to resource manager the DBRM.

At any point we have 3 Attributes for PDB Directives in a CDB Resource Plan which are SHARES, UTILIZATION_LIMIT, PARALLEL_SERVER_LIMIT.

SHARES attribute defines how much share a specific pluggable database will get, like in the following example there are 3 pluggable databases PDB_A, PDB_B, PDB_C with 2,2,1 of shares respectively, this means that PDB_A and PDB_B is twice more important than PDB_C and the resources are to be shared in the ratio of 2:2:1 respectively for all 3 which means they will be guaranteed minimum of 40%, 40% and 20% CPU respectively. And this share of 2:2:1 is minimum guarantee, it can increase more if there is no resource contention. So this is how allocation of resources among pluggable databases (PDBs) in a multitenant container database (CDB) works. Moreover also it allocates resources among consumer groups in a non-CDB or in a PDB as well. A point here to note is that CPU utilization specified by utilization_limit parameter is 100 which means unlimited for pdb_a and pdb_b and for pdb_c the limit is 30 and parallel_server_limit is 30 for pdb_c which means that the maximum percentage (30%) of the parallel execution server pool pdb_c can use. Another interesting point here is that SHARE means all resources together like CPU and Parallel execution servers, so in this example pdb_a and pdb_b both are twice more important as compared to pdb_c so in this case queued parallel queries from the pdb_a and pdb_b PDBs are selected two times as often  as queued parallel queries from the pdb_c PDB, but the upper limit for pdb_c is 30 as explained already.


BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
plan => 'plan_one',
pluggable_database => 'pdb_a',
shares => 2,
utilization_limit => 100,
parallel_server_limit => 100);
END;
/

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
plan => 'plan_one',
pluggable_database => 'pdb_b',
shares => 2,
utilization_limit => 100,
parallel_server_limit => 100);
END;
/

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
plan => 'plan_one',
pluggable_database => 'pdb_c',
shares => 1,
utilization_limit => 30,
parallel_server_limit => 30);
END;
/

Remember the  PDBs pdb_a and pdb_b are more important than the other PDB which is pdb_c in this CDB. Because they get a higher share (2), unlimited CPU utilization resource, and unlimited parallel execution server resource.

Important Views related to CDB/PDB:

DBA_CDB_RSRC_PLANS := Shows all the CDB resource plan directive
DBA_PDBS := Shows PDBs beloging to a particular CDB. Should be run from ROOT.
DBA_PDB_HISTORY := Gives information of a PDB history belonging to its parent (CDB)
V$PDBS := Shows information about the current instance PDB
V$PDB_INCARNATION := Shows information about all PDB incarnations. Incarnation is set whenever a PDB is opened with a resetlogs option.

Download PDF of this article http://goo.gl/E5V7uo