使用db2look 重新创建优化器访问计划

简介
在作为 DB2 UDB 支持分析员（Support Analyst）工作时，我经常从处理优化器或查询计划问题的客户那里听到下列问题：
“我如何在接手生产环境的测试环境中重新创建相同的查询访问计划呢？”
我们常常需要将生产环境复制到测试环境中，包括为查询分析目的重新创建相同的访问计划。
例如，在生产中，您可能会遇到使用糟糕访问计划的查询所导致的性能问题，并且需要在测试系统上复制该访问计划以尝试一些不同的策略，例如操作统计数据，修改优化级别，对 DB2 注册表变量尝试不同的设置等等，以便提高性能。
在理想的世界中，您需要让测试环境尽可能接近地匹配生产。也就是说，您需要在两个环境中使用完全相同的硬件、操作系统维护级别和配置、DB2 级别和配置，以及在测试中使用与生产中相同的数据。然而，并非总是可以达到这种理想情况。如果生产环境具有极其大量的数据，您或许就没有容量来保存生产系统的测试副本。
db2look 实用程序可以用于达到该目标，即使您无法复制所有的生产细节。
本文将解释如何可以在测试系统上模拟生产系统，而无需真正的数据来重新创建查询计划问题。该功能将帮助您调试查询和理解访问计划问题，且不打断生产环境中的工作。但是请注意，如果需要测试结果访问计划的执行，则仍然需要将尽可能多的数据从生产环境装入测试环境。测试系统和生产系统之间的差别仍然总是可能足以导致测试上的执行特性不匹配生产上的。这部分的分析（性能调优）既是一门科学，又是一门艺术。
优化器或查询编译器领域中的其他问题，例如 SQL0901N 错误或实例崩溃，也可以使用本文中所解释的方法来重新创建。您可以尝试各种策略，如测试最新的补丁包（如果系统是处于更老的补丁级别），尝试不同的优化级别、不同的注册表变量等等，以便查看这些修改是否将解决问题。
让我们看一看 db2look 中用于达到该目标的选项。 

db2look 命令及其选项
下面是用于从生产系统捕获所需信息的命令：

清单 1. 重新创建优化器问题的命令

db2look -d <dbname> -l -o storage.out                        
db2look -d <dbname> -f -fd -o config.out
db2look -d <dbname> -e -a -m -o db2look.out 
db2look -d <dbname> -e -a -m -t table1 table2 .... tableX -o table.ddl
现在，让我们更详细地看一看这些 db2look 命令选项。
生成缓冲池、表空间和数据库分区组信息

db2look -d <dbname> -l -o storage.out

下面是对以上 db2look 命令中所用选项的描述：
-d：数据库名 —— 该选项必须指定。 
-l：生成数据库布局。这是用于数据库分区组、缓冲池和表空间的布局。 
-o：将输出重新定向到给定的文件名。如果未指定 -o 选项，然么输出将为标准输出（stdout），通常是输出到屏幕。
-l 选项对于模拟生产环境十分重要。理想情况下，您需要具有相同的缓冲池、数据库分区组（如果处于多分区环境中）和表空间信息（包括临时表空间）。但是，如果您受到了内存约束，无法分配生产中所具有的大型缓冲池，那么就使用 db2fopt 命令。我稍后将在本小节中更详细地讨论该命令。
并非总是可以在测试中设置与生产中相同的表空间。例如，可能设置了大型设备，却无法灵活地在测试中创建相同的设备大小。或者，可能根本无法在测试环境中获得单独的表空间设备。此外，或许无法在测试中设置与生产中相同的路径。需要适当地更改路径、设备和文件以适应测试环境。
下面是优化器为表空间所使用的重要信息。这就是您需要确保在测试和生产中相同的信息。（注意：这里所展示的数字是一个例子。您应在测试中使用与您生产中相同的设置。）

PREFETCHSIZE 16
EXTENTSIZE 16
OVERHEAD 12.670000
TRANSFERRATE 0.180000

如果生产中表空间是“由数据库管理的”，那么在测试中也应该是“由数据库管理的”。如果它在生产中是“由系统管理的”，那在测试中也应该是这样的方式。
注意：如果这是具有多个物理分区（MPP）的系统，那么测试中数据库分区组中的分区数目就必须相同。然而，物理机器的数目不必相同。测试和生产中整个 MPP 环境中逻辑分区的数目必须相同。
生成配置参数和注册表变量

