¾È³çÇϼ¼¿ä. 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
|
|
|