안녕하세요. 본 글은 송수광님께서 <마이크로소프트(프로그래밍 전문 잡지)> 에 기고하신 DB2 기술 컬럼입니다. 유익하고 유용한 사례들이 많이 제공되어 있으니, 활용해보시면 좋으실 것 같습니다.^^

출처 : 마이크로소프트
제공 : DB포탈사이트 DBguide.net


DB2 옵티마이저 활용한 SQL 튜닝 - DB2 옵티마이저 소개와 통계정보의 이해

일반 상용 DBMS 중에서 독자들이 많이 접하게 되는 제품은 Oracle과 MS SQL 정도다. 서점에서 볼 수 있는 DBMS 관련 서적도 대부분 이들 에 대한 것이다. 여담을 하면 필자가 처음으로 입사를 해서, DBMS 분야에 관심이 있다고 하니 임무로 DB2 기술지원이 할당됐다. 그 당시 DB2 는 호스트(메인프레임)에 사용되는 대형 사이트를 제외하고 운영되는 사이트가 많지 않아 흔히 접할 수 있는 제품은 아니었다. 입사하기 전까지 DB2를 접해보지 못했을 뿐더러 그 이름만 DBMS개론 수업시간에 얼핏 들어봤을 뿐이었다.

DB2 소개
DB2는 다양한 규모와 다양한 OS를 지원한다. 개인 사용자를 위한 익스프레스 버전부터 대규모 기업을 위한 엔터프라이즈 버 전 그리고 데이터웨어하우스(DW) 버전이 있으며, 또한 다양한 OS 리눅스, 윈도우, 유닉스(AIX, Solaris, HP-UX)를 지원한다. 따라서 운영되는 OS 차이 때문에 발생하는 부분을 제외하고는 대부분 동일한 사용환경을 제공한다.
과거와 비교하면 공공, 통신, 제조, 금융 등 다양한 분야에서 사용되고 있으며 운영되는 데이터의 양도 그 때와는 비교할 수 없을 만큼 커졌지만, 아직도 일반 사용자들이 참고할 만한 서적 이나 자료들이 많지 않은 것도 사실이다.
DB2와 관련된 여러 분야 중 필자는 이번 연재를 통해 DB2 옵 티마이저(Optimizer)의 특성과 사용자가 수행하는 SQL문에 대 한 실행계획을 수립, 옵티마이저가 사용하는 통계정보에 대한 이 해와 이를 바탕으로 한 SQL 튜닝방법을 소개하고자 한다.

DB2 옵티마이저
DB2에서 튜닝을 하기 위해서는 먼저 DB2 옵티마이저에 대한 이해가 필요하다. 옵티마이저는 사용자가 실행하는 SQL문이 최 적의 응답속도를 낼 수 있도록 액세스되는 테이블 순서, 사용할 인덱스, 조인 방식을 선택해 최적화된 실행계획을 만든다.
DBMS 옵티마이저는 비용기반(Cost-base)과 규칙기반 (Rule-base)으로 나눠진다. 비용기반 옵티마이저는 각각의 비용 (비용 산정요소는 CPU, I/O, 메모리, 네트워크 등)을 계산해 가 장 낮은 비용의 실행계획을 세우는 것이며, 규칙기반 옵티마이저 는 정해진 규칙(검색 방식, 조인 방식, 사용될 인덱스의 특성, 검 색 조건 등)을 기준으로 실행계획을 세우는 것이다. 대부분의 DBMS는 비용기반 옵티마이저를 사용하고 있다. 필자가 생각하는 DB2의 가장 큰 특징은 오랜 기간 동안(DB2 는 처음부터 비용기반 옵티마이저를 사용)발전해온 비용기반 옵 티마이저의 신뢰성(통계 정보가 잘못된 경우 가끔 의도하지 않 은 액세스 플랜(Access Plan)을 만들기도 하는데, 이 내용은 후 에 사례를 통해서 설명하도록 하겠다)과 쿼리 리라이트(Query Rewrite)다. 통계정보에 대한 설명에 앞서 쿼리 리라이트 기능 을 설명하면, 사용자가 수행한 SQL문에 대해 옵티마이저가 최 적화된 실행계획을 만들기 위한 의미상 동일한 SQL문으로 변경 시키는 것을 말한다. 몇 가지 예를 들면

CASE 1. 사용자가 사용할 EMP 테이블의 EMPNO 컬럼에 unique인덱스 또는 primary key가 정의된 경우, 사용자가 이를 모르고 아래와 같은 SQL문을 수행하면 옵티마이저는 DISTINCT 문을 제거해 불필요한 정렬작업을 수행하지 않도록 한다. 이는 필요 없는 구문을 제거한다는 뜻과 같다. 

