Key Concepts - Troubleshooting Oracle Performance - page 33

In the following example, based on the adaptive_cursor_sharing.sql script, the cursor is shareable and
sensitive to bind variables, but it isn't using adaptive cursor sharing:
SQL> EXECUTE :id := 10;
SQL> SELECT count(pad) FROM t WHERE id < :id;
COUNT(PAD)
----------
9
SQL> SELECT sql_id
2 FROM v$sqlarea
3 WHERE sql_text = 'SELECT count(pad) FROM t WHERE id < :id';
SQL_ID
-------------
asth1mx10aygn
SQL> SELECT child_number, is_bind_sensitive, is_bind_aware, is_shareable, plan_hash_value
2 FROM v$sql
3 WHERE sql_id = 'asth1mx10aygn';
CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE PLAN_HASH_VALUE
------------ ----------------- ------------- ------------ ---------------
0 Y N Y 4270555908
Something interesting happens when the cursor is executed several times with different values for the bind
variable. Notice in the following that child number 0 is no longer shareable and that two new child cursors have
replaced it, both using adaptive cursor sharing:
SQL> EXECUTE :id := 990;
SQL> SELECT count(pad) FROM t WHERE id < :id;
COUNT(PAD)
----------
989
SQL> EXECUTE :id := 10;
SQL> SELECT count(pad) FROM t WHERE id < :id;
COUNT(PAD)
----------
9
SQL> SELECT child_number, is_bind_sensitive, is_bind_aware, is_shareable, plan_hash_value
2 FROM v$sql
3 WHERE sql_id = 'asth1mx10aygn'
4 ORDER BY child_number;