Performance issue; cpu has 50%wio

[Daniel Nachaj]

Hello everybody, I have this performace issue on Oracle DB: db file parallel read has values about 758.430 ms. It seems like too large response time from storage. I am using XP12k for it. I have problem to find out what is causing this problem. Just I can see this idle on CPU 0% and too hight %wio. I think it should be the problem with SAN but don't see any waiting time on disks.
System was fine till now and all setting for LVM, filesystem etc should be ok. 10 days before DB have started with too long response times. Please see attached file with performance reports.
Many thanks for every hint :-)

[Daniel Nachaj]

This is HP-UX 11.23 IA.

[John Guster]

you had device swapped at certain point of time which is an indication of lack of memory.Increase device swap space to at least 1.5 times of the size of the RAM will help.
1. is pseudo swap turned on, swapmem_on?
2. what is dbc_pct_max setting?
3. what is the output of ipcs -mopb?
4. disk I/O seems fine as there is no wait time.

[Rita C Workman]

You got disk getting some fairly good hitting on them. Since you're running Oracle, check what are on these disk (what file system they go to).
- Are there too may Oracle logfiles hitting on the same disk, like all your cntl on one disk or all your redo logs on same disk? Do you have dbf files that would be heavy hitters all residing on same disk?
Get your disk to what data resides on them and go over them maybe with your DBA's. Possibly just moving things around would quiet some disk and reduce your I/O concerns

Also, if you look down at your sar -w, it is clearly showing that there is alot of context switching going on with the process. This could be up there due to I/O issue. So I'd probably look at that first and see if this reduces if you do need to move data around on disk.

Lastly, you can get too much context switching going on if some really lousy code is running. So, anything new happen on that system with application/oracle in the last 10 days?

Just a couple quick thoughts. I'm sure some of the performance guru's can share their expertise far better.

Regards,
Rita

[Steven E. Protter]

Shalom,

Your disks are being pounded fairly heavily. Oracle tends to do that.

I'd recommend you have the DBA's look at performance. You probably have a hot spot that is causing disk contention.

Going from raid 5 to raid 1 on the storage might help, but one would have to see the data before making a decision.

SEP

[Daniel Nachaj]

Hello guys,

@John
I can't reboot the massine now so I can't add swap at the moment.

1. is pseudo swap turned on, swapmem_on?
swpamem_on=1
2. what is dbc_pct_max setting?
dbc_max_pct=2
3. what is the output of ipcs -mopb?

