Oracle disk contention/ cpu usage

[Mike Ross]

Hi all

Just been lumbered installing the application my company support on an
oracle installation and know nothing about it, normally handle ingres dbs.

From what I can see oracle is struggling, but I'd like to get an idea of how
fast I should expect it to be.
Its running on a bitching sparc unix, 4 cpus, plenty of disk. However I
believe there is disk contention. Using top the cpu usage never gets above
8% for oracle (I assume thats 8% of total available, hence max 25%) and
iowait is around 15%. The application is running at 20%.

How can I tell if oracle or application is producing the iowait (nothing
else is running on the box) and where its producing the iowait. What sort of
%cpu activity is normal (I realise the higher the better).

the output from sar shows two disks (md2 and md5) having avwaits of 13.4 and
8.3 respectively. How do I translate them to vfstab entries. Is that high?

At the mo the machine is bulk loading a db. Currently running at @ 228
recs/second. Surely that should be much higher?
Any pointers on where to look would be much apprec.

tia
Mike

[Noons]

"Mike Ross" <mike@mchaver.freeserve.co.uk> wrote in
news:avmvo2$h0j$1@news6.svr.pol.co.uk and I quote:

> Just been lumbered installing the application my company support on an
> oracle installation and know nothing about it, normally handle ingres
> dbs.

Oh! That's nice...
Ever thought of getting some training?

> Its running on a bitching sparc unix, 4 cpus, plenty of disk. However I
> believe there is disk contention. Using top the cpu usage never gets
> above 8% for oracle (I assume thats 8% of total available, hence max
> 25%) and iowait is around 15%. The application is running at 20%.

15% io wait is nothing. 8% CPU usage even less. 20% app use
is way too low. I'd say you got a serious problem somewhere
else...
Do not use top. Use sar. Over a reasonably large cycle period,
say 10 seconds or more. Check out the system time. And the
context switches. Both are killers in Unix.

>
> How can I tell if oracle or application is producing the iowait (nothing
> else is running on the box) and where its producing the iowait. What
> sort of %cpu activity is normal (I realise the higher the better).

100% CPU activity can be achieved even with less than twice as many
processes as you have CPUs.

>
> the output from sar shows two disks (md2 and md5) having avwaits of 13.4
> and 8.3 respectively. How do I translate them to vfstab entries. Is that
> high?
>

No, that's not high. You got a serious problem somewhere else,
nothing to do with Oracle or your I/O. As for translating:
that is highly dependent on how you got your system setup
as far as disk I/O and filesystems are concerned.

Try looking in fstab for the device that is mapped against
the directory. It's the /dev/**** string. That should tell
you which disk and partition. Unless you're using a
LVM. As I said: highly dependent on what I/O setup you got.

> At the mo the machine is bulk loading a db. Currently running at @ 228
> recs/second. Surely that should be much higher?

The bulk load process is stuffed. Needs to be looked at.
Not the database setup. My guess is that the process is
reading the input data with one of the high overhead Unix
read calls. Probably getch() or something similar...

> Any pointers on where to look would be much apprec.

Gladly. Check the application code as well as bulk load
code. I'll give you 99% odds there is something very wrong
with one (or both) of them.


--
Cheers
Nuno Souto
[email]nsouto@optusnet.com.au.nosp[/email]am