Back to normal view: https://oracle-base.com/articles/10g/resource-manager-enhancements-10g

Oracle Resource Manager Enhancements in Oracle Database 10g

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.

Setting Consumer Group Switch Times Per Call

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.

CANCEL_SQL and KILL_SESSION

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;
/

Setting Consumer Group Idle Timeouts

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.

Automatically Assigning Resource Consumer Groups to Sessions

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 the Top.

Back to normal view: https://oracle-base.com/articles/10g/resource-manager-enhancements-10g