Oracle Database CPU Performance Troubleshooting

 

Oracle Database CPU Performance Troubleshooting (RHEL)

1. Typical CPU Performance Symptoms

• High CPU utilization (near 100%)
• Load average increases rapidly
• Slow query response while disk latency is normal
• AWR shows:
  • DB CPU
  • CPU time
  • resmgr:cpu quantum
• OS %idle very low and %wa low (CPU, not IO)

---

2. Step‑1: Confirm CPU Pressure at OS Level

✅ Check load vs CPU cores

uptime

nproc

lscpu | egrep 'CPU\(s\)|Core|Socket|Thread'

Interpretation

• Load average ≤ CPU cores → system OK
• Load average >> CPU cores → CPU contention

Example:

load average: 48.21, 45.12, 40.90
CPU cores: 24

➡️ CPU saturation confirmed

---

3. Step‑2: Identify CPU Utilization & Wait

✅ top (quick overview)Focus on top line:

top

%Cpu(s): 92.4 us, 3.1 sy, 0.2 ni, 2.0 id, 1.5 wa

CPU‑bound system

• %us + %sy high
• %id near 0
• %wa low

---

✅ vmstat – verify run queue

vmstat 1 10

Key columns:

Column	Meaning
r	Runnable processes
us	User CPU
sy	System CPU
id	Idle
wa	IO wait

If r > CPU cores consistently → CPU contention

---

4. Step‑3: Identify Top CPU Consumers

✅ Per‑process CPU usage

ps -eo pid,ppid,cmd,%cpu,%mem --sort=-%cpu | head -20

Look for:

• Oracle foreground sessions
• Oracle background processes (ora_*)
• Non‑DB processes stealing CPU

---

✅ Real‑time per‑process view

top -H -p $(pgrep -d',' -f ora_)

This shows Oracle threads consuming CPU.

---

5. Step‑4: Per‑CPU & Core Imbalance

✅ mpstat (very important on NUMA)

mpstat -P ALL 1 5

Look for:

• Some CPUs at 100%
• Others mostly idle

➡️ Indicates:

• NUMA imbalance
• CPU affinity or pinning problem

---

6. Step‑5: Context Switching & Syscall Overhead

✅ pidstat – CPU & context switches

pidstat -u -w 1 5

High:

• cswch/s (context switches)
• nvcswch/s

➡️ Too many active sessions or excessive parsing

---

7. Step‑6: Oracle Side – Is Database the CPU Consumer?

✅ CPU usage inside Oracle

SELECT stat_name, value

FROM v$sysstat

WHERE stat_name IN ('CPU used by this session','DB CPU');

Check ratio:

• DB CPU close to total DB time → CPU bottleneck
• IO waits dominant → not CPU issue

---

8. Step‑7: Identify Oracle Sessions Using CPU

✅ Active CPU sessions

SELECT s.sid, s.serial#, s.username,

s.program, s.status,

p.spid OS_PID

FROM v$session s, v$process p

WHERE s.paddr = p.addr

AND s.status = 'ACTIVE'

AND s.wait_class = 'CPU'

ORDER BY s.last_call_et DESC;

🔁 Correlate SPID with OS:

top -p <spid>

---

9. Step‑8: SQL Responsible for CPU Usage

✅ Top CPU SQL

SELECT sql_id,

cpu_time/1000000 cpu_sec,

executions,

cpu_time/executions avg_cpu

FROM v$sql

ORDER BY cpu_time DESC

FETCH FIRST 10 ROWS ONLY;

Red flags:

• High avg_cpu
• Low executions but high total CPU
• Cartesian joins
• Missing indexes

---

10. Step‑9: Run Queue vs CPU Throttling

✅ Check cgroups / VM CPU limits

cat /sys/fs/cgroup/cpu/cpu.cfs_quota_us

cat /sys/fs/cgroup/cpu/cpu.cfs_period_us

Quota < total cores → CPU throttling

---

11. Step‑10: Oracle Resource Manager (Very Common)

✅ Check RM activation

SHOW PARAMETER resource_manager_plan;

✅ CPU waits due to RM

SELECT event, total_waits, time_waited/100 time_sec

FROM v$system_event

WHERE event LIKE 'resmgr%';

If high:

• Sessions are being CPU throttled intentionally

---

12. Common CPU Root Causes

Cause	Symptom
Bad execution plan	Single SQL consumes most CPU
Missing index	Full scans
High parse rate	Hard parsing
Too many sessions	Context switching
CPU oversubscription	VM / container CPU cap
NUMA imbalance	Some CPUs overloaded

---

13. Immediate Mitigation Actions

✅ Short‑term:

• Kill runaway sessions
• Reduce parallelism
• Disable unnecessary jobs
• Temporarily increase CPU shares

✅ Medium‑term:

• SQL tuning (indexes, hints)
• Bind variables
• Increase cursor cache
• Enable Result Cache (where valid)

✅ Long‑term:

• SQL baselines
• CPU capacity increase
• NUMA pinning
• Resource Manager redesign

---

14. One‑Shot CPU Triage Command Set

uptime

vmstat 1 5

mpstat -P ALL 1 5

top -H -p $(pgrep -d',' -f ora_)

Oracle:

SELECT sql_id, cpu_time/1000000 cpu_sec

FROM v$sql

ORDER BY cpu_time DESC FETCH FIRST 5 ROWS ONLY;

---

15. CPU vs IO – Golden Rule

Observation	Root Cause
High %us, low %wa	CPU bottleneck
High load, CPUs idle	IO bottleneck
High DB CPU in AWR	SQL tuning required
RM waits	Resource controls