Re: EXPLAIN PLAN : better with RULE!

Optimiser time.

The final execution cost (in terms of
CPU, logical I/O and physical I/O, that
is) was the same irrespective of optimiser. It just took the RBO about 800 times
as long to figure out which path to use.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
 UK  Sept
 Australia August
 Malaysia September
 USA x 2  November

http://www.jlcomp.demon.co.uk/seminar.html

Alex Filonov wrote in message
<336da121.0207181428.201b4bb6_at_posting.google.com>...


>"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message

news:<1027008539.1355.1.nnrp-01.9e984b29_at_news.demon.co.uk>...



>> Having done a bit of stress-testing and generally

>> having fun with the optimisers, I can tell you that

>> one test involving 255 tables gave me an optimisation

>> time of:

>>         Rule           45 minutes 22 seconds

>>         all_rows      3.04 seconds

>>         first_rows    3.04 seconds

>

>Optimization time or execution time? I'd like to know sizes of those 255

tables.


>In my case complex query runs minutes.

>

>

>A little bit more about CBO and RBO. I don't say that RBO is better in

finding


>optimal execution plan. It's not an optimizer at all and requires manual

>tuning.


    I'd be inclined to say that it was an optimizer - but you have
    to design a rational set of indexes on the database for the
    optimizer to select from.



>

>I have several queries which ran forever with all_rows/choose/first_rows.

After


>some tuning they ran whithin 5 minutes. Don't know if it's optimal, but

it's


>quite practical. I don't want to post them here, they are too big. If

interested,


>I can send them to you.

>


    Thanks for the offer, but it really takes sitting in front of the
    database checking the stats to explain why the optimizer
    is being decieved into doing things badly.