Re: Cost versus Rule based Optimizer

Aamer

We have recently grappled with a very similar problem. We run 7.2.3 on Dec Unix and recently whilst running in Cost Based Optimizer mode (CBO) we had occasion to delete about 70% or the contents of a major table. We then overnight re-analized the statistics and the overall Buffer cache ratio instead of being about 90% fell to an alarming 12%.

The explanation we arrived at was that the CBO was now accessing the 'least cost' or fastest access route for one particular query to be via a Full Table Scan rather than by the first column of an combination column unique index. This query was performing fairly well. However by access the whole of the, albeit trimed, table, and placing all the extra data in buffer cache; all the other nicely kept data buffers were being blown away every time the 'nasty' query was run.

We deleted statistics for just this table and the buffer cache ratio climbed to an improved 92%. The affect on the 'nasty' query itself was to small to detect easily.

One other thing that might be worth bearing in mind is that we are working with a largely packaged application and yet we believe the best way to make system wide use of CBO is to write or adjust the SQL in the light of CBO. Being a package we are unable to change most of it. Most legacy systems and even modern application programmers will think appropriately for Rule Based Optimizing (RBO).

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.
Aamer <aamer_Janjua_at_hotmail.com> wrote in message
news: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

>

>

>