Bind Peeking

I’ve just received an email from someone who had been on my masterclass, and he had a question about one of the comments I had made, namely:

“bind variable peeking is always done, even if histograms are not generated”.

They quoted a reference to an Oracle white paper [ed: now deleted or moved] which said, on page 15:

In contrast, a condition like order_id = :1 will not trigger bind peeking assuming that order_id does not have histograms. (It may, for instance, be a primary key column, in which case histograms are not beneficial.)

Now the argument in the white paper is basically sound – for a primary key or, in the absence of histograms, for a non-unique column the selectivity of column = constant (whether literal or bind) is set by the value of user_tab_columns.density. So the question arose: could I actually prove that I was right.

To my embarassement, I realised that I had never actually tried to prove my claim – Oracle’s original introduction to bind variable peeking had said that “we peek at bind variables” so I had assumed that this statement was true.

Fortunately, 10g makes it very easy to prove the point. Here are a couple of extracts from the new, improved, 10053 (cost based optimizer) trace file you get from 10gR2.

 
****************************************** 
Current SQL statement for this session: 
select n1 from t3 where id = :m_n1 
*******************************************    

******************************************* 
Peeked values of the binds in SQL statement 
******************************************* 
kkscoacd 
Bind#0 
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 
oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0 
kxsbbbfp=080ff6ac bln=22 avl=02 flg=05 
value=15 

You will have to trust me, of course, that this query was for primary_key_column = constant. But quite clearly, Oracle has peeked at the value.

Footnote

The argument in the white paper does have a couple of holes. What if the referenced column also appears in a join predicate; what if the bound value falls outside the low/high range ? There are still (notionally) cases where peeking is necessary.

On a more pragmatic note, however, I suspect that peeking takes place because (a) it doesn’t cost much and (b) it would be more complicated to write code that had to decide not to peek.