Skip Headers
Oracle® Database Administrator's Guide
11g Release 1 (11.1)

Part Number B28310-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

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 explicitly assigned an initial consumer group are placed in the consumer group DEFAULT_CONSUMER_GROUP.

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". If no mapping rule applies for a new session, the default consumer group for the session is specified by the INITIAL_RSRC_CONSUMER_GROUP attribute of the user who started the session. You can view the value of this attribute by viewing the INITIAL_RSRC_CONSUMER_GROUP column in the *_USER views.

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 SCOTT to the LOW_GROUP consumer group:

BEGIN
DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER ('SCOTT',
    'LOW_GROUP'); 
END;

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, the session-to–consumer group mapping rules are reevaluated, and if a new rule takes effect, 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

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 do so by specifying 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. The 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.

SWITCH_FOR_CALL is useful for three-tier applications where the middle tier server is using session pooling. A top call in PL/SQL is an entire PL/SQL block treated as one call. A top call in SQL is an individual SQL statement.

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;

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 runtime attributes. The login attributes are meaningful only at session login time, when the Resource Manager determines the initial consumer group of the session. In contrast, a session that has already logged in can later be reassigned to another consumer group based on its changing run-time attributes.

You use the SET_CONSUMER_GROUP_MAPPING and SET_CONSUMER_GROUP_MAPPING_PRI procedures to configure the automatic assigning 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 runtime attributes), the mapping rules are reevaluated and the session might be switched to another consumer group.

Two things to note about the preceding rules are the following:

  • If a runtime attribute for which a mapping rule is provided is set to the same value that it already has, no switching takes place.

  • A session can be switched to the same consumer group it is already in. The effect of switching in this case is to initialize to zero the session statistics that are typically initialized during a switch to a different group. An example of such a statistic is the ACTIVE_TIME_IN_GROUP value of the session.

Creating Consumer Group Mapping Rules

The mapping rules for a session consist of a set of attribute/consumer group pairs that determine how a session is matched to a consumer group. You use the SET_CONSUMER_GROUP_MAPPING procedure to map a single session attribute to a consumer group. The parameters for this procedure are the following:

Parameter Description
ATTRIBUTE The login or runtime session attribute type, specified as a package constant
VALUE The value of the attribute being mapped
CONSUMER_GROUP The consumer group to map to

ATTRIBUTE can be one of the following:

Attribute Type Description
ORACLE_USER Login The Oracle Database user name
SERVICE_NAME Login The 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
MODULE_NAME Runtime 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 Runtime 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 Runtime A combination of service and module names in this form: service_name.module_name
SERVICE_MODULE_ACTION Runtime A combination of service name, module name, and action name, in this form: service_name.module_name.action_name

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.

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 10 (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);
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, 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, 'BACKUP', 'LOW_PRIORITY');
END;

Now if user SCOTT's session sets its module name to BACKUP, 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.

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('OLTP', old_group, FALSE);
DBMS_OUTPUT.PUT_LINE('OLD GROUP = ' || old_group);
END;
/

The following line is output:

OLD GROUP = DEFAULT_CONSUMER_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

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.

DEFAULT_CONSUMER_GROUP 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 fail. If you revoke the switch privilege for the initial consumer group from a user, that user is automatically assigned to the DEFAULT_CONSUMER_GROUP upon login.

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.