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

지난 주에는 WS II 의 기본 개념과 쿼리를 처리하는 방식에 대해 알아보았습니다.
이번 주에는 지난 주에 이어 WS II 에 있어 성능 향상을 위한 설정 팁에 대해 살펴보도록
하겠습니다.

---

설정 팁
제대로 된 설정과 원격 데이터 소스 및 원격 오브젝트에 대한 정확한 지식은 최적의 성능을
위한 가장 중요한 요소입니다. 이러한 정보는 다음과 같은 방법으로 설정 할 수 있습니다.

• 서버 옵션 및 칼럼 옵션
  WS II는 원격 데이터 소스가 할 수 있는 것과 할 수 없는 것에 대한 정보를 필요로
  하기 때문에 server option을 정확히 지정해 주는 것이 중요합니다.
  
• 데이터 유형 매핑
  원격 데이터 소스와 로컬 데이터 유형간의 매핑에 유의하십시오.
  
• 함수 매핑
  원격 데이터 소스에 위치한 함수를 WS II가 호출 할 수 있도록 매핑시켜 주어야 합니다.
  
• Nickname 통계정보
• 인덱스 지정
• Nickname에 대한 MQT

우선 서버 옵션에 대해 살펴보겠습니다.

• PUSHDOWN : 처리를 원격 데이터 소스로 넘길 것인지 설정합니다.
  (기본값은 ‘Y’ 입니다.)
  
• COLLATING_SEQUENCE : 원격과 로컬 문자열에 대한 정렬 순서가 같다면 ‘Y’로
  설정하십시오. (기본값은 ‘N’ 입니다.)
  
• DB2_MAXIMAL_PUSHDOWN : cost에 상관없이 PUSHDOWN을 강제합니다.
  안전을 위해 세션단위로 설정하시기 바랍니다. (기본값은 ‘N’ 입니다.)
  
• COMM_RATE, CPU_RATIO, IO_RATIO : 원격 CPU / IO 가 WS II 에 비해 빠른지
  느린지에 대한 link 속도에 대한 정보를 설정합니다.
  
• PLAN_HINTS : Oracle 데이터 소스에 대한 Hint구문을 WS II가 생성하도록 하는
  파라미터 입니다. (권장하지는 않습니다.)
  

칼럼 옵션은 다음과 같습니다.

• VARCHAR_NO_TRAILING_BLANKS : Oracle 데이터 소스의 VARCHAR 칼럼 데이터에
  공백문자로 끝나는 내용이 없다는 것을 설정합니다. 공백문자로 끝나는 내용이 없다면
  ‘Y’로 설정하십시오.
  
• NUMERIC_STRING : 문자열의 내용이 숫자로만 이루어져 있다면 ‘Y’로 설정하십시오.
  ‘Y’로 설정하면 COLLATING_SEQUENCE는 이 칼럼에 대해서는 문제가 되지 않습니다.
  

이상의 파라미터 중 몇가지에 대해 상세히 살펴보겠습니다.

COLLATING_SEQUENCE 는 WS II와 원격 데이터 소스의 문자열 정렬 순서가 같은지 여부를
지정합니다. ‘Y’로 지정하면, SORT가 PUSHDOWN 되거나, 문자 부등호 조건을 안전하게 사용
할 수 있습니다.

예를 들면,

SELECT COUNT(*) .... WHERE p_name > ‘F’;

같은 쿼리를 수행할 때 이 파라미터의 설정에 따라 Explain tree 가 다음과 같이 다르게
나타납니다.

              COLLATING_SEQUENCE=’N’                                           COLLATING_SEQUENCE=’Y’

COLLATING_SEQUENCE 의 기본값은 ‘N’ 입니다. 만약 테스트를 원한다면, WS II와
원격데이터 소스에 작은 테이블을 만들고 몇 건의 문자열 데이터를 넣습니다. 이때 대소문자,
공백으로 시작하는 문자열, 숫자 등 다양한 경우에 대해 비교가 가능하도록 넣습니다.

그런 후 order by를 이용해서 조회하여 정렬 순서가 같은지 확인 할 수 있습니다.
하지만 한번의 테스트로 결론을 내는 것은 다소 위험할 수 있으니, 충분한 테스트 후 설정을
해주시기 바랍니다.
확실한 결과를 얻고 COLLATING_SEQUENCE를 ‘Y’로 설정하게 되면 상당한 성능 개선효과를
기대할 수 있습니다.

