안녕하세요. DB2 및 XpertMon 사용자 여러분!
㈜ 아이티엑스퍼트그룹 DB 사업부입니다.

이번 주 부터 3주 동안 Websphere Information Integrator
( DB2II 나 V7 에서 Data Joiner로 알려져있는) 에 대해서 살펴 보도록 하겠습니다.

이번 주는 그 첫번째 시간으로 WS II의 기본 개념과 쿼리를 어떻게 처리하는가에
대해 살펴보도록 하겠습니다.

---

Understanding Websphere Information Integrator
V8.2 Performance

다양한 이기종 DBMS의 데이터를 DB2에서 접근 할 수 있도록 해주는 WS II가 V8.2에서
여러 가지 기능이 향상되었습니다.

여기서는 WS II의 기본 기능을 이해하고 federated query의 성능을 최적화 하는 방법에 대해
알아 본 후, V8.2 에서 추가된 새로운 기능들을 알아보고, 이상적으로 적용하는 방법을
생각하도록 하겠습니다.

WS II 의 기본 개념

다음 그림은 WS II를 통해 원격 데이터 소스에 접근하는 방법을 보여주고 있습니다.

여기서 Wrapper 는 DB2가 원격 데이터 소스에 접근할 수 있도록 하는 코드를 담고 있습니다.
Server는 Wrapper 를 통해 접근할 수 있는 개체를 나타냅니다. 그리고 Nickname은 원격 문서,
테이블, 뷰 등을 DB2에서 지칭하는 이름입니다. Wrapper 와 Wrapper 에 정의된 Server,
Server와 특정 Server에 정의된 Nickname 등의 관계는 카탈로그에 저장됩니다.

II 인스턴스는 그 외에는 기타 DB2 인스턴스와 같은 역할을 합니다.

Nickname은 응용프로그램이나 카탈로그에서 일반 테이블처럼 인식됩니다. Nickname이
포함된 쿼리를 실행하게 되면, db2agent 는 해당 소스에 대한 Wrapper 라이브러리를 로드하게
됩니다. 옵티마이저는 원격 데이터 소스에 대한 정보와 카탈로그 데이터를 바탕으로 DB2와
원격 데이터 소스에서의 실행 계획을 잡게 됩니다. 쿼리의 일부는 원격 데이터 소스에 맞는
형태로 전달됩니다.

원격 데이터 소스의 입장에서 II 는 dynamic SQL을 보내는 일반 클라이언트로 인식됩니다.


Pushdown은 성능면에서 가장 중요한 요소입니다. 일반적으로 Pushdown이 많이되면 될수록,
데이터를 이동시키는 대신, 데이터에 가까운 곳에서 작업이 수행되므로 성능은 향상된다고
볼 수 있습니다. WS II 는 Wrapper에 저장된 원격 데이터 소스의 정보를 바탕으로 pushdown
기능을 사용합니다.

Pushdown이 사용될 수 있는 지 여부는 다음의 요소에 의해 결정됩니다.

• 필요 기능에 대한 원격 데이터 소스에서의 지원여부
• 서버 옵션
  (예, 대조순서[collating sequence]가 Federated server와 원격 데이터 소스와
  동일한지 여부 등...)

비관계형 데이터 소스에 대해서는 실행시에 원격 데이터 소스가 받아 들일 수 있는 연산자를
확인하기 위한‘협상’ 과정을 거치게 됩니다.


WS II의 성능에 영향을 주는 요소는 다음과 같습니다.

• 처리능력, 네트워크 대역
• 원격 데이터 소스와 WS II 서버와의 트래픽
  • 통신횟수 (원격 데이터 소스로의 요청 횟수)
  • 원격 데이터 소스에서 WS II 서버로 전송되는 데이터 량
• 여러 데이터 소스에 위치한 데이터로 인한 트래픽
• 원격 데이터 소스로의 Pushdown 단계
• WS II와 원격 데이터 소스에서 수행될 실행 계획

여기서 한가지 유의할 사항은, 데이터는 항상 원격 데이터 소스에서 WS II 서버 쪽으로만
이동한다는 점입니다. 그 반대 방향으로는 이동하지 않습니다.

Federated 쿼리의 성능

단일 소스를 가진 Federated 쿼리의 성능에 대해 살펴보겠습니다. 우선 단일 원격 데이터
소스가 있고, WS II 서버에는 로컬 데이터가 없는 환경을 생각해 보겠습니다.

WS II를 통해 쿼리를 수행한 것과, 원격 데이터 소스의 native interface를 통해 쿼리를 수행한
것의 수행시간이 비슷한 경우, 쿼리의 성능이 우수하다고 할 수 있습니다.

