#!/usr/bin/perl -w
# creator: NinGoo
# function: toolbox for check oracle sysstat
# usage: run on oracle server
# last modify: NinGoo 2009-12-09 create,v0.1
#              NinGoo 2009-12-10 add event for v$system_event information,v.02
#              NinGoo 2009-12-11 add v$sessstat info for single statistic,v0.3
#######################################################################################
use strict;
use DBI;
use Getopt::Std;
use vars qw($opt_i $opt_c $opt_n);
#######################################################################################
# catch ctrl+c to quit
$SIG{TERM}=$SIG{INT} = \&quit;

# set env var from shell profile
set_env();

# autoflush for print
$| = 1;

# global var
my %stat_last=();
my %stat_curr=();
my %stat_diff=();
my %wait_last=();
my %wait_curr=();
my %wait_diff=();
my %sess_last=();
my %sess_curr=();
my %sess_diff=();

# cmdline option var
my($interval,$count,$name);

# version
my $version='0.3.3';

###########################################
# main 
###########################################
# get cmdline options
&get_option();

# connect to database as sysdba via DBI
my $dbconn;
eval{
  local $SIG{ALRM} = sub { die "连接数据库超时\n" };
  alarm 20;
  $dbconn=DBI->connect("dbi:Oracle:",'','',{ora_session_mode=>2}) or die "Connect to oracle database error:". DBI->errstr;
  alarm 0;
};

if($@){
  print "connect to oracle database err:".$@."\n";
  exit;
}

# print copyright
print "-------------------------------------------------------------------------------\n";
print "-- tbstat v".$version." --- a tool for oracle system statistics and event.\n";
print "-- Powered by NinGoo.net\n";
print "-------------------------------------------------------------------------------\n";

# do loop
&do_loop();

# disconnect from oracle
$dbconn->disconnect;

