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.
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,
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#.
The wait blocks until all blocks in the IO request have been read.
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)