在測試系統上重新創建優化器/查詢計劃問題的示例
示例 1:
OS:Windows 2000
DB2LEVEL:V8.2 Fixpack 8 ESE 單分區
測試並復制相同的 OS 和 db2level。
數據庫:
生產數據庫:SAMPLE
測試數據庫:DUMMYDB
使用下列命令創建 Sample 數據庫:db2sampl
使用下列命令創建 Dummy 數據庫:
db2 create db DUMMYDB
注意:用與生產中相同的代碼頁、地區和排序序列創建 TEST 數據庫。
生產環境:
--------------------------------------------------------
-- 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;
除了以上設置,還應在數據庫配置中注意下列配置:
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 數據庫,按下列方式對 ORG 和 SALES 表運行 runstats:
db2 connect to sample
db2 runstats on table <schema>.org with distribution and indexes all
db2 runstats on table <schema>.sales with distribution and indexes all
db2 terminate
現在,通過執行 EXPLAIN.DDL 文件生成 EXPLAIN 表,該文件在 <install directory>\sqllib\misc 目錄下:
db2 connect to sample
db2 -tvf <intall path>\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
現在,按下列方式執行該文件:
db2 -tvf query.sql
上面將僅僅以解釋模式編譯查詢。您將在屏幕上看到:
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 生成訪問計劃,如下:
db2exfmt -d SAMPLE -g TIC -w -1 -n % -s % -# 0 -o prod_sample_exfmt.txt
檢查 prod_sample_exfmt.txt 文件的內容。您將看到生成了下面的訪問計劃:
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
現在,這就是您要在生產 sample 數據庫上繼續的計劃。您需要在測試環境中模擬該計劃。
從生產 sample 數據庫中收集下列信息:
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
測試環境:
修改上面將數據庫從 SAMPLE 連接到 DUMMYDB 時所收集的每個文件中的數據庫名。
例如,如果您查看了 3 個文件的內容,就會注意到:
CONNECT TO SAMPLE;
將它修改為:
CONNECT TO DUMMYDB;
在測試環境中接管這些文件。本例中,所有的表都是在默認的表空間 USERSPACE1 中創建的。因此,它們也應在測試系統上相同的 SMS 表空間中用 storage.out 中轉儲的相同配置(包括 PREFETCHSIZE、EXTENTSIZE 等)進行創建。
在 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
檢查輸出文件以確保所有命令都成功運行了。並且按照生產環境設置中所顯示的用於 SAMPLE DB 的設置來修改 DBHEAP、STMTHEAP、NUM_FREQVALUES、NUM_QUANTILES,使它們適用於 DUMMYDB。同時,檢查注冊表變量設置是否盡可能地相同。
使用 db2stop 和 db2start 停止並啟動該實例。重新為 DUMMYDB 數據庫創建解釋表:
db2 connect to dummydb;
<install path>\sqllib\misc\db2 -tvf EXPLAIN.DDL
db2 terminate;
現在,對 DUMMYDB 數據庫運行查詢,在前面對 SAMPLE 數據庫運行查詢時所生成的 query.sql 文件中將數據庫名從 SAMPLE 修改為 DUMMYDB。
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.