How to find your "busiest" tables
This VERY useful information was originally presented in session A-7 at the
1997 IWUC.
This query runs for a minute or two, but returns back a list of the tables
in a given database in descending order by number of I/O operations since
the engine was brought online, or since the last "onstat -z" was run. I
have tested this against ODS 7.23 and on HP-UX and it works just fine. The
info might help you decide to physically relocate or fragment a table.
Put this in a chmod 755 shell script called "dbtableio" (that's what I did
anyways) then run it like this:
/home/informix/jobs> dbtableio databasename
-------------------------------------------------
# Program: dbtableio
# Description: I/O statistics by table.
dbaccess sysmaster - <<EOF
select dbsname,
tabname,
isreads,
iswrites,
lockreqs
from sysptprof
where dbsname = "$1"
order by isreads desc;EOF
-------------------------------------------------