Skip Headers
Oracle® Database Administrator's Guide
11g Release 2 (11.2)

Part Number E17120-11
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

Assigning Sessions to Resource Consumer Groups

This section describes the automatic and manual methods that database administrators, users, and applications can use to assign sessions to resource consumer groups. When a session is assigned to a resource consumer group, Oracle Database Resource Manager (the Resource Manager) can manage resource allocation for it.

Note:

Sessions that are not assigned to a consumer group are placed in the consumer group OTHER_GROUPS.

This section includes the following topics:

Overview of Assigning Sessions to Resource Consumer Groups

Before you enable the Resource Manager, you must specify how user sessions are assigned to resource consumer groups. You do this by creating mapping rules that enable the Resource Manager to automatically assign each session to a consumer group upon session startup, based upon session attributes. After a session is assigned to its initial consumer group and is running, you can call a procedure to manually switch the session to a different consumer group. You would typically do this if the session is using excessive resources and must be moved to a consumer group that is more limited in its resource allocation. You can also grant the switch privilege to users and to applications so that they can switch their sessions from one consumer group to another.

The database can also automatically switch a session from one consumer group to another (typically lower priority) consumer group when there are changes in session attributes or when a session exceeds designated resource consumption limits.

Assigning an Initial Resource Consumer Group

The initial consumer group of a session is determined by the mapping rules that you configure. For information on how to configure mapping rules, see "Specifying Session-to–Consumer Group Mapping Rules".

Specifying Session-to–Consumer Group Mapping Rules

This section provides background information about session-to–consumer group mapping rules, and describes how to create and prioritize them. The following topics are covered:

About Session-to–Consumer Group Mapping Rules

By creating session-to–consumer group mapping rules, you can:

  • Specify the initial consumer group for a session based on session attributes.

  • Enable the Resource Manager to dynamically switch a running session to another consumer group based on changing session attributes.

The mapping rules are based on session attributes such as the user name, the service that the session used to connect to the database, or the name of the client program.

To resolve conflicts among mapping rules, the Resource Manager orders the rules by priority. For example, suppose user SCOTT connects to the database with the SALES service. If one mapping rule states that user SCOTT starts in the MED_PRIORITY consumer group, and another states that sessions that connect with the SALES service start in the HIGH_PRIORITY consumer group, mapping rule priorities resolve this conflict.

There are two types of session attributes upon which mapping rules are based: login attributes and run-time attributes. The login attributes are meaningful only at session login time, when the Resource Manager determines the initial consumer group of the session. Run-time attributes apply any time during and after session login. You can reassign a logged in session to another consumer group by changing any of its run-time attributes.

You use the SET_CONSUMER_GROUP_MAPPING and SET_CONSUMER_GROUP_MAPPING_PRI procedures to configure the automatic assignment of sessions to consumer groups. You must use a pending area for these procedures. (You must create the pending area, run the procedures, optionally validate the pending area, and then submit the pending area. For examples of using the pending area, see "Creating a Complex Resource Plan".)

A session is automatically switched to a consumer group through mapping rules at distinct points in time:

  • When the session first logs in, the mapping rules are evaluated to determine the initial group of the session.

  • If a session attribute is dynamically changed to a new value (which is only possible for run-time attributes), then the mapping rules are reevaluated, and the session might be switched to another consumer group.

Predefined Consumer Group Mapping Rules

Each Oracle database comes with a set of predefined consumer group mapping rules:

  • As described in "About Resource Consumer Groups", all sessions created by user accounts SYS or SYSTEM are initially mapped to the SYS_GROUP consumer group.

  • Sessions performing a data load with Data Pump or performing backup or copy operations with RMAN are automatically mapped to the predefined consumer groups designated in Table 27-6.

You can use the DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING procedure to modify or delete any of these predefined mapping rules.

Creating Consumer Group Mapping Rules

You use the SET_CONSUMER_GROUP_MAPPING procedure to map a session attribute/value pair to a consumer group. The parameters for this procedure are the following:

