Poor Oracle performance with Solaris 8 on a Sunfire, talking to a SAN

Hi Everyone,

This is stumping the DBA and sysadmin team here, so I'm begging for ideas and things to look at, no matter how off-the-wall you may consider them.

We have 2 Oracle 8.1.7.4 (32bit) instances, running on a Sunfire 4800. OS is Solaris 8 (64 bit), with KP 19. We have 10 CPUs, and 12GB RAM.

The volumes are from a RAID 1+0 LUN mounted from an EMC Clariion SAN. The LUN is 100gb in size, and we're using VxVM to chop it up into 10 volumes of varying sizes. These are formatted with VxFS and presented to the OS.

From Solaris, we can use dd to create a 4GB file. We see the LUN being 100% busy, and peaking at about 50MB/s, which works out at around 700 write I/Os per second.

Whip into Oracle, and create a datafile on a filesystem on that LUN, and we get similar performance.

All is well so far.

However.

DBAs have setup a table, no indexes or anything, and then they run an insert of 1 million rows into it.

We then see performance of around 2.5MB/s, with the LUN hovering at the 12% busy mark. Number of write I/Os per second is very poor - around 70 or so.

During this time, we're not seeing any wait I/O from Solaris, nor are the other CPUs busy, and we're seeing no mutexes.

It's as if there's something in Oracle holding it back from pushing those disks.

What we're seeing is all I/O peformance, both reads and writes, sucking quite badly from Oracle.

The DBAs have been perusing statspack output - nothing jumps out at them. We've looking at latch contention, mem useage, SGA sizing, db block buffers - all look OK.

We've exhausted the list of tweaks and problem areas, and still this poor performance exists.

Does anyone have any ideas?

Thanks very much.
TOM

--
"Tell them we are not Gods, but Sysadmins, which is the next best
thing."