Real-Time Troubleshooting Scenario

---

🚨 Real-Time Troubleshooting Scenario (Hands-On Practice)

🎯 Situation

Time: 11:15 AM
Environment: Production
Users complaint:

“Application is very slow. Pages are hanging.”

You have no prior info.

---

⏱️ Your Goal (as DBA)

• Identify root cause in 5–10 minutes

• Avoid guesswork

• Take correct action

---

🔍 Step 1: Where is time spent?

You run:

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

📊 Output:

EVENT                          TIME_WAITED
----------------------------   -----------
enq: TX - row lock contention   9854321
db file sequential read         234567
CPU time                        123456

---

❓ Question 1 (Your Move)

👉 What does this indicate?

✅ Expected Thinking:

• Top wait = row lock contention

• This is blocking issue, not CPU or I/O

---

🔎 Step 2: Find Blocking Session

You run:

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

📊 Output:

BLOCKING_SESSION   SID   EVENT
---------------   ----  -----------------------------
145               201   enq: TX - row lock contention
145               305   enq: TX - row lock contention
145               410   enq: TX - row lock contention

---

❓ Question 2

👉 What do you conclude?

✅ Answer:

• Session 145 is blocking multiple sessions

• This is a critical production issue

---

🔍 Step 3: Identify Blocking SQL

SELECT sid, serial#, username, sql_id, status
FROM v$session
WHERE sid = 145;

Output:

SID   SERIAL#   USERNAME   SQL_ID     STATUS
145   6789      APPUSER    abc123xyz  INACTIVE

---

❓ Question 3

👉 Why is this dangerous?

✅ Answer:

• Session is INACTIVE but holding locks

• Likely:

  • Uncommitted transaction

  • Application issue

---

🔥 Step 4: Business Decision Time

You have 2 options:

Option A:

👉 Wait for user to commit/rollback

Option B:

👉 Kill the session

---

❓ Question 4

👉 What will you do?

✅ Best Answer:

• If production is impacted → Kill session

---

⚡ Step 5: Kill Blocking Session

ALTER SYSTEM KILL SESSION '145,6789' IMMEDIATE;

---

📊 Step 6: Validate Fix

Check again:

SELECT blocking_session FROM v$session
WHERE blocking_session IS NOT NULL;

Output:

No rows selected

---

🎉 Result

• Blocking removed

• Application restored

• Issue resolved in minutes

---

🧠 Now Let’s Level Up (Scenario 2)

---

🚨 New Situation

Users say:

“Reports are very slow”

---

You run:

SELECT event, time_waited
FROM v$system_event
ORDER BY time_waited DESC;

Output:

db file scattered read   9999999
CPU time                 8888888

---

❓ Question 5

👉 What does this indicate?

✅ Answer:

• Heavy Full Table Scans

• Likely:

  • Missing indexes

  • Bad SQL

---

🔍 Next Step

SELECT sql_id, elapsed_time, executions
FROM v$sql
ORDER BY elapsed_time DESC
FETCH FIRST 5 ROWS ONLY;

---

❓ Question 6

👉 What will you do next?

✅ Answer:

• Pick top SQL_ID

• Check execution plan:

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

---

🎯 Final Diagnosis

• Full table scan detected

• Missing index

---

⚡ Fix

CREATE INDEX idx_col ON table_name(column_name);

---

🏁 Final Learning

🔥 Golden Troubleshooting Flow:

1. Check wait events

2. Identify type of issue

3. Find sessions / SQL

4. Take targeted action

---

🎯 Mock Interview Question (Based on Scenario)

👉 “How will you handle blocking in production?”

Perfect Answer:

“I first check wait events to confirm locking, identify blocking session using V$SESSION, verify the SQL and session state, and if it’s impacting users, I kill the blocking session and inform application team.”

---