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

Re: PeopleSoft DBA Forum Digest Number 301

Expand Messages
  • Wolfgang Breitling
    You are not making any sense. With cursor_sharing=exact, Oracle is NOT replacing literals with bind variable, only with cursor_sharing=force or similar. Also,
    Message 1 of 4 , Dec 30, 2004
    View Source
    • 0 Attachment
      You are not making any sense. With cursor_sharing=exact, Oracle is NOT
      replacing literals with bind variable, only with cursor_sharing=force or
      similar. Also, histograms were never ignored and in Oracle9i Oracle is even
      peeking at the bind variable value and uses a histogram just as it would
      for a literal. Unfortunately only for the first parse. Subsequent
      executions share the plan established by the first parse.
      Also, this is the first time that I see Peoplesoft being accused of using
      "bind variables in most or all of their programs". My experience is to the
      contrary. Bind variables are used only sporadically.
      Because of many bugs in early implementations of cursor_sharing=force and
      since excessive parsing has not been the major problem in the Peoplesoft
      applications I tended to, I have never used anything but cursor_sharing=exact.

      At 12:23 PM 12/30/2004, psftdba@yahoogroups.com wrote:

      >Message: 1
      > Date: Wed, 29 Dec 2004 08:35:06 -0800 (PST)
      > From: R P <pjksel@...>
      >Subject: CURSOR_SHARING PARAMETER IN ORACLE
      >
      >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,

      Regards

      Wolfgang Breitling
      Centrex Consulting Corporation
      www.centrexcc.com
    • R P
      I apologize we have cursor_sharing=force, not exact. Wolfgang Breitling wrote: You are not making any sense. With
      Message 2 of 4 , Jan 3, 2005
      View Source
      • 0 Attachment
        I apologize we have cursor_sharing=force, not exact.

        Wolfgang Breitling <breitliw@...> wrote:

        You are not making any sense. With cursor_sharing=exact, Oracle is NOT
        replacing literals with bind variable, only with cursor_sharing=force or
        similar. Also, histograms were never ignored and in Oracle9i Oracle is even
        peeking at the bind variable value and uses a histogram just as it would
        for a literal. Unfortunately only for the first parse. Subsequent
        executions share the plan established by the first parse.
        Also, this is the first time that I see Peoplesoft being accused of using
        "bind variables in most or all of their programs". My experience is to the
        contrary. Bind variables are used only sporadically.
        Because of many bugs in early implementations of cursor_sharing=force and
        since excessive parsing has not been the major problem in the Peoplesoft
        applications I tended to, I have never used anything but cursor_sharing=exact.

        At 12:23 PM 12/30/2004, psftdba@yahoogroups.com wrote:

        >Message: 1
        >    Date: Wed, 29 Dec 2004 08:35:06 -0800 (PST)
        >    From: R P <pjksel@...>
        >Subject: CURSOR_SHARING PARAMETER IN ORACLE
        >
        >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,

        Regards

        Wolfgang Breitling
        Centrex Consulting Corporation
        www.centrexcc.com






        PeopleSoft for the Oracle DBA is published by Apress - see http://www.psftdba.com.
        The PeopleSoft DBA Forum is managed by http://www.go-faster.co.uk.



        Do you Yahoo!?
        Yahoo! Mail - You care about security. So do we.
      • Shaun
        After finding some major performance problems with some of the delivered Psoft views, we did some investigation. Peoplesoft may use bind variables alot, but
        Message 3 of 4 , Jan 4, 2005
        View Source
        • 0 Attachment
          After finding some major performance problems with
          some of the delivered Psoft views, we did some
          investigation. Peoplesoft may use bind variables
          alot, but where-ever you have a 'like' search, it
          switches to literals. In some cases this causes query
          perfomance to vary dependant on amount of data to
          search on (e.g. we found 'A%' took 10seconds 'AB%'
          took 30 seconds and 'ABC%' took 0.1 seconds with
          Cursor_sharing=exact, most weird).
          After much work in trying to tune the view we returned
          to 'as-delivered' and set cursor_sharing=similar, all
          the above examples now take 0.04 seconds, with no
          further improvements from FORCE. All areas of
          performance have improved, no negatives have been
          reported as yet (3 months now).

          Further, it should be noted that Oracle will try a
          number of permutations through the optimizer on these
          queries. This can seriously slow things down (first
          3-4 seconds of the slowist query). We therefore set
          the following:

          optimizer_max_permutations=240

          This also helped greatly (I thing the default is
          3000). How did we get this figure? We calculated
          that with the given queries hitting the DB, the max
          number of permutations likely before the best hit was
          about 130, the rest is wasted. So 240, to be safe.

          HTH
          Shaun Lindsay
          Senior DBA
          University of Cambridge


          --- R P <pjksel@...> wrote:
          > I apologize we have cursor_sharing=force, not exact.
          >
          > Wolfgang Breitling <breitliw@...> wrote:
          > You are not making any sense. With
          > cursor_sharing=exact, Oracle is NOT
          > replacing literals with bind variable, only with
          > cursor_sharing=force or
          > similar. Also, histograms were never ignored and in
          > Oracle9i Oracle is even
          > peeking at the bind variable value and uses a
          > histogram just as it would
          > for a literal. Unfortunately only for the first
          > parse. Subsequent
          > executions share the plan established by the first
          > parse.
          > Also, this is the first time that I see Peoplesoft
          > being accused of using
          > "bind variables in most or all of their programs".
          > My experience is to the
          > contrary. Bind variables are used only sporadically.
          > Because of many bugs in early implementations of
          > cursor_sharing=force and
          > since excessive parsing has not been the major
          > problem in the Peoplesoft
          > applications I tended to, I have never used anything
          > but cursor_sharing=exact.
          >
          > At 12:23 PM 12/30/2004, psftdba@yahoogroups.com
          > wrote:
          >
          > >Message: 1
          > > Date: Wed, 29 Dec 2004 08:35:06 -0800 (PST)
          > > From: R P <pjksel@...>
          > >Subject: CURSOR_SHARING PARAMETER IN ORACLE
          > >
          > >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,
          >
          > Regards
          >
          > Wolfgang Breitling
          > Centrex Consulting Corporation
          > www.centrexcc.com
          >
          >
          >
          >
          >
          >
          > PeopleSoft for the Oracle DBA is published by Apress
          > - see http://www.psftdba.com
          > The PeopleSoft DBA Forum is managed by
          > http://www.go-faster.co.uk
          >
          >
          >
          >
          > ---------------------------------
          > Yahoo! Groups Links
          >
          > To visit your group on the web, go to:
          > http://groups.yahoo.com/group/psftdba/
          >
          > To unsubscribe from this group, send an email to:
          > psftdba-unsubscribe@yahoogroups.com
          >
          > Your use of Yahoo! Groups is subject to the
          > Yahoo! Terms of Service.
          >
          >
          >
          >
          > ---------------------------------
          > Do you Yahoo!?
          > Yahoo! Mail - You care about security. So do we.
        Your message has been successfully submitted and would be delivered to recipients shortly.