#!/usr/local/bin/perl
# 
# $Header: emdb/sysman/admin/scripts/rac/collectSrvPerf.pl /st_emdbsa_11.2/5 2009/03/20 22:21:46 rsamaved Exp $
#
# collectSrvPerf.pl
# 
# Copyright (c) 2007, 2009, Oracle and/or its affiliates. All rights reserved. 
#
#    NAME
#      collectSrvPerf.pl - <one-line expansion of the name>
#
#    DESCRIPTION
#      <short description of component this file declares/defines>
#
#    NOTES
#      <other useful comments, qualifications, etc.>
#
#    MODIFIED   (MM/DD/YY)
#    rsamaved    03/18/09 - fix typo
#    ngade       02/02/09 - Code slap 10.2.0.5.0 -> 11.2 round 2
#    gallison    12/04/08 - Fix up services on standby 
#    sadattaw    06/19/07 - put inst cpu & core into hashtable
#    sadattaw    05/28/07 - fix numcores .
#    sadattaw    03/15/07 - fix available CPU computation
#    rsamaved    02/16/07 - fix min resp calculation
#    rsamaved    01/30/07 - adding method to get instance level elapsed time
#                           and cpu time values
#    rsamaved    01/26/07 - 
#    sadattaw    01/25/07 - service performance collection methods
#    sadattaw    01/25/07 - Creation
# 

use strict;
use DBI qw(:sql_types);
use Cwd;

require "$ENV{EMDROOT}/sysman/admin/scripts/db/dbstate.pl";
require "$ENV{EMDROOT}/sysman/admin/scripts/db/dg/dgutil.pl";


sub collect_service_metric
{
    my ($usr_name, $pwd, $dbName, $addr, $role ) = @_;

my $mode = 0;

if($role =~ /SYSDBA/i)
{
    $mode = 2;
}
elsif($role =~ /SYSOPER/i)
{
    $mode = 4;
}


# --------------------------------------------------------------------
# +++ Establish Target DB Connection
# --------------------------------------------------------------------

my $lda = DBI->connect('dbi:Oracle:', "$usr_name@".$addr, "$pwd",
    {ora_session_mode => $mode, PrintError => 0, RaiseError => 0, AutoCommit => 0})

    or die "em_error=Could not connect to $usr_name/$addr: $DBI::errstr\n";

#print "DB connection successful :  $address \n";
EMD_PERL_DEBUG( "DB connection successful:  $addr \n");

my $database_role = getDBRole($lda);

# do not collect any service info if mounted since views are not accessible...
my $database_status = getDBStatus($lda);
if ($database_status !~ /OPEN/i)
{
  EMD_PERL_DEBUG( "Skipping services.. RAC DB is $database_status\n");
  print "em_result=1||\n";
  $lda->disconnect();
  exit 0;
}
 
#gv$services table does not exclude deleted services so use all_servies to get service list from db
my $tbl = 'all_services';

#Query services defined for this database
my $sql = qq{ SELECT DISTINCT service_id, name, creation_date FROM $tbl };
my $sth = $lda->prepare( $sql );
$sth->execute();

my( $srv_id, $srv_name, $cr_date);
$sth->bind_columns( undef, \$srv_id, \$srv_name, \$cr_date);

#print "Service names from query \n";
EMD_PERL_DEBUG( "Service names from query \n");

my $instcpus = {};
my $instcores = {};
my $instnames = {};

while( $sth->fetch() ) {
#  print "Service :  $srv_id, $srv_name, $cr_date\n";
  EMD_PERL_DEBUG( "Service :  $srv_id, $srv_name, $cr_date\n");

#For each service calculate the metric
  get_service_metric($srv_name, $lda, $dbName, $instcpus, $instcores, $instnames, $database_role);

}

$sth->finish();
$lda->disconnect();

}

sub getDBStatus
{
  my ( $connection ) = @_;
  my $sql = "select status from v\$instance";
  my $dbcur = $connection->prepare($sql);
  $dbcur->execute;

  my @row = $dbcur->fetchrow_array();
  my $status = $row[0];
  return ("${status}");
}

