11.3. Resource Consumer Groups
Resource consumer groups represent the next step in defining a DRM strategy. They allow you to classify users into logical groupings based on resource consumption requirements or business needs.
There are two ways to define resource consumer groups:
A resource consumer group is a method of classifying users based on their resource consumption tendencies or requirements.
A resource consumer group is a method of prioritizing database resource usage by classifying users based on business needs.
For instance, in most companies, payroll users tend to have high priority, because no one wants their paychecks to be late. Therefore, a resource consumer group named PAYROLL can be created and all payroll users assigned to it.
This does not imply anything about the resource consumption tendencies or the requirements of payroll users. Instead, it identifies them based on a business need.
On the other hand, a group of inexperienced users may have a habit of executing queries without first checking join conditions. Their resultant Cartesian products tend to run for hours, wasting system resources. For these users, a group named NOVICE could be created. This group could then have resource limitations placed upon it.
In this situation, group classification is directly related to the consumption tendencies of the users.
In either situation, users can be assigned to one or more resource consumer groups, although each active session can be assigned to only one resource consumer group at a time. A user may, for example, use the system in an online transaction processing (OLTP) capacity for part of the day, perhaps entering orders or doing account maintenance. The rest of the day, the same user may switch to an online analytical processing (OLAP) capacity, running reports and statistical queries. This user could be a member of both the OLTP and OLAP resource consumer groups, but could be assigned to only one for any session.
The following sections explain how to manage resource consumer groups using PL/SQL packages.
11.3.1. Managing Resource Consumer Groups
Resource consumer groups can be managed by using the DBMS_RESOURCE_MANAGER PL/SQL package. This package offers procedures that allow the creation, deletion, and modification of resource consumer groups. It also provides functionality for assigning users to groups and switching the group for user sessions.
In the next few sections, you'll learn how to add, modify, and delete resource consumer groups using the DBMS_RESOURCE_MANAGER package. You will also learn to assign users to groups as well as how to switch user sessions between groups.
NOTE
You must have the ADMINISTER_RESOURCE_MANAGER system privilege to administer Database Resource Manager. This privilege is granted by default to the DBA role.
11.3.1.1. Creating Resource Consumer Groups
To create a new resource consumer group, use the DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP procedure. All that is required when defining a new group is a unique name and a description. It is not necessary (nor possible) to define how this group will be used at this point.
There are three parameters that you can specify when creating a resource consumer group. Table 11.1 describes these parameters.
The CPU_MTH parameter defines the resource scheduling method used between sessions within a resource group. This method governs only CPU resources between group members.
Be aware that a CPU allocation method can also be defined at the plan level. Therefore, the total CPU available to the resource group may already have been limited by the active resource plan.
To create a new resource consumer group named DEVELOPERS that uses a round-robin CPU methodology, see the following example:
SQL> begin
dbms_resource_manager.create_consumer_group('developers',
'application developers'),
end;
SQL>/
PL/SQL procedure successfully completed.To verify that the command succeeded, you can use the DBA_RSRC_CONSUMER_GROUPS view:
SQL> select consumer_group, cpu_method, comments from dba_rsrc_consumer_groups where consumer_group = 'DEVELOPERS'; CONSUMER_GROUP CPU_METHOD COMMENTS --------------- --------------- ------------------------ DEVELOPERS ROUND-ROBIN application developers
By default, there are four resource consumer groups predefined in the database. They are defined in Table 11.2.
As you can see in the description, all users who are not assigned to a group will become part of the DEFAULT_CONSUMER_GROUP group. And users who are not assigned to a group in the currently active plan will be assigned to the OTHER_GROUPS group.
The remaining two groups, SYS_GROUP and LOW_GROUP, were defined to support a predefined resource plan provided by Oracle.
11.3.1.2. Updating Resource Consumer Groups
Consumer resource groups can be updated using the DBMS_RESOURCE_MANAGER.UPDATE_CONSUMER_GROUP procedure. This procedure allows you to change the comment and/or the CPU allocation method for a particular group. Table 11.3 describes the parameters for the DBMS_RESOURCE_MANAGER.UPDATE_CONSUMER_GROUP procedure.
For instance, to change the CPU allocation method for the DEVELOPERS group, the following SQL could be used:
SQL> begin dbms_resource_manager.update_consumer_group( CONSUMER_GROUP => 'DEVELOPERS', NEW_CPU_MTH => 'RUN-TO-COMPLETION'), end; SQL> / PL/SQL procedure successfully completed.
| Parameter | Description |
|---|---|
| CONSUMER_GROUP | The name of consumer group |
| NEW_COMMENT | Updated comment |
| NEW_CPU_MTH | Updated method for CPU resource allocation |
As you can see in this example, the NEW_COMMENT parameter was omitted because no change was being made to it. By the same token, the NEW_CPU_MTH parameter could be omitted if only the comment was being updated.
11.3.1.3. Deleting a Resource Consumer Group
Consumer resource groups can be deleted using the DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP procedure. Deleting a resource group has a couple of implications that are important to understand:
Users assigned to the deleted group as their initial consumer resource group will be assigned to the DEFAULT_CONSUMER_GROUP group.
Current sessions assigned to the deleted group will be switched to the DEFAULT_CONSUMER_GROUP group.
NOTE
Don't worry if you don't understand the implications of these changes right now. They will be made clear as this chapter progresses.
Table 11.4 shows the single parameter required by the DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP procedure.
| Parameter | Description |
|---|---|
| CONSUMER_GROUP | The name of the consumer group to be deleted |
Only the name of the group to be deleted needs to be passed to the procedure, as you can see in this example:
SQL> begin
dbms_resource_manager.delete_consumer_group('DEVELOPERS'),
end;
SQL>/
PL/SQL procedure successfully completed.The DEVELOPERS group should now be deleted from the system.
11.3.1.4. Assigning User Sessions to Consumer Groups
Creating resource consumer groups is only half the battle. You still need a method of assigning consumer groups to user sessions. DRM can be configured to automatically assign consumer groups to sessions based on specific session attributes. This process is called consumer group mapping. In the following sections, you will learn how to create consumer group mappings. You will also learn how to set priorities so DRM knows which mapping has precedence in case of conflicts.
11.3.1.4.1. Creating Consumer Group Mappings
Consumer group mappings can be created by using the DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING procedure. This procedure allows you to map sessions to consumer groups based on login or runtime session attributes. Table 11.5 shows the available attributes that can be mapped.
| Attribute | Type | Description |
|---|---|---|
| CLIENT_OS_USER | Login | Operating system username. |
| CLIENT_PROGRAM | Login | Name of the client program. |
| CLIENT_MACHINE | Login | Name of machine from which the client is logging in. |
| MODULE_NAME | Runtime | Module name that is currently executing, as defined by the DBMS_APPLICATION_INFO.SET_MODULE_NAME procedure. |
| MODULE_NAME_ACTION | Runtime | Module name and module action that are currently executing, as defined by the DBMS_APPLICATION_INFO.SET_MODULE_NAME/SET_ACTION procedures. Attribute is specified in the format SERVICE_NAME.ACTION. |
| ORACLE_USER | Login | Oracle username. |
| SERVICE_NAME | Login | Client service name used to establish a login. |
| SERVICE_MODULE | Runtime | Service name and module name in the format SERVICE_NAME.MODULE_NAME. |
| SERVICE_MODULE_ACTION | Runtime | Service name, module name, and action name in the format SERVICE_NAME.MODULE_NAME.ACTION_NAME. |
Mappings simply define a session attribute, a value for the attribute, and a consumer group. For example, if the session attribute CLIENT_OS_USER has a value of graciej, then assign the OLAP_GROUP to the session. The following code would create this mapping:
SQL> begin dbms_resource_manager.set_consumer_group_mapping(ATTRIBUTE => CLIENT_OS_USER, VALUE => 'graciej', CONSUMER_GROUP => 'OLAP_GROUP'), end; SQL>/ PL/SQL procedure successfully completed.
NOTE
Note that session attributes are defined as Oracle constants and are therefore specified without surrounding single quotes.
11.3.1.4.2. Establishing Mapping Priorities
It is possible that a session may map to more than one consumer group based on mapping rules. Therefore, Oracle allows the creation of mapping priorities through the use of the DBMS_RESOURCE_MANAGER.SET_MAPPING_PRIORITY procedure, as follows:
SQL> begin
dbms_resource_manager.set_mapping_priority(
EXPLICIT => 1,
CLIENT_OS_USER => 2,
CLIENT_MACHINE => 3,
CLIENT_PROGRAM => 4,
ORACLE_USER => 5,
MODULE_NAME => 6,
MODULE_NAME_ACTION => 7,
SERVICE_NAME => 8,
SERVICE_MODULE => 9,
SERVICE_MODULE_ACTION => 10);
end;
SQL>/
PL/SQL procedure successfully completed.The priorities defined in the SET_MAPPING_PRIORITY procedure are used to resolve any conflicting mapping rules. The EXPLICIT attribute refers to an explicit consumer group switch (using one of the switching methods described in the next section).
11.3.1.5. Changing Resource Consumer Groups
Two procedures are provided in the DBMS_RESOURCE_MANAGER package to allow you to explicitly change consumer groups for currently active user sessions: the SWITCH_CONSUMER_GROUP_FOR_SESS and SWITCH_CONSUMER_GROUP_FOR_USER procedures.
In addition, users can be granted the privilege to change their own consumer group. When a user is granted the switch privilege, they can use the DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP procedure to change the consumer group for their current session.
In the following sections, you will learn how to use each of these methods to explicitly change consumer groups. You will also learn how to grant and revoke the switch privilege.
11.3.1.5.1. Switching Groups using DBMS_RESOURCE_MANAGER Procedures
The first procedure, SWITCH_CONSUMER_GROUP_FOR_SESS, explicitly assigns an active session to a new consumer group. The session is identified by the session identifier (SID) and serial number (SERIAL#), both of which can be derived from the V$SESSION table. This procedure is described in Table 11.6.
For example, the following SQL switches a session to the LOW_GROUP group:
SQL> begin
dbms_resource_manager.switch_consumer_group_for_sess (
SESSION_ID => '56',
SESSION_SERIAL=> '106',
CONSUMER_GROUP => 'LOW_GROUP'),
end;
SQL>/
PL/SQL procedure successfully completed.| Parameter | Description |
|---|---|
| SESSION_ID | Session identifier (SID column from the view V$SESSION) |
| SESSION_SERIAL | Serial number of the session (SERIAL# column from view V$SESSION) |
| CONSUMER_GROUP | The name of the target consumer group |
The second method of changing the consumer group for an active session is the SWITCH_CONSUMER_GROUP_FOR_USER procedure. This procedure changes all active sessions for a given Oracle username. Here's an example:
SQL> begin
dbms_resource_manager.switch_consumer_group_for_user (
USER => 'BRANDON',
CONSUMER_GROUP => 'LOW_GROUP'),
end;
SQL>/
PL/SQL procedure successfully completed.This procedure identifies all sessions running under the username of BRANDON and switches them to the LOW_GROUP.
Both of these procedures also switch all parallel sessions that may have been spawned by the session or user.
NOTE
Explicit consumer group changes are not persistent. They affect only current sessions.
11.3.1.5.2. Switching Groups Using DBMS SESSION
If a user has been granted the switch privilege, they can use the DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP procedure to explicitly change the group for their current session. Table 11.7 describes the parameters for this procedure.
| Parameter | Description |
|---|---|
| NEW_CONSUMER_GROUP | The name of the consumer group to which the session is switching. |
| OLD_CONSUMER_GROUP | An output parameter that returns the name of the original consumer group (before the switch). |
| INITIAL_GROUP_ON_ERROR | If the switch fails, this parameter controls the outcome. If TRUE, the session reverts to its original group. If FALSE, an error is raised. |
When this procedure completes, it returns the name of the original consumer group back to the calling program. This is presumably so the program can retain the original group and use it to revert back later in the program, if so desired. The following example shows how to call this procedure from a PL/SQL block:
DECLARE original_group varchar2(30); junk varchar2(30); BEGIN DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP( 'MARKETING', original_group, FALSE); < execute some SQL> DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP( original_group, junk, FALSE); END;
This PL/SQL block switches from the current consumer group to the MARKETING group and saves the original group name in a variable named ORIGINAL_GROUP. After executing some SQL, it uses the ORIGINAL_GROUP variable to switch back to the original group.
11.3.1.5.3. Managing the Switch Privilege
Before a user can switch their own consumer group, they must have been granted the switch privilege directly, or be granted a role that has been granted the switch privilege. The switch privilege is granted to users and/or to roles through the DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP procedure. The parameters for this procedure are described in Table 11.8.
By granting the switch privilege to roles, it is much easier to grant the privilege to entire groups of users, as shown here:
SQL> begin dbms_resource_manager_privs.grant_switch_consumer_group( 'PROG_ROLE', 'DEVELOPERS', FALSE); end; SQL>/ PL/SQL procedure successfully completed.
In this example, the switch privilege is granted to the PROG_ROLE role. Any user granted that role will be able to switch to the DEVELOPERS group, but they cannot grant the privilege to any other users. If the GRANT_OPTION parameter was set to TRUE, the user could, in turn, grant the same privilege to another user.
NOTE
If the switch privilege is granted to PUBLIC for any consumer group, any user will be able to switch to the specified consumer group.
The switch privilege can also be revoked by using the DBMS_RESOURCE_MANAGER_PRIVS.REVOKE_SWITCH_CONSUMER_GROUP procedure. The parameters for this procedure are described in Table 11.9.
This procedure revokes a user or role's privilege to switch to the specified consumer group. Here's an example:
SQL> begin
dbms_resource_manager_privs.revoke_switch_consumer_group(
'PROG_ROLE', 'DEVELOPERS'),
end;
SQL>/
PL/SQL procedure successfully completed.This example revokes the privileges granted in the preceding example.
| Parameter | Description |
|---|---|
| REVOKEE_NAME | Name of user or role with privileges being revoked |
| CONSUMER_GROUP | Name of consumer group being revoked |