使用DB2look 重新创建优化器访问计划(3)

在测试系统上重新创建优化器/查询计划问题的示例
示例 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

  1<schema>.org with distribution and indexes all   
  2db2 runstats on table <schema>.sales with distribution and indexes all   
  3db2 terminate   
  4  
  5现在，通过执行 EXPLAIN.DDL 文件生成 EXPLAIN 表，该文件在 <install directory="">\sqllib\misc 目录下：   
  6  
  7db2 connect to sample   
  8db2 -tvf <intall path="">\EXPLAIN.DDL   
  9db2 terminate   
 10  
 11在名为 query.sql 的文件中保存下列命令：   
 12  
 13connect to sample   
 14set current explain mode explain   
 15select * from org a, staff b where a.deptnumb=b.dept and b.dept=15   
 16set current explain mode no   
 17terminate   
 18  
 19现在，按下列方式执行该文件：   
 20  
 21db2 -tvf query.sql   
 22  
 23上面将仅仅以解释模式编译查询。您将在屏幕上看到：   
 24  
 25C:\&gt;db2 -tvf query.sql   
 26connect to sample   
 27  
 28Database Connection Information   
 29  
 30Database server = DB2/NT 8.2.1   
 31SQL authorization ID = SKAPOOR   
 32Local database alias = SAMPLE   
 33  
 34set current explain mode explain   
 35DB20000I The SQL command completed successfully.   
 36  
 37select * from org a, staff b where a.deptnumb=b.dept and b.dept=15   
 38SQL0217W The statement was not executed as only Explain information requests   
 39are being processed. SQLSTATE=01604   
 40  
 41set current explain mode no   
 42DB20000I The SQL command completed successfully.   
 43  
 44C:\&gt;db2 terminate   
 45DB20000I The TERMINATE command completed successfully.   
 46  
 47使用 db2exfmt 生成访问计划，如下：   
 48  
 49db2exfmt -d SAMPLE -g TIC -w -1 -n % -s % -# 0 -o prod_sample_exfmt.txt   
 50  
 51检查 prod_sample_exfmt.txt 文件的内容。您将看到生成了下面的访问计划：   
 52  
 53Access Plan:   
 54\-----------   
 55Total Cost: 25.8823   
 56Query Degree: 1   
 57  
 58Rows   
 59RETURN   
 60( 1)   
 61Cost   
 62I/O   
 63|   
 644   
 65HSJOIN   
 66( 2)   
 6725.8823   
 682   
 69/-----+-----\   
 704 1   
 71TBSCAN TBSCAN   
 72( 3) ( 4)   
 7312.9682 12.913   
 741 1   
 75| |   
 7635 8   
 77TABLE: SKAPOOR TABLE: SKAPOOR   
 78STAFF ORG   
 79  
 80现在，这就是您要在生产 sample 数据库上继续的计划。您需要在测试环境中模拟该计划。   
 81从生产 sample 数据库中收集下列信息：   
 82  
 83db2look -d SAMPLE -l -o storage.out   
 84db2look -d SAMPLE -f -fd -o config.out   
 85db2look -d SAMPLE -e -a -m -t ORG SALES -o table.ddl   
 86  
 87测试环境：   
 88修改上面将数据库从 SAMPLE 连接到 DUMMYDB 时所收集的每个文件中的数据库名。   
 89例如，如果您查看了 3 个文件的内容，就会注意到：   
 90CONNECT TO SAMPLE;   
 91  
 92将它修改为：   
 93CONNECT TO DUMMYDB;   
 94  
 95在测试环境中接管这些文件。本例中，所有的表都是在默认的表空间 USERSPACE1 中创建的。因此，它们也应在测试系统上相同的 SMS 表空间中用 storage.out 中转储的相同配置（包括 PREFETCHSIZE、EXTENTSIZE 等）进行创建。   
 96在 config.out 文件中进行少量修改。将下列内容：   
 97UPDATE DB CFG FOR SAMPLE USING dft_queryopt 5;   
 98  
 99修改为   
100UPDATE DB CFG FOR SAMPLE USING dft_queryopt 3;   
101  
102并保存 config.out 文件。   
103现在，执行 storage.out、config.out 和 table.ddl，如下：   
104  
105db2 -tvf storage.out &gt; storage_output.out   
106db2 -tvf config.out &gt; config_output.out   
107db2 -tvf table.ddl &gt; table.out   
108  
109检查输出文件以确保所有命令都成功运行了。并且按照生产环境设置中所显示的用于 SAMPLE DB 的设置来修改 DBHEAP、STMTHEAP、NUM_FREQVALUES、NUM_QUANTILES，使它们适用于 DUMMYDB。同时，检查注册表变量设置是否尽可能地相同。   
110使用 db2stop 和 db2start 停止并启动该实例。重新为 DUMMYDB 数据库创建解释表：   
111  
112db2 connect to dummydb;   
113<install path="">\sqllib\misc\db2 -tvf EXPLAIN.DDL   
114db2 terminate;   
115  
116现在，对 DUMMYDB 数据库运行查询，在前面对 SAMPLE 数据库运行查询时所生成的 query.sql 文件中将数据库名从 SAMPLE 修改为 DUMMYDB。   
117  
118C:\&gt;db2 -tvf query.sql   
119connect to dummydb   
120  
121Database Connection Information   
122  
123Database server = DB2/NT 8.2.1   
124SQL authorization ID = SKAPOOR   
125Local database alias = DUMMYDB   
126  
127set current explain mode explain   
128DB20000I The SQL command completed successfully.   
129  
130select * from org a, staff b where a.deptnumb=b.dept and b.dept=15   
131SQL0217W The statement was not executed as only Explain information requests   
132are being processed. SQLSTATE=01604   
133  
134set current explain mode no   
135DB20000I The SQL command completed successfully.   
136  
137C:\&gt;db2 terminate   
138DB20000I The TERMINATE command completed successfully.</install></intall></install></schema></schema>