Parameter Description
ATTRIBUTE The session attribute type, specified as a package constant
VALUE The value of the attribute
CONSUMER_GROUP The consumer group to map to for this attribute/value pair

ATTRIBUTE can be one of the following:

Attribute Type Description
ORACLE_USER Login The Oracle Database user name
SERVICE_NAME Login The database service name used by the client to establish a connection
CLIENT_OS_USER Login The operating system user name of the client that is logging in
CLIENT_PROGRAM Login The name of the client program used to log in to the server
CLIENT_MACHINE Login The name of the computer from which the client is making the connection
CLIENT_ID Login The client identifier for the session

The client identifier session attribute is set by the DBMS_SESSION.SET_IDENTIFIER procedure.

MODULE_NAME Run-time The module name in the currently running application as set by the DBMS_APPLICATION_INFO.SET_MODULE procedure or the equivalent OCI attribute setting
MODULE_NAME_ACTION Run-time A combination of the current module and the action being performed as set by either of the following procedures or their equivalent OCI attribute setting:
  • DBMS_APPLICATION_INFO.SET_MODULE

  • DBMS_APPLICATION_INFO.SET_ACTION

The attribute is specified as the module name followed by a period (.), followed by the action name (module_name.action_name).

SERVICE_MODULE Run-time A combination of service and module names in this form: service_name.module_name
SERVICE_MODULE_ACTION Run-time A combination of service name, module name, and action name, in this form: service_name.module_name.action_name
ORACLE_FUNCTION Run-time An RMAN or Data Pump operation. Valid values are DATALOAD, BACKUP, and COPY. There are predefined mappings for each of these values. If your session is performing any of these functions, it is automatically mapped to a predefined consumer group. See Table 27-6 for details.

For example, the following PL/SQL block causes user SCOTT to map to the DEV_GROUP consumer group every time that he logs in:

BEGIN
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING  
     (DBMS_RESOURCE_MANAGER.ORACLE_USER, 'SCOTT', 'DEV_GROUP');
END;
/

Again, you must create a pending area before running the SET_CONSUMER_GROUP_MAPPING procedure.

You can use wildcards for the value of most attributes in the value parameter in the SET_CONSUMER_GROUP_MAPPING procedure. Specifically, you can use wildcards for the value of all attributes except for ORACLE_USER, SERVICE_MODULE, and SERVICE_MODULE_ACTION. To specify values with wildcards, use the same semantics as the SQL LIKE operator. Specifically, wildcards use the following semantics:

  • % for a multicharacter wildcard

  • _ for a single character wildcard

  • \ to escape the wildcards

Modifying and Deleting Consumer Group Mapping Rules

To modify a consumer group mapping rule, run the SET_CONSUMER_GROUP_MAPPING procedure against the desired attribute/value pair, specifying a new consumer group. To delete a rule, run the SET_CONSUMER_GROUP_MAPPING procedure against the desired attribute/value pair and specify a NULL consumer group.

Creating Mapping Rule Priorities

To resolve conflicting mapping rules, you can establish a priority ordering of the session attributes from most important to least important. You use the SET_CONSUMER_GROUP_MAPPING_PRI procedure to set the priority of each attribute to a unique integer from 1 (most important) to 12 (least important). The following example illustrates this setting of priorities:

BEGIN
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING_PRI(
    EXPLICIT => 1,
    SERVICE_MODULE_ACTION => 2,
    SERVICE_MODULE => 3,
    MODULE_NAME_ACTION => 4,
    MODULE_NAME => 5,
    SERVICE_NAME => 6,
    ORACLE_USER => 7,
    CLIENT_PROGRAM => 8,
    CLIENT_OS_USER => 9,
    CLIENT_MACHINE => 10,
    CLIENT_ID => 11,
    ORACLE_FUNCTION => 12);
END;
/

In this example, the priority of the database user name is set to 7 (less important), while the priority of the module name is set to 5 (more important).

