|Oracle® Database Administrator's Guide
11g Release 2 (11.2)
Part Number E17120-05
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
This section includes the following topics:
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.
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
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.
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; /
SID, session serial number, and current resource consumer group for a session are viewable using the
See Also:Oracle Database Reference for details about 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; /
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:
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.
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.
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, 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:
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; /
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; /
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; /
See Also:"Creating Resource Plan Directives"
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:
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. Runtime 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 runtime attributes.
You use the
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 runtime attributes), the mapping rules are reevaluated and the session might be switched to another consumer group.
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.
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
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
SET_CONSUMER_GROUP_MAPPING procedure to modify or delete any of these predefined 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:
||The session attribute type, specified as a package constant|
||The value of the attribute|
||The consumer group to map to for this attribute/value pair|
ATTRIBUTE can be one of the following:
||Login||The Oracle Database user name|
||Login||The database service name used by the client to establish a connection|
||Login||The operating system user name of the client that is logging in|
||Login||The name of the client program used to log in to the server|
||Login||The name of the computer from which the client is making the connection|
||Runtime||The module name in the currently running application as set by the
||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:
The attribute is specified as the module name followed by a period (.), followed by the action name (
||Runtime||A combination of service and module names in this form:
||Runtime||A combination of service name, module name, and action name, in this form:
||Runtime||An RMAN or Data Pump operation. Valid values are
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
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.
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).
SET_CONSUMER_GROUP_MAPPING_PRIrequires that you include the pseudo-attribute
EXPLICITas 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:
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:Oracle Database PL/SQL Packages and Types Reference for information about setting the module name with the
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.
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 consumer group to which the user is switching.|
||Returns the name of the consumer group from which the user switched. Can be used to switch back later.|
||Controls behavior if a switching error occurs.
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_SESSIONpackage 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_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.
||Grants permission to a user, role, or
||Revokes permission for a user, role, or
The following example grants user
SCOTT the privilege to switch to consumer group
BEGIN DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ( GRANTEE_NAME => 'SCOTT', CONSUMER_GROUP => 'OLTP', GRANT_OPTION => TRUE); END; /
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.
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.
The following example revokes user
SCOTT's privilege to switch to consumer group
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.