#!/usr/local/bin/perl
# $Source: /u2/gharriso/Src/RCS/dbmon.pl,v $
# $Author: gharriso $
# $Revision: 1.5 $
# $Date: 1996/01/30 22:10:18 $
#
# Name: dbmon.pl
#
# Purpose: Display and record major ORACLE statistics
#
# Author:
#        Guy Harrison  guy@jinx.apana.org.au
#

# Display various info from sysstat, etc

use Oraperl;
require "strftime.pl";

$SIG{'INT'}='int_handle';	# interupt handler

  #
  # This array defines the stats which are to be recorded in the
  # monitoring database.
  #
  @save_stats=('sql_gets','sql_hits','latch_gets','latch_misses',
	       'dict_gets','dict_misses','buffer_busy', # 
	       'logical reads','physical reads','user calls',
	       'recursive calls',
	       'parse count','execute count','transactions',
	       'user rollbacks','sorts','disk sort%','table scans',
	       'buffer hit rate','buffer busy%','sql_hit%',
	       'dict_hit%','latch_hit%','connected_users',
	       'active_users_real','redo blocks written',
	       'redo_per_transaction',
	       'db_file_waits','log_file_waits','latch_waits',
	       'free_buffer_waits','buffer_busy_waits','other_waits',
	       'total_waits') ;
  #
  # This array defines the stats which are to be recorded in the
  # monitoring database.
  #
  @save_stats=('sql_gets','sql_hits','latch_gets','latch_misses',
	       'dict_gets','dict_misses','buffer_busy', # 
	       'logical reads','physical reads','user calls',
	       'recursive calls',
	       'parse count','execute count','transactions',
	       'user rollbacks','sorts','disk sort%','table scans',
	       'buffer hit rate','buffer busy%','sql_hit%',
	       'dict_hit%','latch_hit%','connected_users',
	       'active_users_real','redo blocks written',
	       'redo_per_transaction',
	       'db_file_waits','log_file_waits','latch_waits',
	       'free_buffer_waits','buffer_busy_waits','other_waits',
	       'total_waits','db_file_waits%','log_file_waits%','latch_waits%',
	       'free_buffer_waits%','buffer_busy_waits%','other_waits%',
	       'avg write queue');

  $interesting_statistics="'user commits','user rollbacks',".
    "'cumulative open cursors','consistent gets','db block gets',".
      "'physical reads','user calls','recursive calls',".
	"'table scans (short tables)','table scans (long tables)',".
	  "'sorts (memory)','sorts (disk)','buffer busy waits',".
	    "'db block changes','user commits','sorts (rows)',".
	     " 'table fetch continued row','table scan rows gotten',".
	      " 'table fetch by rowid','execute count','parse count',".
		"'enqueue waits','enqueue requests','enqueue timeouts',".
		  "'user commits','redo blocks written',".
                   "'CPU used by this session','free buffer inspected',".
  "'free buffer requested','summed dirty queue length','write requests',".
  "'db block changes','consistent changes'";
  
  


sub main
{
  &init();			# Process command line, etc
  $n=0;
  while(1)			# "Forever"
    {
      $n++;
      &update_display();	# grab stats and update screen
      
      if ($n>1)			# First stats are summaries so don't log them
	{
	  &print_log()   if ($opt_l); # Save to log file
	  &save_to_db()  if ($opt_D); # Save to database
	}
      
      last if ($opt_n && $n==$opt_n); # Maximum rows retrieved
      sleep($sleep_time);	
    }
}

sub init		
{
  #
  # Setup up constants, connect to db, command line, etc
  #
  
  &set_format();
  

  # No of rows to display

  $screen_size=18;
  
  #
  # Get options
  #
  $usage="$0 -u username/password [-s(ilent)] [-l logfile]\n\t [-n no of iterations] [-S econds between displays] [-D connect_string ] -d (debug)\n".
    " -D option stores stats in nominated system\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;
  if ($opt_d)
    {
      &ora_do($lda,"alter session set sql_trace true")||
	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;
    }

  #
  # Connect to the "monitoring" database.  This is the one with a 
  # PERF_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);



  #
  # Open all cursors
  #
  &open_cursors();


  
}				


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);
}


