| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Stats gather job for PeopleSoft Tools version 8.4.8
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
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Apr 11 2007 - 11:43:19 CDT
 |
 |