Cost versus Rule based Optimizer

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 Received on Wed Sep 22 1999 - 03:32:49 CDT