DB file scattered read wait
DB file scattered read wait event identifies that full table scans or index fast full scans are occurring. DB file scattered read means to read the data into multiple discontinuous memory location. Scattered read is the blocks are scattered throughout memory.
SQL Statement cause full table scan, then read with DB_FILE_MULTIBLOCK_READ_COUNT consecutive blocks at a time and scatters them into buffers in the buffer cache. It cause 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.
Solution to Reduce the Wait Event:
- Ensure the value of the DB_FILE_MULTIBLOCK_READ_COUNT parameter is correctly set.
DB_FILE_MULIBLOCK_READ_COUNT = [largest read size] / db_block_size
- Placing tables in the keep buffer pool as appropriate to avoid aging out.
- Use the Parameter optimizer_index_cost_adj.
- Add indexes to proper tune the SQL statement which cause less I/O operation.
- Gather latest statistics and check it reduce I/O in execution plan for SQL queries.
- Create materialized view for avoid extra reads.