RE: analyze table versus DBMS_STATS.GATHER_TABLE

From: <Satheesh.Babu_at_iflexsolutions.com>
Date: Tue, 6 Jul 2004 13:47:03 +0530
Message-ID: <10898BE7CA96D611988B000802255AAF06FAF698@fmgrt.rt.i-flex.com>

DBMS_STATS in 9i consider cpu and io costing too. If you see plan_table in 9i you will have 3 extra columns. May be this is taking is helping you.

Thanks and Regards,
Satheesh Babu.S
Associate Consultant.
080-57593938
Bangalore.
India.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Raphael A. Bauer Sent: Tuesday, July 06, 2004 1:40 PM
To: oracle-l_at_freelists.org
Subject: analyze table versus DBMS_STATS.GATHER_TABLE_STATS(...)

Hi Folks,

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

I don't want to show you my exact tables and queries. They are not so=20 important. More important is: Where are those 40secs hidden - and what=20 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

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

Received on Tue Jul 06 2004 - 03:09:42 CDT

RE: analyze table versus DBMS_STATS.GATHER_TABLE_STATS(...)