Re: EXPLAIN PLAN : better with RULE!

Hi Pete,

Sniff, sniff (blowing of nose), sniff sniff ...

Your picking on me you big bully. Three contradictions over one beauty sleep :)

OK, if there's a bug regarding all this then maybe yes ...

Not where I was coming from though.

Still love ya

Richard
"Pete Sharman" <peter.sharman_at_oracle.com> wrote in message news:ah9duq01mgl_at_drn.newsguy.com...
> In article <K_LZ8.38265$Hj3.115554_at_newsfeeds.bigpond.com>, "Richard
says...
> >
> >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.
>
> Far be it for me to contradict Richard, but there is one scenario where I
> believe it may still change in versions after 7. If you can document a
bug
> where the use of the RBO returns incorrect results, I believe that was
still
> getting fixed. Now the likelihood of finding such a bug now must be
> infinitesimally small, I would imagine ...
>
> Pete
> >
> >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.
> >
> >
>
> HTH. Additions and corrections welcome.
>
> Pete
>
> SELECT standard_disclaimer, witty_remark FROM company_requirements;
>
Received on Fri Jul 19 2002 - 19:20:14 CDT