Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: massive performance drop Oracle 9i on multi processor system (Windows Server 2003, Oracle 9i, 9.2.0.6.0)

Re: massive performance drop Oracle 9i on multi processor system (Windows Server 2003, Oracle 9i, 9.2.0.6.0)

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Mon, 02 May 2005 08:06:21 -0700
Message-ID: <1115046138.639469@yasure>


Sven Bombach wrote:

> The performance of some complex PL/SQL functions has dropped
> dramatically since migrating from Oracle 8i (8.1.7.0.0) on Windows
> Server 2000 to 9i (9.2.0.6.0) on Windows Server 2003. We found out,
> that Oracle 9i performance on a multi processor CPU machine (our
> server has 4 XEON CPUs, Hyperthreading is disabled in BIOS) is highly
> dependent on init.ora parameters; unfortunately our trial and error
> approach to find the proper settings (we searched Oracle Tech Net
> (OTN) and google groups without success) did not help so far.

The problem is pretty well summed up in your statement "trial and error approach."

First strip any hints from your code 8i hints are almost always a problem when moving to 9i. Also return the init parameters to their default values.

Second run an autotrace on both the original 8i and the new 9i and see what is different. If that is not enough information dig deeper by running StatsPack, etc.

Deal with specifics and to do that you need facts. Remember that 4 full table scans are not faster than one.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Mon May 02 2005 - 10:06:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US