sub update_display
{
  #
  # Collect all stats and update the display
  #

  #
  # Collect all raw data
  # Note: The time interval is based on the time to open the cursors, ignoring
  # fetch time.  This would be valid if consistent read was in place but here?
  #
  
  &ora_bind($ss_csr,1);		                 # sysstat stuff

  &ora_bind($sc_csr,1)||die $ora_errstr;         # sql cache 
  (($this_obs{'sql_gets'},$this_obs{'sql_hits'})
   =&ora_fetch($sc_csr))||die $ora_errstr;
  
  &ora_bind($lc_csr,1)||die $ora_errstr;         # latches
  (($this_obs{'latch_gets'},$this_obs{'latch_misses'},
    $this_obs{'latch_sleeps'},$this_obs{'spin_gets'})
   =&ora_fetch($lc_csr))||die $ora_errstr;
  

  &ora_bind($dc_csr,1)||die $ora_errstr;         # dictionary cache
  (($this_obs{'dict_gets'},$this_obs{'dict_misses'})
   =&ora_fetch($dc_csr))||die $ora_errstr;

  &ora_bind($bb_csr,1)||die $ora_errstr;         # buffer busy  
  (($this_obs{'buffer_busy'})
   =&ora_fetch($bb_csr))||die $ora_errstr;

  &ora_bind($au_csr,1)||die $ora_errstr;         # Active and total users
  (($this_obs{'active_users'},$this_obs{'total_users'})
   =&ora_fetch($au_csr))||die $ora_errstr;

  &ora_bind($wait_csr,1)||die $ora_errstr;       # Waits

  $this_obs{'db_file_waits'}=0;
  $this_obs{'log_file_waits'}=0;
  $this_obs{'latch_waits'}=0;
  $this_obs{'free_buffer_waits'}=0;
  $this_obs{'buffer_busy_waits'}=0;
  $this_obs{'buffer waits'}=0;
  $this_obs{'other_waits'}=0;
  $this_obs{'lock_waits'}=0;
  $this_obs{'total_waits'}=0;
  
  while(($l_event,$l_total_waits,$l_time_waited)
	=&ora_fetch($wait_csr))
    {
      $this_obs{'total_waits'}+=$l_time_waited;
      if ($l_event=~/^db file/)
	{
	  $this_obs{'db_file_waits'}+=$l_time_waited;
	}
      elsif ($l_event=~/^log file/)
	{
	  $this_obs{'log_file_waits'}+=$l_time_waited;
	}
      elsif ($l_event=~/^latch/)
	{
	  $this_obs{'latch_waits'}+=$l_time_waited;
	}
      elsif ($l_event=~/^free buffer/)
	{
	  $this_obs{'buffer waits'}+=$l_time_waited;
	  $this_obs{'free_buffer_waits'}+=$l_time_waited;
	}
      elsif ($l_event=~/^buffer busy/)
	{
	  $this_obs{'buffer waits'}+=$l_time_waited;
	  $this_obs{'buffer_busy_waits'}+=$l_time_waited;
	}
      elsif ($l_event=~/^enqueue/)
	{
	  $this_obs{'lock_waits'}+=$l_time_waited;
	}
      else
	{
	  $this_obs{'other_waits'}+=$l_time_waited;
	}
    }
  die $ora_errstr if $ora_errno;


  #
  # Work out the time interval
  #
  $last_timestamp=$info_timestamp;
  $info_timestamp=time();
  if ($last_timestamp==0)
    {
      $interval=1;
    }
  else
    {
      $interval=$info_timestamp-$last_timestamp;
    }
  
  while(($name,$value)=&ora_fetch($ss_csr))
    {
      $this_obs{$name}=$value;
    }
  die $ora_errstr if $ora_errno;

    
  
  foreach $name (keys(%this_obs))
    {
      $stat_delta{$name}=($this_obs{$name}-$last_obs{$name})/$interval;
      $last_obs{$name}=$this_obs{$name};
    }

  
  system("tput clear") if (! $opt_s);
  
  &derive_stats();
  &calc_avg();


  $timehhmm=&strftime('%H:%M' ,time);

  write if (! $opt_s);
  
  
  
}

