Oracle-L: RE: cpu on AIX

From: Henry Poras <hporas_at_etal.uri.edu>
Date: Wed, 09 Oct 2002 15:34:30 -0800
Message-ID: <F001.004E5104.20021009153430@fatcity.com>

Dennis,
Users are complaining, and at least this time the guilty process seems to be an import from a database out of my control. What I am trying to do is control how often this happens. It seems a bit strange that one moderate, single threaded import should drain both CPUs on the server, so I was trying to see how much could be pinned on those processes (imp and associated oracle shadow process)and how much was due to other use and poor configuration.

As I mentioned, both %wio (wa on vmstat AIX) and %idle were ~0. Everything was split (kind of evenly 50-50 to 40-60) between %usr and %sys. Using ps -o pcpu I could pin about 30% of %CPU on the import. I am not sure if this includes the associated system calls (io) from this process. I don't think so. (I wasn't seeing my %CPU adding up to 100% earlier because I was leaving out the kernal processes. I needed a ps -k flag).

Now I am seeing some other funky stuff (maybe related, maybe not. I haven't looked carefully for this before so I don't know) on the same machine. The import ended and the %usr %sys breakdown was still 40-60. There are two kproc processes (async IO???) each using 44.4%CPU. That's been unchanged for hours and the machine is not being heavily used. Also, some other processes are using 10-20%CPU which puts me up over 100% (I guess it really can give 120%).

I'll let you know what I find.

Henry

-----Original Message-----
WILLIAMS
Sent: Wednesday, October 09, 2002 3:50 PM To: Multiple recipients of list ORACLE-L

Henry - Here are my reactions, and hopefully someone that knows how to track system CPU usage back to an application will reply. One thought I have is to run each application on a test server and observe the system I/O there. I haven't tried this. Other thoughts:

Are interactive users complaining? If they are, then you have a definite problem.
In terms of maxing out the CPU, does this situation continue for long periods of time? For example, I have a financial system that is overloaded at the first of the month, but underutilized the rest of the month. But another system simply couldn't process everything they needed each day, so we had to do something. In that case the users ceased running some reports.
I looked in Oracle Performance Tuning 101 to see what Gaja has to say. He points out that the Solaris sar -q command has a "%wio" column, a measure of processes that are currently using the CPU, but are waiting for I/O requests to be serviced and hence are not making prudent use of the CPU. He further says that %sys and %wio should be less than 10-15% and if it is consistently higher you need to get to the bottom of it, and usually it is a application causing the problem. No details on how to get to the bottom.
Maybe you can get some type of O.S. audit that can report what system calls are being made, and that will give you a clue.

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Henry Poras
  INET: hporas_at_etal.URI.EDU

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Wed Oct 09 2002 - 18:34:30 CDT