Re: EXPLAIN PLAN : better with RULE!

Hi Alex,

Are you sure ???

I have never come across such issues. It's my understanding that the RBO has not been changed in any of the 8, 8i and 9i releases.

Could you post an example of a RBO ignoring the +0 conditions and still use an index or a valid unique index being ignored in favour of some other option.

It all sounds most bizarre to me.

Richard
"Alex Filonov" <afilonov_at_yahoo.com> wrote in message news:336da121.0207170923.4469328d_at_posting.google.com...
> "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 Thu Jul 18 2002 - 22:56:21 CDT