db2look -d <dbname> -f -fd -o config.out

这里，我将使用下列参数：
-f：提取配置参数和注册表变量。如果指定了该选项，就会忽略 -wrapper 和 -server 选项。 
-fd：为 opt_buffpage 和 opt_sortheap 生成 db2fopt 语句，以及其他配置和注册表设置。
该命令的输出如下所示：

清单 2. db2look 命令的示例输出

$ db2look -d sample -f -fd

-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: SKAPOOR
-- This CLP file was created using DB2LOOK Version 8.2
-- Timestamp: Sat Mar 26 00:13:36 EST 2005
-- Database Name: SAMPLE
-- Database Manager Version: DB2/6000 Version 8.2.2
-- Database Codepage: 819
-- Database Collating Sequence is: UNIQUE

CONNECT TO SAMPLE; 

-------------------------------------------------------- 
-- Database and Database Manager configuration parameters 
-------------------------------------------------------- 

UPDATE DBM CFG USING cpuspeed 6.523521e-07; 
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 50000; 
!db2fopt SAMPLE update opt_sortheap 10000; 
UPDATE DB CFG FOR SAMPLE USING locklist 1000; 
UPDATE DB CFG FOR SAMPLE USING dft_degree 1;
UPDATE DB CFG FOR SAMPLE USING maxlocks 10; 
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_ANTIJOIN=yes; 
!db2set DB2_INLIST_TO_NLJN=yes; 

COMMIT WORK;
CONNECT RESET; 
TERMINATE; 

-f 和 -fd 选项是用于提取配置参数和注册表变量的关键选项，而优化器将在访问计划阶段使用这些配置参数和环境。在上面的 清单 2 中，请注意下列 -fd 选项所产生的输出：

!db2fopt SAMPLE update opt_buffpage 50000; 
!db2fopt SAMPLE update opt_sortheap 10000; 

db2fopt 命令告诉优化器为“缓冲池大小（Buffer pool size）”使用指定的值，而非将可用缓冲池变量的页面加起来。（db2exfmt 输出中的缓冲池大小将在下面的 缓冲池大小 一节中进行进一步的讨论。）例如，假设由于测试系统上的内存约束，您无法获得大型的缓冲池，并且希望将大小配置得相同，实际上却不是真正有这么大。使用将生成必要的 db2fopt 命令的 -fd 选项来告诉优化器使用指定大小，而非基于对该数据库可用的缓冲池进行计算。
[page_break]正如将在下面的 排序堆 一节中所看到的，它的工作方式与排序堆的相同。
如果您是一名 DBA，就可能会使用 DB2 SQL Explain Tool（db2exfmt）来获得对于 SQL 访问计划的理解。db2exfmt 工具用于格式化解释表的内容。如果您在生产中使用 db2exfmt 查看一个访问计划的输出，就会注意到计划顶部的下列内容。（注意：这些参数通常是由 db2look 输出中的 -f 和 -fd 选项所选择的，除了 dbheap 设置之外）。

清单 3. db2exfmt 的示例输出

Database Context:
---------------- 
        Parallelism:            None    
        CPU Speed:              6.523521e-07       
       Comm Speed:             100 
        Buffer Pool size:       50000 
        Sort Heap size:         10000 
        Database Heap size:     5120 
        Lock List size:         1000 
        Maximum Lock List:      10 
        Average Applications:   1 
        Locks Available:        7849 

Package Context: 
--------------- 
        SQL Type:               Dynamic 
        Optimization Level:     5 
        Blocking:               Block All Cursors 
        Isolation Level:        Cursor Stability 

---------------- STATEMENT 1  SECTION 201 ---------------- 
        QUERYNO:                1 
        QUERYTAG:               CLP 
        Statement Type:         Select 
        Updatable:              No 
        Deletable:              No 
        Query Degree:           1 

如果您稍稍深入查看 db2exfmt 的输出，就在访问计划之后，您将看到是否具有影响优化器计划的注册表设置。
注意：另外，遗憾的是，db2look -f 并非列出了所有相关的注册表变量。您将需要添加那些遗漏的。一般来说，您测试系统上的注册表变量设置应与生产系统上的相同，或者尽可能接近。

