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

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)

From: John Reidy <johnr.nospam_at_spamno.reidysystems.com>
Date: Tue, 03 May 2005 00:02:54 -0000
Message-ID: <117dftekemcj2f5@news.supernews.com>

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

My guess would be that since the upgrade was dont the db hasn't been analysed and hasn't a clue about how to structure the queries, have you done that, and example:

begin
dbms_stats.gather_schema_stats(ownname=>\'AP2SYD\',cascade=>TRUE); end;
/

If true this would explain the performance problems. Received on Mon May 02 2005 - 19:02:54 CDT