#!/bin/sh
# file     : cpuutilcompare.sh  <interval (sec)>  <cycles>
# author   : Craig Shallahamer, craig@orapub.com
# copyright: (c)2011 OraPub, Inc.
# warrenty : No warrenty. Use at your own risk. OraPub is not responsible.
# created  : 20-April-2011
# updated  : 25-April-2011
# flavor   : Linux. It will need to be checked and probably modified for others.

# Purpose: This script was created to compare OS CPU utilization based
# on vmstat with the computed OS CPU utilization based on v$osstat. The
# v$osstat calculation is based on CPU cores not threads (of any kind).
# While the v$osstat calcuation usually matches very closely with
# vmstat, sometimes the v$osstat utilization calculation seems high, which
# means there are more effective cores than physical cores. Perhaps threads
# are providing additional CPU power? This is what this script is about.

#------------------------------------------
# Key Modifications you may need to make:
#------------------------------------------
 
# 1. You will need to modify the Oracle username (uid) and password
# (psd). You should be able to see those variables below near the 
# top of the actual script.

# 2. First do a, SELECT STAT_NAME,VALUE FROM V$OSSTAT and notice the CPU
# core-like stat name. On my box, the stat name is "NUM_CPUS" but on your
# it could be different.  If it is not clear, of all the core-like stats,
# use the one with the largest value that does not contain a thread-like
# word. The core-like value should obviously match the number of CPU cores
# on the database server. In this script, change all NUM_CPUS with the
# core-like stat name on your server. If you unsure, just email me.

# 3. Second, run a quick vmstat (vmstat 5 5) and notice the user and system
# time columns. They will be on the far right. Count the columns (going left
# to right) to identify the user time and system time columns. On Linux
# the columns are probably 13 and 14. If you look down this script for "usr="
# and "sys=" you can see the awk command being used to stript out the user and
# system time values. If the user and system time columns are not 13 and
# 14 on your server, then you will need to adjust the column values below.

# These are the only changes that should be needed. I display lots of
# logging details to help ensure everything is working properly. If
# something seems strange, just email me the log file (log.txt) and the
# final file (finalOut.txt) and I should be able to help.

#------------------------------------------
# Gathering the Data:
#------------------------------------------

# If you want, before you start collecting lots of samples I can check
# that everything is working. Just gather about 10, 30 second samples
# and email me the log file (log.txt) and the final output file (finalOut.txt).
# Here is how you can do this:

# $ ./cpuutilcompare.sh 30 10

# For the actual data collection, gathering 60 samples at a 15 minute
# interval would be fantastic. That will collect 15 hours of data, which should
# include both busy and not so busy data. This is what the command line syntax would
# look like:

# $ nohup ./cpuutilcompare.sh 900 60 1>./out.txt 2>&1 &

# Email me the log file (log.txt) and the final output file
# (finalOut.txt). I will email you the draft blog posting before I
# officially post it for you review.

# The actual script........

interval=$1
cycles=$2

uid=system
psd=manager

vmstatTmp=./vmstat.tmp; rm -f $vmstatTmp
log=./log.txt; rm -f $log
sqlTmp=./sql.tmp; rm -f $sqlTmp
finalOut=./finalOut.txt; rm -f $finalOut

small=0.000000010
loop=1

sqlplus -s $uid/$psd >/dev/null 2>&1 <<EOF
  spool $sqlTmp
  select (a.value/100) busy_time_sec, b.value cpu_cores
  from   ( select value from v\$osstat where stat_name='BUSY_TIME' ) a,
         ( select value from v\$osstat where stat_name='NUM_CPUS' ) b ;
EOF
osstatDetail=`cat $sqlTmp | tail -2 | head -1`
osstatBusyT1=`echo $osstatDetail | awk '{print $1}'`
osstatCoresT1=`echo $osstatDetail | awk '{print $2}'`
echo "  osstat T1 line: $osstatDetail" >> $log
echo "  osstat T1 line: $osstatDetail"
echo "  osstat T1 parsing detail: osstatBusyT1=$osstatBusyT1 osstatCoresT1=$osstatCoresT1" >> $log
echo "  osstat T1 parsing detail: osstatBusyT1=$osstatBusyT1 osstatCoresT1=$osstatCoresT1" 

