Buffer busy waits of Oracle waiting for event

Buffer busy waits (hot block contention)

The official website explains:

This wait indicates that there are some buffers in the buffer cache that multiple processes are attempting to access concurrently. Query V$WAITSTAT for the wait statistics for each class of buffer. Common buffer classes that have buffer busy waits include data block, segment header, undo header, and undo block.

Check the following V$SESSION_WAIT parameter columns:

P1: File ID

P2: Block ID

P3: Class ID

Explanation:

This wait indicates that there is a buffer in the buffer cache that multiple processes are trying to access at the same time. Query V$WAITSTAT wait statistics for each type of buffer. A common buffer with buffer busy waiting includes a data block, a segment header, an undo header, and an undo block.

The generation of this wait event only indicates that a session is waiting for a Buffer (data block).

There are two situations:

(1) when a session attempts to modify a Buffer, but the Buffer is being modified by another session.

Hot blocks are typical of resource contention. Only by analyzing the causes of hot blocks can we prescribe the right remedy to the case: the following are the places where hot blocks occur:

① table block ② index block, segment header block (free list), undo block, etc.

(2) when a session needs to read a Buffer and the Buffer is being read from disk to memory by another session.

In the 11g version, this situation has been isolated, with waiting events named after read by other session.

Buffer busy waits wait events are common when there are hot blocks in the database, and occur when multiple users frequently read or modify the same data block.