SLOB-Analyze.sh (Deprecated)
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…
flashdba 