SLOB2-Analyze.sh
This page contains the SLOB2-Analyze.sh shell script used to generate physical I/O with the SLOB benchmarking tool. For more information read this first.
This script is only designed to work on single-instance AWR reports. Jeff Moss (www.oramoss.com/blog) has posted a RAC-AWR version on his wiki here. He also helped me spot my unforgivable cut-and-pasting errors, for which I am grateful.
The tools and scripts found here are supplied for education purposes only and come with no warranty, either expressly or implied. Use at your own risk and please test thoroughly before use in a environment where you care about data and availability.
#!/bin/bash
# SLOB2 Analyze - script for analyzing AWR reports from SLOB2 Harness to track latency and IOPS stats
# Author: flashdba (http://flashdba.com)
# The output is a CSV file which can be used in spreadsheet software to create graphs
#
# For educational purposes only - no warranty is provided
# Test thoroughly - use at your own risk
# Counters
ERRCNT=0 # Number of errors experienced
FILECNT=0 # Number of files found
PROCCNT=0 # number of files processes
# Define search items from AWR text
AWR_READ_IOPS="physical read total IO requests"
AWR_WRITE_IOPS="physical write total IO requests"
AWR_REDO_IOPS="redo writes"
AWR_DBF_SEQREAD="db file sequential read"
AWR_DBF_PXWRITE="db file parallel write"
AWR_LOG_PXWRITE="log file parallel write"
# Some additional columns which, for now, have to be manually populated later on
INFO_VOLMANAGER="ASM" # ASM or Filesystem?
INFO_ASMLIB="Unknown" # Is ASMLib in use?
INFO_ASM_SECTOR_SIZE="Unknown" # The sector size of the ASM diskgroup
INFO_REDO_BLOCKSIZE="Unknown" # The blocksize of the database redo logs
INFO_STORAGE="Unknown" # Name of storage product
INFO_CONNECTIVITY="8GFC" # Fibre channel, Infiniband, iSCSI etc
INFO_STORAGE_PBA="4096" # Storage Physical Blocksize
INFO_STORAGE_LBA="Unknown" # Storage Logical Blocksize
echoerr() { echo "Error: $@" 1>&2; let ERRCNT++; }
echoinf() { echo "Info : $@" 1>&2; }
echocsv() { echo "$@"; }
usage() {
echo ""
echo "Usage: $0 <awr-filename.txt> (wildcards are accepted)"
echo ""
echo " Redirect stdout to a CSV file to import into a spreadheet"
echo " Errors and info are printed to stderr"
echo ""
echo " Example usage:"
echo " $0 awr*.txt > awr.csv"
echo ""
echo " For use with AWR reports generated using the SLOB2 Harness"
echo " Expects filenames to be in form awr.<update_pct>.<workers>.txt"
echo ""
exit 1
}
[[ $# -eq 0 ]] && usage
echocsv "Filename,Update Pct,Workers,Read IOPS,Write IOPS,Redo IOPS,Total IOPS,Read Num Waits,Read Wait Time (s),Read Latency (us),Write Num Waits,Write Wait Time (s),Write Latency (us),Redo Num Waits,Redo Wait Time (s),Redo Latency (us),CPUs,CPU Cores,CPU Sockets,Linux Version,Kernel Version,Processor Type,SLOB Run Time,SLOB Work Loop,SLOB Scale,SLOB Work Unit,SLOB Redo Stress,SLOB Shared Data Modulus,Volume Manager,ASMLib?,ASM Sector Size,DB Redo Blocksize,Storage,Connectivity,Storage Physical Blocksize,Storage Logical Blocksize"
while (( "$#" )); do
let FILECNT++
if [ -r "$1" ]; then
FILE=$1
echoinf "Analyzing file $FILE"
CHECK_HTML=$(sed -n -e '1,5 p' $FILE | grep "<HTML>" | wc -l)
CHECK_AWR=$(sed -n -e '1,5 p' $FILE | grep WORKLOAD | wc -l)
if [ $CHECK_HTML -gt "0" ]; then
echoerr "$1 is in HTML format - ignoring..."
elif [ $CHECK_AWR -gt "0" ]; then
# Strip any path from filename
FILENAME=`basename $FILE`
# Attempt to deduce UPDATE_PCT and WORKERS values from filename
TMPVAL=`echo $FILENAME | cut -d. -f2`
case $TMPVAL in
*[!0-9]*) echoinf "Cannot deduce UPDATE_PCT from filename $FILENAME, found $TMPVAL"
SLOB_UPDATE_PCT="Unknown"
;;
"") echoinf "Cannot deduce UPDATE_PCT from filename $FILENAME"
SLOB_UPDATE_PCT="Unknown"
;;
*) SLOB_UPDATE_PCT=$TMPVAL
;;
esac
TMPVAL=`echo $FILENAME | cut -d. -f3`
case $TMPVAL in
*[!0-9]*) echoinf "Cannot deduce SLOB_WORKERS from filename $FILENAME, found $TMPVAL"
SLOB_WORKERS="Unknown"
;;
"") echoinf "Cannot deduce SLOB_WORKERS from filename $FILENAME"
SLOB_WORKERS="Unknown"
;;
*) SLOB_WORKERS=$TMPVAL
;;
esac
# Grab IOPS values and calculate total IOPS
READ_IOPS=$(sed -n -e '/Instance Activity Stats/,/Tablespace/p' $FILE | grep "$AWR_READ_IOPS"| cut -c53-66|sed -e 's/,//g'|sed -e 's/^[[:space:]]*//')
WRITE_IOPS=$(sed -n -e '/Instance Activity Stats/,/Tablespace/p' $FILE | grep "$AWR_WRITE_IOPS"| cut -c53-66|sed -e 's/,//g'|sed -e 's/^[[:space:]]*//')
REDO_IOPS=$(sed -n -e '/Instance Activity Stats/,/Tablespace/p' $FILE | grep "$AWR_REDO_IOPS"| cut -c53-66|sed -e 's/,//g'|sed -e 's/^[[:space:]]*//')
TOTAL_IOPS=`echo "$READ_IOPS + $WRITE_IOPS + $REDO_IOPS" | bc -l`
# Work out average latency from DB FILE SEQUENTIAL READ wait events and convert to microseconds (us)
READ_NUM_WAITS=$(sed -n -e '/Foreground Wait Events/,/Background Wait Events/p' $FILE |grep "$AWR_DBF_SEQREAD" |cut -c29-39|sed -e 's/,//g'|sed -e 's/^[[:space:]]*//')
READ_WAIT_TIME=$(sed -n -e '/Foreground Wait Events/,/Background Wait Events/p' $FILE |grep "$AWR_DBF_SEQREAD" |cut -c47-56|sed -e 's/,//g'|sed -e 's/^[[:space:]]*//')
if [ -z "$READ_NUM_WAITS" ]; then
READ_NUM_WAITS=0
READ_LATENCY_u=""
elif [ "$READ_NUM_WAITS" -gt 0 ]; then
READ_LATENCY_u=`echo "scale=3; $READ_WAIT_TIME * 1000000 / $READ_NUM_WAITS" | bc -l`
else
READ_LATENCY_u=""
fi
# Work out average latency from DB FILE PARALLEL WRITE wait events and convert to microseconds (us)
WRITE_NUM_WAITS=$(sed -n -e '/Background Wait Events/,/Wait Event Histogram/p' $FILE |grep "$AWR_DBF_PXWRITE" |cut -c29-39|sed -e 's/,//g'|sed -e 's/^[[:space:]]*//')
WRITE_WAIT_TIME=$(sed -n -e '/Background Wait Events/,/Wait Event Histogram/p' $FILE |grep "$AWR_DBF_PXWRITE" |cut -c47-56|sed -e 's/,//g'|sed -e 's/^[[:space:]]*//')
if [ -z "$WRITE_NUM_WAITS" ]; then
WRITE_NUM_WAITS=0
WRITE_LATENCY_u=""
elif [ "$WRITE_NUM_WAITS" -gt 0 ]; then
WRITE_LATENCY_u=`echo "scale=3; $WRITE_WAIT_TIME * 1000000 / $WRITE_NUM_WAITS" | bc -l`
else
WRITE_LATENCY_u=""
fi
# Work out average latency from LOG FILE PARALLEL WRITE wait events and convert to microseconds (us)
REDO_NUM_WAITS=$(sed -n -e '/Background Wait Events/,/Wait Event Histogram/p' $FILE |grep "$AWR_LOG_PXWRITE" |cut -c29-39|sed -e 's/,//g'|sed -e 's/^[[:space:]]*//')
REDO_WAIT_TIME=$(sed -n -e '/Background Wait Events/,/Wait Event Histogram/p' $FILE |grep "$AWR_LOG_PXWRITE" |cut -c47-56|sed -e 's/,//g'|sed -e 's/^[[:space:]]*//')
if [ -z "$REDO_NUM_WAITS" ]; then
REDO_NUM_WAITS=0
REDO_LATENCY_u=""
elif [ "$REDO_NUM_WAITS" -gt 0 ]; then
REDO_LATENCY_u=`echo "scale=3; $REDO_WAIT_TIME * 1000000 / $REDO_NUM_WAITS" | bc -l`
else
REDO_LATENCY_u=""
fi
# Get CPU information from AWR report
AWR_NUM_CPUS=$(sed -n -e '/^NUM_CPUS/p' $FILE |cut -c27-48|sed -e 's/,//g'|sed -e 's/^[[:space:]]*//')
AWR_NUM_CPU_CORES=$(sed -n -e '/^NUM_CPU_CORES/p' $FILE |cut -c27-48|sed -e 's/,//g'|sed -e 's/^[[:space:]]*//')
AWR_NUM_CPU_SOCKETS=$(sed -n -e '/^NUM_CPU_SOCKETS/p' $FILE |cut -c27-48|sed -e 's/,//g'|sed -e 's/^[[:space:]]*//')
# SLOB2 Harness has the option to print an infostamp to the end of each AWR file
# Test for the existence of this infostamp and parse if found
AWR_INFOSTAMP=$(sed -n -e '/======================================/,$p' $FILE)
if [ `echo -n "$AWR_INFOSTAMP" | wc -l` -gt 0 ]; then
# Infostamp exists
INFO_LINUX=$(echo -n "$AWR_INFOSTAMP" | grep "^LINUX=" | cut -d= -f2)
INFO_KERNEL=$(echo -n "$AWR_INFOSTAMP" | grep "^KERNEL=" | cut -d= -f2)
INFO_PROCESSOR=$(echo -n "$AWR_INFOSTAMP" | grep "^PROCESSOR=" | cut -d= -f2)
INFO_RUN_TIME=$(echo -n "$AWR_INFOSTAMP" | grep "^SLOB RUN_TIME=" | cut -d= -f2)
INFO_WORK_LOOP=$(echo -n "$AWR_INFOSTAMP" | grep "^SLOB WORK_LOOP=" | cut -d= -f2)
INFO_SCALE=$(echo -n "$AWR_INFOSTAMP" | grep "^SLOB SCALE=" | cut -d= -f2)
INFO_WORK_UNIT=$(echo -n "$AWR_INFOSTAMP" | grep "^SLOB WORK_UNIT=" | cut -d= -f2)
INFO_REDO_STRESS=$(echo -n "$AWR_INFOSTAMP" | grep "^SLOB REDO_STRESS=" | cut -d= -f2)
INFO_SHARED_DATA_MODULUS=$(echo -n "$AWR_INFOSTAMP" | grep "^SLOB SHARED_DATA_MODULUS=" | cut -d= -f2)
else
INFO_LINUX="Unknown"
INFO_KERNEL="Unknown"
INFO_PROCESSOR="Unknown"
INFO_RUN_TIME="Unknown"
INFO_WORK_LOOP="Unknown"
INFO_SCALE="Unknown"
INFO_WORK_UNIT="Unknown"
INFO_REDO_STRESS="Unknown"
INFO_SHARED_DATA_MODULUS="Unknown"
fi
# Print harvested information to errinf function
echoinf " Filename = $FILENAME"
echoinf " Update Pct = $SLOB_UPDATE_PCT"
echoinf " Workers = $SLOB_WORKERS"
echoinf " Read IOPS = $READ_IOPS"
echoinf " Write IOPS = $WRITE_IOPS"
echoinf " Redo IOPS = $REDO_IOPS"
echoinf " Total IOPS = $TOTAL_IOPS"
echoinf " Read Num Waits = $READ_NUM_WAITS"
echoinf " Read Wait Time = $READ_WAIT_TIME"
echoinf " Read Latency us = $READ_LATENCY_u"
echoinf " Write Num Waits = $WRITE_NUM_WAITS"
echoinf " Write Wait Time = $WRITE_WAIT_TIME"
echoinf " Write Latency us = $WRITE_LATENCY_u"
echoinf " Redo Num Waits = $REDO_NUM_WAITS"
echoinf " Redo Wait Time = $REDO_WAIT_TIME"
echoinf " Redo Latency us = $REDO_LATENCY_u"
echoinf " Num CPUs = $AWR_NUM_CPUS"
echoinf " Num CPU Cores = $AWR_NUM_CPU_CORES"
echoinf " Num CPU Sockets = $AWR_NUM_CPU_SOCKETS"
echoinf " Linux Version = $INFO_LINUX"
echoinf " Kernel Version = $INFO_KERNEL"
echoinf " Processor Type = $INFO_PROCESSOR"
echoinf " SLOB Run Time = $INFO_RUN_TIME"
echoinf " SLOB Work Loop = $INFO_WORK_LOOP"
echoinf " SLOB Scale = $INFO_SCALE"
echoinf " SLOB Work Unit = $INFO_WORK_UNIT"
echoinf " SLOB Redo Stress = $INFO_REDO_STRESS"
echoinf "SLOB Shared Data Mod = $INFO_SHARED_DATA_MODULUS"
# Write values to CSV file
echocsv "$FILENAME,$SLOB_UPDATE_PCT,$SLOB_WORKERS,$READ_IOPS,$WRITE_IOPS,$REDO_IOPS,$TOTAL_IOPS,$READ_NUM_WAITS,$READ_WAIT_TIME,$READ_LATENCY_u,$WRITE_NUM_WAITS,$WRITE_WAIT_TIME,$WRITE_LATENCY_u,$REDO_NUM_WAITS,$REDO_WAIT_TIME,$REDO_LATENCY_u,$AWR_NUM_CPUS,$AWR_NUM_CPU_CORES,$AWR_NUM_CPU_SOCKETS,$INFO_LINUX,$INFO_KERNEL,$INFO_PROCESSOR,$INFO_RUN_TIME,$INFO_WORK_LOOP,$INFO_SCALE,$INFO_WORK_UNIT,$INFO_REDO_STRESS,$INFO_SHARED_DATA_MODULUS,$INFO_VOLMANAGER,$INFO_ASMLIB,$INFO_ASM_SECTOR_SIZE,$INFO_REDO_BLOCKSIZE,$INFO_STORAGE,$INFO_CONNECTIVITY,$INFO_STORAGE_PBA,$INFO_STORAGE_LBA"
let PROCCNT++
else
echoerr "$1 is not an AWR file"
fi
else
echoerr "Cannot read file $1 - ignoring..."
fi
shift
done
echoinf "No more files found"
echoinf "============================="
echoinf " AWR Files Found = $FILECNT"
echoinf " AWR Files Processed = $PROCCNT"
echoinf " Errors Experienced = $ERRCNT"
echoinf "============================="
if [ $ERRCNT -gt 0 ]; then
exit 1
else
exit 0
fi
#EOF
The script is most easily called by supplying the names of the input files as a wildcard:
[oracle@server1 SLOB2]$ ./slob2-analyze.sh awr.*.*/awr.*.*.txt > slob.csv Info : Analyzing file awr.0.001/awr.0.001.txt Info : Filename = awr.0.001.txt Info : Update Pct = 0 Info : Workers = 001 Info : Read IOPS = 3993.3 Info : Write IOPS = 1.5 Info : Redo IOPS = 0.4 Info : Total IOPS = 3995.2 Info : Read Num Waits = 560 Info : Read Wait Time = 0 Info : Read Latency us = 0 Info : Write Num Waits = 0 Info : Write Wait Time = Info : Write Latency us = Info : Redo Num Waits = 34 Info : Redo Wait Time = 0 Info : Redo Latency us = Info : Num CPUs = 32 Info : Num CPU Cores = 16 Info : Num CPU Sockets = 2 Info : Linux Version = Oracle Linux Server release 6.3 Info : Kernel Version = 2.6.39-400.21.1.el6uek.x86_64 Info : Processor Type = Intel(R) Xeon(R) CPU E5-2470 0 @ 2.30GHz Info : SLOB Run Time = 30 Info : SLOB Work Loop = 0 Info : SLOB Scale = 10000 Info : SLOB Work Unit = 256 Info : SLOB Redo Stress = HEAVY Info : SLOB Shared Data Mod = 0 ... output truncated ... Info : No more files found Info : ============================= Info : AWR Files Found = 6 Info : AWR Files Processed = 6 Info : Errors Experienced = 0 Info : =============================
The result is a CSV file which looks like this:
[oracle@server1 SLOB2]$ cat slob.csv Filename,Update Pct,Workers,Read IOPS,Write IOPS,Redo IOPS,Total IOPS,Read Num Waits,Read Wait Time (s),Read Latency (us),Write Num Waits,Write Wait Time (s),Write Latency (us),Redo Num Waits,Redo Wait Time (s),Redo Latency (us),CPUs,CPU Cores,CPU Sockets,Linux Version,Kernel Version,Processor Type,SLOB Run Time,SLOB Work Loop,SLOB Scale,SLOB Work Unit,SLOB Redo Stress,SLOB Shared Data Modulus,Volume Manager,ASMLib?,ASM Sector Size,DB Redo Blocksize,Storage,Connectivity,Storage Physical Blocksize,Storage Logical Blocksize awr.0.001.txt,0,001,3993.3,1.5,0.4,3995.2,560,0,0,0,,,34,0,,32,16,2,Oracle Linux Server release 6.3,2.6.39-400.21.1.el6uek.x86_64, Intel(R) Xeon(R) CPU E5-2470 0 @ 2.30GHz,30,0,10000,256,HEAVY,0,ASM,Unknown,Unknown,Unknown,Unknown,8GFC,4096,Unknown awr.0.002.txt,0,002,3806.0,2.1,0.4,3808.5,608,0,0,0,,,35,0,,32,16,2,Oracle Linux Server release 6.3,2.6.39-400.21.1.el6uek.x86_64, Intel(R) Xeon(R) CPU E5-2470 0 @ 2.30GHz,30,0,10000,256,HEAVY,0,ASM,Unknown,Unknown,Unknown,Unknown,8GFC,4096,Unknown awr.0.003.txt,0,003,3760.8,1.6,0.5,3762.9,578,0,0,0,,,38,0,,32,16,2,Oracle Linux Server release 6.3,2.6.39-400.21.1.el6uek.x86_64, Intel(R) Xeon(R) CPU E5-2470 0 @ 2.30GHz,30,0,10000,256,HEAVY,0,ASM,Unknown,Unknown,Unknown,Unknown,8GFC,4096,Unknown awr.10.001.txt,10,001,3919.2,603.7,94.1,4617.0,23,0,0,18,0,0,7635,1,130.975,32,16,2,Oracle Linux Server release 6.3,2.6.39-400.21.1.el6uek.x86_64, Intel(R) Xeon(R) CPU E5-2470 0 @ 2.30GHz,30,0,10000,256,HEAVY,0,ASM,Unknown,Unknown,Unknown,Unknown,8GFC,4096,Unknown awr.10.002.txt,10,002,3719.1,1145.7,96.4,4961.2,0,,,60,0,0,8342,2,239.750,32,16,2,Oracle Linux Server release 6.3,2.6.39-400.21.1.el6uek.x86_64, Intel(R) Xeon(R) CPU E5-2470 0 @ 2.30GHz,30,0,10000,256,HEAVY,0,ASM,Unknown,Unknown,Unknown,Unknown,8GFC,4096,Unknown awr.10.003.txt,10,003,3740.0,2315.1,121.7,6176.8,7,0,0,89,0,0,10464,3,286.697,32,16,2,Oracle Linux Server release 6.3,2.6.39-400.21.1.el6uek.x86_64, Intel(R) Xeon(R) CPU E5-2470 0 @ 2.30GHz,30,0,10000,256,HEAVY,0,ASM,Unknown,Unknown,Unknown,Unknown,8GFC,4096,Unknown
Clearly this particular one isn’t much to look at as a) it’s only six rows (my real runs usually have 150 rows per test), and b) it’s much more interesting when you turn it into some sort of graph…
flashdba 
Hi – great scripts, thanks.
I got an error…for some reason, in my AWR report, there was a piece of SQL with the text “NUM_CPU” in it on just one of my runs…which then breaks the sed pattern matching stuff that calculates NUM_CPUS etc..
So, I changed examples of:
sed -n -e ‘/NUM_CPUS/p’ $FILE
…to this…
sed -n -e ‘/^NUM_CPUS/p’ $FILE
i.e. added the ^ character before NUM….this seemed to work as it disregarded the additional occurrences of NUM_CPU in the file and the output CSV was as it should be.
Cheers
Jeff
PS – Mr Till is working alongside me here – glad to have him back!
Hi Jeff – and thanks for the correction. You’re right – I’ve added the new line “^” character to all three of those lines now, that ought to stop a few odd errors.
I guess the problem with using pattern matching is that the pattern you’re looking for might unexpectedly crop up elsewhere. Nowhere is that more the case than in the SQL section of the AWR report. I’ve mentioned elsewhere on here that this analyze script is a low fat version of the full extravaganza deluxe AWR parsing script I wrote for Violin – but unfortunately I cannot share that as it’s Violin IP. But in that version I parse each separate section of the AWR so that I know what sort of data will be present – therefore avoiding most issues like this.
You never know though – there’s always something nasty lurking in an AWR report!
Say hello to my good friend Mr Till. I believe that his return is all part of his master plan to get back to the place where his heart truly belongs… i.e. working with his beloved electricity meters.
Hi
Sorry, this really sounds like I’m picking on your scripts and I’m truly not because I like them a lot and I use them…
Firstly, I think you have a cut/paste typo…Working out redo latency, your “if” test refers to WRITE_NUM_WAITS not REDO_NUM_WAITS…which is throwing an error when I run things sometimes.
I’ve also realised that the scripts are only for the the single instance AWR output files. When I tried to use the RAC ones, after modifying SLOB runit.sh to call the AWR Global report, it obviously doesn’t find things where they are meant to be, so I’ve modified them to do that now….available on my wiki if you want it…http://www.oramoss.com/wiki/index.php?title=slob2_analyze_rac
Terrible cut and paste errors. You’re going to find this hard to believe but when I went to update my repository I found that I’d already fixed the mistaken use of WRITE_NUM_WAITS. But it seems I didn’t then remember to update the blog. I guess that’s the problem with having copies in multiple locations.
Thanks for updating. I’ve added a link to your RAC-enabled version at the top of this page.
Thanks, no worries…I’ve added a post to my blog about this.