DB2_MAXIMAL_PUSHDOWN 은 두 가지 모드로 설정할 수 있습니다.

한가지는 서버 옵션으로 설정하여 해당 서버로 가는 모든 쿼리에 적용되도록 하는 방법과,
세션단위로 설정하는 방법이 있습니다. 세션단위로 설정하는 것이 안전합니다.

이 파라미터를 ‘Y’로 설정하면 옵티마이저의 의지와는 관계없이 모든 SQL이 원격 데이터
소스로 PUSHDOWN 될 것입니다. 따라서, PUSHDOWN이 필요한 경우나, 문제 분석을
위한 경우에만 사용할 것을 권장합니다.

무분별한 PUSHDOWN의 사용은 오히려 성능 저하를 초래하기도 합니다.
예를 들면, 데이터 소스 A에서 테이블 A1과 A2, 그리고 데이터 소스 B에서 B1을 join 하는 데
있어, A1-B1을 먼저 하는 것이 빠를 수 있습니다.
이 때, DB2_MAXIMAL_PUSHDOWN을 ‘Y’로 설정하면 오히려 성능이 나빠지게 됩니다.

      A1-B1 join을 먼저 수행한 최적의 plan                             DB2_MAXIMAL_PUSHDOWN=’Y’로 인해 나빠진 plan

DB2_MAXIMAL_PUSHDOWN 은 PUSHDOWN이 기능상의 문제로 실패하는 경우, 문제 분석을
위해 사용할 수 있습니다. PUSHDOWN이 실패하는 경우, 이 파라미터를 ‘Y’로 설정한 후에도
PUSHDOWN이 실패한다면 이는 데이터 소스에서 특정 작업을 수행할 수 없거나, 같은 작업을
수행하는 함수가 데이터 소스에 없거나, 함수 매핑이 되지 않았다는 의미로 해석할 수 있습니다.

위에서 보여드린 부작용이 있을 수 있으므로, session 단위로 설정하여 사용하실 것을
권장합니다. (SET SERVER OPTION 명령을 사용하면 됩니다.)

VARCHAR_NO_TRAILING_BLANKS는 서버 옵션 및 칼럼 옵션으로 지정할 수 있습니다.

DB2 에서는 ‘HELLO’ 와 ‘HELLO□□□□’ 는 같은 것으로 인식합니다. 하지만 Oracle 에서는
‘HELLO’ 와 ‘HELLO□□□□’ 는 엄연히 다른 것으로 인식합니다.

따라서 VARCHAR칼럼이 공백문자로 끝나지 않는다는 것을 확인해 주지 않으면, WS II는
공백문자를 채워서 두 문자열을 비교하게 됩니다.

이 파라미터의 값으로 ‘Y’는 공백이 없다는 것을 의미하고, ‘N’는 공백문자가 있다는 것을
의미합니다. 이점 유의하시기 바랍니다.

기본값은 ‘N’으로 설정되어 있습니다. 이 값을 ‘Y’로 설정하면 성능에서 이익을 볼 수 있지만,
실제로 공백문자가 있음에도 불구하고 잘못 설정한 것이라면, 검색 결과가 달라지는 문제가
발생하게 되므로 주의하시기 바랍니다.

이 파라미터를 서버 옵션으로 설정하게 되면, 해당 서버에 있는 모든 테이블의 VARCHAR 칼럼에
공백으로 끝나는 문자열이 없다는 의미이므로 이 점 역시 주의하시기 바랍니다.

VARCHAR_NO_TRAILING_BLANKS 를 사용한 예를 살펴 보겠습니다.

SELECT COUNT(*) FROM ora1.part WHERE p_name = ‘cornflower’;

우선, Explain tree 는 설정에 관계없이 위처럼 나옵니다.

여기서, VARCHAR_NO_TRAILING_BLANKS=’Y’로
설정했다면 PUSHDOWN 되는 쿼리는 다음과 같습니다.

SELECT COUNT(*) FROM “ORA”. “PART” A0
     WHERE (A0. “P_NAME”=’conflower’)

이때 해당 칼럼이 공백문자로 끝난다면, 아무런 내용도 리턴되지 않습니다.
잘못된 결과가 나오는 것이죠.

