Buffer Busy Waits wait events

Buffer busy waits occur when another session is reading the block into the buffer OR Another session holds the buffer in an incompatible mode to our request. This wait event was known as buffer busy wait event before oracle 10.

These waits indicate read/read, read/write, or write/write contention. The Oracle session is waiting to pin a buffer. A buffer must be pinned before it can be read or modified. Only one process can pin a buffer at any one time.

This wait can be intensified by a large block size as more rows can be contained within
the block This wait happens when a session wants to access a database block in the buffer cache
but it cannot as the buffer is "busy It is also often due to several processes repeatedly reading the same blocks (eg: if lots of people scan the same index or data block)

These waits are common in an I/O bound system. These wait events may indicates presence of hot blocks even in tuned queries and presence of un-selective or right hand indexes.

Queries in 10g to find the segments whose data blocks have read contention:

1. SELECT p1 "file#", p2 "block#", p3 "class#",sql_id
FROM v$session
WHERE event = 'buffer busy waits' and username='&USER_NAME';

Parameters P1 and P2 represents the file# and block# while parameter p3 represents the wait_class id. In 9i Parameter P3 represented reason code


2. Pass above selected file# and block# to below query to find to the affected segment

SELECT relative_fno, owner, segment_name, segment_type
FROM dba_extents
WHERE file_id = &FILE
AND &BLOCK BETWEEN block_id
AND block_id + blocks - 1;

This block of contention can belong to data block,segement header or undo block.

The main way to reduce buffer busy waits is to reduce the total I/O on the system by tuning the query Depending on the block type, the actions will differ

Data Blocks:

-Eliminate HOT blocks from the application.

-Reduce the number of rows per block( by moving table to tablespace with smaller block size or by below techniques)

-Try rebuilding the object with a higher PCTFREE so that you reduce the number of rows per block.

-Increase INITRANS and MAXTRANS and reduce PCTUSED This will make the table less dense .

-Check for repeatedly scanned /unselective indexes.

-Check for 'right- hand-indexes' (indexes that get inserted into at the same point by many processes).

Segemnt Header:
Use Automate segment management that is bit maps or increase of number of FREELISTs and FREELIST GROUPs

Undo Header:Increase the number of Rollback Segments

block contention wait events are also recorded in specific view V$WAITSTAT and since V$SESSION has all the wait events data integrated with it from 10g and it also have the row wait information, below query can also be used to find the sql statements.

SELECT
s.p1 file_id, s.p2 block_id,o.object_name obj,
o.object_type otype,
s.SQL_ID,
w.CLASS,event
FROM v$session s,
( SELECT ROWNUM CLASS#, CLASS FROM v$waitstat ) w,
all_objects o
WHERE
event IN ('buffer busy waits'')
AND
w.CLASS#(+)=s.p3
AND o.object_id (+)= s.row_wait_OBJ#
ORDER BY 1;

SELECT SQL_FULLTEXT from V$SQL WHERE sql_id=&amp

SQL_FULLTEXT is CLOB column which displays full query

ASH samples the active sessions every one second and so we can query v$active_session_history also to get buffer busy waits or read by other session.

SELECT
p1 file_id , p2 block_id ,o.object_name obj,
o.object_type otype,
ash.SQL_ID,
w.CLASS
FROM v$active_session_history ash,
( SELECT ROWNUM CLASS#, CLASS FROM v$waitstat ) w,
all_objects o
WHERE event='buffer busy waits'
   AND w.CLASS#(+)=ash.p3
AND o.object_id (+)= ash.CURRENT_OBJ#
AND ash.sample_time > SYSDATE - &MIN/(60*24)
ORDER BY 1;