Hey guys, I didn't get a chance to thank you both for your suggestions. Currently we are looking at leaving the tables unlocked and writing a custom script to gather stale on the non transient tables. The reason for this is that it seems that gathering stats still produces superior information to dynamic sampling when it comes to index stats. I only have anecdotal evidence to support this theory, I haven't run 10053 traces with stats and without but I have seen better plans produced with stats gathered than without and the missing piece seemed to be clustering factor. Take all of this with a grain of salt as I have not tested but just thought I'd pass this along. Have either of you ever tried running peoplesoft with cursor sharing set to exact or similar? Just wondering. Thanks again, John On 4/17/07, Wolfgang Breitling <breitliw@xxxxxxxxxxxxx> wrote:
At 09:49 AM 4/17/2007, David Kurtz wrote: >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); > >I don't approve of FOR ALL INDEXED COLUMNS SIZE 1 - that will destroy any >histograms that have been defined. I disapprove of that as well, but for a different reason. I do not approve of the "for all INDEXED columns" restriction. As if only indexed columns needed statistics. The "size 1" part destroying histograms is no different from the analyze behaviour of prior releases. Regards Wolfgang Breitling Centrex Consulting Corporation www.centrexcc.com
