Re: Performance of DBMS_STATS vs ANALYZE

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Fri, 21 May 2004 04:52:58 -0600
Message-Id: <6.1.0.6.2.20040521043711.030624c8@pop.centrexcc.com>

Yes, they are vital. IMO if you are running dbms_stats (or analyze) you MUST gather the basic data for the columns - num_distinct, nulls, min, max, avg_col_len - which is what the 1-bucket histogram is. Technically you, or rather the CBO, only needs this data for columns which are ever used in a predicate, but
a) it is very difficult to know that
b) it is just as time consuming to do the 1-bucket histogram for all columns of the table as it is to do it for a select list of columns.

If you need to reduce the time spent on running dbms_stats.gather_xxx then run fewer of them. The majority of them are a pure waste of time. Some of them are absolutely vital to be run at the right time, and some will at some time cause performance problems.
Know your data and know which tables need to be re-analyzed after what processes have changed the data composition such that the statistics need to be recalculated. And it is not that "10% changed" rule that table monitoring uses.

And use dbms_stats.export_xxx_stats, either explicitly (my preference) or implicitly with the gather so that you can restore the previous statistics when the new statistics cause trouble. Note that I said when, not if. It's only a matter of time.

At 04:21 AM 5/21/2004, you wrote:
>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!
>

regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

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:50:04 CDT