Configuring Resource Manager for Multiple Workloads

Resource Manager can be configured to manage workloads (OLTP,DSS etc) differently by configuring consumer groups and resource plans.

Consumer Group:

A consumer group is a collection of sessions that are managed as a unit. You can define consumer groups for each application in your database. Or you can define consumer groups for each type of workload, e.g. OLTP, reports, maintenance, etc.

Mapping Rules:

Sessions can be automatically mapped to a consumer group by defining consumer group  mapping rules. 

Resource Plan:

A resource plan specifies how the CPU should be shared among the consumer groups. It allocates percentages of the CPU to consumer groups and specifies how unused CPU is redistributed. A resource plan contains a resource plan directive for each consumer group that specifies its CPU allocation.

Steps to configure RM:

1- Obtain Permission to Administer Resource Manager.

This privilege is automatically granted to "SYS". for others user you can grant like below.
SQL> exec dbms_resource_manager_privs.grant_system_privilege( grantee_name => 'INAM',admin_option => true);
PL/SQL procedure successfully completed.

2-  Create a Pending Area
The pending area is a temporary work area for Resource Management configuration. The changes in the pending area are not visible until the pending area is submitted.
SQL> exec dbms_resource_manager.create_pending_area();
PL/SQL procedure successfully completed.

At any time, you can abandon the changes in the pending area as follows:
SQL> exec dbms_resource_manager.clear_pending_area();

3- Create Consumer Groups

The Oracle database comes preconfigured with multiple consumer groups like INTERACTIVE_GROUP (for OLTP), BATCH_GROUP (for batch operations) , SYS_GROUP (for system admins) , OTHER_GROUPS (for all sessions). If the default consumer groups do not describe your workloads, you can create your own ones. You can use the query  select * from dba_rsrc_consumer_groups; to find out existing consumer groups.

SQL> exec dbms_resource_manager.create_consumer_group( 'HOMEFIN_APP', 'Sessions for the HOME Finance Application');
PL/SQL procedure successfully completed.

4- Map Sessions to Consumer Groups

Mapping rules are used to automatically place sessions into a consumer group. For example, the following rule maps sessions in the "HOMEQANEW" service to the "HOMEFIN_APP" consumer group.

