Re: PeopleSoft query

  • From: Nuno Souto <dbvision@xxxxxxxxxxxx>
  • Date: Tue, 14 Jul 2009 20:14:49 +1000

David Kurtz wrote,on my timestamp of 14/07/2009 2:33 AM:

And have you set _UNNEST_SUBQUERY = FALSE?  PeopleSoft have recommended
setting this parameter since Oracle 9i (when the default changed to TRUE).

A long time ago.  It's in the pre-install tasks of the Peoplesoft install guide.


- 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.

Can you tell me which/where it is? Because I've asked for this for ages and no one in our Peoplesoft technical team knows how to turn this on.


Actually the code for this particular statement is static.  It is in the
stored statement table.
So you can change it.

Thanks. Is that ported to a new version as well? That seems to be the major objection of our Peoplesoft guys to any changes to released PS code.


On Oracle 10g, if you set OPTIMIZER_DYNAMIC_SAMPLING to 4 (the default is
2), then this is quite successful.

Yes, 4 is what we have set it to.

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.

Indeed. But in the absence of anyone here knowing how to turn it on, I had to go with DS and locked stats.



--
Cheers
Nuno Souto
in sunny Sydney, Australia
dbvision@xxxxxxxxxxxx
--
//www.freelists.org/webpage/oracle-l


Other related posts: