RE: Stats gather job for PeopleSoft Tools version 8.4.8
- From: "David Kurtz" <info@xxxxxxxxxxxxxxx>
- To: "'Wolfgang Breitling'" <breitliw@xxxxxxxxxxxxx>
- Date: Tue, 17 Apr 2007 16:49:59 +0100
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
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