만약 VARCHAR_NO_TRAILING_BLANKS=’N’로 설정되어있다면, PUSHDOWN 되는 쿼리는
다음과 같습니다.

SELECT COUNT(*) FROM “ORA”.”PART” A0
     WHERE (RPAD(‘cornflower’,55,’ ‘)= RPAD(A0.”P_NAME”,55,’ ‘))

공백 문자를 메꿔넣음으로써 정확한 결과를 보장하지만, P_NAME에 인덱스가 달려있다 해도
무시되므로 속도는 느려집니다.

원격 칼럼과 로컬 컬럼간의 타입 매핑을 통해 성능에 영향을 줄 수 있습니다.
예를 들면, Oracle 의 DATE 타입은 기본적으로 DB2의 TIMESTAMP 타입으로 매핑됩니다.

따라서,... WHERE >= DATE(‘2005-12-20’)은 동작하지
않습니다. WHERE CAST( as DATE ) = DATE(‘2005-12-20’)의 경우는
동작은 하지만 CAST 의 PUSHDOWN으로 인해 성능은 느려집니다.

일반적으로 기본 타입 매핑을 사용하는것이 편리 하지만, 빠른 성능을 위해서는 적절한 형태로
매핑을 해주는 것이 유리합니다.
설정하는 방법은 다음과 같습니다.

ALTER NICKNAME ALTER COLUMN LOCAL TYPE DATE;

위와 같이 설정하면, Oracle 의 DATE 타입 칼럼이 로컬의 DATE 타입으로 매핑됩니다.
내부적으로는, 사용자가 Oracle DATE 타입의 timestamp 부분이 0이라고 알려주는 것이고,
DB2는 그대로 믿습니다. 이렇게 설정한 뒤, CAST 없이 비교를 해도 성공적으로 수행이 됩니다.

칼럼 매핑은 원격 데이터 소스와 WS II와 join이 필요할 경우 특히 중요합니다. 데이터 타입을
맞춰주게 되면 hash join 등의 사용으로 인해 성능개선이 가능합니다.

다른 예를 한가지 들어보면, Oracle의 NUMBER 타입은 NUMBER(10)으로 인식되어 32비트의
INTEGER 보다 큽니다. 따라서 기본적으로 Oracle의 NUMBER는 DECIMAL(10)으로 매핑됩니다.
하지만 만약 Oracle의 데이터가 232-1 보다 작은 값이라면, 이 칼럼을 INT로 매핑하여 로컬의
INT 칼럼과 join이 가능하게 설정할 수도 있습니다.

함수 매핑은 원격 데이터 소스에 있는 함수와 로컬 함수가 같은 함수라는 것을 WS II에게
알려주는 것입니다. 따라서 매개변수와 타입이 호환 가능해야 합니다.

여러 함수들이 이미 매핑 되어 있지만, 새로운 버전부터 지원되는 일부 함수의 경우는 사용자가
직접 매핑해 주어야 하는 것도 있습니다. 함수 매핑은 서버단위로 설정 할 수도 있고, Wrapper
단위에서 전체 서버에 대해 설정 할 수도 있습니다.

예를 들어, 원격 데이터 소스에 있는 함수 function_remote(arg1, arg2)가 있고, 여기 대응하는
로컬 함수 function_local(x, y) 가 있다고 가정합니다.

다음과 같이 함수 매핑을 설정합니다.

CREATE FUNCTION MAPPING my_mapping FOR function_local(char, int) SERVER
my_server OPTIONS ( REMOTE_NAME ‘function_remote’);

위와 같이 설정 한 후, SELECT function_local(x, y) from 을 수행하면,
이 쿼리는 완전히 PUSHDOWN 됩니다.

함수 호출이 PUSHDOWN 되는지 확인하기 위해서 이전에 언급한 DB2_MAXIMAL_PUSHDOWN 을
‘Y’로 설정하여 테스트 할 수 있습니다.
만약 PUSHDOWN되지 않는다면, 이는 원격 데이터 소스에 대응되는 함수가 없거나,
있더라도 함수 매핑이 설정되지 않았다는 것을 의미합니다.
또는 같은 이름의 함수가 이미 존재하는데, 이름만 같을 뿐 내용이 서로 다른 경우도 원인이 됩니다.

WS II 인스턴스와 데이터베이스 설정에 대해 잠시 살펴보겠습니다.

