Re: Rule vs. Cost

Hi,
 I strongly recommend sticking to the vendors suggestions.  There are legacy (?) applications (or applications with some history)  that have been tuned specifically for rules-based optimizer.  Switching to rules may (or may not) hurt performance significantly as  CBO makes different decisions.

 For older applications it is really difficult to put hints on all queries that might be broken by CBO.

 As a matter of fact there are queries that cause ORA-600 in Oracle 7/CBO. Karsten

In article <809f4s$17p$1_at_nnrp1.deja.com>,   markp7832_at_my-deja.com wrote:
> In article <38281703.CFD77797_at_virgajesse.be>,
> Nordine Vandezande <nordine.vandezande_at_virgajesse.be> wrote:
> > Last week i upgraded a database (with cost-based optimization) from
> 7.2.2 to
> > 7.3.4.
> > A specific query took more than a minute to run in the new database,
> while it
> > took 1 second in the previous version.
> > I investigated the problem and noticed that it was a long time ago
> that the
> > tables where analyzed, so i analyzed it again.
> > Now, my query took more then 3 minutes!!!
> > So i deleted the statistics, which as a result that my query took 1
> second (and
> > still using cost-based optimization)!!!
> > It seems to me that there is definitly something wrong with cost-
based
> > optimization in Oracle V7.
> >
> The plans chosen by the rule based optimizer are not always the best
> way to run the query either. You still need to tune. With CBO you
> have a great deal of flexibility via hints. In a perfect system every
> query would be hinted to run exactly as you want it run, and only ad-
> hoc submitted via end-user tools would run under pure CBO. But who
has
> that much time, control, and application knowledge?
>
> One of our production instances exists to run a verdor supplied
> application that was designed per the vendor to run rule based. It had
> been installed with statistics so it was running cost. We lasted less
> than 15 minutes under rule before the end-user complained. We have
ran
> it cost based every since under 7.1.5 and now 7.3.4 with acceptable
> performance. The problem is we have no access to the SQL so it is all
> or nothing. I would recommend trying to run the vendor application
> (mentioned upstream in thread) as the vendor says and adding indexes
> where monitoring shows necessary, and resorting to using statistics on
> selected tables only if you have no other choice. If you do not have
> access to the SQL you are limited in what you can do.
>
> --
> 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/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Nov 09 1999 - 10:22:18 CST