1 Reply Latest reply on Jun 23, 2018 11:07 AM by Jonathan Lewis

    cursor sharing = force is not working for the particular SQLs

    3293377

      Hello everybody,

       

      I have an issue quite surprising for me. My database is 12.1.02.0 and has set cursor_sharing = FORCE. But I have two SQL statements where literals are not replaced. These statements has no hints and are generated by the application. Have you ever met this guys? I am little bit confused how this can happend.

       

      I have tried to flush shared pool to remove all the sql plans. And to be sure that Oracle has to hard parse everything again.

       

      Thanks a lot for ideas.

        • 1. Re: cursor sharing = force is not working for the particular SQLs
          Jonathan Lewis

          A little context might help jog the memories of people who've seen this before.

           

          The obvious guess is that the application temporarily sets cursor_sharing to exact.

          If you have access to the dynamic performance views then the view v$sql_optimizer_env. If you can find the sql_id for these statement you can query their optimizer environment (or, at least, the part of it that Oracle doesn't mind you seeing):

           

          select name, value from V$sql_optimizer_env where sql_id = '{your SQL_ID here}' order by 1

           

           

          Another guess - there are cases where a query which mixes literal and bind variables will not have the literals substituted - do your statements match that description ?

          What does the surrounding application code look like - are the statements called from pl/sql packages, or are they statements sent directly to the database by the front-end?

           

          Regards

          Jonathan Lewis

           

           

          UPDATE:  your question prompted me to write up one cause of cursor_sharing "not working": https://jonathanlewis.wordpress.com/2018/06/23/cursor_sharing-force/