Re: DBMS_STATS

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jeff Landers
  INET: jlanders_at_convergys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).