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.

· Or whether the stats has been stale.