Re: Oracle only utilising single processor

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

This clearly demonstrates you need to tune/redesign the application. PERIOD. You have way too much full table scans, you have increased the SGA far above the Oracle recommended one third.
Hence you have increased page faulting, and your pagefile and the CPU resources associated with it are the bottleneck. What you want is not going to work EVER.

Regards

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address



"Justin Richardson" <justin.richardson_at_redmar.com> wrote in message
news:a24c4b48.0205291117.266410c1_at_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