Re: Multiprocessing: Oracle 7 & Solaris 2.3. How much?

In article <rdw-1208941843030001_at_reflex.demon.co.uk>, rdw_at_reflex.co.uk (Richard Williams) writes:

> How successful is Oracle at dividing query/update load over the
processors?

Oracle has for a long time split its processing functions into the processes that you interact with (i.e. your SQL*Forms session) and the background processes (4 or more) that service the Oracle system needs. If your operating system could successfully split tasks among processors, you could take advantage of mutilple processors. The next bottleneck came when many users tried to hit these key Oracle background processes (like the database writer or archiver) at the same time. Oracle 7.0 allowed you to spawn multiple database writer processes and this helped the situation where you have a lot of writers at the same time. The archiver is one key process that can be a bottleneck, both for not being able to keep up with a large number of heavy updaters or if you archive to disk, the disk fills up and the system stops until you unstick the archiver. The archiver tends to be a problem only on systems that do no clean up the archive logs to tape routinely or you have massive systems (150 GB +) systems that run massive batch update jobs. Very few users can type fast enough to stress the archiver.

The multi-threaded server allows you to have multiple processes working between the user process (perhaps a C program or 4GL tool) and the database key processes. A new feature in Oracle 7.1 that we are about to try out is the parallel query option. The Oracle database tries to break down a query into smaller units (either sequences such as one process hitting the index and another doing the calls on the main table or queries to multiple tables handled by separate processes). I am not sure whether 7.1 is out for the Sun yet, we are only using it on our Sequent box (our Suns are at 7.0.16). One of the greatest challenges that I see from our beta test of 7.1.2 is the need to gather statistics and determine rules as to how many processes a query can be broken in to.

One final note, we are running Oracle 7.0.16 on a Sparcserver 1000 with 6 or 8 processors and it would not matter if we had 100 in there. The system is I/O bound, badly (50% or more of the time waiting for I/O on SAR %wio). The contractor who developed it thought CPU power would solve all problems and ignored I/O capacity (they used regular SCSI versus Fast/Wide) and distributing tables to distribute the I/O load. Therefore, I would always recommend a careful evaluation of whether your system's I/O can keep up with the multiple processors once you start using your additional processors.

Best of luck. Received on Wed Aug 17 1994 - 03:24:04 CEST