Carlos Sierra's Tools and Tips

Every so often I get asked about this two statements:

1. Bind Peeking requires Histograms in order to generate multiple Execution Plans.
2. Freezing CBO Statistics freezes an Execution Plan.

Both are myths. The true is that you don’t have to have Histograms for a Plan using Bind Peeking to change. And even if you freeze your CBO Statistics your Plan may change.

Range Predicates and Bind Peeking

Consider Range Predicate “COLUMN > :b1“. Where column low/high values are “A” and “Z”. If value of bind :b1 is “A” at the time of a hard parse, the Selectivity of this Predicate is close to 1. If the CBO peeks the value of “Z” the Selectivity is close to zero. A variation of Selectivity like this may change an Execution Plan. Thus: “COLUMN does not require to have a CBO Histogram for Bind Peeking to produce multiple Execution Plans”.

Frozen CBO Statistics and Plan Stability

If your table contains columns based on sequences (like in a Primary Key), or if it contains DATE type columns, your Execution Plan may change even if the CBO Statistics do not change.

1. Consider Predicate “N1 = :b1” where N1 is a NUMBER type column based on a sequence with 100 distinct values and low/high values of 1 and 100. Then hard parse this SQL with values of 100 and 200. The first value may result in a Selectivity of 0.01 while the second my be close to zero. That is a significant change that may cause a plan to change.
2. Consider Predicate “D1 = :b2” where D1 is a DATE type column with 100 distinct values and low/high of 2013/01/01 and 2013/04/17. Then hard parse this SQL with values of 2013/02/14 and 2013/12/31. Same as the prior case, one value may render a selectivity close to 0.01 while the other close to zero.

The question in both cases above is: WHY would I pass a value out of range? As time progresses, users tend to pass more recent values and since CBO Statistics are frozen, these peeked values become out of low/high range and departing from their low/high end over time. Thus, the Predicate Selectivity diminishes gradually over time. At some point your otherwise stable Execution Plan may suddenly change. Thus: “Freezing CBO Statistics does not freeze Execution Plans”.

Conclusion

Since lack of Histograms or freezing CBO Statistics do not guarantee Plan Stability, do not rely on these two myths. If what you are looking for is Plan Stability use then SQL Plan Management available since 11g or SQL Profiles available from 10g.