Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: EXPLAIN PLAN : better with RULE!

Re: EXPLAIN PLAN : better with RULE!

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 19 Jul 2002 00:36:46 +0100
Message-ID: <1027035856.15103.0.nnrp-14.9e984b29@news.demon.co.uk>

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.
Received on Thu Jul 18 2002 - 18:36:46 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US