Re: PeopleSoft query
Date: Mon, 13 Jul 2009 22:05:47 +1000
Message-ID: <4A5B231B.200_at_iinet.net.au>
Karl Arao wrote,on my timestamp of 13/07/2009 5:56 PM:
> If you have a similar environment, and have any ideas on how to
> rewrite this SQL, or just any idea :) I'm all ears.. :)
OK, here goes.
>
> 4 141462 TABLE ACCESS FULL PS_GP_PYE_STAT_WRK (cr=3,695 pr=0
Alas, I know it well: the dreadful PS_GP_PYE_STAT_WRK... And the occasional merge join cartesian as a result, particularly in 10.2.0.3...
> -- THIS IS THE SQL STATEMENT
>
> INSERT INTO PS_GP_PYE_HIST_WRK
. . .
> SELECT P.EMPLID,
. . .
> FROM PS_GP_PYE_STAT_WRK WRK, PS_GP_PYE_PRC_STAT P, PS_GP_PYE_SEG_STAT S
> WHERE WRK.EMPLID BETWEEN :1 AND :2
etcetc. Ah yes, I know this one fairly well...
Karl,
Problems with the *WRK tables in Peoplesoft Payroll calculations are widespread.
In a nutshell, the problem is this:
- the *WRK tables are populated during the paycalc process itself.
- they are cleared at the end.
- it does not matter when/how you analyze them, they will NEVER have the correct statistics if the Peoplesoft code itself doesn't do an ANALYZE after populating them during each run.
- which doesn't happen. Hence the problem.
Possible solutions:
1- Get someone to recode the SQL to make it efficient. Most of the PS code in paycalc is auto-generated by a very bad PS tool. You may find that few developers will be willing and it makes your installation highly dependent on such custom code anyway: next release, who's gonna change any new code? Expensive, inefficient. But can produce good results if the coder knows what he/she is doing.
2- Clear the stats on the most used *WRK tables then LOCK the stats on the tables so they cannot be re-analyzed. Now, enable optimizer_dynamic_sampling database-wide and make sure the optimizer_mode is ALL_ROWS as recommended by Peoplesoft. Note: do not just empty the tables and re-analyze. That sets stats to 0 rows and won't cause dynamic sampling. The stats must be NULL - check on dba_tab_statistics - in order for the CBO to do a dynamic sample of that table whenever it sees it in a SQL statement.
3- If in any of the plans you notice "MERGE JOIN CARTESIAN" as one of the access paths, then disable that execution path from the CBO. You'll need two undocumented parameters to do so and Oracle Support will tell you which if you ask nicely. If they don't, then get back to me and I'll let you know how. This is only valid for 10.2.0.3 Oracle.
4- Whine, moan and bitch at the next Oracle conference that it is about bloody time Oracle fixed this glaring hole in the Peoplesoft paycalc code!!!!!
One thing: I am assuming you are in a reasonably modern version of Payroll and using 10g Oracle or onwards. If you are using 9i, then the deal is off: I don't think dynamic sampling works there.
Good luck, HTH.
-- Cheers Nuno Souto dbvision_at_iinet.net.au -- http://www.freelists.org/webpage/oracle-lReceived on Mon Jul 13 2009 - 07:05:47 CDT