Loading ...
Sorry, an error occurred while loading the content.
 

CURSOR_SHARING PARAMETER IN ORACLE

Expand Messages
  • R P
    Hi, We are running Financials 8.4 tools 844.14 and recently upgraded from Oracle 8i to 9i (9205) and had been using the cursor_sharing parameter. We had been
    Message 1 of 2 , Dec 29, 2004
      Hi,
      We are running Financials 8.4 tools 844.14 and
      recently upgraded from Oracle 8i to 9i (9205) and had been using the cursor_sharing parameter. We had been setting it to EXACT, as it was recommended that we do that by some load testing experts. However, it seems like to me that Peoplesoft uses bind variables in most or all of their programs, and setting this parameter to EXACT will not provide any benefit and can actually cause the optimizer to choose bad plans on certain queries. Also,  setting this parameter causes histograms to be ignored as Oracle is replacing literals with bind variables. In Oracle 9i there is a new value for which cursor sharing parameter can be set. It is similar.
       
      Has anyone ever experimented with using the cursor_sharing parameter, or can anyone provide more information about this.
      Thanks a bunch,


      Do you Yahoo!?
      Yahoo! Mail - 250MB free storage. Do more. Manage less.
    • akhendup69
      Hello! I will provide what has been happening to our Psoft CRM with the parameter. We originally tried CURSOR_SHARING=EXACT in our performance testing and
      Message 2 of 2 , Dec 30, 2004
        Hello!

        I will provide what has been happening to our Psoft CRM with the
        parameter.

        We originally tried CURSOR_SHARING=EXACT in our performance testing
        and found that it had a lot of problems as we ramped up to our
        necessary level of 1500 AEs a minute. We were constantly wrestling
        with changes on our highest volume tables and queries.

        We moved it to CURSOR_SHARING=SIMILAR and while it worked pretty well
        there were some pretty bad hits on some key structures that kept
        cropping up as volumes changed at certain points that we gave up on
        this.

        We finally settled on CURSOR_SHARING=FORCED, added a lot
        stored_outlines and made several key SQL changes. So far it works
        well with our current production load of 3,500 AEs a minute.

        We have done a lot of key performance enhancments and changes to get
        the CRM product to meet our needs which did involve this parameter
        and a whole slew of other changes as well.

        In any event I hope this helps.

        Cheers!

        A. Khendup


        We use Oracle 9i as well. The PeopleSoft recommendation is
        --- In psftdba@yahoogroups.com, R P <pjksel@y...> wrote:
        > Hi,
        > We are running Financials 8.4 tools 844.14 and
        > recently upgraded from Oracle 8i to 9i (9205) and had been using
        the cursor_sharing parameter. We had been setting it to EXACT, as it
        was recommended that we do that by some load testing experts.
        However, it seems like to me that Peoplesoft uses bind variables in
        most or all of their programs, and setting this parameter to EXACT
        will not provide any benefit and can actually cause the optimizer to
        choose bad plans on certain queries. Also, setting this parameter
        causes histograms to be ignored as Oracle is replacing literals with
        bind variables. In Oracle 9i there is a new value for which cursor
        sharing parameter can be set. It is similar.
        >
        > Has anyone ever experimented with using the cursor_sharing
        parameter, or can anyone provide more information about this.
        > Thanks a bunch,
        >
        >
        > ---------------------------------
        > Do you Yahoo!?
        > Yahoo! Mail - 250MB free storage. Do more. Manage less.
      Your message has been successfully submitted and would be delivered to recipients shortly.