Re: Oracle only utilising single processor

From: Justin Richardson <justin.richardson_at_redmar.com>
Date: 29 May 2002 12:17:07 -0700
Message-ID: <a24c4b48.0205291117.266410c1@posting.google.com>

Yes we are running Enterprise manager, we enabled a degree level (4) on each of the relevant tables and added the required changes to the init.ora (max servers 20)

The job is a sequences of selects from three different tables and an update to a forth.

I/O is not excessive through-out and the parallel processes are active. Each table is on a striped disk (5 spindles) and there is little or zero I/O queue lengths.

The OPQ did improve performance, though most of the delay appears to be with write operations so we enabled DML on the session, however these does not reduce the processing time.

The problem is user processing on the CPUs never really goes above 100% of the 800% available, (8 processor system). At times there is a large WT status, on all the CPUs, the remainder of the time they are relatively idle.

The system has 32gb, the SGA is 20GB and the database is far smaller.

At times the CPUs spend a lot of time in wait mode, is it work playing with

DB_WRITER_PROCESSES
DISK_ASYNCH_IO
DBWR_IO_SLAVES Disks are UFS, via Disksuite all forcedirectio.

A truss on the parallel processes highlights the following errors;

lwp_cond_signal(0xFFFFFFFF7CEFBF70) = 0 lwp_cond_wait(0xFFFFFFFF7CEFBF70, 0xFFFFFFFF7CEFBF80, 0x00000000) = 0

kaio(AIOWAIT, 0xFFFFFFFFFFFFFFFF)		Err#22 EINVAL

pread(405, "0602\0\001\0 q *\01CE7E6".., 65536, 0x0E254000) = 65536

0xFFFFFFFF7CE7DF80, 0x00000000) (sleeping...)

    Received signal #14, SIGALRM, in semop() [caught] semop(2031616, 0xFFFFFFFF7FFFD4AC, 1) Err#91 ERESTART sigprocmask(SIG_BLOCK, 0xFFFFFFFF7FFFCE68, 0x00000000) = 0 sigprocmask(SIG_UNBLOCK, 0xFFFFFFFF7FFFCE68, 0x00000000) = 0

justin.richardson_at_redmar.com (Justin Richardson) wrote in message news:<a24c4b48.0205190610.72fbb6e3_at_posting.google.com>...
> We are evaluating various configurations to improve performance on an
> Oracle Server. Using a Sun V880, with 8 processors, 32GB memory and
> Oracle 8.1.7.
>
> Spread the tables over various spindles so there is little or no IO
> queues.
>
> However performance is still poor as the benchmark/job we have to test
> is only using a single processor. The other 7 do relatively little,
> with one processor maxed out for the 2 hour test.
>
> Is there any Oracle tuning that can spread the load?
>
> Would Parallel Query Server help? If so how can I tell if this is
> configured or installed? Any recommendations for configuring/tuning
> the database for PQS?
>
> Many thanks
Received on Wed May 29 2002 - 14:17:07 CDT