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

简介

在作为 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 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 选项来告诉优化器使用指定大小，而非基于对该数据库可用的缓冲池进行计算。