Note:

SET_CONSUMER_GROUP_MAPPING_PRI requires that you include the pseudo-attribute EXPLICIT as an argument. It must be set to 1. It indicates that explicit consumer group switches have the highest priority. You explicitly switch consumer groups with these package procedures, which are described in detail in Oracle Database PL/SQL Packages and Types Reference:
  • DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP

  • DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS

  • DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER

To illustrate how mapping rule priorities work, continuing with the previous example, assume that in addition to the mapping of user SCOTT to the DEV_GROUP consumer group, there is also a module name mapping rule as follows:

BEGIN
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
     (DBMS_RESOURCE_MANAGER.MODULE_NAME, 'EOD_REPORTS', 'LOW_PRIORITY');
END;
/

Now if the application in user SCOTT's session sets its module name to EOD_REPORTS, the session is reassigned to the LOW_PRIORITY consumer group, because module name mapping has a higher priority than database user mapping.

You can query the view DBA_RSRC_MAPPING_PRIORITY to see the current priority ordering of session attributes.

To prevent unauthorized clients from setting their session attributes so that they map to higher priority consumer groups, user switch privileges for consumer groups are enforced. Thus, even though the attribute of a particular session matches a mapping pair, the mapping rule is ignored if the session does not have the switch privilege for the designated consumer group.

See Also:

Switching Resource Consumer Groups

This section describes ways to switch the resource consumer group of a session.

This section contains the following topics:

Manually Switching Resource Consumer Groups

The DBMS_RESOURCE_MANAGER PL/SQL package provides two procedures that enable you to change the resource consumer group of running sessions. Both of these procedures can also change the consumer group of any parallel execution server sessions associated with the coordinator session. The changes made by these procedures pertain to current sessions only; they are not persistent. They also do not change the initial consumer groups for users.

Instead of killing (terminating) a session of a user who is using excessive CPU, you can change that user's consumer group to one that is allocated fewer resources.

Switching a Single Session

The SWITCH_CONSUMER_GROUP_FOR_SESS procedure causes the specified session to immediately be moved into the specified resource consumer group. In effect, this procedure can raise or lower priority of the session.

