read by other session wait events

Read by other session or buffer busy waits occur a 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 = 'read by other session' 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

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 ('read by other session')
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='read by other session'
   AND w.CLASS#(+)=ash.p3
   AND o.object_id (+)= ash.CURRENT_OBJ#
      AND ash.sample_time > SYSDATE - &MIN/(60*24)
ORDER BY 1;