very slow performance of oracle

[Pulse001]

We are experiencing very slow performance of oracle database on our server. Particularly DBA team is complaining about wio during load test.

- The OS is HPUX 11.23 on BL860c blade.
- Total 24gb RAM and 4 cores.
- Running Oracle 10g Database.
- I have observed upto 40% wio from sar output during peak load.
- Even though there was 40% wio, there was no swaping.
- Current free memory is 6gb.
- Total storage allocated is 2.6 TB.

bdf output is as below:-

Filesystem kbytes used avail %used Mounted on
/dev/vg00/lvol3 4194304 612304 3554792 15% /
/dev/vg00/lvol1 1835008 157336 1664664 9% /stand
/dev/vg00/lvol8 16777216 6201280 10494632 37% /var
/dev/vg00/lvol7 10485760 3010704 7416728 29% /usr
/dev/vgora/lv_u10 616038400 493238084 115125298 81% /u10_new
/dev/vgnew/lv_u10 523239424 490518840 30675585 94% /u10
/dev/vgnew/lv_u09 524288000 343947077 169070885 67% /u09
/dev/vgdata/lv_u06 131072000 71788563 55578226 56% /u06
/dev/vgdata/lv_u05 143360000 114790263 26784132 81% /u05
/dev/vgdata/lv_u04 143360000 100961073 39749055 72% /u04
/dev/vgdata/lv_u03 143360000 126569944 15740679 89% /u03
/dev/vgdata/lv_u02 143360000 117914462 23855252 83% /u02
/dev/vgdata/lv_u01 15360000 9923592 5112700 66% /u01
/dev/vg00/lvol6 8388608 6774880 1608296 81% /tmp
/dev/vgdata/lv_purge
2048000 1097770 890874 55% /purge
/dev/vg00/lvol5 10485760 5403480 5042616 52% /opt
/dev/vg00/lvol4 20971520 18923800 2033480 90% /home
/dev/vgdata/lv_appslog
4096000 46051 3796875 1% /appslog
/dev/vgdata/lv_flow
5120000 32565 4769521 1% /FLOW


Gurus, i need your help in sorting out the issue. What may be the cause of high wio during peak load ?

I know that almost always the problem is with Database/Application, but how do i prove it to the DBA ?

I have collected relevant logs for past one month, i can share the logs if needed.

[Prasanth V Aravind]

40% wio in sar.... it means cpu is waiting to complete IO...

Do you have glance installed in this box ?
what is the disk utilization (busy) showing in glance ?
what iostat output showing ?
check sar -d output

you will be able to find some disks/fs/vg which having high bottleneck...


Gudluck
Prasanth

[Jean-Luc Oudart]

Hi

I believe you must work with your DBA on this one.
Is this a new situation and if yes what has changed (hw, OS, DB, Application).#
Do you have a performance baseline for the server, database , SAN ?

Can you find out which of these file systems is causing trouble ?

Could you get your DBA to run statspack on the database ? the statspack report along with your data should help narrowing down the issue.

Where is the oracle sw located ?

Regards
Jean-Luc

[Pulse001]

I have captured sar, sar -d, swapinfo -tam, top and sar -w for last one month. During peak load time, i observed 100% busy root disk.

06:00:25 device %busy avque r+w/s blks/s avwait avserv
06:00:27 c1t0d0 100.00 13.63 886 85224 17.39 8.79
c3t0d0 0.50 0.50 1 24 0.00 2.60
06:00:29 c1t0d0 98.01 14.50 855 82325 17.95 8.82
c3t1d3 0.50 0.50 0 8 0.00 11.70
06:00:31 c1t0d0 82.41 13.50 768 67514 14.59 7.69
c3t0d1 0.50 0.50 2 40 0.00 2.47
06:00:33 c1t0d0 86.57 13.10 646 62328 16.72 9.06
c3t1d5 0.50 0.50 0 8 0.00 3.83
06:00:35 c1t0d0 17.50 4.36 63 3053 5.03 7.22
c3t0d2 0.50 0.50 0 8 0.00 10.04
c3t1d4 0.50 0.50 0 8 0.00 7.78
06:00:37 c1t0d0 2.51 0.50 4 64 0.00 19.72
c3t0d1 0.50 0.50 2 32 0.00 3.68
06:00:39 c1t0d0 1.99 0.50 4 64 0.00 15.49
c3t0d0 0.50 0.50 1 32 0.00 0.35
c3t0d1 0.50 0.50 2 48 0.00 2.20
06:00:41 c1t0d0 2.49 0.50 4 96 0.00 20.49
c3t0d2 0.50 0.50 1 16 0.00 5.32
c3t0d3 1.00 0.50 1 16 0.00 8.23
c3t0d6 0.50 0.50 0 8 0.00 3.74
06:00:43 c1t0d0 1.52 0.50 3 34 0.00 18.44
c3t0d1 0.51 0.50 2 32 0.00 3.26
c3t1d3 0.51 0.50 1 8 0.00 6.51
06:00:45 c1t0d0 2.49 0.50 3 56 0.00 6.64
c3t0d3 0.50 0.50 0 1 0.00 6.45
c3t3d0 0.50 0.50 0 1 0.00 9.76

Average c1t0d0 39.58 13.47 324 30099 16.40 8.61
Average c3t0d0 0.10 0.50 0 6 0.00 1.25
Average c3t1d3 0.10 0.50 0 2 0.00 9.10
Average c3t0d1 0.20 0.50 1 15 0.00 2.78
Average c3t1d5 0.05 0.50 0 1 0.00 3.83
Average c3t0d2 0.10 0.50 0 2 0.00 6.90
Average c3t1d4 0.05 0.50 0 1 0.00 7.78
Average c3t0d3 0.15 0.50 0 2 0.00 7.63
Average c3t0d6 0.05 0.50 0 1 0.00 3.74
Average c3t3d0 0.05 0.50 0 0 0.00 9.76



What maybe the cause ? Is oracle trying to write a lot to /tmp/.oracle or something else in vg00 ?

[Prasanth V Aravind]

You may need to use glance OR ovpa for further debugging, which will say which file-system is more read/write intensive during peak hours


Gudluck
Prasanth

[Prasanth V Aravind]

30 days trail version is available for glance plus..


https://h10078.www1.hp.com/cda/hpms/display/main/hpms_content.jsp?zn=bto&cp=1-11-15-28^9637_4000_100__

you can go for this, if you don't have any other options & can get license in in one month.

Gudluck
Prasanth

[Jean-Luc Oudart]

Hi again

is oracle software installed in vg00 ?
(not a good setup!)

as indicated, glance can give you better information.

Regards
Jean-Luc

[Duncan Edmonstone]

so what is writing/reading to the root disk?

during normal operation of an Oracle DB, root disk IO you would expect to be low... has someone put redo logs or the like on the root disk?

HTH

Duncan

[stephen peng]

i don't think installing oracle software on vg00's filesystem would increase vg00's i/o so much. you need to find out all those oracle relevant file position, together with DBA. and them, look into /tmp.

[stephen peng]

the setting of oracle's tmp directory?

[Pulse001]

No oracle is not installed in vg00, it is on /u01. That is why it is even more baffling to understand why io to root disk is very high.

[Pulse001]

I am attaching the captured logs here

[Pulse001]

Logs of top command attached herewith, captured during peak load.

[Pulse001]

Hi,

I have installed Glance now. I can check io to vg00 (for individual LV's e.g /tmp). Let me check the io now and i will sit with DBA to check file locations.

[Pulse001]

.

[TwoProc]

Ask your DBA to review his statspack data, it will clearly list the largest I/O offender queries that are running in the system. You need to start there.