• e-book
• 회원가입
• 정기구독신청
• 전체보기
• 로그인

DB2 옵티마이저 소개와 통계정보의 이해

프린트 이메일 전송

송수강 duck921@hanafos.com｜현재 LIG시스템 TA팀 소속으로 LIG손해보험 DBA 업무를 담당하고 있으며, 이전에는 여러 해 동안 DB2 기술지원과 다양한 DB2 프로젝트의 DBA 업무를 담당하였다. 아직도 DB2의 여러 부분을 파고들어야 해서 항상 고민하고 있다.

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> 옵티마이저의 실행계획 수립단계

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

옵티마이저를 설명하며 통계정보를 사용한다고 언급했는데 과연 어떤 통계정보를 어떻게 사용되는지 자세히 살펴보도록 한다.

DB2에서 수집되는 통계정보는 시스템 카달로그 테이블(DB2의 모든 오브젝트에 대한 정보를 저장하는 메타데이터로서 SYSIBM 스키마로 생성됨)에 저장된다. 통계정보는 직접 카달로그 테이블에서 조회가 가능하지만, 카달로그 테이블에서 사용자가 자주 사용하고 통계정보와 관련된 컬럼들로 이뤄진 카달로그 뷰(SYSCAT 스키마로 생성됨)에서도 조회가 가능하다. 그 내용이 방대하여 다 다를 수는 없지만 필자가 튜닝을 진행하는 동안 자주 참조하는 카달로그 뷰와 그에 해당하는 컬럼 위주로 설명한다.

<리스트 1> 많이 사용하는 카달로그 뷰와 해당 컬럼

분포도 정보를 이해하기 위해 간단한 예를 통해 확인해 보자. 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에 통계정보 갱신했음을 알 수 있다.