#!/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 $_ = ; # 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 {[]|30} {[]|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 " 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 " - 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 " - the number of snapshots to take\n"; print STDERR " defaults to 120 if omitted\n\n"; }