Re: Cost versus Rule based Optimizer

Never mind,

I read the postings a little more thoroughly and found that dbms_utility.anaylyze_database has the delete method for deleting the stats. If only I'd read......

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 Wed Sep 22 1999 - 23:26:45 CEST