This wait indicates that we are waiting for a read operation which reads a number of contiguous blocks from disk.
In most cases this indicates that there is some form of full scan (index fast full or full table scan) involved.
This event signifies that the user process is reading buffers into the SGA buffer cache and is waiting for a physical I/O call to return. A db file scattered read issues a scattered read to read the data into multiple discontinuous memory locations.
A scattered read is usually a multiblock read.
It can occur for a fast full scan (of an index) in addition to a full table scan.
The db file scattered read wait event identifies that a full scan is occurring.
When performing a full scan into the buffer cache, the blocks read are read into memory locations that are not physical.
When a SQL Statement causes a full scan, oracle process reads DB_FILE_MULTIBLOCK_READ_COUNT consecutive blocks at a time and scatters them into buffers in the buffer cache. Since a large no. of blocks have to be read into the buffer cache, server process has to search for a large no. of free/usable blocks in buffer cache which leads to wait included in db file scattered read wait.
User Action
If the TIME spent waiting for multiblock reads is significant, then it is helpful to determine against which segments Oracle is performing the reads.
The files where the reads are occurring can be found by looking at the V$FILESTAT view where BLKS_READ / READS > 1 .
(A ratio greater than one indicates there are some multiblock reads occurring).
It is also useful to see which sessions are performing scans and trace them to see if the scans are expected. This statement can be used to see which sessions may be worth tracing:
SELECT sid, total_waits, time_waited
FROM v$session_event
WHERE event='db file scattered read'
and total_waits>0
ORDER BY 3,2
;
You can also look at:
Statements with high DISK_READS in the V$SQL view
Sessions with high table scans blocks gotten in the V$SESSTAT view
The cure for the db file scattered read waits is the same as the one for the db file sequential read:
* find and tune the SQL statement that clocked the most time on the event.
The goal of tuning is also to minimize the number of logical and physical I/Os.