The following PL/SQL block switches a specific session to a new consumer group. The session identifier (SID) is 17, the session serial number (SERIAL#) is 12345, and the new consumer group is the HIGH_PRIORITY consumer group.

BEGIN
  DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS ('17', '12345',
   'HIGH_PRIORITY');
END;
/

The SID, session serial number, and current resource consumer group for a session are viewable using the V$SESSION view.

See Also:

Oracle Database Reference for details about the V$SESSION view.

Switching All Sessions for a User

The SWITCH_CONSUMER_GROUP_FOR_USER procedure changes the resource consumer group for all sessions pertaining to the specified user name. The following PL/SQL block switches all sessions that belong to user HR to the LOW_GROUP consumer group:

BEGIN
  DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER ('HR',
    'LOW_GROUP'); 
END;
/

Enabling Users or Applications to Manually Switch Consumer Groups

You can grant a user the switch privilege so that he can switch his current consumer group using the SWITCH_CURRENT_CONSUMER_GROUP procedure in the DBMS_SESSION package. A user can run this procedure from an interactive session, for example from SQL*Plus, or an application can call this procedure to switch its session, effectively dynamically changing its priority.

The SWITCH_CURRENT_CONSUMER_GROUP procedure enables users to switch to only those consumer groups for which they have the switch privilege. If the caller is another procedure, then this procedure enables users to switch to a consumer group for which the owner of that procedure has switch privileges.

The parameters for this procedure are the following:

Parameter Description
NEW_CONSUMER_GROUP The consumer group to which the user is switching.
OLD_CONSUMER_GROUP Returns the name of the consumer group from which the user switched. Can be used to switch back later.
INITIAL_GROUP_ON_ERROR Controls behavior if a switching error occurs.

If TRUE, in the event of an error, the user is switched to the initial consumer group.

If FALSE, raises an error.


The following SQL*Plus session illustrates switching to a new consumer group. By printing the value of the output parameter old_group, the example illustrates how the old consumer group name is saved.

SET serveroutput on
DECLARE
    old_group varchar2(30);
BEGIN
  DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP('BATCH_GROUP', old_group, FALSE);
  DBMS_OUTPUT.PUT_LINE('OLD GROUP = ' || old_group);
END;
/

The following line is output:

OLD GROUP = OLTP_GROUP

Note that the Resource Manager considers a switch to have taken place even if the SWITCH_CURRENT_CONSUMER_GROUP procedure is called to switch the session to the consumer group that it is already in.

Note:

The Resource Manager also works in environments where a generic database user name is used to log on to an application. The DBMS_SESSION package can be called to switch the consumer group assignment of a session at session startup, or as particular modules are called.

See Also:

Oracle Database PL/SQL Packages and Types Reference for additional examples and more information about the DBMS_SESSION package

Specifying Automatic Resource Consumer Group Switching

You can configure the Resource Manager to automatically switch a session to another consumer group when a certain condition is met. Automatic switching can occur when:

  • A session attribute changes, causing a new mapping rule to take effect.

  • A session exceeds the CPU or I/O resource consumption limits set by its consumer group.

The following sections provide details:

Specifying Automatic Switching with Mapping Rules

If a session attribute changes while the session is running, then the session-to–consumer group mapping rules are reevaluated. If a new rule takes effect, then the session might be moved to a different consumer group. See "Specifying Session-to–Consumer Group Mapping Rules" for more information.

Specifying Automatic Switching by Setting Resource Limits

This section describes managing runaway sessions or calls that use CPU or I/O resources beyond a specified limit. A runaway session is a SQL query, while a runaway call is a PL/SQL call.

When you create a resource plan directive for a consumer group, you can specify limits for CPU and I/O resource consumption for sessions in that group. You can then specify the action that is to be taken if any single call within a session exceeds one of these limits. The possible actions are the following:

  • The session is dynamically switched to a designated consumer group.

    The target consumer group is typically one that has lower resource allocations. The session's user must have switch privileges on the new consumer group, otherwise the switch cannot occur. See "Granting and Revoking the Switch Privilege" for more information.

  • The session is killed (terminated).

  • The session's current SQL statement is aborted.

The following are the resource plan directive attributes that are involved in this type of automatic session switching.

  • SWITCH_GROUP

  • SWITCH_TIME

  • SWITCH_ESTIMATE

  • SWITCH_IO_MEGABYTES

  • SWITCH_IO_REQS

  • SWITCH_FOR_CALL

See "Creating Resource Plan Directives" for descriptions of these attributes.

Switches occur for sessions that are running and consuming resources, not waiting for user input or waiting for CPU cycles. After a session is switched, it continues in the target consumer group until it becomes idle, at which point it is switched back to its original consumer group. However, if SWITCH_FOR_CALL is set to TRUE, then the Resource Manager does not wait until the session is idle to return it to its original resource consumer group. Instead, the session is returned when the current top-level call completes. A top-level call in PL/SQL is an entire PL/SQL block treated as one call. A top-level call in SQL is an individual SQL statement.

The Resource Manager views a session as idle if a certain amount of time passes between calls. This time interval is not configurable.

SWITCH_FOR_CALL is useful for three-tier applications where the middle tier server is using session pooling.

A switched session is allowed to continue running even if the active session pool for the new group is full. Under these conditions, a consumer group can have more sessions running than specified by its active session pool.

The following are examples of automatic switching based on resource limits:

Example 1

The following PL/SQL block creates a resource plan directive for the OLTP group that switches any session in that group to the LOW_GROUP consumer group if a call in the sessions exceeds 5 seconds of CPU time. This example prevents unexpectedly long queries from consuming too many resources. The switched-to consumer group is typically one with lower resource allocations.

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
   PLAN             => 'DAYTIME',
   GROUP_OR_SUBPLAN => 'OLTP',
   COMMENT          => 'OLTP group',
   MGMT_P1          => 75,
   SWITCH_GROUP     => 'LOW_GROUP',
   SWITCH_TIME      => 5);
