Adaptive Cursor Sharing: What is Adaptive Cursor Sharing?

The following is the direct extract from the documentation on adaptive cursor sharing.

The adaptive cursor sharing feature enables a single statement that contains bind variables to use multiple execution plans. Cursor sharing is "adaptive" because the cursor adapts its behavior so that the database does not always use the same plan for each execution or bind variable value.

For appropriate queries, the database monitors data accessed over time for different bind values, ensuring the optimal choice of cursor for a specific bind value. For example, the optimizer might choose one plan for bind value 9 and a different plan for bind value 10. Cursor sharing is "adaptive" because the cursor adapts its behavior so that the same plan is not always used for each execution or bind variable value.

Adaptive cursor sharing is enabled for the database by default and cannot be disabled. Note that adaptive cursor sharing does not apply to SQL statements containing more than 14 bind variables.

Note:

Adaptive cursor sharing is independent of the CURSOR_SHARING initialization parameter (see "Sharing Cursors for Existing Applications"). Adaptive cursor sharing is equally applicable to statements that contain user-defined and system-generated bind variables.

Bind-Sensitive Cursors

A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable. The database monitors the behavior of a bind-sensitive cursor that uses different bind values to determine whether a different plan is beneficial.

The criteria used by the optimizer to decide whether a cursor is bind-sensitive include the following:

• The optimizer has peeked at the bind values to generate selectivity estimates.

• A histogram exists on the column containing the bind value.

Bind-Sensitive Cursors

In Example 11-1 you queried the emp table using the bind value 9 for deptno. Now you run the DBMS_XPLAN.DISPLAY_CURSOR function to show the query plan:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

The output is as follows:

----------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time|
----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |        |      |       |   2 (100)|         |
|  1 |  SORT AGGREGATE              |        |    1 |    16 |          |         |
|  2 |   TABLE ACCESS BY INDEX ROWID| EMP    |    1 |    16 |   2   (0)| 00:00:01|
|* 3 |    INDEX RANGE SCAN          | EMP_I1 |    1 |       |   1   (0)| 00:00:01|
----------------------------------------------------------------------------------

The plan indicates that the optimizer chose an index range scan, which is expected because of the selectivity (only 1%) of the value 9. You can query V$SQL to view statistics about the cursor:

COL BIND_SENSI FORMAT a10

COL BIND_AWARE FORMAT a10

COL BIND_SHARE FORMAT a10

SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",

IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"

FROM V$SQL

WHERE SQL_TEXT LIKE 'select /*ACS_1%';

As shown in the following output, one child cursor exists for this statement and has been executed once. A small number of buffer gets are associated with the child cursor. Because the deptno data is skewed, the database created a histogram. This histogram led the database to mark the cursor as bind-sensitive (IS_BIND_SENSITIVE is Y).

CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE
------------ ---------- ----------- ---------- ---------- ----------
           0          1          56 Y          N          Y

For each execution of the query with a new bind value, the database records the execution statistics for the new value and compares them to the execution statistics for the previous value. If execution statistics vary greatly, then the database marks the cursor bind-aware.

Bind-Aware Cursors

A bind-aware cursor is a bind-sensitive cursor eligible to use different plans for different bind values. After a cursor has been made bind-aware, the optimizer chooses plans for future executions based on the bind value and its selectivity estimate.

When a statement with a bind-sensitive cursor executes, the database decides whether to mark the cursor bind-aware. The decision depends on whether the cursor produces significantly different data access patterns for different bind values. If the database marks the cursor bind-aware, then the next time that the cursor executes the database does the following:

• Generates a new plan based on the new bind value.

• Marks the original cursor generated for the statement as not shareable (V$SQL.IS_SHAREABLE is N). This cursor is no longer usable and will be among the first to be aged out of the shared SQL area.

Example 11-3 Bind-Aware Cursors

In Example 11-1 you queried emp using the bind value 9. Now you query emp using the bind value 10. The query returns 99,900 rows that contain the value 10:

COUNT(*)   MAX(EMPNO)
---------- ----------
99900      100000

Because the cursor for this statement is bind-sensitive, the optimizer assumes that the cursor can be shared. Consequently, the optimizer uses the same index range scan for the value 10 as for the value 9.

The V$SQL output shows that the same bind-sensitive cursor was executed a second time (the query using 10) and required many more buffer gets than the first execution:

SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI",

IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"

FROM V$SQL

WHERE SQL_TEXT LIKE 'select /*ACS_1%';

CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE

------------ ---------- ----------- ---------- ---------- ----------

0 2 1010 Y N Y

Now you execute the query using the value 10 a second time. The database compares statistics for previous executions and marks the cursor as bind-aware. In this case, the optimizer decides that a new plan is warranted, so it performs a hard parse of the statement and generates a new plan. The new plan uses a full table scan instead of an index range scan:

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   208 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    16 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  | 95000 |  1484K|   208   (1)| 00:00:03 |
---------------------------------------------------------------------------

A query of V$SQL shows that the database created an additional child cursor (child number 1) that represents the plan containing the full table scan. This new cursor shows a lower number of buffer gets and is marked bind-aware:

SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI", 
       IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"
FROM   V$SQL
WHERE  SQL_TEXT LIKE 'select /*ACS_1%';
 
CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE
------------ ---------- ----------- ---------- ---------- ----------
           0          2        1010 Y          N          Y
           1          2        1522 Y          Y          Y

After you execute the query twice with value 10, you execute it again using the more selective value 9. Because of adaptive cursor sharing, the optimizer "adapts" the cursor and chooses an index range scan rather than a full table scan for this value.

A query of V$SQL indicates that the database created a new child cursor (child number 2) for the execution of the query:

CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE
------------ ---------- ----------- ---------- ---------- ----------
           0          2        1010 Y          N          N
           1          1        1522 Y          Y          Y
           2          1           7 Y          Y          Y

Because the database is now using adaptive cursor sharing, the database no longer uses the original cursor (child 0), which is not bind-aware. The shared SQL area will age out the defunct cursor.

Cursor Merging

If the optimizer creates a plan for a bind-aware cursor, and if this plan is the same as an existing cursor, then the optimizer can perform cursor merging. In this case, the database merges cursors to save space in the shared SQL area. The database increases the selectivity range for the cursor to include the selectivity of the new bind.

Suppose you execute a query with a bind value that does not fall within the selectivity ranges of the existing cursors. The database performs a hard parse and generates a new plan and new cursor. If this new plan is the same plan used by an existing cursor, then the database merges these two cursors and deletes one of the old cursors.

Viewing Bind-Related Performance Data

You can use the V$ views for adaptive cursor sharing to see selectivity ranges, cursor information (such as whether a cursor is bind-aware or bind-sensitive), and execution statistics:

• V$SQL shows whether a cursor is bind-sensitive or bind-aware

• V$SQL_CS_HISTOGRAM shows the distribution of the execution count across a three-bucket execution history histogram

• V$SQL_CS_SELECTIVITY shows the selectivity ranges stored for every predicate containing a bind variable if the selectivity was used to check cursor sharing

• V$SQL_CS_STATISTICS summarizes the information that the optimizer uses to determine whether to mark a cursor bind-aware.

As said its default, this feature can be disabled only by setting the hidden initialization parametes.

Source: Oracle Documentation: http://docs.oracle.com/cd/E11882_01/server.112/e16638/optimops.htm