Scops
Docs Download

Oracle problems

Oracle CPU at 100%: The Database's Share, the Sessions on CPU, and the Fix

3 November 2026

A host at 100% CPU has two different stories. In the first, the database is using it, and the sessions queue for a core. In the second, something else on the host is using it, a backup, an agent, a neighbouring instance, a hypervisor giving the cycles to another guest, and the database queues behind it. The two look identical from the application, every screen slow at once, and they have different fixes. V$OSSTAT tells them apart in one query, V$SESSION counts the sessions on CPU, and V$SQL names the statements that burn it.

The host's CPU, and the database's share

Oracle reads the operating system's counters into V$OSSTAT, in hundredths of a second, totalled over all processors:

SELECT stat_name, value
FROM   v$osstat
WHERE  stat_name IN ('NUM_CPUS', 'NUM_CPU_CORES', 'LOAD',
                     'BUSY_TIME', 'IDLE_TIME', 'USER_TIME',
                     'SYS_TIME', 'IOWAIT_TIME',
                     'RSRC_MGR_CPU_WAIT_TIME');

Two readings a minute apart give the rate. On 8 CPUs a minute holds 48,000 hundredths; a BUSY_TIME that grew by 46,000 is a host at 96%. LOAD is the number of processes running or ready to run, and on most platforms the average over the past minute: above the CPU count, there is a queue. The database's own share is in V$SYSSTAT, where CPU used by this session is, by Oracle's tuning guide, the CPU used by all sessions together:

SELECT name, value
FROM   v$sysstat
WHERE  name IN ('CPU used by this session', 'parse time cpu',
                'recursive cpu usage');

Read the two deltas together. The database's CPU grew by 44,000 of the host's 46,000: the database is the consumer, and the next two queries find the sessions and the statements. It grew by 9,000: four fifths of the host is busy with something that is not this instance, and no statement in it is the fix. Oracle's guide puts it plainly: if your process is not consuming that time, then some other process is, so identify the processes using the CPU and tune them. A SYS_TIME far above USER_TIME is the kernel, often memory pressure or a storm of process creation; an IOWAIT_TIME that climbs is idle CPU, not busy, and points at the storage.

The sessions on CPU, and the queue

An active session that is not in a wait is running on a CPU or in the run queue for one. Grouped with the sessions the Resource Manager holds back, and the rest by wait class:

SELECT doing, COUNT(*) AS sessions
FROM  (SELECT CASE WHEN state <> 'WAITING' THEN 'on CPU'
                   WHEN event = 'resmgr:cpu quantum'
                        THEN 'queued for CPU by Resource Manager'
                   ELSE 'waiting: ' || wait_class END AS doing
       FROM   v$session
       WHERE  status = 'ACTIVE'
       AND    type = 'USER')
GROUP  BY doing
ORDER  BY sessions DESC;

More sessions on CPU than cores is saturation by definition: the extra ones hold the run queue and each of them gets a slice. The Resource Manager row has its own counters, which Oracle fills whenever STATISTICS_LEVEL is TYPICAL, with or without a plan:

SELECT name, active_sessions, queue_length,
       ROUND(consumed_cpu_time / 1000)        AS cpu_s,
       ROUND(cpu_wait_time / 1000)            AS cpu_wait_s,
       cpu_waits, yields
FROM   v$rsrc_consumer_group
ORDER  BY consumed_cpu_time DESC;

A cpu_wait_time above zero means the sessions waited for CPU because of resource management: a plan with a utilization limit, or instance caging, which Oracle's reference defines in one line: when the Resource Manager is managing CPU, the database's CPU utilization is limited to CPU_COUNT threads. A caged instance can be at its limit on a host that is half idle.

The statements that burn it

SELECT sql_id, executions,
       ROUND(cpu_time / 1e6)                        AS cpu_s,
       ROUND(cpu_time / NULLIF(executions, 0) / 1e3) AS ms_per_exec,
       ROUND(buffer_gets / NULLIF(executions, 0))   AS gets_per_exec,
       SUBSTR(sql_text, 1, 60)                      AS sql_text
FROM   v$sql
WHERE  last_active_time > SYSDATE - 1 / 24
ORDER  BY cpu_time DESC
FETCH FIRST 10 ROWS ONLY;

