Carlos Sierra's Tools and Tips

When we combine the use of bind variables in a SQL statement with column Histograms we usually end-up having multiple execution plans over time. This plan instability is due to the lack of control we have over the selectivity of the predicates at the time the SQL is hard parsed while using bind peeking. On 11g this behavior is mitigated with the use of Adaptive Cursor Sharing (ACS), and it can be further improved when using ACS together with SQL Plan Management (SPM).

As we generate multiple execution plans for one SQL statement where the reason is the combination of binds and Histograms, some of these plans may perform better than others over a large number of executions. Thus it is natural to consider some of these execution plans as “bad plans”. If the use of ACS and SPM is not an option, then we may need to consider fixing the plan instability issue with the suppression of either bind peeking or Histograms. If we are in such case, it is better to consider the removal of Histograms first, and leave bind peeking active. The reason is that bind peeking even with no Histograms still uses CBO column statistics and among other things it uses column low and high values when computing selectivity.

Back to the question: Should I delete my column Histograms to improve plan stability?

If you are on 10g, or if on 11g but ACS is not an option, then deleting column Histograms may be your best option to improve your plan stability. Like any other change, you rather test first on a non-production environment. Even in such test environment, you may want to restrict your test to only those tables accessed by your SQL. On 11g, DBMS_STATS.DELETE_COLUMN_STATS provides an option to delete just the Histograms while preserving all other column statistics. This is by using parameter COL_STAT_TYPE with value HISTOGRAM. If you want to do the same on 10g, you may want to use the SQLT HGRM module. In any case you can always restore column statistics using DBMS_STATS.RESTORE_TABLE_STATS.

If you are considering deleting Histograms to test the effect on an execution plan and the performance of your SQL, you may want to test first asking the CBO to simply ignore them. If patch for bug 9550277 has been applied in your system then you can command below.

ALTER SESSION SET "_FIX_CONTROL"='9550277:1';