다양한 쿼리를 수행한 결과, 많은 쿼리에 대해 수행시간이 같거나 비슷하게 나왔고, 일부
쿼리에 대해서는 WS II를 통한 것이 수행시간이 길었습니다. 특이한 점은 WS II를 통해 수행한
것이 오히려 더 빨리 완료되는 쿼리도 몇 개 있었습니다.

어떻게 해서 이런 결과가 나타나게 되었는지 알아보기 위해서는 Explain을 통해 다음의 사항을
확인 할 필요가 있습니다.

• 쿼리가 완전하게 Pushdown 되었는가?
• 처리과정에서 몇 건의 데이터가 원격 데이터 소스에서 WS II 쪽으로 이동하는가?
• 쿼리의 결과는 몇 건의 데이터가 나오는가?

여기서 잠깐 WS II 의 실행 계획을 확인하는 방법에 대해 잠시 살펴보겠습니다.

Visual Explain 이나 DB2EXFMT를 통해서 실행 계획을 확인 할 수 있습니다. DB2EXFMT를
사용하기 위해서는 EXPLAIN 테이블이 생성되어 있어야 합니다. EXPLAIN 테이블은
INST_HOME/sqllib/misc/EXPLAIN.DDL 파일을 실행하면 생성됩니다.

EXPLAIN 테이블이 준비되면, 명령창에서 다음 명령을 수행합니다.

db2 "explain for "
위 명령을 수행하면 EXPLAIN 테이블에 내용이 들어갑니다. 이제 explain 결과를 확인하기
위해 다음 명령을 수행합니다.

db2exfmt -d -1 -o

Federated 쿼리의 Explain도 다른 쿼리와 유사하지만, 한가지 'SHIP' 연산자가 있다는 것이
특징입니다.'SHIP' 연산자는 원격 데이터 소스와의 통신을 나타냅니다.


Explain 결과는 트리형태로 나타납니다. 각각의 연산자는 추정 행수(윗줄)와 cost(아랫줄)와
함께 표시됩니다. 맨 위의'RETURN' 연산자에 그 범례를 보여줍니다.

NLJOIN, HSJOIN, MGJOIN, SCAN, IXSCAN, FETCH, SORT, GRPBY, DISTINCT, UNION 등의
연산자는 다른 쿼리와 동일하지만,'SHIP' 연산자는 처리과정이 다른 데이터 소스로 이동한다는
것을 의미합니다. SELECT 문장에서'SHIP' 연산자 위쪽은 로컬에서 일어나는 과정입니다.

트리의 맨 아래에는 테이블, 인덱스, 그리고 Nickname('NK'로 표시)이 위치합니다.

다음 두 Explain 결과를 살펴보겠습니다.
이전에 SELECT 문장에 있어서, 'SHIP' 연산자 위쪽은 WS II 서버에서 일어나는 작업이라고
밝힌 바 있습니다. 이점을 유의하시고 다음 두 explain 결과를 살펴보시기 바랍니다.


WS II V8.2의 새로운 기능을 요약하면 다음과 같습니다.

• fenced wrapper
• local caching
• nickname 통계정보 관리
• informational constraints
• 파티션 환경 및 SMP 에서의 병렬처리

왼쪽은 두 테이블에 대한 join이 완전히 Pushdown 되어 1건만이 리턴되었습니다.

오른쪽은 join은 Pushdown 되었으나, 마지막 sort / group by 는 WS II 서버에서 수행
되었습니다. 이를 위해 302400 건이 원격 데이터 소스에서 WS II 서버로 이동되었습니다.

왼쪽처럼 완전히 Pushdown 되면서, 적은 건수를 리턴하는 쿼리의 경우는 native interface를
통한 것과 비슷한 성능을 내게 됩니다.

하지만 완전히 Pushdown 되면서, 적은 건수를 리턴하게 된다 하더라도 성능이 다를 수가 있는데
이는 원격 소스와의 SQL이 다르거나, WS II의 옵티마이저가 쿼리를 Rewrite 하기 때문입니다.

위 내용을 종합하면 다음과 같이 결론을 낼 수 있습니다.

단일 소스일 때,

• WS II가 native access 와 성능이 유사한 경우는
  • 쿼리가 완전히 Pushdown 되거나,
  • 원격 수행시간이 짧지 않고, 어느 정도 시간이 걸리거나,
  • 리턴되는 결과의 양이 그리 많지 않을 때 입니다.
• WS II 가 더 느린 경우는
  • 쿼리가 완전히 Pushdown 되지 않고, 대량의 중간결과가 WS II 서버로 이동하거나,
  • 쿼리가 매우 짧거나,
  • 리턴되는 결과가 클 때 입니다.