sub calc_avg
{
  #
  # Calculate the average for every stat
  #
  $N++;
  if ($N>1)		# Don't use first obs as an avg
    {
      $obs++;
      foreach $stat (keys(%stat_delta))
	{
	  $stat_sum{$stat}+=$stat_delta{$stat};
	  $stat_avg{$stat}=$stat_sum{$stat}/$obs;
	}
      
      foreach $stat (keys(%derived))
	{
	  $stat_sum{$stat}+=$derived{$stat};
	  $stat_avg{$stat}=$stat_sum{$stat}/$obs;
	}
    }

}

sub derive_stats
{
  #
  # Derive interesting ratio's etc
  #
  
  foreach $stat (keys(%derived))
    {
      $derived{$stat}=0;
    }
  
  $derived{'logical reads'}=$stat_delta{'db block gets'}+
    $stat_delta{'consistent gets'};

  $derived{'buffer hit rate'}=0;
  if ($derived{'logical reads'}>0)
    {
      $derived{'buffer hit rate'}=(($derived{'logical reads'}
				    -$stat_delta{'physical reads'})/
				   $derived{'logical reads'}*100);
    }
  $derived{'buffer busy%'}=0;   
  if ($derived{'logical reads'}>0)
    {
      $derived{'buffer busy%'}=
	$stat_delta{'buffer_busy'}/$derived{'logical reads'};
    }

  $derived{'active_users%'}=0;   
  if ($this_obs{'total_users'}>0)
    {
      $derived{'active_users%'}=($this_obs{'active_users'}/
	                         $this_obs{'total_users'})*100;
    }
  
  $derived{'calls'}=$stat_delta{'recursive calls'}+
    $stat_delta{'user calls'};
  
  $derived{'recursive calls'}=0;   
  if ($stat_delta{'recursive calls'}>0)
    {
      $derived{'recursive calls%'}=($stat_delta{'recursive calls'}/
				    $derived{'calls'})*100;
    }
  
  
  
  $derived{'parse/execute%'}=0;
  if ($stat_delta{'execute count'}>0)
    {
      $derived{'parse/execute%'}=($stat_delta{'parse count'}/
				  $stat_delta{'execute count'})*100;
    }
  
  $derived{'transactions'}=$stat_delta{'user commits'}+
    $stat_delta{'user rollbacks'};
  
  $derived{'rollback%'}=0;
  if ($derived{'transactions'}>0)
    {
      $derived{'rollback%'}=($stat_delta{'user rollbacks'}/
			     $derived{'transactions'})*100;
    }
  
  if ($stat_delta{'enqueue requests'}>0)
    {
      $derived{'enqueue fail%'}=(($stat_delta{'enqueue timeouts'}+
				  $stat_delta{'enqueue waits'})/
				 $stat_delta{'enqueue requests'})*100;
    }
  else    
  {
	$derived{'enqueue fail%'}=0;
  }

  $derived{'sorts'}=$stat_delta{'sorts (memory)'}+$stat_delta{'sorts (disk)'};

  $derived{'disk sort%'}=0;
  if ($derived{'sorts'}>0)
    {
      $derived{'disk sort%'}=($stat_delta{'sorts (disk)'}/$derived{'sorts'})*100;
    }

  $derived{'table scans'}=$stat_delta{'table scans (short tables)'}
  +$stat_delta{'table scans (long tables)'};

  $derived{'long scan%'}=0;
  if ($derived{'table scans'}>0)
    {
      $derived{'long scan%'}=$stat_delta{'table scans (long tables)'}/
	$derived{'table scans'}
    }

  $derived{'sql_hit%'}=0;
  if ($stat_delta{'sql_gets'}>0)
    {
      $derived{'sql_hit%'}=($stat_delta{'sql_hits'}/$stat_delta{'sql_gets'})*100;
    }

  $derived{'latch_hit%'}=0;
  if ($stat_delta{'latch_gets'}>0)
    {
      $derived{'latch_hit%'}=(($stat_delta{'latch_gets'}-
			      $stat_delta{'latch_misses'})/
			      $stat_delta{'latch_gets'})*100;
    }

  $derived{'latch_avg_sleeps'}=0;
  $derived{'spin_get_rate'}=0;
  if ($stat_delta{'latch_misses'}>0)
    {
      $derived{'spin_get_rate'}=($stat_delta{'spin_gets'}/
				 $stat_delta{'latch_misses'})*100;
    }
  
  if (($stat_delta{'latch_misses'}-$stat_delta{'spin_gets'})>0)
    {
      $derived{'latch_avg_sleeps'}=($stat_delta{'latch_sleeps'}/
				    ($stat_delta{'latch_misses'}
				     -$stat_delta{'spin_gets'}));
    }



  $derived{'dict_hit%'}=0;
  if ($stat_delta{'dict_gets'}>0)
    {
      $derived{'dict_hit%'}=(($stat_delta{'dict_gets'}-
			      $stat_delta{'dict_misses'})/
			      $stat_delta{'dict_gets'})*100;
    }

  #
  # These two don't make sense if they're "delta"ed so derive an equivalent
  #
  $derived{'connected_users'}=$this_obs{'total_users'}; 
  $derived{'active_users_real'}=$this_obs{'active_users'};

  $derived{'gets_per_executes'}=0;
  if ($stat_delta{'execute count'} >0)
    {
      $derived{'gets_per_executes'}=$derived{'logical reads'}/$stat_delta{'execute count'};
    }

  $derived{'redo_per_transaction'}=0;
  if ($derived{'transactions'}>0)
    {
      $derived{'redo_per_transaction'}=$stat_delta{'redo blocks written'}/
	$derived{'transactions'};
    }

  $derived{'free_buffer_wait_ratio'}=0;
  if ($stat_delta{'free buffer requested'}>0)
    {
      $derived{'free_buffer_wait_ratio'}
      =($stat_delta{'free_buffer_waits'}/
	$stat_delta{'free buffer requested'})*100;
    }

  $derived{'avg write queue'}=0;
  if ($stat_delta{'write requests'}>0)
    {
      $derived{'avg write queue'}=
	($stat_delta{'summed dirty queue length'}/
	 $stat_delta{'write requests'});
    }

  

  $derived{'db_file_waits%'}=&wait_pct('db_file_waits');
  $derived{'log_file_waits%'}=&wait_pct('log_file_waits');
  $derived{'latch_waits%'}=&wait_pct('latch_waits');
  $derived{'free_buffer_waits%'}=&wait_pct('free_buffer_waits');
  $derived{'buffer_busy_waits%'}=&wait_pct('buffer_busy_waits');
  $derived{'other_waits%'}=&wait_pct('other_waits');
  

}

