#!/usr/bin/env perl
#
# Author: Bertrand Drouvot
# Visit my blog : https://bdrouvot.wordpress.com/
# V1.0 (2014/04)
# V1.1 (2014/05)
#
# Description:
# Utility used to display database IO metrics in real time per snap or per average since the collection began.
# It basically takes a snapshot each second (default interval) of the gv$filestat and gv$tempstat cumulative views and computes the delta
# with the previous snapshot.
# The utility is RAC and Multitenant aware.
# You have to set oraenv on one DB instance
# You can choose the number of snapshots to display and the time to wait between snapshots.
#
# Usage:
# ./db_io_metrics.pl -help
#
# Chek for new version : https://bdrouvot.wordpress.com/db_io_metrics_script/
# v1.1 (2014/04): display "large" metrics nicely
# v1.1 (2014/04): sort the display on the first column if needed
#
#----------------------------------------------------------------#
BEGIN {
die "ORACLE_HOME not set\n" unless $ENV{ORACLE_HOME};
unless ($ENV{OrAcLePeRl}) {
$ENV{OrAcLePeRl} = "$ENV{ORACLE_HOME}/perl";
$ENV{PERL5LIB} = "$ENV{PERL5LIB}:$ENV{OrAcLePeRl}/lib:$ENV{OrAcLePeRl}/lib/site_perl";
$ENV{LD_LIBRARY_PATH} = "$ENV{LD_LIBRARY_PATH}:$ENV{ORACLE_HOME}/lib32:$ENV{ORACLE_HOME}/lib";
exec "$ENV{OrAcLePeRl}/bin/perl", $0, @ARGV;
}
}
use strict;
use DBI;
use DBD::Oracle qw(:ora_session_modes);
use Getopt::Long;
our %options;
our $debug=0;
our $version;
our $nb_pdbs=0;
our $interval=1;
our $count=999999;
our $showinst=0;
our $rac=0;
our $inst_type='RDBMS';
our $dbh;
our $instpattern='all';
our $fs_delimiter='oradata';
our $fs_dg_pattern='';
our $tbs_pattern='';
our $cont_pattern='';
our $file_pattern='';
our $show_pattern='inst';
our $display_pattern='snap';
our $dg_suffixe='';
our $instid_pattern='inst_id';
our $sqlsuffixe;
our $sql1;
our $main_sql='';
our %instances;
our %showinstances=();
our %sql_patterns;
our %diffsnaps;
our %avgdiffsnaps=();
our %rtvalues;
our %pkeys;
our $bkey;
our @ekey;
our %ckeys=();
our @array_of_ckeys_description=();
our @array_of_display_keys=();
our @array_of_ckey=();
our @delta_fields;
our $global_sql_pattern='';
our @array_of_report_header;
our $report_format_values;
our @report_fields_values;
our $seconds;
our $minuts;
our $hours;
our @since_timing;
our %sort_fields;
our $sort_field_pattern='';
sub main {
&get_the_options(@ARGV);
$instid_pattern="inst_id";
my $ckey_cpt=0;
&connect_db;
$version=check_version();
if ($version >= 12) {$nb_pdbs=check_pdbs()};
if ($nb_pdbs == 0) {
$sql_patterns{'fs_dg'}=$fs_dg_pattern;
$sql_patterns{'tbs_name'}=$tbs_pattern;
$sql_patterns{'file_name'}=$file_pattern;
$sort_fields{4}='READS';
$sort_fields{5}='WRITES';
$sort_fields{11}='NONE';
$sort_fields{12}='IOPS';
%sort_fields = reverse %sort_fields;
if (!$sort_field_pattern) {$sort_field_pattern='NONE'};
$main_sql="
select inst_id,fs_dg,file#,tbs_name,PHYRDS,PHYWRTS,READTIM,WRITETIM,PHYBYTESRD,PHYBYTESWR,file_name,interval,iops
from
(select fs.inst_id as inst_id,case when regexp_like (df.name,'\^\\+') then substr(df.name,2,instr(df.name,'/',1)-2) else substr(df.name,1,instr(df.name,'/$fs_delimiter',-1)) end as fs_dg,fs.file# as file#,tbs.name as tbs_name,fs.PHYRDS as PHYRDS,fs.PHYWRTS as PHYWRTS,fs.READTIM*10 as READTIM,fs.WRITETIM*10 as WRITETIM, fs.PHYBLKRD*df.block_size/1024 as PHYBYTESRD,fs.PHYBLKWRT*df.block_size/1024 as PHYBYTESWR,substr(df.name,instr(df.name,'/',-1)+1) as file_name,$interval as interval,fs.PHYRDS+fs.PHYWRTS as iops
from
gv\$filestat fs, gv\$datafile df, gv\$tablespace tbs where fs.inst_id=df.inst_id and tbs.inst_id=df.inst_id and fs.file#=df.file# and tbs.ts#=df.ts#
union all select fs.inst_id as inst_id,case when regexp_like (df.name,'\^\\+') then substr(df.name,2,instr(df.name,'/',1)-2) else substr(df.name,1,instr(df.name,'/$fs_delimiter',-1)) end as fs_dg,fs.file# as file#,tbs.name as tbs_name,fs.PHYRDS as PHYRDS,fs.PHYWRTS as PHYWRTS,fs.READTIM*10 as READTIM,fs.WRITETIM*10 as WRITETIM,fs.PHYBLKRD*df.block_size/1024 as PHYBYTESRD,fs.PHYBLKWRT*df.block_size/1024 as PHYBYTESWR,substr(df.name,instr(df.name,'/',-1)+1) as file_name,$interval as interval,fs.PHYRDS+fs.PHYWRTS as iops
from
gv\$tempstat fs, gv\$tempfile df, gv\$tablespace tbs where fs.inst_id=df.inst_id and tbs.inst_id=df.inst_id and fs.file#=df.file# and tbs.ts#=df.ts#
) where 1=1
";
$pkeys{0}='%30s';
$pkeys{2}='%30s';
$pkeys{10}='%100s';
# What need to be show
my @show_fields = split (/,/,$show_pattern);
foreach my $show (@show_fields) {
if ($show =~ m/^inst$/i ){
# group by instance
$array_of_ckeys_description[$ckey_cpt]{0}='%30s';
$array_of_ckeys_description[$ckey_cpt]{11}='%10s';
$array_of_display_keys[$ckey_cpt]{0}='y';
$array_of_display_keys[$ckey_cpt]{11}='y';
$ckey_cpt=$ckey_cpt+1;
}
if ($show =~ m/^fs_dg$/i ){
# group by fs or dg
$array_of_ckeys_description[$ckey_cpt]{11}='%10s';
$array_of_display_keys[$ckey_cpt]{11}='y';
if (grep (/^inst$/i,@show_fields)) {
$array_of_ckeys_description[$ckey_cpt]{0}='%30s';
$array_of_display_keys[$ckey_cpt]{0}='y';
}
$array_of_ckeys_description[$ckey_cpt]{1}='%100s';
$array_of_display_keys[$ckey_cpt]{1}='y';
$ckey_cpt=$ckey_cpt+1;
}
if ($show =~ m/^tbs$/i ){
# group by tablespace
$array_of_ckeys_description[$ckey_cpt]{11}='%10s';
$array_of_display_keys[$ckey_cpt]{11}='y';
if (grep (/^inst$/i,@show_fields)) {
$array_of_ckeys_description[$ckey_cpt]{0}='%30s';
$array_of_display_keys[$ckey_cpt]{0}='y';
}
if (grep (/^fs_dg$/i,@show_fields)) {
$array_of_ckeys_description[$ckey_cpt]{1}='%100s';
$array_of_display_keys[$ckey_cpt]{1}='y';
}
$array_of_ckeys_description[$ckey_cpt]{3}='%60s';
$array_of_display_keys[$ckey_cpt]{3}='y';
$ckey_cpt=$ckey_cpt+1;
}
if ($show =~ m/^file$/i ){
# group by datafiles
$array_of_ckeys_description[$ckey_cpt]{11}='%10s';
$array_of_display_keys[$ckey_cpt]{11}='y';
if (grep (/^inst$/i,@show_fields)) {
$array_of_ckeys_description[$ckey_cpt]{0}='%30s';
$array_of_display_keys[$ckey_cpt]{0}='y';
}
if (grep (/^fs_dg$/i,@show_fields)) {
$array_of_ckeys_description[$ckey_cpt]{1}='%100s';
$array_of_display_keys[$ckey_cpt]{1}='y';
}
if (grep (/^tbs$/i,@show_fields)) {
$array_of_ckeys_description[$ckey_cpt]{3}='%60s';
$array_of_display_keys[$ckey_cpt]{3}='y';
}
$array_of_ckeys_description[$ckey_cpt]{10}='%100s';
$array_of_display_keys[$ckey_cpt]{10}='y';
$ckey_cpt=$ckey_cpt+1;
}
}
@delta_fields=(4,5,6,7,8,9,12);
$report_format_values="%02d:%02d:%02d %1s %-10s %1s %-16s %1s %-14s %1s %-13s %1s %-7.0f %1s %-7.0f %1s %-7.1f %1s %-7.0f %1s %-8.0f %1s %-7.0f %1s %-8.1f %1s %-7.0f\n";
@report_fields_values=(1,3,10,"4/11","8/11","6/4","8*1024/4","5/11","9/11","7/5","9*1024/5");
@array_of_report_header=(["%02d:%02d:%02d %66s %-7s %1s %-7s %1s %-7s %1s %-8s %11s %-7s %1s %-8s %1s %-7s\n",'','','','Kby','','Avg','','AvgBy/','','Kby','','Avg','','AvgBy/'],["%02d:%02d:%02d %1s %-10s %1s %-16s %1s %-14s %1s %-13s %1s %-7s %1s %-7s %1s %-7s %1s %-7s %1s %-8s %1s %-7s %1s %-8s %1s %-7s\n",'','INST','','FS_DG','','TBS','','FILE','','Reads/s','','Read/s','','ms/Read','','Read','','Writes/s','','Write/s','','ms/Write','','Write'],["%02d:%02d:%02d %1s %-10s %1s %-16s %1s %-14s %1s %-13s %1s %-7s %1s %-7s %1s %-7s %1s %-7s %1s %-8s %1s %-7s %1s %-8s %1s %-7s\n",'','----------','','----------------','','--------------','','-------------','','-------','','-------','','-------','','------','','--------','','-------','','--------','','------']);
} else {
#There is pdbs
$sql_patterns{'fs_dg'}=$fs_dg_pattern;
$sql_patterns{'tbs_name'}=$tbs_pattern;
$sql_patterns{'cont'}=$cont_pattern;
$sql_patterns{'file_name'}=$file_pattern;
$sort_fields{5}='READS';
$sort_fields{6}='WRITES';
$sort_fields{12}='NONE';
$sort_fields{13}='IOPS';
%sort_fields = reverse %sort_fields;
if (!$sort_field_pattern) {$sort_field_pattern='NONE'};
$main_sql="
select inst_id,cont,fs_dg,file#,tbs_name,PHYRDS,PHYWRTS,READTIM,WRITETIM,PHYBYTESRD,PHYBYTESWR,file_name,interval,iops
from
(select fs.inst_id as inst_id,c.name as cont,case when regexp_like (df.name,'\^\\+') then substr(df.name,2,instr(df.name,'/',1)-2) else substr(df.name,1,instr(df.name,'/$fs_delimiter',-1)) end as fs_dg,fs.file# as file#,tbs.name as tbs_name,fs.PHYRDS as PHYRDS,fs.PHYWRTS as PHYWRTS,fs.READTIM*10 as READTIM,fs.WRITETIM*10 as WRITETIM, fs.PHYBLKRD*df.block_size/1024 as PHYBYTESRD,fs.PHYBLKWRT*df.block_size/1024 as PHYBYTESWR,substr(df.name,instr(df.name,'/',-1)+1) as file_name,$interval as interval,fs.PHYRDS+fs.PHYWRTS as iops
from
gv\$filestat fs, gv\$datafile df, gv\$tablespace tbs,gv\$containers c
where
fs.inst_id=df.inst_id
and tbs.inst_id=df.inst_id
and c.inst_id=tbs.inst_id
and fs.file#=df.file#
and tbs.ts#=df.ts#
and tbs.con_id=df.con_id
and df.con_id=c.con_id
and c.con_id=fs.con_id
union all select fs.inst_id as inst_id,c.name as cont,case when regexp_like (df.name,'\^\\+') then substr(df.name,2,instr(df.name,'/',1)-2) else substr(df.name,1,instr(df.name,'/$fs_delimiter',-1)) end as fs_dg,fs.file# as file#,tbs.name as tbs_name,fs.PHYRDS as PHYRDS,fs.PHYWRTS as PHYWRTS,fs.READTIM*10 as READTIM,fs.WRITETIM*10 as WRITETIM,fs.PHYBLKRD*df.block_size/1024 as PHYBYTESRD,fs.PHYBLKWRT*df.block_size/1024 as PHYBYTESWR,substr(df.name,instr(df.name,'/',-1)+1) as file_name,$interval as interval,fs.PHYRDS+fs.PHYWRTS as iops
from
gv\$tempstat fs, gv\$tempfile df, gv\$tablespace tbs,gv\$containers c
where
fs.inst_id=df.inst_id
and tbs.inst_id=df.inst_id
and c.inst_id=tbs.inst_id
and fs.file#=df.file#
and tbs.ts#=df.ts#
and tbs.con_id=df.con_id
and df.con_id=c.con_id
and c.con_id=fs.con_id
) where 1=1
";
$pkeys{0}='%30s';
$pkeys{1}='%80s';
$pkeys{3}='%30s';
$pkeys{11}='%100s';
# What need to be show
my @show_fields = split (/,/,$show_pattern);
foreach my $show (@show_fields) {
if ($show =~ m/^inst$/i ){
# group by instance
$array_of_ckeys_description[$ckey_cpt]{0}='%30s';
$array_of_ckeys_description[$ckey_cpt]{12}='%10s';
$array_of_display_keys[$ckey_cpt]{0}='y';
$array_of_display_keys[$ckey_cpt]{12}='y';
$ckey_cpt=$ckey_cpt+1;
}
if ($show =~ m/^cont$/i ){
# group by containers
$array_of_ckeys_description[$ckey_cpt]{12}='%10s';
$array_of_display_keys[$ckey_cpt]{12}='y';
if (grep (/^inst$/i,@show_fields)) {
$array_of_ckeys_description[$ckey_cpt]{0}='%30s';
$array_of_display_keys[$ckey_cpt]{0}='y';
}
$array_of_ckeys_description[$ckey_cpt]{1}='%80s';
$array_of_display_keys[$ckey_cpt]{1}='y';
$ckey_cpt=$ckey_cpt+1;
}
if ($show =~ m/^fs_dg$/i ){
# group by fs or dg
$array_of_ckeys_description[$ckey_cpt]{12}='%10s';
$array_of_display_keys[$ckey_cpt]{12}='y';
if (grep (/^inst$/i,@show_fields)) {
$array_of_ckeys_description[$ckey_cpt]{0}='%30s';
$array_of_display_keys[$ckey_cpt]{0}='y';
}
if (grep (/^cont$/i,@show_fields)) {
$array_of_ckeys_description[$ckey_cpt]{1}='%80s';
$array_of_display_keys[$ckey_cpt]{1}='y';
}
$array_of_ckeys_description[$ckey_cpt]{2}='%100s';
$array_of_display_keys[$ckey_cpt]{2}='y';
$ckey_cpt=$ckey_cpt+1;
}
if ($show =~ m/^tbs$/i ){
# group by tablespace
$array_of_ckeys_description[$ckey_cpt]{12}='%10s';
$array_of_display_keys[$ckey_cpt]{12}='y';
if (grep (/^inst$/i,@show_fields)) {
$array_of_ckeys_description[$ckey_cpt]{0}='%30s';
$array_of_display_keys[$ckey_cpt]{0}='y';
}
if (grep (/^cont$/i,@show_fields)) {
$array_of_ckeys_description[$ckey_cpt]{1}='%80s';
$array_of_display_keys[$ckey_cpt]{1}='y';
}
if (grep (/^fs_dg$/i,@show_fields)) {
$array_of_ckeys_description[$ckey_cpt]{2}='%100s';
$array_of_display_keys[$ckey_cpt]{2}='y';
}
$array_of_ckeys_description[$ckey_cpt]{4}='%60s';
$array_of_display_keys[$ckey_cpt]{4}='y';
$ckey_cpt=$ckey_cpt+1;
}
if ($show =~ m/^file$/i ){
# group by datafiles
$array_of_ckeys_description[$ckey_cpt]{12}='%10s';
$array_of_display_keys[$ckey_cpt]{12}='y';
if (grep (/^inst$/i,@show_fields)) {
$array_of_ckeys_description[$ckey_cpt]{0}='%30s';
$array_of_display_keys[$ckey_cpt]{0}='y';
}
if (grep (/^cont$/i,@show_fields)) {
$array_of_ckeys_description[$ckey_cpt]{1}='%80s';
$array_of_display_keys[$ckey_cpt]{1}='y';
}
if (grep (/^fs_dg$/i,@show_fields)) {
$array_of_ckeys_description[$ckey_cpt]{2}='%100s';
$array_of_display_keys[$ckey_cpt]{2}='y';
}
if (grep (/^tbs$/i,@show_fields)) {
$array_of_ckeys_description[$ckey_cpt]{4}='%60s';
$array_of_display_keys[$ckey_cpt]{4}='y';
}
$array_of_ckeys_description[$ckey_cpt]{11}='%80s';
$array_of_display_keys[$ckey_cpt]{11}='y';
$ckey_cpt=$ckey_cpt+1;
}
}
@delta_fields=(5,6,7,8,9,10,13);
$report_format_values="%02d:%02d:%02d %1s %-10s %1s %-10s %1s %-8s %1s %-12s %1s %-11s %1s %-7.0f %1s %-7.0f %1s %-7.1f %1s %-7.0f %1s %-8.0f %1s %-7.0f %1s %-8.1f %1s %-7.0f\n";
@report_fields_values=(1,2,4,11,"5/12","9/12","7/5","9*1024/5","6/12","10/12","8/6","10*1024/6");
@array_of_report_header=(["%02d:%02d:%02d %67s %-7s %1s %-7s %1s %-7s %1s %-8s %11s %-7s %1s %-8s %1s %-7s\n",'','','','Kby','','Avg','','AvgBy/','','Kby','','Avg','','AvgBy/'],["%02d:%02d:%02d %1s %-10s %1s %-10s %1s %-8s %1s %-12s %1s %-11s %1s %-7s %1s %-7s %1s %-7s %1s %-7s %1s %-8s %1s %-7s %1s %-8s %1s %-7s\n",'','INST','','CONT','','FS_DG','','TBS','','FILE','','Reads/s','','Read/s','','ms/Read','','Read','','Writes/s','','Write/s','','ms/Write','','Write'],["%02d:%02d:%02d %1s %-10s %1s %-10s %1s %-8s %1s %-12s %1s %-11s %1s %-7s %1s %-7s %1s %-7s %1s %-7s %1s %-8s %1s %-7s %1s %-8s %1s %-7s\n",'','----------','','----------','','--------','','------------','','-----------','','-------','','-------','','-------','','------','','--------','','-------','','--------','','------']);
}
&go_sql_real_time;
}
#
# Ctrl+C signal
#
$SIG{INT}= \&close;
sub close {
print "Disconnecting from RDBMS...\n";
$sql1->finish;
$dbh->disconnect();
exit 0;
}
sub get_the_options {
my $help;
GetOptions('help|h' => \$help,
'interval=i'=>\$interval,
'count=i'=>\$count,
'inst:s' => \$instpattern,
'sort_field:s' => \$sort_field_pattern,
'file:s' => \$file_pattern,
'fs_dg:s' => \$fs_dg_pattern,
'tbs:s' => \$tbs_pattern,
'cont:s' => \$cont_pattern,
'display:s' => \$display_pattern,
'fs_delimiter:s' => \$fs_delimiter,
'show:s' => \$show_pattern) or &usage();
&usage() if ($help);
}
sub go_sql_real_time {
&connect_db;
&check_instance_type($inst_type);
&check_rac;
&build_instances;
&build_rac_pattern;
&build_glob_sql_pattern;
&build_glob_sql;
&initialise_arrays;
&launch_loop;
}
sub connect_db {
$dbh = DBI->connect('dbi:Oracle:',"", "", { ora_session_mode => ORA_SYSDBA });
}
sub check_instance_type {
my $inst_type=$_[0];
debug("Instance Type: ".$inst_type);
my $sql1 = $dbh->prepare('select value from v$parameter where name=\'instance_type\' ');
$sql1->execute;
if ( $sql1->fetchrow_array =~ /$inst_type/i) {
$sql1->finish;
}
else {
print "\n\n ERROR : You must connect to a ".$inst_type." instance \n\n";
$sql1->finish;
$dbh->disconnect();
exit 1;
}
}
sub check_rac {
my $sql1 = $dbh->prepare('select value from v$parameter where name = \'cluster_database\'');
$sql1->execute;
if ( $sql1->fetchrow_array =~ /true/i) {
$rac=1;
}
$sql1->finish;
}
sub check_version {
my $sql1 = $dbh->prepare('select regexp_replace(version,\'\..*\') from v$instance');
$sql1->execute;
return ($sql1->fetchrow_array);
$sql1->finish;
}
sub check_pdbs {
my $sql1 = $dbh->prepare('select count(*) from v$pdbs');
$sql1->execute;
return ($sql1->fetchrow_array);
$sql1->finish;
}
sub build_instances {
my $sql1 = $dbh->prepare('select inst_id,instance_name, host_name from gv$instance');
$sql1->execute;
while ( my ($instid, $instname,$host) = $sql1->fetchrow_array) {
$instances{$instname} = $instid;
}
$sql1->finish;
}
sub build_in_pattern {
my $pattern=shift;
my $column=shift;
my %list_of_field=@_;
my %reverse_list_of_field = reverse %list_of_field;
my @fields = split (/,/,$pattern);
my $output_in_pattern='';
foreach my $field (@fields) {
if (!exists $reverse_list_of_field{uc($field)}) {
print "\n\n ERROR : $field $column is not found !! \n";
exit 1;
} else {
if (!$output_in_pattern) {
$output_in_pattern=" and $column in ('"."$reverse_list_of_field{uc($field)}'";
} else {
$output_in_pattern=$output_in_pattern.",'$reverse_list_of_field{uc($field)}'";
}
}
}
($output_in_pattern)?$output_in_pattern=$output_in_pattern.")":"";
return $output_in_pattern;
}
sub build_rac_pattern
{
if ($rac & ! ($instpattern =~ /all|current/i)) {
my @fields = split (/,/,$instpattern);
foreach my $instname (@fields) {
if (!exists $instances{uc($instname)}) {
print "\n\n ERROR : The instance $instname is not found !! \n";
$dbh->disconnect();
exit 1;
} else {
$showinstances{$instname}=$instances{$instname};;
}
}
}
# If not rac put pattern as current
if (! $rac) {
$instpattern = 'current';
}
if ($instpattern =~ /current/i) {
my $sql1_sql = "select inst_id,instance_name, host_name from gv\$instance where inst_id = userenv('instance')";
my $sql1 = $dbh->prepare($sql1_sql);
$sql1->execute;
while ( my ($instid, $instname,$host) = $sql1->fetchrow_array) {
$instances{$instname} = $instid;
}
$sql1->finish;
}
if (($rac & ($instpattern =~ /all|current/i)) | (! $rac & ($instpattern =~ /current/i))) {
%showinstances = %instances;
}
# RAC : Create the SQL suffixe based on the instances to request on
# Case 1 : The current instance or list of instances
$sqlsuffixe = ((! $rac) | ($rac & $instpattern =~ /current/i) ? " and ".$instid_pattern. " = userenv('instance')" : "");
# Case 2 : All the instances
# Nothing to do
if ($rac & ! ($instpattern =~ /all|current/i)) {
foreach my $inst (keys %showinstances) {
my $inst_id = $showinstances{$inst};
if ($sqlsuffixe) {
$sqlsuffixe = $sqlsuffixe." or ".$instid_pattern. " = $inst_id";
}
else
{
$sqlsuffixe = $sqlsuffixe." and (".$instid_pattern. " = $inst_id";
}
}
$sqlsuffixe = $sqlsuffixe.")";
}
debug("sqlsuffixe: ".$sqlsuffixe);
# Reverse the hash for display usage (Report Section)
%showinstances = reverse %showinstances;
}
sub build_glob_sql_pattern {
foreach my $column (keys %sql_patterns) {
debug("column: ".$column);
debug("pattern: ".$sql_patterns{$column});
if ($sql_patterns{$column}) {$global_sql_pattern = $global_sql_pattern." and ".$column." like '".$sql_patterns{$column}."' "}
}
debug("global_sql_pattern: ".$global_sql_pattern);
}
sub build_glob_sql {
$main_sql = $main_sql.$global_sql_pattern.$sqlsuffixe;
debug("Main sql: ".$main_sql);
}
sub build_the_key {
my @tab1 = @_;
$bkey='';
@ekey=();
foreach my $id (sort { $a <=> $b }(keys %pkeys)) {
if ($bkey) {$bkey = $bkey.".".$pkeys{$id}};
if (!$bkey) {$bkey = $pkeys{$id}};
push(@ekey,$tab1[$id]);
}
}
sub build_compute_key {
my @tab1 = @_;
for my $i ( 0 .. $#array_of_ckeys_description ) {
my $bckey='';
my @eckey=();
for my $j ( sort { $a <=> $b } (keys %{ $array_of_ckeys_description[$i] }) ) {
($bckey)?($bckey = $bckey.".".$array_of_ckeys_description[$i]{$j}):($bckey = $array_of_ckeys_description[$i]{$j});
push(@eckey,$tab1[$j]);
}
my $ckey = sprintf($bckey,@eckey);
$array_of_ckey[$i]=$ckey;
}
}
sub initialise_arrays {
$sql1 = $dbh->prepare($main_sql);
my $key;
$sql1->execute;
while ( my @tab1 = $sql1->fetchrow_array) {
&build_the_key(@tab1);
$key = sprintf($bkey,@ekey);
@{$rtvalues{$key}}=@tab1;
@{$diffsnaps{$key}}=@tab1;
debug("key is : ".$key);
}
}
sub launch_loop {
my $key;
my $ckey;
my $cpt=0;
for (my $nb=0;$nb < $count;$nb++) {
print "............................\n";
print "Collecting $interval sec....\n";
print "............................\n";
sleep $interval;
$sql1->execute;
($seconds, $minuts, $hours) = localtime(time);
# Keep the first timing for the average section
($cpt==0)?(@since_timing=($hours,$minuts,$seconds)):"";
# Empty diffsnaps
%diffsnaps = ();
while ( my @tab1 = $sql1->fetchrow_array) {
&build_the_key(@tab1);
$key = sprintf($bkey,@ekey);
# Build the compute key
&build_compute_key(@tab1);
# Initialise non delta fields
for (my $tabid=0;$tabid < scalar(@tab1);$tabid++) {
for my $i ( 0 .. $#array_of_ckeys_description ) {
my $ckey=$array_of_ckey[$i];
$diffsnaps{$ckey}->[$tabid]=($array_of_display_keys[$i]{$tabid}?"$tab1[$tabid]":"") unless (grep (/^$tabid$/,@delta_fields));
$avgdiffsnaps{$ckey}->[$tabid]=($array_of_display_keys[$i]{$tabid}?"$tab1[$tabid]":"") unless (grep (/^$tabid$/,@delta_fields));
debug("Non delta fields: for display_keys $array_of_display_keys[$i]{$tabid} and tabid $tabid ".$diffsnaps{$ckey}->[$tabid]);
}
}
# get the list of delta fields
foreach my $deltaid (@delta_fields) {
for my $i ( 0 .. $#array_of_ckeys_description ) {
my $ckey=$array_of_ckey[$i];
debug("deltaid : ".$deltaid);
debug("key is : ".$key);
debug("ckey during diff is : ".$ckey);
$diffsnaps{$ckey}->[$deltaid] = $diffsnaps{$ckey}->[$deltaid] + $tab1[$deltaid] - $rtvalues{$key}->[$deltaid];
debug("Previous : ".$rtvalues{$key}->[$deltaid]);
debug("Current : ".$tab1[$deltaid]);
debug("Diff is : ".$diffsnaps{$ckey}->[$deltaid]);
}
}
@{$rtvalues{$key}} = @tab1;
debug("key is : ".$key);
debug("ckey is : ".$ckey);
}
# compute the average since the collection began
foreach my $deltaid (@delta_fields) {
foreach my $diffkey (keys %diffsnaps){
$avgdiffsnaps{$diffkey}->[$deltaid] = (($avgdiffsnaps{$diffkey}->[$deltaid] * $cpt) + $diffsnaps{$diffkey}->[$deltaid]) / ($cpt+1);
}
}
$cpt=$cpt+1;
# Report now for snaps
(grep (/snap/i,$display_pattern))?(print "\n"):"";
(grep (/snap/i,$display_pattern))?(print "......... SNAP TAKEN AT ...................\n"):"";
(grep (/snap/i,$display_pattern))?(print "\n"):"";
(grep (/snap/i,$display_pattern))?(&report_header("snap",@array_of_report_header)):"";
(grep (/snap/i,$display_pattern))?(&report_values("snap",%diffsnaps)):"";
# Report now for average
(grep (/avg/i,$display_pattern))?(print "\n"):"";
(grep (/avg/i,$display_pattern))?(print "......... AVERAGE SINCE ...................\n"):"";
(grep (/avg/i,$display_pattern))?(print "\n"):"";
(grep (/avg/i,$display_pattern))?(&report_header("avg",@array_of_report_header)):"";
(grep (/avg/i,$display_pattern))?(&report_values("avg",%avgdiffsnaps)):"";
}
}
sub report_header {
my $display_date = shift;
my @array_of_report_header = @_;
foreach my $report_ligne (0..@array_of_report_header-1) {
my @header;
@header = ($display_date eq "avg")?(@since_timing):($hours,$minuts,$seconds);
foreach my $report_column (1..@{$array_of_report_header[$report_ligne]}) {
push(@header,$array_of_report_header[$report_ligne][$report_column]);
}
printf ($array_of_report_header[$report_ligne][0],@header);
}
}
sub report_resultset {
my $display_date = shift;
my $pk=shift;
my %resultset=@_;
my $backup_mult;
my @values;
@values = ($display_date eq "avg")?(@since_timing):($hours,$minuts,$seconds);
if (%showinstances) {push(@values,'',$showinstances{$resultset{$pk}->[0]})};
foreach my $id (@report_fields_values) {
push(@values,'');
my @need_div=split(/\//,$id);
my @need_mult=split(/\*/,$id);
if (@need_mult > 1) {
$need_mult[1] =~ s/\/.*//;
$backup_mult = $resultset{$pk}->[$need_mult[0]];
$resultset{$pk}->[$need_mult[0]] = ($resultset{$pk}->[$need_mult[0]]) * $need_mult[1];
debug("Mult is needed for id : ".$id);
debug("Mult[0] is : ".$need_mult[0]);
debug("Mult[1] is : ".$need_mult[1]);
}
if (@need_div > 1) {
$need_div[0] =~ s/\*.*//;
debug("Div is needed for id : ".$id);
debug("needed_div is : ".@need_div);
debug("div[0] is : ".$need_div[0]);
debug("div[1] is : ".$need_div[1]);
if ($resultset{$pk}->[$need_div[1]] > 0) {push(@values,$resultset{$pk}->[$need_div[0]]/$resultset{$pk}->[$need_div[1]])};
if ($resultset{$pk}->[$need_div[1]] == 0) {push(@values,0)};
}
else
{
push(@values,$resultset{$pk}->[$id]);
}
# In case the resultset has been changed, then put the value back (For the average..)
if (@need_mult > 1) {
$resultset{$pk}->[$need_mult[0]] = $backup_mult;
}
}
printf ($report_format_values,@values);
}
sub report_values {
my $nb =1;
my %resultset = ();
my $display_date = shift;
my %display_what = @_;
my $rank = 1;
my %ranked_instances = ();
# rank the instance based on the sort field
foreach my $pk (sort {$display_what{$b}[$sort_fields{uc($sort_field_pattern)}] <=> $display_what{$a}[$sort_fields{uc($sort_field_pattern)}] || $display_what{$a}[0] <=> $display_what{$b}[0]} (keys(%display_what))) {
if (!(exists $ranked_instances{$display_what{$pk}[0]})) {
$ranked_instances{$display_what{$pk}[0]}=$rank;
$display_what{$pk}->[99] = $rank;
$rank++;
} else {
$display_what{$pk}->[99] = $ranked_instances{$display_what{$pk}[0]};
}
debug("Rank is ".$display_what{$pk}->[99]." for instance ".$display_what{$pk}[0]);
}
if ($nb_pdbs == 0) {
foreach my $pk (sort {$display_what{$a}[99] <=> $display_what{$b}[99] || $display_what{$b}[$sort_fields{uc($sort_field_pattern)}] <=> $display_what{$a}[$sort_fields{uc($sort_field_pattern)}] || $display_what{$a}[1] cmp $display_what{$b}[1] || $display_what{$a}[3] cmp $display_what{$b}[3] || $display_what{$a}[10] cmp $display_what{$b}[10]} (keys(%display_what))) {
&report_resultset($display_date,$pk,%display_what);
debug("Sorted value is : ".$display_what{$pk}[$sort_fields{uc($sort_field_pattern)}]);
}
} else {
foreach my $pk (sort {$display_what{$a}[99] <=> $display_what{$b}[99] || $display_what{$b}[$sort_fields{uc($sort_field_pattern)}] <=> $display_what{$a}[$sort_fields{uc($sort_field_pattern)}] || $display_what{$a}[1] cmp $display_what{$b}[1] || $display_what{$a}[2] cmp $display_what{$b}[2] || $display_what{$a}[4] cmp $display_what{$b}[4] || $display_what{$a}[11] cmp $display_what{$b}[11]} (keys(%display_what))) {
&report_resultset($display_date,$pk,%display_what);
debug("Sorted value is : ".$display_what{$pk}[$sort_fields{uc($sort_field_pattern)}]);
}
}
}
sub usage {
&usage_db_io_metrics();
}
sub usage_db_io_metrics {
&connect_db;
$version=check_version();
if ($version >= 12) {$nb_pdbs=check_pdbs()};
if ($nb_pdbs == 0) {
print " \nUsage: $0 [-interval] [-count] [-inst] [-fs_dg] [-tbs] [-file] [-fs_delimiter] [-show] [-display] [-sort_field] [-help]\n";
} else {
print " \nUsage: $0 [-interval] [-count] [-inst] [-cont] [-fs_dg] [-tbs] [-file] [-fs_delimiter] [-show] [-display] [-sort_field] [-help]\n";
}
print "\n";
print " Default Interval : 1 second.\n";
print " Default Count : Unlimited\n\n";
printf (" %-15s %-65s %-10s \n",'Parameter','Comment','Default');
printf (" %-15s %-65s %-10s \n",'---------','-------','-------');
printf (" %-15s %-65s %-10s \n",'-INST=','ALL - Show all Instance(s) ','ALL');
printf (" %-15s %-65s %-10s \n",'','CURRENT - Show Current Instance ','');
if ($nb_pdbs > 0) {
printf (" %-15s %-65s %-10s \n",'-CONT=','Container to collect (wildcard allowed)','ALL');
}
printf (" %-15s %-65s %-10s \n",'-FS_DG=','Filesystem or Diskgroup to collect (wildcard allowed)','ALL');
printf (" %-15s %-65s %-10s \n",'-TBS=','Tablespace to collect (wildcard allowed)','ALL');
printf (" %-15s %-65s %-10s \n",'-FILE=','Datafile to collect (wildcard allowed)','ALL');
printf (" %-15s %-65s %-10s \n",'-FS_DELIMITER=','Folder which follows the FS mount point','ORADATA');
if ($nb_pdbs == 0) {
printf (" %-15s %-65s %-10s \n",'-SHOW=','What to show: inst,fs_dg,tbs,file (comma separated list)','INST');
} else {
printf (" %-15s %-65s %-10s \n",'-SHOW=','What to show: inst,cont,fs_dg,tbs,file (comma separated list)','INST');
}
printf (" %-15s %-65s %-10s \n",'-DISPLAY=','What to display: snap,avg (comma separated list)','SNAP');
printf (" %-15s %-65s %-10s \n",'-SORT_FIELD=','reads|writes|iops','NONE');
print ("\n");
print ("Example: $0 \n");
print ("Example: $0 -inst=BDT_1\n");
print ("Example: $0 -show=inst,tbs\n");
print ("Example: $0 -show=inst,tbs -tbs=%UNDO%\n");
print ("Example: $0 -show=fs_dg\n");
print ("Example: $0 -show=inst,fs_dg -display=avg\n");
print ("Example: $0 -show=inst,fs_dg -sort_field=reads\n");
if ($nb_pdbs > 0) {
print ("Example: $0 -show=inst,tbs,cont\n");
print ("Example: $0 -show=inst,tbs,cont -cont=%P_%\n");
print ("Example: $0 -show=inst,cont -sort_field=iops\n");
}
print "\n\n";
exit 1;
}
sub debug {
if ($debug==1) {
print $_[0]."\n";
}
}
&main(@ARGV);
Advertisements