END;
/

Example 2

The following PL/SQL block 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 10,000 I/O requests or exceeds 2,500 Megabytes of data transferred. The session is returned to its original group after the offending top call is complete.

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
   PLAN                => 'DAYTIME',
   GROUP_OR_SUBPLAN    => 'OLTP',
   COMMENT             => 'OLTP group',
   MGMT_P1             => 75,
   SWITCH_GROUP        => 'LOW_GROUP',
   SWITCH_IO_REQS      => 10000,
   SWITCH_IO_MEGABYTES => 2500,
   SWITCH_FOR_CALL     => TRUE);
END;
/

Example 3

The following PL/SQL block creates a resource plan directive for the OLTP group that kills (terminates) any session that exceeds 60 seconds of CPU time. This example prevents runaway queries from consuming too many resources.

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
   PLAN             => 'DAYTIME',
   GROUP_OR_SUBPLAN => 'OLTP',
   COMMENT          => 'OLTP group',
   MGMT_P1          => 75,
   SWITCH_GROUP     => 'KILL_SESSION',
   SWITCH_TIME      => 60);
END;
/

Granting and Revoking the Switch Privilege

Using the DBMS_RESOURCE_MANAGER_PRIVS PL/SQL package, you can grant or revoke the switch privilege to a user, role, or PUBLIC. The switch privilege enables a user or application to switch a session to a specified resource consumer group. It also enables the database to automatically switch a session to a consumer group specified in a session-to–consumer group mapping rule or specified in the SWITCH_GROUP parameter of a resource plan directive. The package also enables you to revoke the switch privilege. The relevant package procedures are listed in the following table.

Procedure Description
GRANT_SWITCH_CONSUMER_GROUP Grants permission to a user, role, or PUBLIC to switch to a specified resource consumer group.
REVOKE_SWITCH_CONSUMER_GROUP Revokes permission for a user, role, or PUBLIC to switch to a specified resource consumer group.

OTHER_GROUPS has switch privileges granted to PUBLIC. Therefore, all users are automatically granted the switch privilege for this consumer group.

Granting the Switch Privilege

The following example grants user SCOTT the privilege to switch to consumer group OLTP.

BEGIN
  DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP (
   GRANTEE_NAME   => 'SCOTT',
   CONSUMER_GROUP => 'OLTP',
   GRANT_OPTION   =>  TRUE);
END;
/

User SCOTT is also granted permission to grant switch privileges for OLTP to others.

If you grant permission to a role to switch to a particular resource consumer group, then any user who is granted that role and has enabled that role can switch his session to that consumer group.

If you grant PUBLIC the permission to switch to a particular consumer group, then any user can switch to that group.

If the GRANT_OPTION argument is TRUE, then users granted switch privilege for the consumer group can also grant switch privileges for that consumer group to others.

Revoking Switch Privileges

The following example revokes user SCOTT's privilege to switch to consumer group OLTP.

BEGIN
  DBMS_RESOURCE_MANAGER_PRIVS.REVOKE_SWITCH_CONSUMER_GROUP (
   REVOKEE_NAME   => 'SCOTT', 
   CONSUMER_GROUP => 'OLTP');
END;
/

If you revoke a user's switch privileges for a particular consumer group, any subsequent attempts by that user to switch to that consumer group, either manually or automatically through consumer group mapping rules, will fail. The user's session will then be automatically assigned to OTHER_GROUPS.

If you revoke from a role the switch privileges to a consumer group, any users who had switch privileges for the consumer group only through that role are no longer able to switch to that consumer group.

If you revoke switch privileges to a consumer group from PUBLIC, any users other than those who are explicitly assigned switch privileges either directly or through a role are no longer able to switch to that consumer group.