monitors:db_cpu.ksh [Xymonton]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== db_cpu.ksh ======

^ Author | [[ everett.vernon@gmail.com | Vernon Everett ]] |
^ Compatibility | Tested on Xymon 4.3.10, Solaris 9, 10 & 11. On Xymon 4.2.3 the stack graph doesn't work|
^ Requirements | Perl, Oracle Database, Solaris |
^ Download | None |
^ Last Update | <del>2013-04-23</del> 2014-02-27 |

===== Description =====
How much CPU is that Oracle database instance using?

This came about from an interesting discussion with our DBAs. \\
They run multiple databases on a single host, and he wanted to know which of his databases was taking up the lion's share of the CPU. \\
Memory usage is easy, because you can limit it on the database, but CPU not so much. The database isn't a single process.\\
After playing about, I figured out a way to tally up the CPU usage of all the processes related to a single database instance.\\
Once I had done that, I realised that we could do this for all databases, and present the results to Xymon for graphing. The usage trends have proven very useful.
We are using the results for job scheduling, capacity planning, and license reduction by reducing the number of available CPUs. \\
\\
So, that's what it does.\\
It plots the CPU usage of every database instance it can find.\\
It can also alert if one is using too much, and for fun, it can add the "idle" and "other" CPU figures, to complete the 100% and make a stack graph.
===== Installation =====
=== Client side ===  
  - Copy the 2 scripts to ~/client/ext/
  - chown root prustat.pl
  - chmod 5744 prustat.pl
  - Add the following to ~/etc/clientlaunch.cfg
<code>
[db_cpu]
        ENVFILE $XYMONCLIENTHOME/etc/xymonclient.cfg
        CMD $XYMONCLIENTHOME/ext/db_cpu.ksh
        LOGFILE $XYMONCLIENTHOME/logs/db_cpu.log
        INTERVAL 5m
</code>
=== Server side ===
==Edit xymonserver.cfg==
Add to the TEST2RRD variable\\
<code>
db-cpu=ncv
</code>
Add to GRAPHS variable\\
<code>
db-cpu::100
</code>
Add the following config entry.
<code>
SPLITNCV_db-cpu="*:GAUGE"
</code>
==Add the following code to graphs.cfg==
This is the code for a stack graph. Probably works better if you include the idle and other usage figures.
<code>
[db-cpu]
  FNPATTERN db-cpu,(.*).rrd
  TITLE Database %CPU Utilisation
  YAXIS %
  -l 0
  -u 100
  DEF:p@RRDIDX@=@RRDFN@:lambda:AVERAGE
  AREA:p@RRDIDX@#@COLOR@:@RRDPARAM@:@STACKIT@
  GPRINT:p@RRDIDX@:LAST: \: %5.1lf (cur)
  GPRINT:p@RRDIDX@:MAX: \: %5.1lf (max)
  GPRINT:p@RRDIDX@:MIN: \: %5.1lf (min)
  GPRINT:p@RRDIDX@:AVERAGE: \: %5.1lf (avg)\n
</code>
For a line graph, use this code. If you are not including the idle and other CPU usage, then this is probably a better option.
<code>
[db-cpu]
  FNPATTERN db-cpu,(.*).rrd
  TITLE Database %CPU Utilisation
  YAXIS %
  -l 0
  -u 100
  DEF:p@RRDIDX@=@RRDFN@:lambda:AVERAGE
  LINE2:p@RRDIDX@#@COLOR@:@RRDPARAM@
  GPRINT:p@RRDIDX@:LAST: \: %5.1lf (cur)
  GPRINT:p@RRDIDX@:MAX: \: %5.1lf (max)
  GPRINT:p@RRDIDX@:MIN: \: %5.1lf (min)
  GPRINT:p@RRDIDX@:AVERAGE: \: %5.1lf (avg)\n
</code>
===== Source =====
==== prustat.pl ====

