Why PARALLEL_MAX_SERVERS *degrades* performance?

setup: WinNT 4 S/P5, MS I/E 5, Oracle 8.0.5, Dual PII400,

       256MB RAM, One 7200RPM IDE HD.

Installed Oracle8 server with all default options, i.e. did not make any changes to the initorcl.ora. Oracle gave a value of 5 to the parallel_max_servers parameter in the init file.

Machine is used as a development machine and has Delphi 3 installed on it as well.

I have noticed that certain queries run *very* slow. Especially the ones where the application is fetching one record at a time from the result of the Select statement (using Record.Next command). We have a Pentium Pro 200 as Oracle server at the work and my work station is a PII350 which connects to the server over the LAN. The response time that I get at work is much better to what I got at home. So it got me curious. I did some playing around with the parallel_max_server parameter and this is what I found.

• The default value of 5 or Oracle recommended CPUx4 (i.e. 8 in my case) results in slow performance.
• The value of 1 or 2 results in just as good or slightly better (you are always in doubt if the difference is actually there) than when the value is set to 0.

My questions:
1. What is best way to get advantage of 2nd CPU for Oracle in

   my setup?
2. If parallel_max_server is set to 0, does Oracle still benefit

   from NT's thread scheduling on two different CPU's? 3. Why did I experience a phenominal drop in speed (It became so

   slow that once I actually restarted the computer thinking that    it is hung)?

Thanks and Regards,
Nasir Noor (nnoor_at_cris.com) Received on Wed May 19 1999 - 22:25:03 CDT