Oracle Resource Manager (available since 8i) is one of the underestimated Oracle features, because it’s not commonly used among DBAs.

In this article I’ll try to explain it in a simple way to make you change your mind and start making use of it.

In short, it gives the DBA the power of controlling system resources i.e. (CPU) by specifying who has the proirity of using system resources at the time of resource starvation. This means if you don’t have CPU bottle necks throughout the day, you don’t need it!

Licensing:
Resource Manager comes with Enterprise Edition without extra cost.

Why DBA should use Resource Manager:
The following scenarios illustrate the benefit of using the Resource Manager:

– You want to control the resource consumed by users using tools like SQL Developer/TOAD. (i.e. limit the CPU consumption/ Cancel the query/ Kill the session/ Limit the used degree of parallelism).
– In case there is a resource contention occur at night and you have a very important ETL that should complete on time, and you want Oracle to give this job the highest priority.
– There is an important DB service being used by finance applications at the end of the month, and you don’t want other departments connecting via different services to compete the CPU resource with that finance service.
– You want the night RMAN full backup job to release the CPU resource for production applications in case a CPU contention happen at night.

How to use Resource Manager:
Configuring Resource Manager is a bit complicated, but if you follow the right sequence it will be a piece of cake.

Now it’s time to setup a Plan and its Consumer Groups and Map these consumer Groups to Consumers. Before getting confused with these terms, let me first start with an example simplifies them to you:

Imagine you have opened a new fried chicken chain called Zeko Fried Chickens ZFC:
You will start with putting a business plan that will boost the food production and get the customers served as quickly as possible [let’s refer to this plan as Resource Plan] the plan is as follows:
– You categorized the food production into 3 main meals [Spicy , Non-Spicy and Kids meal]. [let’s refer to the meals as Consumer Groups]- You categorize your customers into three categories (Adults like spicy food, Adults don’t like spicy food and kids). [Let’s refer to the customers as Consumers]
– When customers come in, they will order one of the above mentioned meals, each meal will be ready within 2 minutes. [Let’s refer to the process of delivering the meals to the customers as Plan Directive]

Congrats! if you understood the above example you understood the Resource Manager components;

Resource Plan specifies how much CPU will be shared with consumer groups, who will be assigned to these consumer groups, and the distribution of the rest of unused CPU if available.
Consumer Group defines how much CPU resource will be used by specific consumers.
Consumers are the ones who will utilize the resources allocated to the consumer group.
Plan Directive defines the criteria by which the Consumers will get assigned to Consumer Groups.

Next, let’s move to the creation of the Resource Manager components …

– First, create something called Pending Area, it’s kind of a temporary area Oracle  use to track/validate the changes to the resource manager to maintain the integrity of the Resource Manager configurations and avoid any miss up could happen. This Pending Area will be associated with your session only, at the end of setting up the Resource Manager you have to close/finish this Pending Area in order to validate and submit the changes. It’s similar of BEGIN & END in PL/SQL but this one is specific to Resource Manager.

Create a Plan:
EXEC  DBMS_RESOURCE_MANAGER.create_plan(‘prod_plan’, ‘Plan for high and low priority tasks.’);

Rollback: Delete Plan:
    — EXEC dbms_resource_manager.DELETE_PLAN_CASCADE (‘prod_plan’);

Create consumer groups:
EXEC  DBMS_RESOURCE_MANAGER.create_consumer_group(‘high_cg’,       ‘high priority free to utilize the cpu’);
EXEC  DBMS_RESOURCE_MANAGER.create_consumer_group(‘low_cg’,        ‘low priority’);
EXEC  DBMS_RESOURCE_MANAGER.create_consumer_group(‘conditioned_cg’,’low priority when met some conditions’);

Rollback: Delete Consumer Groups:
— EXEC DBMS_RESOURCE_MANAGER.delete_consumer_group(‘high_cg’);
— EXEC DBMS_RESOURCE_MANAGER.delete_consumer_group(‘low_cg’);
— EXEC DBMS_RESOURCE_MANAGER.delete_consumer_group(‘conditioned_cg’);

Create Plan Directive:

Plan Directive defines the resource limits for each Consumer group: [Here is the core idea of the Resource Manager, if you understand it, you understand the whole topic]

Note: MGMT_Px represent the CPU level, MGMT_P1 is the capacity of 100% of all CPUs (cores), whatever will remain unused (leftover) from MGMT_P1 it can be utilized by MGMT_P2 (level2), and whatever leftover from level2 can be used by MGMT_P3 and so on up to 8 levels. Which means if MGMT_P1 is utilizing 100% no CPU will remain left for MGMT_P2.

