Resource Manager: Share CPU Between Workloads

Oracle DBA Certification 13 min min read Updated: Nov 16, 2026 Advanced
Resource Manager: Share CPU Between Workloads
Advanced Topic 2 of 4

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

PieceMeaning
Consumer groupA set of sessions treated alike, for example REPORT_GRP
Resource planA named set of rules for how resources are shared
Plan directiveA 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.

Get Newsletter

Subscibe to our newsletter and we will notify you about the newest updates on Edugators