Slow query: in-depth diagnosis

A query or the whole system is slow. Narrow it down step by step: run a check at each node and choose the branch based on the result. You end with a root cause + fix.

What is slow?

Is one or a few specific queries slow, or the whole system? This splits the investigation into one of two tracks: 'query-focused' or 'resource/bottleneck-focused'.

• One or a few specific queries are slow
• The whole system is slow

Is the query running right now?

Is it a query running/stuck right now, or one that is 'generally slow'? Look at the active sessions.

SELECT sid, serial#, sql_id, last_call_et AS running_sec, event, blocking_session
FROM v$session WHERE status='ACTIVE' AND type='USER' ORDER BY last_call_et DESC;

• Running / stuck right now
• Generally slow; I want to look at its plan

Is the query waiting on something?

If it is active but not progressing, it is most likely waiting. Is blocking_session / wait_event populated?

SELECT sid, blocking_session, event, seconds_in_wait
FROM v$session WHERE sid=&target_sid;

• Blocked by another session
• Waiting on a resource (IO / log / latch)
• No wait; actively burning CPU

Which wait dominates?

The dominant wait event tells you the type of bottleneck.

SELECT event, total_waits, time_waited FROM v$session_event WHERE sid=&target_sid ORDER BY time_waited DESC FETCH FIRST 5 ROWS ONLY;

• IO wait (db file read / PAGEIOLATCH)
• Log / commit wait (log file sync / WRITELOG)
• Lock / enqueue (enq: TX / LCK)

Get the execution plan (EXPLAIN)

Pull the plan. Is there a full table scan, an unexpected join order, or a gap between estimated and actual rows?

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

• Full scan / missing index
• It was fast yesterday; the plan changed suddenly
• Processes too many rows / N+1
• The plan looks good but it is slow

Are statistics up to date?

A large gap between estimated and actual rows (E-rows vs A-rows) is a sign of stale statistics.

SELECT table_name, num_rows, last_analyzed FROM dba_tables WHERE table_name='&TABLE_NAME';

• Statistics are stale / old
• Statistics are current but it is still slow

System-wide: where is the bottleneck?

If everything is slow rather than a single query, look for a resource bottleneck: CPU, IO, memory, or widespread blocking?

SELECT metric_name, value FROM v$sysmetric WHERE metric_name IN ('Host CPU Utilization (%)','Average Active Sessions') AND intsize_csec=(SELECT MAX(intsize_csec) FROM v$sysmetric);

• CPU close to 100%
• High IO wait
• Memory pressure / low cache hit
• Lots of blocking

Root cause: Blocking

The query is waiting on a lock held by another session. Find the session at the head of the chain (root blocker) and resolve it.

Root cause: IO bottleneck

The query reads a lot from disk. Usually due to full scans (missing index) or insufficient memory (buffer cache).

Root cause: Commit / log bottleneck

If 'log file sync' / 'WRITELOG' dominates, commits are waiting on redo/WAL writes.

Root cause: Missing index

The plan does a full scan. A suitable index on filter/join columns gives a large speedup.

Root cause: Plan regression

The query was fast yesterday but not today; the plan changed. Usually stale statistics or a parameter change.

Root cause: Query design

The plan is reasonable but the query inherently does too much work (too many rows, N+1, unnecessary joins).

Root cause: Stale statistics

The optimizer misestimates because statistics are not up to date.

In-depth analysis

If the plan and statistics are fine but it is still slow, dig deeper.

Root cause: CPU bottleneck

The whole system is CPU-bound. Usually a few badly planned queries are burning the CPU.

Root cause: Memory pressure

Low cache hit and/or swapping: memory is insufficient or misallocated.