Re: PeopleSoft query
Date: Tue, 14 Jul 2009 20:43:11 +1000
Message-ID: <4A5C613F.9080204_at_iinet.net.au>
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
> hidden parameter _OPTIMIZER_SORTMERGE_JOIN_ENABLED = FALSE
There is also *mjc_enabled and in 10.2.0.3 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 > http://dbasrus.blogspot.com/2007/09/more-on-peoplesoft.html > http://dbasrus.blogspot.com/2007/09/one-for-peoplesoft-folks.html > And found some references at Metalink3
Please note those were mostly related to 10.2.0.2. In 10.2.0.3 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 10.2.0.3: the bug it addressed is fixed in this release, if you install all the patches to 10.2.0.3 recommended in the Peoplesoft install guide. I have a piece of SQL that exposes the bug in 10.2.0.2, have tested it in 10.2.0.3 and indeed it is gone.
-- Cheers Nuno Souto in sunny Sydney, Australia dbvision_at_iinet.net.au -- http://www.freelists.org/webpage/oracle-lReceived on Tue Jul 14 2009 - 05:43:11 CDT