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

GPPDPRUN Payroll Calculation Performance Issue

Expand Messages
  • Juarez, Jose Antonio
    I already migrated from Solaris to HP-UX and from 9i to 11.2.0.3.0. The problem that we are having is that the time of calculation of some paygroups increase a
    Message 1 of 12 , Sep 13, 2012

      I already migrated from Solaris to HP-UX and from 9i to 11.2.0.3.0. The problem that we are having is that the time of calculation of some paygroups increase a lot of time, ( about 1hr more, and the new hardware on HP-UX is most powerfull - CPU and Memory - ).  I Already run some Cobol traces and I identify some SQL sentences with the issue. I'm in the process to gather more information:

      - collect tkprof output trace for complete cobol process.

      - SQLT XTRACT output ( I you have more information about this tools please send me the links )

      - Generate a AWR report.

       

      Some of the docs. that I'm reading: ID 1305149.1, 1320764.1, 1335428.1, 1472458.1, 465787.1

       

      I'm looking for guidelines or tips to improve the performance.

    • RAVISHANKERAN R
      Hi, Have you done partitioning of the Paygroup tables. If not,then that should be your first target. Then run the Payroll process to find the long running
      Message 2 of 12 , Sep 14, 2012
        Hi,

        Have you done partitioning of the Paygroup tables. If not,then that should be your first target. Then run the Payroll process to find the long running SQL.If you would like to know more about the SQLTXTRACT there is an Oracle Advisor Webcast Archive Recording on this. You can found this in Oracle support site.

        Advisor Webcast Archived Recordings DocID 740964.1

        Thanks,
        Ravishankeran.R
        IBM




        --- In psftdba@yahoogroups.com, "Juarez, Jose Antonio" <jose-antonio.juarez@...> wrote:
        >
        > I already migrated from Solaris to HP-UX and from 9i to 11.2.0.3.0. The problem that we are having is that the time of calculation of some paygroups increase a lot of time, ( about 1hr more, and the new hardware on HP-UX is most powerfull - CPU and Memory - ). I Already run some Cobol traces and I identify some SQL sentences with the issue. I'm in the process to gather more information:
        > - collect tkprof output trace for complete cobol process.
        > - SQLT XTRACT output ( I you have more information about this tools please send me the links )
        > - Generate a AWR report.
        >
        > Some of the docs. that I'm reading: ID 1305149.1, 1320764.1, 1335428.1, 1472458.1, 465787.1
        >
        > I'm looking for guidelines or tips to improve the performance.
        >
      • Juarez, Jose Antonio
        Hi Ravish... I don t know if I have partitioning Paygroup tables, let me check. I already found information about the SQLTXTRACT: SQLT (SQLTXPLAIN) - Tool that
        Message 3 of 12 , Sep 14, 2012

          Hi Ravish…

           

          I don’t know if I have partitioning Paygroup tables, let me check.

          I already found information about the SQLTXTRACT:

          SQLT (SQLTXPLAIN) - Tool that helps to diagnose a SQL statement performing poorly [ID 215187.1]

           

          Regards

           

          From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of RAVISHANKERAN R
          Sent: viernes, 14 de septiembre de 2012 01:32 p.m.
          To: psftdba@yahoogroups.com
          Subject: PeopleSoft DBA Forum Re: GPPDPRUN Payroll Calculation Performance Issue

           

            Hi,

          Have you done partitioning of the Paygroup tables. If not,then that should be your first target. Then run the Payroll process to find the long running SQL.If you would like to know more about the SQLTXTRACT there is an Oracle Advisor Webcast Archive Recording on this. You can found this in Oracle support site.

          Advisor Webcast Archived Recordings DocID 740964.1

          Thanks,
          Ravishankeran.R
          IBM

          --- In psftdba@yahoogroups.com, "Juarez, Jose Antonio" <jose-antonio.juarez@...> wrote:
          >
          > I already migrated from Solaris to HP-UX and from 9i to 11.2.0.3.0. The problem that we are having is that the time of calculation of some paygroups increase a lot of time, ( about 1hr more, and the new hardware on HP-UX is most powerfull - CPU and Memory - ). I Already run some Cobol traces and I identify some SQL sentences with the issue. I'm in the process to gather more information:
          > - collect tkprof output trace for complete cobol process.
          > - SQLT XTRACT output ( I you have more information about this tools please send me the links )
          > - Generate a AWR report.
          >
          > Some of the docs. that I'm reading: ID 1305149.1, 1320764.1, 1335428.1, 1472458.1, 465787.1
          >
          > I'm looking for guidelines or tips to improve the performance.
          >

        • Panikulam Vivek
          Heavy usage of temporary tables is a cause of GPPDPRUN performance issues. Refer pscbo_stats procedure provided by oracle support. This provides a statistics
          Message 4 of 12 , Sep 30, 2012
            Heavy usage of temporary tables is a cause of GPPDPRUN performance issues. Refer pscbo_stats procedure provided by oracle support. This provides a statistics strategy and uses dynamic sampling on temporary tables which was quite helpful to improve performance after upgrade to 11g

            Regards
            Vivek Panikulam

            --- On Sat, 9/15/12, Juarez, Jose Antonio <jose-antonio.juarez@...> wrote:

            From: Juarez, Jose Antonio <jose-antonio.juarez@...>
            Subject: RE: PeopleSoft DBA Forum Re: GPPDPRUN Payroll Calculation Performance Issue
            To: "psftdba@yahoogroups.com" <psftdba@yahoogroups.com>
            Date: Saturday, September 15, 2012, 4:03 AM

             

            Hi Ravish…

             

            I don’t know if I have partitioning Paygroup tables, let me check.

            I already found information about the SQLTXTRACT:

            SQLT (SQLTXPLAIN) - Tool that helps to diagnose a SQL statement performing poorly [ID 215187.1]

             

            Regards

             

            From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of RAVISHANKERAN R
            Sent: viernes, 14 de septiembre de 2012 01:32 p.m.
            To: psftdba@yahoogroups.com
            Subject: PeopleSoft DBA Forum Re: GPPDPRUN Payroll Calculation Performance Issue

             

              Hi,

            Have you done partitioning of the Paygroup tables. If not,then that should be your first target. Then run the Payroll process to find the long running SQL.If you would like to know more about the SQLTXTRACT there is an Oracle Advisor Webcast Archive Recording on this. You can found this in Oracle support site.

            Advisor Webcast Archived Recordings DocID 740964.1

            Thanks,
            Ravishankeran.R
            IBM

            --- In psftdba@yahoogroups.com, "Juarez, Jose Antonio" <jose-antonio.juarez@...> wrote:
            >
            > I already migrated from Solaris to HP-UX and from 9i to 11.2.0.3.0. The problem that we are having is that the time of calculation of some paygroups increase a lot of time, ( about 1hr more, and the new hardware on HP-UX is most powerfull - CPU and Memory - ). I Already run some Cobol traces and I identify some SQL sentences with the issue. I'm in the process to gather more information:
            > - collect tkprof output trace for complete cobol process.
            > - SQLT XTRACT output ( I you have more information about this tools please send me the links )
            > - Generate a AWR report.
            >
            > Some of the docs. that I'm reading: ID 1305149.1, 1320764.1, 1335428.1, 1472458.1, 465787.1
            >
            > I'm looking for guidelines or tips to improve the performance.
            >

          • David Kurtz
            I have an alternative view · Rebuild GP working storage tables as Global Temporary Tables (use on commit preserve) – so no high water mark issues ·
            Message 5 of 12 , Sep 30, 2012

              I have an alternative view

               

              ·         Rebuild GP working storage tables as Global Temporary Tables (use on commit preserve) – so no high water mark issues

              ·         If you are using streamed GP processing , then you also need to partition all the tables to match the streaming.

              ·         Lock and delete statistics on these tables

              ·         Set Optimizer_Dynamic_Sampling to 4

              ·         Use Table preferences in 11g to control statistics collection – see http://blog.psftdba.com/2012/09/maintaining-optimizer-statistics-on.html

               

              You may still have problems with the wrong plans generated by bind variable peeking on small payroll calcs and group lists.

              In 10g, I had some success using stored outlines to enforce the execution plans used by GPPDPRUN when processing the largest paygroup, but you may need to hint stored statements directly because the outlines don’t include NO_EXPAND hints.

              I haven’t tried SQL Profiles in 11g, but they look promising.

               

              Take a look at the material under the Global Payroll link on http://www.go-faster.co.uk

               

               

               

              regards
              _________________________
              David Kurtz
              tel: +44 (0)7771 760660
              mailto:david.kurtz@...

               

               

              From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of Panikulam Vivek
              Sent: 30 September 2012 18:00
              To: psftdba@yahoogroups.com
              Subject: RE: PeopleSoft DBA Forum Re: GPPDPRUN Payroll Calculation Performance Issue

               




              Heavy usage of temporary tables is a cause of GPPDPRUN performance issues. Refer pscbo_stats procedure provided by oracle support. This provides a statistics strategy and uses dynamic sampling on temporary tables which was quite helpful to improve performance after upgrade to 11g

               

              Regards

              Vivek Panikulam

              --- On Sat, 9/15/12, Juarez, Jose Antonio <jose-antonio.juarez@...> wrote:


              From: Juarez, Jose Antonio <jose-antonio.juarez@...>
              Subject: RE: PeopleSoft DBA Forum Re: GPPDPRUN Payroll Calculation Performance Issue
              To: "psftdba@yahoogroups.com" <psftdba@yahoogroups.com>
              Date: Saturday, September 15, 2012, 4:03 AM

               

              Hi Ravish…

               

              I don’t know if I have partitioning Paygroup tables, let me check.

              I already found information about the SQLTXTRACT:

              SQLT (SQLTXPLAIN) - Tool that helps to diagnose a SQL statement performing poorly [ID 215187.1]

               

              Regards

               

              From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of RAVISHANKERAN R
              Sent: viernes, 14 de septiembre de 2012 01:32 p.m.
              To: psftdba@yahoogroups.com
              Subject: PeopleSoft DBA Forum Re: GPPDPRUN Payroll Calculation Performance Issue

               

                Hi,

              Have you done partitioning of the Paygroup tables. If not,then that should be your first target. Then run the Payroll process to find the long running SQL.If you would like to know more about the SQLTXTRACT there is an Oracle Advisor Webcast Archive Recording on this. You can found this in Oracle support site.

              Advisor Webcast Archived Recordings DocID 740964.1

              Thanks,
              Ravishankeran.R
              IBM

              --- In psftdba@yahoogroups.com, "Juarez, Jose Antonio" <jose-antonio.juarez@...> wrote:
              >
              > I already migrated from Solaris to HP-UX and from 9i to 11.2.0.3.0. The problem that we are having is that the time of calculation of some paygroups increase a lot of time, ( about 1hr more, and the new hardware on HP-UX is most powerfull - CPU and Memory - ). I Already run some Cobol traces and I identify some SQL sentences with the issue. I'm in the process to gather more information:
              > - collect tkprof output trace for complete cobol process.
              > - SQLT XTRACT output ( I you have more information about this tools please send me the links )
              > - Generate a AWR report.
              >
              > Some of the docs. that I'm reading: ID 1305149.1, 1320764.1, 1335428.1, 1472458.1, 465787.1
              >
              > I'm looking for guidelines or tips to improve the performance.
              >




            • Juarez, Jose Antonio
              Thank a lot for your invaluable support and Help, especially to David, who send me some useful tips and John Houghton from Oracle Support. This is a Resume
              Message 6 of 12 , Oct 1, 2012

                Thank a lot for your invaluable support and Help, especially to David, who send me some useful tips and John Houghton from Oracle Support.

                This is a Resume that the tasks that I did.

                -          We change some DB parameters: This 2 Docs (Advice for the PeopleSoft Oracle DBA And GPPDPRUN Payroll Calculation Performance Issue ID 1305149.1) help us to improve performance time on the Calculation phase.

                -          For the Identification Phase, we only have a problem with only one SQL Sentence, I add a new Index on PS_GP_PYE_PRC_STAT(CAL_RUN_ID, EMPLID, PRC_ORD_TS, CAL_ID).

                -          I did some test with the hint BIND AWARE (E-COB Address COBOL Performance on Oracle 11g with BIND_AWARE hint ID 1462442.1 ), but the time of calculation is almost the same.

                -          And we begin to use pscbo statistics (pscbo_stats - Improving Statistics in Oracle RDBMS for PeopleSoft Enterprise ID 1322888.1)

                We didn’t rebuilt the working storage tables as Global Temporary tables, because I read that the restart functionality on Application Engine can be affected, and another problem that I had, is that is very hard to identify temporary tables that only are used by GPPDPRUN ( this is because we had more tables that in the name had the characters TMP, TAO, TEMP, WRK ).

                We are in a sox freeze, and a windows very close to the change, (In fact, today is the first day that this new servers is on production ). But maybe the next year we will try to partition some huge tables.  

                Regards from Mexico.

                 

                 

                From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of David Kurtz
                Sent: lunes, 01 de octubre de 2012 12:36 a.m.
                To: psftdba@yahoogroups.com
                Subject: RE: PeopleSoft DBA Forum Re: GPPDPRUN Payroll Calculation Performance Issue

                 

                I have an alternative view

                 ·         Rebuild GP working storage tables as Global Temporary Tables (use on commit preserve) – so no high water mark issues

                ·         If you are using streamed GP processing , then you also need to partition all the tables to match the streaming.

                ·         Lock and delete statistics on these tables

                ·         Set Optimizer_Dynamic_Sampling to 4

                ·         Use Table preferences in 11g to control statistics collection – see http://blog.psftdba.com/2012/09/maintaining-optimizer-statistics-on.html

                You may still have problems with the wrong plans generated by bind variable peeking on small payroll calcs and group lists.

                In 10g, I had some success using stored outlines to enforce the execution plans used by GPPDPRUN when processing the largest paygroup, but you may need to hint stored statements directly because the outlines don’t include NO_EXPAND hints.

                I haven’t tried SQL Profiles in 11g, but they look promising.

                Take a look at the material under the Global Payroll link on http://www.go-faster.co.uk

                regards
                _________________________
                David Kurtz
                tel: +44 (0)7771 760660
                mailto:david.kurtz@...

                From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of Panikulam Vivek
                Sent: 30 September 2012 18:00
                To: psftdba@yahoogroups.com
                Subject: RE: PeopleSoft DBA Forum Re: GPPDPRUN Payroll Calculation Performance Issue

                 Heavy usage of temporary tables is a cause of GPPDPRUN performance issues. Refer pscbo_stats procedure provided by oracle support. This provides a statistics strategy and uses dynamic sampling on temporary tables which was quite helpful to improve performance after upgrade to 11g

                 Regards

                Vivek Panikulam

                --- On Sat, 9/15/12, Juarez, Jose Antonio <jose-antonio.juarez@...> wrote:


                From: Juarez, Jose Antonio <jose-antonio.juarez@...>
                Subject: RE: PeopleSoft DBA Forum Re: GPPDPRUN Payroll Calculation Performance Issue
                To: "psftdba@yahoogroups.com" <psftdba@yahoogroups.com>
                Date: Saturday, September 15, 2012, 4:03 AM

                Hi Ravish…

                 I don’t know if I have partitioning Paygroup tables, let me check.

                I already found information about the SQLTXTRACT:

                SQLT (SQLTXPLAIN) - Tool that helps to diagnose a SQL statement performing poorly [ID 215187.1]

                 Regards  

                From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of RAVISHANKERAN R
                Sent: viernes, 14 de septiembre de 2012 01:32 p.m.
                To: psftdba@yahoogroups.com
                Subject: PeopleSoft DBA Forum Re: GPPDPRUN Payroll Calculation Performance Issue

                   Hi,

                Have you done partitioning of the Paygroup tables. If not,then that should be your first target. Then run the Payroll process to find the long running SQL.If you would like to know more about the SQLTXTRACT there is an Oracle Advisor Webcast Archive Recording on this. You can found this in Oracle support site.

                Advisor Webcast Archived Recordings DocID 740964.1

                Thanks,
                Ravishankeran.R
                IBM

                --- In psftdba@yahoogroups.com, "Juarez, Jose Antonio" <jose-antonio.juarez@...> wrote:
                >
                > I already migrated from Solaris to HP-UX and from 9i to 11.2.0.3.0. The problem that we are having is that the time of calculation of some paygroups increase a lot of time, ( about 1hr more, and the new hardware on HP-UX is most powerfull - CPU and Memory - ). I Already run some Cobol traces and I identify some SQL sentences with the issue. I'm in the process to gather more information:
                > - collect tkprof output trace for complete cobol process.
                > - SQLT XTRACT output ( I you have more information about this tools please send me the links )
                > - Generate a AWR report.
                >
                > Some of the docs. that I'm reading: ID 1305149.1, 1320764.1, 1335428.1, 1472458.1, 465787.1
                >
                > I'm looking for guidelines or tips to improve the performance.
                >

              Your message has been successfully submitted and would be delivered to recipients shortly.