I wrote this post because I feel there is a great need for it. The number of people struggling with unstable query plans due to bind peeking in Oracle 10G is enormous, to say the least. More than that, solutions like disabling bind variable peeking are driving us away from understanding the root cause of the problem and applying the right fix to it.

What are the causes of unstable plans due to bind variable peeking?

There 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. I’ll cover last two in upcoming blog posts.

Histograms

Let me share with you my déjà vu. When I see this:

SQL> select value
  2  	from v$parameter
  3  	where name='_optim_peek_user_binds';

VALUE
--------------------
FALSE

I immediately expect this:

SQL> select	sum(case when max_cnt > 2 then 1 else 0 end) histograms,
  2  		sum(case when max_cnt <= 2 then 1 else 0 end) no_histograms
  3  from (
  4  	select table_name, max(cnt) max_cnt
  5  		from (
  6  			select table_name, column_name, count(*) cnt
  7  				from dba_tab_histograms
  8  				group by table_name, column_name
  9  		) group by table_name
 10  );

HISTOGRAMS NO_HISTOGRAMS
---------- -------------
      1169          2494

The above is an example from a real-world OLTP system running with bind peeking disabled. It is no surprise to me. An exception, you say? Here’s another one . . . (more…)