Re: Full Oracle Block

From: Noons <wizofoz2k_at_yahoo.com.au.nospam>
Date: Sat, 17 Jan 2004 03:48:07 +1100
Message-ID: <40081801$0$16593$afc38c87@news.optusnet.com.au>

"Tyler Smith Watu" <watuni2000_at_yahoo.co.nz> wrote in message news:2f3438a4.0401160735.4668c267_at_posting.google.com...

>

> 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

It varies quite a lot with the Oracle version. With 8.0.x, I never got anywhere decent without full stats. With 8.1.7.4, I never bothered with much more than estimate. With one very weird exception that I still classify as a bug. To do with partitioned tables and local indexes.

Burleson maintains that one should analyse histograms on EVERY index/table/column that is a FK, but he doesn't say if it should be estimate or full. Others claim this is not needed anymore with 9i.

I think at the end of the day one has to use whatever works in whatever version of Oracle one runs. This may finally be fixed in 10g, but until then the CBO is notoriously sensitive to the version you're running. Much less so IME with 9ir2, where it is (almost) reliable.

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam

Received on Fri Jan 16 2004 - 10:48:07 CST