Resource Manager: Share CPU Between Workloads
The Problem
One person runs a huge report during business hours and the order-entry application slows to a crawl. Resource Manager lets you share CPU and other resources fairly between groups of users.
Simple example: A restaurant kitchen gives the dine-in customers priority and lets the catering orders use what is left. Without such rules, one big catering order would stop everyone else.
The Three Pieces
| Piece | Meaning |
|---|---|
| Consumer group | A set of sessions treated alike, for example REPORT_GRP |
| Resource plan | A named set of rules for how resources are shared |
| Plan directive | A rule: this group gets this much CPU, limited to this parallelism |
Create a Plan
sql
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP('REPORT_GRP', 'Reports and analysts');
DBMS_RESOURCE_MANAGER.CREATE_PLAN('DAY_PLAN', 'Business hours plan');
-- reports get at most 20% of CPU when there is competition
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
plan => 'DAY_PLAN', group_or_subplan => 'REPORT_GRP',
comment => 'Limit reports', mgmt_p1 => 20,
parallel_degree_limit_p1 => 2);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
plan => 'DAY_PLAN', group_or_subplan => 'OTHER_GROUPS',
comment => 'Everyone else', mgmt_p1 => 80);
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
END;
/
Always include OTHER_GROUPS: Every plan must have a directive for
OTHER_GROUPS, which covers everybody not mapped to a group. The plan is invalid without it.
Map Users to the Group
sql
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
attribute => DBMS_RESOURCE_MANAGER.ORACLE_USER,
value => 'REPORT_USER',
consumer_group => 'REPORT_GRP');
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
END;
/
BEGIN
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
grantee_name => 'REPORT_USER', consumer_group => 'REPORT_GRP', grant_option => FALSE);
END;
/
Activate the Plan
sql
ALTER SYSTEM SET resource_manager_plan = 'DAY_PLAN' SCOPE = BOTH;
Check What is Happening
sql
SELECT username, resource_consumer_group, COUNT(*)
FROM v$session
WHERE type = 'USER'
GROUP BY username, resource_consumer_group;
SELECT name, cpu_wait_time, consumed_cpu_time
FROM v$rsrc_consumer_group;
When does it matter? Resource Manager limits only matter when the machine is busy. If there is spare CPU, any group can use it. The rules apply only when groups compete.
Other Things You Can Limit
- Maximum parallel degree
- Maximum session idle time
- Runaway queries: switch or cancel a SQL that runs longer than a limit
- I/O on Exadata
Conclusion
Resource Manager protects important workloads by giving each consumer group a fair share of CPU and other limits. Always include OTHER_GROUPS and map users carefully.
Quick Summary: Consumer groups + plan + directives, map users, activate with resource_manager_plan.

