| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Histograms - SIZE clause & num_buckets anomaly
Wolfgang,
How are you? Hope you're doing well.
Just wondering, if I have a database where we already generated histograms on everything, will method_opt=>'for all columns size skewonly' delete the old histograms and just leave me with the ones that are skewed? Or do I have to delete statistics first, like your other post mentioned?
"Also, in order to delete statistics I still use analyze because delete_table_stats doesn't delete index stats."
I wanted to follow up with the "repeat" option or just not analyze at all.
Thanks Larry
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Wolfgang Breitling
Sent: Monday, August 02, 2004 12:30 PM
To: oracle-l_at_freelists.org
Subject: RE: Histograms - SIZE clause & num_buckets anomaly
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
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ********************************************************************** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. ---------------------------------------------------------------- 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 - 15:57:08 CDT
 |
 |