实时显示oracle运行状态的脚本

#!/usr/bin/perl -w

#function: show database statistics every two seconds

#Tangcheng 2008.10.29


use Time::HiRes qw(gettimeofday);
use IPC::Open2;

my $cfgfile;
my $dbuser;
my $dbpass;


my $dblink ='/ as sysdba';
our $exitflag = 0;

$SIG{"INT"} = 'HandleINT';
$SIG{"TSTP"}= 'HandleINT';

@statname = (
'consistent gets',
'physical reads',
'physical writes',
'logons cumulative',
'parse count (hard)',
'parse count (total)',
'redo entries',
'redo writes',
'sorts (disk)',
'sorts (memory)',
'sorts (rows)',
'user calls',
'user commits',
'user rollbacks',
'bytes received via SQL*Net from client',
'bytes sent via SQL*Net to client',
'bytes received via SQL*Net from dblink',
'bytes sent via SQL*Net to dblink',
'leaf node splits'
);


$sql="
SELECT rpad(b.name,64,' ')||'|'||a.value
  FROM v\$sysstat a,v\$statname b
 where a.statistic#=b.statistic#
   and b.name in
   (";
$i = 0;
while ($i <@statname)
{
    if($i == 0)
    {
        $sql=$sql."'".$statname[$i]."'";
    }
    else
    {
        $sql=$sql.",'".$statname[$i]."'";
    }
    $i++;
}
$sql=$sql.")";

system("clear");
print `tput cup 0 4`;
print "value/seconds statistic_name\n";
print `tput cup 1 4`;
print "--------------- ---------------------------------------------------------------\n";
my $db=connectdb($dblink);

if($$db[6] ne "")
{
    print "Connect database Error:\n";
    print $db[6];
    return 1;
}

my @res;
my $pp;
my $isfirst=1;
my @oldstat;
my @currstat;
my $row;

($curr_sec, $curr_usec) = gettimeofday;
$curr_sec=$curr_sec - 2;

while($exitflag==0)
{
    $pre_sec=$curr_sec;
    $pre_usec=$curr_usec;

    executedb($db,$sql);
    ($curr_sec, $curr_usec) = gettimeofday;
    $time_used = ($curr_sec - $pre_sec) + ($curr_usec - $pre_usec)/1000000;

    @res=getdbresult($db); #get sql result

    foreach $line (@res) #get every line of result

    {
        $pp=index($line,"|");
        $currname=substr($line,0,$pp-1);
        $currname=~ s/\s+$//; #trim space

        $currvalue=substr($line,$pp+1);
        $currvalue=~ s/\n$//; #trim \n

        $currstat{$currname}=$currvalue;
    }

    for($i=0;$i<@statname;$i++)
    {
        if($isfirst != 1)
        {
            $row=$i+2;
            print `tput cup $row 4`;
            printf("%15lu %s\n",($currstat{$statname[$i]} - $oldstat{$statname[$i]})/$time_used,$statname[$i]);
        }
        $oldstat{$statname[$i]} = $currstat{$statname[$i]};
    }
    if($isfirst==1)
    {
        $row=@statname + 3;
        print `tput cup $row 4`;
        print "Press ctrl +c to quit...\n";
    }
    $isfirst=0;
    
    sleep(2);
}
$row=@statname+4;
print `tput cup $row 0`;
closedb($db);

sub HandleINT
{
    $exitflag=1;
}


###########################Database function#########################

sub connectdb
{
    my $dburl = shift;
    my @dbhandle;
    my $childpid;
    my @cmdarg;
    $cmdarg[0]="/nolog";
    my $line;
    my $errmsg;

    $ENV{'SQLPATH'}='';

    $childpid=open2(*SQLPLUS_OUT,*SQLPLUS_IN,'sqlplus',@cmdarg);

    print SQLPLUS_IN "connect ".$dburl.";\n";
    flock(SQLPLUS_IN,1);

    $errmsg='';
    while($line=<SQLPLUS_OUT>)
    {
        if(index($line,"Connected")!=-1)
        {
            last;
        }
        if(index($line,"ERROR:")!=-1)
        {
            $errmsg=<SQLPLUS_OUT>;
            last;
        }
    }

    if($errmsg eq "")
    {
        print SQLPLUS_IN "SET SQLPROMPT ''\n";
        flock(SQLPLUS_IN,1);
        print SQLPLUS_IN "set heading off\n";
        print SQLPLUS_IN "set linesize 4000\n";
        print SQLPLUS_IN "set feedback off\n";
        flock(SQLPLUS_IN,1);
    }

    $dbhandle[0]=\*SQLPLUS_OUT;
    $dbhandle[1]=\*SQLPLUS_IN;
    $dbhandle[2]=$childpid;
    $dbhandle[3]=0; #If return data

    $dbhandle[4]=0; #data row is start

    $dbhandle[5]=0;
    $dbhandle[6]=$errmsg;
    return \@dbhandle;
}

sub closedb
{
    my $db=shift;
    my $IN=$$db[1];
    my $OUT=$$db[0];
    print $IN "\nexit;\n";
    flock($IN,1);
    waitpid($$db[2], 0);
    close($IN);
    close($OUT);
}

sub executedb
{
    my ($db,$sql)=@_;
    my $IN =$$db[1];
    $sql =~ s/\n/ /g;
    print $IN "prompt start_data_FWdeQ7gq34ygrQwjSFls\n";
    print $IN $sql.";\n";
    flock($IN,1);
    print $IN "prompt end_data_FWdeQ7gq34ygrQwjSFls\n";
    flock($IN,1);
    $$db[3]=1; #if return data

}

sub getdbresult
{
    my $db= shift;
    my $OUT=$$db[0];
    my @result;
    my $datastart=0;
    if($$db[3]==0) #no return data

    {
        return "";
    }
    my $i=0;
    while($line=<$OUT>)
    {
        if($line eq "\n")
        {
            next;
        }
        if( $line =~ m/start_data_FWdeQ7gq34ygrQwjSFls/)
        {
            $datastart=1;
            next;
        }
        if($datastart==1)
        {
            if( $line =~ m/end_data_FWdeQ7gq34ygrQwjSFls/)
            {
                last;
            }
            $result[$i]=$line;
            $i++;
        }
    }

    return @result;
}