- 원본 SQL문
SELECT DISTINCT EMPNO, FIRSTNME
FROM EMP;

- Rewrite된 SQL문
SELECT EMPNO, FIRSTNME
FROM EMP;

CASE 2. 정의되어 있는 뷰에 추가적인 검색조건(predicate)을 사용할 경우, 해당 뷰에 의해 생성된 결과에 추가적으로 검색조 건을 반영하는 것이 아닌 뷰를 생성하는데 정의된 SELECT문에 해당 검색조건을 함께 사용해 결과를 반환하게 된다. 

- 원본SQL문
CREATE VIEW V_D21_EMP AS
SELECT *
FROM EMPLOYEE
WHERE WORKDEPT = ‘D21’;
SELECT *
FROM V_D21_EMP
WHERE EMPNO = ‘000250’;
- Rewrite된 SQL문
SELECT *
FROM EMPLOYEE
WHERE EMPNO = ‘000250’
AND WEORKDEPT =‘ D21’; 

CASE 3. 사용자가 사용한 검색 조건에 숨겨진 조건을 추가한다.

- 원본 SQL문
SELECT EMP.EMPNO, EMP.FIRSTNME, DEPT.DEPTNAME
FROM EMP, DEPT
WHERE EMP.WORKDEPT = DEPT.DEPTNO
AND EMP.WORKDEPT = ‘A01’;

- Rewrite된 SQL문
SELECT EMP.EMPNO, EMP.FIRSTNME, DEPT.DEPTNAME
FROM EMP, DEPT
WHERE EMP.WORKDEPT = DEPT.DEPTNO
AND EMP.WORKDEPT = ‘A01’
AND DEPT.DEPTNO =‘ A01’;
 

CASE 4. 서브쿼리(Subquery)문을 조인문으로 변경한다.

- 원본 SQL문
SELECT EMPNO, FIRSTNAME
FROM EMP
WHERE WORKDEPT IN
(SELECT DEPTNO
FROM DEPT
WHERE DEPTNAME =‘ SALES’);

- Rewrite된 SQL문
SELECT EMP.EMPNO, EMP.FIRSTNAME
FROM EMP, DEPT
WHERE EMP.WORKDEPT = DEPT.DEPTNO
AND DEPT.DEPTNAME =‘ SALES’;  

<그림 1>은 DB2 옵티마이저의 실행계획 수립단계를 나타낸 그림으로 실행계획을 수립하는 단계 중 쿼리를 다시 쓰는 단계를 거치면서 옵티마이저가 최적화된 실행계획을 수립하기 위해 내 부적으로 사용자가 수행한 SQL문을 수정해 실행하는 것을 알 수 있다.

통계정보
옵티마이저를 설명하며 통계정보를 사용한다고 언급했는데 과 연 어떤 통계정보를 어떻게 사용되는지 자세히 살펴보도록 한다. DB2에서 수집되는 통계정보는 시스템 카달로그 테이블(DB2 의 모든 오브젝트에 대한 정보를 저장하는 메타데이터로서 SYSIBM 스키마로 생성됨)에 저장된다. 통계정보는 직접 카달 로그 테이블에서 조회가 가능하지만, 카달로그 테이블에서 사용 자가 자주 사용하고 통계정보와 관련된 컬럼들로 이뤄진 카달로 그 뷰(SYSCAT 스키마로 생성됨)에서도 조회가 가능하다. 그 내용이 방대하여 다 다를 수는 없지만 필자가 튜닝을 진행하는 동안 자주 참조하는 카달로그 뷰와 그에 해당하는 컬럼 위주로 설명한다.
분포도 정보를 이해하기 위해 간단한 예를 통해 확인해 보자. EMP 테이블의 데이터는 전체 100건이며, 컬럼 데이터로 구성되 어 있다.
인덱스를 생성했다. 

이제 통계정보를 수집해 보자. DB2에서 통계정보를 수집하는 명령은 RUNSTATS이며, 다양한 옵션이 있는데 가장 흔히 사용 하는 옵션은 다음과 같다.
- 분포도 정보 없이 데이터와 인덱스의 통계정보 수집 RUNSTATS ON TABLE DB2V95.EMP AND INDEXES ALL; (참고로 DB2는 기본적으로 해당 데이터베이스에 접속한 사용자 계정 이 스키마명으로 사용되나 사용자 계정과 다른 별도의 스키마명을 생 성할 수도 있다. 앞으로 사용되는 예제 오브젝트들의 스키마명은 DB2V95 이며, 통계정보 수집 시에는 반드시 스키마명. 테이블명으로 기술해야 한다.) - 인덱스로 정의된 컬럼의 분포도 정보를 포함해 데이터와 인덱스의 통계 정보 수집
RUNSTATS ON TABLE DB2V95.EMP AND WITH DISTIRIBUTION ON KEY COLUMNS AND INDEXES ALL; 통계정보 수집 후 위에서 설명한 각각의 카달로그 뷰의 실제 데이터를 조회해 보면 <리스트 4>와 같은 결과를 얻는다. 

<리스트 4>를 통해 전체건수는 100건이고 물리적으로 2 페이 지를 사용하며 2010 -05-15에 통계정보 갱신했음을 알 수 있다. COLCARD는 해당 컬럼 데이터 중 고유한 값의 개수를 의미 한다. 전체 데이터 100건 중 사번과 휴대폰 번호는 각 사원별로 고유하므로 100이 되고, 성별은 남여 2가지이므로 고유값의 개 수가 2가 된다. 이름은 동명이인(박찬호-3, 한예슬-5)를 포함해 고유값 개수가 92다. 중요한 것은 이 데이터를 잘 이해하면 추후 인덱스를 추가할 때 어떤 컬럼의 종류와 순서로 인덱스를 생성할 지 판단하는데 도움이 된다(<리스트 5> 참조).

IDX1 인덱스는 사번 컬럼으로 된 인덱스로, 첫번째 컬럼에 해 당하는 고유값 개수인 FIRSTKEYCARD값이 100이다. 따라서 이 컬럼을 검색조건으로 사용하면 대부분의 데이터가 필터링 돼 액세스 데이터양이 줄어든다. 그러나 IDX4 인덱스는 성별, 입사 일, 지역 컬럼 순으로 된 인덱스로, 첫 번째 컬럼에 해당하는 성 별의 고유값 개수인 FIRSTKEYCARD값이 2다. 따라서 이 컬럼 을 검색 조건으로 사용하면 필터링 비율이 낮아 액세스 데이터양 이 늘어난다. 한편 인덱스의 첫 번째와 두 번째 컬럼은 고유값 개 수인 FIRST2KEYCARD값이 49로, 검색 조건으로 성별과 입사 일을 함께 사용할 경우 필터링 비율이 상승해 액세스 되는 양이 줄어든다(<리스트 6> 참조).
ClusterRatio는 해당 인덱스 순서대로 실제 물리적 데이터 페 이지가 정렬된 비율로, <그림 2>에서 보는 바와 같이 100% 가까 울수록 데이터 정렬비율은 높다. 그러나 앞선 예시는 데이터양이 많지 않아 모두 100%로 나타났을 뿐 실제 운영 데이터에서 이런 경우는 아쉽게도 거의 없다. 클러스터 비율이 높거나 범위검색 또는 해당 인덱스를 사용해 읽을 데이터양이 많을 경우 또는 데 이터가 물리적으로 정렬돼 모여 있을 때 액세스되는 데이터양이 줄어들어 성능이 향상된다. 

통계정보 수집 시 분포도 수집옵션(RUNSTATS 명령의 WITH DISTRIBUTION 구문)을 사용하는 경우에만 분포도 정 보가 수집돼 아래 SQL문으로 조회가 가능하다. 

DB2는 데이터베이스 설정과 관련된 데이터베이스 파라미터 (DB CFG)가 있으며, 통계정보수집과 관련된 파라미터 중 NUM_FREQVALUES(Frequency Value)는 10, NUM_QUANTILES(Quantile Value)는 20으로 각각 설정돼있 다. 이것은 해당 컬럼의 데이터 중 최상빈도 데이터 10개와 최소 값과 최대값을 임의의 20개 구간으로 나누고 각 구간 별로 누적 건수를 수집한다는 것을 의미한다.
RUNSTATS 수행 시 분포도 정보를 수집하면서 옵션을 지정 하면 해당 옵션이 사용되지만, 별도옵션을 사용하지 않으면 분포 도 수집과 관련된 DB CFG에 설정된 값이 사용된다. <리스트 8> 의 결과를 살펴보면(결과 건수가 많아 일부 데이터는 제외하였 으며, 카달로그 데이터에서 -1은 정보를 수집하지 않았음을 의미 한다), 각 컬럼 별로 TYPE F는 10개(SEQNO 1~10), TYPE Q 는 20개(SEQNO 1~20)를 수집했다. 모든 데이터는 고유한 사 번과 이름 중 중복 데이터가 있는 박찬호(3)와 한예슬(2)을 제외 하고 데이터를 수집하지 않아 COLVALUE는 NULL, VAL COUNT는 -1이 됐다. 이름 컬럼의 TYPE Q 데이터를 보면, 이 름이‘한예슬’보다 같거나 작은 데이터의 수는 99(VAL COUNT)이며 이중 유일한 데이터의 수는 93(COLCOUNT)이 다. 입사일 컬럼의 TYPE F 데이터를 보면 반복빈도가 높은 상 위 10개 데이터 중 첫 번째는‘2010-01-21’로 9이며, 열번째는 ‘2010-01-11’로 3이다.
만약 RUNSTATS 명령 수행 시 NUM_FREQVALUES 옵션 을 10보다 큰 값으로 지정하면 다음 빈도의 데이터들이 추가수 집 된다. TYPE Q 데이터를 보면 입사일이‘2010-01-02’보다 같 거나 작은 데이터는 2며, 입사일이‘2010-01-04’보다 같거나 작 은 데이터는 이전 구간 데이터 2건을 포함한 총 7건이다. 

