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)
  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) (click on video below)

Date: Feb 8, 2012

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


Adaptive Cursor Sharing Summary

To wrap up ACS, one thing we should be aware of is [to employ ACS] soft parse is required.

Studies have been done to show that PL/SQL, which really has some nice technology in it, to avoid soft parse. Soft parse has a bit of overhead, so the PL/SQL has built-in soft parse avoidance. When you loop through a query or a cursor, it’s not re-soft parsing over and over and over again.

So soft parse avoidance is an issue and, in fact, on Oracle support – MOS, My Oracle Support – it’s listed as a bug and I guess it could be considered that, but we also musk ask ourselves: Is that what ACS is really designed for? Perhaps not.

We also mentioned cursor_sharing=similar is deprecated and obsolete in R12. There is a BIND_AWARE hint, which I mentioned a little while ago, and that will force the re-optimization on the first execution with a different selectivity profile – because with the hint, we’ve already put it in BIND_AWARE mode. We’ve gone from BIND_SENSITIVE to BIND_AWARE and so we’ve essentially taken out one for the litmus tests, which was a variation or a large variation in the number of rows processed. There’s also an opposite hint called NO_ BIND_AWARE if you want to disable this feature.

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!