8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Oracle Resource Manager Enhancements in Oracle Database 12c Release 1 (12.1)
This article describes the enhancements to automatic consumer group switching in Oracle 12c Release 1 (12.1).
- Runaway Query Management - Automatic Consumer Group Switching
- V$SQL_MONITOR
- Resource Manager with Container Databases (CDB) and Pluggable Databases (PDB)
Related articles.
- Resource Manager : Runaway Query Management
- Resource Manager Quick Links : 8i, 9i, 10g, 11gR1, 11gR2, 12cR1, 12cR2, All Articles
Runaway Query Management - Automatic Consumer Group Switching
Managing runaway queries using automatic consumer group switching in resource manager has a long history.
Oracle 8i allowed manual consumer group switching of a session using the
DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP
procedure.Oracle 9i introduced automatic consumer group switching using the
SWITCH_TIME
andSWITCH_GROUP
parameters of theCREATE_PLAN_DIRECTIVE
procedure. The switch was based on elapsed time of the session. Once switched, the consumer group was fixed for the lifetime of the session. If theSWITCH_ESTIMATE
parameter was set to TRUE, Oracle would estimate the elapsed time and preemptively switch the consumer group if necessary before the execution started.Oracle 10g introduced the
SWITCH_TIME_IN_CALL
parameter, which could be used in place ofSWITCH_TIME
to allow consumer group switching to work properly in connection pools where sessions are shared, such that one session will service multiple independent calls. In addition to allowing a consumer group switching, theSWITCH_GROUP
parameter could be set toKILL_SESSION
orCANCEL_SQL
to control runaway queries.Oracle 11g included a number of changes to this functionality. The
SWITCH_TIME_IN_CALL
parameter was deprecated in favor of the combination ofSWITCH_TIME
andSWITCH_FOR_CALL
. It also allowed consumer group switching based on physical I/O size or I/O requests using theSWITCH_IO_MEGABYTES
andSWITCH_IO_REQS
parameters respectively. Oracle 11gR2 altered theSWITCH_TIME
parameter so it was no longer an elapsed time, but a measure of CPU seconds used.
Oracle 12c introduces a number of changes related to consumer group switching. The SWITCH_GROUP
parameter can now also be set to LOG_ONLY
, so the trigger for a potential switch is logged, but no actual consumer group switch is performed. Plan directives can now include the SWITCH_IO_LOGICAL
and SWITCH_ELAPSED_TIME
parameters to trigger a consumer group switch, so the current list of parameters for the CREATE_PLAN_DIRECTIVE
procedure is now as follows.
SWITCH_GROUP
:NULL
,consumer_group_name
,CANCEL_SQL
,KILL_SESSION
,LOG_ONLY
.SWITCH_TIME
: Time in CPU seconds that will trigger a switch.SWITCH_ESTIMATE
: When set to TRUE, Oracle estimates the elapsed time and if necessary switches the consumer group before the execution begins.SWITCH_IO_MEGABYTES
: The total size of I/O requests that will trigger a switch.SWITCH_IO_REQS
: The number of physical I/O requests that will trigger a switch.SWITCH_FOR_CALL
: When set to TRUE, the switch is based on each top-level call. Once the call is complete, the consumer groups are switched back to original settings. When set to FALSE the switch is at session level and the new consumer group will remain until the session ends.SWITCH_IO_LOGICAL
: The number of logical I/O requests that will trigger a switch.SWITCH_ELAPSED_TIME
: The elapsed time that will trigger a switch.
The allowable values of the SWITCH_GROUP
parameter are described below.
NULL
: Automatic consumer group switching is not enabled for this plan directive.consumer_group_name
: The consumer group the session/call should be switched to if a threshold is reached.CANCEL_SQL
: If a threshold is reached, the current call is cancelled, but the session is not killed.KILL_SESSION
: If a threshold is reached, the current session is killed.LOG_ONLY
: If a threshold is reached, the event is logged in SQL Monitor, but nothing happens to the call or session.
For all the switch-related parameters, there is an equivalent NEW_*
parameter for altering the values using the UPDATE_PLAN_DIRECTIVE
procedure.
Here is an example of automatic consumer group switching to cancel a SQL statement.
We create a test user.
CONN sys/SysPassword1@//localhost:1521/pdb1 AS SYSDBA --DROP USER testuser1 CASCADE; CREATE USER testuser1 IDENTIFIED BY testuser1 QUOTA UNLIMITED ON users; GRANT CREATE SESSION, CREATE PROCEDURE TO testuser1;
In the following example we have a resource plan with a consumer group called NORMAL_CG
for normal sessions. Any sessions with the NORMAL_CG
consumer group that have a single call using CPU for more than 60 seconds will have that SQL cancelled.
BEGIN DBMS_RESOURCE_MANAGER.clear_pending_area; DBMS_RESOURCE_MANAGER.create_pending_area; -- Create plan DBMS_RESOURCE_MANAGER.create_plan( plan => 'long_running_query_plan', comment => 'Plan to handle long running queries.'); -- Create consumer groups DBMS_RESOURCE_MANAGER.create_consumer_group( consumer_group => 'normal_cg', comment => 'Consumer group for normal sessions.'); -- Assign consumer groups to plan and define priorities DBMS_RESOURCE_MANAGER.create_plan_directive ( plan => 'long_running_query_plan', group_or_subplan => 'normal_cg', comment => 'Normal Priority', mgmt_p1 => 100, switch_group => 'CANCEL_SQL', switch_time => 60, switch_for_call => TRUE); DBMS_RESOURCE_MANAGER.create_plan_directive( plan => 'long_running_query_plan', group_or_subplan => 'OTHER_GROUPS', comment => 'Default', mgmt_p2 => 100); DBMS_RESOURCE_MANAGER.validate_pending_area; DBMS_RESOURCE_MANAGER.submit_pending_area; END; /
We allow the TESTUSER1
user to switch to the NORMAL_CG consumer group, then we set the initial consumer group for the TESTUSER1
user to NORMAL_CG
.
BEGIN DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group( grantee_name => 'testuser1', consumer_group => 'normal_cg', grant_option => FALSE); DBMS_RESOURCE_MANAGER.set_initial_consumer_group('testuser1', 'normal_cg'); END; /
Finally we activate the plan.
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'long_running_query_plan';
We can monitor the current consumer group assignment with the following query.
COLUMN username FORMAT A30 COLUMN resource_consumer_group FORMAT A30 SELECT username, resource_consumer_group FROM v$session WHERE username = 'TESTUSER1';
Leave this session open so we can monitor the progress.
In a separate session we connect to the test user, create a function that just sits on CPU for the specified number of minutes, then query the function.
CONN testuser1/testuser1@//localhost:1521/pdb1 CREATE OR REPLACE FUNCTION burn_cpu (p_mins IN NUMBER) RETURN NUMBER AS l_start_time DATE; l_number NUMBER := 1; BEGIN l_start_time := SYSDATE; LOOP EXIT WHEN SYSDATE - l_start_time > (p_mins/24/60); l_number := l_number + 1; END LOOP; RETURN 0; END; / SELECT burn_cpu (5) FROM dual;
Back in our privileged session we see the initial consumer group.
COLUMN username FORMAT A30 COLUMN resource_consumer_group FORMAT A30 SELECT username, resource_consumer_group FROM v$session WHERE username = 'TESTUSER1'; USERNAME RESOURCE_CONSUMER_GROUP ------------------------------ ------------------------------ TESTUSER1 NORMAL_CG SQL>
After approximately 60 seconds the query calling the BURN_CPU function has been cancelled.
SQL> SELECT burn_cpu (5) FROM dual; Error starting at line : 1 in command - SELECT burn_cpu (5) FROM dual Error report - ORA-00040: active time limit exceeded - call aborted SQL>
We clean up the example by disabling the plan, then deleting it.
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ''; BEGIN DBMS_RESOURCE_MANAGER.clear_pending_area; DBMS_RESOURCE_MANAGER.create_pending_area; DBMS_RESOURCE_MANAGER.delete_plan_cascade( plan => 'long_running_query_plan'); DBMS_RESOURCE_MANAGER.validate_pending_area; DBMS_RESOURCE_MANAGER.submit_pending_area; END; /
V$SQL_MONITOR
The V$SQL_MONITOR view includes four new resource manager columns.
RM_LAST_ACTION
:CANCEL_SQL
,KILL_SESSION
,LOG_ONLY
,SWITCH TO <CG NAME>
RM_LAST_ACTION_REASON
:SWITCH_CPU_TIME
,SWITCH_IO_REQS
,SWITCH_IO_MBS
,SWITCH_ELAPSED_TIME
,SWITCH_IO_LOGICAL
RM_LAST_ACTION_TIME
: The time of the last resource manager action.RM_CONSUMER_GROUP
: The current consumer group.
Resource Manager with Container Databases (CDB) and Pluggable Databases (PDB)
In a multitennent environment resource manager performs two separate tasks. At the CDB level it controls the resources allocated to each PDB, allowing you to prioritise some PDBs over others. At the PDB level it controls the resources allocated to each session connected to the PDB, allowing you to prioritise some sessions over others, just as it does in pre-12c instances.
For more information see:
- CREATE_PLAN_DIRECTIVE
- V$SQL_MONITOR
- Resource Manager : Runaway Query Management
- Resource Manager Quick Links : 8i, 9i, 10g, 11gR1, 11gR2, 12cR1, 12cR2, All Articles
Hope this helps. Regards Tim...