sub get_service_metric
{
    my ( $srv_name, $connection, $dbname, $instcpu, $instcore, $instnm, $dbrole) = @_;

my $met_tbl = 'gv$servicemetric_history';
my $actserv_tbl = 'gv$active_services';
my $inst_tbl = 'gv$instance';
my $param_tbl = 'gv$osstat';

#  print "get_service_metric :  $srv_name\n";
  EMD_PERL_DEBUG( "get_service_metric :  $srv_name , role $dbrole\n");

    #calculate average response time and throughput values across all instances for a given service
    # hence query gv$ tables

    my $met_sql = qq{ SELECT mt.inst_id, service_name, 
                TO_CHAR(CAST(begin_time AS TIMESTAMP) AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') time, 
                TO_CHAR(CAST(end_time AS TIMESTAMP) AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') time, 
		intsize_csec, 
                group_id, cpupercall, dbtimepercall, callspersec, dbtimepersec 
                    FROM $met_tbl mt , $actserv_tbl ast
                    WHERE  service_name = ? AND end_time >= SYSDATE - 5/(60*24) AND group_id = 6 and mt.service_name = ast.name and mt.inst_id = ast.inst_id
                    ORDER BY mt.inst_id asc, end_time DESC
                };
    my $met_sth = $connection->prepare( $met_sql );
    if (!$met_sth)
    {
  	EMD_PERL_DEBUG( "em_error=prepare query failed for metric query, returning \n");
        print "em_error=prepare query failed for metric query, returning \n";
	return;
    }

    $met_sth->bind_param( 1, $srv_name, SQL_VARCHAR );
    $met_sth->execute();
    my( $instid, $sv_name, $beginTime, $endTime, $interval, $grpid, $cpupercall, $dbpercall, $callspersec, $dbtimepersec);
    $met_sth->bind_columns( undef, \$instid, \$sv_name, \$beginTime, \$endTime, \$interval, \$grpid, \$cpupercall, \$dbpercall, \$callspersec, \$dbtimepersec );

#    print "\n -- getServiceMetric:: Service metrics records for service $srv_name \n";
#    print "instid | service name | begin time | end time | interval | groupid | cpupercall | dbpercall | calls/sec | dbtime/sec \n";
    EMD_PERL_DEBUG( "\n -- getServiceMetric:: Service metrics records for service $srv_name \n");
##    EMD_PERL_DEBUG( "instid | service name | begin time | end time | interval | groupid | cpupercall | dbpercall | calls/sec | dbtime/sec \n");

    my $cpu_sql = qq{ SELECT value
                        FROM $param_tbl
                        WHERE inst_id = ? and stat_name='NUM_CPUS'
                    };
    my $cpu_core_sql = qq{ SELECT value
                        FROM $param_tbl
                        WHERE inst_id = ? and stat_name='NUM_CPU_CORES'
                    };

    my $inst_sql = qq{ SELECT instance_name
                    FROM $inst_tbl  
                    WHERE inst_id = ?
                };
    my $inst_sth = $connection->prepare( $inst_sql );

    # my $minresp = 1000000.0;
    my $minresp;
    my $maxresp;
    my $mininst = 1;
    my $maxinst = 1;    ##  need some value if all the resp times are 0
    my $timedif = 0.0;
    my $cpuload = 0.0;
    my $totalcpu = 0.0;
    my $availcpu = 0.0;
    my $avgResponse = 0.0;
    my $totalcalls = 0.0;
    my $service_status = '';
    my $running_instlist = '';
    my $lastinst = 0;
    my $totalcpuInst = 0.0;
    my $availinstcpuInst = 0.0;
    my $avgResponseInst = 0.0;
    my $totalcallsInst = 0.0;
    my $beginTimeInst;
    my $endTimeInst;
    my $instentries = 0;

    my $inst_cpu =0;
    my $cpu_sth = $connection->prepare( $cpu_sql );

    my $inst_cpu_cores =0;
    my $cpu_core_sth = $connection->prepare( $cpu_core_sql );

    my $inst_name = "";

    while( $met_sth->fetch() ) {
##      print "$instid, $sv_name, $beginTime, $endTime, $interval, $grpid, $cpupercall, $dbpercall, $callspersec, $dbtimepersec \n";
##        EMD_PERL_DEBUG( "$instid, $sv_name, $beginTime, $endTime, $interval, $grpid, $cpupercall, $dbpercall, $callspersec, $dbtimepersec \n");
        $timedif = $interval /100;  #interval is in 100th of sec
#print "timedif $timedif \n";

	if ($callspersec == 0)
  	{
	   $callspersec = 1;
	}

	if ($instid != $lastinst)
	{
            if ($lastinst != 0)
            {
    		if ($totalcallsInst != 0.0)
    		{
#print "$instid :: total dbtimeInst: $avgResponseInst, total cpu inst :  $totalcpuInst, total callsInst: $totalcallsInst \n";
		    $totalcpuInst = $totalcpuInst / $totalcallsInst;
		    $avgResponseInst = $avgResponseInst / $totalcallsInst;
    		}
              print "inst_result=$dbname|$srv_name|$lastinst|$inst_name|$beginTimeInst|$endTimeInst|$totalcpuInst|$avgResponseInst\n";
              EMD_PERL_DEBUG("inst_result=$dbname|$srv_name|$lastinst|$inst_name|$beginTimeInst|$endTimeInst|$totalcpuInst|$avgResponseInst\n");
            }

	$inst_cpu = $instcpu->{$instid};
	if (!defined $inst_cpu)
	{
           $cpu_sth->execute($instid);
           $cpu_sth->bind_columns( undef, \$inst_cpu );
           $cpu_sth->fetch();
	   $instcpu->{$instid} = $inst_cpu;
#           print "instance $instid, cpu count : $inst_cpu\n";
           EMD_PERL_DEBUG( "instance $instid, cpu count : $inst_cpu \n");
	}
	if ($inst_cpu == 0)
	{
	   $inst_cpu = 1;
	}

	$inst_cpu_cores = $instcore->{$instid};
	if (!defined $inst_cpu_cores)
	{
           $cpu_core_sth->execute($instid);
           $cpu_core_sth->bind_columns( undef, \$inst_cpu_cores );
           $cpu_core_sth->fetch();
	   $instcore->{$instid} = $inst_cpu_cores;
#           print "instance $instid, cpu core count: $inst_cpu_cores \n";
           EMD_PERL_DEBUG( "instance $instid, cpu core count: $inst_cpu_cores \n");
	}
	if ($inst_cpu_cores == 0)
	{
	   $inst_cpu_cores = 1;
	}

	$inst_name = $instnm->{$instid};
	if (!defined $inst_name)
	{
	    $inst_sth->execute($instid);
	    $inst_sth->bind_columns( undef, \$inst_name );
	    $inst_sth->fetch();
	    $instnm->{$instid} = $inst_name;
#            print "instance $instid, nm : $inst_name \n";
            EMD_PERL_DEBUG( "instance $instid, nm : $inst_name \n");
	}


 	   $lastinst = $instid;
           $totalcpuInst = 0.0;
           $availinstcpuInst = 0.0;
           $avgResponseInst = 0.0;
           $totalcallsInst = 0.0;
           $beginTimeInst = $beginTime;
           $instentries = 0;
        }

 	$totalcpuInst += ($cpupercall / 1000) * $callspersec * $timedif;
 	$availinstcpuInst += $timedif* 1000 * $inst_cpu * $inst_cpu_cores;   # get in to milliseconds	

	$avgResponseInst += ($dbpercall/1000) * $callspersec * $timedif;	
	$totalcallsInst += $callspersec * $timedif;	
        $endTimeInst = $endTime;

 	$totalcpu += ($cpupercall / 1000) * $callspersec * $timedif;
 	$availcpu += $timedif* 1000 * $inst_cpu * $inst_cpu_cores;   # get in to milliseconds	

##	$avgResponse += $dbpercall;	
###	$availcpu += $dbpercall * $callspersec * $timedif * $inst_cpu;	

	$avgResponse += ($dbpercall/1000) * $callspersec * $timedif;	
	$totalcalls += $callspersec * $timedif;	

 	if (! defined $minresp or ($dbpercall < $minresp))
	{
	   $minresp = $dbpercall;
	   $mininst = $instid;
	}
 	if ( ! defined $maxresp or ($dbpercall > $maxresp))
	{
	   $maxresp = $dbpercall;
	   $maxinst = $instid;
	}
    }

    if ($availcpu  != 0)
    {
        $cpuload = ($totalcpu/$availcpu) *100;
    }

    # print the last instance data
    if ($totalcallsInst != 0.0)
    {
#print "$instid :: total dbtimeInst: $avgResponseInst, total cpu inst :  $totalcpuInst, total callsInst: $totalcallsInst \n";
	$totalcpuInst = $totalcpuInst / $totalcallsInst;
	$avgResponseInst = $avgResponseInst / $totalcallsInst;
    }
    print "inst_result=$dbname|$srv_name|$lastinst|$inst_name|$beginTimeInst|$endTimeInst|$totalcpuInst|$avgResponseInst\n";
    EMD_PERL_DEBUG("inst_result=$dbname|$srv_name|$lastinst|$inst_name|$beginTimeInst|$endTimeInst|$totalcpuInst|$avgResponseInst\n");
   
#    if ($met_sth->rows != 0)
#    {
#        $avgResponse = $avgResponse / ($met_sth->rows);
#    }

#    print "total cputime: $totalcpu, avail cputime: $availcpu , cpuload % $cpuload\n";
    EMD_PERL_DEBUG("total cputime: $totalcpu, avail cputime: $availcpu, cpuload % $cpuload\n");

    if ($totalcalls != 0.0)
    {
        $avgResponse = $avgResponse / $totalcalls;
    }
#    print "total dbtime: $avgResponse, total calls: $totalcalls \n";
    EMD_PERL_DEBUG("total dbtime: $avgResponse, total calls: $totalcalls\n");

    my $mininst_name = '';
    my $maxinst_name = '';
    if (! defined $minresp)
    {
	$minresp = 0.0;
    }
    else
    {
	$mininst_name = $instnm->{$mininst};
        if (!defined $mininst_name)
        {
    	   $inst_sth->execute($mininst);
    	   $inst_sth->bind_columns( undef, \$mininst_name );
    	   $inst_sth->fetch();
	}
    }
    if (! defined $maxresp)
    {
	$maxresp = 0.0;
    }
    else
    {
	$maxinst_name = $instnm->{$maxinst};
        if (!defined $maxinst_name)
        {
    	   $inst_sth->execute($maxinst);
    	   $inst_sth->bind_columns( undef, \$maxinst_name );
    	   $inst_sth->fetch();
	}
    }

    $minresp = $minresp/1000;
    $maxresp = $maxresp/1000;

#    print "min resp: $minresp, min instid : $mininst, min instname: $mininst_name \n";
#    EMD_PERL_DEBUG( "min resp: $minresp, min instid : $mininst, min instname: $mininst_name \n");
#    print "max resp: $maxresp, max instid : $maxinst, max instname: $maxinst_name \n";
#    EMD_PERL_DEBUG( "max resp: $maxresp, max instid : $maxinst, max instname: $maxinst_name \n");

    my $stat_sql = qq{ SELECT s.inst_id, s.name, i.instance_name
                    FROM $actserv_tbl s, $inst_tbl i 
                    WHERE  s.name = ? AND s.inst_id = i.inst_id
                };
    my $stat_sth = $connection->prepare( $stat_sql );
    $stat_sth->execute($srv_name);
    my( $stat_instid, $stat_sv_name, $stat_instnm);
    $stat_sth->bind_columns( undef, \$stat_instid, \$stat_sv_name, \$stat_instnm );
    my $idx = 0;
    while( $stat_sth->fetch() ) {
#       print "$stat_instid, $stat_sv_name, $stat_instnm \n";
#        EMD_PERL_DEBUG( "$stat_instid, $stat_sv_name, $stat_instnm , $idx \n");
        if ($idx != 0)
        {
            $running_instlist = $running_instlist.",";
        }
        $running_instlist = $running_instlist.$stat_instnm;
	$idx++;
    }

    if ($stat_sth->rows != 0)
    {
        $service_status = "Up";
    }
    else
    {
        $service_status = "Down";
    }

    if ($srv_name eq 'seeddata' or $srv_name eq 'seeddataXDB')
    {
        $service_status = "";
    }
# ignore service if down and standby role. 
   if ($service_status eq "Down" && $dbrole !~ /PRIMARY/i)
   {
      EMD_PERL_DEBUG( "Ignoring $service_status service $srv_name for $dbrole.\n");
   }
   else
   {
    print "em_result=$dbname|$srv_name|$minresp|$mininst_name|$maxresp|$maxinst_name|$avgResponse|$cpuload|$service_status|$running_instlist\n";
    EMD_PERL_DEBUG( "em_result=$dbname|$srv_name|$minresp|$mininst_name|$maxresp|$maxinst_name|$avgResponse|$cpuload|$service_status|$running_instlist\n");
   }
}