• WS II 가 더 빠른 경우도 있는데, 이는
  • WS II가 rewrite 한 쿼리가 완전히 Pushdown 되면서 native access 보다 효율적이거나,
  • 쿼리가 완전히 Pushdown 되지 않았지만, WS II가 훨씬 효율적인 실행 계획을 잡았을 경우 입니다.
  
  여러 소스에 대한 쿼리문장에 대해 살펴보겠습니다. 여러 소스에 대한 쿼리라 함은 다음의 두
  경우 중 하나를 말합니다.
  
  • WS II 서버에는 데이터가 없고, 둘 이상의 원격 데이터 소스의 데이터를 참조
  • WS II 서버와 하나 이상의 원격 데이터에 있는 데이터를 참조
  
  이런 경우에도 성능은 단일 원격 소스와 마찬가지로 데이터의 위치(데이터는 원격 데이터
  소스에서 WS II 쪽으로만 이동합니다.)와 이동되는 데이터의 양에 좌우됩니다.
  
  만약 join 해야 할 두 테이블이 서로 다른 데이터 소스에 위치하고 있다면, join 하기 전에 둘
  모두 WS II 서버로 옮겨와야 합니다. 이런 경우에도 좀 더 바람직한 접근법이 있다면, join을
  위해 데이터를 옮겨온다고 하더라도, 원격 데이터 소스에서 필터링을 통과한 데이터만
  옮겨온다면 성능이 더 나을 것입니다.
  
  그럼 이런 경우 성능에 대한 평가는 어떻게 할까요?
  
  단일 소스의 경우에는 WS II를 통해 수행한 것과, native access 를 통해 수행한 것의 성능을
  비교하는 것으로 평가가 가능했는데요, 여러 소스에 대한 성능 평가는 다소 비교에 무리가 있긴
  하지만, 테이블들이 서로 다른 데이터 소스에 분산된 환경에서의 join과 동일한 데이터 소스에
  함께 위치한 환경에서의 join을 비교하는 방법으로 수행할 수 있습니다.
  
  한가지 예를 들어 보겠습니다. 두 개의 테이블 - Customer, Order- 이 있습니다. 테이블
  이름에서 짐작할 수 있듯이, 각각의 주문정보는 Order 테이블에 들어가고 고객정보는
  Customer 테이블에 들어갑니다. 모든 주문은 Customer Key를 참조하고, 두 테이블에는
  Customer Key 칼럼에 대한 인덱스가 있습니다.
  
  여기서 가능한 join 방법 2가지에 대해 고려해 보겠습니다. 다음 검색 조건과 Explain 트리를
  살펴보시기 바랍니다.
  
  1. Customer Key가 지정된 14명의 고객에 대해, 각각의 고객에 대한 주문 건수를 검색하라.
  2. 총 주문 액수가 450만 이상인 국가의 고객 수를 검색하라.
  
  위 검색에 대한 Explain 트리는 다음과 같습니다.
  
  1번 쿼리에 대한 Explain 트리                                             2번 쿼리에 대한 Explain 트리
  
  왼쪽 1번 쿼리에 대한 내용이 나아 보입니다. 원격 소스 데이터로의 접근에 있어서, 많은
  데이터가 이동되지 않는 반면 오른쪽 쿼리는 대량의 데이터가 WS II 서버로 이동되기 때문에
  나빠 보입니다.
  
  실제로 이 두 쿼리를 두 가지 환경? 두 테이블이 동일한 데이터소스에 있을 경우와 다른
  데이터 소스에 있을 경우? 에서 수행한 결과를 살펴보면 다음과 같습니다.
  
  

  쿼리	두 테이블이 동일 데이터 소스에 있을 경우	두 테이블이 서로 다른 데이터 소스에 있을 경우
  1번 쿼리	18 ms	22 ms
  2번 쿼리	11 sec	106 sec

  
  
  1번 쿼리에 대해서는 두 경우의 차이가 거의 없지만, 2번 쿼리의 경우는 큰 차이를 보입니다.
  이는 2번 쿼리에서는 대량의 데이터가 WS II로 이동되어야 하기 때문입니다.
  
  논점에서 잠시 벗어나는 이야기지만, federation 환경으로 DW 를 구축하는 것은 일반적으로
  좋은 방법이 아님을 보여주는 예이기도 합니다.
  
  

  ---

  XpertMon for DB2 UDB V2
  DB 사업부
  Tel : 02-2108-1458
  Fax : 02-2108-1459
  Mobile : 011-896-6545
  E-mail : hjlee@iteg.co.kr
  URL : http://iteg.co.kr