X

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 11.1.0.7 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 will be marked bind sensitive.
  • 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.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services