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

提示：正如将在下面的 “排序堆 一节中所看到的，它的工作方式与排序堆的相同。 假如您是一名 DBA，就可能会使用 DB2 SQL Explain&正如将在下面的 “排序堆 一节中所看到的，它的工作方式与排序堆的相同。

假如您是一名 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:　

假如您稍稍深入查看 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：提取复制数据库所需的&n。

这里，我们使用了下列参数：

-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" 查看这些参数，并使用并行性（Parallelism）：db2 "update dbm cfg using <parameter> <value>"

更新数据库治理器的配置参数。

该参数表明是启用分区间并行性（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 " 来查看这些参数，以及使用 db2 "update db cfg for using ") 来更新数据库配置参数。

缓冲池大小（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，下降为贪婪连接枚举。