There is an old database that was upgraded from Oracle 10 to 11G R1. In Oracle 10 we had disabled bind peeking. Since Oracle 11 has adaptive cursor sharing, we want to turn bind peeking back on. When we did this we found that many queries went much much faster. Unfortunately, one critical query got very slow. As soon as we turn off bind peeking, the one query gets fast again but everything else goes back to being sluggish.

The question is: In Oracle 11, what would cause bind peeking to make a query slow? I thought adaptive cursor sharing was supposed to take care of the bad bind variable peeks problem.

  • 1
    Without any specific technical details (the query plan, are you using histograms, what indexes, cardinality... I could go on), this is unanswerable. – Philᵀᴹ Oct 15 '13 at 21:50
  • I realize the technical details are missing. I was wondering if there are general theories that might apply to this situation. I can't find much on the web about anyone having bind peek problems in Oracle 11. I'm looking for anyone with practical experience in O11/bind peeking who might have ideas on next steps or things to research. – pandoh Oct 16 '13 at 15:24

Bind peeking is done in stages. First the query is be flagged as "bind sensitive" based on histograms, and then it may become "bind aware". The IS_BIND_SENSITIVE and IS_BIND_AWARE columns in V$SQL will show the state of the query. If IS_BIND_AWARE is not on, the query is not adapting.

This post helps to explains the process.

| improve this answer | |

I eventually got the answer from Oracle support. In a word: statistics.

Short answer: giving the optimizer more information (with bind peeking) resulted in a poor execution plan because it based its estimates on out of date statistics.

Long answer: assume a table with various columns including a date column. When bind peeking is turned off and I have a query like "select * where date >= :d1 and date <:d2", Oracle doesn't peek at the value of :d1 and :d2. It just makes its best guess and estimates the row count. In our case this led to estimates which were "good enough".

If bind peeking is turned on, Oracle looks at the value of :d1 and d:2 and then examines the statistics including the low/high values for the index on the date column. If the values of :d1 and :d2 are outside of the range given in the low/high values then it estimates a row count of 1. In other words, the out-of-date statistics report that low value for the column might be "1/1/2010" and the high value was "10/1/2013", but the date range in the query was "10/20/2013-10/25/2013". Oracle estimates 1 row is returned rather than the actual 50000, and a poor plan is chosen.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.