###########################################
# print usage
###########################################
sub print_usage () {
    print <<EOF

 NAME:
    tbstat

 SYNTAX:
    tbstat -i interval -c count -n statname

 FUNCTION:
    Report sysstat information of oracle

 PARAMETER:
   -i  interval interval time,default 10 seconds
   -c  count    times
   -n  name     statistics name

EOF
}
#########################################################
# get options
#########################################################
sub get_option(){
  my $rtn = getopts('i:c:n:');
  unless ( "$rtn" eq "1" ) { print_usage(); exit 1;}

  $interval=$opt_i?$opt_i:10;
  $count=$opt_c?$opt_c+1:0;
  $name=$opt_n?$opt_n:'nothing';

  if($interval !~ /[0-9]/ || $count !~ /[0-9]/) { print_usage(); exit 1;}
  if(($ARGV[0] && $ARGV[0] !~ /[0-9]/) || ($ARGV[1] && $ARGV[1] !~ /[0-9]/)) { print_usage(); exit 1;}

  if($ARGV[0]){
    $interval=$ARGV[0];
  }
  if($ARGV[1]){
    $count=$ARGV[1]+1;
  }
  if($ARGV[2]){
  $name=$ARGV[2];
  }

  $name=lc($name);
}
########################################################
# main loop to get sysstat/sysevent
########################################################
sub do_loop{
  if($count){
    for(my $c=0;$c<$count;$c++){
      if($name eq 'event'){ 
        get_sysevent($c);
      }
      else{
        get_sysstat($c);
      }
      sleep $interval;
    }
  }
  else{ # if $count == 0 then loop time is unlimit
    for(my $c=0;;$c++){
      if($name eq 'event'){
        get_sysevent($c);
      }
      else{
        get_sysstat($c);
      }
      sleep $interval;
    }
  }
}
#########################################################
# set env from profile
#########################################################
sub set_env{
  my $user=`whoami`;
  chomp($user);
  my $profile="/home/".$user."/.profile";
  if (! -e $profile ){
    $profile="/home/".$user."/.bash_profile"
  }
  open(NEWENV, ". $profile && env|");
  while (<NEWENV>){
    if (/(\w+)=(.*)/){
      $ENV{$1}="$2";
    }
  }
  close NEWENV;
}
#######################################################
# catch ctrl+c
#######################################################
sub quit{
  print "\nexit...\n";
  $dbconn->disconnect;
  exit 1;
}
#######################################################
# get sysstat
#######################################################
sub get_sysstat{
  my ($cnt)=@_;
  my $sql;
  if ($name eq "all"){
    $sql="select /*+ tbstat */name,value from v\$sysstat";
  }
  elsif($name eq "nothing"){
    $sql="select /*+ tbstat */name,value from v\$sysstat where name in (
'CPU used by this session','CR blocks created','DBWR checkpoint buffers written','DBWR undo block writes','bytes received via SQL*Net from client','bytes sent via SQL*Net to client','rollbacks only - consistent read gets','consistent gets','db block changes','db block gets','enqueue requests','enqueue waits','execute count','index crx upgrade (positioned)','leaf node splits','leaf node 90-10 splits','logons cumulative','parse count (failures)','parse count (hard)','physical reads','physical reads cache prefetch','physical writes','redo size','sorts (memory)','sorts (disk)','table scans (long tables)','table scans (short tables)','transaction rollbacks','user commits','free buffer requested','index fast full scans (full)','redo synch time','redo synch writes','redo writes','redo write time','cleanouts only - consistent read gets'
)";
  }
  else{
    $sql="select /*+ tbstat */name,value from v\$sysstat where lower(name) like '%".$name."%'";
  }
  my $recs=$dbconn->selectall_arrayref($sql);

  if($cnt==0){
    my $tmp;
    foreach my $rec(@$recs){
      $stat_last{"$rec->[0]"}=$rec->[1];
      $tmp=$rec->[0];
    }

    if(scalar(@$recs) == 1){
      my $sql_sess="select /*+ tbstat */ss.sid,ss.value
                      from v\$sesstat ss,v\$statname sn
                     where ss.statistic#=sn.statistic#
                       and sn.name='".$tmp."'";

      my $rows=$dbconn->selectall_arrayref($sql_sess);
      foreach my $row(@$rows){
        $sess_last{"$row->[0]"}=$row->[1];
      }
    }
    print "\n";
  }
  else{
    my $tmp;
    foreach my $rec(@$recs){
      $stat_curr{"$rec->[0]"}=$rec->[1];
      $tmp=$rec->[0];
    }

    my $i=0;
    foreach my $key(sort(keys %stat_curr)){
      $stat_diff{"$key"}=($stat_curr{"$key"}-$stat_last{"$key"})/$interval; 
      $stat_last{"$key"}=$stat_curr{"$key"};

      if(($stat_diff{"$key"} != 0 && $stat_diff{"$key"} != -0  && $name eq "all") or ($name ne "all")){
        printf "%40s:%12.0f", $key,$stat_diff{"$key"};
        if ($i % 2 == 1){
          print "\n";
        }
        $i++;
      }
    }

    print "\n";

    if(scalar(@$recs) == 1){
      printf "\n%35s %12s %5s %20s %14s\n",'sid','value','%','machine','sql_id';
      printf "%35s %12s %5s %20s %14s\n",'----------','-----------','-----','-------------------','--------------';
      my $sql_sess="select /*+ tbstat */ ss.sid,ss.value,se.machine,se.sql_id
                     from v\$sesstat ss,v\$statname sn,v\$session se
                     where ss.statistic#=sn.statistic#
                      and ss.sid=se.sid
                      and sn.name='".$tmp."'";

      my %machine=();
      my %sqlid=();

      my $rows=$dbconn->selectall_arrayref($sql_sess);
      foreach my $row(@$rows){
        $sess_curr{"$row->[0]"}=$row->[1];
        $machine{"$row->[0]"}=$row->[2];
        if($row->[3]){
          $sqlid{"$row->[0]"}=$row->[3];
        }
        else{
          $sqlid{"$row->[0]"}=' ';
        }
      } 

      my $sum_value=0;
      foreach my $key(keys %sess_curr){
        if($sess_curr{"$key"} =~ /[0-9]/ && $sess_last{"$key"} && $sess_last{"$key"} =~ /[0-9]/){
          $sess_diff{"$key"}=($sess_curr{"$key"}-$sess_last{"$key"})/$interval;
          $sess_last{"$key"}=$sess_curr{"$key"};
          $sum_value+=$sess_diff{"$key"};
        }
      }

      my $n=0;
      foreach my $key(sort {$sess_diff{$b} <=> $sess_diff{$a}} keys %sess_diff){
        printf "%35s %12.0f %5.1f %20s %14s", $key,$sess_diff{"$key"},$sess_diff{"$key"}*100/(0.0001+$sum_value),$machine{"$key"},$sqlid{"$key"};
        print "\n";
        if($n>10){
         last;
        } 
        else{
         $n++;
        }
      }
    }

    print "------------------------------------------------------------------------------------------------------------\n\n";
  } 
}
#########################################################
# get system event information
#########################################################
sub get_sysevent{
  my ($cnt)=@_;

  my $sql="select /*+ tbstat */event,total_waits,round(time_waited_micro/1000,2) from v\$system_event
where event in ('db file parallel write','db file sequential read','log file parallel write','log file sequential read',
'log file sync','enq: TX - index contention','enq: TX - row lock contention','library cache lock','log buffer space',
'row cache lock','cursor: pin S wait on X','buffer busy waits','control file sequential read','db file parallel read',
'db file parallel write','enq: CF - contention','enq: HW - contention','enq: SQ - contention','enq: TX - allocate ITL entry',
'latch free','log file switch completion','db file scattered read','latch: session allocation','latch: cache buffers chains',
'LGWR wait for redo copy','control file parallel write','cursor: pin S','direct path read','direct path read temp',
'direct path write','direct path write temp','latch: library cache','undo segment extension','os thread startup',
'read by other session','latch: redo writing','SQL*Net more data from client','SQL*Net more data to client','Log archive I/O'
)";

  my $recs=$dbconn->selectall_arrayref($sql);
  if($cnt==0){
    foreach my $rec(@$recs){
      $stat_last{"$rec->[0]"}=$rec->[1];
      $wait_last{"$rec->[0]"}=$rec->[2];
    }
    print "\n";
  }
  else{
    foreach my $rec(@$recs){
      $stat_curr{"$rec->[0]"}=$rec->[1];
      $wait_curr{"$rec->[0]"}=$rec->[2];
    }

    my $i=0;
    printf "%35s:%8s %8s","Event Name","waits","time";
    printf "%35s:%8s %8s\n","Event Name","waits","time";
    printf "----------------------------------------------------------------------------------------------------------\n";
    foreach my $key(sort(keys %stat_curr)){
      $stat_diff{"$key"}=sprintf "%d",($stat_curr{"$key"}-$stat_last{"$key"})/$interval;
      $stat_last{"$key"}=$stat_curr{"$key"};

      if($stat_diff{"$key"} > 0){
        $wait_diff{"$key"}=($wait_curr{"$key"}-$wait_last{"$key"})/$stat_diff{"$key"}/$interval;
      }
      elsif($stat_diff{"$key"} < 0){
        $wait_diff{"$key"}=($wait_curr{"$key"}-$wait_last{"$key"})/(0-$stat_diff{"$key"})/$interval;
      }
      else{
        $wait_diff{"$key"}=0;
      }
      $wait_last{"$key"}=$wait_curr{"$key"};
      printf "%35s:%8d %8.2f", $key,$stat_diff{"$key"},$wait_diff{"$key"};
      if ($i % 2 == 1){
        print "\n";
      }
      $i++;
    }
    print "\n\n";
  }
}