CPU utilization

[zsujith]

Hi,
In my unix system the CPU is 100% utilized.I found that oracle processes are utilized most of the CPU time.
Can any one advice me how to reduce CPU utilization? following is the sar and top command output for your reference.

$ sar -u 1 5
01:22:24 %usr %sys %wio %idle
01:22:25 32 10 58 0
01:22:26 35 14 50 1
01:22:27 40 19 42 0
01:22:28 38 16 45 1
01:22:29 39 17 44 0

Average 37 15 48 0
$
PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND
2234 orasftp0 1 0 0 14G 14G sleep 2:05 3.41% oracle
2246 orasftp0 1 0 0 14G 14G cpu/76 1:42 3.01% oracle
2208 orasftp0 1 0 0 14G 14G sleep 19.1H 2.15% oracle
10378 orasftp0 1 3 0 14G 14G sleep 5:23 2.02% oracle
29594 orasftp0 1 50 0 14G 14G sleep 9:05 1.03% oracle
260 orasftp0 1 16 0 14G 14G sleep 4:19 1.03% oracle
6610 orasftp0 1 20 0 14G 14G sleep 17.8H 0.79% oracle
13647 oracle 1 53 2 14G 14G sleep 13:07 0.73% oracle
13618 orasftp0 1 59 0 14G 14G sleep 7:14 0.63% oracle
29748 orasftp0 1 59 0 14G 14G sleep 9:03 0.56% oracle
2244 orasftp0 1 59 0 14G 14G sleep 0:27 0.53% oracle
13644 oracle 1 14 2 14G 14G sleep 13:41 0.50% oracle
545 orasftp0 1 59 0 14G 14G sleep 7:01 0.50% oracle
10517 orasftp0 1 59 0 14G 14G sleep 2:44 0.43% oracle
18978 orasftp0 1 59 0 14G 14G sleep 5:39 0.26% oracle

[Jeeshan]

your sar -u output shows that, wait i/o is too much high.

may be this is for your disk has bottleneck. execute command
#sar -d 5 5
it show your disk utilization. for this may be some kernel parameters can help yuou to reduce wait i/o. but this is not ultimate remedy.

[OFC_EDM]

Is there only 1 cpu?
How much memory is on the box?
How much of that memory is in use?
What is your disk utilization?
What type of disk is attached?
Which mount point are those processes accessing?
Is that mount point on local or attached disk?


Making the assumption that your bottleneck is disk based on the previous post.......

What's the nature of the orasftp process?
Is it transferring files to other systems?
Are all those processes accessing the same mount point?

If the processes are simply transferring files from a location to a remote server? Maybe moving the files to a different, less busy, disk may help?

[Avinash20]

Please post the output the following output

From # top output
Let me know the ==>
Load averages:
Memory:

# sar -d 2 2
# vmstat 2 2

>> Here from the sar -u output, 50% of the CPU is waiting for the IO.
>> There could be memory of disk bottleneck on the server.

[Bill Thorsteinson]

Check your disk wait statistics with sar.

Is the load on all disks roughly the same? If not you may need to move some tablespaces around. Are the partitions for the tablespaces striped? If not consider striping at least the busiest partitions.

If you are waiting for disk writes, and the load is evenly balanced, you may need more disks and or disk channels.

If you are mostly waiting for reads you need more memory, or need to reduce the memory requirement. Optimizing queries to avoid table scans on large tables may help.

[Hein van den Heuvel]

zsujith>> In my unix system the CPU is 100% utilized.I found that oracle processes are utilized most of the CPU time.

Super! You bought those cpu's for good reason. You wouldn't want them to be idle all the time no?

HPUX? Version? Oracle version? Platform? 32/64 bit? patches?

You mention 100% utilized, but only show just over 50% (usr+sys) and just under 50% free for work (wio+idle).
So which observation should we work with? The sar output, or your reported 100% busy?!

Please realize that wio time is NOT cpu-busy.
If there was a process that could use the CPU it would get it.

100% All the time is typically bad.
50% Average is just about optimal usage with room for occasional busy spots.

>> Can any one advice me how to reduce CPU utilization?

Why? Do you have to pay for it?
Is something not performing acceptably?

I would worry about the USR:SYS ratio, shown as 2.5:1
That's suspect. Might be over aggressive statistics collections, too many context switches, poorly tuned tcp stack, paging pressure, or failure to use raw or direct IO.

But if you really want to reduce CPU then the most expedious way to get there is to ask Oracle where it is burning its time and see if you can optimize that. Basic Oracle tuning stuff. Statspack, AWR,...
OS tuning is not likely to ever reduce the CPU time by say 10%, where Oracle tuning might exceptionally reduce it by 90%, but 20% or more is not uncommon.

ahsan>> your sar -u output shows that, wait i/o is too much high.

Huh? What could possibly be wrong with 50% wio? Its just busy. Please educate us!

ahsan>> may be this is for your disk has bottleneck. ?
Huh? Please educate me how you come to that insight.
Zsujith, suggest there is too much CPU being used, no concern about disks voiced.


>> some kernel parameters can help you to reduce wait i/o.

Not in my experience. If an application needs to do an IO, notably Oracle, then that IO is going to have to happen. No amount of system tuning can help, assuming the Oracle SGA is big enough. 14G is a good chunk!). If the oracle SGA vs Unix Buffer cache is very badly configured then a larger UBC can help, but that's sub-optimal (double buffering).