IPC status from /dev/kmem as of Thu Aug 5 10:06:31 2010
T ID KEY MODE OWNER GROUP NATTCH SEGSZ CPID LPID
Shared Memory:
m 0 0x00a5c581 --rw------- sfmdb users 4 10469376 2895 2898
m 1 0x06347849 --rw-rw-rw- root sys 2 65544 4900 4919
m 2 0x0c6629c9 --rw-r----- root sys 2 27747080 4906 17619
m 3 0x491015d3 --rw-r--r-- root sys 0 22908 4905 4919
m 4 0x4118018a --rw-rw-rw- root root 0 348 5902 2136
m 5 0x4e0c0002 --rw-rw-rw- root root 2 61760 5902 3634
m 6 0x411c01a7 --rw-rw-rw- root root 2 8192 5902 2136
m 5799943 0xf710d700 --rw-rw---- orap01 dba 180 6276755456 5945 29440
m 32776 0x00004dbe --rw-rw-rw- root sapsys 1 1933788 7141 5869
m 98313 0x00000000 --rw-r----- p01adm sapsys 1 1024 7229 7229
m 32778 0x0382be84 --rw-rw-rw- p01adm sapsys 45 4096 7233 7298
m 32779 0x00002749 --rw-r----- p01adm sapsys 42 989532 7266 5869
m 360460 0x0000271a --rw-r----- p01adm sapsys 43 606000000 7266 7299
m 557069 0x00002711 --rw-r----- p01adm sapsys 43 524 7266 7299
m 163854 0x00000000 D-rw-rw-rw- p01adm sapsys 41 10737418240 7266 7266
m 98319 0x0000274e --rw-r----- p01adm sapsys 42 86079040 7266 5869
m 163856 0x00002719 --rw-r----- p01adm sapsys 40 134217728 7300 5869
m 163857 0x00002722 --rw-r----- p01adm sapsys 40 1313776 7300 5869
m 163858 0x00002738 --rw-r----- p01adm sapsys 40 318000000 7300 5869
m 557075 0x0000271d --rw-r----- p01adm sapsys 43 60211232 7298 5869
m 229396 0x00002725 --rw-r----- p01adm sapsys 1 3682592 7298 7298
m 688149 0x00002739 --rw-r----- p01adm sapsys 40 23315248 7300 5869
m 294934 0x00002723 --rw-r----- p01adm sapsys 40 349999824 7300 5869
m 294935 0x00002746 --rw-r----- p01adm sapsys 40 81920192 7300 5869
m 294936 0x00002751 --rw-r----- p01adm sapsys 40 4194400 7300 5869
m 294937 0x00002750 --rw-r----- p01adm sapsys 40 4194528 7300 5869
m 2097178 0x0000274f --rw-r----- p01adm sapsys 1 15904 7299 7299
m 8126491 0x00002716 --rw-r----- p01adm sapsys 40 921600000 8342 5869
m 3375132 0x0000272e --rw-r----- p01adm sapsys 40 83148 8354 5869
m 15433757 0x0000274a --rw-r----- p01adm sapsys 40 2076 7302 5869
m 1365573663 0x0128220f --rw-rw-r-- root sys 1 4192 4781 4781
m 220233760 0x00000000 D-rw-rw-r-- root sys 1 4192 3412 3412
m 241958945 0x01281001 --rw-rw-r-- root sys 1 4192 3415 3415
m 25231394 0x01281b20 --rw-rw-r-- root sys 1 4192 3416 3416
m 15925283 0x01281b27 --rw-rw-r-- root sys 1 4192 3417 3444
m 9928740 0x0128224e --rw-rw-r-- root sys 1 4192 4782 4782
m 39125029 0x01281b1d --rw-rw-r-- root sys 1 4192 3445 980
m 8912934 0x01281b66 --rw-rw-r-- root sys 1 4192 3446 3446
m 6127655 0x01281b70 --rw-rw-r-- root sys 1 4192 3447 3447
m 95518761 0x00002748 --rw-r----- p01adm sapsys 9 533456 7301 5869

[Daniel Nachaj]

@Steven
Did you mean, hot spot on SAN connection to XP, please ? Is it possible that waiting times on disks are fine via sar -d, but despite of hot spot on storage exists please ? I will try to ask storage admin for performance advisor report.

[Daniel Nachaj]

@Rita

You got disk getting some fairly good hitting on them. Since you're running Oracle, check what are on these disk (what file system they go to).
- Are there too may Oracle logfiles hitting on the same disk, like all your cntl on one disk or all your redo logs on same disk? Do you have dbf files that would be heavy hitters all residing on same disk?
Get your disk to what data resides on them and go over them maybe with your DBA's. Possibly just moving things around would quiet some disk and reduce your I/O concerns

Also, if you look down at your sar -w, it is clearly showing that there is alot of context switching going on with the process. This could be up there due to I/O issue. So I'd probably look at that first and see if this reduces if you do need to move data around on disk.

Lastly, you can get too much context switching going on if some really lousy code is running. So, anything new happen on that system with application/oracle in the last 10 days?

Just a couple quick thoughts. I'm sure some of the performance guru's can share their expertise far better.

I have dedicated filesystems for logs, archives and data and each filesystem resides on separate VG. Of course, most bussy disks are disks with oradata. Blok size for oradata has 8K, LV is crated by strict distributed policy. I/O is same as few weeks ago and I am not aware of any change except of patching. Anyway many thanks !

[Daniel Nachaj]

I just find out, that patches PHKL_39902 and PHKL_40094 could cause the performance degradation, Workaround is set mprotect_reduce_protid_on to 0. I have changed it now. Will let you know if it helps me.

[Rita C Workman]

If I read your one response above correctly you said you have dbc_max% set to "2".
From what I see you have around 24Gb of physical memory.

I think your dbc_max% is a bit too low...For 24Gb of phys-mem you might try 10-15% for max% and maybe 5% for dbc_min%.
Makes me wonder about a couple other parm settings you might have...

Could you provide either a list of your parms or just a list of these

dbc_max%
dbc_min%
ninode
vx_ninode
nproc
maxdsiz
maxssiz
maxtsiz
maxuprc
max_thread_proc
semm*
shmmax

That's a start - also I'm guessing you're PARisc and either 11iv1 or 11iv2, which is it?

Hope the patch does it for you, but if not get back to us with the parms.

Rgrds,
Rita