Ideally, all apps/services should have a share in level 1 (MGMT_P1) each share represent % of total CPU, all shares combined should not exceed 100%, This share doesn’t restrict the App/service of using 100% of the CPU if CPU is not utilized by other services (as long as the active running sessions is less than the number of CPUs), but it will maintain that at anytime the Apps/services will get their defined % share in case there are other sessions competing for CPU resources (CPU is busy).

OK, if all applications will be under level 1 then each application will have a defined share in the CPU, but what is the difference between the plan directive levels (MGMT_P1, MGMT_P2, …)?

The difference between plan directive levels is similar to the lion family, when they are not hungry, and meat is available, anyone of the lion family can eat peacfully from the meat (no CPU contention), but when they all become hungry and meat is not enough for all (CPU contention), here the rule will apply (Plan Directive), the lion will eat first till it get stomach full, then the lionesses and last the lion kids.

Note: There is a consumer group exist by default called “OTHER_GROUPS”, it MUST be part of any defined plan directive.

Note: To drop a consumer group you must start with dropping the lowest MGMT_Px consumer group.
Note: Plan directive can be modified (dropped/recreated) on the fly and it will reflect immediately on the current connected sessions once the pending area get submitted.

Define a separate plan directive for default “OTHER_GROUPS”: [80% o CPU in Level 1 – highest]
BEGIN
DBMS_RESOURCE_MANAGER.create_plan_directive (
plan             => ‘prod_plan’,
group_or_subplan => ‘OTHER_GROUPS’,
comment          => ‘high priority by default’,
MGMT_P1          => 80,
MGMT_P2          => 0);
End;
/

 
Rollback: Delete Plan Directive:
— EXEC DBMS_RESOURCE_MANAGER.DELETE_PLAN_DIRECTIVE (plan=>’prod_plan’, group_or_subplan=>’OTHER_GROUPS’);

 
Rollback: Delete Plan Directive:
— EXEC DBMS_RESOURCE_MANAGER.DELETE_PLAN_DIRECTIVE (plan=>’prod_plan’,group_or_subplan=>’low_cg’);

 

