Insights into Statistics, Query Optimization and the Oracle Optimizer

  • November 15, 2017

Adaptive Cursor Sharing (ACS) and Bind Sensitivity

Nigel Bayliss
Product Manager

There’s a common misconception that queries with equality predicates will only become bind sensitive if there’s a histogram on relevant table columns. For example, it’s often thought that the following query will not be marked bind sensitive if SK_ID lacks a histogram:

SELECT sum(amount)
FROM   sales
WHERE  sk_id = :b1;

This looks like a very reasonable assumption. After all, if there’s no skew in SK_ID values then where’s the benefit of knowing the value of :b1? Wouldn’t we expect the cardinality estimate to be the same no matter what value :b1 takes? I’ll come to that shortly.

Let’s first examine where the misconception came from. I think it probably originates from the Oracle Database 11g documentation, so it's unfortunate that the misconception is probably held by the people that read the documentation most carefully. The 11g Performance Tuning Guide mentions the following prerequisite for marking a query bind sensitive: “A histogram exists on the column containing the bind value”. This statement was carried forward into the Oracle Database 12c documentation but was subsequently corrected. The reason it was there in the first place is interesting, and it required some detective work to figure out because it all happened a century ago in I.T. years (10 human-years). I’m pretty sure that the documentation represents the behavior that was originally specified, but refinements to adaptive cursor sharing during development changed things. For example, Oracle Database will mark the example query (above) bind sensitive, even if there’s no histogram. Those of you with version museums can check out whether or not it works like this in the base release (so go ahead and leave a comment if you’ve tried it out).

Why does it work like this? In the following example (where we use range predicates) it’s pretty easy to see why it’s useful to peek at the bind variable values even if there’s no skew or histogram:

SELECT SUM(amount)
FROM   sales
WHERE  sk_id > :b1
AND    sk_id < :b2;

The number of rows matched is highly sensitive to the values of :b1 and :b2 and, in an extreme case, if :b1 is greater than :b2, we’ll match zero rows.

Back to the original query:

SELECT sum(amount)
FROM   sales
WHERE  sk_id = :b1;

Assuming that there’s no histogram on SK_ID and no skew, why would bind peeking be useful? We might expect the cardinality estimate to be about the same no matter what value :b1 takes. Not quite. Consider the case where the minimum value of SK_ID is 1 and the maximum value is 10,000. If :b1 is 20,000, then the predicate will be out of range and we will match zero rows. It’s worth accounting for this possibility.

In summary:

  • Queries with bind variables in predicates are generally marked bind sensitive (there is some dependency on the predicate operator and whether or not the bind variable is a collection).
  • ACS might mark a query bind aware if bind variable values significantly affect the number of rows processed. This may result in multiple SQL execution plans for a particular SQL statement.
  • Histograms are not a prerequisite for ACS.

Always bear in mind that if there is data skew, ACS needs to be able to discern that different bind variable values have different selectivity. For this reason, the use of histograms is highly recommended. Fortunately, they are managed automatically by the database if statistics are gathered using default settings.

Join the discussion

Comments ( 4 )
  • Rajeshwaran, Jeyabal Wednesday, February 27, 2019
    Still the ACS doesn't work for sql's inside PL/SQL ?


    is that got changed in 18c or 19c? sorry dont have access to that database versions, hence asking up here.
  • Nigel Bayliss Tuesday, March 12, 2019
    Hi there,
    If you are referring to bug 8357294, this has not been implemented in 18c or 19c yet. It is to do with the with how PL/SQL handles cursors rather than something that the optimizer has decided. I can tell you that it is something we do want to enhance though.
  • Caglar Sunday, May 24, 2020
    Hello Nigel,

    I tested in Oracle and it works :)



  • Mohamed Houri Tuesday, August 11, 2020
    Hello Nigel,

    Just found yesterday in a 12cR2 database a query using an equality predicate on 4 columns forming the primary key (all 4 columns have histogram). This query sometime generates a cursor which is NOT BIND SENSITIVE. And sometimes the same cursor is BIND SENSITIVE. Bear in mind that histogram have not been deleted. So it seems possible that a cursor will not be marked bind sensitive even in presence of histogram

Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.