Resource manager seems to be the forgotten tool in the DBAs toolbox. Almost every time I mention it to someone they look at me stunned as if they've never heard of it before. Oracle have continued to enhance resource manager with each version and 10g is no exception.
Related articles.
Oracle9i introduced the concept of switch times to enable consumer groups to be switched if the execution time reached a specified threshold. This worked well for basic sessions such as jobs, but was nearly useless for multi-tier environments using connection pooling.
The new SWITCH_TIME_IN_CALL
parameter of the CREATE_PLAN_DIRECTIVE
procedure solves this problem such that the resource usage of one client does not affect future clients in the same session. The default value this parameter is NULL, which represents unlimited time.
The value of this attribute can be updated using the code>NEW_SWITCH_TIME_IN_CALL parameter of the UPDATE_PLAN_DIRECTIVE
procedure.
The SWITCH_TIME_IN_CALL
and SWITCH_TIME
parameters are mutually exclusive.
In addition to automatically switching consumer groups, the CREATE_PLAN_DIRECTIVE
procedure can be used to automatically cancel a SQL statement or kill the session if it exceeds its SWITCH_TIME
limit. To achive this set the SWITCH_TIME
should be set as normal, but the SWITCH_GROUP
should be set to CANCEL_SQL
or KILL_SESSION
instead of a consumer group name.
Here is an example of automatic consumer group switching to cancel a SQL statement.
We create a test user.
CONN / 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 been active for more than 60 seconds will have their 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', cpu_p1 => 100, switch_group => 'CANCEL_SQL', switch_time => 60, switch_time_in_call => TRUE); DBMS_RESOURCE_MANAGER.create_plan_directive( plan => 'long_running_query_plan', group_or_subplan => 'OTHER_GROUPS', comment => 'Default', cpu_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 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.
CONN / AS SYSDBA 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; /
The MAX_IDLE_TIME
and MAX_IDLE_BLOCKER_TIME
parameters of the CREATE_PLAN_DIRECTIVE
procedure allow timeout thresholds to be set against consumer groups. The MAX_IDLE_TIME
parameter defines the maximum time a session assigned to this consumer group can remain in an idle state. The MAX_IDLE_BLOCKER_TIME
parameter defines the maximum time a session assigned to this consumer group can be idle while blocking another sessions resources. Every minute PMON checks for sessions exceeding these thresholds and kills them to release their resources. The default value of these parameters is NULL, which represents unlimited time.
The value of these attributes can be updated using the NEW_MAX_IDLE_TIME
and NEW_MAX_IDLE_BLOCKER_TIME
parameters of the UPDATE_PLAN_DIRECTIVE
procedure.
Oracle 10g introduces a method of maping session attributes to consumer groups, making the use of resource manager easier and more flexible.
A number of login and runtime attributes can be used to make a decision over which consumer group should be assigned to a session. The following constants have been added to the DBMS_RESOURCE_MANAGER
package.
-- Login Attributes oracle_user CONSTANT VARCHAR2(30) := 'ORACLE_USER' service_name CONSTANT VARCHAR2(30) := 'SERVICE_NAME'; client_os_user CONSTANT VARCHAR2(30) := 'CLIENT_OS_USER'; client_program CONSTANT VARCHAR2(30) := 'CLIENT_PROGRAM'; client_machine CONSTANT VARCHAR2(30) := 'CLIENT_MACHINE'; -- Runtime Attributes module_name CONSTANT VARCHAR2(30) := 'MODULE_NAME'; module_name_action CONSTANT VARCHAR2(30) := 'MODULE_NAME_ACTION'; service_module CONSTANT VARCHAR2(30) := 'SERVICE_MODULE'; service_module_action CONSTANT VARCHAR2(30) := 'SERVICE_MODULE_ACTION';
These constants are used by the SET_CONSUMER_GROUP_MAPPING
procedure to define the consumer group mappings.
DBMS_RESOURCE_MANAGER.set_consumer_group_mapping ( attribute IN VARCHAR2, value IN VARCHAR2, consumer_group IN VARCHAR2 DEFAULT NULL)
Assuming we have three consumer groups (OLTP_CONSUMER_GROUP, BATCH_CONSUMER_GROUP and OTHER_GROUPS) we might do something like the following.
BEGIN DBMS_RESOURCE_MANAGER.set_consumer_group_mapping ( attribute => DBMS_RESOURCE_MANAGER.oracle_user, value => 'OLTP_USER', consumer_group => 'OLTP_CONSUMER_GROUP'); DBMS_RESOURCE_MANAGER.set_consumer_group_mapping ( attribute => DBMS_RESOURCE_MANAGER.oracle_user, value => 'BATCH_USER', consumer_group => 'BATCH_CONSUMER_GROUP'); DBMS_RESOURCE_MANAGER.set_consumer_group_mapping ( attribute => DBMS_RESOURCE_MANAGER.module_name, value => 'REPORT', consumer_group => 'BATCH_CONSUMER_GROUP'); END; /
The combination of multiple attribute mappings and explicit switch requests can result in a large and apparently conflicting web of consumer group choices. Using the above mapping example we might have a reporting session (with its module set to "REPORT") run against the OLTP_USER schema. In this situation which consumer group should be selected? The SET_CONSUMER_GROUP_MAPPING_PRI
procedure makes the decision of which consumer group to select simpler by defining a priority for each attribute.
DBMS_RESOURCE_MANAGER.set_consumer_group_mapping_pri ( explicit IN NUMBER, oracle_user IN NUMBER, service_name IN NUMBER, client_os_user IN NUMBER, client_program IN NUMBER, client_machine IN NUMBER, module_name IN NUMBER, module_name_action IN NUMBER, service_module IN NUMBER, service_module_action IN NUMBER)
The parameters map to the constants with the exception of the EXPLICIT
parameter that represents explicit calls to switch consumer groups using the SWITCH_CURRENT_CONSUMER_GROUP
, SWITCH_CONSUMER_GROUP_FOR_SESS
and SWITCH_CONSUMER_GROUP_FOR_USER
procedures. The priorities assigned must be unique integers from 1-10, with 1 representing the highest priority.
BEGIN DBMS_RESOURCE_MANAGER.set_consumer_group_mapping_pri ( explicit => 1, oracle_user => 2, service_name => 3, client_os_user => 4, client_program => 5, client_machine => 6, module_name => 7, module_name_action => 8, service_module => 9, service_module_action => 10); END; /
Using the above example the ORACLE_USER
is a higher priority than the MODULE_NAME
.
For more information see:
Hope this helps. Regards Tim...
Back to normal view: https://oracle-base.com/articles/10g/resource-manager-enhancements-10g