清单 4. 影响访问计划的注册表设置


1) RETURN: (Return Result) 
        Cumulative Total Cost:          57.6764 
        Cumulative CPU Cost:            191909 
        Cumulative I/O Cost:            2 
        Cumulative Re-Total Cost:       5.37264 
        Cumulative Re-CPU Cost:         134316 
        Cumulative Re-I/O Cost:         0 
        Cumulative First Row Cost:      26.9726 
        Estimated Buffer pool Buffers:   2 

        Arguments: 
        --------- 
        BLDLEVEL: (Build level) 
                DB2 v8.1.0.80 : s041221 
        ENVVAR  : (Environment Variable) 
      DB2_ANTIJOIN=yes 
                DB2_INLIST_TO_NLJN = yes 
        STMTHEAP: (Statement heap size) 
                2048 

创建数据定义语言（DDL）
下列 db2look 命令创建了 DDL 以复制所有数据库对象，以及配置和统计信息。

db2look -d <dbname> -e -a -m -o db2look.out

这里，我们使用了下列参数：
-a：为所有的创建器（creator）生成统计数据。如果指定了该选项，那么将忽略 -u 选项。 
-e：提取复制数据库所需的 DDL 文件。该选项生成包含了 DDL 语句的脚本。该脚本可以在另一数据库上运行以重新创建数据库对象。 
-m：以模拟模式运行 db2look 实用程序。该选项生成包含了 SQL UPDATE 语句的脚本。这些 SQL UPDATE 语句捕获所有的统计数据。该脚本可以在另一数据库上运行以复制原来的那一个数据库。当指定 -m 选项时，将忽略 -p、-g 和 -s 选项。
收集数据库子集的统计数据和 DDL
为了仅仅收集某些表和相关对象的统计数据和 ddl，可使用下列命令：

db2look -d <dbname> -e -a -m -t <table1> <table2> .. <tableX> -o table.ddl 

这里，我使用了下列附加参数：
-t：为特定的表生成统计数据。可以将表的最大数目指定为 30。
此外，如果您不使用 -a 选项，就可以使用 -z 选项：
-z：模式名。如果同时指定了 -z 和 -a，那么将忽略 -z。联邦区域将忽略模式名。

注意：-m 选项极其重要。该选项将从系统表收集所有统计数据。测试中的统计数据必须与生产中的相同，这些统计数据是可以在测试环境中模拟生产环境的关键。 

db2exfmt 输出的更多细节
数据库管理器级的配置参数
注意：使用命令 db2 "get dbm cfg" 查看这些参数，并使用 db2 "update dbm cfg using <parameter> <value>" 更新数据库管理器的配置参数。
并行性（Parallelism）：
该参数表明是启用分区间并行性（inter-partition parallelism），还是启用内部分区并行性（intra-partition parallelism）。如果这是具有多个分区的 DPF，那么您将看到 Inter Partition Parallelism。如果这只是 SMP（启用 intra_parallel）单个节点环境，那么您将看到 Intra Partition Parallelism。如果启用了 intra_parallel，并且是多个分区的环境，您将看到该参数为 Inter and Intra partitions parallelism。最后，如果没有分区间或分区内并行性，该参数将显示 NONE。
CPU 速度（cpuspeed）：
SQL 优化器使用 CPU 速度（每条指令几微秒）来评估某些操作的执行成本。
通信速度（comm_bandwidth）：
SQL 优化器使用为通信带宽所指定的值（每秒几兆字节）来评估在分区数据库系统中的分区服务器之间执行某些操作的成本。
数据库级的配置参数
注意：使用命令 db2 "get db cfg for <dbname>" 来查看这些参数，以及使用 db2 "update db cfg for <dbname> using <parameter> <value>") 来更新数据库配置参数。
缓冲池大小（buffer pool size）：
如果使用 buffpage 作为一个缓冲池的默认值，那么 db2exfmt 输出中显示的缓冲池大小就是由 buffpage 参数决定的，或者基于 syscat.bufferpools 的内容进行计算。所显示的数目就是分配给数据库的缓冲池页面的总数目。例如，假设我们具有下列缓冲池：

表 1. 缓冲池设置

