RE: Stats gather job for PeopleSoft Tools version 8.4.8

Wolfgang

You have be told the truth (I've just looked at PT8.48.07).  These are the
default DDL Models.

DBMS_STATS.GATHER_TABLE_STATS (ownname=> [DBNAME], tabname=>[TBNAME],
estimate_percent=>1, method_opt=> 'FOR ALL COLUMNS SIZE 1',cascade=>TRUE);

DBMS_STATS.GATHER_TABLE_STATS (ownname=> [DBNAME], tabname=>[TBNAME],
estimate_percent=> dbms_stats.auto_sample_size, method_opt=> 'FOR ALL
INDEXED COLUMNS SIZE 1',cascade=>TRUE);

So, PeopleTools development have added a new variable [DBNAME], and they
must have removed the schema name from [TBNAME].
I don't approve of FOR ALL INDEXED COLUMNS SIZE 1 - that will destroy any
histograms that have been defined.

However, going back to the original question, it will still be necessary to
encapsulate DBMS_STATS inside another packaged procedure to catch the errors
for locked statistics (which are slightly different)

ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 13056
ORA-06512: at "SYS.DBMS_STATS", line 13076
ORA-06512: at line 2



> -----Original Message-----
> From: Wolfgang Breitling [mailto:breitliw@xxxxxxxxxxxxx] 
> Sent: Tuesday, April 17, 2007 3:53 PM
> To: info@xxxxxxxxxxxxxxx
> Cc: darrah.john@xxxxxxxxx; 'Oracle-L Freelists'
> Subject: RE: Stats gather job for PeopleSoft Tools version 8.4.8
> Importance: High
> 
> I have not seen it myself, but I have been told that 8.48 
> changed those model statements to use 
> dbms_stats.gather_table_stats. Maybe the OP can confirm or deny that.
> 
> At 04:06 AM 4/17/2007, David Kurtz wrote:
> >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.
> 
> Regards
> 
> Wolfgang Breitling
> Centrex Consulting Corporation
> www.centrexcc.com 
> 
> 
> 

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

Other related posts: