Re: Cost versus Rule based Optimizer

From: <markp7832_at_my-deja.com>
Date: Thu, 23 Sep 1999 14:35:30 GMT
Message-ID: <7sddri$7qm$1@nnrp1.deja.com>

Graham, I have reason to doubt the information that the Oracle rdb rep gave you. Oracle had a CBO in version 7 which came out before Oracle purchased rdb from DEC if my memory is correct. Also I believe that the primary developer had experience with Ingres's optimizer.

Also we have a purchased package that the vendor said should run rule based. It was in an instance that had statistics on every table so we deleted them during a manufacturing line down shift and less than 15 minutes into the next shift we were getting complaints so we regened the statistics and have been running that way ever since. Since we have no access to the SQL statements I was pretty sure we were going to be hurting either way, but CBO works even though the vendor wrote for RBO. The problem is really that most people, including myself, need to learn to write better SQL. Every application SQL statement needs to be tuned. Using the CBO does not change this.

---

In article <7sbec9$4316_at_news.abbott.com>,   "Graham C Thornton" <graham.thornton_at_ln.ssw.abbott.com> wrote:
> 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
> >
> >

>
> Had the same thing happen to me a while back, and what really annoyed
me
> was when the guy at Oracle support plain refused to believe me that
the
> 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
> for the analyze schema command to bulk-delete the statistics.
>
> I talked to an Rdb expert a few weeks ago and raised this point. She
told
> me that Oracle's CBO had been copied almost byte-for-byte from Rdb
when
> Oracle took the product from DEC. Apparently Rdb uses a relatively
simple
> algorithm for the CBO, with a method being discarded if certain
criteria
> are met, regardless if in fact it ends up being the best of a bad
bunch.

>

> 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
> want statistics without trashing performance, set the OPTIMIZER MODE
to RULE
> in the INIT.ORA file.

>

> Graham

>

> --
> Empowerment - delegating the responsibility but not the authority.
> -------------------------------

>
> Opinions expressed do not necessarily reflect those of Abbott
Laboratories.
>
>

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Sep 23 1999 - 09:35:30 CDT