Skip to Content

SMI Query Series - Sequential Scans on Tables

SMI Query Series - Sequential Scans on Tables

The sysmaster interface (SMI) presents a comfortable way to extract information about various parts of your running IDS instance. This information can be used for:

  • ad hoc analysis of performance problems
  • determining the status of several IDS sub components
  • monitoring replication metrics
  • sampling data for capacity planning purposes
  • tracking system usage of individual sessions
  • and many more...

The below SMI query is the first one of a new SMI query series at The Informix Zone. The queries show you how to extract valuable information from the sysmaster database.

smi_seq_scans.sql

The purpose of this SMI query is to display sequential scans on tables. Sequential scans are not always a bad thing, but they may hurt performance if they occur frequently on tables that contain more than a few hundred rows. An update statistics or the creation of a missing index (plus update statistics if you are not already on IDS 11) on the respective table should normally help in such situations.

The smi_seq_scans.sql comes in two versions. Choose the one that fits your current IDS version. You may swap the two columns in the ORDER BY clause to sort the result set by the number of sequential scans instead of the table size. The purpose of the substrings (stn.dbsname[1,15] and stn.tabname[1,20]) is only to make sure that the retrieved row is displayed on a single line within the dbaccess utility. You can remove it if you need the full database and table names.

You might also reset the statistics profile (onstat -z) before executing the query. Executing the query several times after the reset of the statistics shows you how many sequential scans occurred in this period.

IDS < 10

DATABASE sysmaster;
SELECT 
   stn.dbsname[1,15], 
   stn.tabname[1,18], 
   sti.ti_nptotal *
   (
      select sh_pagesize from sysshmvals
   )/1024/1024 mb_total,
   sti.ti_nrows, 
   spp.seqscans
FROM  systabnames stn, systabinfo sti, sysptprof spp
WHERE stn.partnum = sti.ti_partnum
AND   stn.partnum = spp.partnum
AND   spp.seqscans > 0
ORDER BY mb_total DESC, spp.seqscans DESC;

IDS >= 10

DATABASE sysmaster;
SELECT
   stn.dbsname[1,15],
   stn.tabname[1,18],
   sti.ti_nptotal *
   (
      select pagesize
         from  sysdbspaces
         where name = dbinfo('dbspace', sti.ti_partnum)
   )/1024/1024 mb_total,
   sti.ti_nrows,
   spp.seqscans
FROM  systabnames stn, systabinfo sti, sysptprof spp
WHERE stn.partnum = sti.ti_partnum
AND   stn.partnum = spp.partnum
AND   spp.seqscans > 0
ORDER BY mb_total DESC, spp.seqscans DESC;
Copyrightblog |