Re[2]: Highest table being accessed
--IMA.Boundary.2547240980
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Description: cc:Mail note part
Eric and Claus
Here is another one. This generates the output into a file after every
user-defined interval and outputs to a pipe delimited file so that it
can later be uploaded into Excel/Lotus and analyzed. Its written in
perl, so you would need perl installed.
I am sending this as an ASCII attachment (readable in Notepad).
HTH
Sujit
______________________________ Reply Separator _________________________________
Subject: Re: Highest table being accessed
Author: Eric_Melillo@agsea.com at Internet
Date: 3/19/98 10:24 AM
Claus -
You may find this shell script handy in the future -- It gives historical
data since statistics were last zero'd
( i.e.: tbstat -z or bounce of engine ). Change "order by" to fit your
needs best.
##### -- CUT HERE -- ##### -- CUT HERE -- #####
#!/bin/ksh -f
# ##########
# SCRIPT NAME : hits.sh
#
# PURPOSE : To determine the number of read / write hits are going
against a database
#
# NOTES : Expects the database name to be passes as a parameter
# ##########
db=$1
if [ -z "$1" ]
then
echo "\\\\nusage : hits.sh <dbname>"
exit
fi
dbaccess sysmaster <<EOF
select
dbsname[1,12],
tabname[1,15],
pf_dskreads diskreads,
pf_bfcread cache_reads,
pf_dskwrites diskwrites,
pf_bfcwrite cache_writes
from
sysptntab,
systabnames
where
sysptntab.partnum=systabnames.partnum and
tabname[1,3] != "sys" and
dbsname not in ("sysmaster","rootdbs","sysutils") and
tabname != "TBLSpace" and
dbsname = '$db'
order by 1, 3 desc,4 desc,5 desc,6 desc
EOF
##### -- CUT HERE -- ##### -- CUT HERE -- #####
Eric P. Melillo
Sr. Database Administrator
Associated Grocers, Inc.
Seattle, WA
csa@sysdeco.dk on 03/19/98 08:44:33 AM
Please respond to csa@sysdeco.dk
To: informix-list@iiug.org
cc: (bcc: Eric Melillo/AGInc)
Subject: Re: Highest table being accessed
Susan Elliott (ISG) wrote:
> Does any one know how to work out which tables in a database are being
>
> accessed the most ???
>
> Please advise....
> Thanks in advance
> Suze.
(This works Informix DS only)
database sysmaster;
select tabname, bufreads, pagreads, bufwrites, pagwrites
from sysptprof
where dbsname = "nameOfDatabase"
order by bufreads desc;
Set the order by clause to the type of load (read or write) that you
want to
messure.
You should notice that sysptprof only contains data for tables in use.
That is
when the last user accessing a table disconnects, the profile
information
for that table is removed. So you must do your messures 'online'.
best regards
claus
begin: vcard
fn: Claus Samuelsen
n: Samuelsen;Claus
org: Sysdeco Danmark A/S
adr: Gydevang 22 B;;;DK-3450 Aller?d;;;Denmark
email;internet: csa@sysdeco.dk
title: Systems Manager
tel;work: +45 4814 3000
tel;fax: +45 4814 3011
x-mozilla-cpt: ;0
x-mozilla-html: FALSE
end: vcard
--------------EAE30CD7C2AF2AA6CF290D0A--
--IMA.Boundary.2547240980
Content-Type: application/octet-stream; name="app_prof.pl"
Content-Transfer-Encoding: base64
Content-Description: Unknown data type
Content-Disposition: attachment; filename="app_prof.pl"
#!/usr/bin/perl
#
# This command repeatedly scans sysprofile (sysmaster table having
# the data from which onstat -p is displayed) at predefined intervals
# and generates a delimited flat file which can be uploaded to
# a spreadsheet program for graphical viewing.
#
# To use this program, run the application in one window and the
# application profiler (this) in another. You need to supply the
# the sampling time interval in the command line. It will automatically
# zero the statistics before starting and at each time interval,
# sample the sysmaster:sysprofile table.
#
# Author: Sujit Pal Date Written: 02/27/1998
#
$SIG{INT} = \\&catch_zap; # Setup Interrupt signal handler
if ($#ARGV != 0)
{
die "Command Format: app_prof.pl sampling_time_interval_in_seconds\\n";
}
else
{
$sample_time = $ARGV[0];
}
open(APP_PROF, ">app_prof.out");
print APP_PROF "dskreads|bufreads|dskwrites|bufwrites|isamtot|isopens|isstarts|isreads|iswrites|isrewrites|isdeletes|iscommits|isrollbacks|ovlock|ovuser|ovtrans|latchwts|buffwts|lockreqs|lockwts|ckptwts|deadlks|lktouts|numckpts|plgpgwrites|plgwrites|llgrecs|llgpagewrites|llgwrites|pagreads|pagwrites|flushes|compress|fgwrites|lruwrites|chunkwrites|btradata|btraidx|dpra|rapgs_used|seqscans|totalsorts|memsorts|disksorts|maxsortspace|\\n";
$elapsed_time = 0;
$rc = `onstat -z`;
while (true)
{
chop(@profile = `echo "SELECT * FROM sysprofile" | \\
dbaccess sysmaster 2>/dev/null`);
$i = 0;
$profln = '';
foreach (@profile)
{
$i++;
if ($i <= 4)
{
next;
}
($name, $value) = split(' ', $_);
$profln = $profln . "|" . $value;
}
$profln = $profln . "|\\n";
print APP_PROF $profln;
sleep $sample_time;
$elapsed_time += $sample_time;
print "Time Elapsed = " . $elapsed_time . " secs\\n";
}
sleep 5;
close(APP_PROF);
#
# Signal Handler for Interrupt
#
sub catch_zap
{
my $signame = shift;
$shucks++;
close(APP_PROF);
die "Program stopped due to SIG$signame\\n";
}
--IMA.Boundary.2547240980--