Re: RBO to CBO

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Fri, 31 Dec 2004 21:16:52 -0800
Message-ID: <41d63128$1_4@127.0.0.1>

Richard Foote wrote:

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

On reflection I think you are correct. Most of the hinting was related to index choice and join method.

I think what I meant by CBO didn't like it is that many of the queries ran far faster and more efficiently when the hints were removed and the CBO was free to query rewrite them to its little heart's contentment.

I have some examples but I am not at liberty to share them due to a non-disclosure agreement. One thing though was changes from nested loops to hash joins which was the case in quite a few of the queries.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Fri Dec 31 2004 - 23:16:52 CST