In addition to SQL tuning, it may also be worthwhile to check the index’s clustering factor if the execution plan calls for table access by index rowid. The clustering factor of an index defines how ordered the rows are in the table. It affects the number of I/Os required for the whole 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. 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. In this case, it is unlikely for the index entries in the same leaf block to point to rows in the same data block, 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. What happens if the table has more than one index Well, that is the downside. You can only cater to the most used index.

Also check to see if the application has recently introduced a new index using the following query. The introduction of a new index in the database may cause the optimizer to choose a different execution plan for SQL statements that access the table. The new plan may yield a better, neutral, or worse performance than the old one.

cluster_factor――表明有多少邻近的索引条目指到不同的数据块。如果表里的数据与索引排序是相似的，聚簇因子就小，最小值是表里非空的数据块总数。如果表里的数据和索引排序迥异，聚簇因子就大，最大值是索引中的键数。值越小，越表明表行是有序存储的

select owner, 
       substr(object_name,1,30) object_name, 
       object_type, 
       created
from   dba_objects
where  object_type in ('INDEX','INDEX PARTITION')
order by created;

The OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING initialization parameters can influence the optimizer to favor the nested loops operation and choose an index access path over a full table scan. The default value for the OPTIMIZER_INDEX_COST_ADJ parameter is 100. A lower value tricks the optimizer into thinking that index access paths are cheaper. The default value for the OPTIMIZER_INDEX_CACHING parameter is 0. A higher value informs the optimizer that a higher percentage of index blocks is already in the buffer cache and that nested loops operations are cheaper. Some third-party applications use this method to promote index usage. Inappropriate use of these parameters can cause significant I/O wait time. Find out what values the sessions are running with. Up to Oracle9i Database, this information could only be obtained by tracing the sessions with the trace event 10053 at level 1 and examining the trace files. In Oracle Database 10g, this is as simple as querying the V$SES_OPTIMIZER_ENV view.