Define the Conditioned group: [the least priority but 100% of level 2]
BEGIN
DBMS_RESOURCE_MANAGER.create_plan_directive(
plan             => ‘prod_plan’,
group_or_subplan => ‘conditioned_cg’,
comment          => ‘if the utilization met specific parameter lower the priority’,
MGMT_P1          => 0,
MGMT_P2          => 100,
MGMT_P3          => 0,
— Switch to other group (with lower CPU)/Cancel Call/Kill Session if the switch conditions are met:
—  switch_group        => ‘low_cg’,

 
Rollback: Delete Plan Directive:
— EXEC DBMS_RESOURCE_MANAGER.DELETE_PLAN_DIRECTIVE (plan=>’prod_plan’,group_or_subplan=>’conditioned_cg’);

 

Validate/Apply the resource plan:
EXEC DBMS_RESOURCE_MANAGER.validate_pending_area;
EXEC DBMS_RESOURCE_MANAGER.submit_pending_area();

Map/Assign Users/Services/Modules/Machines to Consumer Groups:

Let’s suppose that we don’t want the reporting applications to compete with the other production Apps for the CPU resources, in this case we will assign the sessions connecting through the reporting application service and let’s say it’s name is reporting_svc, to conditioned_cg consumer group:

create a pending area: [temporary work area for Resource Management configuration until it get submitted]

EXEC  DBMS_RESOURCE_MANAGER.clear_pending_area();
EXEC  DBMS_RESOURCE_MANAGER.create_pending_area();

BEGIN
DBMS_RESOURCE_MANAGER.set_consumer_group_mapping (
  –attribute     => DBMS_RESOURCE_MANAGER.oracle_user,
–attribute     => DBMS_RESOURCE_MANAGER.module_name,
–attribute     => DBMS_RESOURCE_MANAGER.client_os_user,
–attribute     => DBMS_RESOURCE_MANAGER.client_program,
–attribute     => DBMS_RESOURCE_MANAGER.client_machine,
–attribute     => DBMS_RESOURCE_MANAGER.service_module,
attribute       => DBMS_RESOURCE_MANAGER.service_name,
value           => ‘reporting_svc‘,
consumer_group  => ‘conditioned_cg’);
END;
/

 

Validate/Apply the resource plan:
EXEC DBMS_RESOURCE_MANAGER.validate_pending_area;
EXEC DBMS_RESOURCE_MANAGER.submit_pending_area();

Grant permission to All users to use/switch to the created Consumer Groups:

EXEC dbms_resource_manager_privs.grant_switch_consumer_group(‘public’, ‘high_cg’, FALSE);
EXEC dbms_resource_manager_privs.grant_switch_consumer_group(‘public’, ‘low_cg’,  FALSE);
EXEC dbms_resource_manager_privs.grant_switch_consumer_group(‘public’, ‘conditioned_cg’, FALSE);

Activiate the Resource Plan:
SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = prod_plan SCOPE=BOTH SID=’*’;

Or:

 
Rollback: Reset the plan in RAC setup:
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = DEFAULT_PLAN;

Rollback: Reset the plan in a Standalone setup:
  ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ”;

To prevent a resource plan from being changed during such maintenance windows use FORCE keyword:

SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ‘FORCE:prod_plan’ SCOPE=BOTH SID=’*’;

The advantage of keeping default resource plan DEFAULT_PLAN on RAC protects PMON and LMS, from CPU starvation, SYS & SYSTEM sessions will have the highest priority, maintenance AUTO TASKS (i.e. Gather Statistics, SQL Tuning, Segment Advisor) will have the lowest priority.

Monitoring The Resource Manager:

View which Resource Plan is currently Active: [CPU should be ON]
SQL> select name, cpu_managed from v$rsrc_plan where is_top_plan = ‘TRUE’;  

View a Consumer Plan and its consumer Groups:
SQL> select group_or_subplan from dba_rsrc_plan_directives where plan = ‘prod_plan’;

View the History of activated Resource Plans:
SQL> select name, to_char(start_time, ‘MON DD HH24:MI’) start_time, to_char(end_time, ‘MON DD HH24:MI’) end_time from v$rsrc_plan_history order by start_time;

View the Consumer Groups Mapping Attributes:
SQL> select * from DBA_RSRC_GROUP_MAPPINGS order by consumer_group;

View the mapped Consumer Group to the connected sessions:
SQL> SELECT username,initial_rsrc_consumer_group FROM dba_users;

SQL> SELECT username,sid, resource_consumer_group FROM  v$session WHERE  SERVICE_NAME=’reporting_svc’;

— Display the Sessions and their current consumer group:
     col ORIGINAL_CONSUMER_GROUP for a30
select s.username,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, v$session s where r.orig_consumer_group_id = c.consumer_group_id and s.sid=r.sid;

— Display the Sessions that moved between consumer groups:
     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;

View the Consumer Groups:
col CONSUMER_GROUP     for a30
col CATEGORY        for a20
col COMMENTS        for a30
col CPU_METHOD        for a20
col MGMT_METHOD        for a20
col status        for a10
col MANDATORY        for a10
select CONSUMER_GROUP,status,mandatory,CPU_METHOD,MGMT_METHOD,CATEGORY,substr(COMMENTS,1,30) “COMMENTS” from DBA_RSRC_CONSUMER_GROUPS
where  consumer_group not like ‘%ORA$%’
and    CATEGORY=’OTHER’;

View the Consumer Group attributes:
SQL> select ATTRIBUTE,value,status from DBA_RSRC_GROUP_MAPPINGS where CONSUMER_GROUP=’CONDITIONED_CG’;

Consumer Groups CPU Consumption:
SQL> select to_char(begin_time, ‘HH:MI’) time, consumer_group_name,   60 * (select value from v$osstat where stat_name = ‘NUM_CPUS’) total_CPU,   60 * (select value from v$parameter where name = ‘cpu_count’) db_total, cpu_consumed_time / 1000 consumed, cpu_consumed_time / (select value from v$parameter where name =       ‘cpu_count’) / 600 %cpu_utilization, cpu_wait_time / 1000 throttled from v$rsrcmgrmetric_history  order by begin_time;

Note: The existance of wait event “resmgr:cpu quantum” represent the CPU throttling that caused by the Resource Manager when DB has a workload exceeds the CPU capacity.

References:
https://www.oracle.com/technetwork/database/performance/resource-manager-twp-133705.pdf
https://www.oracle.com/technetwork/articles/servers-storage-admin/o11-056-oracledb-rm-419380.pdf
https://oracle-base.com/articles/8i/resource-manager-8i
http://www.dba-oracle.com/t_consumer_groups.htm