Oracle DBA Lesson 104 Part 2 — Test resource policy against workload objectives

A resource policy can improve interactive response while delaying batch processing. A useful comparison establishes whether that tradeoff meets the agreed service objectives, identifies why sessions waited, and proves that the prior configuration and workload behavior can be restored. Use the active CDB and PDB plans and each session's current consumer group to design and assess a bounded policy comparison.

Oracle DBA Lesson 104 Part 2 — Test resource policy against workload objectives

Agree what success means

Define objectives before looking at a candidate result.

Workload	Objective
Interactive requests	At least 95% complete within 1 second; P95 response time at most 1 second
Fixed batch job	Completes within 80 seconds

P95 is the 95th percentile of the measured request durations. Record the measurement method, request population, sample size, and time period. Also agree limits on errors, timeouts, and impacts on other work. Meeting the response objective while violating the batch deadline leaves an acceptance condition unmet.

Control the comparison

Use the same SQL statements, bind-value distribution, data volume, concurrency, and bounded workload duration in the baseline and candidate runs. Match warm-up conditions and record host load, available CPU capacity, and relevant configuration. Keep optimizer settings, statistics, structures, and data unchanged between runs. If another change occurs, investigate it and repeat the affected comparison.

Confirm the active root and PDB plans, the applicable directives, and the current consumer-group assignments in each run. A group name or a dictionary plan definition alone does not identify the policy in force. Scheduler windows can change plan selection. Choose a period in which the plan stays stable, and record any transition. Do not change Scheduler configuration to make a comparison convenient.

Use equal measured intervals within each stable plan and repeat the test to assess variability. The same PDB, group, instance, and workload population must be identifiable at both endpoints. Record start and end timestamps and the actual elapsed time. Do not substitute a nominal delay for a measured interval.

See V$RSRC_PLAN and Using Oracle Resource Manager for PDBs.

Check the intended sessions

SQL*Plus can run this inspection template:

SHOW CON_NAME

SELECT sid, serial#, username, resource_consumer_group,
       state, event, wait_class
FROM v$session
WHERE username IN (:interactive_user, :batch_user)
ORDER BY sid;

Define interactive_user and batch_user as SQL*Plus VARCHAR2(128) client binds, and assign the exact intended usernames before executing the template:

VARIABLE interactive_user VARCHAR2(128)
VARIABLE batch_user VARCHAR2(128)

The account filter may include more sessions than the test needs. Identify the intended sessions and record SID, SERIAL#, container, service or module where applicable, and observation time. A reused SID can represent a different session. A RAC investigation needs instance identity and a separately designed scope.

Interpret the fields together:

• RESOURCE_CONSUMER_GROUP is the session's current group. Membership can change, so capture it during the test.
• STATE = 'WAITING' means that EVENT describes a current wait. Other wait-state values refer to an earlier wait.
• EVENT and WAIT_CLASS help distinguish scheduling pressure, I/O, locking, and other causes. Repeated samples can still miss short waits. Application timings are necessary to establish service impact.

A Resource Manager CPU wait can reflect intentional scheduling. A session waiting for a transaction lock needs investigation of its blocker. A current-wait snapshot by itself does not establish the full cause of end-to-end application latency.

See V$SESSION.

Separate current counts from cumulative counters

Capture this view at the start and end of a bounded interval in the same PDB and a stable plan:

SELECT name, active_sessions, execution_waiters, requests,
       cpu_wait_time, consumed_cpu_time
FROM v$rsrc_consumer_group
ORDER BY name;

Field	Interpretation
ACTIVE_SESSIONS	Current count of active sessions in the group
EXECUTION_WAITERS	Current count of active sessions waiting for a CPU execution time slice
REQUESTS	Cumulative requests executed in the group
CPU_WAIT_TIME	Cumulative milliseconds waiting on Resource Manager CPU scheduling; excludes I/O, latch, and enqueue contention
CONSUMED_CPU_TIME	Cumulative CPU milliseconds consumed by the group's sessions

