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

Re: PeopleSoft DBA Forum Digest Number 301

Expand Messages
  • SP
    I think I saw some postining on this Oracle parameter in PeopleSoft website. May be helpful to you in your case. Sri ...
    Message 1 of 4 , Dec 30, 2004
    • 0 Attachment
      I think I saw some postining on this Oracle parameter
      in PeopleSoft website. May be helpful to you in your
      case.

      Sri

      --- psftdba@yahoogroups.com wrote:

      >
      > ------------------------ Yahoo! Groups Sponsor
      > --------------------~-->
      > $4.98 domain names from Yahoo!. Register anything.
      >
      http://us.click.yahoo.com/Q7_YsB/neXJAA/yQLSAA/JqWylB/TM
      >
      --------------------------------------------------------------------~->
      >
      >
      > There is 1 message in this issue.
      >
      > Topics in this digest:
      >
      > 1. CURSOR_SHARING PARAMETER IN ORACLE
      > From: R P <pjksel@...>
      >
      >
      >
      ________________________________________________________________________
      >
      ________________________________________________________________________
      >
      > 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,
      >
      >
      > ---------------------------------
      > Do you Yahoo!?
      > Yahoo! Mail - 250MB free storage. Do more. Manage
      > less.
      >
      > [This message contained attachments]
      >
      >
      >
      >
      ________________________________________________________________________
      >
      ________________________________________________________________________
      >
      >
      > 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
      >
      >
      > psftdba-unsubscribe@yahoogroups.com
      >
      >
      >
      ------------------------------------------------------------------------
      >
      >
      >
      >
      >
    • 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 2 of 4 , Dec 30, 2004
      • 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 3 of 4 , Jan 3, 2005
        • 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 4 of 4 , Jan 4, 2005
          • 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.