Re: Cost versus Rule based Optimizer

[Quoted] Aamer wrote in message <37E89431.5BA7DBBB_at_hotmail.com>...
>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
>
>

[Quoted] Had the same thing happen to me a while back, and what really annoyed me [Quoted] was when the guy at Oracle support plain refused to believe me that the [Quoted] performance had gone to pieces just by analyzing the schema. Even more time was lost due to him not knowing that you can use DELETE as the method [Quoted] for the analyze schema command to bulk-delete the statistics.

[Quoted] I talked to an Rdb expert a few weeks ago and raised this point. She told [Quoted] me that Oracle's CBO had been copied almost byte-for-byte from Rdb when [Quoted] Oracle took the product from DEC. Apparently Rdb uses a relatively simple [Quoted] algorithm for the CBO, with a method being discarded if certain criteria [Quoted] are met, regardless if in fact it ends up being the best of a bad bunch.

[Quoted] Also, the CBO apparently cannot take into account latency caused by distributed databases, and gateways in particular really screw up the optimizer. Lastly many apps are specifically written for RBO, so if you [Quoted] want statistics without trashing performance, set the OPTIMIZER MODE to RULE [Quoted] in the INIT.ORA file.

Graham

--
[Quoted] Empowerment - delegating the responsibility but not the authority.
-------------------------------

[Quoted] Opinions expressed do not necessarily reflect those of Abbott Laboratories.