缓冲池名称大小
IBMDEFAULTBP1000
BP11000
BP24000
BPIND11000
BPIND21000
BPLONG1000
BPTEMP1000
总数：10,000

db2exfmt 输出将显示所有缓冲池中的页面总数为总的大小。在上面的例子中，就是 10,000。 注意：页面大小（Pagesize）无关紧要，仅仅是页面的数目。
如果您无法在测试中分配到与生产中相同数量的缓冲池，那么可以在 db2look 中使用 -fd 选项来使用 db2fopt 备选命令。
在 MPP 中，优化器为运行查询的节点使用总的缓冲池信息时，要按每个节点来计算 opt_buffpage。因此，该修改将仅仅应用到运行该工具的那个节点上。
排序堆大小（SORTHEAP）
该参数定义用于私有排序的私有内存页面的最大数目，或用于共享排序的共享内存页面的最大数目。
您应将之设置为与生产中相同的值。同样，通过在 db2look 中使用 -fd 选项，您将注意到：

!db2fopt SAMPLE update opt_sortheap 256;

这将重写 sortheap 配置参数，优化器也将之用作 sortheap 值。同样，在运行时真正分配的排序堆（sortheap）实际上将由数据库配置中的 sortheap 设置来决定。与 opt_buffpage 相同，如果您无法在测试系统上分配与生产系统上相同大小的排序堆（sortheap），那么可以使用 opt_sortheap。
数据库堆大小（DBHEAP）：
每个数据库都有一个数据库堆，数据库管理器使用它来代表连接到数据库上的所有应用程序。 它包含表、索引、表空间和缓冲池的控制块信息。
锁列表大小（LOCKLIST）：
该参数表示分配给锁列表的存储器大小。
最大锁列表（MAXLOCKS）：
该参数定义数据库管理器执行升级之前必须填入的应用程序所占有锁列表的百分比。
locklist 和 maxlocks 将帮助确定某扫描（索引扫描或表扫描）期间将持有的锁类型，以及隔离级别。例如，您将在计划中注意到（比如说）索引扫描操作：

  IXSCAN: (Index Scan)

          TABLOCK : (Table Lock intent)
                 INTENT SHARE

注意：如果测试系统的 db2exfmt 输出中的可用锁（Locks Available）与生产系统不同，就不要进行连接 —— 该差异不影响查询计划。
平均应用程序（AVG_APPLS）：
SQL 优化器使用该参数来帮助评估在运行时有多少缓冲池可用于所选择的访问计划中（因为连接到数据库的所有活动应用程序共享缓冲池）。
优化级别（DFT_QUERYOPT）：
查询优化类用于在编译 SQL 查询时指导优化器使用不同的优化级别。
查询深度（DFT_DEGREE）：
用于 SQL 语句的分区内部并行程度。如果设置为 ANY，优化器就对联机的实际 CPU 数目敏感。如果您使用 ANY，那么就应该将测试和生产系统上的 CPU 数目配置得相同，除非禁用分区内并行（intra_parallel）。
除了以上修改之外，还必须确保其他一些参数都相同。
保留的高频值数目（NUM_FREQVALUES）：
该参数允许您指定“高频值（most frequent values）”的数目，当在 RUNSTATS 命令上指定 WITH DISTRIBUTION 选项时，将收集该值。
保留的分位数数目（NUM_QUANTILES）：
该参数控制在 RUNSTATS 命令上指定 WITH DISTRIBUTION 选项时将收集的分位数（quantile）数目。
测试系统上的上述两个参数 NUM_FREQVALUES 和 NUM_QUANTILES 必须与生产系统中的相同，以便确保在测试系统上收集与生产中相同数目的频值数目和分位数值。
SQL 语句堆（4KB）（STMTHEAP）：
在 SQL 语句的编译期间，语句堆（statement heap）用作 SQL 编译器的工作空间。该参数指定该工作空间的大小。如果测试中的该参数小于生产中的，您就可能会开始看到 SQL0101N 消息，因为缺乏编译查询所需要的语句堆空间。如果没有足够的语句堆用于动态连接枚举，您也可能看到 SQL0437W RC=1，下降为贪婪连接枚举。
[page_break]在测试系统上重新创建优化器/查询计划问题的示例
示例 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 
               

关键词：运用db2look 重新创建优化器访问计划