Autonomous Database queries waiting on resmgr:cpu quantum: services, consumer groups and concurrency limits

#Symptoms

• Report queries are slow; ASH/AWR or Performance Hub shows high resmgr:cpu quantum waits.
• Statements sit queued before running (Performance Hub shows them waiting), especially on the HIGH service.
• Batch jobs slow down interactive users, or the other way round.

Autonomous Database uses Database Resource Manager with predefined consumer groups. The connection service you pick (tpurgent, tp, high, medium, low) decides the consumer group, and with it CPU shares, parallelism and concurrency limits.

resmgr:cpu quantum means: the session is ready to run but Resource Manager is holding it back because its group has used its share of CPU.

#Step 1: who is waiting, in which group?

SELECT consumer_group_id, event, COUNT(*) AS samples
FROM   v$active_session_history
WHERE  sample_time > SYSDATE - 30/1440
GROUP  BY consumer_group_id, event
ORDER  BY samples DESC
FETCH FIRST 20 ROWS ONLY;

Map IDs to names and see current usage:

SELECT name, active_sessions, queue_length, cpu_wait_time, consumed_cpu_time
FROM   v$rsrc_consumer_group
ORDER  BY consumed_cpu_time DESC;

SELECT s.username, s.service_name, i.current_consumer_group, i.state, COUNT(*) AS sessions
FROM   v$session s JOIN v$rsrc_session_info i ON i.sid = s.sid
WHERE  s.type = 'USER'
GROUP  BY s.username, s.service_name, i.current_consumer_group, i.state
ORDER  BY sessions DESC;

• queue_length > 0 → statements waiting for a concurrency slot.
• High cpu_wait_time → the group is CPU-throttled.

#Step 2: understand the services

Roughly (check the "Predefined Database Service Names" page for exact current values):

The classic mistake: every tool connects with high because it sounds best. Only a few statements run at once on high; the rest queue.

Check what applications actually use:

SELECT service_name, program, COUNT(*) FROM v$session
WHERE  type = 'USER' GROUP BY service_name, program ORDER BY 3 DESC;

#Step 3: put workloads on the right service

• Dashboards and short queries: medium or low (more concurrency).
• A few heavy ETL statements: high.
• OLTP apps: tp, with tpurgent only for latency-critical calls.

This is a connection-string change (the TNS alias in the wallet's tnsnames.ora), no database change needed.

#Step 4: adjust shares and limits if defaults don't fit

The CS_RESOURCE_MANAGER package lets an ADMIN user change plan directives for the predefined groups:

BEGIN
  CS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
    consumer_group    => 'HIGH',
    shares            => 8,
    concurrency_limit => 4);
END;
/

-- Revert to defaults
BEGIN
  CS_RESOURCE_MANAGER.REVERT_TO_DEFAULT_VALUES(consumer_group => 'HIGH', shares => TRUE, concurrency_limit => TRUE);
END;
/

Parameters and allowed ranges vary by workload type and version; check DESC CS_RESOURCE_MANAGER and the package documentation.

You can also cap runaway statements (runtime or I/O limits per group) with CS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE options such as elapsed-time and I/O limits, which cancel or switch long queries instead of letting them starve others.

Warning

Raising concurrency_limit on HIGH doesn't add CPU. More statements running at once means each gets less CPU and can be slower. Fix service choice first.

#Step 5: is there simply not enough CPU?

SELECT metric_name, ROUND(AVG(value), 1) AS avg_val, ROUND(MAX(value), 1) AS max_val
FROM   v$sysmetric_history
WHERE  metric_name IN ('CPU Usage Per Sec', 'Host CPU Utilization (%)', 'Average Active Sessions')
AND    begin_time > SYSDATE - 1/24
GROUP  BY metric_name;

If CPU is saturated across all groups:

• Enable compute auto scaling (uses up to 3× the base ECPUs and bills for what you use):

oci db autonomous-database update --autonomous-database-id <adb-ocid> --is-auto-scaling-enabled true

• Or scale the base ECPU count for sustained load.
• Tune the heaviest SQL (Performance Hub > SQL Monitoring) before buying more CPU.

#Decision table

#Prevent it next time

• Document which service each application must use, and enforce it in connection strings.
• Review v$rsrc_consumer_group weekly for queueing.
• Turn on auto scaling for spiky workloads.
• Test service changes with Performance Hub during a real peak.