Do not want bind peeking? what you have in then? _optim_peek_user_binds=false vs. /*+ NO_BIND_AWARE */

Adaptive cursor sharing introduced in 11g R1 to address the bind peeking issues with things track especially the bind aware and bind sensitivity. This is for plan stability right,

Read here more on this.

Can this stability when bind peeking issues is be achievable in 10g, well the obvious answer is sql profiles, stored out lines.

Another option is to disable the bind peeking using the hidden parameter _optim_peek_user_binds=false

But this may have side affects as you are turning off for entire instance, what if and how you want to disable the bind peeking for only single statement,

Well according to the document 740052.1, you can get the same consistent behavior (plan stability)  in 10g using the following optimizer hint without sql profiles or outlines if you are experiencing bind peeking issues.

/*+ NO_BIND_AWARE */

Example:-

SQL> variable lv_id number;
SQL> exec :lv_id :=10;
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
-------- ------------ - ----------
cysh98920 0 Y 1

Now, let's turn off bind peeking using this hint
=============================

SQL> alter system flush shared_pool;
System altered.

SQL> exec :lv_id :=100;
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>

See your bind value is not peeked this time. So /*+ NO_BIND_AWARE */  is alternative for your _optim_peek_user_binds=false

-Thanks

Geek DBA