SQL> exec dbms_resource_manager.set_consumer_group_mapping( attribute => dbms_resource_manager.service_name, value => 'M
OHEQANEW', consumer_group => 'HOMEFIN_APP');
PL/SQL procedure successfully completed.

The session attributes that can be used by mapping rules are listed below

service_module_action: A combination of service name, module name, and action name, in this format:

service_name.module_name.action_name

service_module: A combination of service and module names in this format: service_name.module_name

module_name_action: A combination of module and action names in this format: module_name.action_name

module_name: The module name in the currently running application.

service_name: The service name used by the client to establish a connection.

oracle_user: The Oracle database user name.

client_program: The name of the client program used to log onto the server.

client_os_user: The operating system user name of the client.

client_machine: The name of the computer from which the client is making the connection.

client_id: The client identifier.

Sessions can be explicitly mapped to a consumer group using dbms_session.switch_consumer_group(). Sessions can also be configured to automatically switch from one consumer group to another, based on the amount of CPU or I/O consumed or the expected execution time, using the switch_time, switch_io_megabytes, switch_io_reqs, or switch_estimate directives.

SQL> exec dbms_resource_manager.submit_pending_area;

PL/SQL procedure successfully completed.

5-  Add Permissions for Consumer Groups

In order to switch into a consumer group, a user or role must have permission. The following

PL/SQL command allows the user "INAM" to switch into consumer group "HOMEFIN_APP".

SQL> exec dbms_resource_manager_privs.grant_switch_consumer_group( grantee_name => 'INAM', consumer_group => 'HOMEFIN_APP',grant_option => FALSE);

PL/SQL procedure successfully completed.

The following PL/SQL command allows any user to switch into the "HOMEFIN_APP" consumer group.

exec dbms_resource_manager_privs.grant_switch_consumer_group( grantee_name => 'public',consumer_group => 'HOMEFIN_APP', grant_option => FALSE);

6- Create a Resource Plan (Consumer groups are managed by a resource plan)

The Oracle database comes preconfigured with multiple resource plans. Before creating your own resource plans, you should consider if any of these resource plans meet your requirements.

DEFAULT_PLAN: general purpose plan that prioritizes SYS to allow the DBA to debug hangs and unexpected heavy loads.

MIXED_WORKLOAD_PLAN: Plan for managing mixed workloads (interactive and batch).

SQL> exec dbms_resource_manager.create_pending_area();

PL/SQL procedure successfully completed.

SQL> exec dbms_resource_manager.create_plan( 'HOMEFIN_APP_PLAN', 'Plan for HOME Finance Application');

PL/SQL procedure successfully completed.

7- Add Resource Plan Directives

A resource plan directive specifies how much CPU should be allocated to the consumer group. 

SQL> exec dbms_resource_manager.create_plan_directive( 'HOMEFIN_APP_PLAN','HOMEFIN_APP', 'Percentage of CPU for HOMEFIN_

APP consumer group', mgmt_p1 => 75);

PL/SQL procedure successfully completed.

The parameter, mgmt_p1, specifies the percentage of the CPU to allocate for the consumer

group. You are required to provide a directive for OTHER_GROUPS, the default consumer group.

SQL> exec dbms_resource_manager.create_plan_directive( 'HOMEFIN_APP_PLAN', 'OTHER_GROUPS', 'Percentage of CPU for OTHER_

GROUPS consumer group', mgmt_p1 => 25);

PL/SQL procedure successfully completed.

SQL> exec dbms_resource_manager.submit_pending_area();

PL/SQL procedure successfully completed.

8- Enable the Resource Plan

SQL> alter system set resource_manager_plan = 'HOMEFIN_APP_PLAN' sid='*';

System altered.

select group_or_subplan from dba_rsrc_plan_directives where plan = 'HOMEFIN_APP_PLAN'

GROUP_OR_SUBPLAN
HOMEFIN_APP
OTHER_GROUPS

Monitoring Resource Manager

1- If Resource Manager is currently enabled
select name, cpu_managed from v$rsrc_plan where is_top_plan = 'TRUE';

NAME	CPU_MANAGED
HOMEFIN_APP_PLAN	ON

2- History of Resource Manager usage
select name,to_char(start_time, 'MON DD HH24:MI') start_time,to_char(end_time, 'MON DD HH24:MI') end_time,window_name
from v$rsrc_plan_history order by start_time desc;

NAME	START_TIME	END_TIME	WINDOW_NAME
	OCT 28 02:00	NOV 03 23:59
DEFAULT_MAINTENANCE_PLAN	OCT 27 23:59	OCT 28 02:00	SUNDAY_WINDOW
	OCT 27 01:59	OCT 27 23:59
DEFAULT_MAINTENANCE_PLAN	OCT 26 06:00	OCT 27 01:59	SATURDAY_WINDOW
	OCT 21 14:34	OCT 26 06:00
HOMEFIN_APP_PLAN	NOV 06 18:34
DEFAULT_PLAN	NOV 06 18:33	NOV 06 18:34
DEFAULT_PLAN	NOV 06 18:04	NOV 06 18:33
DEFAULT_PLAN	NOV 06 17:14	NOV 06 18:04
	NOV 04 02:00	NOV 06 17:14
DEFAULT_MAINTENANCE_PLAN	NOV 03 23:59	NOV 04 02:00	SUNDAY_WINDOW

3- How Was Resource Manager Enabled, window_name indicates the scheduler window that automatically enabled the resource plan. If the window_name is NULL, then the resource plan was enabled using the
"resource_manager_plan" parameter
select window_name, resource_plan, active
from dba_scheduler_windows where resource_plan is not null and enabled = 'TRUE';

WINDOW_NAME	RESOURCE_PLAN	ACTIVE
MONDAY_WINDOW	DEFAULT_MAINTENANCE_PLAN	FALSE
TUESDAY_WINDOW	DEFAULT_MAINTENANCE_PLAN	FALSE
WEDNESDAY_WINDOW	DEFAULT_MAINTENANCE_PLAN	TRUE
THURSDAY_WINDOW	DEFAULT_MAINTENANCE_PLAN	FALSE
FRIDAY_WINDOW	DEFAULT_MAINTENANCE_PLAN	FALSE
SATURDAY_WINDOW	DEFAULT_MAINTENANCE_PLAN	FALSE
SUNDAY_WINDOW	DEFAULT_MAINTENANCE_PLAN	FALSE

If active is TRUE for a scheduler window, then that scheduler window is currently open and its resource plan is enabled.

4- Current consumer group for sessions
select sid, resource_consumer_group from v$session where resource_consumer_group like 'HOME%';

SID	RESOURCE_CONSUMER_GROUP
321	HOMEFIN_APP

5- Show the permissions for all consumer groups
select grantee, granted_group from DBA_RSRC_CONSUMER_GROUP_PRIVS
order by granted_group;

GRANTEE	GRANTED_GROUP
PUBLIC	DEFAULT_CONSUMER_GROUP
PUBLIC	LOW_GROUP
INAM	HOMEFIN_APP
SYSTEM	SYS_GROUP

6- Determine the consumer group with the mapping rules selected, the mapping attribute that was
used, and the consumer group that the session originally started in.
select r.sid, r.mapped_consumer_group, r.mapping_attribute,c.consumer_group original_consumer_group
from v$rsrc_session_info r, dba_rsrc_consumer_groups c
where r.orig_consumer_group_id = c.consumer_group_id;

7- Detect sessions that have been automatically switched
select r.sid,c1.consumer_group original_consumer_group,c2.consumer_group current_consumer_group
from v$rsrc_session_info r, dba_rsrc_consumer_groups c1,dba_rsrc_consumer_groups c2
where r.orig_consumer_group_id = c1.consumer_group_id
and r.current_consumer_group_id = c2.consumer_group_id
and r.orig_consumer_group_id != r.current_consumer_group_id;

8- Shows if the current resource plan has any auto-switching directives
select plan, group_or_subplan, switch_time, switch_io_megabytes, switch_io_reqs
from dba_rsrc_plan_directives
where plan in (select name from v$rsrc_plan)
and (switch_time is not null
or switch_io_megabytes is not null
or switch_io_reqs is not null);