Bind Variable Peeking :
In 10g , oracle shared the same execution plan for all queries whatever the value of the bind variable.
But column may contain the skewed data so a single plan will not be suitable for different set of bind variable value.
By peeking at bind values, optimizer can determine the selectivity of a where clause as if like literals has been used.
Adaptive cursor sharing :
In 11g ,adaptive cursor sharing features enables a single statement (that contains the bind variable ) to use multiple execution plans.
Bind Aware Cursor : Bind sensitive cursor which uses different plan for different bind values.
Bind Sensitive Cursor : Optimal plan depend on the bind variable value.
In 11g: Oracle added two columns to v$SQL table:
IS_BIND_SENSITIVE: Tells you if optimizer peeked a bind variable value and if a different value may change the explain plan.
IS_BIND_AWARE: Tells you if a query uses cursor sharing, occurs only after the query has been marked bind sensitive.
Turn-off optimizer bind variable peeking and this can be done in several ways::
Re-set the _optim_peek_user_binds hidden parameter and re-analyze using dbms_stats with the argument: method_opt=> 'for all columns size 1';
If aplication is vendor package and change using Stored Outlines is time consuming use
cursor_sharing=force . It will reduce hard parsing and thus library cache contention.
cursor_sharing=similar will be removed in 12c
Find Bind variable value and plan :
SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING FROM v$sql_bind_capture WHERE sql_id= &sqlid
select * from table ( dbms_xplan.display_cursor ('&sqlid',0, 'ADVANCED'));
