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

Problem w/ payroll calc after upgrading to Oracle 9i

Expand Messages
  • Winslow Chang
    Has anyone had an issue with their calc running really long after upgrading to Oracle 9i (9.2.0.3) I m running on an HPUX11.0 64 bit box. Seems like the
    Message 1 of 3 , Oct 23, 2003
    • 0 Attachment
      Has anyone had an issue with their calc running really long after
      upgrading to Oracle 9i (9.2.0.3)
      I'm running on an HPUX11.0 64 bit box.

      Seems like the oracle optimizer is now favoring fast_full_scans over some
      range scans for a couple
      of the SQL calc which is giving me horrendous run times.

      Has anyone migrated to 9.2.0.3 from 8.1.7 on Tools 8.1.8, HRMS 8.3??
      Any tips for the init.ora file I should make to preserve all optimizer
      behavior for 8.1.7?
      I already had optimizer_features_enabled set to 8.1.6 as on the old instance
      but this didn't seem to make a difference in 9i



      thanks in advance,
      Winslow

      -------------------------------------------------------------------------
      This electronic mail message contains information that (a) is or may be CONFIDENTIAL, PROPRIETARY IN NATURE, OR OTHERWISE
      PROTECTED BY LAW FROM DISCLOSURE, and (b) is intended only for the use of the addressee(s) named herein. If you are not an
      intended recipient, please contact us immediately at postmaster@... and take the steps necessary to delete the message
      completely from your computer system.
    • David Kurtz
      You have asked a classic question - Why does my execution plan change when I upgrade my database? First I would start by identifying the statements that are
      Message 2 of 3 , Oct 27, 2003
      • 0 Attachment
        You have asked a classic question - 'Why does my execution plan change when I upgrade my database?'
         
        First I would start by identifying the statements that are now causing a problem.  Use SQL_TRACE (or event 10046) to identify the statements that are now taking the most time (sounds like you may already have done this).
         
        Then see if there are differences in statistics between the tables and if so why.
        See if any parameters have changed value (including hidden parameters) - use event 10053 to see these, and to see why the optimiser chose a different path.
         
        One of the things that does change is the way Oracle finds ways of reducing the number of consistent reads.  For instance, you will see Pins instead of CRs.  This has the effect of lowering the row cache hit ratio when you upgrade.  This also changes the relative costs of SQL operations.
         
        Further reading:
         

        Tim Gorman: www.evdbt.com. The search for Intelligent Life in the Cost Based Optimiser.

        Wolfgang Breitling: www.centrexcc.com. Looking under the hood of the CBO.

        Jonathan Lewis: www.jlcomp.demon.co.uk: Lots of articles about CBO

         

         

        _________________________
        David Kurtz
        Go-Faster Consultancy Ltd.
        tel: +44 (0)7771 760660
        fax: +44 (0)7092 348865
        mailto:david.kurtz@...
        web: www.go-faster.co.uk
        PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

        -----Original Message-----
        From: Winslow Chang [mailto:WChang@...]
        Sent: 23 October 2003 19:17
        To: 'psftdba@yahoogroups.com'
        Subject: PeopleSoft DBA Forum Problem w/ payroll calc after upgrading to Oracle 9i


        Has anyone had an issue with their calc running really long after
        upgrading to Oracle 9i (9.2.0.3)
        I'm running on an HPUX11.0 64 bit box.

        Seems  like the oracle optimizer is now favoring fast_full_scans over some
        range scans for a couple
        of the SQL calc which is giving me horrendous run times.

        Has anyone migrated to 9.2.0.3 from 8.1.7 on Tools 8.1.8, HRMS 8.3??
        Any tips for the init.ora file I should make to preserve all optimizer
        behavior for 8.1.7?
        I already had optimizer_features_enabled set to 8.1.6 as on the old instance
        but this didn't seem to make a difference in 9i



        thanks in advance,
        Winslow

        -------------------------------------------------------------------------
        This electronic mail message contains information that (a) is or may be CONFIDENTIAL, PROPRIETARY IN NATURE, OR OTHERWISE
        PROTECTED BY LAW FROM DISCLOSURE, and (b) is intended only for the use of the addressee(s) named herein.  If you are not an
        intended recipient, please contact us immediately at postmaster@...  and take the steps necessary to delete the message
        completely from your computer system.



        To unsubscribe from this group, send an email to:
        psftdba-unsubscribe@yahoogroups.com

        The PeopleSoft DBA Forum is managed by Go-Faster Consultancy Ltd. http://www.go-faster.co.uk


        Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
      • litbighor
        Hi, May be you can read this article Doc ID 922202.1 from Metalink about outlines ? I ve never used them yet. ... change when ... causing a ... that are ...
        Message 3 of 3 , Nov 5, 2003
        • 0 Attachment
          Hi,

          May be you can read this article Doc ID 922202.1 from Metalink
          about outlines ?
          I've never used them yet.



          --- In psftdba@yahoogroups.com, "David Kurtz" <david.kurtz@g...>
          wrote:
          > You have asked a classic question - 'Why does my execution plan
          change when
          > I upgrade my database?'
          >
          > First I would start by identifying the statements that are now
          causing a
          > problem. Use SQL_TRACE (or event 10046) to identify the statements
          that are
          > now taking the most time (sounds like you may already have done
          this).
          >
          > Then see if there are differences in statistics between the tables
          and if so
          > why.
          > See if any parameters have changed value (including hidden
          parameters) - use
          > event 10053 to see these, and to see why the optimiser chose a
          different
          > path.
          >
          > One of the things that does change is the way Oracle finds ways of
          reducing
          > the number of consistent reads. For instance, you will see Pins
          instead of
          > CRs. This has the effect of lowering the row cache hit ratio when
          you
          > upgrade. This also changes the relative costs of SQL operations.
          >
          > Further reading:
          >
          > Tim Gorman: www.evdbt.com. The search for Intelligent Life in the
          Cost Based
          > Optimiser.
          >
          > Wolfgang Breitling: www.centrexcc.com. Looking under the hood of
          the CBO.
          >
          > Jonathan Lewis: www.jlcomp.demon.co.uk: Lots of articles about CBO
          >
          >
          >
          >
          > _________________________
          > David Kurtz
          > Go-Faster Consultancy Ltd.
          > tel: +44 (0)7771 760660
          > fax: +44 (0)7092 348865
          > mailto:david.kurtz@g...
          > web: www.go-faster.co.uk
          > PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
          >
          > -----Original Message-----
          > From: Winslow Chang [mailto:WChang@b...]
          > Sent: 23 October 2003 19:17
          > To: 'psftdba@yahoogroups.com'
          > Subject: PeopleSoft DBA Forum Problem w/ payroll calc after
          upgrading to
          > Oracle 9i
          >
          >
          >
          > Has anyone had an issue with their calc running really long after
          > upgrading to Oracle 9i (9.2.0.3)
          > I'm running on an HPUX11.0 64 bit box.
          >
          > Seems like the oracle optimizer is now favoring fast_full_scans
          over some
          > range scans for a couple
          > of the SQL calc which is giving me horrendous run times.
          >
          > Has anyone migrated to 9.2.0.3 from 8.1.7 on Tools 8.1.8, HRMS
          8.3??
          > Any tips for the init.ora file I should make to preserve all
          optimizer
          > behavior for 8.1.7?
          > I already had optimizer_features_enabled set to 8.1.6 as on the
          old
          > instance
          > but this didn't seem to make a difference in 9i
          >
          >
          >
          > thanks in advance,
          > Winslow
          >
          > ------------------------------------------------------------------
          -------
          > This electronic mail message contains information that (a) is or
          may be
          > CONFIDENTIAL, PROPRIETARY IN NATURE, OR OTHERWISE
          > PROTECTED BY LAW FROM DISCLOSURE, and (b) is intended only for
          the use of
          > the addressee(s) named herein. If you are not an
          > intended recipient, please contact us immediately at
          postmaster@b...
          > and take the steps necessary to delete the message
          > completely from your computer system.
          >
          >
          > Yahoo! Groups Sponsor
          > ADVERTISEMENT
          >
          >
          >
          >
          > To unsubscribe from this group, send an email to:
          > psftdba-unsubscribe@yahoogroups.com
          >
          > The PeopleSoft DBA Forum is managed by Go-Faster Consultancy Ltd.
          > http://www.go-faster.co.uk
          >
          > Your use of Yahoo! Groups is subject to the Yahoo! Terms of
          Service.
        Your message has been successfully submitted and would be delivered to recipients shortly.