Re: Cost-Based or Rule-Based?

MMK Productions wrote:

> What version of Oracle were you using when you found these results?

The versions are 7.3.2, 7.3.3 and 8.0.4, on NCR UNIX.

> In every DB and in every application CBO was always better?

In most of the cases CBO is better. Sometimes you'll get the same plans (and the same speed), sometimes the speed will just a little bit better. But what I saw, 90% of slow expressions are faster - 10-20 min instead of 2-4 hours.

> When we searched Oracle's support site and the net we found dozens of user
> reports and even some actual Oracle logs showing V7.2 and 7.3's CBO had made
> the wrong index pick and the performance was WORSE than RBO.

Right, I saw this situation twice, both cases for the same structure of the SQL expression -
when you have something like:

select x,y,z...
from a,b,c...
where ...

    b.some_date= (select max(some_date) from b where ....) ;
For sub-expression Oracle had selected an index with better selectivity, and plan had smaller cost, but the final execution time was awful.

Well, you need to monitor your application, at list at the beginning, and try to find these cases.

> >mode for slow SQL expressions increased speed in 50-2000 times.
> >I had one expression that in RBO mode ran for 3h 5 min, in CBO - 5 sec.

Yes, it was a select from 3 joined tables, two were big - 1.5 -3 M rows, and this expression returned only one record. CBO had return this row in 5 sec.

Another expression, that I've tested a couples of days ago, took 26 min in CBO, and 48 hours in RBO. Again, it was select of 8 (or 10, I can't recall) joined tables.

Sincerely.
Peter. Received on Mon Jan 25 1999 - 20:10:08 CST