RE: Histograms - SIZE clause & num_buckets anomaly
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 02 Aug 2004 11:29:48 -0600
I have the impression it is 2.
Many sites, when they collect histograms, adopt the, in my view dangerous,
approach to use method_opt=>'for all indexed columns' or method_opt=>'for
all columns', often leaving the size choice to default, or use the maximum
of 254. I take that from the posts that I occasionally see at metalink or
newsgroups where People wonder why gather_table_stats takes so much longer
than analyze did in Oracle 8 only to discover that they collect histograms
for all 200+ columns of a multi-million row table.
In my view histograms are like a scalpel, not like a chainsaw. They are a
precision instrument and each use warrants careful consideration and
testing of the correct bucket size. One size, as in "for all columns [ size
254]" certainly does not fit all.
I'll probably get inundated with e-mail from all the outdoorsmen who
consider a chainsaw a precision instrument.
I already have a showcase to demonstrate that "for all indexed columns ..."
is not good enough; that a histogram on an un-indexed column can improve an
access plan. One of these days I'm confident I pull it together and create
a showcase that demonstrate that "for all [indexed] columns ..." not only
wastes resources but that a histogram on an indexed column can be
detrimental to an access plan.
At 09:27 AM 8/2/2004, you wrote:
>1. I hadn't searched carefully enough.
>2. Not many people pay attention to histogram creation.
>3. Not too many shops have D/W environments (which would make DBAs consider
>histograms).
>4. I am overestimating the impact of the issue.
>
>Must be one or more of the above. I still think this needs to be fixed (has
>it been, in 10G?) i.e. instead of choosing some number more than 50%, there
>should be a better way.
>
>Anyways, it was nice to get the doubt solved.
>
>Thanks & regards,
>Charu.
Regards
Wolfgang Breitling
Centrex Consulting Corporation
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:
