Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Detecting whether the RBO or CBO is being used

Re: Detecting whether the RBO or CBO is being used

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 22 Jun 2002 11:16:03 -0700
Message-ID: <af2et30159q@drn.newsguy.com>


In article <9920a15d.0206220851.378afbb3_at_posting.google.com>, alhepworth_at_hotmail.com says...
>
>> >> >>
><Snip>
>> >> >>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)
>
>I altered the 2 indexes to DEGREE=1 but am still seeing costs in the
>explain plan. Any ideas?
>If the degree was set to something other than 1 for any index, even
>though it would not be considered for the query, could this trip the
>CBO?
>TIA
>Alan

any index on the set of tables used in the query -- even if not used -- yes, it would. Just alter all indexes back to 1 that are not 1 since you don't want cbo anyway for whatever reason.

Also, there aren't any views here are there? if so, look for hidden hints in the view, that'll do it.

--
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 
Received on Sat Jun 22 2002 - 13:16:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US