Re: Upgrade horror stories/victories

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.
>
Received on Wed Nov 24 1999 - 14:12:39 CST