Managing Resource Consumer Groups - Oracle 10g

Before you enable the Database Resource Manager, you must assign resource consumer groups to users. This can be done manually, or you can provide mappings that enable the database to automatically assign user sessions to consumer groups depending upon session attributes.

In addition to providing procedures to create, update, or delete the elements used by the Database Resource Manager, the DBMS _RESOURCE _MANAGER package contains procedures that effect the assigning of resource consumer groups to users. It also provides procedures that allow you to temporarily switch a user session to another consumer group.

The DBMS _RESOURCE _MANAGER _PRIVS package, described earlier for granting the Database Resource Manager system privilege, can also be used to grant the switch privilege to another user, who can then alter their own consumer group. Of the procedures discussed in this section, you use a pending area only for the SET _CONSUMER _GROUP _MAPPING and SET _CONSUMER _MAPPING _PRI procedures. These procedures are used for the automatic assigning of sessions to consumer groups.

This section contains the following topics:

  • Assigning an Initial Resource Consumer Group
  • Changing Resource Consumer Groups
  • Managing the Switch Privilege
  • Automatically Assigning Resource Consumer Groups to Sessions

Assigning an Initial Resource Consumer Group

The initial consumer group of a session is determined by mapping the attributes of the session to a consumer group.

Changing Resource Consumer Groups

There are two procedures, which are part of the DBMS _RESOURCE _MANAGER package, that allow administrators 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 a session of a user who is using excessive CPU, an administrator can instead change that user's consumer group to one that is allowed less CPU. Or, this switching can be enforced automatically, using automatic consumer group switching resource plan directives.

Switching a Session

