Re: Full Oracle Block

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Sat, 17 Jan 2004 12:18:14 GMT
Message-ID: <aK9Ob.16228$Wa.12825@news-server.bigpond.net.au>

"Tyler Smith Watu" <watuni2000_at_yahoo.co.nz> wrote in message news:2f3438a4.0401160735.4668c267_at_posting.google.com...
> Thank you for your response,you did actually bring up another issue we
> have been debating on for some time and if there are any ideas please
> feel free to share
>
> Are there any known "big" advantages between estimating statistics to
> a full compute.At least from Oracle tuning books quite a few of the
> authors on life examples say how a run of full statistics made big
> changes
>
>

Hi M Vincento

First piece of advice, be careful of whom you take advice :)

Many of the tuning books by so-called experts are bloody awful and are full of mistakes, inaccuracies and "advice" that has never been tested. There are of course excellent books but boy, there are some dreadful ones around as well.

In almost all cases, an estimate of statistics is sufficient enough for the CBO to have a good enough picture as if it had computed statistics. And a sample size of 5% max is again generally more than sufficient.

The tables that need a more "accurate" stats are not your large multi Gig tables as many of these "experts" claim but your tiny little tables. A table that has grown from (say) 5 rows to 20 rows can potentially have a dramatic effect on execution plans as the CBO can't now accurately calculate the ramifications of future join combinations.

If you're on 9i, look up some of the dynamic sampling options available with dbms_stats.

But don't take my word (or anyone else's), test it for yourself. Compute statistics and check out the executions plans of your critical transactions. Then estimate on a (say) 5% sample and compare the differences.

Bet you there isn't any ;)

Cheers

Richard Received on Sat Jan 17 2004 - 06:18:14 CST