?????. 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
|
|
|
