Re: RBO to CBO

"DA Morgan" <damorgan_at_x.washington.edu> wrote in message news:41d44380$1_1_at_127.0.0.1...
> Niall Litchfield wrote:
>
>> "DA Morgan" <damorgan_at_x.washington.edu> wrote in message
>> news:41d3a2b3$1_2_at_127.0.0.1...
>>
>>>Oradba Linux wrote:
>>>
>>>
>>>>Any major/minor issues to watch out for during the conversion especially
>>>>with regards to sql written specifically to RBO
>>>
>>>Strip out hints and retune each statement as required.
>>
>>
>> I wouldn't regard sql with hints as 'written especially for the CBO',
>> since with the exception of the rule hint they would run under the CBO.
>
> Earlier this year I did a one-month gig with a local telecom. Moved
> them from 8i RULE to 10g CBO. You'd likely be amazed how much of
> their SQL was hinted ... heavily hinted: And the CBO didn't like any
> of it.
> --

Hi Daniel

I think the point Niall is making is that your "heavily hinted" SQL that was running in the 8i RULE environment was *actually* running with the CBO all along as hints (with rare exceptions such as the RULE hint itself) run under the CBO.

So your migration of hinted SQL wasn't from RULE to CBO at all but from CBO to CBO.

So when you say the "CBO didn't like any of it", what you're actually saying is that the CBO worked fine in 8i but the CBO didn't work quite so well with 10g. This has nothing to do with RULE based conversion but in changes in the environment and the *CBO* changes between 8i to 10g.

There are a lot of ifs and buts when converting from 8i to 10g but considering the SQL was hinted, it would be interesting to determine exactly why the 10g CBO "didn't like it" and how the hints were subsequently ignored or applied in an inappropriate manner based on other changes to the execution plans. Do you have any specific examples on what changed to cause these issues and what were "good" execution plans in 8i but "bad" ones in 10g with identically hinted SQL ?

Such indeed is the danger of hints ...

Cheers

Richard Received on Fri Dec 31 2004 - 22:42:24 CST