RE: PeopleSoft query
Date: Mon, 13 Jul 2009 17:33:39 +0100
And have you set _UNNEST_SUBQUERY = FALSE? PeopleSoft have recommended setting this parameter since Oracle 9i (when the default changed to TRUE).
>[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Nuno Souto
>- 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.
There is an option on the run control to do exactly this.
>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
Actually the code for this particular statement is static. It is in the
stored statement table.
So you can change it.
>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.
On Oracle 10g, if you set OPTIMIZER_DYNAMIC_SAMPLING to 4 (the default is
2), then this is quite successful.
However, if you do this, you can't collect stats in the program with the option on the run control, because using ANALYZE a table with locked stats raises an error. Unfortunately it doesn't use dbms_stats.