等待事件--db file scattered reads

linfeng_oracle發表於2013-08-01
事件
等待事件--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.
db file scattered read Reference Note# 34558.1
 
 
 
 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24996904/viewspace-767602/，如需轉載，請註明出處，否則將追究法律責任。

相關文章