• 튜닝을 할 경우에는 우선 WSII 인스턴스에 있는 로컬데이터에 대한 튜닝을 먼저 합니다.
• Nickname 은 테이블 스페이스에 할당되지 않기 때문에, 자신의 버퍼풀이 필요하지 않습니다.
• Federated Query 를 사용함에 있어 추가로 필요한 자원 중 가장 중요한 것은 CPU와 메모리 입니다
• 완전히 PUSHDOWN 되지 않는 Federated Query를 처리하기 위해서는 SORTHEAP과
  임시 테이블 스페이스(버퍼풀)를 필요로 합니다.

DB2는 Nickname 통계정보를 카탈로그에 저장하여 관리합니다.
이 통계정보는 Nickname생성시에 가져오게 됩니다. 따라서 Nickname을 생성하기에 앞서
원격 데이터 소스에서 통계정보를 수집해 줄 것을 권장합니다. 최초에 Nickname의 통계정보를
가져왔다고 하더라도 이후에 원격 데이터 소스에서 갱신된 통계정보는 적용이 되지 않습니다.

만약, EXPLAIN 결과가 다음과 같이 나온다면, Nickname의 통계정보가 비어있는 것으로 볼 수
있습니다. Nickname의 행수는 기본적으로 1,000 건으로 보기 때문입니다.


통계정보는 데이터 소스에 따라 수집하는 정보의 단위가 틀립니다.
원격 뷰, Alias 등의 통계정보는 수집되지 않습니다.

만약, EXPLAIN 결과가 잘못된 것처럼 보인다면, Nickname의 통계정보를 확인해 볼 필요가
있습니다. WS II는 모든 원격 데이터 소스에 대해 같은 수준의 통계정보를 수집하지는 못합니다.

몇 가지 통계정보의 공식은 다음과 같습니다.

• CARD = 테이블의 건수
• COLCARD = 테이블의 칼럼에 대한 고유한 값(distinct value)의 건수
• High2key/Low2key = 해당 칼럼의 두 번째 큰 값/작은 값
• FIRSTKEYCARD = 인덱스의 맨 첫 번째 칼럼에 대해 고유한 값(distinct value)
• FULLKEYCARD = 인덱스의 모든 칼럼에 대해 고유한 값(distinct value)

이러한 통계정보는 각기 다음 뷰에 저장됩니다.

뷰	칼럼
SYSSTAT.COLUMNS	COLCARD, HIGH2KEY, LOW2KEY
SYSSTAT.TABLES	CARD
SYSSTAT.INDEXES	FIRSTKEYCARD, FULLKEYCARD

만약, Nickname 의 통계정보가 없고, NNSTAT()으로 수집되지 않는 경우, 원격 데이터 소스의
내용으로부터 통계정보를 수동으로 추출할 수 도 있습니다.

우선 다음과 같은 쿼리를 통해 Nickname의 통계정보가 있는 지 확인합니다.

SLECT CHAR(COLNAME,20) AS COLNAME,
      COLCARD, CHAR(HIGH2KEY,33) AS HIGH,
      CHAR(LOW2KEY,33) AS LOW
     FROM SYSSTAT.COLUMNS
   WHERE TABSCHEMA=’’
     AND TABNAME=’’;

해당 내용을 수정하고자 하는 경우에는 다음과 같이 SQL문을 사용해서 갱신합니다.

UPDATE SYSSTAT.COLUMNS SET
    COLCARD=’’,
    HIGH2KEY=’’,
    LOW2KEY=’’
  WHERE COLNAME=’’
    AND TABNAME=’’
    AND TABSCHEMA=’’;

CREATE NICKNAME 문으로 Nickname을 생성할 때 원격에 있는 해당 테이블의 통계정보를
WS II 서버에 등록합니다.
그러면 이 이후로 이 통계정보를 갱신하기 위해서는 어떻게 해야 할까요?

첫 번째 방법은 Nickname을 drop 한 뒤, 새로 생성하는 방법이 있습니다.
하지만 별로 좋은 방법은 아닙니다. 이런 경우 해당 Nickname 을 참조하는 패키지가 invalid
하게 되고, 권한문제도 발생하게 됩니다.

두 번째 방법은 WS II V8.2 부터 새롭게 소개된 NNSTAT() 이라는 스토어드 프로시저를
사용하는 방법입니다. 제어센터에서 Update Statistics 를 사용하는 방법도 있습니다만,
이것 역시 NNSTAT()를 내부적으로 호출하는 것입니다.

