RE: statspack snapshots cause 3-4 sec of 100% CPU utilization

From: Boris Dali <boris_dali_at_yahoo.ca>
Date: Mon, 09 Jun 2003 07:51:06 -0700
Message-ID: <F001.005AD8FE.20030609072944@fatcity.com>

Nice one, John!
And quite portable. Runs on Solaris and Linux alike. On HP-UX 11.0 I had to modify it slightly, but it looks good too:

if [ $# -eq 1 ] ;then
  UNIX95= ps -eo pid,pcpu,ruser,time,etime,args | grep $1 | sort -nr +1 | awk '{if (NR <= 20) print substr($0,1,80)}'
else
  same as above wo/ grep
fi

I usually used BSD stile of ps on Linux. Something like:
 ps aufx | egrep '(USER|oracle)'
with exporting COLUMNS=200 prior to running this and stiking the puppy into an alias. But sorting by %CPU looks cool. (Solaris doesn't seem to understand the f flag and it should be /usr/ucb/ps, not default /usr/bin/ps)

Ok, back to the original issue :-)
Since snapshot causes 100% CPU util only for some 3 sec it was quite a challenge to catch it. The best I got so far is 72% for the shadow process taking the snapshot (after some 20 tries). I should probably put it in an infinite loop and "spool" (or tee) it to a file. Might as well try to catch /proc/pid/status (as I don't have the luxury of pmap here on Linux) for some memory stuff.

One thing, John. Since it doesn't split CPU utilization into user and kernel buckets - how does this help me? Wouldn't it be nice to get output similar to ptime [or at least time(x)] in "ps" output?

I am thinking of taking Dennis's advice and see what happens with level=0 or simply start commenting out code in the package and see when high CPU utilization drops (and I was hopping to go with level=7 to get some segement stats - he-he)

Thanks, John.

Cheers,
Boris Dali.

• John Kanagaraj <[EMAIL PROTECTED]> wrote: > Boris,
  >
  > Nice to discuss this with someone who understands
  > the numerous (and various)
  > options of 'sar' :) I use 'sar -r' to cross verify
  > the 'rate of need for
  > swap' - a sudden increase may mean either bursts of
  > I/O (eating up File
  > buffer space), memory leaks or a sudden rush of
  > programs.... Could you take
  > a quick snapshot of the top 20 CPU consumers using
  > the script below when the
  > snapshot runs? It takes the SID as a parameter to
  > grep out only Oracle
  > processes for that SID. The interesting part is that
  > the CPUTIME *and*
  > ELAPSED time is shown - you should run the snapshot
  > as a script (as in
  > sqlplus perfstat/**** @snapshot.sql) where
  > snapshot.sql has an execute,
  > followed by an exit. This way, one has a crude set
  > of CPU and Elapsed time
  > for that process as it runs...
  >
  > I use this to quickly point out processes that are
  > heavy and consistent CPU
  > consumers, allowing me to rap some knuckles ;-)
  >
  > #!/bin/ksh
  > #
  > # Name: top20.ksh
  > # Purpose: Display the top 20 CPU consumers.
  > Specify a SID to collect
  > # only those top procs related to that
  > SID in a multi-db system
  > # Author: John Kanagaraj, DBSoft Inc/ Aug 2001
  > # Notes: Tested and works on Solaris - may need
  > adjustment for other OS
  > #
  > uptime
  > echo "PID %CPU RUSER CPUTIME ELAPSED
  > COMMAND"
  > if [ $# == 1 ]; then
  > ps -eo pid,pcpu,ruser,time,etime,args | grep
  > $1 | sort -nr +1 |
  > head -20 | awk '{print substr($0,1,80)}'
  > else
  > ps -eo pid,pcpu,ruser,time,etime,args | sort
  > -nr +1 | head -20 |
  > awk '{print substr($0,1,80)}'
  > fi
  >
  > John Kanagaraj
  > Oracle Applications DBA
  > DBSoft Inc
  > (W): 408-970-7002
  >
  > Great, uplifting music - http://www.klove.com
  >
  > ** The opinions and statements above are entirely my
  > own and not those of my
  > employer or clients **
  

  ---

Post your free ad now! http://personals.yahoo.ca

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Boris Dali
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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 Mon Jun 09 2003 - 09:51:06 CDT