Understanding Oracle Wait Events: Buffer Busy Waits, Latches, and I/O Operations

Oracle databases generate a variety of wait events that indicate where sessions are blocked while accessing data. Recognizing these events helps DBAs diagnose performance bottlenecks.

1. Buffer busy waits

Occurs when a session waits for a buffer (data block) that is being modified or read by another session. Two main causes are concurrent modifications of the same block or a session reading a block that another session is loading into memory. In newer versions the second case appears as read by other session. This event is common for hot blocks and appears in AWR or Statspack reports.

select name, parameter1, parameter2, parameter3 from v$event_name where name='buffer busy waits';
NAME               PARAMETER1  PARAMETER2  PARAMETER3
------------------- ---------- ---------- ----------
buffer busy waits   file#       block#      class#

2. Buffer latch

Oracle protects the hash list of cached buffer chains with a latch. When a session accesses a block, it must acquire this latch. Long latch waits happen when the hash chain is long, causing sessions to spend excessive time searching, a typical symptom of hot blocks.

Parameters:

Latch addr : Virtual address of the latch in SGA (queryable via

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

).

Chain# : Index in the buffer chains hash list; a value of 0xfffffff indicates waiting for an LRU latch.

3. Control file parallel write

When multiple copies of the control file exist, Oracle writes updates to all copies in parallel. Frequent writes can be caused by rapid log switches or I/O bottlenecks.

Parameters:

Files – number of control files written.

Blocks – number of blocks written.

Requests – number of I/O requests.

4. Control file sequential read

Occurs when Oracle reads the control file sequentially, such as during backup, RAC instance sharing, or reading file headers.

Parameters:

File# – control file number.

Block# – starting block number.

Blocks – number of blocks read.

5. DB file parallel read

Used during recovery when Oracle reads data blocks from data files in parallel. It is unrelated to user‑initiated parallel queries.

Parameters: Files, Blocks, Requests.

6. DB file parallel write

Generated by the DBWR process when it writes dirty buffers to disk in parallel. Long waits may indicate insufficient free buffers; enabling asynchronous I/O can reduce the wait time.

Parameters: Requests, Timeouts.

7. DB file scattered read

Triggered by user operations that read multiple blocks per I/O, such as full table scans or index fast full scans. Despite the name, the read is sequential; "scattered" refers to how the blocks are stored in memory after being read.

Parameters: File#, Block#, Blocks.

8. DB file sequential read

Occurs when Oracle reads a single block per I/O, typical for index lookups, rollback, ROWID access, or control file dumps. "Sequential" indicates the blocks are stored contiguously in memory.

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

9. DB file single write

Happens when Oracle updates data file headers, e.g., during a checkpoint. Excessive waits may signal too many data files causing long header updates.

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

10. Direct path read

Data blocks are read directly into the session's PGA, bypassing the shared SGA. Common for temporary segment data, sort operations, and hash/merge joins. Long waits can indicate heavy temporary data generation or insufficient PGA space.

Parameters: Descriptor address, First dba, Block cnt.

11. Direct path write

The opposite of direct path read: sessions write data from PGA directly to disk, often during temporary tablespace sorts, direct‑load (APPEND), or parallel DML.

Parameters: Descriptor address, First dba, Block cnt.

12. Enqueue

Enqueue events represent lock waits. Long enqueue waits indicate blocking sessions; the AWR enqueue activity section helps identify the lock type.

Parameters: Name, 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 data buffer cache or excessive dirty buffers that DBWR cannot flush quickly.

Parameters: File#, Block#.

14. Latch free

Prior to Oracle 10g, latch free covered all latch waits; newer versions split common latch events out. Parameters: Address (latch address), Number (latch identifier, searchable via v$latchname).

15. Library cache lock

Occurs when concurrent sessions contend for the same database object in the library cache, such as when one session performs DDL on a table and others wait to access it.

Parameters: Handle address, Lock address, Mode, Namespace (from v$db_object_cache).

These wait events can be queried using the provided SQL snippets and examined in AWR/Statspack reports to pinpoint performance issues and guide tuning actions.