High CPU

Server CPU is pegged. First verify that the database is really the one consuming it, then follow the branches to the root cause: badly planned queries, a plan regression, excessive concurrency or a non-DB process.

Is the database really consuming the CPU?

Check on the operating system side which process is burning CPU. Is most of the load in DB processes, or in a neighboring process (backup, antivirus, another application)? This splits the investigation in two.

-- OS side: top -o %CPU  (or: top -c)
-- DB-side verification:
SELECT ROUND(value,1) AS host_cpu_util_pct FROM v$sysmetric WHERE metric_name='Host CPU Utilization (%)' AND rownum=1;

• Yes, DB processes are consuming the CPU
• No, a neighboring process / backup job is consuming it

How did the load appear?

If the DB is consuming the CPU, identify the pattern: are a few heavy queries dominant, did the problem start suddenly after a change, or is there high concurrency/connection contention?

• A few heavy queries are burning the CPU
• Started suddenly; there was a recent deploy / parameter change
• Many connections / concurrency contention

Find the queries consuming the most CPU

Pull the top queries by total CPU consumption. Then decide whether each is heavy per call (bad plan) or cheap per call but called very frequently.

SELECT sql_id, executions, ROUND(cpu_time/1e6,1) AS cpu_sec, ROUND(cpu_time/GREATEST(executions,1)/1000,2) AS cpu_ms_per_exec, sql_text
FROM v$sql ORDER BY cpu_time DESC FETCH FIRST 10 ROWS ONLY;

• Heavy per call; bad plan / full scan
• Cheap per call but called very frequently

Root cause: Badly planned queries are burning CPU

A few queries consume excessive CPU per call because of full scans or a bad join order. Fixing their plans is the core solution.

Root cause: Excessive call frequency

The queries are individually cheap but called thousands of times per second; that is where the total CPU cost comes from. Usually an N+1 pattern or an uncached hot path.

Root cause: Plan regression / stale statistics

The system was normal yesterday; CPU spiked after a deploy, parameter change or data load. The optimizer may have switched to a worse plan.

Root cause: High concurrency / connection contention

Individual queries are acceptable, but too many sessions run at the same time; more active sessions than CPU cores cause context switching and latch/spin contention.

Root cause: A non-DB process is consuming CPU

It is not the database but a neighboring process consuming the CPU: a backup/compression job, an antivirus scan, a log shipper or another application on the same machine.