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) (click on video below)
  5. Oracle 11g Adaptive Cursor Sharing Concepts (1:58)
  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.



11g Feedback Based Optimization

So let’s take a look at the new concept that 11g provides, and that’s Feedback Based Optimization. I guess I should note that this is not an Oracle only concept. DB2 has the learning optimizer. [Microsoft] SQL Server has some notion of it.

So Oracle’s implementation of it is two features, adaptive cursor sharing and cardinality feedback, and of course it’s different in many ways from the other products on the market, but the concept that the database vendors are getting into is, well, let’s take some of the run-time data and send it back to the optimizer. We’ll put it in the cursor cache, note it’s the cursor cache, so at least for now it is not persistent, and then the SQL engine feeds back this knowledge and the optimizer can make use of it.

As I mentioned, it is the foundation for the two features:

  • Adaptive cursor sharing to overcome limitations in peeking and the other limitations that John just spoke of, and:
  • Cardinality feedback. To fix cardinality misestimates.

It should be noted that this feature is primarily intended for frequently executed SQL. Since both features can cause SQL to be re-optimized, which we’ve already determined is expensive, the features are intended for SQL with high execution counts, so that the cost of re-optimization can be amortized over many subsequent executions. Short-duration SQL statements usually fit this model, such as SQL and an online transaction processing application. Long-duration SQL such as decision support queries against data warehouses are typically not executed frequently enough to benefit.

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!