Are you using bind variables, array interface, and how often do you commit?
(should be not often)
Jim
"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 - 21:13:07 CDT