sub set_format
{
  #
  # Format for the screen display 
  #
  format DBMON=
ORACLE statistics/second (Averages in brackets): @<<<<<<<<<<<< @<<<<<
$instance_name,$timehhmm
-------------------------------------------------------------------------------
Users:        @######### (@#########)       Active  : @##.## (@##.##)
$derived{'connected_users'},$stat_avg{'connected_users'},$derived{'active_users_real'},$stat_avg{'active_users_real'}
Logical gets: @######### (@#########)       Hit rate: @##.## (@##.##)
$derived{'logical reads'},$stat_avg{'logical reads'},$derived{'buffer hit rate'},$stat_avg{'buffer hit rate'}
SQL cache   : @######### (@#########)           Hit : @##.## (@##.##)
$stat_delta{'sql_gets'},$stat_avg{'sql_gets'},$derived{'sql_hit%'},$stat_avg{'sql_hit%'}
Dict cahce  : @######### (@#########)           Hit : @##.## (@##.##)
$stat_delta{'dict_gets'},$stat_avg{'dict_gets'},$derived{'dict_hit%'},$stat_avg{'dict_hit%'}
latch reqsts: @######### (@#########)           Hit : @##.## (@##.##)
 $stat_delta{'latch_gets'},$stat_avg{'latch_gets'},$derived{'latch_hit%'},$stat_avg{'latch_hit%'}
                                    latch spin get% : @##.## (@##.##)
 $derived{'spin_get_rate'},$stat_avg{'spin_get_rate'}
                                   latch avg sleeps : @##.## (@##.##)
 $derived{'latch_avg_sleeps'},$stat_avg{'latch_avg_sleeps'}
Redo writes : @######### (@#########)
$stat_delta{'redo blocks written'},$stat_avg{'redo blocks written'}
Executes    : @######### (@#########)          Parse: @##.## (@##.##)
$stat_delta{'execute count'},$stat_avg{'execute count'},$derived{'parse/execute%'},$stat_avg{'parse/execute%'}
Calls       : @######### (@#########)      Recursive: @##.## (@##.##)
$derived{'calls'},$stat_avg{'calls'},$derived{'recursive calls%'},$stat_avg{'recursive calls%'}
Transactions: @######### (@#########)       Rollback: @##.## (@##.##)
$derived{'transactions'},$stat_avg{'transactions'},$derived{'rollback%'},$stat_avg{'rollback%'}
Enqueue req : @######### (@#########)           Fail: @##.## (@##.##)
$stat_delta{'enqueue requests'},$stat_avg{'enqueue requests'},$derived{'enqueue fail%'},$stat_avg{'enqueue fail%'}
Sorts       : @######### (@#########)           Disk: @##.## (@##.##)
$derived{'sorts'},$stat_avg{'sorts'},$derived{'disk sort%'},$stat_avg{'disk sort%'}
Table scans : @######### (@#########)           Long: @##.## (@##.##)
$derived{'table scans'},$stat_avg{'table scans'},$derived{'long scan%'},$stat_avg{'long scan%'}

Waits:       db I/O    log I/O      latch    buffer     enqueue        oth
secs     @######.## @######.## @######.## @######.## @######.## @######.##
$stat_delta{'db_file_waits'},$stat_delta{'log_file_waits'},$stat_delta{'latch_waits'},$stat_delta{'buffer waits'},$stat_delta{'lock_waits'},$stat_delta{'other_waits'}
avg      @######.## @######.## @######.## @######.## @######.## @######.##
&tot_wait_pct('db_file_waits'),&tot_wait_pct('log_file_waits'),&tot_wait_pct('latch_waits'),&tot_wait_pct('buffer waits'),&tot_wait_pct('lock_waits'),&tot_wait_pct('other_waits')
%total       @##.##     @##.##     @##.##     @##.##     @##.##     @##.##
&wait_pct('db_file_waits'),&wait_pct('log_file_waits'),&wait_pct('latch_waits'),&wait_pct('buffer waits'),&wait_pct('lock_waits'),&wait_pct('other_waits')
.
  $~="DBMON";

}

