SPM, Part 02: Adaptive Cursor Sharing

This post is a second part of two.
It's a copy and paste from several links that you find in the"Reference" section.

Adaptive Cursor Sharing

Oracle introduced the bind peeking feature in Oracle 9i. With bind peeking, the Optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. This allows the optimizer to determine the selectivity of any WHERE clause condition as if literals have been used instead of bind variables, thus improving the quality of the execution plan generated for statements using bind variables.

However, there was a problem with this approach, when the column used in the WHERE clause with the bind contained a data skew. If there is data skew in the column, it is likely that a histogram has been created on this column during statistics gathering. When the optimizer peeks at the value of the user-defined bind variable and chooses a plan, it is not guaranteed that this plan will be good for all possible values for the bind variable. In other words, the plan is optimized for the peeked value of the bind variable, but not for all possible values.

In 11g, the optimizer has been enhanced to allow multiple execution plans to be used for a single statement that uses bind variables. This ensures that the best execution plan will be used depending on the bind value.

 A cursor is marked bind sensitive (means “been monitored”) if the optimizer believes the optimal plan may depend on the value of the bind variable. When a cursor is marked bind sensitive, Oracle monitors the behavior of the cursor using different bind values, to determine if a different plan for different bind values is called for (Oracle observes the cursors for a while and sees how the values differ. If the different values can potentially alter the plan, the cursor is labeled "Bind-Sensitive" and the column IS_BIND_SENSITIVE shows "Y". After a few executions, the database knows more about the cursors and the values and decides if the cursor should be made to change plans based on the values. If that is the case, the cursor is called "Bind-Aware" and the column IS_BIND_AWARE shows "Y". In summary: Bind-Sensitive cursors are potential candidates for changed plans and Bind-Aware ones are where the plans actually change. A new view V$SQL_CS_HISTOGRAM shows how many times the SQL statement was executed, organized into three buckets for each child cursor).

This cursor was marked bind sensitive because the histogram on the "deptno" column, for example, was used to compute the selectivity of the predicate "where deptno = :deptno". Since the presence of the histogram indicates that the column is skewed, different values of the bind variable may call for different plans.

Behind the scenes during the first two executions, Oracle was monitoring the behavior of the queries, and determined that the different bind values caused the data volumes manipulated by the query to be significantly different.  Based on this difference, Oracle "adapts" its behavior so that the same plan is not always shared for this query. But if we execute the query again with a more selective bind value.

 If we look at the execution statistics again, there are three cursors now:

select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware, is_shareable from v$sql where sql_text like '%deptno = :deptno%';

The original cursor was discarded when the cursor switched to bind aware mode (means: selectivity of predicates during a soft parse determine which optimal plan to use among the ones in memory). This is a one-time overhead. Note that the cursor is marked as not shareable (is_shareable is "N"), which means that this cursor will be among the first to be aged out of the cursor cache, and that it will no longer be used.  In other words, it is just waiting to be garbage collected.

There is one other reason that you may notice additional cursors for such a query in 11g.  When a new bind value is used, the optimizer tries to find a cursor that it thinks will be a good fit, based on similarity in the bind value's selectivity.  If it cannot find such a cursor, it will create a new one (like above, when one (#1) was created for unselective "10" and one (#2) was created for highly-selective "9").  If the plan for the new cursor is the same as one of the existing cursors, the two cursors will be merged, to save space in the cursor cache.  This will result in one being left behind that is in a not shareable state.  This cursor will be aged out first if there is crowding in the cursor cache, and will not be used for future executions.

So it requires two mechanisms: one to control how to change state from “sensitive” to “aware” and another to select a plan in memory as per the values of the binds. Column V$SQL_CS_STATISTICS.ROWS_PROCESSED seems to drive this mechanism. This column is updated during a hard parse and it is a fuzzy representation of the amount of data the query manipulates during its execution. For small values of V$SQL_CS_STATISTICS.ROWS_PROCESSED we increment by one V$SQL_CS_HISTOGRAM.BICKED_ID(0). For medium values we increase by one V$SQL_CS_HISTOGRAM.BICKED_ID(1). And for large values we do V$SQL_CS_HISTOGRAM.BICKED_ID(2). Cursor Sharing histogram buckets 0-2 are updated on every execution of a bind sensitive query. They represent data volume manipulation of every execution. If there are significant variances then the query would benefit of ACS and it becomes Bind Aware.

There are a couple of scenarios where the values stored on these 3 buckets cause the cursor to become bind aware:

1. When two of the buckets have the same value, and this value is not zero
2. When the smallest and the largest buckets (0 and 2) have a value other than zero

Reference

SQL Plan Management (Part 1 of 4) Creating SQL plan baselines (Maria Colgan)
SQL Plan Management (Part 2 of 4) Creating SQL plan baselines (Maria Colgan)
SQL Plan Management (Part 3 of 4) Creating SQL plan baselines (Maria Colgan)
SQL Plan Management (Part 4 of 4) Creating SQL plan baselines (Maria Colgan)
How do adaptive cursor sharing and SQL Plan Management interact? (Unknown)
Why are there more cursors in 11g for my query containing bind variables? (Unknown)
Oracle Optimizer and SPM plan interaction (Mohamed Houri)
Stage and Fix SQL Statement (Unknown)

dbms_sqldiag (Jonathan Lewis)

SQL Patch I (Dominic Brooks)
SQL Patch II (Dominic Brooks)
SQL Patch III (Dominic Brooks)
SQL Patch IV (Dominic Brooks)
Adding and Disabling Hints Using SQL Patch (Nigel Bayliss)
Using SQL Patch to add hints to a packaged application (Unknown)

Adaptive Cursors and SQL Plan Management (Arup Nanda)
How a Cursor becomes Bind Aware? (Carlo Sierra)
Adaptive Cursor Sharing with SQL Plan Baselines (Dominic Brooks)