Q: Performance probs SC2000/Sol 2.3/Oracle7.0.16

Hi all,

we have set up a rather large DB machine and experience heavy performance problems. Maybe someone has helpful suggestions. Here the configuration:

Sun SC 2000:
- 12 processors (currently 11, one blew up ;-)

• 1.8 GB RAM
• 6 x 2.9 GB F/W differential SCSI for OS uses
• 10 x SparcStorageArray with 30 GB (30 x 1GB) for DB 2 way mirrored, so 150 GB are there
• Solaris 2.3 w/ patches (101318-54)
• Online Disk Suite 2.01
• Oracle 7.0.16

First we tried with DB on filesystems and saw a heavy 'pumping effect' where the machine was running fine for 5 seconds, and then idling for about 20. It looks like this had something to do with an interaction of fsflush and the Oracle db writer, so we changed to raw devices.

Now we don't see the pumping effect anymore, but we have a lousy read performance (i.e. a full table scan lasts about 10 times longer than on the Filesystem). Oracle db_block_size is 4 k and db_multi_block_read is set to 8, so Oracle should read in 32k blocks. iostat reports reads of 4k on the devices. And though no disk is more than 20-30% busy (delivering about 200-300kB/s), we see a *LOT* (about 60%) of CPU time spent in wio (wait for io). If we use dd on the devices with various block sizes, we see the expected results (about 1MB/s with 4 k blocks, at max 3.6 MB/s with 32k blocks read performance).

This is also what we see when the application is running normally. About 15% user CPU, 15% system CPU, 60% wait for IO. No disk really busy (max 40% for 1 or 2 seconds on some disk). BTW: Also on writing in normal operation Oracle doesn't nearly peak out any disk, but we can see the same (~60% wait for io).

We are now a little bit clueless what to do, because we don't really see the bottleneck. It is just that the machine is too slow. We will try fiddling with the db_block_size and db_multi_block_read, and with the mirroring parameters. We are just now setting up a test machine, to see whether disk striping will help.

Help will be *MUCH* appreciated.

Thanks and merry Xmas to all

-- 
Joe