#!/bin/sh
# Create on 8-Dec-2010 by Craig Shallahamer, craig@orapub.com
#
# The purpose of this script is to gather performance data to analyze differences in
# read call counts from three different sources; operating system process tracing,
# Oracle's wait interface, and Oracle's instance statistics.
#
# This works on Linux. For other operating systems, you may need to make
# some modifications. Because of the time and impact of gathering data, do not expect the
# results to be perfect. But they should be good enough to draw conclusions, especially with an
# interval of over a couple of minutes.
# 
# Use at your own risk!

# Inputs:
#	1. The OS pid of an Oracle process, probably a server process
#	2. The sample interval in seconds. 

tracepid=$1
interval=$2

# Usage example: $ ./gomain.sh 1234 5 
# Usage example: $ nohup ./gomain.sh 1234 900  >out.out 2>&1 &

# Oracle's on Linux uses the readv and pread64 system calls. Look near the end of this script.
# If your OS uses a different call, you will need to alter the associated code to correctly 
# capture the results.

# Make sure to correctly set the Oracle username and password.
orauid=system
orapsd=manager

outfile=./gomain.txt
tmpfile1=/tmp/bogus1.txt
tmpfile2=/tmp/bogus2.txt
tmpfile3=/tmp/bogus3.txt
rm -f $outfile $tmpfile1 $tmpfile2 $tmpfile3

# For testing I use just a few loops, but for the real experiment I like lots of samples!
#
for loop in 1 2 3 4 5 6
#for loop in 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0
do
  echo "==== Loop $loop ===="
  echo "Tracing pid $tracepid now in the background..."
  strace -rp $tracepid >$tmpfile1 2>&1 &

  sqlplus $orauid/$orapsd <<EOF
  set tab off
  col x new_val thesid noprint
  col y new_val total_waits_t0 noprint
  select s.sid x from v\$process p, v\$session s where p.addr = s.paddr and p.spid=$tracepid;
  drop table bogus1;
  create table bogus1 as select * from v\$sesstat where statistic#=44 and sid=&thesid;
  drop table bogus2;
  select sum(total_waits) y from v\$session_event where sid=&thesid and event in ('db file sequential read','db file scattered read','direct path read','direct path read temp','db file parallel read','control file sequential read','log file sequential read');
  exec dbms_lock.sleep($interval);
  spool $tmpfile2
  select 'stat44 '||(cur.value-prev.value)
  from v\$sesstat cur,
       bogus1 prev
  where cur.statistic#=44
    and cur.statistic#=prev.statistic#
    and cur.sid=prev.sid
    and cur.sid=&thesid;
  spool off
  spool $tmpfile3
  select ltrim(sum(total_waits)-&total_waits_t0) from v\$session_event where sid=&thesid and event in ('db file sequential read','db file scattered read','direct path read','direct path read temp','db file parallel read','control file sequential read','log file sequential read');
  spool off
EOF

  pid=`ps -eaf|grep strace | grep -v ps | grep -v grep | awk '{print $2}'`
  echo "Killing pid $pid"
  ps -eaf | grep $pid
  kill -2 $pid

  stracecnt=`cat $tmpfile1 | grep -e readv -e pread64 | wc -l`
  sesstatcnt=`cat $tmpfile2 | grep stat44 | grep -vi cur| awk '{print $2}'`
  waitcnt=`cat $tmpfile3 | tail -3 | head -1`
  echo "$loop $stracecnt $sesstatcnt $waitcnt"
  echo "$loop $stracecnt $sesstatcnt $waitcnt" >> $outfile

done

echo "=============================="
echo "sample stracecnt sesstatcnt waitcnt"
cat $outfile