The clustering_factor is very important for costing index range scans; but there are some features of Oracle, and some performance-related strategies, that result in an unsuitable value for the clustering_factor.
In many cases, we can predict the problems that are likely to happen, and use alternative methods for generating a more appropriate clustering_factor. We can always use the dbms_stats package to patch a correct clustering_factor into place.
If the clustering_factor is exaggerated because of multiple freelists, or the use of ASSM, then you can use Oracle’s internal code for generating the clustering_factor with a modified value for the second parameter of the sys_op_countchg() function to get a more realistic value.
If the clustering_factor is exaggerated because of reverse key indexes, added columns, or even column reordering, then you may be able to generate a value based on knowing that the real functionality of the index relies on a subset of the columns. If necessary, build the reduced index on the backup data set, generate the correct clustering_factor, and transfer it to the production index.
Adjusting the clustering_factor really isn’t hacking or cheating; it is simply ensuring that the optimizer has better information than it can derive (at present) for itself.
KeywordsWrong Index Execution Plan Concurrent Process Data Dictionary Cluster Factor
Unable to display preview. Download preview PDF.