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.

Cost Based optimizer can create much more varieties of execution plan compare to RBO. Some of them could be very bad, but some of them are much better than RBO can ever create. It is because some access methods are not supported in RBO, but only with CBO. If there is risk of bad execution plan with CBO ( query has 15 tables etc.), you better constraint execution plan with hints. Very strong hint is /*+ ordered */. Remember, the same you doing with RBO, it is just impossible to switch off this "hint" :-). Order in "from" clause using RBO is forced in reverse order into execution plan, if it is possible to obtain proper result with this order. And you can have additional benefit with hash joins, which reducing cost of whole query. In the other words:

If

     CBO does not work for you even you have proper statistics info in tables, histograms created and you not using binding variables, but embedding literal values

then

     change order in from clauses in your statements ( previously optimized for RBO) and put /*+ ordered */ hint. You will have better results compare to RBO.

There is always possible to find query, which take half second with RBO and forever with CBO, but there is as well way how to put hints to this query to have the same or better performance again, using CBO. Talking from my own experience :-). Does anybody have another opinion :-) ?

Stan

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