The SWITCH _CONSUMMER _GROUP _FOR _SESS causes the specified session to immediately be moved into the specified resource consumer group. In effect, this statement can raise or lower priority. The following statement changes the resource consumer group of a specific session to a new consumer group. The session identifier (SID) is 17, the session serial number (SERIAL#) is 12345, and the session is to be changed to the high_priority consumer group.

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

Switching Sessions for a User

The SWITCH_CONSUMER_GROUP_FOR_USER procedure changes the resource consumer group for all sessions with a given user name.

Using the DBMS_SESSION Package to Switch Consumer Group

If granted the switch privilege, users can switch their current consumer group using the SWITCH _CURRENT _CONSUMER _GROUP procedure in the DBMS _SESSION package.

This procedure enables users to switch to a consumer group 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:

Using the DBMS_SESSION Package to Switch Consumer Group

The following example illustrates switching to a new consumer group. By printing the value of the output parameter old _group, we illustrate how the old consumer group name has been saved.

The DBMS_SESSION package can be used from within a PL/SQL application, thus allowing the application to change consumer groups, or effectivel priority, dynamically.

Managing the Switch Privilege

Using the DBMS _RESOURCE _MANAGER _PRIVS package, you can grant or revoke the switch privilege to a user, role, or PUBLIC. The switch privilege gives users the privilege to switch their current resource consumer group to a specified resource consumer group. The package also enables you to revoke the switch privilege. The actual switching is done by executing a procedure in the DBMS _SESSION package. A user who has been granted the switch privilege (or a procedure owned by that user) can use the SWITCH _CURRENT _CONSUMER _GROUP procedure to switch to another resource consumer group. The new group must be one to which the user has been specifically authorized to switch.

Granting the Switch Privilege

The following example grants the privilege to switch to a consumer group. User scott is granted the privilege to switch to consumer group bug _batch _group.

User scott is also granted permission to grant switch privileges for bug_batch_group to others. If you grant a user permission to switch to a particular consumer group, then that user can switch their current consumer group to the new consumer group. If you grant a role permission to switch to a particular resource consumer group, then any users who have been granted that role and have enabled that role can immediately switch their current consumer group to the new 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 bug _batch _group.

If you revoke a user's switch privileges to a particular consumer group, then any subsequent attempts by that user to switch to that consumer group will fail. If you revoke the initial consumer group from a user, then that user will automatically be part of the DEFAULT _CONSUMER _GROUP when logging in.

If you revoke from a role the switch privileges to a consumer group, then any users who only had switch privilege for the consumer group through that role will not be able to subsequently switch to that consumer group.

If you revoke switch privileges to a consumer group from PUBLIC, then any users other than those who are explicitly assigned switch privileges either directly or through PUBLIC, will not be able to subsequently switch to that consumer group.

Automatically Assigning Resource Consumer Groups to Sessions

You can configure the Database Resource Manager to automatically assign consumer groups to sessions by providing mappings between session attributes and consumer groups. Further, you can prioritize the mappings so as to indicate which mapping has precedence in case of conflicts.

There are two types of session attributes: login attributes and runtime attributes. The login attributes are meaningful only at session login time, when the Database 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 run-time attributes.

You use the SET _CONSUMER _GROUP _MAPPING and SET _CONSUMER _MAPPING _ PRI procedures to configure the automatic assigning of sessions to consumer groups. You must use a pending area for these procedures.

Creating Consumer Group Mappings

The SET _CONSUMER _GROUP _MAPPING procedure maps a session attribute to a consumer group. The parameters for this procedure are:

Creating Consumer Group Mappings

The attribute can be one of the following:

Attribute-Type-DescriptionAttribute-Type-Description

For each of the session attributes, you specify a mapping that consists of a set of pairs (attribute, consumer group)that determines how a session is matched to a consumer group. For example, the following statement causes user scott to map to the dev _group every time he logs in:

Creating Attribut Mapping Priorities

In order to decide between conflicting mappings, you can establish a priority ordering of the attributes from most important to least important. The priority of each attribute is set to a unique integer from 1 to 10 using the SET _CONSUMER _MAPPING _PRI procedure. For example, the following statement illustrates this setting of priorities:

To illustrate how mapping priorities work, assume that in addition to the mapping of scott to the dev group, there is also a module name mapping as follows:

EXEC DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING - (DBMS_RESOURCE_MANAGER.MODULE_NAME, 'backup', 'low priority');

In this example, the priority of the database user name is set to 4 (les important), while the priority of the module name is set to 3 (more important). Now if the session sets its module name to backup, presumably because it is going to perform a backup operation, then the session would be reassigned to the low priority consumer group, because that mapping has a higher priority.

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. This means that even though the attribute of a given session matches a mapping pair, the mapping is only considered valid if the session has the switching privilege for that particular consumer group. Sessions are automatically switched only to consumer groups for which they have been granted switch privileges.

Explicit Session Switching

A session can manually switch its own consumer group, given the privilege to do so, using the procedure DBMS _SESSION.SWITCH _CURRENT _CONSUMER _GROUP. Although there is no mapping for the EXPLICIT group setting, it is considered an attribute in the mapping priorities when deciding whether to use this EXPLICIT setting or use the consumer group mapping. In other words, if the EXPLICIT setting has the highest priority, then the session always switches consumer groups when it calls the SWITCH _CURRENT _CONSUMER _GROUP procedure.

On the other hand, if the EXPLICIT setting has the lowest priority, then the session only switches consumer groups if none of the other session attributes match in the mappings. Note that the Database Resource Manager considers a switch to have taken place even if the SWITCH _CURRENT _COSUMER _GROUP procedure is called to switch the session to the consumer group that it is already in.

Automatic Group Switching

Each session can be switched automatically to another consumer group via the mappings at distinct points in time:

  • When the session first logs in, the mapping is evaluated to determine the initial

    group of the session.

  • If the current mapping attribute of a session is A, then if the attribute A is set to

    a new value (only possible for runtime attributes) then the mapping is reevaluated and the session is switched to the appropriate consumer group.

  • If a runtime session attribute is modified such that the current mapping

    becomes a different attribute B, then the session is switched.

  • Whenever the client ID is set to a different value, the mapping is reevaluated

and the session is switched.
Two things to note about the preceding rules are:

  • If a runtime attribute for which a mapping is provided is set to the same value it

    already has, or if the client ID is set to the same value it already has, then 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 zero out session statistics that are typically zeroed out during a switch to a different group (for example, the ACTIVE _ TIME _IN _GROUP value of the session).


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 10g Topics