# Database Discussions > Oracle >  db file scattered read

## jkoopmann

What is the "db file scattered read" a symptom of?
1. index read
2. Dyslexia
3. Failed disk drive
4. Full table scan

If you can answer this question click 
*Click Here to Take a Quiz*

_And get some free stuff!_

----------


## vicks79

Hi

db file scattered read generally indicates waits related to full table scans. As full table scans are pulled into memory, they rarely fall into contiguous buffers but instead are scattered throughout the buffer cache. A large number here indicates that your table may have missing or suppressed indexes. Although it may be more efficient in your situation to perform a full table scan than an index scan, check to ensure that full table scans are necessary when you see these waits. Try to cache small tables to avoid reading them in over and over again, since a full table scan is put at the cold end of the LRU (Least Recently Used) list.

----------


## safi_dba

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)

----------


## skhanal

Wow responding to a 4 years old post.

----------

