database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
Oracle
ㆍInformix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Informix Q&A 2501 게시물 읽기
No. 2501
[TIP] SMI Query Series - Sequential Scans on Tables
작성자
김선규(cbspd)
작성일
2008-04-04 14:25
조회수
7,093

이 쿼리는 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;


[Top]
No.
제목
작성자
작성일
조회
2504Locale에 대하여 한방에 알려주시면 감사하겠습니다. [1]
거북이
2008-04-24
8428
2503[TIP] Free informix online Certification Assesment Testing 정보
김선규
2008-04-16
6725
2502쿼리 도와주세요 [1]
하늘
2008-04-04
6716
2501[TIP] SMI Query Series - Sequential Scans on Tables
김선규
2008-04-04
7093
2500[TIP] SMI Query Series - Database Size
김선규
2008-04-04
6892
2499instance down일때.. [2]
최재환
2008-04-02
5919
2498[TIP] SMI Query Series - Shared Memory Segments
김선규
2008-04-02
5937
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.047초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다