while [ $loop -le $cycles ]
do
  echo "Begining loop $loop of $cycles at `date`" >>$log
  echo "Begining loop $loop of $cycles at `date`" 

  osstatBusyT0=$osstatBusyT1
  osstatCoresT0=$osstatCoresT1
  echo "  osstatBusyT0=$osstatBusyT0 osstatCoresT0=$osstatCoresT0" >> $log
  echo "  osstatBusyT0=$osstatBusyT0 osstatCoresT0=$osstatCoresT0" 

  vmstat $interval 2 1>$vmstatTmp 2>&1

  sqlplus -s $uid/$psd >/dev/null 2>&1 <<EOF
    spool $sqlTmp
    select (a.value/100) busy_time_sec, b.value cpu_cores
    from   ( select value from v\$osstat where stat_name='BUSY_TIME' ) a,
           ( select value from v\$osstat where stat_name='NUM_CPUS' ) b ;
EOF

  osstatDetail=`cat $sqlTmp | tail -2 | head -1`
  osstatBusyT1=`echo $osstatDetail | awk '{print $1}'`
  osstatCoresT1=`echo $osstatDetail | awk '{print $2}'`
  echo "  osstat T1 line: $osstatDetail" >> $log
  echo "  osstat T1 line: $osstatDetail"
  echo "  osstat T1 parsing detail: osstatBusyT1=$osstatBusyT1 osstatCoresT1=$osstatCoresT1" >> $log
  echo "  osstat T1 parsing detail: osstatBusyT1=$osstatBusyT1 osstatCoresT1=$osstatCoresT1" 

  osstatBusyDelta=`echo "scale=4;${osstatBusyT1}-${osstatBusyT0}"|bc`
  osstatCoresAvg=`echo  "scale=4;(${osstatCoresT1}+${osstatCoresT0})/2"|bc`

  osstatCapacity=`echo "scale=4;${osstatCoresAvg}*${interval}"|bc`
  osstatUtil=`echo "scale=4;${osstatBusyDelta}/${osstatCapacity}"|bc`

  echo "  osstat results: osstatBusyDelta=$osstatBusyDelta osstatCoresAvg=$osstatCoresAvg osstatCapacity=$osstatCapacity osstatUtil=$osstatUtil" >> $log
  echo "  osstat results: osstatBusyDelta=$osstatBusyDelta osstatCoresAvg=$osstatCoresAvg osstatCapacity=$osstatCapacity osstatUtil=$osstatUtil" 
  vmstatData=`tail -1 $vmstatTmp`
  usr=`echo $vmstatData | awk '{print $13}'`
  sys=`echo $vmstatData | awk '{print $14}'`
  vmstatUtil=`echo "scale=4;(${usr}+${sys})/100"|bc`
  echo "  vmstat line: $vmstatData" >>$log
  echo "  vmstat line: $vmstatData"
  echo "  vmstat parsing detail: usr=$usr sys=$sys vmstatUtil/100=$vmstatUtil" >>$log
  echo "  vmstat parsing detail: usr=$usr sys=$sys vmstatUtil/100=$vmstatUtil" 

  utilDiff=`echo "scale=4;${osstatUtil}-${vmstatUtil}"|bc`
  echo "  Error Util Calc: osstatUtil=$osstatUtil vmstatUtil=$vmstatUtil utilDiff=$utilDiff" >> $log
  echo "  Error Util Calc: osstatUtil=$osstatUtil vmstatUtil=$vmstatUtil utilDiff=$utilDiff"

  effectiveCores=`echo "scale=4;$osstatBusyDelta/($small+$interval*$vmstatUtil)"|bc`
  coreDiff=`echo "scale=4;${effectiveCores}-${osstatCoresAvg}"|bc`
  echo "  Error Core Calc: effectiveCores=$effectiveCores osstatCoresAvg=$osstatCoresAvg coreDiff=$coreDiff" >> $log 
  echo "  Error Core Calc: effectiveCores=$effectiveCores osstatCoresAvg=$osstatCoresAvg coreDiff=$coreDiff" 

  echo "FINAL: $loop $interval $osstatCoresAvg $osstatCapacity $osstatUtil $vmstatUtil $utilDiff $coreDiff" >> $finalOut
  echo "  FINAL: $loop $interval $osstatCoresAvg $osstatCapacity $osstatUtil $vmstatUtil $utilDiff $coreDiff" 

  loop=`expr ${loop} + 1`
done

echo ""
echo "FINAL: loop interval osstatCoresAvg osstatCapacity osstatUtil vmstatUtil utilDiff coreDiff" 
cat $finalOut