#!/usr/local/bin/perl
# $Source: /acat4/users/gharriso/Src/RCS/latchmon.pl,v $
# $Author: gharriso $
# $Revision: 1.1 $
#
# simple latch monitor
#
#


#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();
  while (1)
    {
      $n++;
      &mon_latch();
      if ($opt_l && $n > 1)
	{
	  &print_log();
	}
      last if ($opt_n && $n==$opt_n); 
      sleep($sleep_time);
    }
}

sub format
{
# Monitor latches
format LM_TOP=
 ORACLE Latch activity : @<<<<<<<<<<
$instance_name
                 Totals: @>>>>>>>>>>     @>>>>>>>>> @>>
$tot_gets,$tot_misses,int((($tot_gets-$tot_misses)/$tot_gets)*100)
                                                             Avg   Spin
Latch                           Gets PCT     Misses PCT Hit% Sleep Get%
------------------------ ----------- --- ---------- --- ---- ----- ----
.
format LM_ROW=
@<<<<<<<<<<<<<<<<<<<<<<< @#######.## @>> @######.## @>>  @>> @#.## @###
$latch_name,$gets_,int(($gets{$latch_name}/$tot_gets)*100),$misses_,int(($misses{$latch_name}/$tot_misses)*100),$hit_rate,$avg_sleeps{$latch_name},$spin_get_rate{$latch_name}
.
$^="LM_TOP";
$~="LM_ROW";

}

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


sub init
{
  $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:")||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 $!;
    }


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

    $SIG{'INT'}='int_handle';
  
  $main_csr=&ora_open($lda,
		      'select name,sum(waits_holding_latch),
                            sum(gets+immediate_gets),
                            sum(misses+immediate_misses),
                            sum(sleeps),sum(spin_gets)
                       from sys.v_$latch
                      where 1=:1
                      group by name')
    ||die $ora_errstr;

  &format();

  $instance_name=&get_instance($lda);
  
  

}

sub get_instance
{
  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);

}

sub mon_latch
{
  
      &ora_bind($main_csr,1)||die $ora_errstr;
      $tot_misses=0;
      $tot_gets=0;
      $tot_sleeps=0;
      while(@l_data=&ora_fetch($main_csr))
	{
	  $latch=$l_data[0];
	  $waits{$latch}=$l_data[1]-$sum_waits{$latch};
	  $gets{$latch}=$l_data[2]-$sum_gets{$latch};
	  $misses{$latch}=$l_data[3]-$sum_misses{$latch};
	  $sleeps{$latch}=$l_data[4]-$sum_sleeps{$latch};
	  $spin_gets{$latch}=$l_data[5]-$sum_spin_gets{$latch};
	  $sum_waits{$latch}=$l_data[1];
	  $sum_gets{$latch}=$l_data[2];
	  $sum_misses{$latch}=$l_data[3];
	  $sum_sleeps{$latch}=$l_data[4];
	  $sum_spin_gets{$latch}=$l_data[5];
	  $tot_gets+=$gets{$latch};
	  $tot_misses+=$misses{$latch};
	  $tot_sleeps+=$sleeps{$latch};
	}
  $last_timestamp=$info_timestamp;
  $info_timestamp=time();
  if ($last_timestamp==0)
    {
      $interval=1;
    }
  else
    {
      $interval=$info_timestamp-$last_timestamp;
    }
      $tot_misses=1 if ($tot_misses==0);
      $tot_gets=1   if ($tot_gets==0);
      @latch_list=keys %waits;
      @latch_list=sort by_misses @latch_list;
      @latch_list=reverse @latch_list;
      if ($n==1)
	{
	  # Sorted by total gets at start of monitor
	  @latch_list_sum=keys %gets;
	  @latch_list_sum=sort by_gets @latch_list_sum;
	  @latch_list_sum=reverse @latch_list_sum;
	}
      system("tput clear") if (! $opt_s);
      $-=0;
      foreach $i (0..$#latch_list)
	{
	  last if ($i >= 18);
	  $latch_name=$latch_list[$i];
	  $waits_=$waits{$latch_name}/$interval;
	  $gets_=$gets{$latch_name}/$interval;
	  $misses_=$misses{$latch_name}/$interval;
	  $sleeps_=$sleeps{$latch_name}/$interval;
	  if ($misses{$latch_name} >0)
	  {
		  $avg_sleeps{$latch_name}=$sleeps{$latch_name}
		       /$misses{$latch_name};
		  $spin_get_rate{$latch_name}=($spin_gets{$latch_name}
		       /$misses{$latch_name})*100;
	  }
	  else
	  {
		$avg_sleeps{$latch_name}=0;
		$spin_get_rate{$latch_name}=0;
	}


	  if ($gets_==0)
	    {
	      $hit_rate=100;
	    }
	  else
	    {
	      $hit_rate=int((($gets_-$misses_)/$gets_)*100);
	      # REVISIT: Is this right?
	    }
	  write if (! $opt_s);
	}
}				#

sub by_gets
{
	#Sort by total waits
	$gets{$a} <=> $gets{$b};
}

sub by_misses
{
	#Sort by total waits
	$misses{$a} <=> $misses{$b};
}

sub print_log
{

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

  select LOG_FILE;
  $|=1;				# unbuffered
  
  if ($n==2)			# First line in the log
    {
      print $now;
      for $latch (@latch_list_sum)
	{
	  print ",".$latch."_gets,".$latch."_misses,".$latch.
	    "_getpct,".$latch."_misspct,".$latch."_sleep,".$latch."_spin_get_rate";

	}
      print "\n";
    }

  print $now;
  for $latch (@latch_list_sum)
    {
      print ",".$gets{$latch}.",".$misses{$latch}.","
	.int(($gets{$latch}/$tot_gets)*100).","
	  	.int(($misses{$latch}/$tot_misses)*100).","
		.$avg_sleeps{$latch}.",".$spin_get_rate{$latch};
	  
    }
  print "\n";

  select STDOUT;
  
}

&main() ;