Here is how the cursor sharing works at a high level with bind variables, especially in conjunction with Histograms (prior to oracle 11g):
- Optimizer peeks value for the bind on initial parse
- Initial value of the bind determines the plan
- Same plan is used/shared regardless of future bind values
- One plan not always appropriate and optimal for all bind values and results in following problems or side-effects:
- Performance problems
- Instability issues
- Unhappy end users
Adaptive Cursor Sharing address this problem. it shares execution plans ONLY when bind values are "equivalent" - means if it doesn't result in performance degradation. This is the default behavior in Oracle11g.
More information on adaptive cursor sharing can be found at: http://optimizermagic.blogspot.com/2009/04/update-on-adaptive-cursor-sharing.html - The intention of this post is not to talk about this feature - but how to turn-off this default behavior. I can't really think of a reason why anyone would like to turn this feature off, but it is possible, if at all we need it for some reason. This can be achieved by using NO_BIND_AWARE hint.
I believe this hint is meant to be an alternative for using the hidden parameter -
_optim_peek_user_binds=false which is used to turn-off bind peeking. Here is the example:
The default behavior:
SQL> variable lv_id number;
SQL> exec :lv_id :=100;
PL/SQL procedure successfully completed.
SQL> Select count(*) from sh.sales where prod_id = :lv_id;
COUNT(*)
----------
0
----if the bind values are peeked, we would see records in V$SQL_CS_STATISTICS:
SQL> Select address, child_number, peeked, executions from v$sql_cs_statistics;
ADDRESS CHILD_NUMBER P EXECUTIONS
-------- ------------ - ----------
34C54884 0 Y 1
Now, let's turn off bind peeking.
======================
SQL> alter system flush shared_pool;
System altered.
SQL> exec :lv_id :=1000;
PL/SQL procedure successfully completed.
SQL> Select /*+
NO_BIND_AWARE */ count(*) from sh.sales where prod_id = :lv_id;
COUNT(*)
----------
0
----With the above hint, the sql is not bind aware, therefore we don't see any records in v$SQL_CS_STATISTICS view.
SQL> Select address, child_number, peeked, executions from v$sql_cs_statistics;
no rows selected
SQL>
I guess, this is a better and elegant way to turn-off bind-peeking at the SQL level instead of turning-off bind-peeking at the instance level using the hidden parameter - _optim_peek_user_binds. Again, this parameter is typically used to prevent the side/ill-effects of bind-peeking and the same is now (with 11g) obviated with the introduction of Adaptive Cursor Sharing feature in 11g.
BTW, this hint is available in 11.1.0.7.
