Re: Poor Performance after 9i migration on Windows

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Sat, 10 Jul 2004 06:21:00 GMT
Message-ID: <pan.2004.07.10.06.20.59.654079@sbcglobal.net>

On Fri, 09 Jul 2004 23:40:39 +0200, Christian Antognini wrote:

> Mhmm... when I spoke from instance parameters, as I wrote in my first post,
> I mean the optimizer parameters!

You mean optimizer_index_(caching|cost_adj}? I would advise against setting them blindly and trying to turn CBO into RBO. Philosophy of the RBO was very simple: if there is index, use it. By setting optimizer_index_caching to 95 and optimizer_index_cost_adj to 5, one effectively turns the CBO into RBO, thus effectively eliminating all the goodies like hash joins. While it does work on the applications that have been moved from RBO- based 8i to CBO and 9i, it also results in highly skewed applications which are not easy to move to another version or platform.

>> The root cause of the problem is usually fouled up SQL.

>
> As written by Kalle they faced out problems after the migration, i.e. I
> deduce that before the migration the application was performing well. Now,
> if the application has not been changed, advising to start with an
> application tuning is absurd!

Well, not really. My understanding is that he was also switching from version 8i, which doesn't have system statistics, to version 9i. My suggestion to start looking into application makes sense because without it, one cannot say what is slow. One can have two equally disasterous occurences:
a) Full table scan, instead of a range scan and b) Nested loop/index scan where FTS and hash join would

be much faster.

One usually cannot tell them apart without looking into the application. Granted, a) is much more frequent, but b) is equally disasterous, and trust me, b) does happen.

-- 
Well-behaved women seldom make history

Received on Sat Jul 10 2004 - 01:21:00 CDT