Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Rule vs. Cost
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.
Received on Tue Nov 09 1999 - 09:38:04 CST