안녕하세요. DB2 및 XpertMon 사용자 여러분!
㈜ 아이티엑스퍼트그룹 DB 사업부입니다.
이번에는 3회에 걸쳐서 DB2LOOK을 사용한 optimizer access plan 재생성에 대해서
살펴보도록 하겠습니다.
크게 세 가지로 나누어서 다룰 예정인데, 첫째는 db2look의 명령어와 옵션에 대해서이고,
둘째는 db2exfmt에 대한 detail한 정보, 마지막은 전반적인 예제가 될 것입니다.
이번에는 db2look의 명령어와 옵션 -f와 -fd, 그리고 db2fopt에 대해 살펴보도록 하겠습니다.

---

db2look을 사용한 optimizer access plan 재생성 I
소개
DB2 UDB 지원 분석에서 보면, Optimizer 또는 Query planning문제를 다루는 질문을
자주 하게 됩니다.
"어떻게 나의 테스트 환경에서 운영환경과 같은 Query access plan을 재생성 하는 지에 대해서."
이 문제를 해결하기 위해서 매번 운영환경을 테스트환경으로 복제해야 합니다
예를 들면, 좋지 않은 access plan으로 인해 Query 성능문제를 경험하게 되고 다른 환경에서
시도하기 위해 테스트 시스템에서 access plan을 복제하기를 원합니다.
또한, 성능향상을 위해서 통계를 조작하는 것처럼 optimizer level을 변경하고,
DB2 환경변수 값을 변경합니다.
가장 이상적인 방법은, 가능한 운영환경과 같은 테스트환경을 구축하는 것입니다.
즉, 두 환경에서 정확하게 같은 하드웨어, OS LEVEL과 구성, DB2 LEVEL과 구성을 사용하기를
원합니다.
그러나, 이 이상적인 환경은 거의 가능하지 않습니다. 즉, 운영환경이 데이터가 많은
환경이라면 테스트환경 용량을 운영환경처럼 가지지 못할 것입니다.
비록 운영환경의 모든 detail한 부분까지 복제할 수 없을지라도 DB2LOOK 유틸리티는
이런 목적을 해결할 수 있습니다.

자 이제 이 목표를 이루기 위하여 DB2LOOK의 옵션을 보겠습니다.

db2look 명령어와 옵션
다음은 운영환경에서 필요한 정보를 추출하는 명령어입니다.
Listing 1. optimizer 문제를 recreate하는 명령어

db2look -d -l -o storage.out db2look -d -f -fd -o config.out db2look -d -e -a -m -o db2look.out db2look -d -e -a -m -t table1 table2 .... tableX -o table.ddl

이제 좀 더 자세한 DB2LOOK 명령어 옵션을 봅니다.

버퍼풀, 테이블스페이스, database파티션 그룹 정보 추출

db2look -d -l -o storage.out

위의 DB2LOOK 명령어에서 사용된 옵션에 대한 설명은 다음과 같습니다.

• -d : database명 (필수)
• -l : database layout을 추출
• -o : 주어진 파일명에 output을 redirect

운영환경을 복제하기 위해서 -l 옵션은 중요합니다. 가장 이상적인 것은, 같은 버퍼풀,
DB Partition group(multi-partition환경), 테이블스페이스(temp tablespace포함)
정보를 가지는 것입니다. 그러나, 만약 메모리가 부족하거나, 운영환경에 가지고 있는
large 버퍼풀이 테스트환경에 할당될 수 없다면 DB2FOPT 명령어를 사용합니다.
이 명령어에 대해서는 이 section에서 나중에 더 자세히 다룰 예정입니다.
또, 테스트 환경이 항상 같은 테이블스페이스를 가지는 것이 가능한 것은 아닙니다.
예를 들면 큰 사이즈로 구성된 device를 가질 수도 있고, 테스트환경에서 같은 device
사이즈를 생성하기 위한 유연성을 가지지 못할 수도 있습니다.
또한, 운영환경에서처럼 테스트환경에서 동일한 Path를 가지지 못할 수도 있습니다.
그래서 테스트환경에 적합하게 맞는 Path, Device, File등을 추가할 필요가 있습니다. 중요한 정보는 optimizer에 의해 테이블스페이스가 아래의 옵션을 사용합니다.
이것은 확실히 테스트 환경과 운영환경 둘 다 같아야 합니다.
(사이즈는 환경마다 다를 수 있고 단지 예입니다)

PREFETCHSIZE 16 EXTENTSIZE 16 OVERHEAD 12.670000 TRANSFERRATE 0.180000

만약 테이블스페이스가 운영환경에서 “managed by database” 라면 테스트환경 또한
같아야 합니다. Note: 만약 시스템이 MPP라면, DB partition group에서의 partition 수는 테스트환경에서도
같아야 합니다. 그러나 물리적인 시스템 수는 같지 않아도 됩니다.
다만, 전체 MPP환경에서의 논리적인 partition수는 두 환경에서 같아야 합니다.

