Re: Oracle disk contention/ cpu usage

"Mike Ross" <mike_at_mchaver.freeserve.co.uk> wrote in news:avmvo2$h0j$1_at_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
nsouto_at_optusnet.com.au.nospam