Tuesday, August 27, 2013

Db file sequential read

Db file sequential read
                                                

Summary
The db file sequential read wait event means that Oracle is waiting while doing a single-block I/O read. This is the case when reading an index.

Like all wait events the columns P1, P2, P3 give us the information needed to diagnose the waiting.

Tip: A db sequential read is usually a single-block read, although it is possible to see sequential reads for more than one block (See P3 which is the number of blocks read).
This wait may also be seen for reads from datafile headers (P2=1 indicates a file header read) .
 Parameters: 
    P1 = file#
    P2 = block#
    P3 = blocks

 file#   This is the file# of the file that Oracle is trying to read 
                from. In Oracle8/9 it is the ABSOLUTE file number.

        block#  This is the starting block number in the file from where 
                Oracle starts reading the blocks. Typically only one block is
  being read.

                To find the object that Oracle doing the I/O use one of       
                the two following ways
                 
                SELECT owner, segment_type, segment_name, partition_name,      
                tablespace_name 
         FROM dba_extents 
         WHERE :P2 BETWEEN block_id AND (block_id + blocks - 1) 
         AND file_id = :P1;

                Or even better
                SELECT a.SID, c.obj, c.FILE#, c.dbablk 
         FROM v$session_wait a, x$bh c 
         WHERE a.p1 = c.FILE#(+)
         AND a.p2 = c.dbablk(+) 
         AND a.event = 'db file sequential read'
         AND a.SID = :sid_waiting; 

        blocks  This parameter specifies the number of blocks that Oracle is 
                trying to read from the file# starting at block#. This is
  usually "1" but if P3 > 1 then this is a multiblock read.
  Multiblock "db file sequential read"s may be seen in 
  earlier Oracle versions when reading from a SORT (TEMPORARY) 
         segments. 

 Wait Time: 
    The IO is generally issued as a single IO request to the OS - the wait 
    blocks until the IO request completes.
    Note than an Oracle read request to the OS may be satisfied from an
    OS file system cache so the wait time may be very small.
Some advise
If you see this wait event then general you are in a good position. All the databases (especially the very big systems) have some wait events and doing IO for an index scan is usual.

But SEC (seconds in waiting) from this query must be 0 or close to zero (1,2,3,4). If you see time waiting to increasing then you must tune the index I/O

How to improve performance for db file sequential read
The steps, starting first from that might have better results, are:

1. Rebuild the index (Fast index rebuild)
***To eliminate this case you have to examine the state of the index. 
If it is "compact" with no empty holes then there is no need to rebuild it.
 Find index skewed, rebuild
 Find indexes browning, rebuild

2. Tune SQL to use a better index
3. Distribute the index in different filesystems to reduce contention for I/O (Disk I/O)
4. Increase the db_cache_size

Embercadero has really nice explanation on this,try below site:

https://sites.google.com/site/embtdbo/wait-event-documentation/oracle-io-waits

No comments :

Post a Comment