Wait Event: db file scattered reads

Wait Event: db file scattered reads
Possible Causes:

• The Oracle session has requested and is waiting for multiple contiguous database blocks (up to DB_FILE_MULTIBLOCK_READ_COUNT) to be read into the SGA from disk.
• Full Table scans
• Fast Full Index Scans

Actions:

• Optimize multi-block I/O by setting the parameter DB_FILE_MULTIBLOCK_READ_COUNT
• Partition pruning to reduce number of blocks visited
• Consider the usage of multiple buffer pools and cache frequently used indexes/tables in the KEEP pool
• Optimize the SQL statement that initiated most of the waits. The goal is to minimize the number of physical and logical reads.
• Should the statement access the data by a full table scan or index FFS?
• Would an index range or unique scan be more efficient?
• Does the query use the right driving table?
• Are the SQL predicates appropriate for hash or merge join?
• If full scans are appropriate, can parallel query improve the response time?
• The objective is to reduce the demands for both the logical and physical I/Os, and this is best achieved through SQL and application tuning.
• Make sure all statistics are representative of the actual data.
• Check the LAST_ANALYZED date

Remarks:

If an application that has been running fine for a while suddenly clocks a lot of time on the db file scattered read event and there hasn’t been a code change, you might want to check to see if one or more indexes has been dropped or become unusable.
Reg:
db file scattered read Reference Note# 34558.1
gavinsoorma.com