하지만 이런 경우에도 제약사항은 있습니다. 원격 데이터 소스에 수집되어 있는 통계정보를
가져오는 것일 뿐, 새로이 통계정보를 수집하는 것은 아닙니다. DB2의 runstat 명령은
Nickname에는 적용되지 않습니다. 원격 데이터 소스에 있는 오브젝트에 대한 통계정보를
수집하기 위해서는 getstats 라는 툴을 사용하면 됩니다.

getstats는 원격 데이터 소스의 오브젝트에 대한 통계정보를 수집하는 툴입니다. 자원 사용이
많고 오버헤드가 있으므로 꼭 필요시에만 사용하는 것이 좋습니다. NNSTAT() 스토어드
프로시저 호출시 원격에 해당 오브젝트에 대한 통계정보가 없을 경우 자동으로 getstats 툴이 호출됩니다.

그럼 여기서 NNSTAT() 스토어드 프로시저가 어떻게 동작하는지 살펴보겠습니다.

Nickname에 대해 NNSTAT() 스토어드 프로시저를 호출하면, 동일한 원격 오브젝트에 대해
더미 Nickname을 생성합니다. 더미 Nickname을 생성하는 단계에서 (CREATE NICKNAME)
원격 오브젝트에 대한 통계정보를 가져옵니다. 더미 통계정보(새롭게 가져온 통계정보)를
기존 Nickname의 통계정보에 넣고, 더미는 삭제됩니다. 만약 원격에 통계정보가 없었다면,
getstats 툴을 사용하여 통계정보를 수집하여 가져옵니다. 단, NNSTAT()을 사용해도
Nickname이 생성된 이후 만들어진 인덱스에 대한 정보를 가져오지 못합니다.

Nickname 에 대한 로컬 인덱스는 존재하지 않습니다. 다만 원격 데이터 소스에 존재하는
인덱스의 정보를 카탈로그에 저장할 뿐입니다. 일반적으로 원격 인덱스에 대한 정보는
Nickname 생성시에 가져옵니다. Nickname 생성 이후에 원격 오브젝트에 추가된 인덱스의
정보나, view, synonyms, 비관계형 오브젝트에 대한 인덱스 정보는 갖고 있지 못합니다.

이런 경우, 원격 오브젝트에 대한 인덱스 정보를 WS II에게 알려줄 수 있는 방법이 있습니다.

이를 인덱스 지정(index specification)이라고 합니다. 방법은 CREATE INDEX 문과 유사합니다.

인덱스 지정은 원격 오브젝트의 어떤 칼럼에 인덱스가 있다는 내용을 알려줄 뿐, 해당 인덱스의
통계정보를 가져오지는 못합니다. 그에 반해 NNSTAT() 스토어드 프로시저는 인덱스 지정등으로
알려진 인덱스의 통계정보를 가져옵니다.

성능 개선을 위한 또 하나의 방법은 MQT를 사용하는 것입니다. join을 통해 로컬 및 Nickname에
대한 쿼리의 결과를 로컬 테이블로 유지하는 이 방법은 자주 사용되는 쿼리의 성능을 상당부분
개선할 수 있게 합니다.
유지 방법은 기본적으로 DEFERRED REFRESH 를 사용한 ‘시스템 유지’ 와 복제를 통해
동기화를 유지하는 ‘사용자 유지’ 방법이 있습니다.
V8.2의 제어센터의 ‘Cache Table Wizard’를 사용하는 방법도 있습니다.

원격 데이터에 대한 MQT를 사용하여 성능상 개선효과를 기대할 수 있는 경우를 살펴보겠습니다.

• 로컬에 fact 테이블이 있고, 원격에 dimension 테이블이 있을 경우,
  dimension 테이블의 내용 일부를 로컬에 캐시 하여 원격 데이터에 대한 접근을 줄일 수
  있습니다.
  
• 원격 fact 테이블과 하나 이상의 원격 dimension 테이블의 prejoin을 캐시하여
  fact 테이블의 내용 중 관심 있는 부분을 로컬에 보관 합니다.
  
• 원격 fact 테이블과 하나 이상의 dimension 테이블의 합을 로컬에 저장하여 많은
  쿼리가 원격 데이터에 접근하지 않고 로컬에서 처리될 수 있도록 합니다.
  

---

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