Oracle Database 11g New Features Adaptive Cursor Sharing

Will we ever get the question of the use of bind variables over literals in our SQL answered?  Mix together the following in your Oracle database environment: large tables, skewed data, histograms (or lack of), bind variables, literals, the CURSOR_SHARING initialization parameter and bind variable peeking.  The mix can lead to uneven and poor performance.

Oracle Database 11g provides an advanced solution, another answer to the question: Adaptive Cursor Sharing.  Via feedback from the runtime engine, 11g can deliver multiple solutions (execution plans) for the same query, without code changes.

In this tutorial you will receive a brief review of the terms previously mentioned and learn what feedback-based optimization and adaptive cursor sharing is. A demonstration is included.

This free training is segmented in several separate lessons:

  1. Introduction to Oracle Adaptive Cursor Sharing (2:13)
  2. Review SQL Statement Processing (2:14)
  3. Common Optimization Problems and 9i / 10g Solutions (10:00)
  4. 11g Feedback Based Optimization (2:19)
  5. Oracle 11g Adaptive Cursor Sharing Concepts (1:58)
  6. How 11g Adaptive Cursor Sharing Works (5:12) (click on video below)
  7. Demonstration of 11g Adaptive Cursor Sharing (7:23)
  8. 9i and 10g Features with 11g ACS (1:50)
  9. Adaptive Cursor Sharing Summary (1:58)

Date: Feb 8, 2012

NOTE: Some corporate firewalls will not allow videos hosted by YouTube.



How 11g Adaptive Cursor Sharing Works

When SQL is introduced into the system, it can be marked as bind sensitive. It can be; not is.

When a bind sensitive query is introduced, Oracle stores the additional information in the cursor and that additional information is things like selectivity estimates for each predicate that contains a bind variable. There are some limitations there, and we’ll talk about those.

Now, as repeated executions occur and new values are introduced, you could imagine the enormous amount of possible combinations of values in complex SQL that has many bind variables. So I think that Oracle limits this tracking of selectivity on 12 variables.

It would appear that additional selectivity processing code is now included in the soft-parse phase of a query for bind sensitive SQL. So, with the low overhead in mind as one of our goals, only SQL that contains predicates where selectivity can be computed quickly are marked as bind sensitive, and that includes equality predicates – but only equality predicates on columns with histograms. There are no histograms, there’s no basis, there’s no reason to have this feature, ACS, enabled, because we would assume that we were going to get the same amount of data back no matter what the value is in the bind variable. If histograms are introduced, we’d assume there’s data skew, and we do need this feature. We do need different execution plans.

John: Dave, can I interject a question here? Different execution plans for the same statements and also applying* general cursors, how is this going to interact with one of my favorite topics, which is SQL plan baselining, where I try to enforce stability and prevent change?

Dave: Yes. What happens there is that the new plan is entered into the system, if you will, but since it hasn’t been accepted, hasn’t been evolved, it would not be used. So the plan is saved but not yet used. And, as you taught me previously, John, you could set up a nightly job to evolve plans and accept them, so tomorrow the plan becomes an acceptable execution plan.

John: Right. So we get the stability of different bind variables typically a day later.
Dave: Right.
John: That makes sense.
Dave: Right.

So we see the types of predicates, equality predicates with columns with histograms and range type predicates. But, variations in selectivity alone is not enough to know that re-optimization is worth it. Remember, we said we want to be sure as much as possible that going through re-optimization and putting a new plan in the library cache, are all fairly expensive. So Oracle wanted another test, and let’s see what this other test is.

The other test is the concept of row source profiling. So, in row source profiling, Oracle samples query executions and saves these execution statistics including the number of rows processed by the query. To reduce overhead, the sampling rate is decreased over repeated executions. It can eventually be turned off.

If row source profiling – after the execution of a query – reveals large variations in the number of rows processed, some additional information is stored in the cursor, and the query is made bind aware. Statements become bind aware.

A bind aware query is re-optimized if on a subsequent execution at cursor matching time during a soft parse Oracle realizes that the new values are outside the selectivity range stored in the bind profile. If the new execution plan is different from the original plan or all the other child plans, a new child cursor is created. So you will end up with additional child cursors in this scenario.

Copyright 2017

Free Online Registration Required

The tutorial session you want to view requires your registering with us.

It’s fast and easy, and totally FREE.

And best of all, once you are registered, you’ll also have access to all the other 100’s of FREE Video Tutorials we offer!