Re: RBO to CBO

"DA Morgan" <damorgan_at_x.washington.edu> wrote in message news:41d63128$1_4_at_127.0.0.1...
>> 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.

BTW Richard was as usual correct, this was exactly my point.

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

There are at least a couple of issues that I can think of that may be important.

The first would be what the performance was like *with the data that was migrated* on 8i without the hints. One of the issues with hints is that having put them in (typically early in a applications life) they tend never to get removed even if in an ideal world they should be. A little judicious renaming of indexes can work wonders here. (Until 10 - maybe 9.2 - when you can hint the table and column names :( ). Note I'm not saying that the performance *would* have been better, just that it may have been.

The second is that the statistics likely were different. It is almost impossible to collect the same stats in 10 as you did in 8i - I guess analyze might do it. This is BTW generally a Good Thing (tm), but does, especially in combination with the differences that *do* exist in the optimizer, make comparing the CBO between versions 2 or 3 releases apart somewhat problematic.

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com



>

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