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 CPUCPU timeresmgr:cpu quantum
- OS
%idlevery low and%walow (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 + %syhigh%idnear 0%walow
✅ 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 CPUclose 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 |
