在測(cè)試系統(tǒng)上重新創(chuàng)建優(yōu)化器/查詢計(jì)劃問(wèn)題的示例：示例 1：OS：Windows 2000DB2LEVEL：V8.2 Fixpack 8 ESE 單分區(qū)測(cè)試并復(fù)制相同的 OS 和 db2level。數(shù)據(jù)庫(kù)：生產(chǎn)數(shù)據(jù)庫(kù)：SAMPLE測(cè)試數(shù)據(jù)庫(kù)：DUMMYDB使用下列命令創(chuàng)建 Sample 數(shù)據(jù)庫(kù)：db2sampl使用下列命令創(chuàng)建 Dummy 數(shù)據(jù)庫(kù)：db2 create db DUMMYDB注重：用與生產(chǎn)中相同的代碼頁(yè)、地區(qū)和排序序列創(chuàng)建 TEST 數(shù)據(jù)庫(kù)。生產(chǎn)環(huán)境：----------------------------------------- Database SAMPLE and Database Managerconfiguration 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;除了以上設(shè)置，還應(yīng)在數(shù)據(jù)庫(kù)配置中注重下列配置： 1234下一頁(yè) db2 get db cfg for sample > dbcfg_sample.outDatabase heap (4KB) (DBHEAP) = 600SQL statement heap (4KB) (STMTHEAP) = 2048Number of frequent values retained (NUM_FREQVALUES) = 10Number of quantiles retained (NUM_QUANTILES) = 20確保在修改數(shù)據(jù)庫(kù)治理器配置（dbm cfg）之后停止并啟動(dòng)該實(shí)例。對(duì)于 sample 數(shù)據(jù)庫(kù)，按下列方式對(duì) ORG 和 SALES 表運(yùn)行 runstats：db2 connect to sampledb2 runstats on table <schema>.org with distributionand indexes alldb2 runstats on table <schema>.sales with distributionand indexes alldb2 terminate現(xiàn)在，通過(guò)執(zhí)行 EXPLAIN.DDL 文件生成 EXPLAIN 表，該文件在 sqllibmisc 目錄下：db2 connect to sampledb2 -tvf <intall path>EXPLAIN.DDLdb2 terminate在名為 query.sql 的文件中保存下列命令：connect to sampleset current explain mode explainselect * from org a, staff b wherea.deptnumb=b.dept and b.dept=15set current explain mode noterminate現(xiàn)在，按下列方式執(zhí)行該文件：db2 -tvf query.sql上面將僅僅以解釋模式編譯查詢。您將在屏幕上看到：C:>db2 -tvf query.sqlconnect to sampleDatabase Connection InformationDatabase server = DB2/NT 8.2.1SQL authorization ID = SKAPOORLocal database alias = SAMPLEset current explain mode explainDB20000I The SQL command completedsuccessfully.select * from org a, staff b wherea.deptnumb=b.dept and b.dept=15SQL0217W The statement was not executedas only Explain information requestsare being processed. SQLSTATE=01604set current explain mode noDB20000I The SQL command completed successfully.C:>db2 terminateDB20000I The TERMINATE command completed successfully. 上一頁(yè)1234下一頁(yè) 使用 db2exfmt 生成訪問(wèn)計(jì)劃，如下：db2exfmt -d SAMPLE -g TIC -w -1-n % -s % -# 0-o prod_sample_exfmt.txt檢查 prod_sample_exfmt.txt 文件的內(nèi)容。您將看到生成了下面的訪問(wèn)計(jì)劃：Access Plan:-----------Total Cost: 25.8823Query Degree: 1RowsRETURN( 1)CostI/O|4HSJOIN( 2)25.88232/-----+-----4 1TBSCAN TBSCAN( 3) ( 4)12.9682 12.9131 1| |35 8TABLE: SKAPOOR TABLE: SKAPOORSTAFF ORG現(xiàn)在，這就是您要在生產(chǎn) sample 數(shù)據(jù)庫(kù)上繼續(xù)的計(jì)劃。您需要在測(cè)試環(huán)境中模擬該計(jì)劃。從生產(chǎn) sample 數(shù)據(jù)庫(kù)中收集下列信息：db2look -d SAMPLE -l -o storage.outdb2look -d SAMPLE -f -fd -o config.outdb2look -d SAMPLE -e -a -m -t ORG SALES -o table.ddl測(cè)試環(huán)境：修改上面將數(shù)據(jù)庫(kù)從 SAMPLE 連接到 DUMMYDB 時(shí)所收集的每個(gè)文件中的數(shù)據(jù)庫(kù)名。例如，假如您查看了 3 個(gè)文件的內(nèi)容，就會(huì)注重到：CONNECT TO SAMPLE;將它修改為：CONNECT TO DUMMYDB;在測(cè)試環(huán)境中接管這些文件。本例中，所有的表都是在默認(rèn)的表空間 USERSPACE1 中創(chuàng)建的。因此，它們也應(yīng)在測(cè)試系統(tǒng)上相同的 SMS 表空間中用 storage.out 中轉(zhuǎn)儲(chǔ)的相同配置（包括 PREFETCHSIZE、EXTENTSIZE 等）進(jìn)行創(chuàng)建。在 config.out 文件中進(jìn)行少量修改。將下列內(nèi)容：UPDATE DB CFG FOR SAMPLE USING dft_queryopt 5; 上一頁(yè)1234下一頁(yè) 修改為UPDATE DB CFG FOR SAMPLE USING dft_queryopt 3;并保存 config.out 文件。現(xiàn)在，執(zhí)行 storage.out、config.out 和 table.ddl，如下：db2 -tvf storage.out > storage_output.outdb2 -tvf config.out > config_output.outdb2 -tvf table.ddl > table.out檢查輸出文件以確保所有命令都成功運(yùn)行了。并且按照生產(chǎn)環(huán)境設(shè)置中所顯示的用于 SAMPLE DB 的設(shè)置來(lái)修改 DBHEAP、STMTHEAP、NUM_FREQVALUES、NUM_QUANTILES，使它們適用于 DUMMYDB。同時(shí)，檢查注冊(cè)表變量設(shè)置是否盡可能地相同。使用 db2stop 和 db2start 停止并啟動(dòng)該實(shí)例。重新為 DUMMYDB 數(shù)據(jù)庫(kù)創(chuàng)建解釋表：db2 connect to dummydb;<install path>sqllibmiscdb2 -tvf EXPLAIN.DDLdb2 terminate;現(xiàn)在，對(duì) DUMMYDB 數(shù)據(jù)庫(kù)運(yùn)行查詢，在前面對(duì) SAMPLE 數(shù)據(jù)庫(kù)運(yùn)行查詢時(shí)所生成的 query.sql 文件中將數(shù)據(jù)庫(kù)名從 SAMPLE 修改為 DUMMYDB。C:>db2 -tvf query.sqlconnect to dummydbDatabase Connection InformationDatabase server = DB2/NT 8.2.1SQL authorization ID = SKAPOORLocal database alias = DUMMYDBset current explain mode explainDB20000I The SQL command completedsuccessfully.select * from org a, staff b wherea.deptnumb=b.dept and b.dept=15SQL0217W The statement was not executed as onlyExplain information requestsare being processed. SQLSTATE=01604set current explain mode noDB20000I The SQL command completed successfully.C:>db2 terminateDB20000I The TERMINATE command completedsuccessfully 上一頁(yè)1234