Re: PeopleSoft query

From: Nuno Souto <>
Date: Tue, 14 Jul 2009 20:43:11 +1000
Message-ID: <>

Karl Arao wrote,on my timestamp of 14/07/2009 5:09 AM:

> Stephane:
> -----------
> Thanks for the idea on analytical functions.. I agree with you (also
> with the index scan on sub queries), the lesser/efficient the work the
> faster it gets.
> I'll explore on that and will discuss with the developer on how will
> this affect the functionality and any dependencies.

FWIW: we have followed Stephane's idea of using the analytical functions in some of these subqueries so common in peoplesoft and it works like a charm. Unfortunately, our Peoplesoft guys seem to be allergic to any changes to PS-released code so we can only use it in site-specific custom code.

> - Are you running multiple concurrent payroll calculation processes?

This seems to be a complete no-no in our installation. Single pay calcs work like a charm nowadays. Even full monthly pay calcs rarely take more than 5 minutes. As soon as someone starts two of them, even if they are just for one week and half a dozen temps, all heck breaks lose and we get huge blow-ups in runtimes.

> Yes we have encountered a "MERGE JOIN CARTESIAN" on a particular SQL,
> and it made the payroll run for so long and we need to cancel it and
> re-run again. To permanently disable cartesian joins, we used the

There is also *mjc_enabled and in a specific syntax mentioning the word "cartesian". Look it up in the x$ parameter table in your version of Oracle. I'll look up the two we are using and post them in the blog: long overdue anyway.

> Regarding the *WRK tables, what is your OPTIMIZER_DYNAMIC_SAMPLING
> value when you locked the table statistics? is it 4 as David
> suggested?

Yes, indeed: 4.

 > I've also read your nice post on PeopleSoft
 > And found some references at Metalink3

Please note those were mostly related to In there have been a lot of bug fixes to the CBO and the number of required undocumented parameters is a lot less. For example, the _GBY_HASH_AGGREGATION_ENABLED one you mentioned before is not needed anymore in the bug it addressed is fixed in this release, if you install all the patches to recommended in the Peoplesoft install guide. I have a piece of SQL that exposes the bug in, have tested it in and indeed it is gone.

Nuno Souto
in sunny Sydney, Australia
Received on Tue Jul 14 2009 - 05:43:11 CDT

Original text of this message