On Mon, Mar 17, 2003 at 09:53:52PM +0000, Tim Sweetman wrote:
> Chris Benson wrote:
>
> >Bzzzt! in 8i and 9i, Oracle defaults to the Cost Based Optimizer.
> >Unfortunately it needs a (manual)
> >
> > SQL> exec dbms_utilities.gather_schema_statistics('APPS');
> >
> >(or something like that) to work reasonably well. Viz last week a
> >4-table query on out of date statistics -> 15min to use 500MB of temp
> >space then fail. With current statistics: -> correct results in 0.08
> >seconds :-}
> >
> FWIW, MySQL and Informix Dynamic Server both need manual stuff done
> before they gather statistics (though I think the optimisers on both
> will often do a decent job without the stats)...
We're spending about 6 hours every Sunday mornings gathering stats
for one application :-( It seems that we've also been wasting 2hours
a night gathering partial (estimated) stats ... which overwrite the
complete stats. So we're better off with week-old actual values than 1
day-old estimates!
Isn't Oracle wonderful: "jobs for life"(tm)
--
Chris Benson
