Re: Performance of DBMS_STATS vs ANALYZE

From: Charlotte Hammond <charlottejanehammond_at_yahoo.com>
Date: Fri, 21 May 2004 03:21:00 -0700 (PDT)
Message-ID: <20040521102100.6161.qmail@web20723.mail.yahoo.com>

Hi Connor

• Connor McDonald wrote: >

Notice that your analyze command did not calculate column stats, but the dbms_stats did. You'll get a "fairer" assessment if you analyze command is: ANALYZE TABLE myschema.<table> ESTIMATE STATISTICS SAMPLE <<n>> PERCENT FOR TABLE FOR ALL INDEXES
for all columns size 1;  

--
 

Thanks for the suggestion - if I gather columns stats using ANALYZE it does slow things down a bit. It is still 2x faster than DBMS_STATS, but not 3 or 4x as it was before.  

But ideally I'd like make DBMS_STATS faster, not ANALYZE slower :-) Are these 1-bucket histograms on each column useful to the CBO? If not, can they be turned off (METHOD_OPT=>'FOR ALL COLUMNS SIZE 0' doesn't do anything).  

As Jonathan Lewis also suggested, perhaps I'm dwelling on this more than I need to - but I'm curious!  

Thanks
- Charlotte                       

---

Do you Yahoo!?
Yahoo! Domains - Claim yours for only $14.70/year

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

---

Received on Fri May 21 2004 - 05:17:51 CDT