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.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. 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.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.Significant db file sequential read wait time is most likely an application issue.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.The optimization goal is to minimize both the number of logical and physical I/Os.Do the statements use the right driving table?.Would full table scans be more efficient?.Is it appropriate for the SQL statements to access data through index lookups?.Inspect the execution plans of the SQL statements that access data through indexes.Consider the usage of multiple buffer pools and cache frequently used indexes/tables in the KEEP pool.Make sure optimizer statistics are up to date.Use partitioning to reduce the amount of blocks being visited.Rebuild indexes with a high clustering factor.Check the column order of the index with the WHERE clause of the Top SQL statements.
Check indexes on the table to ensure that the right index is being used.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.High I/O on a particular disk or mount point.In this post I try to cover most popular of them. As working on performance tuning since more than 4 yrs there are very few wait events. Keep visiting/commenting!Īs there are over 800 wait events but but frequently you may come across very few. Note: One of our visitors and my friend Kavita Yadav asked this question by posting a comment.
0 kommentar(er)
