Re: Oracle 8i + Solaris8 + Enterprise450 + 2 CPU's

Sybrand Bakker wrote:

> I don't think it is a good idea to use PQ if you have only 2 CPU's.

PQ runs fine on a single CPU. It all depends on total CPU capacity and current CPU load.

We have been through this before Sybrand. :-)

> After all PQ will start up one process per CPU and a third process
> coordinating the 2 processes.

Not necessarily. Oracle does not perform process-to-CPU binding. It simply spawns a new process or thread. The operating system handles the CPU/process load balancing.

Thus, if your CPU 0 is running at 80% idle, you may well find that 2 PQ's plus the shadow process, are all running on CPU 0. CPU 1 can very well sit at 99% idle. (this was the case on Windows-NT Advance Server 3.5 running Oracle 7.1 when I tested this specifically some years ago)

Some kernels allows process to be bound to a CPU (I recall doing that many years ago on some Unix flavour). In retrospect, what I did back then was a bad idea as the kernel should be the sole one responsible for load balancing and process distribution, without outside inteference.

For example, on HP-UX 11 there is no process per CPU binding. That is what it seems like to me.

A single PQ process (i.e. Unix PID) is serviced by multiple CPU's. That is what both top and glance say. The PQ process runs on CPU 0, then CPU 2, then CPU 5, then CPU 0 again... each snapshot from top (and glance) shows it is running on a different CPU (this specific machine has 6 CPU's in total).

So as I mentioned before - it comes down to basic CPU capacity. And not number of CPU's. Granted that the more CPU's you have, the more CPU capacity you have.

> Also: tests shows you will *not* benefit from PQ if you haven't
> striped the affected tables, either in Oracle or on O/S level.

Yes. Good point and very important. You can very likely find that all your CPU's are sitting with very high idle times as they are all waiting data from the i/o subsystem which becomes the bottleneck.

I have seen [99% WAITING FOR I/O] on a MPP cluster (on all nodes) because of the incorrect striping or no striping. That means that the CPUs are doing less than 1% effective work. Additional PQ's only worsen the situation by increasing contention.

--
Billy