#!/bin/sh # (c)2011 OraPub, Inc. # This shell script will take an Oracle 11.2 trace file # and having the trace file name and sqlid of interest, will # output the elapsed time for each execution of the sqlid. # This can be used to analyze the sqlid's elapsed times. # This was written for Linux and has been verified to work on Ora 11.2 trace files # Warrenty: There is none. Use at your own risk. # Author: Craig Shallahamer, craig@orapub.com 25-July-2011 # Version 4 : SQLID only and includes all time (p,e,f,close) # Usage: Enter the trace file and the sqlid below. Then execute this script. # trcfl=prod18_ora_21445.trc sqlid=5hy19uf6q4unx outfl=./sampledata.txt logfl=./samplelog.txt wafl=./wolframdata.txt tmp1=./temp_work_file1.txt tmp2=./temp_work_file2.txt rm -f $outfl rm -f $logfl rm -f $wafl rm -f $tmp1 rm -f $tmp2 cat $trcfl >> $logfl sample=0 line_no=0 while read line do echo -n "." line_no=`echo "$line_no+1"|bc` i=`echo $line | grep "PARSING IN CURSOR #" | grep -c $sqlid` if [ $i -eq "0" ] then echo "N: $line_no $line" >> $logfl else sample=`echo "$sample+1"|bc` start_line_num=$line_no echo "Y: $line_no : $line" >> $logfl read line echo -n "." line_no=`echo "$line_no+1"|bc` i=`echo $line | grep -c "CLOSE #"` while [ $i -eq "0" ] do read line echo -n "." line_no=`echo "$line_no+1"|bc` echo " $line_num Next: $line" >> $logfl i=`echo $line | grep -c "CLOSE #"` done end_line_num=$line_no lines=`echo "$end_line_num-$start_line_num" | bc` echo " line detail; start=$start_line_num end=$end_line_num lines=$lines " >> $logfl group=`head -$end_line_num $trcfl | tail -$lines ` echo "--------" >> $logfl echo "$group" >> $logfl echo "$group" > $tmp1 e=`cat $tmp1 | grep ",e=" | awk -F "," '{print $2}' | awk -F "=" '{print $2}' | awk '{s+=$1} END {print s}'` echo "--------" >> $logfl elapsed1=`echo "scale=6;$e/1000000"|bc` elapsed2=`echo "scale=3;$e/1000000"|bc` echo " e=$e elapsed1(s)=$elapsed1 elapsed2(s)=$elapsed2" >> $logfl echo "$sample,$elapsed1," >> $outfl fi done < $trcfl echo "" echo "--------------------------" cat $outfl | grep -v ",0," echo "--------------------------" cat $outfl | grep -v ",0," | awk -F "," '{print $2 ","}'