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
Received on Thu Sep 23 1999 - 04:31:59 CDT