Re: 9i optimizer problems

Me wrote:

> We've been using cost based optimization since Oracle 7. Our upgrade from
> 7.3.4 to 8.1.7 was fairly painless but the upgrade to 9.2 is proving
> troublesome. The main problem seems to be that the 9i optimizer is making
> decisions that can result in order of magnitude response time increases
> (not to mention temp space usage) in some queries.
>
> The queries that I'm dealing with are quite simple and have no hints.
> I've already reviewed the "advisor" views and thrown more memory resources
> at the
> database. It seems reasonable to me that the 9i optimizer should work at
> least as well as 8i.
>
> Has anyone encountered similar problems with Oracle 9i?
>
> And yes, the tables are analyzed.

First, it's impossible to provide any meaningful advice without at least one concrete example. Second, stored outlines were invented in 8i for a reason. Third, the optimizer in 9i is a whole lot *smarter* than it was in 8i. Fourth, have you got things like system statistics calculated, dynamic sampling enabled, appropriate histograms, optimizer_mode set correctly, optimizer_features_enabled set properly, and about 15 other parameters that need to be watched.

Throwing memory at the problem is in any event a very poor way to approach such an issue. Particularly if you've been throwing it at PGA Aggregate Target.

Without explain plans, however, it's not going to be possible to diagnose further.

Regards
HJR

-- 
--------------------------------------------
See my brand new website, soon to be full of 
new articles: www.dizwell.com.
Nothing much there yet, but give it time!!
--------------------------------------------