CPU inside Oracle is mostly logical reads, so the number that explains the top of the list is gets per execution. A statement with a modest count of gets and millions of executions is the application's loop; one with a million gets per execution and a hundred executions is a plan that visits too many blocks, a missing index, a join order that flipped, and the plan page has the query that shows the old plan. When parse time cpu is a large share of CPU used by this session, the CPU goes to parsing, and Oracle's guide says that ratio should be close to zero: literal SQL from an application that does not bind, which is also what drains the shared pool.

The fix, in order

  1. When the host is busy and the database is not, fix the host. The process is in top, not in Oracle: a backup compressing on the database server, an antivirus scan, a monitoring agent that reads every file, a second instance on the same host, or a virtual machine whose cycles went to a neighbour, which shows as steal time. Move it, cap it or schedule it; the database can only wait for it.
  2. Fix the statement at the top of the list. Gets per execution is the lever: a plan that reads a tenth of the blocks uses a tenth of the CPU. A plan that flipped goes back with a baseline; a loop that runs a statement a million times becomes one statement; a report that runs at nine o'clock runs at six.
  3. Stop the parsing. Bind variables in the application are the fix; CURSOR_SHARING = FORCE is the stopgap that turns literals into binds for the application that cannot change this week; SESSION_CACHED_CURSORS keeps the soft parses cheap.
  4. Share the CPU with the Resource Manager. A plan with a UTILIZATION_LIMIT on the reporting group keeps a batch from taking every core, and Oracle only enforces it when CPU usage is at 100%, so it costs nothing below that. Instance caging, CPU_COUNT plus an active plan, keeps two instances on one host from starving each other.
  5. Count the parallel servers as CPUs. A degree of 64 is 128 processes that all want a core; the parallel pool is a CPU multiplier, and a degree that fits the host is the first thing to set.
  6. Add cores last, and know what they cost. On Enterprise Edition a core added to the host is a licence as well as a chip, and the capacity forecast below says how many weeks the current ones have left.

See it coming

Radar Engine, the detection layer of Scops, watches both stories at once on every database, live and without an agent: the sessions on CPU against the usable CPUs, and the host's CPU against the database's share. It also forecasts the busiest hour of each day against 85% of the threads, so a host that will be out of CPU in three weeks is a finding today:

DetectorClockWhen it fires
CPU saturatedLive. Sessions on CPU or in the queue, against the usable CPUs.As many as the CPUs: Critical. Rising toward them: Warning, with an ETA.
Host CPU busyLive. 85% or more, less than half of it from the database.Warning. 95% with a run queue of twice the cores: Critical.
CPU capacity running outForecast of the busiest hour of each day over 14 days, against 85% of the CPU threads.Above 85% on three of the last seven days: Watch. With sessions in the queue: Warning. Busiest hour at 95%: Critical.
Unusual CPU loadHourly, against the same hour on other days.By size, duration and cause: the usual load, many statements, mostly a backup or a database link. Critical only with a named cause, high for 3 hours.

Host CPU busy is the second story by name, the host above 85% with less than half of it from the database, which is the finding that stops a DBA from chasing a statement that is not there. A saturation that happens at the same time every day is lowered to a Watch when daily patterns are set to be ignored, so the nightly batch does not page anyone.

Questions

How do I tell whether Oracle or something else is using the CPU?
Two readings of V$OSSTAT BUSY_TIME and of V$SYSSTAT "CPU used by this session" a minute apart, both in hundredths of a second. The database's delta close to the host's means the database; a small share means another process on the host.

What does the resmgr:cpu quantum wait mean?
The session is ready to run and the Resource Manager is holding it back, because of a plan's utilization limit or instance caging. V$RSRC_CONSUMER_GROUP counts that time in CPU_WAIT_TIME, per consumer group.

How many sessions on CPU is too many?
More than the cores. The extra ones sit in the run queue and every session gets a slice. Radar calls it Critical when the sessions on CPU or in the queue reach the number of usable CPUs.

Does instance caging fix CPU saturation?
It limits the instance to CPU_COUNT threads once a resource plan is active, which protects the other instances on the host. Inside the caged instance the fix is still the statement at the top of V$SQL.

Related problems

Sources