Re: Detecting whether the RBO or CBO is being used

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)

--
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