#!/usr/local/bin/perl
# $Source: /u2/gharriso/Src/RCS/dbtop.pl,v $
# $Revision: 1.2 $
# $Author: gharriso $
# $Locker: gharriso $
# $Date: 1996/11/16 01:28:37 $ 
# 
# Name: dbtop
# 
# Purpose: Show ORACLE users sorted by IO usage, etc
#
# Author:
#        Guy Harrison  guy@jinx.apana.org.au
#

#eval 'exec oraperl -S $0 ${1+"$@"}'
#	    if $running_under_some_shell;


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

sub init
{
  #
  # Log on
  #
    require "ctime.pl";
    require "getopts.pl";
    
    #get command line options
    &Getopts('hrc:l:u:P')||die $usage ;

      if ($opt_u)
    {
      $login=$opt_u;
    }
  else
    {
      if (defined($ENV{ORA_LOGIN}))
	{
	  $login=$ENV{ORA_LOGIN};
	}
      else
	{
	  $login="/";
	}
    }

    chop($date = &ctime(time));
    
    
    $usage = <<EOFEOF;

Usage:  $0 -u username/password1 [-c cycle_seconds -l no_of_processes]

            
EOFEOF

    if ($opt_h) 
    {   
        print STDERR $usage;
        exit;
    }

    if ($opt_c)
      {
	$cycle_secs=$opt_c;
      }
    else
      {
	$cycle_secs=60;
      }

    if ($opt_l)
      {
	$processes=$opt_l;
      }
    else
      {
	$processes=18;
      }
	  

      
    if ($#ARGV+1 > 1 ) 
    {   
        print STDERR "Error:  Incorrect number of command line arguments\n";
        print STDERR $usage;
        exit;
    }
       
    # connect to the database
    # print "login as ".$login."\n";
    $lda = &ora_login("", "$login", "")
      || &abend("$ora_errstr");

    if ( $ENV{SQL_TRACE} eq "TRUE" )
      {
	&ora_do($lda,"alter session set sql_trace true");
      }

    $info_timestamp=0;

}

sub abend
{
    die "@_\n";
    
}


sub show_sessions
{
  #
  #
  #
  undef(%username,%progname,%phys_reads,%blk_gets,%con_gets,%blk_changes,%con_changes);
  undef %phys_reads;
  if (! defined($ss_csr))
    {
      $ss_csr=&ora_open($lda,q{
             select si.sid,se.username,
                    se.program s_program,p.program p_program ,
                    si.physical_reads,si.block_gets,
                    si.consistent_gets,si.block_changes,
                    si.consistent_changes,
                    se.osuser,se.process,p.spid,0,
                    se.serial#,
                    se.status,se.machine
               from sys.v_$sess_io si, sys.v_$session se, v$process p
              where si.sid(+)=se.sid
                and p.addr(+)=se.paddr
                and 1=:1 }
          )
	||&abend("open ss_csr:$ora_errstr");
    }

  #
  # This dummy bind might cut down on parse time
  #
  &ora_bind($ss_csr,1)
    ||&abend("Bind ss_csr: $ora_errstr");

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

  $tot_log_rds=0;
  $tot_phys_rds=0;
  $tot_chgs=0;
  $tot_cpu=0;
  $proc_count=0;
  $active_count=0;
  $load=`uptime`;
  $load=~/load average:(\s+)((\d+).(\d+)),(.*)/;
  $load=$2;
  while (($sid,$username,$s_progname,$p_progname,$phys_reads,$blk_gets,
	  $con_gets,$blk_changes,$con_changes,$osuser,$pidc,$pids,$cpu,
	  $serial,$status,$machine)
	 =&ora_fetch($ss_csr))
    {
      $proc_count++;
      $active_count++ if ($status eq 'ACTIVE');
      &decipher_progname();

      &calc_io();
     }
  &abend("fetch sl_csr :$ora_errstr") if $ora_errno;
  &print_out();
}

sub get_local_progname
{
  undef %pid_cmd;
  local($ospid,$oscmd);
  
  open(PS,"ps -ef |cut -c 10-16,49-69|")||die $!;
  while (<PS>)
    {
      chop;
      $ospid=substr($_,0,6);
      $oscmd=substr($_,7);
      $pid_cmd{int($ospid)}=$oscmd;
    }

}


sub print_out
{
  system("cls");
  $-=0;

  $host=`uname -n`;
  chop($host);


  if ($opt_P)
    {
      # Use ps to work out program names (local only)
      &get_local_progname();
    }

  @sid_list=keys %phys_reads;
  @sid_list=sort by_log_reads @sid_list;
  @sid_list=reverse @sid_list;
  foreach $i (0..$#sid_list) 
    {
      last if ($i >= $processes);

      $sid=$sid_list[$i];
      $username=$username{$sid};

      if ($opt_P && ( $host eq $machine{$sid}) )
      {
	 $progname=$pid_cmd{$pidc{$sid}};
       }
      else
	{
	  $progname=$progname{$sid};
	}
      $phys_reads=$phys_reads{$sid};
      $blk_gets=$blk_gets{$sid};
      $con_gets=$con_gets{$sid};
      $blk_changes=$blk_changes{$sid};
      $con_changes=$con_changes{$sid};
      $osuser=$osuser{$sid};
      $pidc=$pidc{$sid};
      $pids=$pids{$sid};
      $cpu=$cpu{$sid};
      if (($blk_gets+$con_gets)>0)
	{			 
	  $hit=int(((($con_gets+$blk_gets)-$phys_reads)/
		    ($con_gets+$blk_gets))*100);
	}			 
      else			 
	{			 
	  $hit=-1;
	}
      $tot_log_rds=1;
      write;
    }
  
}

sub by_phys_reads
{
  #
  # Subroutine for the sort function to sort the sids by physical IO
  #
  $phys_reads{$a} <=> $phys_reads{$b};
}

sub by_log_reads
{
  #
  # Subroutine for the sort function to sort the sids by logical reads
  #
  ($blk_gets{$a}+$con_gets{$a}) <=> ($blk_gets{$b}+$con_gets{$b});
}

sub calc_io
{
  #Work out the difference in io since the last sample.  Sort and print
  #In physical io order

  #
  # Current IO = this -last
  #
  if ($serial==$serial_l{$sid})
    {
      $phys_reads_c=$phys_reads-$phys_reads_l{$sid};
      $blk_gets_c=$blk_gets-$blk_gets_l{$sid};
      $con_gets_c=$con_gets-$con_gets_l{$sid};
      $blk_changes_c=$blk_changes-$blk_changes_l{$sid};
      $con_changes_c=$con_changes-$con_changes_l{$sid};
      $cpu_c=$cpu-$cpu_l{$sid};
    }
  else
    {
      $phys_reads_c=$phys_reads;
      $blk_gets_c=$blk_gets;
      $con_gets_c=$con_gets;
      $blk_changes_c=$blk_changes;
      $con_changes_c=$con_changes;
      $cpu_c=$cpu;
    }

  $tot_log_rds+=($blk_gets_c+$con_gets_c);
  $tot_phys_rds+=$phys_reads_c;
  $tot_chgs+=($blk_changes_c+$con_changes_c);
  $tot_cpu+=$cpu_c;

  #
  # Store current to an associative array
  #
  $username{$sid}=$username;
  $machine{$sid}=$machine;
  $osuser{$sid}=$osuser;
  $progname{$sid}=$progname;
  $phys_reads{$sid}=$phys_reads_c;
  $blk_gets{$sid}=$blk_gets_c;
  $con_gets{$sid}=$con_gets_c;
  $blk_changes{$sid}=$blk_changes_c;
  $con_changes{$sid}=$con_changes_c;
  $pidc{$sid}=$pidc;
  $pids{$sid}=$pids;
  $cpu{$sid}=$cpu_c;

  #
  # Store last to an associative array
  #
  $phys_reads_l{$sid}=$phys_reads;
  $blk_gets_l{$sid}=$blk_gets;
  $con_gets_l{$sid}=$con_gets;
  $blk_changes_l{$sid}=$blk_changes;
  $con_changes_l{$sid}=$con_changes;
  $cpu_l{$sid}=$cpu;
  $serial_l{$sid}=$serial;

}

sub decipher_progname
{
  local(@path);
  if (defined($s_progname) )
    #
    # If there's a program for the session,  use that, it's the 
    # program name of the client
    #
    {
      ($progname)=split('@',$s_progname); # Get rid of everything after the@
      if (index($progname,'<',0)>=$[)
	{
	  {
	    #Assume it's a unix program 
	    @path=split('/',substr($progname,1,length($progname)-2));
	  }
	  
	}			# 
      elsif (index($progname,'\\',0) !=$[)
	{
	  # This is probably a dos program
	  @path=split('\\\\',$progname);
	}			# 
      $progname=$path[$#path];
    }				# 
  else
    {				# 
      # Use the p_program (shadow process)
      ($junk,$progname)=split('\(',$p_progname);
      $progname=substr($progname,0,length($progname)-1);
    }				# 
}				# 

sub get_sess_details
{
  #
  # User details
  #
  local($sid)=@_;
  if (!  defined($gsd_csr))
    {
      $gsd_csr=&ora_open($lda,
			 'select username,osuser,process
			   from V$SESSION
			  where sid=:1')
	||&abend("open gsd_csr:$ora_errstr");
    }
  &ora_bind($gsd_csr,$sid)
    ||&abend("bind gsd_csr: $ora_errstr");
  ($username,$osuser,$process)=&ora_fetch($gsd_csr);
  &abend("fetch gsd_csr: $ora_errstr") if $ora_errno;

  #
  # Get the owner name
  #
  if (! defined($gsd_csr2) )
    {
      $gsd_csr2=&ora_open($lda,
			  'select object_name
                             from sys.dba_objects
                            where object_id=:1')
	||&abend("open gsd_csr2: $ora_errstr");
    }
   &ora_bind($gsd_csr2,$lid1)
    ||&abend("bind gsd_csr: $ora_errstr");
  ($object)=&ora_fetch($gsd_csr2);
  &abend("fetch gsd_csr2: $ora_errstr") if $ora_errno;
  
}

sub sio_format
{
  format SIO_TOP=
ORACLE Session IO @>>s interval         Totals: @>>>>>> @>>>>>>>> @>> @>>>>>>
$interval,int($tot_cpu/$interval),int($tot_log_rds/$interval),int(($tot_log_rds-$tot_phys_rds)*100/$tot_log_rds),int($tot_chgs/$interval)
Users: @>>> Active: @>>>>   Load Avg: @>>>>>>
$proc_count,$active_count,$load

SID  User        UNIX User   Pid   Program      Machine Gets/sec  Hit Chgs/sec
---- ----------- ----------- ----- ------------ ------- --------- --- -------
.
  
  format SIO_ROW=
@>>> @<<<<<<<<<< @<<<<<<<<<< @>>>> @<<<<<<<<<<< @<<<<<< @>>>>>>>> @>> @>>>>>>
$sid,$username,$osuser,$pids,$progname,$machine{$sid},int(($blk_gets+$con_gets)/$interval),$hit,int(($blk_changes+$con_changes)/$interval)
.
#        1         2         3         4         5         6         7         8  
#2345678901234567890123456789012345678901234567890123456789012345678901234567890
$^ = "SIO_TOP";
$~ = "SIO_ROW";
  
}

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

sub main
{
#  $SIG{'INT'}='int_handle';
  &init();
  &sio_format();
  while(1)
    {
      &show_sessions();
      sleep $cycle_secs;
    }
}

&main();