Session throttled by Resource Manager

Resource Manager divides CPU and active sessions among consumer groups, according to the active resource plan. When the session's group goes beyond what the plan allows, the database holds the session, and the time shows up in the resmgr waits.

Symptoms

In the Diagnosis, the finding Session throttled by Resource Manager (RESOURCE_MANAGER) appears. The text says how much of the total time went to the Scheduler wait class, as a percentage and in seconds, lists the events of the class that weighed the most and ends with View profile by class.

The finding appears when the Scheduler class weighs on the total time. The severity is always medium. Three events belong to this class:

• resmgr:cpu quantum: the plan took CPU away from the session to enforce the split among the groups.
• resmgr:become active: the group reached its limit of sessions active at the same time, and the session waited its turn.
• resmgr:pq queued: the parallel statement stayed in the queue until parallel processes were free.

Cause

• Server without free CPU, with an active plan: Resource Manager shares the CPU among the groups, and a session in a group with a small share waits.
• Group limit: the plan limits the group's CPU even with free CPU on the server, or limits the number of active sessions.
• Instance caging: with an active plan and cpu_count below the number of CPUs on the server, the whole instance is limited.
• Maintenance window: in the Scheduler windows, the DEFAULT_MAINTENANCE_PLAN plan takes effect for the automated tasks, such as statistics gathering, and the user workload loses priority.

Resolution

The queries show the current plan and group: run them at the time the operation runs, because the active plan changes with the windows.

1. See the active plan:

   select name, is_top_plan, cpu_managed, instance_caging
     from v$rsrc_plan;

   Result: the plan with is_top_plan equal to TRUE is the one in effect. With no row, no plan is active now.

2. See the session's consumer group. The SID is in Session, in the Session and environment card of the Summary:

   select sid, serial#, username, service_name, module, resource_consumer_group
     from v$session
    where sid = &sid;

3. See the CPU share and the limits of each group in the plan:

   select group_or_subplan, mgmt_p1, mgmt_p2, utilization_limit,
          active_sess_pool_p1, parallel_server_limit
     from dba_rsrc_plan_directives
    where plan = '&plano'
    order by mgmt_p1 desc;

4. Compare the trace Period, in the Summary, with the Scheduler windows:

   select window_name, resource_plan, repeat_interval, duration,
          last_start_date, next_start_date, enabled, active
     from dba_scheduler_windows;

5. Agree on the adjustment with the DBA: map the session to the right group (by service, module or user), change the group's share in the plan, or move the operation outside the maintenance window.

The Oracle documentation on Resource Manager describes plans, groups, mappings and instance caging.

How to confirm

The active plan depends on the time of day. Measure again at the same time as the previous measurement, or the comparison mixes different plans.

1. Apply the adjustment agreed with the DBA.
2. Turn on tracing for the same operation with waits (turn on tracing with DBMS_MONITOR).
3. Upload the new file and open the report.
4. Compare the Scheduler row in By wait class and the resmgr events in the profile.

Result: the Scheduler class no longer weighs on the total time and the finding disappears from the Diagnosis. If the time moved to CPU or to unaccounted-for time within calls, the server still has no free CPU: see unaccounted-for time within calls.

In the report

The sample report has no Scheduler class waits; the links open the same sections in it.

• Summary: the trace Period and the Session and environment card, with Session, Service and Module (summary).
• Diagnosis: the finding, with the View profile by class link.
• By wait class: the Scheduler row (wait classes).
• Response time profile: the resmgr events, with Occurrences and Avg (s). The name of resmgr:cpu quantum opens the Resource Manager limiting CPU card (profile).
• Top statements by component and by class: the statements the session was running while it was throttled (statements by component).