Oracle database is slow

when someone says:

🚨 “Oracle database is slow”

---

⚡ Oracle Performance Troubleshooting – Step-by-Step Playbook

---

🧠 Step 0: Stay Calm & Don’t Guess

Before doing anything:

👉 Don’t jump into AWR immediately
👉 Don’t restart database
👉 Don’t assume it’s “CPU issue”

First rule:

Find the bottleneck, not the symptom

---

🔍 Step 1: Identify Where Time is Spent (Your Query 👇)

SELECT event, total_waits, time_waited
FROM v$system_event
ORDER BY time_waited DESC
FETCH FIRST 5 ROWS ONLY;

👉 What to check:

If you see	Meaning
db file sequential read	Index I/O
db file scattered read	Full table scan
log file sync	Commit issue
CPU time high	CPU pressure
enq: TX row lock	Blocking

✅ Now you know WHERE to focus

---

⚡ Step 2: Check Active Sessions (Real-Time)

SELECT sid, serial#, username, sql_id, event, wait_class, seconds_in_wait
FROM v$session
WHERE status = 'ACTIVE'
ORDER BY seconds_in_wait DESC;

👉 Identify:

• Long running sessions

• Blocking sessions

• Top wait events

---

🚫 Step 3: Check Blocking / Locks

SELECT blocking_session, sid, serial#, wait_class, event
FROM v$session
WHERE blocking_session IS NOT NULL;

If blocking exists:

👉 That’s your issue (most critical)

---

🧮 Step 4: Identify Top SQL (Heavy Queries)

SELECT sql_id, executions, elapsed_time/1000000 total_sec
FROM v$sql
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

👉 Look for:

• High elapsed time

• High executions

• Same SQL repeated

---

📊 Step 5: Check CPU Usage

SELECT stat_name, value
FROM v$sysstat
WHERE stat_name LIKE '%CPU%';

👉 Or OS level:

top

If CPU is high:

• Bad SQL

• Missing indexes

• High concurrency

---

💾 Step 6: Check I/O Bottleneck

SELECT file#, phyrds, phywrts
FROM v$filestat;

👉 OR check wait events:

• db file sequential read → index reads

• db file scattered read → full scans

---

🔄 Step 7: Check Redo / Commit Issues

SELECT event, time_waited
FROM v$system_event
WHERE event = 'log file sync';

👉 If high:

• Too many commits

• Slow disk

---

🌐 Step 8: Check Network Issues

Look for:

• SQL*Net message from client

• SQL*Net more data

👉 Usually application-side delay

---

📈 Step 9: Check AWR (If Needed)

Only AFTER initial diagnosis:

@?/rdbms/admin/awrrpt.sql

Focus on:

• Top SQL

• Wait events

• Load profile

---

🧪 Step 10: Drill Down into Problem SQL

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

👉 Check:

• Full table scans

• Bad joins

• Wrong cardinality

---

🚀 Step 11: Take Action (Based on Root Cause)

Issue	Action
Blocking	Kill session / fix transaction
Bad SQL	Tune query / add index
CPU high	Optimize SQL
I/O high	Add indexes / check storage
Redo issue	Reduce commits
Stats stale	Gather stats

---

🔥 Real DBA Flow (Golden Rule)

When DB is slow:

1. Check wait events ✅

2. Check active sessions ✅

3. Check blocking ✅

4. Identify top SQL ✅

5. Drill into execution plan ✅

---

🧠 Interview-Level Answer (Important)

If interviewer asks:

👉 “Database is slow, what will you do?”

Say:

“First I check system wait events to identify where time is spent, then I analyze active sessions and blocking, followed by identifying top SQL and reviewing execution plans before taking corrective action.”

---

⚡ Pro Tips (From Real Production Experience)

• 80% issues = bad SQL or missing index

• 10% = locking

• 10% = infrastructure (CPU/I/O)

---

🎯 Final Thought

“Performance tuning is not about fixing everything…
It’s about fixing the right thing first.”

---