Re: How to calibrate the CBO

Just to round up the question, the procedure is running about 10x faster after my last changes. Uff! At last!

By looking at the tkprof output, only one issue remains, which still demands my attention. I found the following:

"The following statements encountered a error during parse:

select /*+ cursor_sharing_exact dynamic_sampling(0) no_monitoring */ count(*),sum(....." etc.

this (very long) statement with lots of count,sum,substrb(dump(min.. etc. is obviously some sort of statistics gathering statement. After the statement itself, I find:

"Error encountered: ORA-32550"

But as far as I could recognize during 5 minutes (I had no more time today), the procedure ran through to the end without any problem otherwise. I will have to chase this ORA-32500, no clue what that is. Don't know why the hint "cursor_sharing_exact" appears, it was set to "similar" for the session. Same discrepance for "dynamic_sampling(0)", since it was actually set to 3 systemwide.

I will check the "_unnest_subquery = false" and the like in 9i plans asap.

Thanks very much for your recommendations, you gave me a lot of challenging homework that I will engage with as time permits. I definitely need to become more proficient with all of this execution plan stuff. We have another far more complex and larger DB to migrate to 9i soon and I am better prepared for such trouble now.

>> 8) One more thought of mine: If nothing helps, setting
>> compatible='8.1.7' could help me out until the situation clarifies.
>>
>
>I think you probably mean optimizer_features_enable.

Right, thanks.

Regards
Rick Denoire Received on Mon Feb 09 2004 - 16:58:57 CST