1 2 Previous Next 16 Replies Latest reply: Feb 13, 2013 1:41 PM by ji li Go to original post RSS
      • 15. Re: 11gR2  cursor_sharing=force  and  adaptive cursor sharing
        ji li
        I can't believe the driver was this was some ratio rather than actual performance issues / latch contention over parsing.
        Just revert the change if there's no driver for it and no actual, tangible, significant benefit.
        Yes, this is being considered. It was a first step review of cache hit ratios.
        But, it doesn't seem to make sense to have Oracle recreate a brand new execution plan each time it runs a similar query that is only different by the literal value.
        Hence the reason to consider changing it to FORCE.

        This is a complicated subject understanding the full scope of histograms, ACS and SPM as it relates to cursor_sharing.

        There is one thing I read here that seems to relate to our situation however:

        http://aychin.wordpress.com/2011/04/04/adaptive-cursor-sharing-and-spm/

        In cases when we will use CURSOR_SHARING parameter to replace literals with binds and share cursor between different sessions, in 11gR2 we need to set it to FORCE to effectively use ACS.

        Restrictions of the Adaptive Cursor Sharing in 11gR2, published by Oracle.

        ACS will not mark the cursor as bind sensitive if:

        It is using Parallel query
        Hints are in use
        Outlines are being used
        It is not recursive query
        The number of binds is more than 14

        Only if all requirements is met, Oracle will mark the query bind sensitive and will monitor its selectivity. But if not, it will handle it as in the past versions.

        Edited by: ji li on Feb 13, 2013 11:17 AM
        • 16. Re: 11gR2  cursor_sharing=force  and  adaptive cursor sharing
          ji li
          Also, from the page you referenced:

          This example shows that if you use histograms, and want the optimizer to choose an optimal plan for different literal values using ACS, then you should set cursor_sharing to force.

          The paragraph from the page you gave me says:
          This example shows that if you use histograms, and want the optimizer 
          to choose an optimal plan for different literal values using ACS,
           then you should set cursor_sharing to force. If it is set to similar, then
           literal replacement will not take place, and a child cursor will be 
          created for each value of the literal. Setting cursor_sharing to similar 
          effectively disables ACS for these kinds of queries. By setting 
          cursor_sharing to force and letting adaptive cursor sharing kick in, the 
          optimizer can choose optimal plans for different values, but if 
          the same plan is appropriate for several values, they will share a single 
          child cursor. Historically, cursor_sharing=similar has been 
          recommended as a middle ground between no literal replacement 
          (which causes a lot of cursors to be generated) and forced literal 
          replacement (which causes a potentially sub-optimal plan to be shared 
          for all literals). We now recommend using adaptive cursor 
          sharing along with cursor_sharing=force instead. 
          1 2 Previous Next