Create and Use a Resource Manager Plan in Oracle 26ai

Define the Workload Policy

The plan is named ESALES_PLAN. Sessions belonging to the existing application account PETSTORE will enter WEBUSER; sessions for REPORT_USER will enter MANAGER. These consumer-group names preserve the earlier example, although MANAGER represents reporting work here rather than a database administration role.

Shares express relative entitlement, not a CPU ceiling. The weights 7:2:1 carry forward the intent of the previous lesson's 70/20/10 illustration. When groups compete for CPU, their weights influence allocation, subject to other limits. They do not reserve physical cores or require every measurement to show a fixed percentage. Unused capacity can be available to other groups.

The reporting UTILIZATION_LIMIT of 40 is a separate ceiling introduced in this lesson. It can restrict reporting even when more CPU would otherwise be available. Interpret it within the applicable PDB and CDB configuration, not as an unconditional 40% allocation of the entire physical host. The example values are teaching choices, not recommendations for sizing a production system.

The two concurrency controls also have different meanings. ACTIVE_SESS_POOL_P1 limits concurrent active calls in a group; it does not limit how many connections can remain logged in. PARALLEL_DEGREE_LIMIT_P1 limits an operation's degree of parallelism (DOP). A DOP limit of four neither means four connected sessions nor guarantees that an operation will run in parallel.

Prepare an Isolated Test PDB

Use a writable, self-managed test PDB separated from production traffic. Connect as an administrator with ADMINISTER_RESOURCE_MANAGER, authority to grant consumer-group switching privileges, ALTER SYSTEM authority for activation, and access to the catalog views used below. Application accounts should remain ordinary users; they do not need DBA privileges for this exercise.

• Ensure PETSTORE and REPORT_USER already exist as local accounts, can log in, and have appropriate test data.
• Confirm that ESALES_PLAN, WEBUSER, and MANAGER are unused names. If definitions already exist, inspect them and adapt the exercise rather than deleting them.
• Coordinate Resource Manager changes so another administrator is not using the pending area.
• Record the existing plan setting and inspect mappings and their priorities before changing anything.
• Ask the CDB administrator to confirm the CDB resource policy needed for PDB-level enforcement.

The final point matters because a PDB operates within a container database. A PDB plan divides resources among its workloads, while a CDB plan governs allocation among PDBs. PDB CPU policies can interact with automatic activation of DEFAULT_CDB_PLAN when no CDB plan is specified. Do not assume every effect is confined to the PDB or make an unreviewed root-level change to complete this exercise.

This lesson does not configure Autonomous service policies or host-wide CPU management. It requires neither SERVER_WIDE CPU scope nor Linux cgroup changes. Use the service-specific administration approach for an Autonomous environment instead of replacing its managed resource plan with this lab.

1. Inspect the Current Configuration

Run these commands in SQL*Plus while connected to the intended PDB. Save the actual RESOURCE_MANAGER_PLAN value outside the script so you can restore it afterward.

-- SQL*Plus commands:
SHOW CON_NAME
SHOW PARAMETER resource_manager_plan

SELECT plan, status
FROM dba_rsrc_plans
WHERE plan = 'ESALES_PLAN';

SELECT consumer_group
FROM dba_rsrc_consumer_groups
WHERE consumer_group IN ('WEBUSER', 'MANAGER');

SELECT * FROM dba_rsrc_group_mappings;

SELECT attribute, priority
FROM dba_rsrc_mapping_priority
ORDER BY priority;

A matching plan or consumer-group name is a reason to stop and investigate. It is not permission to overwrite a previous lesson's work. Similarly, existing mapping rules may already classify these application accounts. Record relevant values before deciding whether a change is appropriate.

Mapping priority is part of the policy. A higher-priority matching rule can determine a session's consumer group instead of the username mapping introduced below. The example assumes no competing rule overrides those mappings. Do not reset all priorities merely to obtain the expected laboratory result.

