> >> >>
<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
Received on Sat Jun 22 2002 - 11:51:42 CDT
