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 16:49:59 +0100
Message-ID: <000601c78108$0ffc1a50$0a0a0a0a@GOFASTER4>


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_at_centrexcc.com]
> Sent: Tuesday, April 17, 2007 3:53 PM
> To: info_at_go-faster.co.uk
> Cc: darrah.john_at_gmail.com; '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
Received on Tue Apr 17 2007 - 10:49:59 CDT

Original text of this message

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