Oracle problems

buffer busy waits: Find the Hot Block, and the Fix

3 November 2026

buffer busy waits is a block that is already in the buffer cache and that another session holds in a way this one cannot share; read by other session is the same block while another session is still reading it from disk. Oracle's reference lists them as two of the four reasons a session cannot pin a buffer, and until release 10.1 they were one event. Both mean the same thing for the DBA: many sessions want the same few blocks at the same moment. The block is usually the right-hand edge of an index fed by a sequence, a small table everyone updates, or a segment header handing out free space one session at a time. The first query names the block and its class, the second names the segment, and the fix depends on the class.

What the wait means

The reference puts it in one line, "Wait until a buffer becomes available", and then lists the four reasons, each with its own event: buffer busy waits when "another session has the buffer pinned", read by other session when "another session is reading the buffer from disk", and the two gc buffer busy events when the buffer is on its way from another RAC instance. The wait time for buffer busy waits is "Normal wait time is 1 second. If the session was waiting for a buffer during the last wait, then the next wait will be 3 seconds." The parameters are file#, block# and class#, and the class is the part that tells the cause: a data block, a segment header, an undo header and a free list block are different problems with different fixes.

Which block, and what kind

SELECT s.event, s.p1 AS file#, s.p2 AS block#, s.p3 AS class#,
       s.sql_id, COUNT(*) AS sessions
FROM   v$session s
WHERE  s.event IN ('buffer busy waits', 'read by other session')
GROUP  BY s.event, s.p1, s.p2, s.p3, s.sql_id
ORDER  BY sessions DESC;

One file and block with twenty sessions behind it is the hot block. The class names, with the waits since startup for each, are in V$WAITSTAT:

SELECT class, count, time
FROM   v$waitstat
WHERE  count > 0
ORDER  BY count DESC;

Then the segment that owns the block, and the statistic Oracle keeps per segment, which ranks the hot objects without catching them in the act:

SELECT owner, segment_name, segment_type, partition_name
FROM   dba_extents
WHERE  file_id = &file#
AND    &block# BETWEEN block_id AND block_id + blocks - 1;

SELECT owner, object_name, object_type, value AS buffer_busy_waits
FROM   v$segment_statistics
WHERE  statistic_name = 'buffer busy waits'
ORDER  BY value DESC FETCH FIRST 15 ROWS ONLY;

The sql_id in the first query is the statement all those sessions run, and for read by other session it is usually one query that twenty sessions started within the same second, each asking for the same blocks that none of them has yet.

The causes, by class

A data block in an index. The index is fed by a sequence or a timestamp, so every insert goes to the same rightmost leaf block, and a hundred sessions inserting at once queue on it. A data block in a table. A small table with rows everyone updates, a counter, a status row, a queue table, or many sessions inserting into the same block because the block had free space for all of them. A segment header or a free list block. Sessions inserting into a segment in a tablespace with manual segment space management queue on the free list; automatic segment space management spreads them. An undo header or undo block. Many sessions reading a block that other transactions keep changing, each one building its own consistent version from undo. read by other session. The block is cold and popular: after a restart, after a large scan pushed it out, or because the statement reads the same index range from every session.

The fix, in order

1. Read the class before you change anything. The class decides the fix, and the segment statistics say whether the block was hot for a minute or all week.
2. A sequence-fed index: spread the inserts. A scalable sequence, CREATE SEQUENCE ... SCALE, affixes a numeric offset to the beginning of each value, in the words of Oracle's reference, so consecutive inserts land in different leaf blocks; a hash-partitioned index does the same for an existing key; a reverse key index spreads them too but costs every range scan on that column. Raise the sequence cache so the sessions do not also queue on the sequence itself.
3. A hot table block: fewer rows in it. A higher PCTFREE, or a smaller block for that table, puts fewer hot rows in each block, so sessions updating different rows stop meeting on the same one. A counter row that every transaction updates is an application design that no storage setting fixes; a sequence or a row per session replaces it.
4. Free list contention: move to ASSM. A tablespace with automatic segment space management, which has been the default for a long time, replaces the free list with bitmap blocks and spreads the inserting sessions. A segment still in an old dictionary-managed or manual tablespace is moved with ALTER TABLE ... MOVE.
5. read by other session: fewer reads of the same block. The statement that every session runs needs a better access path, or its hot blocks need to stay in the cache, with the KEEP pool for a small segment or a buffer cache sized so the popular index does not fall out. Twenty sessions starting the same query in the same second is also an application behaviour, a dashboard or a job, that runs once and shares the answer.
6. Undo contention: shorter transactions. Sessions building consistent reads from undo are reading a block another transaction changed and has not committed; a long transaction that touches hot blocks and waits is the cause, and the row lock page has the same cure.
7. RAC: the same block on two instances. When the waits are gc buffer busy, the block travels between instances; partitioning the work by instance, or a sequence with a large cache and NOORDER, keeps each hot block on one node.

See it coming

Radar Engine, the detection layer of Scops, samples every session every 5 seconds and knows the usual size of each wait class at each hour, so a hot block that appears at the start of a campaign is a finding with the block, the segment and the statement while the queue is still ten sessions long:

The finding names the event, the statement behind most of the waiting sessions and the segment they meet on. The Activity chart draws the Concurrency class by statement and by object, on any edition and without the Diagnostics Pack.

Questions

What are buffer busy waits?
A session wants a block that is already in the buffer cache, and another session holds it in a way this one cannot share. Oracle's reference lists it as one of four reasons a buffer cannot be pinned; the usual cause is many sessions on the same few blocks, an index block fed by a sequence, a hot table row or a segment header.

What is read by other session?
The same situation while the block is still being read from disk: another session has already issued the read, and this one waits for it to arrive rather than reading it again. It was part of buffer busy waits before release 10.1. Many sessions starting the same query on cold blocks is the usual cause.

How do I find the hot block?
V$SESSION gives P1, P2 and P3 for the waiting sessions, which are the file, the block and the class; DBA_EXTENTS names the segment that owns the block; V$WAITSTAT names the class; and V$SEGMENT_STATISTICS ranks the segments by buffer busy waits since startup.

How do I fix buffer busy waits on an index?
Spread the inserts away from the rightmost leaf block: a scalable sequence, a hash-partitioned index, or a reverse key index when the column is never range-scanned, and a larger sequence cache. The fix is in the index and the sequence, not in the buffer cache size.

Related problems

• latch: cache buffers chains
• enq: TX - row lock contention
• db file sequential read waits
• RAC lost blocks and interconnect waits
• Oracle wait events, by class
• free buffer waits
• gc buffer busy acquire and release
• All Oracle problems

Sources

• Oracle: Descriptions of wait events, V$WAITSTAT, V$SEGMENT_STATISTICS, CREATE SEQUENCE
• Scops docs: Detector reference