等待事件--db file sequential reads

等待事件--db file sequential reads

 

Possible Causes

Use of an unselective index

Fragmented Indexes

High I/O on a particular disk or mount point

Bad application design

Index reads performance can be affected by slow I/O subsystem and/or poor database files layout, which result in a higher average wait time

 

Actions

Check indexes on the table to ensure that the right index is being used

Check the column order of the index with the WHERE clause of the Top SQL statements

Rebuild indexes with a high clustering factor

Use partitioning to reduce the amount of blocks being visited

Make sure optimizer statistics are up to date

Relocate ‘hot’ datafiles

Consider the usage of multiple buffer pools and cache frequently used indexes/tables in the KEEP pool

Inspect the execution plans of the SQL statements that access data through indexes

Is it appropriate for the SQL statements to access data through index lookups?

Is the application an online transaction processing (OLTP) or decision support system (DSS)?
 
Would full table scans be more efficient?

Do the statements use the right driving table?

The optimization goal is to minimize both the number of logical and physical I/Os.

Remarks

The Oracle process wants a block that is currently not in the SGA, and it is waiting for the database block to be read into the SGA from disk.
Significant db file sequential read wait time is most likely an application issue.
If the
DBA_INDEXES.CLUSTERING_FACTOR of the index approaches the number of blocks in the table, then most of the rows in the table are ordered. This is desirable.

 However, if the clustering factor approaches the number of rows in the table, it means the rows in the table are randomly ordered and thus it requires more I/Os to complete the operation. You can improve the index’s clustering factor by rebuilding the table so that rows are ordered according to the index key and rebuilding the index thereafter.

The OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING initialization parameters can influence the optimizer to favour the nested loops operation and choose an index access path over a full table scan.

Tuning I/O related waits Note# 223117.1

db file sequential read Reference Note# 34559.1

 

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