最終版-perl工具解析資料庫的報告檔案0120

標籤：

 

********************需要根據自己的實際環境修改哦****************************

********************

1. 收集awr報告樣本   awrreport.sql

--該指令碼請用具有 dba 許可權的使用者執行，普通使用者沒有許可權訪問資料庫的基表

conn &usr/ &pass @ &oracle_sid

set linesize 1200 ;
set pagesize 0;
set long 99999;
set heading off;
--set termout off;
set echo off;
set feedback off;
set timing off;
set serveroutput on;

exec dbms_output.put_line(‘report_name，for example：hydk‘);
spool awrrpt_tmp.sql;

select
‘spool &report_name‘||‘_awrrpt_‘||snap_id||‘_‘||(snap_id+1)||‘.lst‘||chr(10)||
‘select output ‘||chr(10)||
‘ from table(dbms_workload_repository.awr_report_text(‘||dbid||‘,1,‘||snap_id||‘,‘||(snap_id+1)||‘));‘||chr(10)||‘spool off;‘||chr(10)
from dba_hist_snapshot;
whenever sqlerror continue;
spool off ;
@awrrpt_tmp.sql;

---- 這裡要等一段時間，多敲幾下斷行符號以保證上面的語句都執行 ---

host del awrrpt_tmp.sql;
exit;

 

********************

2.    .bat檔案 批量執行sql指令碼，避免 重複的複製粘貼

@echo off
echo ***************************************************
echo * *
echo * 此指令碼用於資料庫的awr報告檔案 *
echo * *
echo ***************************************************
echo
echo 請按照[ ]中的提示輸入參數，如不輸入，則自動設為預設
echo.

rem -------------------------------------------------------
%~d0
cd %~dp0

set setup=setup.sql

set NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK

echo set echo off >> %setup%
echo set verify off >> %setup%
rem -------------------------------------------------------------------
rem 指令碼內容寫入setup.sql一起運行
echo.

set model=

if "%model%"=="1" goto model1
if "%model%"=="" goto model1

goto end

:model1
echo @./start/awrreport.sql >> %setup%
goto next

:next
rem 運行指令碼

echo exit >> %setup%
sqlplus /nolog @%setup%
echo 運行成功
del setup.sql
::exit

:end

exit

 

********************

3. perl工具 解析 產生的  .lst 檔案

rem 該指令碼用於使用perl工具 解析 產生的 .lst 檔案 （以實際環境為準）
rem 進入指定盤符
c:

rem 進入該盤符下的指定目錄，改盤符就是 那些.lst檔案的目錄
cd C:\Users\Administrator\Desktop\mon_ora11g\指令碼產生awr報告檔案

rem 使用絕對路徑下的perl工具，解析 產生的 .lst 檔案
E:\app\oracle\product\11.2.0\db_1\perl\bin\perl.exe -w awrreport.pl

 

********************

 

4. perl 工具的 設定檔

use strict;
#use File::Find;
#

my $root_dir=‘.‘;
open(DataFile, ">$root_dir\\fzjk.txt") || die "can not create file";

print DataFile " 日期 ", " 時間 ", " Redo size",
" Logical reads", " Block changes",
" Physical reads", " Physical writes",
" User calls", " Parses",
" Hard parses",
" Logons", " Executes",
" Transactions",
"\n";

print DataFile " PerSecond PerTransaction PerSecond PerTransaction",
" PerSecond PerTransaction", " PerSecond PerTransaction",
" PerSecond PerTransaction", " PerSecond PerTransaction",
" PerSecond PerTransaction", " PerSecond PerTransaction",
" PerSecond PerTransaction",
" PerSecond PerTransaction",
" PerSecond",
"\n";

my @xljk = ("Buffer Nowait", "Redo NoWait", "Buffer Hit", "In-memory Sort",
"Library Hit", "Soft Parse", "Execute to Parse", "Latch Hit","Parse CPU to Parse Elapsd",
"Non-Parse CPU");
open(XLFile, ">$root_dir\\xljk.txt") || die "can not create file";

print XLFile " 日期 ", " 時間 ";

foreach my $i ([email protected])
{
print XLFile " $xljk[$i]| ";
}
print XLFile "\n";

open(GXZFile, ">$root_dir\\gxcjk.txt") || die "can not create file";
my @gxzjk = ( "Memory Usage ","SQL with executions>1","Memory for SQL w/exec>1");
print GXZFile " 日期 ", " 時間 ";

foreach my $i ([email protected])
{
print GXZFile " $gxzjk[$i]| ";
}

print GXZFile "\n begin end ave begin end begin end";
print GXZFile "\n";

 

#下例僅遍曆目前的目錄：
my($file);
my @filename;

 

