Re: EXPLAIN PLAN : better with RULE!

Isn't that because obviously unsuitable ones are chucked fairly sharpish... ?

The 10053 output is unreadable by mere mortals like me especially where it sums up the major choices

but 10046,tkprof,explain plan is mainly what I use ... and recently where CBO is concerned ...

you're going to have to show the details to prove to me that CBO is taking a bad route...

e.g. one 817 site I was at had HASH_AREA_SIZE set to 1MB out of the box (massive DM env) and wondered why the optimizer wasn't picking HJ as an option for equijoins etc . Once it was set high enough the optimizer went HJ all the way - 15 hours -> 9 mins.

"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
>
>
> 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.
>
> --
> 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 - 11:49:00 CDT