Re: Highest table being accessed
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--