Identifying The Hottest Tables (Informix)

Yesterday I posted about how to identify the hottest table in a PostgreSQL database. Pretty much the same functionality is available for administrators of Informix databases as well; this kind of information is found in the sysmaster database which is the database engine's own database.

    SELECT
            TRIM(dbsname) || ':' || TRIM(tabname) AS relation_name,
            isreads AS records_read,
            pagreads AS page_reads,
            iswrites AS records_inserted
            bufwrites AS buffered_writes
    FROM sysmaster:sysptprof
    ORDER BY isreads DESC;

This will list a record for every object in the database including indexes; the ratio of ISAM operations vs. buffer page operations can give you a hint as to the effectiveness of your server's configuration. If the ratio is very low for busy object your buffer pool is possibly too small.

If you are interested in the counts of various query operations the sysptprof table also provides the following values:

• isrwrite : The number of records updated.
• isrdelete : The number of records deleted.

These counters will reset whenever the database server is restarted. In versions 11.7 and later of the Informix persistent values are available from the sysmaster database.

Many more sysmaster queries can be found in the Informix Wiki.