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

RE: PeopleSoft DBA Forum Re: GPPDPRUN Payroll Calculation Performance Issue

Expand Messages
  • 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 1 of 12 , Oct 1, 2012
    • 0 Attachment

      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.