SLOB2-Analyze.sh

slob ghost

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…

5 Responses to SLOB2-Analyze.sh

  1. oramoss says:

    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!

    • flashdba says:

      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.

  2. oramoss says:

    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

    • flashdba says:

      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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 842 other followers