Re: Upgrade horror stories/victories

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Wed, 24 Nov 1999 20:59:28 +0800
Message-ID: <383BE130.6A2E@yahoo.com>

Jonathan Lewis wrote:
>
> Could it be that the 'one bucket histogram'
> was actually 'a histogram on one column' ?
>
> The
> <pair of rows in user_tab_histograms (faked
> in, even if you haven't generated a histogram).>
> is simply a different representation of the
> low_value / high_value which is also displayed
> in user_tab_columns.
>
> You are quite right, of course, there are always
> likely to be a few special cases where the
> averaging effect of a simple analyze is never
> good enough - even at 100% - and Oracle needs
> a histogram to get a good result. The default
> bucket count is 75 buckets though, so over-casual
> use of histograms can actually lead to a drop in
> performance of low-volume queries due to excessive
> parse costs.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> r.golds wrote in message
> <81hek0$81l$1_at_ssauraab-i-1.production.compuserve.com>...
> >Dear Johnathan Lewis
> >
> >We DID analyze the tables.
> >
> >We always import followed by a number of clean up things (analyze estimate
> >statistics 20000 rows, alter procedure .. compile, etc).
> >
> >I remember asking them to create a one bucket histogram, and they did
> >whatever the minimum was. It might not have been 'one' bucket. And it was
> >the extra histogram command that made the difference, according to my
> staff.
> >
> >
> >I note your <and also a pair of rows in user_tab_histograms (faked
> >in, even if you haven't generated a histogram).> which is surprising,
> >because although I don't know if those rows are in user_tab_histograms, I
> do
> >know that just analyzing didn't help this query. There is a possibility
> that
> >our testing was done against O805. I think it was against O815, but I
> >wouldn't bet a paycheck on it.
> >
> >When analyze estimate statistics didn't work, we did a full analyze (which
> >took almost a day). And that didn't work either.
> >

I recall a nice script on Oracle metalink which predicted (reasonably) the usefulness of a histogram by

• adding the histogram
• checking the distribution to see if it skewed
• removing the histogram if not.

The author I think was "... Lin"

Cheers
Connor
--

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Wed Nov 24 1999 - 06:59:28 CST