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.
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 a10COL BIND_SHARE FORMAT a10
COL BIND_AWARE 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_SHAREABLEisN). This cursor is no longer usable and will be among the first to be aged out of the shared SQL area.
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",FROM V$SQL
IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"
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$SQLshows whether a cursor is bind-sensitive or bind-aware -
V$SQL_CS_HISTOGRAMshows the distribution of the execution count across a three-bucket execution history histogram -
V$SQL_CS_SELECTIVITYshows the selectivity ranges stored for every predicate containing a bind variable if the selectivity was used to check cursor sharing -
V$SQL_CS_STATISTICSsummarizes 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.
