Processing Oracle I/O stats

#
# NOTE: have to download script dependency
./hist.pl
#
# IO THROUGHPOUT

#
#  dbid        date     time        bytes/sec      max bytes/sec
# 1323927447 2012/03/06 17:00        5673256      28648583
#
echo
echo "Read Throughput"
cat reads.lst | awk '
{sum+=($4);ct++}
END {
  printf("   MB per second %10d \n", ( (sum/ct) / (1024*1024) )  );
  printf("   hours         %10d \n",  ct );
  printf("   total MB      %10d \n", (sum*3600) /(1024*1024));
} '
echo

echo "Wait Time"
# Avg wait time
echo "" | awk '{printf("  %-25s %10s %12s %9s\n", "event","count","total sec", "avg ms") }' event="$i"
for i in "db file sequential read" "db file scattered read"  "log file parallel write" ; do
  #echo -n "average waits time::  $i"
  grep "$i" waits.lst |  awk '{sum+=$8;rows++;ct+=$7}END{printf("  %-25s %10d %12d %12.3f\n", event,ct,ct*(sum/rows)/1000,(sum/rows)) }' event="$i"
done
for i in "log file sync"  ; do
  #echo "average waits time::  $i"
  grep "$i" waits.lst |  awk '{sum+=$7;rows++;ct+=$6}END{printf("  %-25s %10d %12d %12.3f\n", event,ct,ct*(sum/rows)/1000,(sum/rows)) }' event="$i"
done
echo

echo "Hourly Average Waits"
# Avg wait time
for i in "db file sequential read" "db file scattered read"  "log file parallel write"  "log file sync"  ; do
  echo "hourly average waits time::  $i"
  echo ""  | awk '{printf("%10s %10s %10s %10s\n","date", "time", "count", "avg ms")}'
  grep "$i" waits.lst  | sed -e "s/  *$i  */ /"  | awk '{printf("%10s %10s %10d %7.3f\n",$1, $2, $3, $4)}'
done

echo
# WAIT HISTOGRAMS
#  1          2       3               4  5    6     7
# 2012/03/07,07:59,direct path read,7657,612,730160,8
echo "log file parallel write"
grep "log file parallel write" ash.lst |  awk '{print $7}' |  ./hist.pl
echo "db file sequential read"
grep "db file sequential read" ash.lst |  awk '{print $7}' | ./hist.pl
echo "db file scattered read"
grep "db file scattered read"  ash.lst |  awk '{print $8}' | ./hist.pl

Comments