sub wait_pct
{
  # return the pct of total waits for the nominated category
  local($wait)=@_;

  if ($stat_delta{'total_waits'} >0)
    {
      return(($stat_delta{$wait}/$stat_delta{'total_waits'})*100);
    }
  else
    {
      return(0);
    }
}
  
sub tot_wait_pct
{
  # return the pct of total waits for the nominated category
  local($wait)=@_;

  if ($stat_avg{'total_waits'} >0)
    {
      return(($stat_avg{$wait}/$stat_avg{'total_waits'})*100);
    }
  else
    {
      return(0);
      }
}
sub int_handle
{
  local($sig)=@_;
  print "Exit $sig\n";
  exit(0);
}

sub open_cursors
{
  #
  # Declare the main cursor
  #
  $ss_csr=&ora_open($lda,
                  "select name,value
                     from sys.v_\$sysstat
                    where 1=:1
                      and name in ($interesting_statistics)
                     ")||die $ora_errstr;

  $sc_csr=&ora_open($lda,
		    "select sum(gets),sum(gethits)
                       from sys.v_\$librarycache
                      where namespace='SQL AREA'
                        and 1=:1")
    ||die $ora_errstr;

  $lc_csr=&ora_open($lda,
		    "select sum(gets),
                            sum(misses),
                            sum(sleeps),
                            sum(spin_gets)
                       from sys.v_\$latch
                      where 1=:1")
    ||die $ora_errstr;

  $dc_csr=&ora_open($lda,
		    "select sum(gets),sum(getmisses)
                       from sys.v_\$rowcache
                      where 1=:1")
    ||die $ora_errstr;

  $bb_csr=&ora_open($lda,
		    "select sum(count) from sys.v_\$waitstat
                      where 1=:1")
    ||die $ora_errstr;

  $au_csr=&ora_open($lda,
		    "select sum(decode(status,'ACTIVE',1,0)),
                            count(status)
                       from sys.v_\$session
                      where type='USER' and 1=:1")
    ||die $ora_errstr;

  if ($opt_D)
    {
      $save_db_csr=&ora_open($mlda,
			     "insert into perf_stats
                                 (db_name,timestamp,stat_name,stat_value)
                           values (:1,to_date(:2,'DD/MM/YY:HH24:MI:SS'),
                                   :3,:4)") # 
	||die $ora_errstr;
    }

  $unwanted_events="'Null event','client message','smon timer',".
	"'rdbms ipc message','pmon timer','WMON goes to sleep'".
	",'virtual circuit status','dispatcher timer',".
	  "'SQL*Net message from client'";
  $unwanted_clause="and event not in ($unwanted_events)";
  $wait_csr=&ora_open($lda,
		      "select event,total_waits,time_waited
                         from sys.v_\$system_event
                        where 1=:1 $unwanted_clause")
    ||die $ora_errstr;
			  

}

sub save_to_db
{
  local($now); 
  require "strftime.pl";
  $now=&strftime('%e/%m/%y:%H:%M:%S' ,time);
  for $stat (sort(keys(%derived)))
    {
      if (&is_interesting($stat))
	{
	  &ora_bind($save_db_csr,
		    $instance_name,$now,$stat,$derived{$stat})
	    ||warn $ora_errstr;
	}

    }
  for $stat (sort(keys(%stat_delta)))
    {
      if (&is_interesting($stat))
	{
	  &ora_bind($save_db_csr,
		    $instance_name,$now,$stat,$stat_delta{$stat})
	    ||warn $ora_errstr;
	}

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


sub is_interesting
{
  local($stat_name)=@_;
  for $stat (@save_stats)
    {
      if ($stat eq $stat_name)
	{
	  return(1);
	}
    }
}

sub print_log
{

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

  select LOG_FILE;
  $|=1;				# unbuffered
   
  @temp1=keys(%derived);
  @temp2=keys(%stat_delta);
  push(@temp1,@temp2);
  $columns=$#temp1;
  if ($n==2 || $columns!=$last_columns)	# First line in the log
    {                                   # or new column added (bug!)
      print $now;
      for $stat (sort(keys(%derived)))
	{
	  print ",".$stat;
	}
      for $stat (sort(keys(%stat_delta)))
	{
	  print ",".$stat
	}
      print "\n";
    }
  $last_columns=$columns;

  print $now;
  for $stat (sort(keys(%derived)))
    {
      print ",".$derived{$stat};
    }
  for $stat (sort(keys(%stat_delta)))
    {
      print ",".$stat_delta{$stat};
    }
  print "\n";

  select STDOUT;
  
}

&main();