Looking at Linux Utilization with sar and Oracle’s V$OSSTAT

By James Koopman on December 21, 2012


With the advent of the new V$OSSTAT view in Oracle, DBAs now have one more method of determining CPU utilization. This little view has, in my opinion has the following three benefits:

  1. DBAs don’t have to shell out to the operating system to execute system level command
  2. DBAs feel much better about querying a table; it’s what we do
  3. Scripting becomes much easier to integrate system statistics with database statistics

With the advent / proliferation of GUI interfaces to databases I often wonder how many DBAs ever get exposed to the system prompt. We spend a lot of our time in Oracle’s Enterprise Manager (OEM) or the latest vendor tool to help solve daily problems. Vendor tools are great, without them it would take us much longer to solve the daily issues that come up. But sometimes there just happens to be a feature, option, trick, or in this case a statistic that we just might want to get a look at in its rawest form.

CPU utilization has always been interesting to watch as processes consume system resources. The Unix utility sar has been the tool of choice ever since I can remember. Following is a snippet of running the sar command for CPU activity every 5 seconds for 60 times (5 minutes). During the execution of the sar command, sar writes to standard output (the screen) for the predefined counters—displaying the cumulative activity for the counter across the interval chosen.

[oracle@ludwig ~]$ sar -u 5 60

Linux 2.6.18-8.el5 (ludwig)     02/20/2009
04:21:22 PM       CPU     %user     %nice   %system   %iowait    %steal     %idle
04:21:27 PM       all          8.22        0.00      3.41             3.81      0.00         84.57
04:21:32 PM       all          5.01        0.00      2.40             4.81      0.00         87.78
04:21:37 PM       all          7.98        0.00      2.79             4.99      0.00         84.23
04:21:42 PM       all          33.33      0.00      3.39             4.59      0.00         58.68
04:21:47 PM       all          94.61      0.00      5.19             0.20      0.00           0.00
04:21:52 PM       all          88.38      0.00      5.61             3.21      0.00           2.81

In much the same way sar reports on CPU utilization we can invoke Oracle’s V$OSSTAT command to get similar results. Understanding that, like many Oracle internal statistics, the V$OSSTAT contains cumulated statistics we must impose a time interval on these statistics if we are to understand what is currently happening.

Below is a call, from the operating system, to a shell script called vosstat.sh. This script connects to an Oracle instance and execute a SQL script called vostat.sql. Together they are able to effectively subtract two statistical collections over a time interval and output sar-like information. The only difference is that while sar reports percentages vosstat.sh reports on hundredths of seconds. Parameters for this script are TNS entry, seconds to wait inside database between samples, seconds to wait in shell script, and number of times to run.

[oracle@ludwig ~]$ ./vosstat.sh db11FS 5 0 60

time                               CPU       user       nice     system     iowait       idle
20090220:16:21:24          1         129          0          8              13          362
20090220:16:21:29          1           16          0          3              18          482
20090220:16:21:34          1           31          0          4              28          467
20090220:16:21:41          1         131          0         13             23          357
20090220:16:21:52          1         429          0         26              23           41

Running scripts is futile unless we do something with them. For CPU utilization, it helps us zero in on high activity time frames—pinpointing where we need to spend our time tuning. Realizing also that times of low activity are time periods where we can move batch processing, reports, and even system downtime or maintenance to.

Monitor and use wisely.

