#!/usr/local/bin/perl
# $Source: /u2/gharriso/Src/RCS/ww.pl,v $
# $Author: gharriso $
# $Revision: 1.5 $
#
# Display wait info from session_event table
# bit rushed and it shows.... sorry!
#
#


#eval 'exec oraperl -S $0 ${1+"$@"}'
#	    if $running_under_some_shell;
#
#eval 'use Oraperl; 1' || die $@ if $] >= 5;
use Oraperl;

$SIG{'INT'}='int_handle';

sub main
{
  &init();			# Process command line, etc
  $n=0;
  while(1)			# "Forever"
    {
      $n++;
      &display_waits();
      if ($n>1)
	{
	  &print_log() if ($opt_l);
	  &save_to_db() if ($opt_D); # 
	}
      last if ($opt_n && $n==$opt_n);
      sleep($sleep_time);
    }
}

sub init			# 
{
  
  &set_format();
  
#
# No of rows to display
#
  $screen_size=18;
  
#
# Get options
#
  $usage="$0 -u username/password [-s(ilent)] [-l logfile]\n\t [-A (showall events)] [-n no of iterations] [-S econds between displays] \n";
  if ($#ARGV<0)
    {
      die $usage;
    }

  require "getopts.pl";
  &Getopts("u:sl:An:S:D:d")||die $usage;
  
  if ($opt_u)
    {
      $login=$opt_u;
    }
  else
    {
      if (defined($ENV{ORA_LOGIN}))
	{
	  $login=$ENV{ORA_LOGIN};
	}			# 
      else
	{
	  $login="/";
	}

    }


# Connect to ORACLE
#
  $lda=&ora_login("","$login","")||die $ora_errstr;

#
# Open the logfile if requested
#
  if ($opt_l)
    {
      open(LOG_FILE,">>$opt_l")||die $!;
      select LOG_FILE;
      $|=1;			# Unbuffered
    }

  #
  # Unwanted events.  Unless -A is specified,  do not display these 
  #
  if (! $opt_A)
    {
      $unwanted_events="'Null event','client message','smon timer',".
	"'rdbms ipc message','pmon timer','WMON goes to sleep','dispatcher timer','virtual circuit status'".
	  ",'SQL*Net message from client','parallel query dequeue wait'";
      $unwanted_clause="and event not in ($unwanted_events)";
    }


  if ($opt_S)
    {
      $sleep_time=$opt_S;
    }
  else
    {
      $sleep_time=30;
    }

    #
  # Connect to the "monitoring" database.  This is the one with a 
  # PERF_WAIT_STATS table in which the stats can be deposited
  #
  if ($opt_D)
    {
      $mlda=&ora_login("","$opt_D","")
	||die "Cant connect to monitoring db:\n$ora_errstr";
        if ($opt_d)
	  {
	    &ora_do($mlda,"alter session set sql_trace true")||
	      die $ora_errstr;
	  }
    }

  #
  # Instance name of the database being monitored
  #
  $instance_name=&get_instance($lda);


  #
  # Declare the main cursor
  #
  $ww_csr=&ora_open($lda,
                  "select event,total_waits,time_waited,
                          0
                     from sys.v_\$system_event
                    where 1=:1 $unwanted_clause
                    ")||die $ora_errstr;
  
}				

sub display_waits
{
  &ora_bind($ww_csr,1)||die $ora_errstr;

  #
  # Work out the time interval
  #
  $last_timestamp=$info_timestamp;
  $info_timestamp=time();
  if ($last_timestamp==0)
    {
      $interval=1;
    }
  else
    {
      $interval=$info_timestamp-$last_timestamp;
    }

  $tot_waits=0;
  $tot_wait_time=0;
  while(@ww_data=&ora_fetch($ww_csr))
    {
      $event=$ww_data[0];
      #
      # Compute this samples event total with last samples
      #
      $count{$event}=$ww_data[1]-$sum_count{$event};
      if ($count{$event} <0)
	{
	  # -ive value returned is this b/c the number wrapped?
	  system("echo \"-ive value: $event/$ww_data[1]/$sum_count{$event}\" >ERROR");
	}
      $sum{$event}=$ww_data[2]-$sum_sum{$event};
      $sum_count{$event}=$ww_data[1];
      $sum_sum{$event}=$ww_data[2];
      $distinct_sid{$event}=$ww_data[3]-$distinct_sid;
      $tot_waits+=$ww_data[1];
      $tot_wait_time+=$ww_data[2];
    }

  #
  # Calculate the overalls totals 
  #
  $this_tot_waits=$tot_waits;
  $this_wait_time=$tot_wait_time;
  $tot_waits-=$last_tot_waits;
  $tot_wait_time-=$last_wait_time;
  $last_tot_waits=$this_tot_waits;
  $last_wait_time=$this_wait_time;

  # Protect against divide by zero
  
  if ($tot_waits==0)
    {
      $tot_waits=1;
    }
  if ($tot_wait_time==0)
    {
      $tot_wait_time=1;
    }
  #
  # Sort the list
  #
  @event_list=keys %count;
  @event_list=sort by_wait_time @event_list;
  @event_list=reverse @event_list;

  #
  # Display the results
  # 
  system("tput clear") if (!$opt_s);
  $-=0;
  foreach $i (0..$#event_list)
    {
      last if ($i >= $screen_size);
      $event_name=$event_list[$i];
      $wait_counts=$count{$event_name}/$interval;
      $time_waited=$sum{$event_name}*100/$interval;
      $wait_pct=int($count{$event_name}*100/$tot_waits);
      $time_pct=int($sum{$event_name}*100/$tot_wait_time);
      $distinct_sids=$distinct_sid{$event_name};
      if ($count{$event_name}==0)
	{
	  $avg_wait=0;
	}
      else
	{
	  $avg_wait=$sum{$event_name}/$count{$event_name};
	}
      require "strftime.pl";
      $thistime=&strftime('%e/%m/%y:%H:%M:%S' ,time);
      write if (! $opt_s);
      
    }

}				# 

sub print_log
{

  local($now); 
  require "strftime.pl";
  $now=&strftime('%e/%m/%y:%H:%M:%S' ,time);

  select LOG_FILE;
  $|=1;				# unbuffered

  @temp=keys(%count);
  $this_count=$#temp;

  if ($n==2 ||			
      ($n>2 && $last_count < $this_count))   # In case a new stat is added
    {
      #
      # Setup the header
      #
      @event_list=(sort(keys(%count)));
      print $now;
      for $event (sort(@event_list))
	{
	  print ",".$event."(N)".",".
	    $event." (t),".$event." (N%),".$event." (t%)";
	}
      print "\n";
      
    }

  print $now;
  for $event_name (sort(@event_list))
    {
      print ",".$count{$event_name}/$interval||$!;
      print ",".$sum{$event_name}*100/$interval;
      print ",".int($count{$event_name}*100/$tot_waits);
      print ",".int($sum{$event_name}*100/$tot_wait_time);
    }
  print "\n";

  select STDOUT;

  $last_count=$this_count;
  
}

sub by_tot_waits
{
	#Sort by total waits
	$count{$a} <=> $count{$b};
}

sub by_wait_time
{
	#Sort by total waits
	$sum{$a} <=> $sum{$b};
}

sub int_handle
{
  local($sig)=@_;
  print "Exit $sig\n";
  exit(0);
}

sub set_format
{
    #  Display format
  
  
  format WW_TOP=
Instance: @<<<<<<<<<<<      @>>>>>>>>>>>>>>>>>> 
$instance_name,$thistime
ORACLE Wait stats  TOTAL:      @>>>>>>>>>>>>>>>     @>>>>>>>>>>>  intrvl: @<<<s
$tot_waits,$tot_wait_time,$interval

Event                          Waits/sec        PCT Time/sec*100  PCT Ave wait  
------------------------------ ---------------- --- ------------- --- -------- 
.
  format WW_ROW=
@<<<<<<<<<<<<<<<<<<<<<<<<<<<<< @############.## @>> @#########.## @>> @####.## 
$event_name,$wait_counts,$wait_pct,$time_waited,$time_pct,$avg_wait
.
  $^="WW_TOP";
  $~="WW_ROW";

}

sub save_to_db
{
  local($now); 
  require "strftime.pl";
  $now=&strftime('%e/%m/%y:%H:%M:%S' ,time);

  if (!defined($save_db_csr))
    {
      $save_db_csr=&ora_open($mlda,
			     "insert into perf_wait_stats
                                     (db_name,timestamp,stat_name,
                                      stat_value)
                              values (:1,to_date(:2,'DD/MM/YY:HH24:MI:SS'),
                                      :3,:4)")
	||die $ora_errstr;
    }
    for $event (keys(%count))
    {
      $count_delta=$count{$event}/$interval;
      $time_delta=$sum{$event}/$interval;
      $pct_n=($count{$event}*100/$tot_waits);
      $pct_time=($sum{$event}*100/$tot_wait_time);


	  &ora_bind($save_db_csr,
		    $instance_name,$now,$event."(t)",$time_delta)
	    ||warn $ora_errstr;
	  &ora_bind($save_db_csr,
		    $instance_name,$now,$event."(n)",$count_delta)
	    ||warn $ora_errstr;
	  &ora_bind($save_db_csr,
		    $instance_name,$now,$event."(%n)",$pct_n)
	    ||warn $ora_errstr;
	  &ora_bind($save_db_csr,
		    $instance_name,$now,$event."(%t)",$pct_time)
	    ||warn $ora_errstr;

    }
  &ora_commit($mlda)||warn $ora_errstr;
}  

sub get_instance
{
  #
  # Return the instance name for a given lda
  #
  local($lda)=@_;
  local($gi_csr)=&ora_open($lda,
			   'select name from sys.v_$database')
    ||die $ora_errstr;
  (($instance_name)=&ora_fetch($gi_csr))
    ||die $ora_errstr;
  &ora_close($gi_csr);
  return($instance_name);
}

&main();