Re: Detecting whether the RBO or CBO is being used

In article <9920a15d.0206191140.f109e75_at_posting.google.com>, alhepworth_at_hotmail.com says...
>
>Thomas Kyte <tkyte_at_oracle.com> wrote in message
>news:<aepqdd0j1p_at_drn.newsguy.com>...
>> In article <9920a15d.0206190143.1ce0b2f2_at_posting.google.com>,
>> alhepworth_at_hotmail.com says...
>> >
>> >chris_at_databaseguy.com (Chris Leonard) wrote in message
>> >news:<b8cc3cda.0206171427.56230eb6_at_posting.google.com>...
>> >> I assume you mean "choose" and not "rule" ... ??? Just a sanity check.
>> >> Also, do you have the necessary statistics generated?
>> >>
>> >> HTH,
>> >> Chris
>> >> ______________________________
>> >> Chris Leonard
>> >> MCSE, MCDBA, MCT, OCP, CIW
>> >> The Database Guy at PPI
>> >> http://www.propoint.com
>> >> Brainbench MVP for Oracle Admin
>> >> http://www.brainbench.com
>> >>
>> >>
>> >>alhepworth_at_hotmail.com (Alan) wrote in message
>> >>news:<9920a15d.0206170208.4e0f8c07_at_posting.google.com>...
>> >> > Hi
>> >> > I'm investigating some performance problems within our system and am
>> >> > looking at the explain plans for some sql. On one box (v8.1.7) when I
>> >> > run the following:
>> >> >
>> >> > alter session set optimizer_mode = rule
>> >> > select blah from blah where blah
>> >> >
>> >> > I can see that plan and no costs are present.
>> >> >
>> >> > When I run it on our v8.0.5 box, I can see costs in the plan. Does
>> >> > this mean that the optimizer_mode is not set to rule for some reason?
>> >> >
>> >> > Thanks in advance
>> >> > Alan
>> >
>> >
>> >I have _no_ statistics generated on either box at the moment. My query
>> >is why the 8.0.5 explain plans are showing costs when I would expect
>> >the rule based optimiser to be used.
>> >Alan
>>
>>
>>checkout the degree column in user_tables and user_indexes -- if not set to 1,
>>it'll be cbo (even the index will do that -- in 805, not in most other releases)
>>
>>you might have a parallel table or index which forces the CBO in 805 (parallel
>> table = cbo in all releases)
>
>Thanks, I think we're onto something !
>I have 2 indexes which have DEGREE='DEFAULT'. Could this be why CBO is
>being invoked? Why would this field be set to DEFAULT? Is is safe to
>manually update this?
>Cheers
>Alan

I don't have an 805 instance to play with right now -- but -- try the alter index command (not a manual update) to set the degree back to one and see if the cbo stops being used. This quirk only affected 805 (indexes with parallel tripped the cbo)

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp