1부: Informix SQL 튜닝
(Part 1: Tuning Informix SQL)
예제 및 분석
SQL 쿼리는 Informix® 데이터베이스 어플리케이션의 백본을 형성한다. 이 글에서는 Informix를 위한
SQL 쿼리에 대한 지침 및 SQL 쿼리 튜닝 시 고려해야 할 사항을 알아 보고 필자의 경험에서 수집한
실제 튜닝 예제 몇 가지를 살펴 보도록 한다.
이 글에서 사용한 제품을 얻는 방법: developerWorks 가입자에게는 여러 DB2, Lotus, Rational, Tivoli 및
WebSphere 제품(Eclipse 기반 WebSphere Studio IDE 포함)의 단일 사용자 라이센스가 부여되므로 어플
리케이션 개발, 테스트, 평가 및 데모 목적으로 사용 가능하다. 가입자가 아니라면 지금 가입할 수 있다.
서론
SQL 쿼리 튜닝은 프로그래머 및 개발자의 임무로 간주되는 경우가 많지만 데이터베이스 관리자 역시
이 프로세스에 적극적으로 참여해야 한다. 데이터베이스 관리자가 SQL 쿼리 튜닝에 참여할 경우 얻게
되는 중요한 이점 중 하나는 다른 관점을 제시할 수 있다는 것이다. 프로그래머는 어플리케이션 성능에
중점을 두고 문제에 접근하지만 DBA는 데이터베이스 자체에 대한 이해를 바탕으로 문제에 접근한다,
따라서 데이터베이스 레이아웃, 테이블 및 인덱스 배치, Informix 및 시스템 자원의 효과적 활용(예: 데이
터 단편화, PDQ 우선 순위, CPU 시간, 메모리 사용, 데이터 저장)에 대해 조언과 제안을 내놓을 수 있
다. 때로는 프로그래머및 개발자가 효율성을 더 높이기 위해 쿼리를 수정할 수 있도록 성능과 관련하여
서로 다른 시각에서 쿼리를 바라보길 원하는 경우도 있다.
이 글에서는 다양한 튜닝 방법에 대해 살펴 본다. 1부에서는 튜닝 기준, 툴 및 일반적인 방법론을 다룬
다. 또한 OPTCOMPIND 매개 변수와 같은 옵티마이저의 역할, 쿼리 지시문, 내부 통계에 대해서도 설명
한다.
이어지는 2부에서는 액세스 방식, 조인 방식, 테이블 및 인덱스 범위, 인덱스 수준, 단편화 및 PDQ 우
선 순위 등 쿼리 자체의 성능 요인들에 대해 설명한다. 또한 2부에는 예제 및 분석이 포함되어 있다.
튜닝 기준
튜닝 기준은 주로 개별 비즈니스 요구 사항에 따라 결정된다. 일반적으로 시스템 및 데이터베이스의 성
능이 관건이다. 그렇다면 시스템 및 데이터베이스의 성능 프로필을 어떻게 결정할 것인가? 여러 가지
고려 사항이 존재하지만 가장 중요한 것은 응답 시간 및 자원 사용량이다. 응답 시간은 사용자가 자신
의 요청, 즉 SQL 쿼리가 완료될 때까지 기다리는 시간을 의미한다. 자원 사용량이란 SQL 쿼리 수행에
서 CPU, 메모리, 디스크 등과 같은 시스템 자원의 사용량을 가리킨다.
일반적으로 튜닝 과정을 마친 후 SQL 쿼리의 응답 시간이 더 짧아지고 시스템 자원의 사용량이 줄어들
기 때문에 효율성 향상을 위해 SQL 쿼리를 튜닝한다고 말할 수 있다. SQL 쿼리의 응답 시간 및 자원
사용량을 어떻게 측정하는가? Unix 및 Informix에서는 응답 시간 및 자원 사용량을 과학적, 정량적으로
측정하는 데 도움이 될 툴 및 유틸리티를 제공한다.
튜닝 툴
Unix 툴
time 및 timex
파일 전송, 데이터베이스 쿼리 실행 및 기타 활동 등 시스템 작업의 응답 시간을 보고할 때 time 유틸리
티를 사용할 수 있다. 다음은 time 유틸리티를 사용하여 간단한 데이터베이스 쿼리의 응답 시간을 측정
하는 예이다.
sys3523:omcadmin > time dbaccess airgen_cm_db sel.sql
Database selected.
(count(*))
5958
1 row(s) retrieved.
Database closed.
real 0m0.09s
user 0m0.01s
sys 0m0.06s
위 출력의 마지막 부분에서는 해당 작업, 즉 데이터베이스 쿼리의 시간 통계 정보를 자세하게 나타낸다.
• "real" 필드는 쿼리 시작부터 쿼리 종료까지 경과된 시간을 나타낸다.
• “user” 필드는 해당 작업을 위해 사용자 프로세서가 사용한 CPU 시간의 합계를 나타낸다.
• “sys” 필드는 시스템 전체적으로 사용한 CPU 시간의 합계를 나타낸다.
가장 관심 있게 살펴 볼 필드가 "real"이다. 여기서는 해당 작업의 응답 시간을 나타낸다. 위 예제를 자
세히 살펴 보면, 이번 쿼리의 응답 시간이 0.09초임을 알 수 있다.
timex는 time 유틸리티가 변형된 것으로 보다 쉽게 읽을 수 있는 형식으로 시간을 표시한다. 똑같은 쿼
리에 대한 timex 출력은 다음과 같다.
sys3523:omcadmin > timex dbaccess airgen_cm_db sel.sql
Database selected.
(count(*))
5958
1 row(s) retrieved.
Database closed.
real 0.09
user 0.02
sys 0.04
Vmstat
vmstat 유틸리티는 시스템을 탐색하여 실행 프로세서, 가상 메모리, 디스크 I/O, 트랩, CPU 등 시스템
자원의 사용 통계 정보를 보고한다. 또한 시스템이 재부팅되거나 부팅된 후 가상 메모리 활동을 한 줄
로 요약하여 표시한다. 다음은 출력의 예이다.
sys3523:omcadmin > vmstat 1 10
procs memory page disk faults cpu
r b w swap free re mf pi po fr de sr m0 m1 m4 m5 in sy cs
us sy id
0 0 0 1959208 1294824 141 824 1 1 1 0 0 0 0 0 0 906 946 700
2 3 95
0 0 0 1995568 1260288 0 46 0 0 0 0 0 0 0 0 0 834 386 213
0 0 100
0 0 0 1995568 1260288 0 40 0 0 0 0 0 0 0 0 0 884 265 199
0 1 99
0 0 0 1995568 1260288 0 40 0 0 0 0 0 0 0 0 0 834 325 186
0 0 100
0 0 0 1995568 1260288 43 286 0 0 0 0 0 0 0 0 0 869 1682 242
0 1 99
0 0 0 1995352 1260048 658 3503 0 0 0 0 0 0 0 0 0 827 21930 375
3 14 83
0 0 0 1995408 1260240 662 3495 0 0 0 0 0 0 0 0 0 825 22010 387
4 13 83
0 0 0 1995568 1260288 121 691 0 0 0 0 0 0 0 0 0 834 4310 261
1 3 96
0 0 0 1995568 1260288 0 40 0 0 0 0 0 0 0 0 0 824 250 188
0 0 100
0 0 0 1995568 1260288 0 40 0 0 0 0 0 0 0 0 0 824 365 214
0 0 100
vmstat 유틸리티는 time interval 및 count라는 두 매개 변수를 갖는데, 둘 다 정수이다. time interval은
vmstat가 갱신되는 간격을, count는 최대 갱신 횟수를 지정한다. 매개 변수가 지정되지 않으면 vmstat는
이전의 시스템 통계 정보를 나열할 뿐 갱신되지 않는다. 이 경우 제공되는 통계 정보는 정확하지 않으
며 최신 버전이 아니다. 간격이 지정되면 vmstat는 마지막 간격(초) 동안의 시스템 활동을 요약하기를
반복한다. count가 지정되면 vmstat는 지정된 count 횟수에 도달할 때까지 반복하여 시스템 통계 정보를
가져 온다.
이 출력에서 가장 관심을 끄는 부분은 "r", "po" 및 "id" 필드이다.
• "r" 필드는 시스템 대기열에서 자원 실행을 기다리는 작업이 몇 개인지 나타낸다.
• "po" 필드는 메모리에서 현재 페이지 아웃된 페이지의 수를 나타낸다. 이 숫자가 매우 크고 계
속 증가한다면 일반적으로 물리적 메모리, 즉 RAM이 부족한 것을 의미하며, 메모리를 추가 설
치해야 하는 경우도 있다.
• "id" 필드는 현재 얼마만큼의 시스템 CPU 자원이 사용 중인지 나타낸다.
이 필드들은 모두 현재 시스템 자원이 어떻게 사용되고 있는지 정확하게 파악하는 데 도움이 된다.
Informix 툴
Informix가 자세한 SQL 쿼리 플랜 및 실행 통계 정보를 수집하도록 제공하는 가장 종합적인 툴은 SET
EXPLAIN 유틸리티이다. 이 유틸리티는 sqexplain.out이라는 이름의 파일을 생성하고 쿼리 실행의 각 단
계를 상세하게 기록한다. 또한 쿼리 예상 비용을 제시하고 쿼리 결과를 예측한다. SET EXPLAIN 출력
파일을 검토하면서 쿼리 성능 개선을 위한 조치가 취해졌는지 확인할 수 있다. 다음 예는 매우 복잡한
쿼리에 대한 set explain 출력이다.
QUERY:
------
SELECT --+AVOID_FULL(omchn)+AVOID_FULL(daphn)
omchn.omc_hn_uanc,
nvl(daphn.gtt_version,"0000000000000000000"),
nvl(idachn.egt4_version,"0000000000000000000"),
nvl(ihlrhn.hlr_timestamp,"00000000000000"),
vsgw_hn.hn_igw_uanc,
nvl(vsgw_hn.hn_igw_version, "00000000000000")
FROM omchn, daphn, idachn, ihlrhn, vsgw_hn
WHERE daphn.dap_hn_inst = omchn.omc_hn_inst
AND idachn.idac_hn_inst = omchn.omc_hn_inst
AND ihlrhn.hlr_hn_inst = omchn.omc_hn_inst
AND vsgw_hn.vsgw_hn_inst = omchn.omc_hn_inst
DIRECTIVES FOLLOWED:
AVOID_FULL ( omchn )
AVOID_FULL ( daphn )
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 8
Estimated # of Rows Returned: 1
1) root.idachn: SEQUENTIAL SCAN
2) root.daphn: INDEX PATH
(1) Index Keys: dap_hn_inst (Serial, fragments: ALL)
Lower Index Filter: root.daphn.dap_hn_inst =
root.idachn.idac_hn_inst
NESTED LOOP JOIN
3) root.vsgw_hn: SEQUENTIAL SCAN
NESTED LOOP JOIN
4) root.omchn: INDEX PATH
Filters: root.vsgw_hn.vsgw_hn_inst =
root.omchn.omc_hn_inst
(1) Index Keys: omc_hn_inst (Serial, fragments:
ALL)
Lower Index Filter: root.idachn.idac_hn_inst =
oot.omchn.omc_hn_inst
NESTED LOOP JOIN
5) root.ihlrhn: INDEX PATH
(1) Index Keys: hlr_hn_inst (Serial, fragments:
ALL)
Lower Index Filter: root.ihlrhn.hlr_hn_inst =
root.omchn.omc_hn_inst
NESTED LOOP JOIN
위 출력은 세 부분으로 나눌 수 있다.
• 첫 부분은 쿼리 구문을 나타낸다.
• 두 번째 부분은 쿼리의 예상 비용을 나타낸다.
• 세 번째 부분은 쿼리 실행의 각 단계를 자세하게 설명한다.
가장 관심을 끄는 부분은 두 번째 및 세 번째 부분이다. 예상 비용이란 옵티마이저가 쿼리 플랜을 비교
하기 위해 사용하는 비용 단위이다. 이 단위는 곧바로 시간으로 변환되지 않으며, 일반적인 디스크 액세
스에 대한 상대적 시간을 나타낸다.
옵티마이저는 평가된 플랜 중에서 예상 실행 비용이 가장 낮은 이 쿼리 플랜을 선택했다. 예상 비용이
더 높은 쿼리는 일반적으로 비용이 더 낮은 쿼리보다 실행 시간이 길어진다. 세 번째 부분은 쿼리 튜닝
측면에서 매우 중요하다. 쿼리에서 사용한 데이터 액세스 방법, 조인 방법 등 유용한 정보를 많이 제공
하기 때문이다. 위 예에서는 순차적인 스캔을 보여 주며, 데이터 검색을 수행하기 위해 인덱스를 사용하
고 모든 데이터를 조인하기 위해 nested-loop 조인 방법을 사용했다. 여기에 대해서는 이 글의 뒷 부분
에서 자세히 설명하도록 한다.
이 유틸리티는 쉽게 사용할 수 있다. 어떤 SQL 쿼리에 대해 자세한 쿼리 실행 플랜을 알고 싶다면 아
래와 같이 원래 쿼리의 앞에 SET EXPLAIN ON 문을 추가하면 된다.
set explain on;
select count(*) from acg;
그러면 Informix server는 사용자의 홈 디렉토리에 sqexplain.out이라는 파일을 만들고 자세한 쿼리 실행
플랜 및 위에서 설명한 비용을 기록한다. 이 파일은 누적형이다. 즉 SET EXPLAIN ON 문 다음에 여러
SQL 쿼리가 오면 각 쿼리 실행 플랜 및 그 비용은 이 파일이 제거될 때까지 파일에 첨부된다. 저장 프
로시저의 경우, 원래 저장 프로시저에 대해 UPDATE STATISTICS를 실행하여 자세한 실행 플랜을 얻는
다. 저장 프로시저는 통계를 업데이트할 때만 쿼리 실행 플랜을 업데이트할 수 있기 때문이다. 예를 들
어, 저장 프로시저 dap_int에 대해 자세한 실행 플랜을 보려면 다음과 같이 해야 한다.
set explain on;
update statistics for stored procedure dap_int();
버전 9.3부터는 이 유틸리티가 훌륭하게 향상되었다. 쿼리를 실행하지 않고서도 자세한 쿼리 실행 플랜
을 얻을 수 있는 것이다. 따라서 실제 운영 환경에서 쿼리 실행 플랜을 얻는 것이 가능해졌다. 이 새로
운 기능을 이용하려면 다음과 같이 키워드 AVOID_EXECUTE를 SET EXPLAIN ON 문에서 사용해야 한
다.
set explain on avoid_execute;
select count(*) from act;
이 유틸리티 사용 방법에 대한 자세한 내용은 IBM Informix Dynamic Server Administrator's Guide, Version
9.4를 참조한다.
일반 방법론
그렇다면 이러한 툴을 SQL 쿼리 튜닝에 어떻게 적용하는가? 사람마다 방식이 달라질 수 있지만 일반적
으로 아래에 요약된 방법론 및 단계를 따라야 한다.
1. 원래 SQL 쿼리에 대한 통계를 수집한다. 이 단계에서는 위에서 설명한 툴을 사용하여 쿼리에
대한 통계를 얻어야 한다. 나중에 심층 분석을 위해 응답 시간, 자세한 실행 플랜 및 비용을 확
인한다.
2. 통계를 분석한다. 이 단계에서는 위에서 수집한 통계 정보를 심층 분석하면서 쿼리 실행 플랜을
정확하게 파악해야 한다. 위에서 설명한 것처럼 쿼리 튜닝에서는 성능이 가장 중요한 관심사이
다. 쿼리 플랜 검사 시 성능에 영향을 미치는 모든 요인(액세스 방법, 조인 방법, 서브쿼리, 테
이블 및 인덱스 범위, 테이블 및 인덱스 단편화 등)을 고려해야 한다. 각 요인에 대해서는 2부
에서 자세히 설명할 것이다.
3. 테스트 환경을 설정한다. 이는 매우 중요한 단계이다. 테스트 환경은 쿼리가 실행되는 운영 환
경과 동일한, 또는 매우 유사한 하드웨어/소프트웨어 구성으로 설정해야 한다. 예를 들어, 운영
시스템에 400HM CPU 6개가 있다면 테스트 시스템 역시 400HM CPU 6개를 갖춰야 한다. 그렇
지 않으면 앞으로 실시할 테스트는 아무런 유효성 및 연관성을 갖지 않는다. 모든 쿼리는 최종
적으로 운영 환경에서 실행된다는 점을 명심한다.
4. 변경 작업을 수행하고 새로운 쿼리를 테스트한다. 이는 중요한 단계이며 또한 튜닝 과정 중 가
장 지루한 단계이기도 하다. 원래 쿼리에서 한번에 하나씩 변경하고 성능이 개선되었는지(응답
시간 단축) 테스트를 통해 확인한다. 테스트 세부 사항, 이를테면 변경 사항, 응답 시간, 실행
플랜 등을 기록한다. 변경 작업 후 쿼리 성능이 원래 쿼리보다 나아지지 않는다면 변경 이전으
로 돌아간다. 테스트는 유효하고 연관성을 가져야 한다. 즉 반복 가능해야 한다. 예를 들어, 같
은 쿼리를 대상으로 동일한 테스트를 두 차례 실시했는데 첫 번째 테스트에서는 응답 시간이
매우 우수했지만(예: 10초) 두 번째에는 응답 시간이 30초로 늘어났다면 두 테스트의 응답 시간
차이가 너무 크기 때문에 이는 반복 가능한 테스트가 아니다. 반복 가능한 테스트라면 테스트간
차이가 최소화되어야 한다.
5. 테스트 결과를 분석한다. 테스트 결과를 분석할 때는 그 결과의 유효성 및 신뢰성을 검사해야
한다. 하드웨어, 소프트웨어, 부하 및 그 밖의 모든 요소를 검사하여 테스트 결과가 유효하고
신뢰할 만한지 확인하도록 한다.
6. 운영 시스템에서 개선 사항을 구현한다. 구현에 앞서 마지막으로 상세한 검토를 실시하고 새 쿼
리가 운영 환경에서 어떤 문제도 발생시키지 않는지 확인한다.
옵티마이저와 그 역할
Oracle, SQL Server와 같은 다른 관계형 데이터베이스 관리 시스템과 마찬가지로 Informix도 내부에 옵
티마이저가 있어 최상의 쿼리 실행 플랜을 선택한다. SQL 쿼리에 대해 구문 분석이 실시된 다음 옵티마
이저는 디스크 I/O, CPU 비용 등과 같은 요인들을 분석하여 가능한 모든 쿼리 실행 방법을 검토한다.
그런 다음 상향식 BFS(breath-first search) 전략을 동시에 구사하면서 가능한 모든 플랜을 구성한다.
다시 말해 옵티마이저는 먼저 가능한 모든 조인 플랜을 구성해 보고 중복된 쌍(다른 조인 쌍과 동일한
테이블을 포함하고 동일한 행 집합을 생성하는 조인 쌍) 중 더 비싼 플랜을 제거한다. 만약 쿼리에서 추
가 테이블을 사용한다면, 옵티마이저는 조인할 추가 테이블마다 나머지 중복 쌍 각각을 새로운 테이블
에 조인시켜 가능한 모든 조인 쌍을 만들고 더 비싼 중복 테이블 쌍을 제거하는 식으로 작업한다. 가능
한 조인 조합 중 중복되지 않는 세트가 있다면 옵티마이저는 실행 비용이 가장 낮은 플랜을 선택한다.
예를 들어, 옵티마이저는 인덱스 사용 여부를 결정하거나, 쿼리에 조인이 사용된다면 조인 방법(hash,
sort merge 또는 nested loop) 및 테이블의 조인 순서를 결정해야 한다.
옵티마이저는 각 테이블에서 읽어야할 행 수를 기준으로 쿼리 예상 비용을 계산한다. 또한 예상 행 수
는 WHERE 절 내부에서 사용된 각 조건 표현식의 선택도에 따라 달라진다. 옵티마이저는 UPDATE
STATISTICS에서 생성한 데이터 분배 정보를 사용하여 쿼리의 필터 선택도를 계산한다. 하지만 데이터
분포 정보가 없다면 옵티마이저는 테이블 인덱스를 기준으로 다른 유형의 필터 선택도를 계산한다. 예
를 들어, 인덱싱된 열에 literal 값 및 NULL 값이 있다면 선택도는 인덱스에 있는 서로 다른 키의 개수와
동일하다. 데이터 분배 정보가 없을 때 옵티마이저가 선택도 계산에 사용하는 자세한 내용에 대해서는
Performance Guide 10장을 참조한다. 하지만 이런 방식으로 계산된 선택도는 데이터 분배를 사용하여
계산한 선택도만큼 정확하지 않다.
따라서 선택도 계산의 정확성은 UPDATE STATISTICS 실행 횟수에 따라 달라진다. UPDATE STATISTICS를
자주 실행한다면(low 옵션으로 UPDATE STATISTIC를 실행하는 경우를 제외하고) 그 때마다 데이터
분배가 업데이트되므로 옵티마이저는 더 정확하게 선택도를 계산한다.
옵티마이저는 쿼리 플랜을 만들면서 다음과 같은 시스템 카탈로그 정보를 사용한다.
• 테이블의 행 수 – 가장 최근에 실시한 UPDATE STATISTICS 문 기준
• 컬럼이 고유한 값을 갖도록 제약 받는지 여부
• 컬럼 값의 분산도 - UPDATE STATISTICS 문에서 MEDIUM 또는 HIGH 키워드로 요청한 경우
• 데이터가 저장되어 있는 디스크 페이지 수
• 테이블에 존재하는 인덱스 – 인덱싱하는 컬럼, 오름차순/내림차순 여부, 클러스터링 여부 포함
• 인덱스 구조의 깊이 (인덱스 조회를 수행하는 데 필요한 작업의 양 측정)
• 인덱스 항목이 차지하는 디스크 페이지 수
• 인덱스의 Unique 값 정도 – 동등 필터가 반환하는 행 수를 계산할 때 사용 가능
• 인덱스로 사용되는 컬럼에서, 두 번째로 크고 두 번째로 작은 키 값
옵티마이저의 동작은 Informix 구성 파일의 OPTCOMPIND 매개 변수 값, 쿼리 지시문 그리고 내부 통계
값의 정확성, 이 세가지 핵심 요인이 영향을 미친다.
OPTCOMPIND 매개 변수
OPTCOMPIND는 Informix 구성 파일에 있는 환경 변수 또는 매개 변수이다. 옵티마이저는 이 값을 사용
하여 데이터 액세스 방법을 선택한다. 3가지 값(0, 1 및 2) 중 하나를 갖는데, 다음과 같은 의미가 있다.
• 값이 0으로 설정된 경우, 적합한 인덱스가 존재한다면 옵티마이저는 인덱스 스캔을 선택한다.
예상 비용도 고려하지 않는다.
• 값이 1로 설정된 경우, 트랜잭션 격리 모드가 Repeatable Read가 아니라면 옵티마이저는 값이
0인 것처럼 동작한다. 트랜잭션 격리 모드가 Repeatable Read라면 옵티마이저는 순전히 예상
비용만을 기준으로 선택한다.
• 값이 2로 설정된 경우, 옵티마이저는 트랜잭션 격리 모드와 상관 없이 예상 비용을 사용하여 실
행 플랜을 결정한다.
OPTCOMPIND를 환경 변수나 매개 변수로 설정할 수 있지만, onconfig의 매개 변수로 설정해야 실행 시
우선 순위를 갖는다.
쿼리 지시문
옵티마이저에 영향을 미치는 또 한 가지 방법은 쿼리 지시문을 사용하는 것이다. 쿼리 지시문이란 SQL
쿼리에 포함된 힌트로서 옵티마이저에게 쿼리 실행 방법을 지시한다. 쿼리 지시문에는 다음과 같이 4가
지 유형이 있다.
• 옵티마이저에게 데이터 검색을 위해 지정된 액세스 방법, 즉 순차적 스캔 또는 인덱스 스캔을
사용하게 하는 액세스 플랜 지시문
• 옵티마이저에게 지정된 순서대로 테이블을 조인하게 하는 조인 순서 지시문
• 옵티마이저에게 지정된 조인 방법(nested loop 조인, sort merge 조인, dynamic hash 조인)을 사용
하여 쿼리에서 테이블을 조인하게 하는 조인 방법 지시문
• 옵티마이저에게 지정된 규칙을 사용하여 쿼리 결과를 반환하게 하는 목표 지시문
이 지시문 사용 방법에 대한 자세한 내용은 IBM Informix Dynamic Server Performance's Guide, Version
9.4의 11장을 참조한다.
내부 통계
내부 통계란 시스템 카탈로그에 있는 통계로서 옵티마이저가 가장 저렴한 쿼리 실행 플랜을 결정할 때
사용하는 데이터를 의미한다. 옵티마이저가 최상의 쿼리 플랜을 선택하려면 내부 통계를 정확하고 최신
버전으로 유지해야 한다. 데이터베이스 서버는 데이터베이스 테이블이 생성되면 테이블, 인덱스, 저장
프로시저, 트리거와 같은 데이터베이스 객체의 통계 프로필을 초기화하고 시스템 카탈로그에 데이터 분
배를 포함시킨다. 그러나 이 통계를 자동으로 업데이트하지는 않는다.
통계 프로필을 최신 버전으로 유지하려면 UPDATE STATISTICS를 정기적으로 실행해야 한다. 그렇지 않
으면 시스템의 통계 프로필이 시스템의 최신 상태를 반영하지 못해 옵티마이저가 수많은 쿼리 실행 플
랜 중에서 올바른 선택을 할 수 없다. UPADTE STATISTICS를 실행하는 3가지 모드가 있으며, 일반적으
로 많은 양의 테이블 데이터를 변경하는 대형 배치 작업을 수행한 후, 또는 테이블에 인덱스를 추가한
후에는 반드시 UPDATE STATISTICS를 실행하도록 한다. UPDATE STATISTICS 수행 방법에 대한 자세
한 내용은 IBM Informix Dynamic Server Administrator's Guide, Version 9.4를 참조한다. 가장 중요한 원칙
은 UPDATE STATISTICS를 자주 실행할수록 시스템의 통계 프로필이 정확하고 최신 버전이 된다는 것
이며, 또한 옵티마이저가 올바른 쿼리 실행 플랜을 선택할 가능성도 높아진다.
앞서 설명한 대로 옵티마이저의 동작이 OPTCOMPIND 및 지시문의 영향을 받지만 일반적으로 옵티마
이저는 다음 지침을 기준으로 쿼리 플랜을 선택한다.
• 옵티마이저는 쿼리가 테이블에서 많은 양의 데이터를 검색할 경우 인덱스를 사용하지 않는다.
예를 들어, 회사의 고객이 50개 주 전체에 걸쳐 골고루 분포된 경우에 뉴욕을 제외한 모든 주의
고객 정보를 검색하려면 다음과 같이 쿼리를 실행한다.
SELECT * FROM customer WHERE STATE <> "NEW YORK";
곧 옵티마이저는 테이블의 데이터 중 98%를 검색할 가능성이 있음을 감지하고, 인덱스를(그리
고 데이터 페이지를) 살피기 보다는 테이블을 순차적으로 읽거나 스캔한 다음 관련 데이터를
가져오는 것이 더 효율적이라고 판단한다.
• 테이블에 여러 인덱스가 정의되었다면 옵티마이저는 테이블 중 가장 많은 데이터를 제외시킬 수
있는 인덱스를 사용한다. 예를 들어, 뉴욕에 20만 명의 고객이 있는데 1997년 1월 20일에는 약
1천 명의 고객만 주문했다면 그 이름과 주소를 얻기 위해 다음과 같이 쿼리를 실행한다.
SELECT name, address FROM customer
WHERE state = "NEW YORK" AND order_date = "01/20/97"
옵티마이저는 아마 주에 대한 인덱스보다는 order_date에 대한 인덱스를 사용할 것이다.
• 쿼리에 지시문이 전혀 없다면 옵티마이저는 가장 제한적인 필터를 먼저 사용하여 테이블에서 데
이터를 검색한다. 다음 쿼리를 살펴 보자.
SELECT * FROM customer, orders
WHERE customer.customer_num = orders.customer_num
AND customer.state = "NEVADA";
이 예에서 옵티마이저는 가장 먼저 customer_state=NEVADA라는 조건을 평가하는데, 그러면 많
은 수의 데이터 행을 제외시킬 수 있기 때문이다. 그런 다음 두 테이블을 조인한다. 즉 데이터베
이스 서버의 부하를 최대한 줄이는 것이다. 옵티마이저가 먼저 두 테이블 조인에 나선다면, 그 조
인 결과가 엄청난 양이 되어 CPU, 메모리와 같은 시스템 자원을 많이 사용할 수 있다. 평균 사용
자 수가 1백만 명이고 각각 매달 한 차례씩 주문한다면, 조인 결과 최소한 1백만 건의 레코드가
반환되며 분명 시스템 성능은 크게 저하될 것이다.
• 옵티마이저는 조인된 열 중에서 인덱스가 있는 열이 없다면 dynamic hash 조인을 선택한다. 이
전 예에서 customer.customer_num 및 orders.customer_num이 인덱싱되지 않았다면 옵티마이저
는 최상의 실행 플랜으로 dynamic hash 조인을 선택했을 것이다.
• 옵티마이저는 다음과 같은 경우에 nested-loop 조인을 선택한다.