Chinaunix首页 | 论坛 | 博客
  • 博客访问: 843079
  • 博文数量: 150
  • 博客积分: 5123
  • 博客等级: 大校
  • 技术积分: 1478
  • 用 户 组: 普通用户
  • 注册时间: 2008-11-06 10:03
文章分类

全部博文(150)

文章存档

2011年(2)

2010年(139)

2009年(9)

分类: Oracle

2010-08-31 16:12:27

此脚本使用perl,此脚本是通过sqlplus连接到oracle提取性能数据库的,所以并不需要安装DBI。

#!/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;
}


阅读(1384) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~