Oracle Wait Events Reference - Diagnose & Fix Database Waits

Wait events tell you exactly where Oracle sessions spend their time. This reference covers the wait events DBAs encounter most, with diagnostic queries and tuning guidance for each one.

Finding Your Top Wait Events

Start by identifying which waits dominate your system:

-- Top system-wide wait events (excluding idle)
SELECT event, total_waits, time_waited_micro/1000000 AS time_waited_sec,
       ROUND(average_wait * 10, 2) AS avg_wait_ms
FROM v$system_event
WHERE wait_class != 'Idle'
ORDER BY time_waited_micro DESC
FETCH FIRST 15 ROWS ONLY;

-- What active sessions are waiting on right now
SELECT event, COUNT(*) AS sessions_waiting
FROM v$session
WHERE status = 'ACTIVE' AND wait_class != 'Idle'
GROUP BY event
ORDER BY sessions_waiting DESC;

For a deeper introduction to wait event analysis methodology, see the Oracle Wait Events Guide.

I/O Wait Events

db file sequential read - Single Block I/O — index reads and slow storage
db file scattered read - Full Table Scans — multiblock reads and missing indexes
direct path read - Direct Path I/O — serial and parallel direct reads
db file parallel write - DBWR Writes — database writer performance
read by other session - Concurrent Block Reads — hot block I/O contention
cell single block physical read - Exadata I/O — storage cell reads and flash cache

Redo & Commit Wait Events

log file sync - Commit Performance — the classic commit-latency wait
log file parallel write - LGWR Writes — redo log I/O performance
log buffer space - Redo Buffer Sizing — redo generation outpacing LGWR

Lock & Contention Wait Events

enq: TX - row lock contention — blocking sessions and row locks
enq: TM - table lock contention — DDL blocking and unindexed foreign keys
buffer busy waits - Buffer Contention — hot blocks and segment headers
free buffer waits - DBWR Issues — buffer cache pressure

Parse & Latch Wait Events

Oracle Latch Waits - Shared Pool & Buffers — latch contention diagnosis
library cache lock - DDL Contention — DDL vs. parse blocking
library cache pin - Compilation Waits — package recompilation blocking
cursor: pin S wait on X - Mutex Contention — hard parse storms

RAC & Parallel Wait Events

gc buffer busy - RAC Global Cache — interconnect and hot block issues
Parallel Query Waits - PX Allocation — PX server allocation and downgrades

Idle & Network Wait Events

SQL*Net message from client - Idle Waits — when to ignore it, when not to

These utility scripts help you capture wait data quickly:

For AWR-based historical analysis, see the Performance Tuning Cheat Sheet.