Cursor sharing transform strings in bind variable.
So if you do SELECT * FROM EMP WHERE ENAME='SCOTT'; it will be transformed in SELECT * FROM EMP WHERE ENAME=:sys_b0;
This sometimes improved performance of application that do not use binds, for instance.
Now let’s imagine your application generates trace files. The application generates a SELECT 'LSC-11.1011.000.1110.1.0100.000.110' FROM DUAL;
. Ok, let’s look for the string in trace files :
$ cd udump
$ grep LSC-11 *.trc
$
$ grep DUAL$ *.trc
LSC01_ora_8630490.trc:SELECT :"SYS_B_0" FROM DUAL
LSC01_ora_8630490.trc:SELECT :"SYS_B_0" FROM DUAL
LSC01_ora_8839288.trc:SELECT :"SYS_B_0" FROM DUAL
LSC01_ora_8839288.trc:SELECT :"SYS_B_0" FROM DUAL
LSC01_ora_8933560.trc:SELECT :"SYS_B_0" FROM DUAL
LSC01_ora_8933560.trc:SELECT :"SYS_B_0" FROM DUAL
WTF! The tracing mechanism of the application is no longer usable 😈
Another side effect is the length of the column and is described there : http://asktom…P11_QUESTION_ID:3696883368520
If you have an sql trace with binds (10046 with level 12) you have the chance to find out the values. It may be hard if there are hundreds of different values, but you should not be using literals for such a query anyway, so there is no difference here for sqls with hundreds of different bind values.
Laurent, another side effect (and a more important one) is bind-peeking. Before you had optimal execution plans(thanks to the literals), now you have binds, so bind-peeking. If there is a histogram on the column you may get suboptimal plans for some bind values and optimal plans for others.
Thanks for the comment.
No, it was not trace files with bind.
About bind peeking, there is another value SIMILAR which partly solves the problem. Still I set the parameter back to EXACT on that db.
Interesting quote from the Oracle optimizer people
http://optimizermagic.blogspot.com/2007/12/why-are-there-more-cursors-in-11g-for.html
“We hope that in the future, this feature [11g’s adaptive cursor sharing] will persuade people to set cursor_sharing to force.”
I wonder if there is, or will be an event, to record the bind values used when a query is optimized.
Pingback: "cursor_sharing=similar" et "method_opt=>’FOR ALL COLUMNS SIZE AUTO’" | ArKZoYd