Re: EXPLAIN PLAN : better with RULE!

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:<AK5Z8.36497$Hj3.110485_at_newsfeeds.bigpond.com>...
> Hi Alex,
>
> May I ask a quick question.
>
> I'm curious to know what do you mean when you say the RBO is much worse in
> 8i ?
>
> Cheers
>
> Richard

Hi Richard,

I had bad experience after upgrade from 8.0.6 to 8.1.6. Several complex queries which ran fine on 7.3 and 8.0.6 with RBO started running forever on 8.1.6. I found several problems.

1. Old tricks like ||'' and +0 in conditions don't help in some cases even if you set session parameter optimizer_mode = rule and you have no hints in a statement.
2. Old RBO rule that unique indexes are choosen first doesn't apply to 8i.
3. In complex queries, old RBO rule that tables are taken in reverse order of the from clause, doesn't apply. This happens only sometimes, but it's annoying. I haven't checked it on 8.1.7 yet.

The most annoying part is that you can't be sure that plan you had on 8.0 is the same on 8.1 even with optimizer_mode = rule.

> "Alex Filonov" <afilonov_at_yahoo.com> wrote in message
> news:336da121.0207161208.13ae77b3_at_posting.google.com...
> > "Epicentre Team B Annecy" <carmanet_at_epicentre.fr> wrote in message
> news:<ah0k2t$4j0$1_at_wanadoo.fr>...
> >
> > What's value of parameter optimizer_max_permutations? Try to increase it
> to
> > something above 50000.
> >
> > My humble experience tells me that any complex query needs manual tuning.
> > Even rule based optimizer is not very good here. With CBO you are
> severerly
> > restricted by above mentioned parameter. You can't make it really big, and
> real
> > number of permutations needed for efficient CBO plan is growing
> proportional to
> > factorial of tables involved. So you know were to shove all advices about
> > analyzing, parameter setting and all, right?
> > There are 2 real problems here.
> > 1. Rule based optimizer on 8.1 is much worse than it was in previous
> versions.
> > 2. Manual tuning is time consuming and requires real understanding of
> tuning.
> > But this is the only way to go.
> > I'm happy for you actually that RULE hint works OK. Just leave it this way
> and
> > don't worry. And if it's not good enough, than you need to use combination
> of
> > hints to force the plan you like.
Received on Wed Jul 17 2002 - 12:23:45 CDT