Oracle vs HP-UX stats. Could anybody explain?

[Yuriy_8]

Have strange situation. "sar -u" shows that wio is near 1-3%, so no waiting on IO. But Oracle stats says that most important waits events are "direct path read" and "direct path writes" means heavy IO work.
Could anybody explain this?

[Mel Burslan]

sar output is a reflection of the whole system performance. Unless your oracle is living on the system disk and this is the only disk you have on that system, the sar output does not have much meaning for the io performance of oracle.

It depends how your database is laid out on your volumes. If all your database containers are on a single volume, guess what ? everybody will need to go down that one single pipe and there definitely be a i/o contention.

I am not a database admin and/or performance expert but, without knowing the database volume/filesystem layout, it is very hard to determine the causes of bad performance.

[Florian Heigl (new acc)]

direct path to my understand means `uncached/unbuffered` which would be somewhat natural for a database.

throw in a sar -d 3 20 to get exact disk stats, also try glance / measureware and look for high run queues and alike.

but no wio during the test time should mean things are ok.
of course the disk i/o is still the largest producer of wio, because the disks are accessed a lot and *are* the slowest component available.

tuning opportunities are there, i.e. check vendor docs and raise scsi queue depths (we're talking 1%-3% gain here), increase buffering and stuff.

my tip: get an evalution copy of spotlight, and an experienced dba for cross-checking v$-tables.
I found it´s a sysadmin`s dream for analyzing oracle performance issues and on quite a few occasions could tell our DB staff which query was flawed durig time of performance issues.
(I'm not affilated with the vendor, I don`t even know it, it was the DBA´s tool after all :)

[Jon M Zellhoefer · Accepted solution]

What type of disk is your database on? If you are on an EMC or XP disk array, you will see high disk I/O activity in the database, but not from sar because it does not report any I/O's after they have been commited to the fabric. This is especially the case if you are using mincache=direct, convosync=direct in your fstab for those database filesystems (not applicable if you are using raw devices).

[Yuriy_8]

I will explain situation in more details.
I am a DBA and had to investigate performance problem with our db. Oracle says (through it's V$ views) that the most important wait evens are IO related. But our OS administrator looked at sar -u and said that CPU were fully utilized and wio was only 1-2%. Everybody decided that i was wrong and IO wasn't out bottleneck.

We use HP-UX 11i and HP EVA storage. Datafiles are on raw devices. There are no other applications except db.

So as i understand it's posible that sar won't show high wio if we use such storage? Even if we use raw devices?

[Jean-Luc Oudart]

Yuriv

What version of Oracle do you run ?
Do you use Async IO ?
Do you have statspack reports ?

Regards
Jean-Luc