Re: ORA 8 optimizer really bad!

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Fri, 28 Jan 2000 19:35:40 +0800
Message-ID: <38917F0C.7B22@yahoo.com>

markp7832_at_my-deja.com wrote:
>
> 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.

I used to agree with such a hypothesis until recently I got the following results on a 10,000,000 table

estimate		colx = 6,000 distinct
sample 25 %             colx = 100,000 distinct
sample 50 %             colx = 80,000 distinct
compute                 colx = 1,700,000 distinct

Needless to say, the estimate versions of the stats were causing some grief.

(Sequent 8.0.5.1)

Cheers
--

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 Fri Jan 28 2000 - 05:35:40 CST