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.

Setting Consumer Group Switch Times Per Call
CANCEL_SQL and KILL_SESSION
Setting Consumer Group Idle Timeouts
Automatically Assigning Resource Consumer Groups to Sessions

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 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 an 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.

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 further information see:

Hope this helps. Regards Tim...

Back to the Top.