DB file scattered read wait event

This event signifies that the user process is reading buffers into the SGA buffer cache and is waiting for a physical I/O call to return. A db file scattered read issues a scattered read to read the data into multiple discontinuous memory locations. A scattered read is usually a multiblock read. It can occur for a fast full scan (of an index) in addition to a full table scan.

The db file scattered read wait event identifies that a full scan is occurring. When performing a full scan into the buffer cache, the blocks read are read into memory locations that are not physically adjacent to each other. Such reads are called scattered read calls, because the blocks are scattered throughout memory. This is why the corresponding wait event is called 'db file scattered read'. multiblock (up to DB_FILE_MULTIBLOCK_READ_COUNT blocks) reads due to full scans into the buffer cache show up as waits for 'db file scattered read'

In one line we can describe scattered read  The Oracle session has requested and is  waiting for multiple contiguous database blocks to be read into the SGA from disk.

Cuase :

• Full Table scans
• Fast Full Index Scans
• Missing or unselective or unusable  index
• Table not analyzed after created index or lack of accurate statistics for the optimizer

If an application that has been running fine for a while suddenly clocks a lot of time on the db file scattered read event and there hasn’t been a code change, you might want to check to see if one or more indexes has been dropped or become unusable.

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

FROM v$session_wait

WHERE event IN ('db file scattered read')

;

Where P1,P2,P3 are

P1 - The absolute file number

P2 - The block being read

P3 - The number of blocks (should be greater than 1)

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;

.

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 ('db file scattered read')

AND

    w.CLASS#(+)=s.p3

   AND o.object_id (+)= s.row_wait_OBJ#

ORDER BY 1;

Find the related SQL statement  using sql_id

SELECT sql_text FROM   v$sqltext WHERE sql_id=&sq_id ORDER BY piece

You can also find the objects using below sql :-

Finding the SQL Statement executed by Sessions Waiting for I/O

SELECT SQL_ADDRESS, SQL_HASH_VALUE

  FROM V$SESSION

 WHERE EVENT ='read by other session';

Finding the Object Requiring I/O

SELECT row_wait_obj#

  FROM V$SESSION

 WHERE EVENT = 'db file scattered read';

To identify the object and object type contended for, query DBA_OBJECTS using the value for ROW_WAIT_OBJ# that is returned from V$SESSION. For example:

SELECT owner, object_name, subobject_name, object_type

  FROM DBA_OBJECTS

 WHERE data_object_id = &row_wait_obj;

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

• Optimize the SQL statement that  initiated most of the waits. The goal is
• to minimize the number of physical  and logical reads.
• Optimize multi-block I/O by setting the parameter DB_FILE_MULTIBLOCK_READ_COUNT i.e if DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter  values too high which favors full scans reduce it.
• Partition pruning to reduce number of  blocks visited
• Consider the usage of multiple buffer  pools and cache frequently used  indexes/tables in the KEEP pool
• Make sure that  the query use the right driving table?
• Are the SQL predicates appropriate  for hash or merge join?
• If full scans are appropriate, can  parallel query improve the response
• time?
• The objective is to reduce the demands for both the logical and
• physical I/Os, and this is best  achieved through SQL and application tuning.
• Gather statistics the related objects if they are missing  . Check the LAST_ANALYZED date from user_tables view