Re: DBMS_STATS

[Jeff Landers]

Terrian, Tom (Contractor) (DAASC) wrote:

 I have never had good luck with DBMS_STATS.  It seems that the old analyze runs much faster.....Runs in 45 seconds:analyze table log_trans partition (log_trans_20030104) estimate statistics sample 5 percent; Takes over 2 hours:execute dbms_stats.gather_table_stats(ownname => 'LDGADMIN', -
                                      tabname => 'LOG_TRANS', -
                                      partname => 'LOG_TRANS_20030102', -
                                      estimate_percent => 5);
Am I missing something?  Aren't both commands the same?Thanks,Tom

  Hello Tom

We too experienced terrible performance upon first using dbms_stats.    There
are two things we did that brought the performance in line with the analyze.
1.  We set method_opt =  'FOR ALL INDEXED COLUMNS SIZE 1'
2.  We set  estimate_percent = 15

Hope this helps.

BTW:
Sun/Solaris 2.6 & 2.8
Running 8i, 9i, 9.2
5 terabyte db's

Jeff