2. Build, Validate, and Submit the Plan

Create a pending area to stage the definitions, create two consumer groups, and attach three directives to the plan. OTHER_GROUPS already exists as a built-in group. The plan needs a directive for that fallback; the script does not create it as a new group.

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;

  DBMS_RESOURCE_MANAGER.CREATE_PLAN(
    plan    => 'ESALES_PLAN',
    comment => 'Teaching plan for web and reporting workloads');

  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
    consumer_group => 'WEBUSER',
    comment        => 'Interactive web workload');

  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
    consumer_group => 'MANAGER',
    comment        => 'Reporting workload');

  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan                     => 'ESALES_PLAN',
    group_or_subplan         => 'WEBUSER',
    comment                  => 'Favor web work under contention',
    shares                   => 7,
    parallel_degree_limit_p1 => 1);

  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan                     => 'ESALES_PLAN',
    group_or_subplan         => 'MANAGER',
    comment                  => 'Bound reporting CPU and concurrency',
    shares                   => 2,
    utilization_limit        => 40,
    active_sess_pool_p1      => 4,
    parallel_degree_limit_p1 => 4);

  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan                     => 'ESALES_PLAN',
    group_or_subplan         => 'OTHER_GROUPS',
    comment                  => 'Required fallback policy',
    shares                   => 1,
    parallel_degree_limit_p1 => 1);

  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
END;
/

CREATE_PLAN defines the policy container. Each CREATE_PLAN_DIRECTIVE then connects a consumer group to that plan and specifies its allocation or limits. This PDB example uses a single level without subplans. It does not carry forward the legacy eight-level CPU allocation example.

VALIDATE_PENDING_AREA checks the staged configuration for consistency. SUBMIT_PENDING_AREA submits the definitions. Submitting this new plan does not activate it. A successful block establishes configuration, but application sessions are not yet shown to be running under the intended policy.

If a call fails, stop before executing later stages. Inspect the error and correct the pending definitions deliberately. Do not hide errors or assume an ordinary transaction rollback reverses the entire configuration lifecycle. Clearing a pending area discards unsubmitted work and should only be done when that is intended, not as a routine preamble to every attempt.

3. Grant Consumer-Group Switching Privileges

After the groups have been submitted, authorize each application account to use its intended group. These are consumer-group switching privileges, not Resource Manager administration privileges.

BEGIN
  DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
    grantee_name   => 'PETSTORE',
    consumer_group => 'WEBUSER',
    grant_option   => FALSE);

  DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
    grantee_name   => 'REPORT_USER',
    consumer_group => 'MANAGER',
    grant_option   => FALSE);
END;
/

GRANT_OPTION => FALSE avoids delegating authority to pass the privilege to other accounts. A grant alone does not define a mapping or move the user's existing sessions. Authorization and classification are separate configuration steps.

4. Map New Application Logins

Use a new pending area to add mappings based on the database username. The package constant ORACLE_USER identifies the login attribute being matched.

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;

  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
    attribute      => DBMS_RESOURCE_MANAGER.ORACLE_USER,
    value          => 'PETSTORE',
    consumer_group => 'WEBUSER');

  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
    attribute      => DBMS_RESOURCE_MANAGER.ORACLE_USER,
    value          => 'REPORT_USER',
    consumer_group => 'MANAGER');

  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
END;
/

Verify these mappings using fresh PETSTORE and REPORT_USER logins. Do not assume that submitting a login mapping retroactively reclassifies every connection already held by an application. Confirm the actual consumer group through session inspection.

Username mapping is straightforward when web and reporting workloads use separate accounts. A connection pool that shares one database account across different workloads needs a classification design that reflects those workloads. Service or module mappings can be appropriate extensions, provided the application supplies reliable attributes and the mapping priorities are reviewed. They are outside this first example's executable configuration.

