Re: Cost versus Rule based Optimizer

ANALYZE TABLE table_name DELETE STATISTICS;

will 'delete' the statistics which will be removed from the DBA_TABLE view related to the table specified. The stats are obviously stored deeper in the database (ie. in a X$ table) but I never have had need to look direct. Certainly do not 'delete' them direct

Regards

--
Kevin A Lewis (BOCM PAULS LTD - Animal Feed Manufacturer - Ipswich England)
                        <KevinALewis_at_HotMail.com>

The views expressed herein by the author of this document
are not necessarily those of BOCM PAULS Ltd.
Rob Calfee <trace_at_primenet.com> wrote in message
news:37e946a7.20124624_at_news.primenet.com...


> What table do you delete the analyze information out of?


>

> Rob Calfee

>

> On Wed, 22 Sep 1999 09:32:49 +0100, Aamer <aamer_Janjua_at_hotmail.com>

> wrote:

>

> >Hi All

> >

> >     To give a background to the problem.   We have a live system which

> >has running very slow and required some serious performance tuning.  Its

> >running on  AIX  box with two processors and 1 GB of  RAM.  Its oracle

> >7.3.2. Using  in house developed application.

> >

> >On checking the database it was found that the tables and schemas where

> >not analysed. So in fact it was using rule based optimisation.  We

> >immediately analysed  all schemas .

> >   After analysing    The buffer hit ratio which was 95% dropped to 84%

> >and the sort in disk increased from 0.5% to 15%.

> >

> >Once we deleted the analysing information it improved  the buffer hit

> >ratio to 94% and  sort on disk   to 6% .

> >

> >  We  have not changed any parameter  for buffer size or sort area size.

> >

> >

> >

> >  Its  not understood why when we analysed  the buffer hit ration

> >dropped.

> >

> >  Do we require to increase the size of buffer  as Oracle caches the

> >analysed statistics or  some thing wrong with our application.

> >

> >  Any comments or suggestions will be  highly appreciated.   Please if

> >you could email them to

> >

> >  Aamer_janjua_at_hotmail.com

> >

> >

> >

> > Thanks

> >

> >

> >

>

> Rob Calfee

> DBA

> rcalfee_at_incsystem.com