안녕하세요. DB2 및 XpertMon 사용자 여러분!
㈜ 아이티엑스퍼트그룹 DB 사업부입니다.
이번에는 DB2LOOK을 사용한 optimizer access plan 재생성의 세 번째인 optimizer plan을
재생성하는 예제에 대해 살펴보도록 하겠습니다.
테스트 시스템에서의 optimizer/query plan을 재생성하는 예제
Example 1:
OS: Windows 2000
DB2LEVEL: V8.2 Fixpack 8 ESE Single partition
TEST와 PRODUCTION의 OS와 db2level이 같음.
Databases:
Production Database: SAMPLE
Test Database: DUMMYDB
Sample database 생성방법: db2sampl
Dummy database 생성방법: db2 create db DUMMYDB
PRODUCTION 환경:
|
--------------------------------------------------------
Database SAMPLE and Database Manager configuration parameters
--------------------------------------------------------
UPDATE DBM CFG USING cpuspeed 9.446886e-007;
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 250;
!db2fopt SAMPLE update opt_sortheap 256;
UPDATE DB CFG FOR SAMPLE USING locklist 50;
UPDATE DB CFG FOR SAMPLE USING dft_degree 1;
UPDATE DB CFG FOR SAMPLE USING maxlocks 22;
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_INLIST_TO_NLJN=yes;
!db2set DB2_HASH_JOIN=yes;
|
추가로, 아래의 구성이 database configuration에 기록됩니다
|
db2 get db cfg for sample > dbcfg_sample.out
Database heap (4KB) (DBHEAP) = 600
SQL statement heap (4KB) (STMTHEAP) = 2048
Number of frequent values retained (NUM_FREQVALUES) = 10
Number of quantiles retained (NUM_QUANTILES) = 20 |
dbm cfg를 변경 후에는 꼭 인스턴스를 재 시작해야 합니다.
그리고 sample database에서, ORG와 SALES table에 runstats를 실행합니다.
|
db2 connect to sample
db2 runstats on table .org with distribution and indexes all
db2 runstats on table .sales with distribution and indexes all
db2 terminate |
이제, sqllibmisc 디렉토리의 EXPLAIN.DDL파일을 실행함으로써
EXPLAIN table을 가져옵니다.
|
db2 connect to sample
db2 -tvf EXPLAIN.DDL
db2 terminate |
query.sql 파일을 열어 다음 명령어를 저장합니다.
|
connect to sample
set current explain mode explain
select * from org a, staff b where a.deptnumb=b.dept and b.dept=15
set current explain mode no
terminate |
이제 다음 파일을 실행합니다.
아래와 같이 explain mode에서 쿼리를 컴파일하게 되어 경고가 발생합니다.
|
C:>db2 -tvf query.sql
connect to sample
Database Connection Information
Database server = DB2/NT 8.2.1
SQL authorization ID = SKAPOOR
Local database alias = SAMPLE
set current explain mode explain
DB20000I The SQL command completed successfully.
select * from org a, staff b where a.deptnumb=b.dept and b.dept=15
SQL0217W The statement was not executed as only Explain information requests are being processed. SQLSTATE=01604
set current explain mode no
DB20000I The SQL command completed successfully.
C:>db2 terminate
DB20000I The TERMINATE command completed successfully. |
다음과 같이 db2exfmt를 사용하여 access plan을 가져옵니다.
|
db2exfmt -d SAMPLE -g TIC -w -1 -n % -s % -# 0 -o rod_sample_exfmt.txt |
그리고 prod_sample_exfmt.txt 파일의 내용을 보면 다음의access plan 이 나타납니다.
Access Plan:
-----------
Total Cost: 25.8823
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
4
HSJOIN
( 2)
25.8823
2
/-----+-----
4 1
TBSCAN TBSCAN
( 3) ( 4)
12.9682 12.913
1 1
| |
35 8
TABLE: SKAPOOR TABLE: SKAPOOR
STAFF ORG |
여기까지, production sample database에 대한 plan입니다.
test 환경에서도 이 과정과 같습니다.
그럼 production sample database에서 다음 정보를 수집합니다.
|
db2look -d SAMPLE -l -o storage.out
db2look -d SAMPLE -f -fd -o config.out
db2look -d SAMPLE -e -a -m -t ORG SALES -o table.ddl |
TEST ENVIRONMENT:
위에 수집된 각각의 파일들에서 DB명을 sample에서 dummydb로 바꿉니다.
예를 들어 3개 파일의 내용을 보면,
이것을 아래와 같이 변경합니다:
test 환경에서 파일들을 인계합니다. 예를 들면, 모든 table들은 default tablespace인
USERSPACE1에 생성됩니다. 그래서 PREFETCHSIZE, EXTENTSIZE 등을 포함하여 똑같은
구성으로 test 시스템에서 같은 SMS tablespace로 생성할 것입니다.
그리고 config.out파일에서 값 하나를 변경합니다.
|
UPDATE DB CFG FOR SAMPLE USING dft_queryopt 5; |
이것을 아래와 같이 변경합니다.
|
UPDATE DB CFG FOR SAMPLE USING dft_queryopt 3; |
그리고, config.out 파일을 저장합니다
이제 storage.out, config.out과 table.ddl 을 실행합니다
|
db2 -tvf storage.out > storage_output.out
db2 -tvf config.out > config_output.out
db2 -tvf table.ddl > table.out |
모든 command가 성공적으로 수행되면, production 환경에 setting된 SAMPLE DB처럼
DUMMYDB의 DBHEAP, STMTHEAP, NUM_FREQVALUES, NUM_QUANTILES이 변경됩니다.
또한, 가능한 동일하게 환경 변수를 체크합니다. 그리고 db2stop, db2start를 사용하여
instance를 재 시작하고 DUMMYDB database의 explain table을 생성합니다.
|
db2 connect to dummydb;
sqllibmiscdb2 -tvf EXPLAIN.DDL
db2 terminate; |
이제, query.sql 파일에서 DB명을 SAMPLE에서 DUMMYDB로 수정하고 DUMMYDB database에
쿼리를 실행합니다.
|
C:>db2 -tvf query.sql
connect to dummydb
Database Connection Information
Database server = DB2/NT 8.2.1
SQL authorization ID = SKAPOOR
Local database alias = DUMMYDB
set current explain mode explain
DB20000I The SQL command completed successfully.
select * from org a, staff b where a.deptnumb=b.dept and b.dept=15
SQL0217W The statement was not executed as only Explain information requests
are being processed. SQLSTATE=01604
set current explain mode no
DB20000I The SQL command completed successfully.
C:>db2 terminate
DB20000I The TERMINATE command completed successfully. |
db2exfmt output:
|
db2exfmt -d DUMMYDB -g TIC -w -1 -n % -s % -# 0 -o test_dummydb_exfmt.txt |
test_dummydb_exfmt.txt의 내용과 access plan을 보면:
Access Plan:
-----------
Total Cost: 25.8843
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
4
MSJOIN
( 2)
25.8843
2
/-----+-----
1 4
TBSCAN TBSCAN
( 3) ( 5)
12.913 12.9682
1 1
| |
8 35
TABLE: SKAPOOR TABLE: SKAPOOR
ORG STAFF |
Test는 Production과 다른 access plan을 가지는 것을 볼 수 있습니다.
이 경우에 명백하게, test system에서 DFT_QUERYOPT (default query optimization)가
5에서 3으로 변경됨으로써 Hash Join이 아닌 Merge Join을 선택한 것을 볼 수 있습니다.
그리고 total cost의 작은 차이를 볼 수 있습니다.
지금 보여진 것처럼, TEST와 PRODUCTION에서, 유일한 차이점은 Optimization Level을
의도적으로 5에서 3으로 변경한 것입니다.
이 경우에, 아래의 UPDATE문을 사용하여 DFT_QUERYOPT를 update합니다.
|
UPDATE DB CFG FOR SAMPLE USING dft_queryopt 5 |
그리고 나서 DB를 terminate하고 restart하십시오. DUMMYDB에 query.sql을 다시 실행하고
db2exfmt 명령어로 access plan을 추출하십시오. 이번에는 같은 access plan을 보여줍니다.
즉, 이 과정에서 소개된 모든 optimizer-related parameters는 동일합니다.
Example 2:
이 예는 db2look 명령어의 m 옵션의 중요성을 보여줍니다.
이 통계는 미리 언급한 것처럼 m 옵션으로 수집하고 test와 production 둘 다에서 똑같이
사용할 것입니다.
예를 들면, 통계가 완전하게 갱신되지 않을 때 plan이 어떻게 변화되는지 보여줍니다.
DBM/DB 구성, db2set 환경변수는 위의 Example 1과 같습니다.
여기서 스키마명은 SKAPOOR입니다. DB는 Example 1처럼 SAMPLE과 DUMMY이고 platform과
db2level은 AIX 5.1에 DB2 UDB ESE V8.2, Fix pack 8, 하나의 partition입니다.
그럼 sample DB에 다음의 명령어를 수행해 보겠습니다:
|
db2 "connect to sample"
db2 "create index name_ind on staff (name,id)"
db2 "runstats on table skapoor.staff with distribution and indexes all"
db2 "set current explain mode explain"
db2 "select name from staff where id=10 order by name"
db2 "set current explain mode no"
db2 "terminate" |
db2exfmt을 사용하여 access plan을 추출합니다. 다음의 access plan 보면:
Access Plan:
-----------
Total Cost: 0.111065
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
1
IXSCAN
( 2)
0.111065
0
|
35
INDEX: SKAPOOR
NAME_IND |
sample DB로부터 db2look 정보를 수집합니다.
|
db2look -d sample -l -o storage.out
db2look -d sample -e -a -m -t STAFF -o db2look.out
db2look -d sample -f -fd -o config.out
|
위에 Example 1에 했던 것처럼 sample 대신에 dummy database에 연결하여 이 파일들을
수정합니다. 수동으로 이 통계 중 하나를 수정하면 db2look.out에서의 다음 문장을 찾습니다.
(schema명, TABSCHEMA와 INDSCHEMA가 여기서는 다릅니다):
|
UPDATE SYSSTAT.INDEXES
SET NLEAF=1,
NLEVELS=1,
FIRSTKEYCARD=35,
FIRST2KEYCARD=35,
FIRST3KEYCARD=-1,
FIRST4KEYCARD=-1,
FULLKEYCARD=35,
CLUSTERFACTOR=-1.000000,
CLUSTERRATIO=100,
SEQUENTIAL_PAGES=0,
DENSITY=0,
AVERAGE_SEQUENCE_GAP=0.000000,
AVERAGE_SEQUENCE_FETCH_GAP=0.000000,
AVERAGE_SEQUENCE_PAGES=0.000000,
AVERAGE_SEQUENCE_FETCH_PAGES=0.000000,
AVERAGE_RANDOM_PAGES=1.000000,
AVERAGE_RANDOM_FETCH_PAGES=0.000000,
NUMRIDS=35,
NUMRIDS_DELETED=0,
NUM_EMPTY_LEAFS=0
WHERE INDNAME = 'NAME_IND' AND INDSCHEMA = 'SKAPOOR '
AND TABNAME = 'STAFF' AND TABSCHEMA = 'SKAPOOR '; |
이제 FIRSTKEYCARD, FIRST2KEYCARD, FULLKEYCARD AND NUMRIDS를 35에서 37로 변경합니다.
그리고 db2look.out 파일을 저장하고 3개의 파일을 실행합니다.
|
db2 -tvf config.out > config_output.out
db2 -tvf storage.out > storage_output.out
db2 terminate
db2stop
db2start
db2 -tvf db2look.out > db2look_output.out |
처음 두 개 파일(config_output.out과 storage_output.out) 의 내용을 시험해 보고,
이제 examine db2look_output.out 파일의 내용을 시험해 봅니다.
다음의 실패하는 update문장을 볼 수 있습니다
|
UPDATE SYSSTAT.INDEXES SET NLEAF=1, NLEVELS=1, FIRSTKEYCARD=37, FIRST2KEYCARD=37
, FIRST3KEYCARD=-1, FIRST4KEYCARD=-1, FULLKEYCARD=37, CLUSTERFACTOR=-1.000000, C
LUSTERRATIO=100, SEQUENTIAL_PAGES=0, DENSITY=0, AVERAGE_SEQUENCE_GAP=0.000000, A
VERAGE_SEQUENCE_FETCH_GAP=0.000000, AVERAGE_SEQUENCE_PAGES=0.000000, AVERAGE_SEQ
UENCE_FETCH_PAGES=0.000000, AVERAGE_RANDOM_PAGES=1.000000, AVERAGE_RANDOM_FETCH_
PAGES=0.000000, NUMRIDS=37, NUMRIDS_DELETED=0, NUM_EMPTY_LEAFS=0 WHERE INDNAME =
'NAME_IND' AND INDSCHEMA = 'SKAPOOR ' AND TABNAME = 'STAFF' AND TABSCHEMA = 'SK
APOOR '
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1227N The catalog statistic "37" for column "FULLKEYCARD" is out of range
for its target column, has an invalid format, or is inconsistent in relation
to some other statistic. Reason Code = "8". SQLSTATE=23521 |
위에서 본 것처럼, index, NAME_IND UPDATE문장은 FULLKEYCARD가 테이블의 cardinality
(CARD)보다 크기 때문에 실패했습니다. 다음 update 문장에서 본 것처럼 db2look.out
파일에서 CARD값은 35입니다.
|
UPDATE SYSSTAT.TABLES
SET CARD=35,
NPAGES=1,
FPAGES=1,
OVERFLOW=0,
ACTIVE_BLOCKS=0
WHERE TABNAME = 'STAFF' AND TABSCHEMA = 'SKAPOOR '; |
이제 다시 같은 쿼리를 실행합니다.
|
db2 "select name from staff where id=10 order by name" |
explain mode와 access plan 추출은 보시다시피 다릅니다:
Access Plan:
-----------
Total Cost: 12.972
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
1
TBSCAN
( 2)
12.972
1
|
1
SORT
( 3)
12.9708
1
|
1
TBSCAN
( 4)
12.9682
1
|
35
TABLE: SKAPOOR
STAFF |
이 예는 만약 테이블에 WRITE가 발생하는 동안 RUNSTATS가 실행다면 이 통계는 이 예에서
부적합할 수도 있습니다. 즉, 통계를 update하는 UPDATE 문장은 SQL1227N 에러를 발생할
수 있습니다. 모든 UPDATE 문장이 성공적으로 실행되는 것은 매우 중요합니다.
그리고 만약 일치하지 않는다면, 수정하고 다시 실행합니다. 위의 경우에 해결책은
KEYCARDS와 NUMRIDS를 37에서 35로 바꾸는 것이었습니다.
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
|
|
|
