f



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
0
emailspam (3)
5/2/2005 1:35:41 PM
comp.databases.oracle.server 22978 articles. 1 followers. Post Follow

5 Replies
3865 Views

Similar Articles

[PageSpeed] 55

"Sven Bombach" <emailspam@fahr-zur-hoelle.org> wrote in message
news:3e68bd40.0505020535.64aff21c@posting.google.com...
> 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

Instead of setting init.ora parameters, especially undocumented ones, you
need to find out why the performance has changed.  Did you analyze your
tables?  Increasing the cpu count and having it take longer sounds like some
sort of parallizing is taking process is taking place.(and you don't want it
to.  parellel operations don't automatically make it faster.)
Jim


0
5/2/2005 1:58:16 PM
Jim Kennedy wrote:
....
> Instead of setting init.ora parameters, especially undocumented ones,
you
> need to find out why the performance has changed.
....

An 10046 trace should help identifying where the bottleneck is...

0
vlado1 (3)
5/2/2005 2:15:57 PM
check to see if shared servers are now being used:

select server, count(1)
from sys.v_$session
group by server;

SQL> select server, count(1)
  2  from sys.v_$session
  3  group by server;

SERVER      COUNT(1)
--------- ----------
DEDICATED         28

SQL> show parameter server

Are the packages pinned (kept) in the shared pool?
The effect of multiple CPUs could exacerbate latch contention in the
shared pool.

-bdbafh

0
bdbafh (710)
5/2/2005 2:39:28 PM
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@x.washington.edu
(replace 'x' with 'u' to respond)
0
DA
5/2/2005 3:06:21 PM
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.
0
5/3/2005 12:02:54 AM
Reply: