[성능] SQL Optimizer Profile

요즘에도 오라클의 Hint가 SQL 튜닝의 방법으로 선호되는지 모르겠다.

JAVA 기반의 개발 환경에서 iBatis 등을 이용하여 XML 파일에 SQL을 중앙집중화(?) 방식으로 사용하는 추세이고, 오라클 아닌 다른 DBMS를 사용하더라도 개발된 SQL 수정없이 사용하는 요건이 중요해 지면서 “특정 DBMS의 기능”에 편중되는 방식은 지양되고 있는 것 같다.

사용 중인 오라클이 버전 업그레이드를 한다던가 하는 경우에 Hint가 사용된 SQL은 성능 저하를 일으키는 경우을 들어 본 적도 있다.

 

SQL의 성능 개선으로 Hint 사용이 중요한 튜닝 방법으로 인식되어 왔지만, 개인적으로 이런 방법은 “호환되지 않는" 구속성이 있어 튜닝 방법으로서는 부정적인 시각으로 보게 되었다.

 

DB2에서 실행 계획 변경은 Optimizer Guideline 이라는 XML 방식으로 한다.

간편한 방식은 SQL 뒤에 XML 태그를 붙이는 방식으로 많이 알려진 방식이다. 이외 optimizer profile 을 만들어서, 즉 ibatis의 xml 처럼 XML 문서에 SQL과 Guideline을 설정하여 사용도 가능하다.

 

1. 테스트 시나리오

   - 목적: 데이터 정렬을 ORDER BY 를 사용하지 않고, Index를 이용하여 오름차순으로 데이터 결과 집합을 반환

   - 테이블 명: T2  (컬럼: C1, C2, C3)

   - 인덱스    : T2_IDX2 (C2)

                    T2_IDX3 (C2, C3)

db2 +p -tv << EOF connect to sample ; drop table t2 ; create table t2 (c1 int, c2 int, c3 int) ; create index t2_idx2 on t2 (c2) ; create index t2_idx3 on t2 (c2,c3) ; insert into t2 values (1,5,10),(2,10,9),(3,3,8),(4,6,7),(5,9,6),(6,7,5),(7,5,1),(8,3,4); runstats on table inst15.t2 and detailed indexes all ; terminate ; EOF

     - 테스트 SQL

db2 "select * from t2 where c2 > 0"

 

2. OPTIMIZER PROFILE 기능 적용

db2set db2_optprofile=yes db2start

 

3. 실행 계획 확인

-- PLAN 테이블 생성 $ db2 "call sysproc.sysinstallobjects('EXPLAIN','C',NULL,CURRENT SCHEMA)" -- 최적화 프로파일 테이블 생성 $ db2 "call sysproc.sysinstallobjects('OPT_PROFILES', 'c', '', CURRENT SCHEMA)" cat > opt1.db2 << EOF select * from t2 where c2 > 0 EOF $ db2expln -d sample -f opt1.db2 -g –t …. Optimizer Plan:  Operator    (ID) RETURN   ( 1)    |  TBSCAN   ( 2)    | Table: INST15 T2   -- 결과 값 C1          C2          C3 ----------- ----------- -----------           1           5          10           2          10           9           3           3           8           4           6           7           5           9           6           6           7           5           7           5           1           8           3           4

* table scan이 선택된 이유는, 데이터가 적기 때문에 Index Scan 이 비효율적이라고 Optimizer 가 판단을 했기 때문이다.

  ( 통계정보를 갱신하지 않고 plan을 확인했다면 index scan이 수행되었을 것이다. 초보 시절 index scan 하던 것이 통계 수집 후 table scan 한 것을 이상하게 생각한 적이 있었다 ^^; )

 

4. 프로파일 작성 및 등록

파일명: opt1.xml

<?xml version="1.0" encoding="UTF-16"?>   <OPTPROFILE VERSION="10.5.0"> <STMTPROFILE ID="TEST1">     <STMTKEY>              <![CDATA[select * from t2 where c2 > 0]]>     </STMTKEY>       <OPTGUIDELINES>             <IXSCAN TABLE="T2" INDEX="T2_IDX2"/>     </OPTGUIDELINES> </STMTPROFILE> </OPTPROFILE>

(참고) XML 문서 명은 “스키마명.프로파일명.업무명.xml” 식으로 사용하는 것이 관리 상 좋을 것 같다.   “업무명"은 해당 SQL을 쉽게 파악할 수 있는 “업무 단위” 혹은 “테이블 단위” 등으로 명명하면 될 것 같다.

 

등록

cat > opt1.del << EOF "INST15","TEST","opt1.xml" EOF   $ db2 import from opt1.del of del modified by lobsinfile replace into systools.opt_profile

(주의) 스키마 명(INST15), 프로파일명(TEST)”를 지정해야 한다.

        프로파일명은 임의적이고, 스키마 명은 PLAN 테이블의 스키마 명을 지정한다.

 

5. PLAN 변경 확인

-- 프로파일 명 지정 $ db2 SET CURRENT OPTIMIZATION PROFILE="TEST"   -- PLAN만 수집 $ db2 SET CURRENT EXPLAIN MODE EXPLAIN   -- SQL 수행 $ db2 "select * from t2 where c2 > 0"   -- PLAN 확인 $ db2exfmt -d SAMPLE -1 -o exfmt1.out $ cat exfmt1.out … Profile Information: -------------------- OPT_PROF: (Optimization Profile Name)         INST15.TEST STMTPROF: (Statement Profile Name)         TEST1 … Access Plan: -----------         Total Cost:             6.83711         Query Degree:           1                 Rows                RETURN                (   1)                 Cost                  I/O                  |                   7                FETCH                (   2)                6.83711                   1            /-----+-----\           7               8 ...skipping one line        (   3)            T2       0.0233252          Q1           0          |           8  INDEX:    INST15        T2_IDX2          Q1   $ db2 SET CURRENT EXPLAIN MODE no $ db2 "select * from t2 where c2 > 0"   C1          C2          C3 ----------- ----------- -----------           3           3           8           8           3           4           1           5          10           7           5           1           4           6           7           6           7           5           5           9           6           2          10           9   8 record(s) selected with 1 warning messages printed.

 

만일 C2와 C3에 대해서 오름차순 정렬을 한다면

db2 +p –tv << EOF connect to sample ; select * from t2 where c2 > 0 /* <OPTGUIDELINES>      <IXSCAN TABLE='T2' INDEX='T2_IDX3'/>    </OPTGUIDELINES> */ ; EOF -- 결과 C1          C2          C3 ----------- ----------- -----------           8           3           4           3           3           8           7           5           1           1           5          10           4           6           7           6           7           5           5           9           6           2          10           9

 

Embedded SQL 및 Package 에서 Guideline을 적용하려는 경우,  prepare 단계에서 profile을 선언하면 된다.

 

문서 참고: http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.perf.doc%2Fdoc%2Fc0024522.html

 

테스트는 Windows, Linux, AIX에서 했으나, Windows에서는 Guideline 이 수행되지 않았다.

table scan이 발생하는 SQL을 Index Scan으로 우회할 수 있다면, Guideline을 사용하는 것이 도움을 줄 수 있을 것이다.

성능이 좋지 않은 SQL을 디자인 어드바이저(db2advis) 등을 통하여 개선되지 않는 경우, 고려를 해 보면 좋을 듯 하다.