Database Resource Manager Plans, Consumer Groups, Directives, and CPU/Parallel Limits

Learning outcomes

At 10:00, ServiceHub OLTP calls, a monthly billing batch, an analyst scan and a maintenance job all compete for the same CPU. The operating system sees Oracle processes, not business priority. Oracle Database Resource Manager converts business classes into database scheduling policy: sessions belong to resource consumer groups, a resource plan contains plan directives, and the active plan decides how contested CPU and other governed resources are shared or limited.

1. Resource Manager allocates to groups, not individual SQL text

A resource consumer group is a class of sessions with similar processing needs. A resource plan is the active policy container. Each plan directive connects one consumer group to the plan and specifies attributes such as CPU shares, active-session limits, maximum parallel degree, parallel-server pool percentage, undo/PGA limits or automatic switching.

The built-in OTHER_GROUPS consumer group catches sessions not explicitly represented by another leaf in the plan. Every top-level plan must contain it.

2. Resource Manager is not a Free feature

The current 26ai licensing matrix marks Database Resource Manager = N for Oracle AI Database Free and SE2-ODA; it is available in EE/EE-ES and specified cloud offerings. The following plan commands are therefore for an entitled deployment only.

SELECT name,value,ispdb_modifiableFROM v$parameterWHERE name IN (  'resource_manager_plan',  'resource_manager_cpu_scope',  'cpu_count',  'parallel_degree_policy',  'parallel_servers_target')ORDER BY name;SELECT  sid,  username,  service_name,  resource_consumer_groupFROM v$sessionWHERE type='USER'ORDER BY sid;

Seeing Resource Manager metadata/views or an internal maintenance plan does not create a license entitlement. Feature availability is determined by the current offering/license agreement.

3. Entitled setup begins in a pending area

The pending area is a staging copy of Resource Manager schema objects. It lets a DBA build/modify groups, mappings, plans and directives, validate them, and only then submit them to the data dictionary.

ALTER SESSION SET CONTAINER=FREEPDB1;BEGIN  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(    consumer_group => 'SH21_OLTP',    comment        => 'Latency-sensitive ServiceHub requests');  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(    consumer_group => 'SH21_BATCH',    comment        => 'Scheduled bulk work');  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(    consumer_group => 'SH21_ANALYTICS',    comment        => 'Interactive analytical/reporting work');  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(    consumer_group => 'SH21_MAINT',    comment        => 'Maintenance and controlled admin jobs');  DBMS_RESOURCE_MANAGER.CREATE_PLAN(    plan    => 'SH21_DAY_PLAN',    comment => 'ServiceHub daytime workload governance');END;/

4. Deliberately wrong: omit OTHER_GROUPS

BEGIN  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(    plan             => 'SH21_DAY_PLAN',    group_or_subplan => 'SH21_OLTP',    comment          => 'OLTP',    shares           => 8);  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();END;/-- Expected validation chain includes:-- ORA-29382: validation of pending area failed-- ORA-29377: consumer group ... is not part of top-plan ...-- / plan is missing required OTHER_GROUPS coverage.

The exact companion message depends on the invalid state, but the mechanism is deterministic: a top plan cannot leave unclassified sessions outside a leaf consumer group.

5. Repair with a complete share-based plan

Current Oracle documentation recommends shares rather than older CPU_P*/MGMT_P* percentage patterns for simple modern plans. Shares are relative weights when CPU is contested; they are not reservations of physical cores and do not force a group to consume its allocation when idle.

BEGIN  DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP('SH21_OLTP','OLTP');  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP('SH21_BATCH','Batch');  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP('SH21_ANALYTICS','Analytics');  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP('SH21_MAINT','Maintenance');  DBMS_RESOURCE_MANAGER.CREATE_PLAN(    plan    => 'SH21_DAY_PLAN',    comment => 'ServiceHub daytime plan');  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(    plan                     => 'SH21_DAY_PLAN',    group_or_subplan         => 'SH21_OLTP',    shares                   => 8,    parallel_degree_limit_p1 => 2,    parallel_server_limit    => 20);  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(    plan                     => 'SH21_DAY_PLAN',    group_or_subplan         => 'SH21_BATCH',    shares                   => 3,    parallel_degree_limit_p1 => 4,    parallel_server_limit    => 30);  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(    plan                     => 'SH21_DAY_PLAN',    group_or_subplan         => 'SH21_ANALYTICS',    shares                   => 2,    parallel_degree_limit_p1 => 8,    parallel_server_limit    => 40);  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(    plan                     => 'SH21_DAY_PLAN',    group_or_subplan         => 'SH21_MAINT',    shares                   => 1,    parallel_degree_limit_p1 => 2,    parallel_server_limit    => 20);  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(    plan             => 'SH21_DAY_PLAN',    group_or_subplan => 'OTHER_GROUPS',    shares           => 1);  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();END;/

