이 쿼리는 Sequential Scan이 자주 일어나는 table을 보여주는 쿼리입니다. 이 쿼리 결과는 onmode -z을 하게 되면 초기화되며 보여주는 결과 방식에 의해서 버전에 따른 두가지 방식이 존재합니다. 아래의 쿼리 결과는 자주 발생하는 Sequential scan table을 보여주는 것으로 쿼리가 느릴경우 tuning point 잡기가 훨씬 수월하실 겁니다.

다음은 쿼리입니다.

===================================================
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;

========================================================================

당연히 원문이 뒤따라 갑니다.

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;