Re: EXPLAIN PLAN : better with RULE!

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 - 11:15:22 CDT