>> but this is not ultimate remedy.
Correct.

Kevin>> If the processes are simply transferring files from a location to a remote server? Maybe moving the files to a different, less busy, disk may help?

Hmmm,,, you may be reading too much in that username "sftp0".
It is unlikely to have anything to do with 'ftp' as in file-transfer-protocol.
But then, I may be reading too much in the command 'oracle'.
(It might not be the product we all know and love, but a renamed ftp. :-)


Avinash>> Please post the output the following output From # top output
Load averages:
Memory:
Please help me understand how that would help?!

Avinash>> vmstat 2 2
Now the vmstat _could_ be interesting if we have paging going on.
Unlikely, but if it did, then it could explain high system time and wio.


Bill>> Is the load on all disks roughly the same? If not you may need to move some tablespaces around.

Please help me understand how that would reduce CPU utilization.

Bill>> Optimizing queries to avoid table scans on large tables may help.

Now we are talking! albeit a WAG.

Ask Oracle which queries are burning most CPU and try to optimize those (first).

There are tools, companies, and consultants ready to assist with that.

Hope this helps some,
Best regards,
Hein van den Heuvel
HvdH Performance Consulting.

[Michael Steele_2]

Hi:

Gee that's a lot of writing for what is probably a memory leak and easily found using UNIX95. For example:

UNIX95= ps -o vsz,rsz,user,time,comm,args | sort -rn | head -10

You're looking for memory consumption with the vsz arguement, or virtual stack size. Put the command into a 15 minute cron and save the output to a file. Something like


echo ############### date ######## > file
UNIX95= ps -o vsz,rsz,user,time,comm,args | sort -rn | head -10 >> file
echo > file

You'll find one oracle process increasing vsz every 15 minutes instead of remaining steady. This is how you find a memory leak.

[Michael Steele_2]

Note: The disk bottleneck advice already given is also good advice, 'sar -d'. With this report a disk bottleneck exists when avwait is > than avserv. Note every disk where this occurs. Then 'pvdisplay' on the disk to find the filesystem. Consider defragging this filesystem or increasing its file system block size, or splitting it across stripped disks, or making another filesystem on other disks.

For example:

10:30:27 device %busy avque r+w/s blks/s avwait avserv

Average c0t0d0 6.20 22.50 14 302 5.46 5.25
Average c0t1d0 3.60 47.50 6 87 14.91 14.57

For c0t0d0 avwait = 5.46 > 5.25 / avserv
For c0t1d0 avwait = 14.91 > 14.57 / avserv
pvdiaply -v /dev/dsk/c0t0d0 | page
pvdisplay -v /dev/dsk/c0t1d0 | page

[Hein van den Heuvel]

Michael>> for what is probably a memory leak and easily found using UNIX95.

Yet another 'intersting' conclusion in the endless list of clueless replies.

Please help me understand how you conclude the is a memory leak, progressing over time.

Oracle is not known to do so.
And if it did, would it not be a specific process or two, not all of them as suggested by the provided ps output?

Oracle is known to map large, even very large SGA (Shared Global Area) memory segements shared by all, making all processes much similar in size. 14G is large, but nothing special really. No leak, just a setting by the DBA which may, or might not, be appropriate.

Current versions of Oracle even manage the total amount of heap (malloc) memory the combined processes of a single instance use.
Google for: pga_aggregate_target

Not the SA, but the DBA is to one to tackle a problem like this (if there even is a problem, and not just a misinterpretation of sar output)

Hope this clarifies some.
Regards,
Hein.

[Michael Steele_2]

When a process can't release its memory its usually hung and can't exit or close. A process that can't exit or close is going to continue to consume CPU time. If you search on memory leak you'll see UNIX95 is used to diagnose.

Looking back on the command example I neglected to add the PCPU arguement.

UNIX95= ps -o vsz,rsz,PCPU, user,time,comm,args | sort -rn | head -10

Refer to -o arguements under 'ps' command.

http://docs.hp.com/en/B2355-60103/ps.1.html

[Oviwan]

hey

for a rdbms it's the best if the cpu is at 100% used by oracle processes. so oracle gets the data most likely from the SGA, so the disk I/O are reduced. hence to reduce your cpu utilization, reduce the SGA then you will get more disk I/O's but this would be senseless.

Regards

[Michael Steele_2]

Hein:

I would agree with ashan that %wio is too high. But I also wonder how reliable sar is to glance. 'Sar' can and does report incorrect data.

zsujith:

Compare your %wio with glance and see how similar they are.

%wio is in measure of disk i/o wait time. But this is going to vary with disk array cache size, disk rotation, raid, o/s, etc.

Hein:

Please restrict your replys to the rules of the forum and respond to the author of the thread only. I've seen too many of your replys directed to other forum members. Bad business. Don't be so prideful.

[zsujith]

Thank You Michle,

I couldnâ t reduce the waiting for I/O, How ever I understand that disk speed is one of the possible causes for WIO. As my understand disk striping may reduce the waiting for I/O.

Please correct me...

Thanks
Sujith

[Michael Steele_2]

Disk stripping will reduce i/o. Load balancing across more spindles. One extent gets written to the first, the 2nd extent gets written to the 2nd, the 3rd to the 1st, and so on.