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

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.

We are testing performance behaviour with some business critical PL/SQL procedures that do authorization calculations for our application. Simplified these functions do a lot of inserts, deletes and selects in/from tables (being used as temp tables in lack of in memory temp table support of oracle 9i). We can monitor the progress within the stored procedure by using UTL_FILE functions to write a log file into the file system of the server; we real time monitor the log file using "tail -f".

<PL/SQL do something>

UnbufferedLogFileOutputUsingUTL_FILE();
<PL/SQL do something>

UnbufferedLogFileOutputUsingUTL_FILE();
[...]

When setting "CPU_COUNT = 1" in init.ora the function will typically run up to 10 seconds, when setting "CPU_COUNT = 4" it will take more than 2 minutes (!!!)

We experimented with the following other parameters so far:

db_writer_processes (set to 4 in combination with cpu_count = 4) fast_start_parallel_rollback = high
_optimizer_cost_model = cpu

Although the last parameter is undocumented it has brought the greatest improvement so far: dropping >2 min execution time down to 40 seconds (therefore still much higher than 10 sec with cpu_count = 1)

Is there any way to use the 4 CPUs AND reach execution times below 10 sec (... with CPU_COUNT = 1) in Oracle 9i on Windows Server 2003?

Any help highly appreciated ...

S. Bombach Received on Mon May 02 2005 - 08:35:41 CDT