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) (click on video below)
  9. Adaptive Cursor Sharing Summary (1:58)

Date: Feb 8, 2012

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



9i and 10g Features with 11g ACS

Stored outlines enhance, negate adaptive cursor sharing, so I think SQL profiles do, too. I need to do more research on that. We might have an opportunity for a quick follow-up.

CURSOR_SHARING=SIMILAR is deprecated. It’s planned for obsolescence —

John: Can you absolutely confirm that?

Dave: Yes. You bet. That’s pretty clearly documented. I mentioned this Metalink Doc ID here and if you check out the documentation on that, it includes this in the first paragraph:

“We recommend that customers discontinue setting cursor_sharing=similar due to the many problematic situations customers have experienced using it. The ability to set this will be removed in Version 12 of the Oracle database. The settings of EXACT and FORCE will remain available. Instead, we recommend the use of adaptive cursor sharing in 11g and up.” I paraphrased in there somewhat.

John: Well, thanks for confirming that, Dave. That’s putting it pretty bluntly. I’ve never seen it myself, but many people I know have seen appalling problems in cursor_sharing=similar.

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!