Side effect of cursor sharing

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 :twisted:

Another side effect is the length of the column and is described there : http://asktom…P11_QUESTION_ID:3696883368520

Published by

Laurent Schneider

Oracle Certified Master

4 thoughts on “Side effect of cursor sharing”

  1. 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.

  2. 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.

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>