DB file scattered read wait event in Oracle

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.

Explanation
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:

  1. 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
  1. Placing tables in the keep buffer pool as appropriate to avoid aging out.
  2. Use the Parameter optimizer_index_cost_adj.
  3. Add indexes to proper tune the SQL statement which cause less I/O operation.
  4. Gather latest statistics and check it reduce I/O in execution plan for SQL queries.
  5. Create materialized view for avoid extra reads.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.