The numerical shares/DOP/percentages are lab examples, not recommendations. Production values come from measured workload/SLO/capacity evidence.

6. Submit is not activate

SELECT plan,status,num_plan_directivesFROM dba_rsrc_plansWHERE plan='SH21_DAY_PLAN';SELECT  plan,  group_or_subplan,  mgmt_p1,  parallel_degree_limit_p1,  parallel_server_limit,  active_sess_pool_p1,  queueing_p1FROM dba_rsrc_plan_directivesWHERE plan='SH21_DAY_PLAN'ORDER BY group_or_subplan;

Submitting stores the valid plan. It does not activate a newly created plan. Activation is a separate operational change.

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN='SH21_DAY_PLAN';SELECT name,is_top_plan,cpu_managed,instance_cagingFROM v$rsrc_plan;SELECT name,valueFROM v$parameterWHERE name='resource_manager_plan';

7. Observe enforcement rather than assuming shares work

SELECT  name,  active_sessions,  execution_waiters,  requests,  cpu_wait_time,  consumed_cpu_timeFROM v$rsrc_consumer_groupORDER BY name;SELECT  sid,  current_consumer_group,  orig_consumer_group,  mapped_consumer_group,  stateFROM v$rsrc_session_infoORDER BY sid;

Consumer-group CPU wait time shows Resource Manager scheduling pressure; it does not prove application latency or business SLO compliance. Correlate with service/module/action/client metrics.

8. Free governance-model lab

Free cannot enforce the plan, but it can validate the policy model, relative priorities and missing-class coverage.

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE servicehub_governance_model PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE servicehub_governance_model (  workload_class VARCHAR2(20) PRIMARY KEY,  cpu_shares     NUMBER NOT NULL CHECK (cpu_shares > 0),  max_dop        NUMBER,  max_active     NUMBER,  queue_seconds  NUMBER,  service_name   VARCHAR2(64) NOT NULL,  objective      VARCHAR2(200) NOT NULL);INSERT INTO servicehub_governance_model VALUES ('OLTP',8,2,NULL,NULL,'servicehub_oltp','Protect request latency');INSERT INTO servicehub_governance_model VALUES ('BATCH',3,4,2,120,'servicehub_batch','Bound background throughput');INSERT INTO servicehub_governance_model VALUES ('ANALYTICS',2,8,2,120,'servicehub_analytics','Bound scan concurrency');INSERT INTO servicehub_governance_model VALUES ('MAINT',1,2,1,300,'servicehub_maint','Controlled operational work');INSERT INTO servicehub_governance_model VALUES ('OTHER',1,NULL,NULL,NULL,'servicehub_default','Default/fail-safe class');COMMIT;SELECT  workload_class,  cpu_shares,  ROUND(100*cpu_shares/SUM(cpu_shares) OVER(),1) AS share_pct_if_all_busy,  max_dop,  max_active,  queue_secondsFROM servicehub_governance_modelORDER BY cpu_shares DESC;

The percentage is only the relative share when all represented classes contend; it is not a guaranteed CPU reservation. The table is a design artifact, not database enforcement.

9. 26ai adds an optional server-wide CPU scope

RESOURCE_MANAGER_CPU_SCOPE is new in 26ai. Its default is INSTANCE_ONLY. SERVER_WIDE can govern CPU across multiple database instances on one supported Linux/Exadata server using Linux control groups. It is static, not PDB-modifiable, and is not a generic Free/Windows/non-Exadata feature.

10. Cleanup/rollback

DROP TABLE servicehub_governance_model PURGE;

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN='';-- Remove plan objects only after verifying they are not referenced/active.-- Use a pending area and DBMS_RESOURCE_MANAGER delete procedures.

11. Production judgment

Resource Manager is appropriate when one database must arbitrate competing workload classes under contention. It does not fix inefficient SQL or create CPU that does not exist. Start with service/workload telemetry, define the minimum policy, validate it, activate under controlled load, monitor wait/queue/cancel behavior and keep a tested empty/previous-plan rollback.

No COMPATIBLE increase or restart is required for normal instance-local plan switching. RESOURCE_MANAGER_PLAN is dynamic and PDB-modifiable. The 26ai SERVER_WIDE CPU mode is a separate static Exadata/Linux-specific capability. Lesson 2 classifies sessions into those groups using service/module/action identity rather than treating a database username as a workload class.

Authoritative references

• Managing Resources with Oracle AI Database Resource Manager — plans, groups, directives, pending area and monitoring
• DBMS_RESOURCE_MANAGER — current plan-directive API
• RESOURCE_MANAGER_PLAN — activation/PDB scope
• RESOURCE_MANAGER_CPU_SCOPE — 26ai server-wide CPU mode
• Licensing Information — Resource Manager/parallel offering matrix