A very good article about bind peeking in Oracle Database 10G : http://www.pythian.com/news/867/stabilize-oracle-10gs-bind-peeking-behaviour-by-cutting-histograms/

exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');

here are three things that might put you at risk of unstable plans due to bind variable peeking. Those are histograms, partitions, and range-based predicates.

histograms and bind variable peeking can cause an unstable plan.

In 10G, bind variable peeking happens only on a hard parse, which means that all following executions will use the same plan, regardless of the bind variable value.

It might be a good idea to wipe out all histograms in a database (gather your stats with FOR ALL COLUMNS SIZE 1 clause), and manually add them when you decide that you really need one.


Niciun comentariu:

Trimiteți un comentariu