Final version of-perl Tool parsing database report file 0120

Need to modify according to your actual environment Oh ****************************

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

1. Collect the AWR report sample Awrreport.sql

--the script is executed with a user with DBA authority, and the normal user does not have access to the base table of the database

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;

----Here to wait for a while, knock a few more returns to ensure that the above statements are executed---

Host del Awrrpt_tmp.sql;
Exit

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

2.. bat file executes SQL scripts in bulk to avoid duplicate copy and paste

@echo off
Echo ***************************************************
Echo * *
echo * This script is used for the AWR report file of the database *
Echo * *
Echo ***************************************************
Echo
echo follow the instructions in [] to enter parameters, if not entered, automatically set as default
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 Script content write Setup.sql run together
Echo.

Set model=

If "%model%" = = "1" goto MODEL1
If "%model%" = = "goto Model1

Goto END

: Model1
echo @./start/awrreport.sql >>%setup%
Goto Next

: Next
REM Run script

Echo Exit >>%setup%
Sqlplus/nolog @%setup%
Echo Run successfully
Del Setup.sql
:: Exit

: End

Exit

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

3. The Perl tool parses the generated. lst file

REM The script is used to parse the generated. lst file using the Perl tool (whichever is the actual environment)
REM enters the specified drive letter
C:

REM enters the specified directory under the drive letter, which is the directory of the. lst files
CD C:\Users\Administrator\Desktop\mon_ora11g\ script generates AWR report file

REM uses the Perl tool under absolute path to parse the generated. lst file
E:\app\oracle\product\11.2.0\db_1\perl\bin\perl.exe-w awrreport.pl

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

4. Configuration files for Perl tools

Use strict;
#use File::find;
#

My $root _dir= '. ';
Open (DataFile, "> $root _dir\\fzjk.txt") | | Die "Can not create file";

Print datafile "Date", "Time", "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 "Date", "Time";

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 "Date", "Time";

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 $, $, "\ n";
}
}
Closedir (MyDir);
#print @filename, "\ n";

#sub Subdig ($a)

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

$_ = $b;
m/(\d+) \d*/;
#print "", $ $, "\ n";
$aa <=> $; } @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 ($ $, ":")!=-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 $, "", $, "";
}

#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", $ $);
}

foreach My $j ([email protected])
{

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

foreach My $j ([email protected])
{
if ($line =~/$gxzpat [$j]\s+ (\s+) \s+ (\s+)/)
{
printf (gxzfile "%-6s%-6s", $, $);
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;

Final version of-perl Tool parsing database report file 0120