Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Sunday, November 10, 2013

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_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'

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

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;
OCT 28 02:00NOV 03 23:59
OCT 27 01:59OCT 27 23:59
OCT 21 14:34OCT 26 06:00
DEFAULT_PLANNOV 06 18:33NOV 06 18:34
DEFAULT_PLANNOV 06 18:04NOV 06 18:33
DEFAULT_PLANNOV 06 17:14NOV 06 18:04
NOV 04 02:00NOV 06 17:14

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';

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%';

5- Show the permissions for all consumer groups
select grantee, granted_group from DBA_RSRC_CONSUMER_GROUP_PRIVS
order by granted_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);

No comments: