Database Reference
In-Depth Information
CHILD_NUMBER BUCKET_ID COUNT
------------ ---------- ----------
0 0 1
0 1 1
0 2 0
1 0 1
1 1 0
1 2 0
2 0 1
2 1 0
2 2 0
To better understand how the content of the
v$sql_cs_histogram
view is used, I suggest you experiment with
several scenarios using the
adaptive_cursor_sharing_histogram.sql
script.
There are two main limitations related to adaptive cursor sharing. First, by default, cursors are created non-bind
aware. Second, the bind awareness of a given cursor isn't persisted. As a result, at least one execution, and in some
situations many executions (when there were a high number of efficient executions), has to be inefficient before a
cursor can take advantage of adaptive cursor sharing. To avoid these limitations, it's possible as of version 11.1.0.7
to specify the
bind_aware
hint. Notice how, in the following example, both child cursors are bind aware and use an
efficient execution plan:
SQL> EXECUTE :id := 10;
SQL> SELECT /*+
bind_aware
*/ count(pad) FROM t WHERE id < :id;
COUNT(PAD)
----------
9
Plan hash value: 4270555908
---------------------------------------------
| Id | Operation | Name |
---------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS BY INDEX ROWID| T |
| 3 | INDEX RANGE SCAN | T_PK |
---------------------------------------------
SQL> EXECUTE :id := 990;
SQL> SELECT /*+
bind_aware
*/ count(pad) FROM t WHERE id < :id;
COUNT(PAD)
----------
989
