db file scattered read

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!

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.

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)

Wow responding to a 4 years old post.