Re: Upgrade horror stories/victories

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.

My office is up to O815, but most of my clients are still at O734 so I don't have the command memorized yet. Also, as of this week, we have a button on our application that cleans up the database after an import.

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.

We have to do analyze on our database with every import, because one of our 20 million row tables has two duplicate indexes -- one of which has about 400,000 rows per value, and the other has about 10 rows per value and without an analyze the optimizer always picks the wrong one. But with an anazlye the optimizer always gets it right.

Best regards
Robb

Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote in message news:943384596.7909.2.nnrp-08.9e984b29_at_news.demon.co.uk...
>
> This sounds like you did an import which did NOT
> do an ANALYZE of the table.
>
> When a table is analyzed, each column
> has a min and max value visible in user_tab_columns,
> and also a pair of rows in user_tab_histograms (faked
> in, even if you haven't generated a histogram).
>
> Also, you can't generate a one-bucket histogram,
> so I suspect the command you ran was simply:
> analyze table XXX estimate/compute statistics.
>
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> r.golds wrote in message
> <81eh6t$j8$1_at_ssauraab-i-1.production.compuserve.com>...
> >
> >One of my favourite examples of this is
> > select * from MyTable where MyPrinaryKey < 100;
> >
> >In O734 this ran instantly using an index.
> >In O815 this causes a full table scan.
> >
> >We started thinking about it, and decided that O734 was making the
mistake.
> >The optimizer doesn't know if this column is all positive numbers or not.
> >It's possible that all of them are negative, and hence a full table scan
> >would be faster.
> >
> >So we ran some command, (sorry I can't remember the name), and it created
a
> >histogram on the index (one bucket) which obviously held the min and max
> >values for the data, and suddenly O815 started using the index again.
> >
>
>
>
Received on Wed Nov 24 1999 - 13:36:34 CST