SLOB-Analyze.sh (Deprecated)

slob ghost

Note: This page covers SLOB version 1. SLOB2 is now available so rather than read this, please click here.

This page contains the SLOB-Analyze.sh shell script used to generate physical I/O with the SLOB benchmarking tool. For more information read this first.

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
# SLOB Analyze - script for analyzing multiple AWR reports to track latency and IOPS stats
# 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

ERRCNT=0

# 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"

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 ""
	exit 1
}

[[ $# -eq 0 ]] && usage

echocsv "Filename,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)"

while (( "$#" )); do
	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`
			# 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:]]*//')
			READ_LATENCY_u=`echo "scale=3; $READ_WAIT_TIME * 1000000 / $READ_NUM_WAITS" | bc -l`

			# 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:]]*//')
			WRITE_LATENCY_u=`echo "scale=3; $WRITE_WAIT_TIME * 1000000 / $WRITE_NUM_WAITS" | bc -l`

			# 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:]]*//')
			REDO_LATENCY_u=`echo "scale=3; $REDO_WAIT_TIME * 1000000 / $REDO_NUM_WAITS" | bc -l`

			# Print harvested information to errinf function
			echoinf "        Filename = $FILENAME"
			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"

			# Write values to CSV file
			echocsv "$FILENAME,$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"
		else
			echoerr "$1 is not an AWR file"
		fi
	else
		echoerr "Cannot read file $1 - ignoring..."
	fi
shift

done

echoinf "No more files found"

if [ $ERRCNT -gt 0 ]; then
	echoinf "Completed with $ERRCNT errors"
	exit 1
else
	echoinf "Completed successfully"
	exit 0
fi

The script is most easily called by supplying the names of the input files as a wildcard:

[oracle@server2 SLOB]$ ./slob-analyze.sh awr-OL6.3UEK-512sector-4kredo-slob-ro-0-00[12]*txt > slob.csv
Info : Analyzing file awr-OL6.3UEK-512sector-4kredo-slob-ro-0-001.txt
Info :         Filename = awr-OL6.3UEK-512sector-4kredo-slob-ro-0-001.txt
Info :        Read IOPS = 3404.7
Info :       Write IOPS = 4.3
Info :        Redo IOPS = 1.3
Info :       Total IOPS = 3410.3
Info :   Read Num Waits = 1185519
Info :   Read Wait Time = 297
Info :  Read Latency us = 250.523
Info :  Write Num Waits = 78
Info :  Write Wait Time = 0
Info : Write Latency us = 0
Info :   Redo Num Waits = 451
Info :   Redo Wait Time = 0
Info :  Redo Latency us = 0
Info : Analyzing file awr-OL6.3UEK-512sector-4kredo-slob-ro-0-002.txt
Info :         Filename = awr-OL6.3UEK-512sector-4kredo-slob-ro-0-002.txt
Info :        Read IOPS = 6602.2
Info :       Write IOPS = 2.1
Info :        Redo IOPS = 0.1
Info :       Total IOPS = 6604.4
Info :   Read Num Waits = 2503589
Info :   Read Wait Time = 641
Info :  Read Latency us = 256.032
Info :  Write Num Waits = 137
Info :  Write Wait Time = 0
Info : Write Latency us = 0
Info :   Redo Num Waits = 50
Info :   Redo Wait Time = 0
Info :  Redo Latency us = 0
Info : No more files found
Info : Completed successfully

The result is a CSV file which looks like this:

[oracle@half-server4 SLOB]$ cat slob.csv
Filename,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)
awr-OL6.3UEK-512sector-4kredo-slob-ro-0-001.txt,3404.7,4.3,1.3,3410.3,1185519,297,250.523,78,0,0,451,0,0
awr-OL6.3UEK-512sector-4kredo-slob-ro-0-002.txt,6602.2,2.1,0.1,6604.4,2503589,641,256.032,137,0,0,50,0,0

Clearly this particular one isn’t much to look at as a) it’s only two rows (mine usually have 47 rows each for read-only and read-write), and b) it’s much more interesting when you turn it into some sort of graph…

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