Status: Verified
GOAL:
How to analyze table or index degradation during a performance and tuning
GOAL:
How to extracts table names, fragment counts, fragmentation factors, etc.
FIX:
The program is run as follows:
Windows:
perl dbanalys.cgi -
[ ] [ ] -
[ ] [ ] -
[ ] [ ]
UNIX:
dbanalys.cgi -
[ ] [ ] -
[ ] [ ] -
[ ] [ ]
Messages are displayed on the screen, and are also written to a log
file (log.txt). The record information is written to a file named
rec.txt, and the index information is written to a file named idx.txt.
The program also takes four optional threshold arguments. If these are
omitted, they default to:
Table size (bytes) - 25000
Table size (records) - 5000
Index size (blocks) - 100
Index size (bytes) - 2000
These values are based on subjective experience. Often tables under a
certain size have high scatter factors, therefore it is helpful to
eliminate them from consideration, since it is likely that small
tables are often held entirely in memory. The same logic applies to
small indexes.
You can include all tables and indexes by specifying the thresholds as
zeroes:
dbanalys.cgi mydbanal.txt 0 0 0 0
After processing the DBANALYS, the data can be read into Excel using
the following steps:
1) Select the upper-left cell where you want the data.
2) Select Data -> Get External Data -> Import Text File.
3) Find the file you want to import.
4) Select Delimited data, click Next.
5) Set the delimiter to be Space, click Next.
6) Leave all fields as General, and click Finish.
7) Excel shows a query box that asks where you want to put the
data. The cell you selected should be in the answer box. Click
OK.
8) Use the Chart wizard to build your chart.
#!/usr/bin/perl
#
# Perl program to process a DBANALYS output file and extract the
# information from the TABANALYS and IXANALYS segments into a file
# readable by Microsoft Excel
#
# Author: Steven C. Robbins
# Progress Software Technical Support
# 1/21/1001
#
# Revised: 3/7/01 - fixed code to expand metric suffixes
#
# Disclaimer: This program is offered as-is, with no warranties
# expressed or implied.
#
# Check the command line arguments - if the user gave a file name
# on the command line, use it, otherwise ask him for a file to
# process.
#
# Command lines:
#
# dbanalys [ ] [ ] -
# [ ] [ ] -
# [ ] [ ]
#
# If the program is invoked with no arguments, the default values
# for the minimum table size (bytes and records), minimum index size
# (bytes and blocks), and record and index output files are:
#
# Table size (bytes) - 25000
# Table size (records) - 5000
# Index size (blocks) - 100
# Index size (bytes) - 2000
# Record output file - rec.txt
# Index output file - idx.txt
# Log file - log.txt
#
# These values are based on subjective experience. Often tables under
# a certain size have high scatter factors, therefore it is helpful to
# eliminate them from consideration, since it is likely that small
# tables are often held entirely in memory. The same logic applies to
# small indexes.
# Assign default values for thresholds and output files
$table_byte_thresh = 25000;
$table_rec_thresh = 5000;
$index_block_thresh = 100;
$index_byte_thresh = 2000;
$rec_output = "rec.txt";
$idx_output = "idx.txt";
$log_file = "log.txt";
# Process the command line
$count = @ARGV;
if ($count > 0) {
$input_file = @ARGV[0];
open(ANALYSIS, "$input_file") || die "Can't open file
$input_file";
open(LOGFILE, ">$log_file") || die "Can't open file $log_file";
shift @ARGV;
# Extract the four (or less) thre.sholds from the command line
for ($i = 0; $i -\n";
print " [ ] [ ] -\n";
print " [ ] [ ] -\n";
print " [ ] [ ]\n";
exit(0);
}
print "\n";
print "Input file: $input_file\n";
print "Table byte threshold: $table_byte_thresh\n";
print "Table record threshold: $table_rec_thresh\n";
print "Index block threshold: $index_block_thresh\n";
print "Index byte threshold: $index_byte_thresh\n";
print "Record analysis file: $rec_output\n";
print "Index analysis file: $idx_output\n";
print "Log file: $log_file\n";
print "\n";
print LOGFILE "Input file: $input_file\n";
print LOGFILE "Table byte threshold: $table_byte_thresh\n";
print LOGFILE "Table record threshold: $table_rec_thresh\n";
print LOGFILE "Index block threshold: $index_block_thresh\n";
print LOGFILE "Index byte threshold: $index_byte_thresh\n";
print LOGFILE "Record analysis file: $rec_output\n";
print LOGFILE "Index analysis file: $idx_output\n";
print LOGFILE "Log file: $log_file\n";
print LOGFILE "\n";
open(RECORD_ANALYSIS,">$rec_output") ||
die "Can't open file $rec_output";
open(INDEX_ANALYSIS,">$idx_output") ||
die "Can't open file $idx_output";
%tablestat = process_dbanalys_rec($input_file);
foreach $table (sort keys %tablestat) {
if ($table ne "\177Totals:") {
if (($tablestat{$table}[0] >= $table_rec_thresh) ||
($tablestat{$table}[1] >= $table_byte_thresh)) {
print RECORD_ANALYSIS
"$table $tablestat{$table}[0] $tablestat{$table}[1]
",
"$tablestat{$table}[6] $tablestat{$table}[7]\n";
} else {
print "Table $table is below the thresholds\n";
print LOGFILE "Table $table is below the thresholds\n";
}
} else {
print RECORD_ANALYSIS
"Totals: $tablestat{$table}[0] $tablestat{$table}[1] ",
"$tablestat{$table}[6] $tablestat{$table}[7]\n";
}
}
%indexstat = process_dbanalys_idx($input_file);
foreach $indexkey (sort keys %indexstat) {
if ($indexkey ne "\177Totals:") {
@indexpair = split(/\177/, $indexkey);
if (($indexstat{$indexkey}[3] >= $index_block_thresh) ||
($indexstat{$indexkey}[4] >= $index_byte_thresh)) {
print INDEX_ANALYSIS "@indexpair[0] @indexpair[1] ",
"$indexstat{$indexkey}[3] $indexstat{$indexkey}[4] ",
"$indexstat{$indexkey}[5] $indexstat{$indexkey}[6]\n";
} else {
print "Index @indexpair[0] on table ",
"@indexpair[1] is below the thresholds\n";
print LOGFILE "Index @indexpair[0] on table ",
"@indexpair[1] is below the thresholds\n";
}
} else {
print INDEX_ANALYSIS "Totals: ",
"$indexstat{$indexkey}[0] $indexstat{$indexkey}[1] ",
"$indexstat{$indexkey}[2] $indexstat{$indexkey}[3]\n";
}
}
print "Processing complete.\n";
print LOGFILE "\nProcessing complete.\n";
close(RECORD_ANALYSIS);
close(INDEX_ANALYSIS);
close(LOGFILE);
exit(0);
# Subroutine to trim the metric suffixes the V9 puts on numbers
# in the DBANALYS report (i.e. k = 1000, m = 1000000, etc)
sub t.rim_suffix {
my ($val) = @_;
$val =~ s/\.0B\b//i;
$val =~ s/K\b/00/i;
$val =~ s/M\b/00000/i;
$val =~ s/G\b/00000000/i;
$val =~ s/T\b/00000000000/i;
$val =~ s/\.//i;
return $val;
}
# Subroutine to build a hash/array structure with the names
# of the tables as the key and the array of DBANALYS data
# as its value.
sub process_dbanalys_rec {
my($dbanalys_file) = @_;
my %hashresults = ();
# Process the DBANALYS file as follows:
#
# Read until we find the string "RECORD BLOCK SUMMARY".
# Read the analysis information for each table. Stop when
# we read a line where the first token is "Totals:"
$processing_records = 0;
open(ANALYSIS, "$dbanalys_file") ||
die "Can't open file $dbanalys_file";
# Start processing the DBANALYS
while ($line = <ANALYSIS>) {
# Discard any blank lines
while ($line eq "\n") {
$line = <ANALYSIS>;
}
# Remove any multiple spaces from the line and split it. There are a
# maximum of nine fields (in any of the lines we care about).
$line = join(" ", split " ", $line);
@fields = split(/ /, $line);
# Check to see if we've hit the end of the block.
# If so, process the totals fields just like a table.
if (@fields[0] eq "Totals:") {
if (@fields[8] ne "") {
@fields[1] = trim_suffix(@fields[1]);
@fields[2] = trim_suffix(@fields[2]);
@fields[3] = trim_suffix(@fields[3]);
@fields[4] = trim_suffix(@fields[4]);
@fields[5] = trim_suffix(@fields[5]);
@fields[6] = trim_suffix(@fields[6]);
$tablename = @fields[0];
$reccount = @fields[1];
$tablesize = @fields[2];
$recmin = @fields[3];
$recmax = @fields[4];
$recmean = @fields[5];
$fragcnt = @fields[6];
$fragfact = @fields[7];
$scatfact = @fields[8];
# Add an octal 177 to the beginning of "Totals" to make it appear
# at the end of the list.
$tablename = "\177".$tablename;
$hashresults{$tablename} =
[$reccount, $tablesize, $recmin, $recmax,
$recmean, $fragcnt, $fragfact, $scatfact];
} else {
print "Error processing totals\n";
print LOGFILE "Error processing totals\n";
}
close(ANALYSIS);
return %hashresults;
}
# Check to see if we've hit the record block summary for an area
# while processing records. Since there is no delimiter between
# record block summaries for each area, we need to discard the
# next three lines (they are header information).
if ((@fields[0] eq "RECORD") && (@fields[1] eq "BLOCK") &&
(@fields[2] eq "SUMMARY") && (@fields[3] eq "FOR") &&
(@fields[4] eq "AREA") && ($processing_records)) {
$line = <ANALYSIS>;
$line = <ANALYSIS>;
$line = <ANALYSIS>;
$line = <ANALYSIS>;
$line = join(" ", split " ", $line);
@fields = split(/ /, $line);
}
# Split the fields into eight values (table name, record count,
# size, min record size, max record size, mean record size,
# fragment count, fragment factor and scatter factor).
if (($processing_records) &&
(substr(@fields[0],0,5) ne "-----")) {
if (@fields[8] ne "") {
@fields[1] = trim_suffix(@fields[1]);
@fields[2] = trim_suffix(@fields[2]);
@fields[3] = trim_suffix(@fields[3]);
@fields[4] = trim_suffix(@fields[4]);
@fields[5] = trim_suffix(@fields[5]);
@fields[6] = trim_suffix(@fields[6]);
$tablename = @fields[0];
$reccount = @fields[1];
$tablesiz.e = @fields[2];
$recmin = @fields[3];
$recmax = @fields[4];
$recmean = @fields[5];
$fragcnt = @fields[6];
$fragfact = @fields[7];
$scatfact = @fields[8];
$hashresults{$tablename} =
[$reccount, $tablesize, $recmin, $recmax,
$recmean, $fragcnt, $fragfact, $scatfact];
} else {
print "Error processing table @fields[0]\n";
print LOGFILE "Error processing table @fields[0]\n";
}
}
# Check to see if we've hit the record block summary.
if ((@fields[0] eq "RECORD") && (@fields[1] eq "BLOCK") &&
(@fields[2] eq "SUMMARY")) {
$processing_records = 1;
$line = <ANALYSIS>;
$line = <ANALYSIS>;
$line = <ANALYSIS>;
print "Processing records...\n";
print LOGFILE "Processing records...\n";
}
}
close(ANALYSIS);
return %hashresults;
}
# Subroutine to build a hash/array structure with the table/index
# pairs (an array) as the key and the array of IXANALYS data
# as its value.
sub process_dbanalys_idx {
my($dbanalys_file) = @_;
my %hashresults = ();
# Process the DBANALYS file as follows:
#
# Read until we find the string "INDEX BLOCK SUMMARY".
# Read the analysis information for each index. Stop when
# we read a line where the first token is "Totals:"
$processing_indexes = 0;
$v9flag = 0;
open(ANALYSIS, "$dbanalys_file") ||
die "Can't open file $dbanalys_file";
# Start processing the DBANALYS
while ($line = <ANALYSIS>) {
# Discard any blank lines
while ($line eq "\n") {
$line = <ANALYSIS>;
}
# Remove any multiple spaces from the line and split it. There are a
# maximum of nine fields (in any of the lines we care about).
$line = join(" ", split " ", $line);
@fields = split(/ /, $line);
# Check to see if we've hit the end of the block.
# If so, process the totals fields just like an index,
# except that it only has five fields.
if ((@fields[0] eq "Totals:") && ($processing_indexes)) {
if (@fields[4] ne "") {
@fields[1] = trim_suffix(@fields[1]);
@fields[2] = trim_suffix(@fields[2]);
@fields[3] = trim_suffix(@fields[3]);
@fields[4] = trim_suffix(@fields[4]);
$tablename = @fields[0];
$blocks = @fields[1];
$indexsize = @fields[2];
$indexutil = @fields[3];
$indexfact = @fields[4];
# Add an octal 177 to the beginning of "Totals" to make it appear
# at the end of the list.
$tablename = "\177".$tablename;
$hashresults{$tablename} =
[$blocks, $indexsize, $indexutil, $indexfact];
} else {
print "Error processing totals\n";
print LOGFILE "Error processing totals\n";
}
close(ANALYSIS);
return %hashresults;
}
# Check to see if we've hit the index block summary for an area
# while processing indexes. Since there is no delimiter between
# index block summaries for each area, we need to discard the
# next two lines (they are header information).
if ((@fields[0] eq "INDEX") && (@fields[1] eq "BLOCK") &&
(@fields[2] eq "SUMMARY") && (@fields[3] eq "FOR") &&
(@fields[4] eq "AREA") && ($processing_indexes)) {
$v9flag = 1;
$line = <ANALYSIS>;
$line = <ANALYSIS>;
$line = <ANALYSIS>;
$line = join(" ", split " ", $line);
@fields = split(/ /, $line);
}
# Split the fields into eight values (index name, index number,
# levels, blocks, size, percent utilizati.on, and utilization factor).
if (($processing_indexes) &&
(substr(@fields[0],0,5) ne "-----")) {
if (((@fields[1] eq "") && ($v9flag)) ||
((@fields[1] eq "Index") &&
(@fields[2] eq "Fields") && (!$v9flag))) {
$current_table = @fields[0];
} else {
if (@fields[7] ne "") {
@fields[4] = trim_suffix(@fields[4]);
@fields[5] = trim_suffix(@fields[5]);
$indexname = @fields[0];
$indexnum = @fields[1];
$indexflds = @fields[2];
$indexlvl = @fields[3];
$indexbks = @fields[4];
$indexsize = @fields[5];
$indexutil = @fields[6];
$indexfact = @fields[7];
$indexkey = $current_table."\177".$indexname;
$hashresults{$indexkey} =
[$indexnum, $indexflds, $indexlvl, $indexbks,
$indexsize, $indexutil, $indexfact];
} else {
print "Error processing index @fields[0] ",
"on table $current_table\n";
}
}
}
# Check to see if we've hit the index block summary.
if ((@fields[0] eq "INDEX") && (@fields[1] eq "BLOCK") &&
(@fields[2] eq "SUMMARY")) {
$processing_indexes = 1;
$line = <ANALYSIS>;
print "Processing indexes...\n";
print LOGFILE "Processing indexes...\n";
}
}
close(ANALYSIS);
return %hashresults;
}
.