db file sequential read and db file scattered read

db file sequential read and db file scattered read

The db file sequential read and db file scattered read are I/O related wait events.

1. Determine the object name and partition name (if applicable) using the P1 and P2 values supplied by the CURRENT_EVENT cursor:

select segment_name, partition_name

from   dba_extents

where   between block_id and (block_id + blocks - 1)

and    file_id = ;

Querying the DBA_EXTENTS view in this manner can be slow, especially when many sessions are waiting on db file sequential read and db file scattered read events. This is because DBA_EXTENTS is a complex view that is comprised of many views and base tables. The performance gets worse when the number of extents in the database is high. The following are two alternatives that improve performance.

Precreate a working table (regular heap or global temporary table) with the same structure as the DBA_EXTENTS view. Then take a snapshot of the DBA_EXTENTS view once at the beginning of each sampling interval (or once a day depending on the number of extents and their volatility) and insert the data into the working table. In this case, the preceding query can be rewritten to go against the working table instead of the DBA_EXTENTS view.

Obtain the object number (OBJ) from the X$BH view using the P1 and P2 from the CURRENT_EVENT cursor. With the object number, you can resolve the object name (NAME) and subobject name (SUBNAME) by querying the DBA_OBJECTS view. An example of the query follows. The caveat to this method is that you must wait for the block to be read into the SGA; otherwise, the X$BH view has no information on the block that is referenced by the P1 and P2. Also, you may not be quick enough to catch the blocks that are being read in by a full table scan operation. This is because full table scans against NOCACHE objects do not flood the buffer cache. A small number of blocks are quickly reused. By the time you get the P1 and P2 values from the V$SESSION_WAIT view, the block in X$BH could have been reused by another block from subsequent reads.

select distinct a.object_name, a.subobject_name

  from   dba_objects a, sys.x$bh b

  where  (a.object_id = b.obj or a.data_object_id = b.obj)

   and    b.file#  = 

   and    b.dbablk = ;

2. Obtain the SQL statement that is associated with the db file sequential read or db file scattered read event using the hash value supplied by the CURRENT_EVENT cursor:

select hash_value, address, piece, sql_text

from   v$sqltext 

where  hash_value = 

order by piece;