#!/usr/bin/perl
#
# Copyright:	(c)1996-2007 Centrex Consulting Corporation
# Author:	Wolfgang Breitling
#
#------------------------------------------------------------------------------
#
# Based on 
#   The Session Snapper v1.06
#   (c) Tanel Poder ( http://www.tanelpoder.com )
#
#------------------------------------------------------------------------------
#

use DBI;
use DBD::Oracle;
use Time::HiRes qw( gettimeofday usleep );

# an associative array with 'username'=>'password' pairs. The username must be in uppercase
# if you (like I) don't like the idea of having the passwords in the script put it into a
# file in a secure place and pull that in with a require('filename_including_path');
# Or you can just leave the array empty and be prompted for the password
%pwd = ('SCOTT'=>'tiger');

if ($#ARGV < 0 ) {
	usage();
  exit;
  }

if ( $ARGV[0] =~ /^gather\=/i ) {	
	$option = shift;
  $option =~ tr/A-Z/a-z/;
  $option =~ /^.*\=(.*)$/;
	$lv_gather = $1;
	$lv_gather =~ tr/\,//d;
  }
else {
  $lv_gather = 'stw';
  }
if ( length($lv_gather) <= 0 ) {
	print "nothing to gather\n";
 	usage();
  exit;
  }

if ($#ARGV < 1 ) {
	usage();
  exit;
  }

