Question:  I understand that Oracle11g now has an improved method for cursor_sharing, so that Oracle will compare execution plans, and only use a changed plan when the histogram indicates a difference that makes a difference.  What are the internal details of this 11g adaptive cursor sharing?

Answer:  Oracle has improved cursor_sharing several times over the years.  Remember, adaptive cursor sharing is only deployed in rare cases where a skewed column distribution (as noted by the histogram), indicates that a different execution plan would be faster.  For example, a query with a popular bind variable value would be best served with a full table scan while an unpopular bind variable value would benefit from an index access plan.

But remember, this is a rare occurrence in many systems.

In my experience at BC, about 80% of shops have uniformly distributed data. Large tables remain large, and the distribution of values within a column remain unchanged.

On the other hand, we have roughly 20% of databases that experience highly volatile data loads, where tables are small on one day and huge the next, or cases where the is a "difference that makes a difference". In these databases, huge changes in the tables data (usually associated with high DML) changes the distribution of data values, necessitating a re-analysis of column histograms.

Histograms are critical to the CBO decision to choose an index vs. a full scan, and also for determining the optimal table join order.

Cursor_sharing=similar

The original mechanism (cursor_sharing=similar) had significant issues in Oracle9i.  Robert Freeman notes:

"Oracle8i introduced a feature called cursor sharing which provided the ability of the optimizer to convert literals within SQL statements into bind variables in certain situations. As a result, SQL statements that are alike with the exception of literal values, can share a given cursor.

This has the impact of reducing the overall time to parse the SQL statement and, perhaps most importantly, reducing fragmentation of the shared SQL area of the shared pool. Unfortunately, one of the end results of cursor sharing and the use of bind variables is that the optimizer has a difficult time determining the selectivity of the data in the columns associated with the bind variable. This can lead to sub-optimal execution plans. You use the parameter cursor_sharing=force to enable cursor sharing.

Oracle9i now adds modifications to cursor sharing. If you set cursor_sharing=similar, the optimizer will be able to analyze the distribution of the data in the columns (using the analyzed statistics of the table, columns, associated indexes and any histograms that you may have generated, and determine if the parsed execution plan will be optimal. If the plan does appear to be optimal then the parsed SQL statement will be used."

Dr. Tim Hall notes that the 11g adaptive cursor sharing uses bind variable peeking in a different way:

"DBAs are always encouraging developers to use bind variables, but when bind variables are used against columns containing skewed data they sometimes lead to less than optimum execution plans.

This is because the optimizer peaks at the bind variable value during the hard parse of the statement, so the value of a bind variable when the statement is first presented to the server can affect every execution of the statement, regardless of the bind variable values.

Oracle 11g uses Adaptive Cursor Sharing to solve this problem by allowing the server to compare the effectiveness of execution plans between executions with different bind variable values.

If it notices suboptimal plans, it allows certain bind variable values, or ranges of values, to use alternate execution plans for the same statement. This functionality requires no additional configuration."

Dr. Hall also notes that the v$sql keeps this adaptive information:

"If we look at the V$SQL view entry for this query, we can see the IS_BIND_SENSITIVE column is marked as 'Y', so Oracle is aware this query may require differing execution plans depending on the bind variable values, but currently the IS_BIND_AWARE column is marked as 'N', so Oracle as not acted on this yet."
 

SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware
FROM   v$sql
WHERE  sql_text = 'SELECT MAX(id) 
FROM acs_test_tab WHERE record_type = :l_record_type';

SQL_ID        CHILD_NUMBER I I
------------- ------------ - -
9bmm6cmwa8saf            0 Y N

See my relates notes and articles on Oracle cursor sharing and bind variable peeking:

If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.