OTHER_GROUPS provides fallback coverage for sessions whose current consumer groups are not represented in the active plan. It is not a newly created database user, and this example does not create a username mapping to it.

5. Activate the Plan in the Test PDB

Review the submitted configuration and then run the following command in the same intended PDB:

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'ESALES_PLAN' SCOPE=MEMORY;

This command changes runtime policy. SCOPE=MEMORY is appropriate for the initial laboratory trial because it does not establish a durable startup configuration. Treat persistent deployment as a separate operational decision after testing and review.

Scheduler windows or later administrative changes can affect which plan is active. Check runtime state when testing instead of assuming the plan remains active because the command succeeded earlier. PDB enforcement also depends on the relevant CDB configuration, so an unexpected result may require inspection beyond this PDB's definitions.

6. Verify Definitions, Classification, and Behavior

Verification has three parts: confirm the stored directives, confirm the active plan, and confirm the consumer groups of fresh application sessions. Run these queries as the administrator with the required view access:

SHOW PARAMETER resource_manager_plan

SELECT plan, group_or_subplan,
       mgmt_p1 AS configured_shares,
       utilization_limit,
       active_sess_pool_p1,
       parallel_degree_limit_p1
FROM dba_rsrc_plan_directives
WHERE plan = 'ESALES_PLAN'
ORDER BY group_or_subplan;

SELECT * FROM v$rsrc_plan;

SELECT sid, serial#, username, service_name,
       resource_consumer_group
FROM v$session
WHERE username IN ('PETSTORE', 'REPORT_USER')
ORDER BY username, sid;

For this shares-based plan, DBA_RSRC_PLAN_DIRECTIVES.MGMT_P1 exposes the configured share value. The alias configured_shares makes its meaning clear in this query. Do not substitute a nonexistent SHARES column, or assume MGMT_P1 represents shares in every possible plan.

Expect three directives: WEBUSER with seven shares and DOP one; MANAGER with two shares, CPU ceiling 40, active-call limit four, and DOP four; and OTHER_GROUPS with one share and DOP one. Fresh PETSTORE sessions should show WEBUSER, and fresh REPORT_USER sessions should show MANAGER, subject to the mapping-priority assumptions.

These configuration checks do not prove that the policy meets the application's goals. In a controlled test, run representative web and reporting work concurrently, then observe web response time, report completion time, and queueing. Compare those results with the same workload's baseline. Keep the input data and workload volume comparable so that the policy change can be evaluated meaningfully.

A lightly loaded system need not exhibit a 70/20/10 CPU split. Relative CPU scheduling becomes relevant under contention; the explicit reporting ceiling has a different purpose. Likewise, several idle REPORT_USER connections do not demonstrate a failure of the active-call limit. Distinguish logged-in sessions from calls actively competing for execution.

The commands are checked against Oracle documentation, not presented as results from a live Oracle instance. Actual runtime behavior must be verified in your environment; no successful-execution transcript or measured CPU distribution is implied here.

7. Restore the Previous Runtime Policy

After the trial, restore the exact RESOURCE_MANAGER_PLAN value recorded at the beginning, using SCOPE=MEMORY in the same PDB. An empty string is appropriate only if that was the actual previous setting and the wider configuration permits it. Do not use it as a universal rollback command.

Restoring the active plan does not remove submitted groups, directives, mappings, or grants. If complete cleanup is required, review which objects this lab created and which prior values need restoration. Remove only the lab's own additions or restore the recorded mapping values. Keep this reviewed cleanup separate from the main execution sequence.

Apply the Policy to Real Workloads

Oracle 26ai References

• DBMS_RESOURCE_MANAGER package reference
• DBMS_RESOURCE_MANAGER_PRIVS package reference
• Managing resources with Oracle Database Resource Manager
• RESOURCE_MANAGER_PLAN parameter
• DBA_RSRC_PLAN_DIRECTIVES view

DB Resource Manager - Exercise