RE: PeopleSoft DBA Forum Re: GPPDPRUN Payroll Calculation Performance Issue
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.
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
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
--- 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: "email@example.com" <firstname.lastname@example.org>
Date: Saturday, September 15, 2012, 4:03 AM
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]
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
--- In email@example.com, "Juarez, Jose Antonio" <jose-antonio.juarez@...> wrote:
> I already migrated from Solaris to HP-UX and from 9i to 184.108.40.206.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.