Performance Question/Mystery

Got an interesting problem with tuning Oracle.... Let me say up front I'm not a DBA (but am an experienced systems professional).

Environment:   	Pyramid Nile 150 (4 250Mhz Mips CPUS), 
		Oracle 7.1.6, 
		SQL Queries being run using SAS/Access.    
		Test database of 120 Gb.   
		Oracle OPS installed

We are doing some benchmarking on alternative SCSI Disk storage on this machine. In the baseline tests, running 3 parallelized queries, the machine averaged 60-80% busy - mostly user, some sys and wio. (as measured with sar). In this environment, since the machine is not running at 100%, I guess I assumed we were likely I/O bound. I/O service times were ~ 10-15 ms on all the disk involved.

So, we put a high performance disk subsystem in place and ran the same tests. The disk average service time dropped to ~2-5ms, and CPU dropped a bit. However, the elapsed time (wall clock) of the queries didn't drop significantly. I guess I expected that the CPU usage would go up and the elapsed time of the queries would come down.

This is starting to look like perhaps Oracle isn't multithreading as much as we might like internally (ie are we bottlenecked on internal locks in ORacle?). Does anyone have any experience here which they would be willing to share?

Thanks
Terry Green Received on Sun Feb 09 1997 - 00:00:00 CST