Read by other session wait event

“Read by other session wait event” wait event indicates a wait for another session to read the data from the disk into oracle buffer cache .The main cause for this wait event is

contention for "hot" blocks or objects .i.e several processes repeatedly reading the same blocks, e.g. many sessions scanning the same index or performing full table scans on the same table

When query is requested data from the database, Oracle will first read the data from disk into the  SGA. If two or more sessions request the same information,

the first session will read the data into the buffer cache while other sessions wait.

Before 10g this wait event knows as buffer busy wait.

Find the file_id and block_id using below query

SELECT p1 "file#", p2 "block#", p3 "class#"

FROM v$session_wait

WHERE event = 'read by other session';

Find the object name using  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;

You can also find the sql id and sql statement using below queries

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_text FROM   v$sqltext WHERE sql_id=&sq_id ORDER BY piece

Once you identify the hot blocks and the segments they belong to, and related quires then you reduce the using following solutions

• Tune the query
• Try to delete and reinsert the rowinside the block
• Reduce the amount of data into block by increase the pctfree  for table.
• Move table to samller blocksize tablespace.
• Check if any low cardinality indexes are being used, because this type
• of an index will make the database read a large number of data blocks into the buffer cache, . If possible, replace any low cardinality indexes with an index on a column with a high cardinality.

Buffer busy wait : http://adminoracle10g.blogspot.in/2012/12/buffer-busy-wait-event_28.html

.