Re: Why PARALLEL_MAX_SERVERS *degrades* performance?

NNOOR wrote in message <7hvvaf$p69_at_chronicle.concentric.net>...
>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.

What is parallel min servers set to? This specifies the default number of parallel query slave servers that Oracle will fire up. The max servers specifies the number to which this can be increased to.

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

This can also be due to networking (using small TCP packets for the fetch instead of large ones) or the way the Delphi program has been coded (the TTable and TQuery classes are often misused/abused in Delphi). I commented on this in this ng just a few days ago when VB/Delphi and ODBC performance problems were raised.

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

Er... I can not entirely agree with this. I'm sure your observation is correct, but IMHO it is a bit incomplete and the wrong conclusions are being made.

Just a brief description of what Oracle does with parrallel server/query processes (PQs for short). When Oracle decide in it's infinite wisdom to screw up yet another explain plan (just kidding!), it may select to do a full table scan. In order to process the full table as quickly as possible, Oracle uses PQ processes to assist in getting the data off the disk and processing it - the number which is used depends (in order) on :

1) parallel hint (if used) in the SELECT statement
2) the PARALLEL clause for the table to be scanned
3) number of PQs available/idle.

Oracle then looks at the stats for the table to decide how much of the table each PQ must scan (it actually adds the clause ROWID BETWEEN :1 and :2 to your SELECT statement).

OK, the end result is that Oracle fires up PQs (each PQ process gets an entry in V$SESSION while they are being used) to scan segments of the table. Data that is returned by these PQs and "serialised" back to the main session (i.e. your use session that executed the original SQL).

>1. What is best way to get advantage of 2nd CPU for Oracle in
> my setup?

Parallel query.

>2. If parallel_max_server is set to 0, does Oracle still benefit
> from NT's thread scheduling on two different CPU's?

Not the right answer. PQ has nothing to do with thread scheduling. PQ processes (threads on NT and processes on UNIX) are simply a way for Oracle to read and process large amounts of data in parallel.

Oracle itself (talking about the database engine) runs different processes/threads already - have a look at the V$PROCESS table for a list of threads (processes on UNIX) that Oracle creates by default.

There's little that an application can do on NT (even UNIX) ito process scheduling. That is the function of the operating system. The application is limit to defining the priority of the threads and of course synchronising the thread processes. That said, on some UNIX systems you do have thiugh a kernel call that allows you to bind a process to a specific CPU. With this you can for example have the db engine running on CPU0 and the log reader/writer on CPU1. But even so, this is very primitive from of processing scheduling - this is the job of the kernel and should be left to the kernel IMO.

>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)?

Good question. Without knowing the details... <mental shrug> What I can do though is tell you where I had performance problems with PQs.

1. The table to be scanned has wrong/old or no stats which results in Oracle giving the 1st PQ all the data (valid rowids) to scan, while the other PQs are scanning "empty rows". This of course has absolutely no performance gains at all and may even cause a drop in performance compared to the SQL not using PQ at all.
2. The PQ CPU load is too large to be handled by the CPU(s) - if the CPU load is already at 80% and you run a query in parallel, the load created by the PQ processes may push the CPU load way over a 100% and create severe bottlenecks. OTOH if a single query has been used this query will have a much smaller load and may even be faster that the parallel query because it does not create any performance bottlenecks on the system. A case of simply trying to do too much with too little CPU power which results in slower performance.
3. Bugs in PQ - OK to be fair this was not a PQ bug, but a bug in the UNIX kernel. An async call by a PQ got "lost" by the kernel and the PQ process waited forever for it to be completed. You could have run into a similar kind of problem with the specific versions of Oracle, NT and the hardware you are using (unlikely though IMO).

Lastly, it just could be that there is something else that is a problem and you just happen to fix inadvertently, by disabling PQ. For example a memory bottleneck - by disabling PQ you have reduced the memory requirements and now the system behaves acceptably. Or something like that.

Bottomline is that I think you can not describe the performance problems you are experiencing to the PQ processes directly, without doing some more investigation.

regards,
Billy Received on Fri May 21 1999 - 05:18:07 CDT