#!/bin/bash
# ----------------------------------------------------------------------
# Script  : vosstat.sh
# Author  : James F. Koopmann
# Purpose : Report on CPU statistics through V$OSSTAT
# ----------------------------------------------------------------------
if [ $# != 4 ]
then
echo " "
echo " "
echo "Usage: ./vosstat.sh {ORACLE_SID} {sleep_in_db} {sleep_in_sh} {count}"
echo " "
echo "Where: ORACLE_SID  - Oracle TNS name for connection"
echo "       sleep_in_db - time to sleep between sample collections"
echo "       sleep_in_sh - time to sleep in shell before connecting to Oracle"
echo "       count       - number of times to sample database for CPU stats"
echo " "
echo "Notes: a count of 99 will cause the script to run without bounds"
echo " "
echo "       typical cron entry, to run for an hour and sample every 10 seconds:"
echo "       00 08 * * * vosstat.sh orcl 10 0 360 >> vosstat.lst"
echo " "
echo "       to run on the command line for 10 samples would be:"
echo "       ./vosstat.sh orcl 10 0 10"
echo " "
echo " "
exit 1
fi

export ORACLE_HOME=/opt/app/oracle/product/11.1.0/db_1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME:$PATH

export ORACLE_SID=$1
export SLEEPINDB=$2
export SLEEPINSH=$3
export COUNTDOWN=$4

echo "time                  CPU       user       nice     system     iowait       idle"

c=0
while [ "$c" -lt "$COUNTDOWN" ]
do
sqlplus -s sys/pwd@$ORACLE_SID as sysdba @vosstat.sql $SLEEPINDB
sleep $SLEEPINSH
((c=c+1))
if [ "$COUNTDOWN" -eq "99" ]
then
c=0
fi
done
--# ----------------------------------------------------------------------
--# Script  : vosstat.sql
--# Author  : James F. Koopmann
--# ----------------------------------------------------------------------
set echo     off
set feedback off
set heading  off
set linesize 40
set pagesize 55
set verify   off
column cpus1    new_value cpus1
column idle1    new_value idle1
column user1    new_value user1
column sys1     new_value sys1
column iowait1  new_value iowait1
column nice1    new_value nice1
set termout off
SELECT
sum(decode(stat_name,'NUM_CPUS',value,0))    cpus1,
sum(decode(stat_name,'IDLE_TIME',value,0))   idle1,
sum(decode(stat_name,'USER_TIME',value,0))   user1,
sum(decode(stat_name,'SYS_TIME',value,0))    sys1,
sum(decode(stat_name,'IOWAIT_TIME',value,0)) iowait1,
sum(decode(stat_name,'NICE_TIME',value,0))   nice1
FROM v$osstat;
set termout on
set termout off
column sleeptime  new_value sleeptime
SELECT &1 sleeptime from dual;
exec DBMS_LOCK.SLEEP (&&sleeptime);
set termout on
column cpus2    new_value cpus2
column idle2    new_value idle2
column user2    new_value user2
column sys2     new_value sys2
column iowait2  new_value iowait2
column nice2    new_value nice2
set termout off
SELECT
sum(decode(stat_name,'NUM_CPUS',value,0))    cpus2,
sum(decode(stat_name,'IDLE_TIME',value,0))   idle2,
sum(decode(stat_name,'USER_TIME',value,0))   user2,
sum(decode(stat_name,'SYS_TIME',value,0))    sys2,
sum(decode(stat_name,'IOWAIT_TIME',value,0)) iowait2,
sum(decode(stat_name,'NICE_TIME',value,0))   nice2
FROM v$osstat;
set termout on
column cpus    new_value cpus
column idle    new_value idle
column users   new_value users
column sys     new_value sys
column iowait  new_value iowait
column nice    new_value nice
column rpttime new_value rpttime
set termout off
SELECT
ROUND((&&cpus2),3) cpus,
ROUND((&&idle2-&&idle1),3) idle,
ROUND((&&user2-&&user1),3) users,
ROUND((&&sys2-&&sys1),3)   sys,
ROUND((&&iowait2-&&iowait1),3) iowait,
ROUND((&&nice2-&&nice1),3) nice,
to_char(sysdate,'YYYYMMDD:HH24:MI:SS') rpttime
FROM dual;
set termout on
prompt &&rpttime &&cpus &&users &&nice &&sys &&iowait &&idle
undefine sleeptime
exit

Related Posts

Leave a Reply