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) (click on video below)
  6. How 11g Adaptive Cursor Sharing Works (5:12)
  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.



Oracle 11g Adaptive Cursor Sharing Concepts

So adaptive cursor sharing provides enhanced optimization in some queries containing bind variables and as John and I have mentioned, it really is the next generation’s solution when peeking falls short, which we’ve seen and heard that it does at times. In fact, in John’s case study, they disabled peeking.

We’ve also learned that the execution plan selected from peeking, based on those first values, may not be the best plan for subsequent values. So SQL performance can be unpredictable.

What are the goals of ACS as laid out in one of the original papers back in probably 2001, 2002 that was written on this concept?

First of all, there should be no user intervention. The developer or the DBA should not have to do anything in order to feed back information from the SQL engine to the optimizer. If we’re talking about re-optimization, we all should be a little nervous about that. We want it to be as low overhead as possible. So it must be fast, use as little memory as possible, don’t use memory when it’s not necessary – of course, using memory requires latches, and latches have their own set of scalability issues – and as best as possible, we must determine that it must be worth it. That is to say the new plan must perform better than the original plan.

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!