Buffer Busy Wait and Read by Other Session in Oracle

Assume a block is accessed and pinned in the buffer cache by Session 1 (each buffer can be pinned by only one session at a time). While this block is pinned, if another session attempts to access the same block, it must wait. This type of wait is called Buffer Busy Wait.

What distinguishes Buffer Busy Wait from some other wait events is that the contention occurs at the block level. Even if the two sessions intend to access different rows within the same block, they are still competing at the block level.

As you know, Oracle blocks can contain different types of data. For example, one block may hold user data, while another may store control information. Therefore, contention for blocks can be divided into several categories:

• Data Block
• Data Segment Header
• Undo Header
• Undo Block Let’s briefly review the role of each block type in this wait event.

1) Data Block
This refers to the blocks that store object data. Buffer Busy Waits at this level can have several causes:

• Poorly tuned queries: Try to minimize the number of blocks read by each session.
• Hot block: A block containing frequently accessed rows can become a bottleneck. You should optimize such blocks (techniques are explained later).
• **Frequent cache misses on hot blocks: **If a block is not present in the buffer cache and two sessions attempt to access it, the second session must wait until the first session brings it into memory.

• Before Oracle 10.1, this was counted as Buffer Busy Wait, but since 10.1, it is reported as Read by Other Session.

• Best practice: Keep tables containing hot blocks cached in the buffer cache.

• Large block size: Larger blocks hold more rows, increasing the likelihood of simultaneous access. To reduce contention, you can:

• Rebuild the object with a higher PCTFREE.

• Use a smaller block size so fewer rows are stored per block.

• Index contention:
  If the problem is caused by an index, use hash partitioning or a reverse key index. Reversing the index key redistributes rows across blocks, reducing block-level contention.
  To identify the tables and indexes suffering from buffer busy waits, you can use the following query:
  

SELECT owner,
       object_name,
       l.object_type,
       SUM(value) buffer_busy_count,
       ROUND(SUM(value) * 100 / SUM(SUM(value)) OVER(), 2) pct
FROM   v$segment_statistics l
WHERE  statistic_name = 'buffer busy waits'
AND    value > 0
GROUP BY owner, object_name, l.object_type
ORDER BY SUM(value) DESC;

Example for rebuilding an index as reverse:

ALTER INDEX SALTEST.SPPA_INDX4 REBUILD REVERSE;

Alternatively, to generate rebuild scripts for all indexes with high contention:

SELECT 'ALTER INDEX ' || owner || '.' || object_name || ' REBUILD REVERSE;'
FROM   v$segment_statistics
WHERE  statistic_name = 'buffer busy waits'
AND    value > 0
AND    object_type = 'INDEX'
GROUP BY owner, object_name, object_type
ORDER BY SUM(value) DESC;

2) Data Segment Header
Each segment has a header block that stores information such as the high water mark and free list. Contention on this block may occur when:

• A session deletes data, requiring freelist updates.
• A session inserts data, increasing the high water mark.

• A new extent is allocated.
  Best practices to reduce waits:

• Partition the segment (creates multiple headers and reduces contention).

• Use larger extents to minimize frequent extent allocation.

• Use ASSM (Automatic Segment Space Management) tablespaces instead of freelist-managed ones.

3. Undo Header / Undo Block
When there are too few undo segments but a high number of concurrent transactions, contention may occur on undo headers. This is especially common when undo management is manual.

Undo block contention can also occur when two sessions try to modify data that generates undo information in the same undo block (hot block).

Solution:

• Increase the size of the undo tablespace.
• Tune the application to reduce excessive undo generation. You can identify which blocks are involved using:

SELECT p1 "File #", p2 "Block #", p3 "Reason Code"
FROM   v$session_wait
WHERE  event = 'buffer busy waits';

Then find the segment owning the block:

SELECT owner, segment_name, segment_type
FROM   dba_extents
WHERE  file_id = &P1
AND    &P2 BETWEEN block_id AND block_id + blocks - 1;

Read by Other Session
As mentioned earlier, if a block is not in the buffer cache and two sessions need it, the second session waits until the first session brings it into cache. This wait, previously classified as Buffer Busy Wait, is now reported as Read by Other Session in Oracle 10.1+.

If queries are not well optimized or if disk I/O is slow, this wait event can consume a noticeable portion of database time.

Demo for Buffer Busy Wait events
Test Setup (2K Block Size):
Oracle version: 19.28
Set db_2k_cache_size=500m and create a tablespace with 2K block size.

SQL> alter system set db_2k_cache_size=500m  ;
System altered.

SQL> alter session set container=pdb;
Session altered.

SQL> CREATE TABLESPACE tbs2k    DATAFILE SIZE 300M blocksize 2K;
Tablespace created

Create a table and insert data into it.

SQL> create table vahid.tbl2k(a number,b number) tablespace tbs2k   ;
Table created

begin
for I in 1..20111 loop
insert into vahid.tbl2k  values(I,i+10);
end loop;
commit;
end;
/

Capture AWR snapshots before and after running two concurrent

exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

sessions that update rows in the table.

--session 1
begin
for i in 1..20111 loop
  if i mod 2=0 then
update vahid.tbl2k   set a=i where a=i;
end if;
end loop;
commit;
end;
/

--session 2
alter session set container=pdb;
begin
for i in 1..20111 loop
  if i mod 2!=0 then
update vahid.tbl2k   set a=i where a=i;
end if;
end loop;
commit;
end;
/

AWR Report Results:

There is some information in the ‘Segments by Buffer Busy Waits’ section.

Key Takeaways

• Buffer Busy Wait = contention on a block already in cache.
• Read by Other Session = waiting for another session to complete a disk read and populate the cache.
• Block size, object design, and query optimization play a crucial role in reducing these waits.