설명한 통계정보가 이해됐다면 DB2 옵티마이저가 통계정보 를 어떻게 사용하는지 예를 통해 알아보자.

SELECT *
FROM EMP
WHERE 사번 = 10;
검색조건에 = 가 사용됐다. 분포도 정보 중 TYPE이 F인 값 들이 이용되며, 해당 컬럼에 중복된 데이터가 없어 VAL COUNT를 수집하지 않았으므로 옵티마이저는 이 SQL문의 수 행결과로 1건이 반환된다고 예상한다(실제는 9건이며, DB2는 8 건의 데이터가 반환된다고 예상한다).

SELECT *
FROM EMP
WHERE 이름 = 박찬호;
검색조건에 = 가 사용됐다. 분포도 정보 중 TYPE이 F인 값 들이 이용되며, 분포도 정보에서 이름이 ‘박찬호’인 VALCOUNT가 3인 정보가 있어 옵티마이저는 이 SQL문의 수 행결과로 3건이 반환된다고 예상한다(실제는 3건이며, DB2는 3 건의 데이터가 반환된다고 예상한다).

SELECT *
FROM EMP
WHERE 이름 < =‘ 강용석’;
검색조건에 범위가 사용됐으므로 분포도 정보 중 TYPE이 Q 인 값들을 이용한다. 분포도 정보에서 이름이‘강용석’보다 작거 나 같은 VALCOUNT가 5인 정보가 있고, 해당 구간에서 고유건 수를 의미하는 DISCOUNT가 5이므로 옵티마이저는 이 SQL문 의 수행결과로 5건이 반환된다고 예상한다(실제는 5건이며, DB2는 5건의 데이터가 반환된다고 예상한다).

SELECT *
FROM EMP
WHERE 입사일 < =‘ 2010-01-04’
검색조건에 범위가 사용되었으므로 분포도 정보 중 TYPE이 Q인 값들을 이용한다. 분포도 정보에서 입사일이‘2010-01-04’ 보다 작거나 같은 VALCOUNT가 7인 정보가 있고 DISCOUNT는 수집이 되지 않았다. 해당 구간에 중복 데이터가 존재한다는 의미이므로 옵티마이저는 이 SQL문의 수행결과로 약 7건 이상의 데이터가 반환된다고 예상한다(실제는 9건이며, DB2는 8건의 데이터가 반환한다고 예상한다).

SELECT *
FROM EMP
WHERE 입사일 BETWEEN‘ 2010-01-04’AND‘ 2010-01-30’;
검색조건에 범위가 사용되었으므로 분포도 정보 중 TYPE이 Q인 값들을 이용한다. 분포도 정보에서 입사일이‘2010-01-29’ 보다 작거나 같은 VALCOUNT가 95인 정보가 있고‘2010-01- 04’보다 작거나 같은 VALCOUNT가 7이며, DISCOUNT는 수 집이 되지 않았다. 해당구간에 중복 데이터가 존재한다는 의미 이므로 옵티마이저는 이 SQL문의 수행결과로 약 87건 이상의 데이터가 반환된다고 예상한다(실제는 93건이며, DB2는 약 94 건의 데이터가 반환된다고 예상한다).

SELECT *
FROM EMP
WHERE 성별 =‘ 남자’;
위와 같은 SQL문이 수행될 때 분포도 정보가 있다면 옵티마 이저는 85건의 데이터가 반환한다고 예상할 것이다. 그러나 분 포도 정보가 없다면 이를 판단할 수 없으므로 테이블의 CARD수 는 100이고, 성별 컬럼의 COLCARD는 2이므로 반환되는 행수 는 100/2인 50이라고 판단한다. 이 경우 조건이 남자이면 실제 데이터보다 더 적은 수의 데이터가 반환된다고 판단하며, 조건이 여자일 경우 실제 데이터보다 더 많은 수의 데이터가 반환된다고 판단한다.
다음 호에서는 DB2는 사용자의 SQL문을 어떻게 수행하는지 에 관한 액세스 플랜을 보는 법과 튜닝에 대한 몇몇 사례를 설명 하겠다.