구성 파라미터와 환경변수 추출

db2look -d -f -fd -o config.out

다음의 파라미터를 사용합니다.
-f : 구성 파라미터와 환경 변수를 추출, 이 옵션이 지정되면 -wrapper와 server옵션은 무시됩니다.
-fd : opt_buffpage와 opt_sortheap을 위한 db2fopt문 추출, 다른 구성 파라미터와
환경변수도 포함되어 있음
Listing 2. db2look 을 사용한 Sample output

$ db2look -d sample -f -fd -- No userid was specified, db2look tries to use Environment variable USER -- USER is: SKAPOOR -- This CLP file was created using DB2LOOK Version 8.2 -- Timestamp: Sat Mar 26 00:13:36 EST 2005 -- Database Name: SAMPLE -- Database Manager Version: DB2/6000 Version 8.2.2 -- Database Codepage: 819 -- Database Collating Sequence is: UNIQUE CONNECT TO SAMPLE; -------------------------------------------------------- -- Database and Database Manager configuration parameters -------------------------------------------------------- UPDATE DBM CFG USING cpuspeed 6.523521e-07; UPDATE DBM CFG USING intra_parallel NO; UPDATE DBM CFG USING federated NO; UPDATE DBM CFG USING fed_noauth NO; !db2fopt SAMPLE update opt_buffpage 50000; !db2fopt SAMPLE update opt_sortheap 10000; UPDATE DB CFG FOR SAMPLE USING locklist 1000; UPDATE DB CFG FOR SAMPLE USING dft_degree 1; UPDATE DB CFG FOR SAMPLE USING maxlocks 10; UPDATE DB CFG FOR SAMPLE USING avg_appls 1; UPDATE DB CFG FOR SAMPLE USING stmtheap 2048; UPDATE DB CFG FOR SAMPLE USING dft_queryopt 5; --------------------------------- -- Environment Variables settings --------------------------------- !db2set DB2_ANTIJOIN=yes; !db2set DB2_INLIST_TO_NLJN=yes; COMMIT WORK; CONNECT RESET; TERMINATE;

-f와 -fd 옵션은 구성 파라미터와 환경 변수를 추출하기 위해 사용하는 key 옵션입니다.
Optimizer는 access plan을 사용합니다. Listing 2에서 아래의 output은 -fd 옵션으로부터 나타납니다.

!db2fopt SAMPLE update opt_buffpage 50000; !db2fopt SAMPLE update opt_sortheap 10000;

DB2fopt 명령어는 버퍼풀이 사용 가능한 페이지들을 추가하는 것보다 빠른
(db2exfmt에서의 버퍼풀 사이즈는 아래 버퍼풀 사이즈 색션에서 더 다룰 것 입니다)
버퍼풀 사이즈의 지정된 값을 사용하도록 optimizer에게 알려 줍니다.
예를 들면, 테스트 시스템에서 메모리 제약으로 큰 버퍼풀을 사용하지 못할 때, 같은
사이즈로 구성하기를 원할 때, -fd옵션을 사용하면 데이터베이스를 위한 버퍼풀 사용에
기반을 두어 계산하는 것보다 빠른 지정된 사이즈를 사용하여 optimizer에게 알려주는
db2fopt 명령문을 추출합니다.
이것은 sort heap에 대해서도 같은 방법으로 동작합니다.
아래는 db2fopt 명령문에 대한 사용 예입니다.
Note) db2look의 -fd옵션은 값을 가져오지만 사용법을 알아야 하고 데이터베이스 구성에서의
sortheap과 버퍼풀을 위해 syscat.bufferpools를 사용하고 다시 back하려는 값을 어떻게
reset하는지 알아야 합니다.

C:>db2fopt Usage: db2fopt update [opt_buffpage ] [opt_sortheap f]   or db2fopt get [opt_buffpage] [opt_sortheap]]

만약 opt_buffpage와 opt_sortheap값 설정을 원한다면 아래와 같이 하십시오.

db2fopt update opt_buffpage opt_sortheap

예를 들면:

C:>db2fopt sample update opt_buffpage 50000 opt_sortheap 10000 Update succeeded

그 다음엔 꼭 terminate를 하고 connection을 다시 합니다.
만약에 값을 보여주길 원한다면 아래와 같이 하십시오.

C:>db2fopt sample get opt_buffpage opt_sortheap

만약 값을 재설정 하기를 원한다면 두 개의 파라미터를 사용하지 마십시오.
그리고 버퍼풀 페이지 추정을 위해 syscat.bufferpools로 back하십시오.
그리고 SortHeap 사이즈를 위한 DB를 구성하십시오.