if ($#ARGV < 2 ) { $interval = 30; } else { $interval = $ARGV[2]; }
if ($#ARGV < 3 ) { $repeat = 120; } else { $repeat = $ARGV[3]; }

($username,$db) = split("@",$ARGV[0]);
$db =~ tr/a-z/A-Z/;
$username =~ tr/a-z/A-Z/;	
$logon=$username . "@" . $db;
if ( exists($pwd{$username}) ) {
  $logon_pwd = $pwd{$username};
  }
else {
	print STDERR "Enter the password for $username: ";
  $| = 1;               # force a flush after our print
  $_ = <STDIN>;         # get the input from STDIN (presumably the keyboard)
  chomp;                # remove the newline character from the end of the input
	$logon_pwd = $_;
	}

if ( $username eq 'SYS' ) {
  $dbh = DBI->connect('dbi:Oracle:',"$logon","$logon_pwd",{ RaiseError => 1, AutoCommit => 0, ora_session_mode => 2 });
} else {
  $dbh = DBI->connect('dbi:Oracle:',"$logon","$logon_pwd",{ RaiseError => 1, AutoCommit => 0 });
}

if ( $ARGV[1] =~ /^SELECT / ) {
	$sql_sids = $dbh->prepare_cached($ARGV[1]);
	$sql_sids->execute;
	@sids=();
	while ( ($sid) = $sql_sids->fetchrow_array ) {
	  push @sid,$sid;
	  $sid_list = join(",",@sid);
    }
  }
else {
  $sid_list=$ARGV[1];
  }

%snap_names = ();
%snap_values = ();

$sql_time = $dbh->prepare_cached("select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate,'SSSSS') from dual");

@sql=("select sid,program,type from v\$session");
if ( $sid_list ne 'ALL' ) {
  push(@sql," where sid in (".$sid_list.")");
  }
push(@sql," order by 1");
$stmt=join(" ",@sql);
$sql_program = $dbh->prepare_cached($stmt);


$sql_version = $dbh->prepare("select substr(banner,instr(banner, 'Release ')+8,instr(banner,'.',1,1)-instr(banner, 'Release ')-8) from v\$version where rownum=1");
$sql_version->execute;
($oracle_version) = $sql_version->fetchrow_array;

$sql_count_stats = $dbh->prepare("select count(*) from v\$statname");
$sql_count_stats->execute;
($stats_count) = $sql_count_stats->fetchrow_array;

$sql_count_events = $dbh->prepare("select count(*) from v\$event_name");
$sql_count_events->execute;
($event_count) = $sql_count_events->fetchrow_array;

$sql_count_latches = $dbh->prepare("select count(*) from v\$latch");
$sql_count_latches->execute;
($latch_count) = $sql_count_latches->fetchrow_array;

$sql_count_enqueues = $dbh->prepare("select count(*) from v\$enqueue_stat");
$sql_count_enqueues->execute;
($enqueue_count) = $sql_count_enqueues->fetchrow_array;

$sql_statname = $dbh->prepare("select statistic#, name from v\$statname order by statistic#");
$sql_statname->execute;
while ( ($statistic, $name) = $sql_statname->fetchrow_array ) {
	$snap_names{$statistic} = $name;
  }

$sql_eventname = $dbh->prepare("select event# + $stats_count + 1, name from v\$event_name order by event#");
$sql_eventname->execute;
while ( ($statistic, $name) = $sql_eventname->fetchrow_array ) {
	$snap_names{$statistic} = $name;
  }

$sql_latchname = $dbh->prepare("select latch# + $stats_count + $event_count + 1, name from v\$latchname order by latch#");
$sql_latchname->execute;
while ( ($statistic, $name) = $sql_latchname->fetchrow_array ) {
	$snap_names{$statistic} = $name;
  }

@sql = ("select ascii(substr(eq_type,1,1))*256 + ascii(substr(eq_type,2,1))"
       ,"+ $stats_count + $latch_count + $event_count + 1 statistic#, eq_type from v\$enqueue_stat");
$stmt=join(" ",@sql);
$sql_enquename = $dbh->prepare_cached($stmt);
$sql_enquename->execute;
while ( ($statistic, $name) = $sql_enquename->fetchrow_array ) {
	$snap_names{$statistic} = $name;
  }

if ( $oracle_version > 9 ) {
  $sql_time_model = $dbh->prepare("select stat_id, stat_name from v\$sys_time_model order by stat_id");
  $sql_time_model->execute;
  while ( ($statistic, $name) = $sql_time_model->fetchrow_array ) {
	  $snap_names{$statistic} = $name;
    }
 }

@sql=();
$binds=0;
if ($lv_gather =~ /s/) {
  push(@sql,"select 'STAT' stype, sid, statistic#, value from v\$sesstat");
      if ( $sid_list ne 'ALL' ) {
        push(@sql," where sid in (".$sid_list.")");
        $binds++;
        }
  }

if ($lv_gather =~ /w/) {
  if ( $lv_gather =~ /s/ ) { push(@sql,"union all"); }
  push(@sql,"select 'WAIT', sw.sid, en.event# + $stats_count + 1,"
       ," nvl(se.time_waited_micro,0) + ( decode(se.event||sw.state, sw.event||'WAITING', sw.seconds_in_wait, 0) * 1000000 ) value"
       ," from v\$session_wait sw, v\$session_event se, v\$event_name en"
       ,"where sw.sid = se.sid and se.event = en.name");
  if ( $sid_list ne 'ALL' ) {
    push(@sql,"and se.sid in (".$sid_list.")");
    $binds++;
    }
  }

if ($lv_gather =~ /l/) {
  if ( $lv_gather =~ /s/ || $lv_gather =~ /w/ ) { push(@sql,"union all"); }
  push(@sql,"select ' LAT', s.sid, l.latch# + $stats_count + $event_count + 1, l.gets + l.immediate_gets value"
#      deliberate cartesian join
       ,"from v\$latch l, v\$session s");
  if ( $sid_list ne 'ALL' ) {
    push(@sql," where sid in (".$sid_list.")");
    $binds++;
    }
  }

if ($lv_gather =~ /e/) {
  if ( $lv_gather =~ /s/ || $lv_gather =~ /w/ || $lv_gather =~ /l/ ) { push(@sql,"union all"); }
  push(@sql,"select ' ENQ', s.sid, ascii(substr(e.eq_type,1,1))*256 + ascii(substr(e.eq_type,2,1))"
       ,"+ $stats_count + $event_count + $latch_count + 1 statistic#, e.total_req# value"
#      deliberate cartesian join
       ,"from v\$enqueue_stat e, v\$session s");
  if ( $sid_list ne 'ALL' ) {
    push(@sql," where sid in (".$sid_list.")");
    $binds++;
    }
  }

if ($lv_gather =~ /t/) {
	if ( $oracle_version > 9 ) {
    if ( $lv_gather =~ /s/ || $lv_gather =~ /w/ || $lv_gather =~ /l/ || $lv_gather =~ /e/ ) { push(@sql,"union all"); }
      push(@sql,"select 'TIME' stype, sid, stat_id, value from v\$sess_time_model");
      if ( $sid_list ne 'ALL' ) {
        push(@sql," where sid in (".$sid_list.")");
        $binds++;
        }
    }
  }

push(@sql,"order by 2,1,3");

$stmt=join(" ",@sql);
$sql_snap = $dbh->prepare_cached($stmt);

$sql_program->execute;
while ( ($sid,$program,$type) = $sql_program->fetchrow_array ) {
	print STDOUT sprintf("%7d %-48s %-10s\n",$sid,$program,$type);
  }

$sql_time->execute;
($dbtm,$prev_snap_time) = $sql_time->fetchrow_array;
($prev_sec, $prev_usec) = gettimeofday();
$sql_snap->execute;
while ( ($type,$sid,$name,$value) = $sql_snap->fetchrow_array ) {
  $snap_values{$name} = $value;
  }
usleep($interval*1000000);

print_head();
for ($c=1;$c<=$repeat;$c++) {
	($sec, $usec) = gettimeofday();
  $sql_time->execute;
  ($dbtm,$snap_time) = $sql_time->fetchrow_array;
  $sql_snap->execute;
  $prv_sid=-1;
  while ( ($type,$sid,$name,$value) = $sql_snap->fetchrow_array ) {
#    if ( $type eq 'STAT' && $value> 0 ) {print STDERR "$type\t$name\t$value\n";}
  	if ( $sid != $prv_sid ) { print "\n"; }
  	$prv_sid = $sid;
  	$difference = $value-$snap_values{$name};
    $snap_values{$name} = $value;
  	if ( $difference > 0 ) {
      print_line($dbtm,$sid,$type,$snap_names{$name},$difference,$sec-$prev_sec+($usec-$prev_usec)/1000000);
      }
    }

  print STDOUT "-- end of snap $c\n";
	($sec, $usec) = gettimeofday();
	$uint=($interval - $sec-$prev_sec+($usec-$prev_usec)/1000000)*1000000;
   $prev_sec = $sec;
	$prev_usec = $usec;
 usleep($interval*1000000);

  }

print STDOUT "\n";
$sql_program->execute;
while ( ($sid,$program,$type) = $sql_program->fetchrow_array ) {
	print STDOUT sprintf("%7d %-48s %-10s\n",$sid,$program,$type);
  }

sub print_head {
  print STDOUT sprintf("\n\t%-20s\t%7s\t%6s\t%4s\t%-55s\t%15s    \t%12s\n" 
    ,"Snapshot Start","Seconds","SID","Type","Statistic","Delta","Delta/sec");
  }

sub print_line {
	($dbtm,$sid,$type,$name,$value,$delta) = @_;
	if ( $type eq 'STAT' && $name =~ /CPU/ ) {
	  $value = $value*10;
    print STDOUT sprintf("\t%-20s\t%7.3f\t%6d\t%4s\t%-55s\t%15d (ms)\t%12.1f (ms)\n" 
      ,$dbtm,$delta,$sid,$type,$name,$value,$value/$delta);
   return;
    }
	if ( $type eq 'TIME' || $type eq 'WAIT' ) {
		$value = $value/1000;
    print STDOUT sprintf("\t%-20s\t%7.3f\t%6d\t%4s\t%-55s\t%15d (ms)\t%12.1f (ms)\n" 
      ,$dbtm,$delta,$sid,$type,$name,$value,$value/$delta);
    return;
    }
  print STDOUT sprintf("\t%-20s\t%7.3f\t%6d\t%4s\t%-55s\t%15d     \t%12.1f\n" 
    ,$dbtm,$delta,$sid,$type,$name,$value,$value/$delta);
}

sub usage {
  print STDERR " snapper.pl [gather=[s][t][w][l][e]] username\@db <sid(s)_to_snap> {[<seconds_in_snap>]|30} {[<snapshot_count>]|120}\n\n";
  print STDERR "    gather   - if omitted, gathers statistics \"s\", \"t\" and \"w\"\n";
  print STDERR "             - if specified, then gather following:\n";
  print STDERR "               s - Session Statistics from v\$sesstat\n";
  print STDERR "               t - Session Time model info from v\$sess_time_model\n";
  print STDERR "               w - Session Wait statistics from v\$session_event and v\$session_wait\n";
  print STDERR "               l - instance Latch get statistics ( gets + immediate_gets )\n";
  print STDERR "               e - instance Enqueue lock get statistics\n";
  print STDERR "         you can combine the above parameters in any order, separate them by commas\n";
  print STDERR "         but don't use spaces as otherwise they are treated as following parameters\n\n";
  print STDERR "    username\@db the username and database to logon to. The database is the sqlnet alias. It could be\n";
  print STDERR "         a full connect string following the sqlnet (tnsname.ora) coding rules with all spaces removed\n";
  print STDERR "         What about the password? It is retrieved from an associative array loaded from an external file.\n";
  print STDERR "         You need to change the path and filename of the \"require\" line in the script. See the comment on\n";
  print STDERR "         how to code the array.\n\n";
  print STDERR "    <sids_to_snap> can be either one sessionid, multiple sessionids separated by commas\n";
  print STDERR "         or a SQL statement (enclosed in double quotes) which returns a list of SIDs.\n";
  print STDERR "         If the SELECT is in all capitals then the select is converted to a list of sids\n";
  print STDERR "         and this constant (!) list of sids is used for all snaps,\n";
  print STDERR "         else the sql is executed as a subselect at each snap\n";
  print STDERR "         e.g. \"select sid from v\$session\" results in a sql like\n";
  print STDERR "         select ... where sid in (select sid from v\$session)\n";
  print STDERR "         whereas \"SELECT sid from v\$session\" results in a sql like\n";
  print STDERR "         select ... where sid in (nnn, mmm, ooo)\n";
  print STDERR "         in the first case the list is dynamic, growing and shrinking as sessions start or end \n";
  print STDERR "         in the second case the list is static, only to shrink if sessions end.\n\n";
  print STDERR "    <seconds_in_snap> - the number of seconds between taking snapshots. This script uses the HiRes\n";
  print STDERR "         module, included with Perl 5.8 and up, so the seconds can even be entered as fractional seconds\n";
  print STDERR "         defaults to 30 if omitted\n\n";
  print STDERR "    <snapshot_count>  - the number of snapshots to take\n";
  print STDERR "         defaults to 120 if omitted\n\n";	
}
