[ Oracle DB Wait Events Series ] - DB File Scattered REad Wait Event

 DB File Scattered Read

The DB File scattered Read wait event occurs when an Oracle Database session waits for a physical multi-block I/O request to read data from disk into the buffer cache. It is a normal part of Database operation, but it becomes a performance bottlenexk if it dominates your database wait time.

Why is it called scattered?

While the blocks are read from disk sequentially in a single large contiguous operation, they are placed into buffer cache slots that are scattered throughout memory (discontinuous locations).

Possible Root Causes

1. Full Table Scans (FTS) - The database is reading an entire table block-by-block because no usable index exists.
2. Index Fast Full Scans (FFS) - The database is reading all the blocks of an index instead of navigating the index tree structure.
3. Poor SQL Execution Plans - The Oracle optimizer chooses a full scan instead of an index lookup due to stale statistics or falwed query logic.
4. Slow I/O Subsystems - The storage tier takes too long to fulfil the multi-block requests.

How to Trobleshoot and Fix

1. Tune the SQL Statements
• Check the Execution plan of the SQL Query to see what it is using Full Table Scan.
• Add missing indexes to turn a Full Table Scan into a highly efficient signle-block index lookup.
• Ensure Table and index statistics are updated using DBMS_STATS so the optimizer can make the right decision.
2. Verify Index Usability
• Check if an index was unintentionally dropped, corrupted, or marked as unusable. Rebuild unusable indexes.
3. Adjust DB Parameters
• Review the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter. Seting it too high can make the optimizer think full table scans are cheaper than they actually are, leading to excessive scattered read. Leaving it to the Oracel default is generally recommendded.
4. Optimize Memory and Storage
• Increase the size of the buffer cache (SGA). If the database has enough memory to keep frequently accessed data cached, it wont need to perform repetitive physical disk reads.
• If the physical I/O latency is high (e.g. average wait time > 10ms), investigate hardware bottlenecks or move hot datafiles to faster storage subsystems like SSDs.

Query to check if you have any DB File scattered Read Wait events currently happenning in the database:

SELECT 

      sid, username, status, sql_id, state, 

      seconds_in_wait, p1, p2, p3

 FROM v$session

WHERE state='WAITING' AND event='db file scattered read';

The wait time is the actual time it takes to do all of the I/Os.

p1 - file# - The file number from where the blocks are being read.

p2 - block# The starting block number of the multi-block read.

p3 - blocks - The number of blocks that the session is trying to read from the file#, starting at block#.