Db file scattered read
                                                 Last update (2008-05-13 14:27:07)
                                                                                                                   Date added (2008-05-13 13:01:19)

Summary
The db file scattered read wait event means that Oracle is waiting while doing a multiblock IO to complete. This is the case during full table scans or fast full index scans. Oracle can read multiple blocks (up to the initialization parameter db_file_multiblock_read_count) and scatters them into buffers in the buffer cache.

The parameter db_file_multiblock_read_count depends on the platform and the release of Oracle you are running. For exaple for Unix and Linux Systems a good value is 8.

Like all wait events the columns P1, P2, P3 give us the information needed to diagnose the waiting.
 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.

                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 scattered 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#. 

 Wait Time:	
    The wait blocks until all blocks in the IO request have been read.
Some advise
If you see this wait event then general you are NOT in a good position. It is very usual some databases to have wait events and doing IO for a full table or index scan, but can be avoided

START considering to create an index instead of full table scan, if this is not posible, try to imporove the full table or index scan.

How to improve performance for db file scattered read
The steps are, starting from that might come with better results:

1. Move the table to reset table High Water Mark
2. Use Parallel processing
3. Use tablespace with bigger block size
4. If the table has small size change it to IOT
5. Distribute the table or index in different filesystems to reduce contention for I/O (Disk I/O)
Reviews
Categories
Filters
Search