Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Stats gather job for PeopleSoft Tools version 8.4.8

RE: Stats gather job for PeopleSoft Tools version 8.4.8

From: David Kurtz <info_at_go-faster.co.uk>
Date: Tue, 17 Apr 2007 11:06:57 +0100
Message-ID: <00a501c780d8$23a9a480$0a0a0a0a@GOFASTER4>


When PeopleSoft AE jobs gather statistics they do so with the %UpdateStats macro in the application engine. The macro uses the DDL models (4 & 5), and evaluates to ANALYZE TABLE command.

CREATE TABLE t (a NUMBER);
exec DBMS_STATS.LOCK_TABLE_STATS('SCOTT','T'); ANALYZE TABLE t ESTIMATE STATISTICS;

ERROR at line 1:
ORA-38029: object statistics are locked

The %UpdateStats macro was introduced to solve the genuine problem of statistics being incorrect on working storage tables in the middle of batch processes. It also predates Dynamic Sampling in Oracle.

You could customise the application engine step to ignore any error - but then you would have to do this to a large number of places.

I prefer to customise the DDL models - however, be away that each minor tools upgrade will usually reset the DDL model. I have written a PL/SQL procedure (attached) that can be called from the DDL model default so that %UpdateStats uses DBMS_STATS instead of ANALYZE. I will now have to update it to trap the exception from locked statistics (thank you Wolfgang - I hadn't thought of that).

regards



David Kurtz
Go-Faster Consultancy Ltd.
tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
mailto:david.kurtz_at_go-faster.co.uk
web: www.go-faster.co.uk
Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com The PeopleSoft DBA Blog: http://psftdba.blogspot.com PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Wolfgang Breitling
> Sent: Wednesday, April 11, 2007 5:43 PM
> To: darrah.john_at_gmail.com
> Cc: Oracle-L Freelists
> Subject: Re: Stats gather job for PeopleSoft Tools version 8.4.8
> Importance: High
>
> 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
> ______________________________________________________________________
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>


--
http://www.freelists.org/webpage/oracle-l


Received on Tue Apr 17 2007 - 05:06:57 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US