For cumulative counters, calculate end value − start value over the actual interval. Current session counts are snapshots. Subtracting them does not produce an interval wait duration. Oracle's executed-request counter is not automatically the same population as application requests or completed batch jobs. Keep the work measure and its source explicit.

With STATISTICS_LEVEL set to TYPICAL or ALL, resource information can appear even without an active plan managing that resource. CPU_WAIT_TIME is zero when CPU resources are not actively managed. Neither the presence of statistics nor a zero counter alone establishes policy activation or the absence of application delay.

Calculate the interval increase

Suppose the batch group's CPU_WAIT_TIME is 20,000 ms at the start and 32,000 ms at the end of a measured 30-second stable-plan interval:

32,000 − 20,000 = 12,000 ms of accumulated group CPU waiting.

These 12 seconds are summed across contributing sessions. They are different from the 30 seconds of elapsed observation time and from any individual request's response time. Aggregate session time can exceed elapsed wall time when sessions overlap. Do not label this calculation as a host CPU percentage.

Read the wait increase together with the group's consumed-CPU increase, executed-work measures, host capacity and load, and application response and completion timings. Preserve each source's units and measurement population.

Plan changes and counter resets

Oracle documents that these consumer-group statistics reset when the PDB resource plan changes. CDB plan changes do not reset these statistics. Any policy transition inside a sample still changes its interpretation. Use a fresh stable-plan interval for each comparison.

Take a new starting snapshot after the candidate PDB plan is active and stable. Calculate its delta from its own start and end values. Never subtract a pre-change counter from a post-reset counter. Repeat any interval that spans a reset or an unexpected plan or group transition. Different PDBs can have different reset times. This view's unmatched accumulated totals are unsuitable for comparing their histories.

See V$RSRC_CONSUMER_GROUP.

Judge both service outcomes

Using the same workload and measurement method, compare these application results. They are separate from the 30-second counter sample:

Metric	Prior policy	Candidate policy	Objective
Interactive P95 response	2.4 s	0.8 s	At most 1 s
Fixed batch completion	60 s	72 s	At most 80 s

Given these inputs, the candidate meets both timing objectives. Batch completion is 12 seconds later. If the agreed batch deadline were 65 seconds, the candidate would fail that condition. A change in the deadline changes the decision even when the candidate measurements stay the same.

The 30-second counter sample and the full batch completion time are separate measures. Select the counter sample during the same defined contention phase in each run. The fixed job's completion timing covers its whole run. Keep interactive arrivals, concurrency, and the batch workload bounded and repeatable. Include the actual sample placement in the evidence.

Timing arithmetic alone leaves repeatability, errors, timeouts, other affected work, the active policy, and restoration unverified. Report those conditions separately.

Design the comparison and the reversal

Record the comparison before changing the policy:

1. The two workload objectives, limits on errors and other impacts, the fixed statements, data, and binds, concurrency, duration, warm-up, and sample placement.
2. Maximum workload and runtime bounds, and concrete stop thresholds for host load, errors, latency, and resource pressure, with an operator assigned to stop the test. Resolve these thresholds before the run.
3. Prior root and PDB selection and active-state evidence, relevant directives and mappings, parameter persistence, Scheduler-window context, and the reviewed candidate policy.
4. The activation steps and the prior-state restoration steps for that scope. Record the original state even when a selection parameter is blank. Automatic CDB activation can make the runtime state more complex than that value alone.
5. Start and end group snapshots, session membership and wait samples, the application timing method, host observations, repeated-run comparisons, and evidence retention.
6. Stop, restoration, and postchecks: end the bounded workloads, restore the captured policy, mappings, and settings that actually changed, confirm the active state and groups, then rerun the agreed bounded baseline check.

