Can Bind Variable Peeking Cause Problems even with no Histograms on the Predicate Columns

Author's name: see below

Author's Email: see below

Date written: 14.7.2006

Oracle version(s): 10.2.0.1.0

It is a well known fact that the reason for the majority of problems with the bind variable peek feature is an histogram on the column referenced in the access predicate. The histogram is certainly the main but not the only cause for a non appropriate execution plan. Another less known situation where a different value of bind variables can lead to a change of execution plan is an access predicate on a (sub)partitioned table. This is a particularly important scenario in case of range partitioned fact tables organized as rolling windows. These tables contain two types of partitions, those filled up and those pre allocated to future loads. As the optimiser statistics of both types are very different, the risk of getting the wrong execution plan in case of peeking “in the wrong partition” is relatively high.

Author

Title/URL

Suggested by

Referee's comments

Jaromir D.B.Nemec

Happy New Peek

Author

This is a true story with all kind of exciting attributes:
large partitioned tables with proper collected statistics on them,
nothing was changed but a nightly job performance explodes,
no histogram on the access predicate column,
Oracle 9i / 10g in a data warehouse environment