Understanding Oracle Wait Events: A Deep Dive into 33 Common Waits

1. Overview of Wait Events

Oracle wait events are divided into two categories: idle (IDLE) events, which occur when the database is waiting for work and are generally not a focus during tuning, and non‑idle (NON‑IDLE) events, which occur during active database or application operations and require investigation.

The v$event_name view lists all wait events; Oracle 10g defines 872 events, while 11g defines 1,116 events. desc v$event_name; Key columns include EVENT#, EVENT_ID, NAME, PARAMETER1‑3, WAIT_CLASS_ID, WAIT_CLASS# and WAIT_CLASS.

2. Counting and Classifying Events

To see the total number of events: select count(*) from v$event_name; Classification by wait class:

SELECT wait_class#, wait_class_id, wait_class, COUNT(*) AS count FROM v$event_name GROUP BY wait_class#, wait_class_id, wait_class ORDER BY wait_class#;

Result example (truncated):

WAIT_CLASS# WAIT_CLASS_ID WAIT_CLASS   count
0          1893977003   Other          717
1          4217450380   Application    17
2          3290255840   Configuration  24
...

3. Related Performance Views

Important views for diagnosing wait events include:

V$SESSION – session start information.

V$SESSION_WAIT – current wait details for each session.

V$SESSION_WAIT_HISTORY – recent ten waits per session.

V$SQLTEXT – SQL text of sessions waiting on resources.

V$ACTIVE_SESSION_HISTORY (ASH) – sampled wait history, one sample per second, retained about one hour.

WRH#_ACTIVE_SESSION_HISTORY – AWR storage of ASH data (default one‑week retention).

DBA_HIST_ACTIVE_SESS_HISTORY – combined view for historical analysis.

V$SYSTEM_EVENT – cumulative wait information since instance startup.

4. Detailed Descriptions of 33 Common Wait Events

1. Buffer busy waits

Occurs when a session waits for a data block that is being modified or read by another session. The block‑level latch (Latch) protects the block during modification.

Typical steps:

Acquire exclusive latch on the block.

Modify the block.

Release the latch.

Parameters (query example):

SELECT name, parameter1, parameter2, parameter3 FROM v$event_name WHERE name = 'buffer busy waits';

Result shows PARAMETER1 = file#, PARAMETER2 = block#, PARAMETER3 = class#.

2. Buffer latch

Protects the hash list (cache buffer chains) that stores block locations. Long buffer chains or hot blocks cause contention.

Mitigation: increase DB_BLOCK_LRU_LATCHES or add more buffer pools.

Parameters: Latch address (addr) – can be resolved with:

select * from v$latch a, v$latchname b where addr = latch_addr and a.latch# = b.latch#;

3. Control file parallel write

Parallel writes to multiple control file copies during checkpoint or log switch. Causes include frequent log switches or I/O bottlenecks.

Mitigation: enlarge log files, reduce control file copies, place copies on separate disks.

Parameters: Files, Blocks, Requests.

4. Control file sequential read

Sequential reads of control files during backup, RAC sharing, or header reads.

Parameters: File#, Block#, Blocks.

5. Db file parallel read

Parallel reads during recovery; not related to parallel query.

Parameters: Files, Blocks, Requests.

6. Db file parallel write

Background DBWR writes of dirty buffers; may indicate insufficient free buffers when accompanied by free buffer waits.

Mitigation: enable asynchronous I/O.

Parameters: Requests, Timeouts.

7. Db file scattered read

Occurs with full table scans or index fast full scans; data blocks are read sequentially but stored scattered in memory.

Parameters: File#, Block#, Blocks.

8. Db file sequential read

Single‑block reads for index access, rollback, ROWID lookups, etc.

Parameters: File#, Block#, Blocks (usually 1).

9. Db file single write

Writes of data file headers, often during checkpoints.

Parameters: File#, Block#, Blocks (usually 1).

10. Direct path read

Reads data directly into PGA for temporary or sorting operations, bypassing SGA.

Parameters: Descriptor address, First dba, Block cnt.

11. Direct path write

Writes from PGA to disk, typical for temporary tablespace sorts, direct loads, or parallel DML.

Parameters: Descriptor address, First dba, Block cnt.

12. Enqueue

Lock‑related wait; long enqueue waits indicate blocking.

Parameters: Name, Mode.

13. Free buffer waits

Occurs when no free buffer space is available for reading or constructing consistent read images.

Causes: small data buffer, excessive dirty buffers.

Parameters: File#, Block#.

14. Latch free

Pre‑10g generic latch wait; after 10g specific latch events appear.

Parameters: Address, Number, Tries.

15. Library cache lock

Contention on shared objects (e.g., DDL on a table) causing sessions to wait.

Parameters: Handle address, Lock address, Mode, Namespace.

16. Library cache pin

Similar to lock, occurs when an object is pinned in the shared pool during recompilation.

Parameters: Same as library cache lock.

17. Log file parallel write

LGWR writes redo buffers to multiple redo log members in parallel.

Parameters: Files, Blocks, Requests.

18. Log buffer space

Waits when redo buffer is full and LGWR cannot keep up.

Mitigation: increase redo buffer size, improve I/O.

19. Log file sequential read

Reading redo logs sequentially, e.g., during archive.

Parameters: Log#, Block#, Blocks.

20. Log file single write

Updates to redo log file headers during log switches or sequence changes.

Parameters: Log#, Block#, Blocks.

21. Log file switch (archiving needed)

Online log switch while previous log not yet archived; may indicate ARCH process issues.

No parameters.

22. Log file switch (checkpoint incomplete)

Switch occurs before checkpoint completes; may require larger log files or more log groups.

No parameters.

23. Log file sync

Commit‑related wait for LGWR to flush redo buffer to disk.

Parameter: Buffer#.

24. SQL*Net break/reset to client

Server waiting for client response after sending a break or reset.

Parameters: Driver id, Break?

25. SQL*Net break/reset to dblink

Same as above but between databases via DBLINK.

Parameters: Driver id, Break?

26. SQL*Net message from client

Idle wait while server processes client requests.

Parameters: Driver id, #bytes.

27. SQL*Net message from dblink

Idle wait for messages from a remote database via DBLINK.

Parameters: Driver id, #bytes.

28. SQL*Net message to client

Server waiting to send data to client; may indicate client slowness or network issues.

Parameters: Driver id, #bytes.

29. SQL*Net message to dblink

Server sending data to a remote database; similar causes as client case.

Parameters: Driver id, #bytes.

30. SQL*Net more data from client

Server awaiting additional data (e.g., large SQL text) from client.

Parameters: Driver id, #bytes.

31. SQL*Net more data from dblink

Server awaiting more data from a remote database during distributed transaction.

Parameters: Driver id, #bytes.

32. SQL*Net more data to client

Server sending large result sets to client; may be limited by network.

Parameters: Driver id, #bytes.

33. SQL*Net more data to dblink

Server sending large data to remote database via DBLINK.

Parameters: Driver id, #bytes.