Re: Table Hits on V7
At 10:06 AM 10/30/96 -0600, you wrote:
::Hi All,
::
::
:: Is there any way (or even a tool available) that will
:: let you see how many time a table has been hit ?
::
::
::[Informix v7.x, online]
::
::
::Thanks,
:: ##################### Manoj Schroff (Systems Engineer)
On small systems, those with fewer than 500 tables, the following
query runs fairly well:
SELECT a.dbsname, a.tabname, a.owner, b.pf_isread, b.pf_iswrite,
b.pf_isrwrite, b.pf_isdelete, b.pf_seqscans, b.partnum
FROM sysmaster:systabnames a,
sysmaster:sysptntab b
WHERE a.partnum = b.partnum;
It will return row reads, writes, updates, deletes, and sequential
scans on the tables. There are other statistics available from the
sysptntab table that may be of interest -- locks requests/waits,
buffer reads/writes, and disk reads/writes to name a few.
On larger systems, you'd be much better off running a stored
procedure to retrieve the desired statistic, something like:
CREATE PROCEDURE TableSeqScans ()
RETURNING CHAR(18), CHAR(18), CHAR(18), INT;
DEFINE xdbsname CHAR(18);
DEFINE xtabname CHAR(18);
DEFINE xowner CHAR(18);
DEFINE xvalue INT;
DEFINE xpart INT;
FOREACH
SELECT partnum, pf_seqscans
INTO xpart, xvalue
FROM sysmaster:sysptntab
WHERE pf_seqscans > 0
FOREACH
SELECT dbsname, tabname, owner
INTO xdbsname, xtabname, xowner
FROM sysmaster:systabnames
WHERE partnum = xpart
END FOREACH
RETURN xdbsname, xtabname, xowner, xvalue WITH RESUME;
END FOREACH;
END PROCEDURE;
For a comprehensive performance management tool, check out DBVision
for Informix from Platinum.
Rick