RE: Histograms
- From: "Murching, Bob" <bob_murching@xxxxxxxxx>
- To: "'charlottejanehammond@xxxxxxxxx'" <charlottejanehammond@xxxxxxxxx>, "'ORACLE-L'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 16 Aug 2005 10:38:05 -0400
Tried exactly that when benchmarking some existing apps in a new
environment. I suspect that the risk-benefit ratio for global histogram
collection is going to vary too much from shop to shop to provide any
general answer. From my testing, there was a notable benefit but (like Mr.
Breitling's anecdote) at least one batch process that suffered enormously.
I think it's a great thing to kick around as part of doing a database
upgrade. As with many complex and relatively modern software features,
choosing to implement them is a bit of a betting process. Do you presume
that extensive use of histograms is part of the future of Oracle and
therefore getting comfortable with 'em now is probably a good bet? Or do
you presume that their value will remain on the periphery of CBO efficiency
and maybe it's better to play it safe and look for some other hill (with
greater rewards) to die on? I lean toward "histogram relevance and
contribution is increasing" and will probably give global histogram
collection a "first date" when we do our next version upgrade. I'm a sucker
for the lure of better performance. But I'd never take similar chances
with, say, database backup/recovery...
-----Original Message-----
From: Charlotte Hammond [mailto:charlottejanehammond@xxxxxxxxx]
Sent: Tuesday, August 16, 2005 10:16 AM
To: ORACLE-L
Subject: Re: Histograms
Thanks all.
Perhaps to play devil's advocate slightly (as I said, we don't use
histograms much here for the reasons
given): why not turn this on its head and say gather ALL histograms (so CBO
has maximum information) and then remove those that cause trouble? (And
since you don't compulsively regather stats of course, it's only a one-off
exercise to get the histograms :-)
I guess I was also slightly interested in the detail behind the web site - I
think David Kurtz paraphrased it well when he said 'I heard about this from
some people, it sounds like a good idea but I haven't tried it for myself'.
Has anyone talked about this to the people listed, namely Jeff Maresh, Arup
Nanda or Mike Ault, who appear to HAVE actually tried it and seem to think
there's something in it; I'm curious as to their thinking.
Thank you
Charlotte
--- Wolfgang Breitling <breitliw@xxxxxxxxxxxxx> wrote:
> Like David and Yasin, I am against the "broad brush"
> method and use
> histograms only in specific cases with demonstrated benefits.
> I don't have a testcase, but I have a documented case at a client
> where they changed their week(end)ly statistics gathreing from the
> default "for all columns size 1" to "for all indexed columns size
> skewonly" and had the bottom fall out of a batch job that usually took
> 1.5 minutes because one frequently executes sql (1000s of times) went
> from .01 elapsed to around 80 seconds elapsed.
> So be careful whenever you change your statistics gathering strategy.
> And that is not limited to histograms.
>
> Charlotte Hammond wrote:
>
> > Hi All,
> >
> > Can I poll the list on their views on the use of histograms? I've
> > previously been quite
> conservative,
> > only gathering them when we've had a specific case
> of
> > bad CBO performance due to skewed data. However I
> was
> > interested in this article mentioning a "broad
> brush"
> > approach to histogram gathering:
> >
> >
>
http://www.dba-oracle.com/oracle_tips_all_columns_histograms.htm
> >
> > Any thoughts to share?
> >
> > Thank you
> > Charlotte
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam? Yahoo! Mail has the best spam
> protection around
> > http://mail.yahoo.com
> > --
> > //www.freelists.org/webpage/oracle-l
> >
>
> --
> Regards
>
> Wolfgang Breitling
> Centrex Consulting Corporation
> www.centrexcc.com
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
