Re: Performance of DBMS_STATS vs ANALYZE
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 21 May 2004 04:52:58 -0600
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
