Re: Stats gather job for PeopleSoft Tools version 8.4.8
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: darrah.john@xxxxxxxxx
- Date: Wed, 11 Apr 2007 10:43:19 -0600
At 09:14 AM 4/11/2007, John Darrah wrote:
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?
for 1) and 2):
I am not aware of an event to suppress the error. Since you are
writing a custom job you can do either of two things
- find out if a table is locked before calling dbms_stats.gather
or
- declare an exception for the error and catch it with a NULL when clause
additionally to catch those cases where tables get analyzed when they
are empty, but are not when used I check num_rows after the
statistics are gathered and delete the stats if num_rows=0.
for 3)
To globally disable statistics updating by AE jobs set the dbflag in
the process scheduler config (psprcs.cfg) to 1 - or the next higher
odd value if you have it set to 4, 8, or 12 already
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________
--
//www.freelists.org/webpage/oracle-l
Other related posts:
