6 Replies Latest reply: Oct 4, 2012 12:56 PM by rp0428 RSS

    Considerations for setting cursor_sharing=force

    MattR
      Hi all,

      We have a vendor application that doesn't use of bind variables much at all and we're seeing a lot of hard parsing.
      We're planning to implement cursor_sharing=FORCE in our 11.1.0.7 RAC database (currently set to EXACT).

      I am interested if anyone has any tips or knows of anything to watch out for in implementing cursor_sharing=force.
      I.e. bugs we could hit, potential performance degradations, any other lessons or tips that you may be able to share.

      This is obviously going to be tested thoroughly in dev/qa before being implemented in production.

      Appreciate any feedback you may be able to provide.

      Cheers,
      Matt
        • 1. Re: Considerations for setting cursor_sharing=force
          vlethakula
          From 11gr2, CURSOR_SHARING=SIMILIAR is deprecated.
          Left with EXACT and FORCE.
          Check,
          ANNOUNCEMENT: Deprecating the cursor_sharing = 'SIMILAR' setting [ID 1169017.1]
          • 2. Re: Considerations for setting cursor_sharing=force
            rp0428
            >
            I am interested if anyone has any tips or knows of anything to watch out for in implementing cursor_sharing=force.
            I.e. bugs we could hit, potential performance degradations, any other lessons or tips that you may be able to share.
            >
            Both of these experts, Jonathan Lewis and Charles Hooper, have plenty to say about it.,

            http://jonathanlewis.wordpress.com/2008/03/09/cursor_sharing/

            http://hoopercharles.wordpress.com/2011/07/03/the-cursor_sharing-parameter-is-a-silver-bullet-what-is-wrong-with-this-quote/

            And be sure to see Jonathan's reply date Oct 13, 2009 9:44AM in this thread
            Need huge shared pool size
            >
            There are (at least) three different issues that conspire to cause you problems.

            a) Literal string SQL is typically non-sharable SQL - but can get the best execution plans each time, at a cost in optimisation overheads (CPU, latching, memory).

            b) If you set cursor_sharing = force, statement which differ only in literal values become "the same" because of bind variable substitution into the text. But this means the plan for every execution of the statement will be the same (until something flushes the current plan from memory and a new plan appears). With bind variables in place, Oracle uses bind variable peeking to find that values that should be used for the first optimisation. This means that at random intervals you may get a plan which was great for the first person and terrible for everyone else. Typical causes of extreme variation: queries that use widely varying date ranges, queries against columns on which you have built histograms, queries where the parition key of a partitioned object. Typical ymptoms: wild variations in performance, but only a very small number of copies (often only one) of substituted SQL statements in the library cache.

            c) If you set cursor_sharing = similar, Oracle does bind variable substitution - then checks to see if any of the substitutions are for range-based predicates (e.g. between two date values), are predicates on columns with histograms or partitioning keys. If any of these are seen then Oracle re-optimises the query if it hasn't got a plan for the same set of values already. Typical symptoms: large numbers of copies of substituted SQL statements in the library cache.

            You've tried cursor_sharing = exact - but did you get rid of histograms at the same time. If not this could explain performance problems (although this combination should not cause an extreme growth in library cache usage).
            • 3. Re: Considerations for setting cursor_sharing=force
              Osama_Mustafa
              Check Jonathan Lewis Blog
              http://jonathanlewis.wordpress.com/2008/03/09/cursor_sharing/
              • 4. Re: Considerations for setting cursor_sharing=force
                Mohamed Houri
                Here also a practical case I have been confronted to which I have summarized here below

                http://hourim.wordpress.com/2011/06/16/bind-variable-shared-pool-and-cursor-sharing-parameter/

                Best regards

                Mohamed Houri
                www.hourim.wordpress.com
                • 5. Re: Considerations for setting cursor_sharing=force
                  JohnWatson
                  The replies so far are ignoring a criticcal 11g new feature: Adaptive Cursor Sharing. ACS means that theorectically you can set cursor_sharing=force, and Oracle will develop (if necessary) multiple plans that are suitable for different bind variable values, and switch between them for each execution. ACS really does work. It relies on repeated executions, and so can take a while to stabilize. The interaction with SQL plan baselining (if you've enabled it) can complicate things.
                  I have found that in some circumstances, particularly with 11.1, ACS can result in an excessive number of child cursors; it is better in 11.2. But overall, I would say go for cursor_sharing=force, and see how the ACS mechanism handles it. Possibly, very well.
                  • 6. Re: Considerations for setting cursor_sharing=force
                    rp0428
                    >
                    The replies so far are ignoring a criticcal 11g new feature: Adaptive Cursor Sharing
                    >
                    Not true!

                    Charles Hooper's article mentions adaptive and also refers to this article by the Oracle Optimizer group
                    https://blogs.oracle.com/optimizer/entry/explain_adaptive_cursor_sharing_behavior_with_cursor_sharing_similar_and_force