analyze table versus DBMS_STATS.GATHER_TABLE_STATS(...)

From: Raphael A. Bauer <raphael.bauer_at_informatik.hu-berlin.de>
Date: Tue, 06 Jul 2004 10:09:42 +0200
Message-ID: <40EA5E46.3060900@informatik.hu-berlin.de>

Hi Folks,

I got a really interesting topic about 9i. I encountered a big difference in the performance of my sql commands wether I use analyze table (..) or DBMS_STATS.GATHER_TABLE_STATS(...) (both "full" analyzes). DBMS_STATS is in my case about 40secs faster than analyze... The plan of both sql queries stayed the same, only the costs differed a bit. My Oracle Books say that DBMS_STAT and analyze.. are qute the same. But that's not reality... A - I forgot - my test schema is only one simple table. Nothing to do with partitions or more complex topics...

I don't want to show you my exact tables and queries. They are not so important. More important is: Where are those 40secs hidden - and what is the real difference of DBMS_STATS.GATHER_TABLE_STATS and analyze.

Thanks a lot!

Raphael

---

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 Tue Jul 06 2004 - 03:06:26 CDT