Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Detecting whether the RBO or CBO is being used
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
Received on Wed Jun 19 2002 - 14:40:17 CDT