C:>db2fopt sample update opt_buffpage -1 opt_sortheap -1 Update succeeded

그리고 반드시 DB연결은 terminate, reconnect 하셔야 합니다.
그리고 나서 다시 db2fopt의 get옵션을 사용해서 보면 아래와 같이 됩니다.
C:>db2fopt sample get opt_buffpage opt_sortheap
opt_buffpage value is -1
opt_sortheap value is -1
DBA는, SQL access plan을 보기 위해 DB2 SQL Explain Tool(db2exfmt)을 사용합니다.
db2exfmt Tool은 explain 테이블의 내용을 포맷합니다. 만약 db2exfmt를 사용한
access plan중 하나의 output을 보면 plan의 맨 윗부분에 다음과 같이 나타납니다.
(Note: 대부분의 경우를 보면, 이들 파라미터는 DBHEAP을 제외하고 db2look의 -f,
-fd옵션에서도 볼 수 있습니다)
Listing 3. db2exfmt로부터의 sample output

Database Context: ---------------- Parallelism: None CPU Speed: 6.523521e-07 Comm Speed: 100 Buffer Pool size: 50000 Sort Heap size: 10000 Database Heap size: 5120 Lock List size: 1000 Maximum Lock List: 10 Average Applications: 1 Locks Available: 7849 Package Context: --------------- SQL Type: Dynamic Optimization Level: 5 Blocking: Block All Cursors Isolation Level: Cursor Stability ---------------- STATEMENT 1 SECTION 201 ---------------- QUERYNO: 1 QUERYTAG: CLP Statement Type: Select Updatable: No Deletable: No Query Degree: 1

db2exfmt output의 아래쪽에, access plan후의 오른쪽에서, Optimizer plan에 영향을
받는 어떤 레지스트리 설정 되었는지 볼 수 있습니다.
Note: 물론 db2look -f에 의해 모든 적절한 레지스트리 변수가 리스트 되는 것은
아니므로 Missing된 것들을 추가할 필요가 있습니다. 일반적으로 테스트 시스템에 적용된
레지스트리 변수는 동일하고, 일반적으로 close되어 있을 것입니다.
Listing 4. access plan에 의해 영향 받는 레지스트리 설정

1) RETURN: (Return Result) Cumulative Total Cost: 57.6764 Cumulative CPU Cost: 191909 Cumulative I/O Cost: 2 Cumulative Re-Total Cost: 5.37264 Cumulative Re-CPU Cost: 134316 Cumulative Re-I/O Cost: 0 Cumulative First Row Cost: 26.9726 Estimated Buffer pool Buffers: 2 Arguments: --------- BLDLEVEL: (Build level) DB2 v8.1.0.80 : s041221 ENVVAR : (Environment Variable) DB2_ANTIJOIN=yes DB2_INLIST_TO_NLJN = yes STMTHEAP: (Statement heap size) 2048

Data definition language (DDL) 생성
다음의 db2look 명령어는 구성과 통계정보를 포함하여 모든 DB 오브젝트를 복제하기
위한 DDL을 생성합니다.

db2look -d -e -a -m -o db2look.out

아래의 파라미터를 사용합니다.

• -a : 모든 creator의 통계를 발생시킵니다. 만약 옵션이 지정된다면 -u 옵션은 무시됩니다.
• -e : DB를 복제하기 위해 필요한 DDL 파일을 추출합니다. 이 옵션은 DDL문을 포함한 스크립트를 추출합니다. 이 스크립트는 DB 오브젝트를 다시 생성하기 위해 다른 DB에서 실행할 수 있습니다.
• -m : 가상 모드에서 db2look 유틸리티를 실행합니다. 이 옵션은 SQL update문을 포함한 스크립트를 추출합니다. 이 SQL UPDATE문은 모든 통계를 캡쳐합니다. 이 스크립트는 기존 DB를 복제하기 위해 다른 DB에서도 실행될 수 있습니다. -m 옵션이 지정되면, -p, -g, -s 옵션은 무시됩니다.

Database subset을 위한 통계와 DDL 수집
어떤 테이블과 관계된 오브젝트의 통계와 DDL 수집을 위해 다음의 명령어를 사용합니다.

db2look -d -e -a -m -t .. -o table.ddl

여기서, 다음의 추가된 파라미터를 사용합니다.

• -t : 지정된 테이블을 위한 통계를 추출합니다. 테이블의 최대수는 30개 지정할 수 있습니다.
• -z : 스키마명. 만약 -z와 -a가 둘 다 지정되면, -z는 무시됩니다.

Note: -m 옵션은 중요합니다. 이 옵션은 system 테이블로부터 모든 통계를 수집합니다.
이 통계는 운영환경에서처럼 테스트환경에서도 같아야 하고 테스트환경에서 운영환경처럼
사용할 수 있는 키입니다.

---

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