Stats gather job for PeopleSoft Tools version 8.4.8
- From: "John Darrah" <darrah.john@xxxxxxxxx>
- To: "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 11 Apr 2007 09:14:03 -0600
Does anyone know of a good way to disable the people tools stats gathering
steps that sometimes run as part of an app engine batch job? Here is my
basic problem.
I have deleted statistics on all TAO, TEO, and P_SEL_ALLOC tables and locked
them so I can use, the proc_gather_stats oracle job to gather database stats
and use dynamic sampling for the tables with locked stats. This all works
fine except that some peoplesoft jobs try to gather stats on these tables
and the error thrown causes the job to fail. At this point I have unlocked
the stats hoping that peoplesoft would consistantly gather stats on these
tables when it needed to. The problem is that peoplesoft does not always
gather stats prior to running these jobs and because the proc_gather_stats
procedure now analyzes the previously locked tables, they show as having 0
rows and the CBO incorrectly determines a merge join cartesion as the best
join method. At this point my choices as I see them are to
1) write a custom gather_stats job that manually excludes TAO,TEO, and
P_SEL_ALLOC tables.
2) set some event so dbms_stats does not throw an error when attempting to
gather stats on a locked table (don't even know if such an event exists)
3) turn off the gather stats step globally in peopleSoft (don't know if this
is possible)
If anyone else out there has experienced something similar, can you let me
know what solution you used?
Thanks,
John
Other related posts:
