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

Date: Feb 8, 2012

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



Review SQL Statement Processing

To fully understand ACS, we should be familiar with how SQL statements are compiled. You might call it parsing. I’ll just give you a little broader term – compiled – and this includes soft parse, which involves parsing the statement. You’ve written “select column name, column name from table name where predicates,” and that has to be parsed and translated into an internal format.

Soft parse also includes syntax check and semantic check. We have to know the semantics of the statement, who’s executing it, do you have proper privileges, etcetera. Soft parse also includes searching the library cache to see if the query plan is already stored, if it already has been optimized and, if so, hard parse can be avoided. Hard parse is optimizing for the lowest cost execution plan and it does involve often an expensive step. By default, it compares up to 2,000 permutations if there are that many for a complex query. To settle on the lowest cost plan, you can imagine how expensive that could be.

Hard parse also stores the execution plan in the library cache, part of the shared pool. This requires latches, memory locks. These are a bad thing, because they decrease scalability and consume memory.

So let’s settle on the fact that hard parse is a high overhead operation and you probably heard the motto, “Parse once, compile many.” Now, soft parse can also be avoided at times as well. We’ll learn, however, today that that might not be the best thing for adaptive cursor sharing.

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!