(This guest blog entry contributed by James Koopmann of Pinehorse, Inc., a well known database performance consultant, trainer and speaker, and a friend of Confio.)
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:
- DBAs don’t have to shell out to the operating system to execute system level command
- DBAs feel much better about querying a table; it’s what we do
- 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
|