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

Hi!

Well, conventional insert just behaves differently than just dumping a datafile to filesystem. There is redo logging needed for each of your records for example (depending on how you have implemented your inserts) and much much more. Also, your inserting server process will not be responsible of writing the blocks to disk, DBWR does that (unless you are using direct path inserts).

Also, Oracle doesn't parallelize your conventional inserts any way if you are issuing them just from one session, that's why you don't see any CPU usage.

Try to do a insert /*+ APPEND */ into table select * from sys.source$ or something similar to see better "performance" of your disks.

Otherwise I suggest you turning to v$session_wait and v$session_event for help in tuning your insert speeds and parallelize your inserts.

Tanel.

"Tom Kranz" <tom_at_siliconbunny.com> wrote in message news:858768d8.0309220811.742e1505_at_posting.google.com...
> 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."
Received on Mon Sep 22 2003 - 16:19:33 CDT