Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Performance issue; cpu has 50%wio

Daniel Nachaj
Occasional Advisor

Performance issue; cpu has 50%wio

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 :-)


12 REPLIES
Daniel Nachaj
Occasional Advisor

Re: Performance issue; cpu has 50%wio

This is HP-UX 11.23 IA.
John Guster
Trusted Contributor

Re: Performance issue; cpu has 50%wio

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
Honored Contributor

Re: Performance issue; cpu has 50%wio

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
Exalted Contributor

Re: Performance issue; cpu has 50%wio

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
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Daniel Nachaj
Occasional Advisor

Re: Performance issue; cpu has 50%wio

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
Occasional Advisor

Re: Performance issue; cpu has 50%wio

@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
Occasional Advisor

Re: Performance issue; cpu has 50%wio

@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
Occasional Advisor

Re: Performance issue; cpu has 50%wio

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
Honored Contributor

Re: Performance issue; cpu has 50%wio

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
Volker Borowski
Honored Contributor

Re: Performance issue; cpu has 50%wio

Hey,

this is a SAP system.

So if everything was OK 10 days ago, and your
storage now shows no defects, why do you even
consider to change something on the storage
as option number one?

I mean if it was bad before that, ok, something could have been missed, but if it
was fine, do not go that way right now.
There are tons of things to check inside SAP
or Oracle first, before it comes to storage
(again: on a system that has been well before!).

If SAP has been patched recently or new
modules have been activated, a significant
amount of new objects could have been
created inside the DB. So may be simply some
DB-stats need to be calculated.

With SAP it is important not only to be on
a current ORACLE patchset, but to have quite
a lot of bugfixes on top of that and a DB
parameterset, that fits the bugfixes as well.

As for what is running right now, check
transactions ST04 / ST04old inside the SAP,
to see what the application is doing right
now and if some indexes could help.

Hope this helps
Volker
TwoProc
Honored Contributor

Re: Performance issue; cpu has 50%wio

I would have the DBA's make sure that the current long running queries that are on the system haven't gone wacky due to bad cost statistics. If your stats haven't been updated in a while, this could (and probably is) more of a DBA/Oracle issue than a system issue. To date, I've only seen two occasions where the system itself if responsible for a slowdown, one was a runaway system process(every evident from glance), and the second was the write through cache board on the XP had errored and the XP storage system decided to (correctly)protect itself and its data integirty by turning this feature off until a board level replacement was made.

Other than the two above cases, over the last many years, it's always a case of statistics being out of date on tables, and needing to refreshed so that the optimizer in Oracle could pick the correct execution plan to get the queries done correctly.

Please have your DBA check long running queries, queries consuming the most disk I/O, and run statspack reports to identify the problem.
We are the people our parents warned us about --Jimmy Buffett
Dave Johnson_1
Super Advisor

Re: Performance issue; cpu has 50%wio

My 2 cents...
adding swap space on 11.23 IA-64 does not cause/require a reboot of the server. If you want/need to remove a swap file, that requires a reboot, but not adding some.
I have an rx6600 running 11.23 and added swap space last month, no reboot.