Re: ORA 8 optimizer really bad!

In article <38912B6A.4F27035_at_erols.com>,   Jerry Gitomer <jgitomer_at_erols.com> wrote:
>
> I wouldn't mind automatic table analysis for any table with
less than
> a million rows, but I much prefer to choose my own time to anyalze any
> table
> with over a million rows. In part this is because I prefer to do a
full
> analysis and compute the statistics instead of estimating them.
>
> After all, I can always either use the package to analyze when I
> want or write multiple sets of scripts to be run at different times
and
> frequency in order to get my tables analyzed when it is convenient for
> me.
>
> --
> Jerry Gitomer
> Once I learned how to spell DBA, I became one.
>

I have found that estimating with a sample size row count of 50,000 works pretty well for tables up to 40 million rows. I base this on both the results of query performance and comparision of the num_rows column of dba_tables with actual counts and get numbers within a 100 rows for most of my tables.

The estimates run much faster so if time is a consideration you might try it.

analyze table owner.table_name estimate statistics sample 50000 rows;

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Jan 28 2000 - 12:37:31 CST