Resolving High CPU Usage in Oracle Database – Real-World DBA Troubleshooting Guide

You log into the server, and your monitoring tool screams:
CPU usage 99% — Oracle taking it all.

Your users complain that everything is slow, AWR reports show “CPU time” at the top, and suddenly everyone’s looking at you — the DBA — for answers.

Don’t panic. High CPU usage in Oracle databases is common and usually fixable with the right approach.
Let’s go step-by-step through how to find what’s eating up CPU and how to fix it quickly.

---

What Causes High CPU Usage in Oracle?

High CPU consumption means Oracle is working harder than it should — either because of inefficient SQL, runaway sessions, or system misconfiguration.

Here are the most common reasons:

1. Expensive SQL queries (missing indexes, bad plans, large table scans)
2. Too many concurrent sessions or background processes
3. Parameter misconfigurations (like optimizer_features_enable, parallel_degree_policy)
4. High logical I/O or parsing overhead
5. High CPU on OS level — caused by backups, monitoring agents, or other services

---

Step-by-Step Guide to Troubleshoot High CPU in Oracle

Step 1: Confirm the Issue at OS Level

Before diving into Oracle, check if CPU usage is truly high:

top -c

Look for:

• Which process is using CPU (e.g., oracleORCL (LOCAL=NO))
• CPU usage percentage
• The OS user (should be oracle)

If Oracle is the culprit, note the PID (process ID).

---

Step 2: Map the OS Process to an Oracle Session

Find which session or SQL is behind that CPU-hungry process:

SELECT s.sid, s.serial#, s.username, s.program, p.spid, s.sql_id
FROM v$session s, v$process p
WHERE s.paddr = p.addr AND p.spid = &PID;

You’ll get the SID, SQL_ID, and user causing the spike.

---

Step 3: Identify the SQL Statement

Now find the actual SQL:

SELECT sql_id, elapsed_time/1000000 AS elapsed_sec, cpu_time/1000000 AS cpu_sec, executions, sql_text
FROM v$sql
WHERE sql_id = '&SQL_ID';

This reveals the exact query burning CPU.

If you see cpu_time is high and executions is low — it’s a heavy SQL.
If executions is high — it’s being executed too often.

---

Step 4: Review SQL Execution Plan

Use:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&SQL_ID', NULL, 'ALLSTATS LAST'));

Look for:

• Full table scans (indicated by “TABLE ACCESS FULL”)
• High logical reads (cr / pr values)
• Missing indexes or poor join methods

Fix: Create missing indexes, rewrite joins, or gather fresh statistics.

---

Step 5: Check for Parsing and Shared Pool Issues

Excessive parsing can drive CPU up. Run:

SELECT name, value FROM v$sysstat WHERE name LIKE 'parse%';

If parse count (hard) is high, your application might not be using bind variables.
👉 Encourage developers to use bind variables to reuse execution plans and reduce parsing load.

---

Step 6: Analyze AWR / ASH Reports

If you have AWR access:

• Run a short AWR snapshot around the issue window.
• Check Top SQL by CPU Time, Top Events, and Load Profile.

Or use ASH:

SELECT sql_id, COUNT(*)
FROM v$active_session_history
WHERE session_state='ON CPU'
GROUP BY sql_id
ORDER BY COUNT(*) DESC FETCH FIRST 10 ROWS ONLY;

This gives you the most CPU-hungry SQLs over time.

---

Step 7: Look for System-Wide Causes

Sometimes the issue isn’t SQL. Check:

• Background processes

SELECT program, status FROM v$process WHERE background = 1;

• Parallel slaves stuck:

SELECT username, program, status FROM v$session WHERE username IS NOT NULL;

oo many parallel sessions or runaway jobs can spike CPU usage fast.

---

Step 8: Quick Fix (If System is Hanging)

If a runaway query is blocking others:

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Or, if you need to temporarily calm the system:

ALTER SYSTEM SET resource_manager_plan='DEFAULT_PLAN';

Then, tune SQLs permanently afterward.

---

Example: Real Production Case

One of our Oracle 19c environments suddenly spiked to 98% CPU.
We traced the PID → session → SQL_ID and found a nightly report query performing a full table scan on a 12M-row table after a recent statistics refresh changed the execution plan.

Fix: We created a composite index, gathered targeted stats, and flushed the shared pool:

EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', cascade => TRUE);
ALTER SYSTEM FLUSH SHARED_POOL;

CPU dropped from 98% to 25% instantly — and the job completed 4x faster.

---

Prevention Tips for Future

1. Use AWR baselines to compare CPU trends over time.
2. Regularly monitor Top SQL by CPU in AWR or OEM.
3. Tune queries before production deployments.
4. Use SQL Plan Management (SPM) to prevent plan regressions.
5. Avoid hard parses — use bind variables in all applications.
6. Apply database patches and OS updates regularly.

---

Related Articles

• How To Download And Install The Latest OPatch
• Oracle Grid Infrastructure 19c Restart Home Installation — Complete Step-by-Step Guide
• How to Fix “ORA-01111: Name for Data File Is Unknown” in Oracle Standby Databases

---

Final Thoughts

High CPU usage in Oracle can look scary at first, but it’s often just one SQL query running wild.
By following a structured approach — check OS → session → SQL → plan — you can identify and fix the culprit quickly.

Remember, the goal isn’t just to fix it once — but to make sure it never happens again with the right monitoring and tuning strategy.