Understanding Oracle’s Top 30 Wait Events: Causes, Parameters, and Tuning Tips

1. Buffer Busy Waits

The event indicates a session is waiting for a data block (buffer) that is currently being modified or read by another session. Common causes are concurrent modifications or reads of the same block. In newer versions the second case appears as "read by other session".

select name, parameter1, parameter2, parameter3 from v$event_name where name='buffer busy waits';

Parameters: file#, block#, class#.

2. Buffer Latch

Oracle protects the hash list (cache buffer chains) that stores block locations with a latch. When a session needs to access this list it must acquire the latch; contention occurs if the list is long or the same block is hot.

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

Parameters: Latch addr (virtual address of the latch in SGA).

3. Control File Parallel Write

When multiple control file copies exist, Oracle writes to all of them in parallel. Frequent writes are often caused by rapid log switches or I/O bottlenecks.

Files – number of control files written.

Blocks – number of blocks written.

Requests – number of I/O requests.

4. Control File Sequential Read

This wait occurs when Oracle reads control file information sequentially, typically during backup, RAC file sharing, or reading file headers.

File# – control file number.

Block# – starting block number.

Blocks – number of blocks read.

5. DB File Parallel Read

Although named "parallel", this wait appears during database recovery when Oracle reads multiple data files in parallel to restore blocks.

Files – number of files read.

Blocks – number of blocks read.

Requests – number of I/O requests.

6. DB File Parallel Write

Generated by the DBWR background process when it writes dirty buffers to disk in parallel. If accompanied by free buffer waits, it may indicate insufficient free memory.

Requests – I/O operations performed.

Timeouts – wait timeout duration.

7. DB File Scattered Read

Occurs when a user query reads many blocks (e.g., full table scan or index fast full scan). The term "scattered" refers to how the blocks are stored in memory after being read, not the I/O pattern.

File# – data file number.

Block# – starting block number.

Blocks – number of blocks read.

8. DB File Sequential Read

Triggered by operations that read a single block at a time, such as index lookups (excluding IFFS), rowid access, or control‑file reconstruction.

File# – data file number.

Block# – starting block number.

Blocks – should be 1.

9. DB File Single Write

Occurs when Oracle updates data‑file headers (e.g., during a checkpoint). A large number of files can increase the wait time.

File# – data file number.

Block# – block being updated.

Blocks – usually 1.

10. Direct Path Read

Session reads blocks directly into PGA, bypassing the SGA. Typical for temporary segment sorts, parallel execution, hash joins, or merge joins. High PGA usage or insufficient temporary space can cause this wait.

Descriptor address – pointer to the I/O descriptor.

First dba – oldest block address in the descriptor.

Block cnt – number of buffers involved.

11. Direct Path Write

Opposite of Direct Path Read: data is written from PGA directly to disk, bypassing SGA. Common during temporary tablespace sorts, direct‑load (APPEND), or parallel DML.

Descriptor address – pointer to the I/O descriptor.

First dba – oldest block address.

Block cnt – number of buffers involved.

12. Enqueue

"Enqueue" is another term for a lock. Long enqueue waits indicate blocking; the AWR enqueue activity section can identify the specific lock type.

Name – enqueue name/type.

Mode – enqueue mode.

13. Free Buffer Waits

When a session needs a free buffer in memory but none are available, or when constructing a consistent read image, this wait occurs. Causes include too small a buffer cache or excessive dirty buffers.

File# – data file number.

Block# – block number.

14. Latch Free

Prior to 10g, all latch waits were reported as Latch Free. Post‑10g, common latch events have been split out.

Address – latch address.

Number – latch number (lookup via v$latchname).

15. Library Cache Lock

Occurs when concurrent sessions contend for the same library‑cache object (e.g., a table undergoing DDL). The session must wait until the DDL completes.

Handle address – object address.

Lock address – lock address.

Mode – object data segment.

Namespace – namespace name from v$db_object_cache.

16. Library Cache Pin

Similar to Library Cache Lock but triggered when a session pins an object in the shared pool (e.g., recompiling PL/SQL) that another session is using.

Handle address – object address.

Lock address – lock address.

Mode – object data segment.

Namespace – namespace name.

17. Log File Parallel Write

LGWR writes redo buffers to multiple redo log members in parallel. Bottlenecks often stem from insufficient I/O bandwidth or poorly distributed redo members.

Files – number of redo files written.

Blocks – number of blocks written.

Requests – number of I/O operations.

18. Log Buffer Space

Occurs when the redo log buffer is full and LGWR cannot free space fast enough. Solutions include increasing LOG_BUFFER size or improving I/O performance.

19. Log File Sequential Read

Triggered when redo log information is read sequentially, such as during online redo archiving.

Log# – redo log sequence number.

Block# – block number.

Blocks – number of blocks read.

20. Log File Single Write

Occurs when LGWR updates the redo log file header (e.g., adding a new member or changing the sequence number).

Log# – redo log group number.

Block# – block number.

Blocks – number of blocks written.

21. Log File Switch (archiving needed)

In ARCHIVELOG mode, this wait appears when the next online log cannot be switched because the previous log has not yet been archived, often due to a stalled ARCH process.

No parameters.

22. Log File Switch (checkpoint incomplete)

When switching to a new online log, Oracle must ensure that all dirty buffers for the previous log have been checkpointed; otherwise the switch waits.

Active – log not checkpointed.

Inactive – log checkpointed.

Current – current log.

Typical remedies: increase log file size or add more log groups.

23. Log File Sync

Generated by a user session issuing COMMIT; the session waits for LGWR to write the redo to disk. High commit rates, slow I/O, oversized or undersized redo buffers, and inappropriate LOG_IO_SIZE settings can exacerbate this wait.

Buffer# – redo buffer to be written.

24. SQL*Net Break/Reset to Client

Server attempts to break or reset the connection to the client, usually due to unstable network conditions.

Driver id – protocol information.

Breaks – 0 for reset, non‑zero for break.

25. SQL*Net Break/Reset to DBLink

Same as above but occurs on a DBLink session between two databases.

Driver id – protocol information.

Breaks – 0 for reset, non‑zero for break.

26. SQL*Net Message from Client

Typical idle wait after the client sends a request and the server processes it, then waits for the next client message.

Driver id – protocol information.

#bytes – number of bytes received.

27. SQL*Net Message from DBLink

Idle wait on a DBLink session while the remote database sends a message.

Driver id – protocol information.

#bytes – bytes received from the remote server.

28. SQL*Net Message to Client

Server is sending a response to the client; the wait may be caused by a busy client or network issues.

Driver id – protocol information.

#bytes – bytes sent.

29. SQL*Net Message to DBLink

Server sends a message to a remote database over a DBLink; delays can stem from a busy remote server or network problems.

Driver id – protocol information.

#bytes – bytes sent.

30. SQL*Net More Data from Client

Server awaits additional data from the client to complete a large request (e.g., a long SQL statement).

Driver id – protocol information.

#bytes – bytes received so far.