This is a slightly modified version of Brendan Gregg's excellent dtrace script.\\
I take no credit for it at all. (As much as I would love to)\\
Go look [[http://www.brendangregg.com|here]] for more of his awesome work. \\
I changed the PATH definition to hard coded in the script, instead of inherited from the environment, which could cause security issues because the script is running as root.

<hidden onHidden="Show Code ⇲" onVisible="Hide Code ⇱">
<code>
#!/usr/bin/perl
#
# prustat - Process utilisation stats: %CPU, %Mem, %Disk, %Net. Solaris 10.
#       Needs to run as root. This is a demonstration release - check for
#       newer optimised versions. Uses Kstat, DTrace and procfs.
#
# 12-Mar-2005, ver 0.50  (demonstration release, http://www.brendangregg.com)
#
#
# USAGE:
#       prustat [-cehinuwxz] [-p PID] [-s sort] [-t top] [interval] [count]
#
#      prustat               # %Utilisation
#      prustat -i            # + I/O stats
#      prustat -u            # + USR/SYS times
#      prustat -x            # + Context Switchs
#      prustat -c            # Clear screen
#      prustat -w            # Wide output
#      prustat -z            # Skip zero lines
#      prustat -e            # Extra precision
#      prustat -p PID        # this PID only
#      prustat -s sort       # sort on pid|cpu|mem|disk|net|utime|vctx|...
#      prustat -t lines      # print top number of lines only
#  eg,
#      prustat 2                # 2 second samples (first is historical)
#      prustat 10 5             # 5 x 10 second samples
#      prustat -t 8 10 5        # 5 x 10 second samples, top 8 lines only
#      prustat -ct 20 5         # 20 lines with screen refresh each 5 seconds
#      prustat -iuxct 5 10      # multi output, all reports every 10 seconds
#      prustat -ct 22 -s cpu 5  # 22 lines, sort by cpu, every 5 secs
#      prustat -ct 22 -s mem 5  # 22 lines, sort by mem, every 5 secs
#      prustat -ct 22 -s net 5  # 22 lines, sort by network, every 5 secs
#      prustat -ct 22 -s disk 5 # 22 lines, sort by disk, every 5 secs
#
# FIELDS:
#               PID     Process ID
#               CPU     Percent CPU
#               Mem     Percent RAM
#               Disk    Percent Disk
#               Net     Percent Network
#               MAJF    Major Page Faults (disk I/O)
#               INBLK   In Blocks (disk I/O reads)
#               OUBLK   Out Blocks (disk I/O writes)
#               CHAR-kb Character I/O Kbytes
#               COMM    Command name
#               USR     User Time
#               SYS     System Time
#               WAIT    Wait for CPU Time
#               VCTX    Voluntary Context Switches (I/O bound)
#               ICTX    Involuntary Context Switches (CPU bound)
#               SYSC    System calls
#
# WARNING: This program will run DTrace to gather Disk and Network data.
#       This has not been fully tested on different environments to study the
#       impact of these extra measurements. For now this is a demonstration
#       release - best to run in development for short periods. Check for
#       newer versions and updates to this message.
#
# NOTE: There is no historical values for Disk or Network utilisation percent,
#       the first sample for these will always show zero.
#
# REFERENCES: /usr/include/sys/procfs.h
#
# SEE ALSO: iosnoop, psio, prusage              # process Disk I/O
#           socketsnoop.d                       # process TCP
#           prstat -m                           # USR/SYS times, ...
#
# COPYRIGHT: Copyright (c) 2005 Brendan Gregg.
#
#  This program is free software; you can redistribute it and/or
#  modify it under the terms of the GNU General Public License
#  as published by the Free Software Foundation; either version 2
#  of the License, or (at your option) any later version.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#  along with this program; if not, write to the Free Software Foundation,
#  Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
#
#  (http://www.gnu.org/copyleft/gpl.html)
#
# Author: Brendan Gregg  [Sydney, Australia]
#
# 12-Mar-2005   Brendan Gregg   Created this.

use Getopt::Std;
use Sun::Solaris::Kstat;
my $Kstat = Sun::Solaris::Kstat->new();


#
# --- Default Variables ---
#
$INTERVAL = 1;          # seconds to sample
$MAX = 1;               # max count of samples
$NEW = 0;               # skip summary output (new data only)
$WIDE = 0;              # print wide output (don't truncate)
$SCHED = 0;             # print PID 0
$TOP = 0;               # print top many only
$CLEAR = 0;             # clear screen before outputs
$ZERO = 0;              # if 1, skip zero entries (all 0.00)
$STYLE_UTIL = 1;        # default output style, utilisation
$STYLE_IO = 0;          # output style, I/O
$STYLE_CTX = 0;         # output style, Context Switches
$STYLE_TIME = 0;        # output style, Times
$STYLE_EXTRA = 0;       # output style, Extra precision
$MULTI = 0;             # multi report, multiple styles
$TARGET_PID = -1;       # target PID, -1 means all
$TIME_BEGIN = 1;        # start of interval, ns
$TIME_END = 1;          # end of interval, ns
$count = 1;             # current iteration
$NIC_DEF = 100_000_000; # default NIC speed (100 Mbps)

### Network card instance names
@Network = qw(dmfe bge be ce eri ge hme le ppp qfe rtls sppp iprb);
$Network{$_} = 1 foreach (@Network);


#
# --- Command Line Arguments ---
#
&Usage() if $ARGV[0] eq "--help";
getopts('cehinuwxzp:s:t:') || &Usage();
&Usage() if $opt_h;
$NEW = 1 if $opt_n;
$WIDE = 1 if $opt_w;
$CLEAR = 1 if $opt_c;
$ZERO = 1 if $opt_z;
$STYLE_IO = 1 if $opt_i;
$STYLE_CTX = 1 if $opt_x;
$STYLE_TIME = 1 if $opt_u;
$STYLE_EXTRA = 1 if $opt_e;
$STYLE_IO = 1 if $opt_i;
$STYLE_UTIL = 0 if $opt_i || $opt_x || $opt_u || $opt_e;
$TOP = $opt_t if defined $opt_t;
$SORT = $opt_s if defined $opt_s;
$TARGET_PID = $opt_p if defined $opt_p;
$MAX = 2**32 if @ARGV == 1;
$INTERVAL = shift(@ARGV) || $INTERVAL;
$MAX = shift(@ARGV) || $MAX;
$CLEARSTR = `clear` if $CLEAR;
$MULTI = 1 if ($STYLE_IO + $STYLE_CTX + $STYLE_TIME) > 1;


#
# --- Determine Network Capacity ---
#
my ($error,$time,$module,$instance,$name);
my ($bytes,$rbytes,$wbytes);
my (%Modules,%Instances,%Names);
$NIC_SPEED = 0;         # sum of Mbps across all NICs

### Loop over all NICs
foreach $module (keys(%$Kstat)) {
        next unless $Network{$module};
        $Modules = $Kstat->{$module};
        foreach $instance (keys(%$Modules)) {
                $Instances = $Modules->{$instance};
                foreach $name (keys(%$Instances)) {
                        $Names = $Instances->{$name};
                        if (defined $$Names{ifspeed}) {
                                $NIC_SPEED += $$Names{ifspeed};
                        } else {
                                $NIC_SPEED += $NIC_SPEED;
                        }
                }
        }
}
$NIC_SPEED = $NIC_DEF if $NIC_SPEED == 0;


#
# --- Open DTrace ---
#
@Dscript = <DATA>;
$dscript = join('',@Dscript);
$ENV{"PATH"} = "/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:/usr/perl5/bin:/usr/openwin/bin:/usr/X11/bin:/usr/ccs/bin:/usr/dt/bin/:/usr/local/samba/bin:/opt/sfw/bin";
open(DTRACE,"$dscript 2>/dev/null |") || die("ERROR1: Can't open dtrace: $!\n");

### Cleanup on signals
$SIG{INT} = \&Cleanup;
$SIG{QUIT} = \&Cleanup;
$SIG{TERM} = \&Cleanup;
$SIG{PIPE} = \&Cleanup;


#
# --- Main ---
#
for (;$count <= $MAX; $count++) {

        ### Get CPU and Mem data
        &GetProcStat();

        next if $NEW && $count == 1;

        ### Preprocess PID
        &ProcessPID();

        ### Print data
        print $CLEARSTR if $CLEAR;
        &PrintUtil($SORT) if $STYLE_UTIL;
        &PrintExtra($SORT) if $STYLE_EXTRA;
        &PrintIO($SORT) if $STYLE_IO;
        &PrintCtx($SORT) if $STYLE_CTX;
        &PrintTime($SORT) if $STYLE_TIME;

        ### Cleanup memory
        undef %Comm;
        undef %PID;
        $TIME_BEGIN = $TIME_END;

        ### Get Disk and Net data
        for ($pause = 0; $pause < $INTERVAL; $pause++) {
                &GetDTraceStat();
        }
}

close(DTRACE);


#
# --- Subroutines ---
#

# GetProcStat - Gets /proc usage statistics and saves them in %PID.
#       This can be run multiple times, the first time %PID will be
#       populated with the summary since boot values.
#       This reads /proc/*/usage and /proc/*/prstat.
#
sub GetProcStat {
   my $pid;
   chdir "/proc";

   ### Main PID Loop
   foreach $pid (sort {$a<=>$b} <*>) {
        next if $pid == $$;
        next if $pid == 0 && $SCHED == 0;
        next if $TARGET_PID > -1 && $pid != $TARGET_PID;

        ### Read usage stats
        open(USAGE,"/proc/$pid/usage") || next;
        read(USAGE,$usage,256);
        close USAGE;

        ### Unpack usage values
        ($pr_lwpid, $pr_count, $pr_tstamp, $pr_create, $pr_term,
         $pr_rtime, $pr_utime, $pr_stime, $pr_ttime, $pr_tftime,
         $pr_dftime, $pr_kftime, $pr_ltime, $pr_slptime, $pr_wtime,
         $pr_stoptime, $filltime, $pr_minf, $pr_majf, $pr_nswap,
         $pr_inblk, $pr_oublk, $pr_msnd, $pr_mrcv, $pr_sigs,
         $pr_vctx, $pr_ictx, $pr_sysc, $pr_ioch, $filler) =
         unpack("iia8a8a8a8a8a8a8a8a8a8a8a8a8a8a48LLLLLLLLLLLLa40",$usage);

        ### Process usage values
        $New{$pid}{utime} = timestruct2int($pr_utime);
        $New{$pid}{stime} = timestruct2int($pr_stime);
        $New{$pid}{ttime} = timestruct2int($pr_ttime);
        $New{$pid}{ltime} = timestruct2int($pr_ltime);
        $New{$pid}{wtime} = timestruct2int($pr_wtime);
        $New{$pid}{slptime} = timestruct2int($pr_slptime);
        $New{$pid}{minf}  = $pr_minf;
        $New{$pid}{majf}  = $pr_majf;
        $New{$pid}{nswap} = $pr_nswap;
        $New{$pid}{inblk} = $pr_inblk;
        $New{$pid}{oublk} = $pr_oublk;
        $New{$pid}{vctx}  = $pr_vctx;
        $New{$pid}{ictx}  = $pr_ictx;
        $New{$pid}{sysc}  = $pr_sysc;
        $New{$pid}{ioch}  = $pr_ioch;
        # and a couple of my own,
        $New{$pid}{blks}  = $pr_inblk + $pr_oublk;
        $New{$pid}{ctxs}  = $pr_vctx + $pr_ictx;


        ### Read psinfo stats
        open(PSINFO,"/proc/$pid/psinfo") || next;
        read(PSINFO,$psinfo,256);
        close PSINFO;

        ### Unpack psinfo values
        ($pr_flag, $pr_nlwp, $pr_pid, $pr_ppid, $pr_pgid, $pr_sid,
         $pr_uid, $pr_euid, $pr_gid, $pr_egid, $pr_addr, $pr_size,
         $pr_rssize, $pr_pad1, $pr_ttydev, $pr_pctcpu, $pr_pctmem,
         $pr_start, $pr_time, $pr_ctime, $pr_fname, $pr_psargs,
         $pr_wstat, $pr_argc, $pr_argv, $pr_envp, $pr_dmodel,
         $pr_taskid, $pr_projid, $pr_nzomb, $pr_poolid, $pr_zoneid, $filler) =
         unpack("iiiiiiiiiiIiiiiSSa8a8a8Z16Z80iiIIaa3iiiiii",$psinfo);

        ### Process psinfo values
        $PID{$pid}{pctcpu} = $pr_pctcpu / 0x8000;
        $PID{$pid}{pctmem} = $pr_pctmem / 0x8000;
        $PID{$pid}{uid} = $pr_uid;
        $New{$pid}{size}   = $pr_size;
        $New{$pid}{rssize} = $pr_rssize;

        ### Save command name
        $Comm{$pid} = $pr_fname;
   }

   ### Turn incrementals into values
   foreach $pid (keys %New) {
        # save PID values,
        foreach $key (keys %{$New{$pid}}) {
                $PID{$pid}{$key} = $New{$pid}{$key} - $Old{$pid}{$key};
        }
   }
   undef %Old;

   ### Remember old value
   foreach $pid (keys %New) {
        # save old values,
        foreach $key (keys %{$New{$pid}}) {
                $Old{$pid}{$key} = $New{$pid}{$key};
        }
   }
}


# GetDTraceStat - read detals from a DTrace connection until a heartbeat
#       is read (happens every second).
#
sub GetDTraceStat {
        my ($line,$cmd,$rest,$uid,$pid,$size,$name,$delta);

        while ($line = <DTRACE>) {
                chomp($line);
                ($cmd,$rest) = split(' ',$line,2);

                ### Start
                $TIME_BEGIN = $rest if $cmd eq "B";

                ### Heartbeat
                if ($cmd eq "T") {
                        $TIME_END = $rest;
                        last;
                }

                ### Network traffic
                if ($cmd eq "N") {
                        ($uid,$pid,$size,$name) = split(' ',$rest);
                        next if $TARGET_PID > -1 && $pid != $TARGET_PID;
                        $PID{$pid}{netrw} += $size;
                        unless (defined $Comm{$pid}) {
                                $Comm{$pid} = $name;
                                $PID{$pid}{uid} = $uid;
                        }
                }

                ### Disk traffic
                if ($cmd eq "D") {
                        ($uid,$pid,$delta,$size,$name) = split(' ',$rest);
                        next if $TARGET_PID > -1 && $pid != $TARGET_PID;
                        $PID{$pid}{dtime} += $delta;
                        unless (defined $Comm{$pid}) {
                                $Comm{$pid} = $name;
                                $PID{$pid}{uid} = $uid;
                        }
                }
        }
}

# ProcessPID - pre process %PID before printing.
#       This calculates values such as sumpct for sorting.
#
sub ProcessPID {
        my ($pid,$cpu,$mem,$disk,$net,$sample);
        my ($factorcpu,$factormem,$factordisk,$factornet);

        ### Factors for %util conversions
        $sample = $TIME_END - $TIME_BEGIN || 1;
        $factorcpu = 100;
        $factormem = 100;
        $factordisk = 100 / $sample;
        $factornet = 800 / ($NIC_SPEED * ($sample / 1_000_000_000));

        ### Process %PID
        foreach $pid (keys(%PID)) {
                $cpu = $PID{$pid}{pctcpu} * $factorcpu;
                $mem = $PID{$pid}{pctmem} * $factormem;
                $disk = $PID{$pid}{dtime} * $factordisk;
                $net = $PID{$pid}{netrw} * $factornet;
                $PID{$pid}{cpu} = $cpu;
                $PID{$pid}{mem} = $mem;
                $PID{$pid}{disk} = $disk;
                $PID{$pid}{net} = $net;
                $PID{$pid}{all} = $cpu + $mem + $disk + $net;
        }
}

# PrintUtil - print a report on utilisation.
#
sub PrintUtil {
        my $sort = shift || "all";
        my $top = $TOP;
        my ($pid,$cpu,$mem,$disk,$net,$all);

        ### Print header
        printf("%5s %6s %6s %6s %6s  %s\n","PID",
         "%CPU","%Mem","%Disk","%Net","COMM");

        ### Print report
        foreach $pid (&SortPID("$sort")) {

                # Fetch utilisations
                $cpu = $PID{$pid}{cpu};
                $mem = $PID{$pid}{mem};
                $disk = $PID{$pid}{disk};
                $net = $PID{$pid}{net};
                $all = $PID{$pid}{all};

                # Skip zero lines if needed
                if ($ZERO && ($all < 0.02)) { next; }

                # Print output
                printf("%5s %6.2f %6.2f %6.2f %6.2f  %s\n",$pid,
                 $cpu,$mem,$disk,$net,trunc($Comm{$pid},33));
                last if --$top == 0;
        }
        print "\n" if $MULTI;
}

# PrintExtra - print a report on utilisation, with extra decimal places.
#
sub PrintExtra {
        my $sort = shift || "all";
        my $top = $TOP;
        my ($pid,$cpu,$mem,$disk,$net,$all);

        ### Print header
        printf("%5s %8s %8s %8s %8s  %s\n","PID",
         "%CPU","%Mem","%Disk","%Net","COMM");

        ### Print report
        foreach $pid (&SortPID("$sort")) {

                # Fetch utilisations
                $cpu = $PID{$pid}{cpu};
                $mem = $PID{$pid}{mem};
                $disk = $PID{$pid}{disk};
                $net = $PID{$pid}{net};
                $all = $PID{$pid}{all};

                # Skip zero lines if needed
                if ($ZERO && ($all < 0.02)) { next; }

                # Print output
                printf("%5s %8.4f %8.4f %8.4f %8.4f  %s\n",$pid,
                 $cpu,$mem,$disk,$net,trunc($Comm{$pid},33));
                last if --$top == 0;
        }
        print "\n" if $MULTI;
}

# PrintIO - print a report with I/O statistics: minf, majf, inblk, oublk, ioch.
#
sub PrintIO {
        my $sort = shift || "blks";
        my $top = $TOP;
        my ($pid,$cpu,$mem,$disk,$net,$all);

        ### Print header
        printf("%5s %6s %6s %6s %6s %8s %8s %9s %s\n","PID",
         "%CPU","%Mem","%Disk","%Net","INBLK","OUBLK",
         "CHAR-kb","COMM");

        ### Print report
        foreach $pid (&SortPID("$sort")) {

                # Fetch utilisations
                $cpu = $PID{$pid}{cpu};
                $mem = $PID{$pid}{mem};
                $disk = $PID{$pid}{disk};
                $net = $PID{$pid}{net};
                $all = $PID{$pid}{all};

                # Skip zero lines if needed
                if ($ZERO && ($all < 0.02)) { next; }

                # Print output
                printf("%5s %6.2f %6.2f %6.2f %6.2f %8d %8d %9.0f %s\n",
                 $pid,$cpu,$mem,$disk,$net,$PID{$pid}{inblk},
                 $PID{$pid}{oublk},$PID{$pid}{ioch}/1024,
                 trunc($Comm{$pid},33));
                last if --$top == 0;
        }
        print "\n" if $MULTI;
}

# PrintTime - print a report including usr, sys and wait times.
#
sub PrintTime {
        my $sort = shift || "cpu";
        my $top = $TOP;
        my ($pid,$cpu,$mem,$disk,$net,$all);

        ### Print header
        printf("%5s %6s %6s %6s %6s %8s %8s %8s %s\n","PID",
         "%CPU","%Mem","%Disk","%Net","USR","SYS",
         "WAIT","COMM");

        ### Print report
        foreach $pid (&SortPID("$sort")) {

                # Fetch utilisations
                $cpu = $PID{$pid}{cpu};
                $mem = $PID{$pid}{mem};
                $disk = $PID{$pid}{disk};
                $net = $PID{$pid}{net};
                $all = $PID{$pid}{all};

                # Skip zero lines if needed
                if ($ZERO && ($all < 0.02)) { next; }

                # Print output
                printf("%5s %6.2f %6.2f %6.2f %6.2f %8d %8d %8d %s\n",
                 $pid,$cpu,$mem,$disk,$net,$PID{$pid}{utime},
                 $PID{$pid}{stime},$PID{$pid}{wtime},
                 trunc($Comm{$pid},33));
                last if --$top == 0;
        }
        print "\n" if $MULTI;
}

# PrintCtx - print a report on context switches: vctx, ictx and sys calls.
#
sub PrintCtx {
        my $sort = shift || "ctxs";
        my $top = $TOP;
        my ($pid,$cpu,$mem,$disk,$net,$all);

        ### Print header
        printf("%5s %6s %6s %6s %6s %8s %8s %9s %s\n","PID",
         "%CPU","%Mem","%Disk","%Net","VCTX","ICTX",
         "SYSC","COMM");

        ### Print report
        foreach $pid (&SortPID("$sort")) {

                # Fetch utilisations
                $cpu = $PID{$pid}{cpu};
                $mem = $PID{$pid}{mem};
                $disk = $PID{$pid}{disk};
                $net = $PID{$pid}{net};
                $all = $PID{$pid}{all};

                # Skip zero lines if needed
                if ($ZERO && ($all < 0.02)) { next; }

                # Print output
                printf("%5s %6.2f %6.2f %6.2f %6.2f %8d %8d %9d %s\n",
                 $pid,$cpu,$mem,$disk,$net,$PID{$pid}{vctx},
                 $PID{$pid}{ictx},$PID{$pid}{sysc},
                 trunc($Comm{$pid},33));
                last if --$top == 0;
        }
        print "\n" if $MULTI;
}

# SortPID - sorts the PID hash by the key given as arg1, returning a sorted
#       array of PIDs.
#
sub SortPID {
        my $sort = shift;

        ### Sort numerically
        if ($sort eq "pid") {
                return sort {$a <=> $b} (keys %PID);
        } else {
                return sort {$PID{$b}{$sort} <=> $PID{$a}{$sort}} (keys %PID);
        }
}

# timestruct2int - Convert a timestruct value (64 bits) into an integer
#       of seconds.
#
sub timestruct2int {
        my $timestruct = shift;
        my ($secs,$nsecs) = unpack("LL",$timestruct);
        my $time = $secs + $nsecs * 10**-9;
        return $time;
}

# trunc - Returns a truncated string if required.
#
sub trunc {
        my $string = shift;
        my $length = shift;

        if ($WIDE) {
                return $string;
        } else {
                return substr($string,0,$length);
        }
}

# Cleanup - subroutine for signal management.
#
sub Cleanup {
        close(DTRACE);
        exit(0);
}

# Usage - print usage message and exit.
#
sub Usage {
        print STDERR <<END;
prustat ver 0.50, demo release
USAGE: prustat [-cehinuwxz] [-p PID] [-s sort] [-t top] [interval] [count]

      prustat               # \%Utilisation
      prustat -i            # + I/O stats
      prustat -u            # + USR/SYS times
      prustat -x            # + Context Switchs
      prustat -c            # Clear screen
      prustat -w            # Wide output
      prustat -z            # Skip zero lines
      prustat -e            # Extra precision
      prustat -p PID        # this PID only
      prustat -s sort       # sort on pid|cpu|mem|disk|net|utime|vctx|...
      prustat -t lines      # print top number of lines only
  eg,
      prustat 2                # 2 second samples (first is historical)
      prustat 10 5             # 5 x 10 second samples
      prustat -t 8 10 5        # 5 x 10 second samples, top 8 lines only
      prustat -ct 20 5         # 20 lines with screen refresh each 5 seconds
      prustat -iuxct 5 10      # multi output, all reports every 10 seconds
      prustat -ct 22 -s cpu 5   # 22 lines, sort by cpu, every 5 secs
      prustat -ct 22 -s mem 5   # 22 lines, sort by mem, every 5 secs
      prustat -ct 22 -s net 5   # 22 lines, sort by network, every 5 secs
      prustat -ct 22 -s disk 5  # 22 lines, sort by disk, every 5 secs

END
        exit;
}

#
# --- DTrace ---
#

__DATA__
/usr/sbin/dtrace -n '
/*
** The following is a trimmed version of two seperate DTrace scripts:
**
** socketsnoop.d - snoop TCP network socket traffic by process.
**  This is intended to identify the process responsible
**  for network traffic. Written in DTrace (Solaris 10 build 63).
**
** iosnoop.d - A program to print I/O events as they happen, with useful
**      details such as UID, PID, inode, command, etc.
**      Written in DTrace (Solaris 10 build 63).
**
*/

#pragma D option quiet


/*
** --- TIMESTAMPS ---
*/
dtrace:::BEGIN {
        printf("B %d\n",timestamp);
        /* last is used as a timestamp to the disk request, OR,
           to the last disk completion. This is needed to avoid
           over counting disk times due to disk buffers (queues),
           however remains a minor simplification. */
        last = timestamp;
}
profile:::tick-1sec {
        /* heartbeat */
        printf("T %d\n",timestamp);
}


/*
** --- DISK ----
*/

/*
**  Store entry details
*/
io:::start
{
        this->dev = args[0]->b_edev;
        this->blk = args[0]->b_blkno;
        start_uid[this->dev,this->blk] = curpsinfo->pr_euid;
        start_pid[this->dev,this->blk] = pid;
        start_comm[this->dev,this->blk] = (char *)curpsinfo->pr_fname;
        last = timestamp;
}

/*
**  Process completion
*/
io:::done
{
        /* fetch entry values */
        this->dev = args[0]->b_edev;
        this->blk = args[0]->b_blkno;
        this->delta = timestamp - last;
        this->suid = start_uid[this->dev,this->blk];
        this->spid = start_pid[this->dev,this->blk];
        this->scomm = start_comm[this->dev,this->blk];

        /* memory cleanup */
        start_uid[this->dev,this->blk] = 0;
        start_pid[this->dev,this->blk] = 0;
        start_comm[this->dev,this->blk] = 0;
        last = timestamp;
}

/*
**  Print event details
*/
io:::done
{
        printf("D %d %d %d %d %s\n",
         this->suid,this->spid,this->delta,args[0]->b_bcount,
         this->scomm == 0 ? "." : stringof(this->scomm));
}


/*
** --- NETWORK ----
*/

/*
**  Store Write Values
*/
fbt:ip:tcp_output:entry
{
        self->uid = curpsinfo->pr_euid;
        self->pid = pid;
        self->comm = (char *)curpsinfo->pr_fname;
        self->size = msgdsize(args[1]);
        self->ok = 1;
}

/*
**  Store Read Values
*/
fbt:sockfs:sotpi_recvmsg:entry
{
        self->uid = curpsinfo->pr_euid;
        self->pid = pid;
        self->comm = (char *)curpsinfo->pr_fname;
        /* We track the read request (man uio), */
        self->uiop = (struct uio *) arg2;
        self->residual = self->uiop->uio_resid;
        /* The following ensures the type is AF_INET (sys/socket.h), */
        this->sonode = (struct sonode *)arg0;
        self->ok = (int)this->sonode->so_type == 2 ? 1 : 0;
}
fbt:sockfs:sotpi_recvmsg:return
/arg0 != 0 && self->ok/
{
        /* calculate successful read size */
        self->size = self->residual - self->uiop->uio_resid;
}

/*
**  Print output
*/
fbt:ip:tcp_output:entry, fbt:sockfs:sotpi_recvmsg:return
/self->ok/
{
        printf("N %d %d %d %s\n",self->uid,self->pid,
         self->size,stringof(self->comm));
        self->ok = 0;
        self->uid = 0;
        self->pid = 0;
        self->comm = 0;
        self->size = 0;
        self->residual = 0;
        self->uiop = 0;
}
'

</code>
</hidden>


==== db_cpu.ksh ====
This is my contribution.\\
It calls the perl script above.
<hidden onHidden="Show Code ⇲" onVisible="Hide Code ⇱">
<code>
#!/usr/bin/ksh
COLOUR=green
type top >> /dev/null
ERR=$?
if [ $ERR -eq 0 ]
then
   TOP=$(type top| cut -d\( -f2 | cut -d\) -f1)
else
   [ -x /usr/local/bin/top ] && TOP=/usr/local/bin/top
fi

CPU_tmp=$XYMONTMP/db_cpu_list
TEMPFILE=$XYMONTMP/db_cpu
NO_DB_ALERT=true
NO_DB_COLOUR=yellow

# Some typesets to make formatting easier.
typeset -L15 DB
typeset -R10 USAGE

# To include the idle and "other" CPU utilisations in your graph, set this
# to a value of 1. Set this to anything else to only show the database usage
SHOW_IDLE=1

# To alert on excessive CPU usage, set this to 1, or anything else if you don't
EXCESS_ALERT=1
# And set the alert levels. Salt to taste.
# These will be ignored if EXCESS_ALERT is not set to 1
RED=75
YELLOW=50

# Start making the page.
echo "$(date +%a" "%b" "%d" "%T" "%Z" "%Y) - Database CPU Usage" > $TEMPFILE
echo >> $TEMPFILE
echo "Database          CPU % Usage" >> $TEMPFILE
echo "--------          -----------" >> $TEMPFILE

# Get the idle figure. Run top -d4, and take the last value to prevent
# a possible skewed result from the other xymon tasks that have just kicked off.
if [ -x $TOP ]
then
   # We have a working version of top
   IDLE=$($TOP -n -d4 | grep "^CPU states" | cut -d"%" -f1 | sed "s/CPU states: //g" | tail -1)
   IDLE="${IDLE}000"
else
  # No working top. We will have to resort to iostat.
  # Pity. Top is at least accurate to 1 decimal place.
  IDLE=$(iostat -p 2 5 | awk '{ print $NF }' | tail -1)
  IDLE="${IDLE}.0000"
fi

# Set DBID to your oracle identifier. You can use the userID or the username
# or any value that will identify your oracle processes in a ps | grep
DBID="^ 0000100"
# Get a list of all Databases running on the system
DBLIST=$(ps -efa | grep "$DBID" | grep pmon | cut -d"_" -f3 | sort | uniq)
if [ -z "$DBLIST" ]
then
    echo "No databases found!" >> $TEMPFILE
    [ "$NO_DB_ALERT" = "true" ] && COLOUR=$NO_DB_COLOUR
    DB_USED="0.0000" # A reasonable assumtion if there are no databases
else
   # Get a list of all the processes and their CPU utilisation.
   # This is a slightly modified version of Brendan Gregg's excellent dtrace script.
   # I take no credit for it at all. (As much as I would love to)
   # Go look here for more of his awesome work. http://www.brendangregg.com
   $XYMONCLIENTHOME/ext/prustat.pl -we 2>/dev/null | awk '{ print $1" x " $2 }' > $CPU_tmp 2>/dev/null

   DB_USED=0
   OTHER=0
   for tDB in $DBLIST
   do
      [ -z "$tDB" ] && continue # Do not bother if there are empty strings
      LIST="0"
      DB=$tDB
      # We can't use $DB for the egrep, because of the formatting. It adds extra spaces.
      for PROC in $(ps -efa | egrep "oracle${tDB} |ora_...._${tDB}$"| grep -v " grep " | awk '{ print $2 }')
      do
        LIST=$(grep "^${PROC} x " $CPU_tmp | cut -d" " -f3)+$LIST
      done
      # We need to use bc for our calculations, because shell maths is limited
      # to integers, and we need a higher level of precision for the
      # results to "add up".
      USAGE=$(echo $LIST | sed 's/++*/+/g' | sed 's/^+//g' | bc)
      USAGE=$(echo $USAGE | sed 's/ \./0\./g')
      [ $USAGE -eq 0 ] && USAGE="0.0000"
      if [ $EXCESS_ALERT -eq 1 ]
      then
         # We want to alert on excessive usage.
         [ $USAGE -gt $YELLOW ] && COLOUR=yellow
         [ $USAGE -gt $RED ] && COLOUR=red
      fi
      DB_USED=$(echo "$DB_USED+$USAGE" | bc)
      USAGE=$(echo $USAGE | sed 's/ \./0\./g')
      echo "$DB $USAGE" >> $TEMPFILE.2
   done
fi
if [ $SHOW_IDLE -eq 1 ]
then
   OTHER=$(echo "100-(${DB_USED}+${IDLE})" | bc)
   # Sometimes, with low "Other" usage, and because we collect the samples at
   # different times, we can end up with negative values for "OTHER"
   # This is obviously incorrect, so we do a bit of magic to make it "look" right.
   # We only change the IDLE and OTHER values though, not the database values.
   # Those are real data.
   if [ $OTHER -lt 0 ]
   then
      IDLE=$(echo ${IDLE}+${OTHER} | bc)
      OTHER="0.0000"
   fi
   [ $IDLE -eq 0 ] && IDLE="0.0000"
   echo "--------          -----------" >> $TEMPFILE.2
   DB="Other          "
   USAGE=$OTHER
   USAGE=$(echo "$USAGE" |  sed "s/ \./0\./g")
   echo "$DB $USAGE" >> $TEMPFILE.2
   DB="Idle"
   USAGE=$IDLE
   USAGE=$(echo "$USAGE" |  sed "s/ \./0\./g")
   echo "$DB $USAGE" >> $TEMPFILE.2
fi
cat $TEMPFILE.2 >> $TEMPFILE
# Send the status
$XYMON $XYMSRV "status $MACHINE.db-cpu $COLOUR $(cat $TEMPFILE)"
# Clear the TEMPFILE again, so we can send just the data for graphing.
> $TEMPFILE
grep -v -- "^-----" $TEMPFILE.2 | while read a b
                                  do
                                     echo "$a : $b" >> $TEMPFILE
                                  done

# Now send the graph details
$XYMON $XYMSRV "data $MACHINE.db-cpu $(echo; cat $TEMPFILE; echo; echo "Ignore this")"
rm $CPU_tmp
rm $TEMPFILE $TEMPFILE.2

</code>
</hidden>
===== Known  Bugs and Issues =====
==The Numbers==
The numbers are not 100% accurate. They can't be. \\
I am getting the database numbers and the idle numbers at different times. Only seconds apart, but a lot can change in a second.\\
The database numbers are about as accurate as I can get them, and they are all collected at the same time, so relative to each other, they are accurate.\\
Accurate to 4 decimal places of a percent. But this means that anything using less than 0.0001% of the CPU will not be counted, and enough of these could add up.\\
That said, I think 4 decimal places should be OK. If not, maybe we can get Brendan Gregg to write a 6 decimal place version of his script.\\
I am also calculating the value for "other", and then fudging it a little when I need to.\\
The numbers are not 100% accurate!\\
However, within a 1% uncertainty range, I think it's good enough.\\

==Graphs==
I have provided 2 graph types. Line2 and stack.\\
The line2 graph doesn't really make sense if you have SHOW_IDLE=1 in the script.\\
The stack graph doesn't work with older versions on Xymon.\\
However, I would suggest you try the options, and see what works best for you.\\
Just remember, if you have SHOW_IDLE=1 and then change it, you will have to manually remove the "idle" and "other" rrd files from the server to prevent them from hanging about and potentially making your graphs look crappy.
<code>
rm ~/data/rrd/<$HOST>/db-cpu,Other.rrd
rm ~/data/rrd/<$HOST>/db-cpu,Idle.rrd
</code>

==Real Bugs==
The following bugs have been fixed.\\
  * Bug with grep causing additional processes to be included under specific conditions where a database instance name is a sub-string of another instance name. 
  * Top is not part of a standard Solaris install. Updated to use sar is top is not available.\\
  * Now it can handle the case of no databases running.\\
\\
At present I am unaware of any further bugs, but if you find any, let me know, and I will do my best to fix them.

===== To Do =====
Rest.\\
Where are my laurels?
===== Credits =====
Much of the credit for this must go to [[http://www.brendangregg.com/|Brendan Gregg]].\\
Without his magic, I would never have been able to create this script.\\
<Shameless plug>\\
Buy Brendan's books.
[[http://www.bookdepository.com/search?searchTerm=brendan+gregg&search=Find+book|Available here]]\\
</Shameless Plug>\\
\\
And some credit goes to a curious pair of DBAs who actually seem to care what their database is doing to the system. Or maybe they just love pretty graphs.\\
\\
Thanks to Andrey Chervonets for pointing out a possible issue with the grep command, which could cause a dramatic skewing of the CPU usage.\\
\\
Credit also to Nick Pettefar for reminding me that SMCtop is not part of a standard Solaris install, and is not available everywhere.
===== Changelog =====

  * **2013-03-01**
    * Initial release
  * **2013-03-07**
    * Updated grep to ensure databases are correctly identified when you have a instance name that is a sub-string of another instance name. e.g. testdb and testdb2
    * Updated to use sar if top is unavailable
  * **2013-04-23**
    * Updated to handle the case of no databases running. Previously, it just didn't bother doing anything, but now it will report there are no databases, and can trigger a colour change if there are no databases found.
  * **2014-02-27**
    * Updated to run with Solaris 11.
    * If top isn't available, (Solaris 10 and earlier) will use iostat to get idle stats.
    * Fixed a few display bugs.