# file    : getparsetime.sh
# last    : 9-Jul-2012
# author  : Craig Shallahamer, craig@orapub.com
# warrenty: Absolutely no warrenty or implied usefullness. Use at your own risk.

# This file will, when used in conjunction with getparsetime.pl, will
# create unique Oracle sql statements, run them while sql tracing them,
# extract the parse time, and load the times into the op_results table.

rm -i ./op_results.sql
chill=4

for loops in 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 27 29 30 
do
  echo ""
  echo "Running statement $1 seven times while chilling for $chill seconds..."
  echo ""
sqlplus system/manager <<EOF
  set serveroutput on
  spool /tmp/bogus.tmp
  exec makeSqlStmtLong;
  spool off
  !cat /tmp/bogus.tmp | grep select > /tmp/sqltext.tmp

  -- If you don't flush lots of optimizer stuff must be brought into the cache
  -- which I think is a little unfair to include this.
  -- alter system flush shared_pool;

  exec dbms_lock.sleep($chill);
  alter session set sql_trace true;
  start /tmp/sqltext.tmp
  start /tmp/sqltext.tmp
  start /tmp/sqltext.tmp
  start /tmp/sqltext.tmp
  start /tmp/sqltext.tmp
  start /tmp/sqltext.tmp
  start /tmp/sqltext.tmp
  alter session set sql_trace false;
  spool /tmp/tfilename.tmp
  select getmytracefile from dual;
  spool off
EOF

  trcfile=`cat /tmp/tfilename.tmp | grep trc`
  perl ./getparsetime.pl 100 $trcfile >/dev/null
done

cat ./op_results.sql

echo ""
echo ""
echo "In five seconds the op_results table will be deleted and reloaded..."
sleep 5
sqlplus system/manager <<EOF
delete from op_results;
start ./op_results.sql
select count(*) from op_results;
EOF

echo ""
echo "The experiment has completed"
date