Re: EXPLAIN PLAN : better with RULE!

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<1027008539.1355.1.nnrp-01.9e984b29_at_news.demon.co.uk>...
> Oh dear -
> I have to contradict you twice in one day.
>
> 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.

>

You are quite lucky. I didn't have such luck. Try to get optimal plan with CBO in any of cases listed below. Suppose you have more than 10 tables in each case.

1. You have couple of connect by inline views in your query.
2. You have several outer joins.
3. You have couple of huge tables, each of them has about 15 indexes.
4. You have huge table which is updated all the time by different users. As a result, full table scan on it is impossible, because it spends more time reading rollback/undo than table itself. This case is the trickiest. Usually you are going for FTS when reading >= 20% of the table. But in this case you sometimes better off using index to access 40% of the table.

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'm just saying that sometimes it's easier to force good plan using RBO tricks. In most complex cases I used CBO with hints, actually forcing plan I want. It's especially useful when you can efficiently use hash joins.

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.

>
> The CBO usually finds optimal paths in complex
> joins despite the best efforts of designers and
> dbas to confuse the issue with unsuitable indexing
> strategies, inappropriate statistics, and misleading
> resource definitions.

Just an example: everything started working about 100% longer when sample size when analyzing tables was changed from 10% to 20%. It was 8.1.6, Oracle Applications.

>
> --
> 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.0207180744.cea7b92_at_posting.google.com>...
> >
> >You can estimate. In reality, it depends also on number of indexes, table
> >segments and lots of other things. However, you can safely bet that if you
> have
> >more than 7 tables in your query, CBO is not gonna give you optimal plan,
> ever.
> >
Received on Thu Jul 18 2002 - 17:28:32 CDT