opendir(myDir, $root_dir);
while ($file = readdir myDir)
{

#@filename = (@filename, $file) if ($file =~/lst$/i);
if ($file =~/lst$/i)
{
@filename = (@filename, $file);
#$file =~/(\d+)\D*(\d+)/;
#print $1, $2, "\n";
}
}
closedir(myDir);
#print @filename, "\n";

#sub subdig($a)

#
@filename = sort {
$_=$a;
m/(\d+)\D*/;
my $aa = $1;
#print $aa;

$_ = $b;
m/(\d+)\D*/;
#print " ", $1, "\n";
$aa <=> $1; } @filename;


my @xlpat = ("Buffer Nowait %:", "Redo NoWait %:", "Buffer Hit %:", "In-memory Sort %:",
"Library Hit %:", "Soft Parse %:", "Execute to Parse %:", "Latch Hit %:",
"Parse CPU to Parse Elapsd %:", "% Non-Parse CPU:");
my @xlpatlen = (13,11,11,14,11,10,16,9,25,13);

my @gxzpat = ("Memory Usage %:","% SQL with executions>1:"," % Memory for SQL w/exec>1:");
my @gxzpatlen = (12,21,23);

foreach my $i ([email protected])
{
open(Spreport, "$root_dir\\$filename[$i]" ) || warn "can not open file\n";
#print DataFile $root_dir ."\\". $filename[$i], "\n";

while(my $line = <Spreport> ) #擷取檔案中的每一行
{
if( $line =~/Begin Snap:\s+\d+\s+(\S+)\s*(\S+)\s*(\S+)/ )
{



if(index($3,":")!=-1)
{print DataFile $1,$2,$3, " ";
print XLFile $1,$2,$3, " ";
print GXZFile $1,$2,$3, " ";
}
else
{print DataFile $1,$2," ", " ";
print XLFile $1,$2, " ";
print GXZFile $1,$2, " ", " ";
}

}

#Redo size
if(($line =~/Redo size\s+(\S+)\s*(\S+):\s+(\S+)\s*(\S+)/ || $line =~/Redo size:\s+(\S+)\s*(\S+)/)&& $line =~/:\s+(\S+)\s*(\S+)/ )
{
# $line =~/:\s+(\S+)\s*(\S+)/;
printf(DataFile "%-8s %-12s ",$1,$2);
#print DataFile $1," ", $2, " ";
}

#Logical read
if(($line =~/Logical read\s+(\S+)\s*(\S+):\s+(\S+)\s*(\S+)/ || $line =~/Logical reads:\s+(\S+)\s*(\S+)/)&& $line =~/:\s+(\S+)\s*(\S+)/ )
#if( $line =~/Logical reads\s+(\S+)\s*(\S+):\s+(\S+)\s*(\S+)/ || $line =~/Redo sizes:\s+(\S+)\s*(\S+)/)&& $line =~/:\s+(\S+)\s*(\S+)/ )
{
printf(DataFile "%-9s %-12s ",$1,$2);
}

#Block changes
if(($line =~/Block changes\s+(\S+)\s*(\S+):\s+(\S+)\s*(\S+)/ || $line =~/Block changes:\s+(\S+)\s*(\S+)/)&& $line =~/:\s+(\S+)\s*(\S+)/ )
#if( $line =~/Block changes\s+(\S+)\s*(\S+):\s+(\S+)\s*(\S+)/ &&$line =~/:\s+(\S+)\s*(\S+)/ )
{
printf(DataFile "%-8s %-12s ",$1,$2);
}

#Physical read

if(($line =~/Physical read\s+(\S+)\s*(\S+):\s+(\S+)\s*(\S+)/ || $line =~/Physical reads:\s+(\S+)\s*(\S+)/)&& $line =~/:\s+(\S+)\s*(\S+)/ )

#if(($line =~/Logical read\s+(\S+)\s*(\S+):\s+(\S+)\s*(\S+)/ || $line =~/Logical reads:\s+(\S+)\s*(\S+)/)&& $line =~/:\s+(\S+)\s*(\S+)/ )
#if( $line =~/Physical reads\s+(\S+)\s*(\S+):\s+(\S+)\s*(\S+)/ &&$line =~/:\s+(\S+)\s*(\S+)/ )
{
printf(DataFile "%-8s %-12s ",$1,$2);
}

#Physical write
if(($line =~/Physical write\s+(\S+)\s*(\S+):\s+(\S+)\s*(\S+)/ || $line =~/Physical writes:\s+(\S+)\s*(\S+)/)&& $line =~/:\s+(\S+)\s*(\S+)/ )
#if( $line =~/User calls\s+(\S+)\s*(\S+):\s+(\S+)\s*(\S+)/ &&$line =~/:\s+(\S+)\s*(\S+)/ )
{
printf(DataFile "%-8s %-12s ",$1,$2);
}

#User calls

if(($line =~/User calls\s+(\S+)\s*(\S+):\s+(\S+)\s*(\S+)/ || $line =~/User calls:\s+(\S+)\s*(\S+)/)&& $line =~/:\s+(\S+)\s*(\S+)/ )
#if(($line =~/User calls\s+(\S+)\s*(\S+):\s+(\S+)\s*(\S+)/ || $line =~/User callss+(\S+)\s*(\S+)/)&& $line =~/:\s+(\S+)\s*(\S+)/ )
{
printf(DataFile "%-8s %-12s ",$1,$2);
}


#Parses
if(($line =~/Parses\s+(\S+)\s*(\S+):\s+(\S+)\s*(\S+)/ || $line =~/Parses:\s+(\S+)\s*(\S+)/)&& $line =~/:\s+(\S+)\s*(\S+)/ )
#if( $line =~/parses\s+(\S+)\s*(\S+):\s+(\S+)\s*(\S+)/ &&$line =~/:\s+(\S+)\s*(\S+)/ )
{
printf(DataFile "%-8s %-12s ",$1,$2);
}

#Hard parses
if(($line =~/Hard parses\s+(\S+)\s*(\S+):\s+(\S+)\s*(\S+)/ || $line =~/Hard parses:\s+(\S+)\s*(\S+)/)&& $line =~/:\s+(\S+)\s*(\S+)/ )
#if( $line =~/Hard parses\s+(\S+)\s*(\S+):\s+(\S+)\s*(\S+)/ &&$line =~/:\s+(\S+)\s*(\S+)/ )
{
printf(DataFile "%-8s %-12s ",$1,$2);
}

# if(($line =~/Physical write\s+(\S+)\s*(\S+):\s+(\S+)\s*(\S+)/ || $line =~/Physical write:\s+(\S+)\s*(\S+)/)&& $line =~/:\s+(\S+)\s*(\S+)/ )
# if( $line =~/Sorts\s+(\S+)\s*(\S+):\s+(\S+)\s*(\S+)/ &&$line =~/:\s+(\S+)\s*(\S+)/ )
# {
# printf(DataFile "%-8s %-12s ",$1,$2);
# }

#Logons

if(($line =~/Logons\s+(\S+)\s*(\S+):\s+(\S+)\s*(\S+)/ || $line =~/Logons:\s+(\S+)\s*(\S+)/)&& $line =~/:\s+(\S+)\s*(\S+)/ )
#if( $line =~/Logons\s+(\S+)\s*(\S+):\s+(\S+)\s*(\S+)/ &&$line =~/:\s+(\S+)\s*(\S+)/ )
{
printf(DataFile "%-8s %-12s ",$1,$2);
}

#Executes
if(($line =~/Executes\s+(\S+)\s*(\S+):\s+(\S+)\s*(\S+)/ || $line =~/Executes:\s+(\S+)\s*(\S+)/)&& $line =~/:\s+(\S+)\s*(\S+)/ )
#if( $line =~/Executes\s+(\S+)\s*(\S+):\s+(\S+)\s*(\S+)/ &&$line =~/:\s+(\S+)\s*(\S+)/ )
{
printf(DataFile "%-8s %-12s ",$1,$2);
}

#Transactions

if(($line =~/Transactions\s+(\S+)\s*(\S+):\s+(\S+)\s*(\S+)/ || $line =~/Transactions:\s+(\S+)\s*(\S+)/)&& $line =~/:\s+(\S+)\s*(\S+)/ )
#if( $line =~/Transactions\s+(\S+)\s*(\S+):\s+(\S+)\s*(\S+)/ &&$line =~/:\s+(\S+)\s*(\S+)/ )
{
printf(DataFile "%-8s",$1);
}

foreach my $j ([email protected])
{

printf(XLFile "%-$xlpatlen[$j]s ", $1) if($line =~/$xlpat[$j]\s+(\S+)/ );
}

foreach my $j ([email protected])
{
if($line =~/$gxzpat[$j]\s+(\S+)\s+(\S+)/ )
{
printf(GXZFile "%-6s %-6s ", $1, $2) ;
printf(GXZFile "%-7s ",($1+$2)/2) if($j==0);
printf GXZFile " " if($j==1);
}
}

}
print DataFile "\n";
print XLFile "\n";
print GXZFile "\n";

close Spreport;
}

close DataFile;
close XLFile;
close GXZFile;

 

最終版-perl工具解析資料庫的報告檔案0120