ADMINISTER_RESOURCE_MANAGER permits Resource Manager administration through its package. Grant and revoke it through DBMS_RESOURCE_MANAGER_PRIVS. Plan-parameter changes and container operations can require additional authority. Ordinary workload accounts do not need administrative privileges. Inspection access is separate from plan-change authority.

General Resource Manager policy testing is not a Standard Edition 2 exercise merely because Standard Edition 2 supports instance caging.

This comparison does not supply a universal activation command. ROLLBACK of a transaction does not restore a resource plan or an initialization parameter. Do not equate an accepted setting with completed service recovery. End the comparison at a stop threshold or at its normal bound, restore the prior configuration, and verify expected workload behavior as well as the active plans and groups.

See Oracle Database Resource Manager, RESOURCE_MANAGER_PLAN, and Licensing Information.

Recap

Define both objectives before reading a candidate result. At least 95 percent of interactive requests should complete within 1 second, with P95 response time at most 1 second, and the fixed batch job should complete within 80 seconds. P95 is the 95th percentile of the measured request durations. Record the measurement method, population, sample size, time period, and the limits on errors, timeouts, and other work. Meeting the interactive objective while violating the batch deadline leaves an acceptance condition unmet.

Hold the SQL, bind-value distribution, data volume, concurrency, duration, warm-up, optimizer settings, statistics, structures, and data constant. Confirm the active root and PDB plans, directives, and consumer groups in each run. A group name or a dictionary plan definition does not identify the policy in force. Scheduler windows can change plan selection. Use equal measured intervals inside each stable plan, and record the actual elapsed time.

On V$SESSION, RESOURCE_CONSUMER_GROUP is the current group, and EVENT describes a current wait only when STATE is WAITING. A Resource Manager CPU wait can be intentional scheduling. A lock wait needs its blocker. A snapshot does not explain full application latency. On V$RSRC_CONSUMER_GROUP, ACTIVE_SESSIONS and EXECUTION_WAITERS are current counts. REQUESTS, CPU_WAIT_TIME, and CONSUMED_CPU_TIME are cumulative, so calculate end value − start value. A rise from 20,000 ms to 32,000 ms is 12,000 ms accumulated across the group's sessions. That total is not elapsed wall time, not each request's response time, and not a host CPU percentage. These statistics reset when the PDB resource plan changes. CDB plan changes do not reset them. Take a fresh start and end inside the new stable plan, and never subtract across a reset.

In the application comparison, interactive P95 moves from 2.4 seconds to 0.8 seconds and batch completion from 60 seconds to 72 seconds. Both meet the stated objectives. A 65-second batch deadline would reject the same measurements. The counter sample and the full job duration are separate measures, so place the sample in the same contention phase and keep the workload repeatable. ROLLBACK does not restore a resource plan or an initialization parameter. After restoring the captured settings, verify the active plans and groups and the expected baseline workload behavior.

Quiz

1. Which result is sufficient for accepting a candidate policy?

Lower interactive latency, regardless of batch behavior Higher CPU use alone Evidence meeting both workload objectives and agreed impact limits A consumer-group name in a plan definition

2. A cumulative group counter rises from 20,000 to 32,000 ms in a stable interval. What does the difference represent?

12,000 ms accumulated for that group's measured counter 12,000 percent host CPU use Every request's response time The current number of sessions waiting

3. What should happen after the PDB plan changes and group counters reset?

Subtract the old plan's start value from the new plan's end value Take a fresh start and end within the new stable plan Compare unmatched totals from another PDB Interpret a negative delta as improved performance

4. Is eliminating every Resource Manager wait necessarily an improvement?

Yes, all waiting is a policy failure Yes, even if interactive latency becomes unacceptable Yes, if a batch job finishes first No; controlled scheduling waits can protect a service objective

5. What completes the reversal after restoring the captured policy settings?

Assuming that the configuration command restored service Committing a workload transaction Verifying active plans/groups and expected baseline workload behavior Removing every maintenance window

https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily