RE: Histograms - SIZE clause & num

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Mon, 02 Aug 2004 11:29:48 -0600
Message-Id: <6.1.0.6.2.20040802110935.02f933d8@pop.centrexcc.com>

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_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 Mon Aug 02 2004 - 13:44:03 CDT

RE: Histograms - SIZE clause & num_buckets anomaly