Re: RBO to CBO

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Mon, 03 Jan 2005 16:36:05 -0800
Message-ID: <41d9e3ca$1_2@127.0.0.1>

Niall Litchfield wrote:

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

I agree with everything you've said. I've found that just a simple query such as:

SELECT DISTINCT srvr_id
FROM servers
WHERE srvr_id NOT IN (

    SELECT srvr_id
    FROM servers
    MINUS
    SELECT srvr_id
    FROM serv_inst);

Where servers has 100 rows and serv_inst has 999 rows and the intersection is 11 srvr_ids produce the exact same Explain Plan in 9.2.0.4 and 10.1.0.3 but one costs it at 3 and the other at 305.

And in case anyone thinks I've lost my mind, well I have, but the above query is not evidence of that fact. Rather it was created for a class on Explain Plan I teach.

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

Received on Mon Jan 03 2005 - 18:36:05 CST