内容

DB2 UDB 性能调优场景，第 1 部分

用 Java 程序演示 DB2 UDB OLTP 调优

模拟 OLTP 工作负载调优 DB2 配置参数

Xiaomei Wang, Wini Mark, Ken Lau, 和 Raul Chong
2005 年 8 月 11 日发布
Comments

系列内容：

此内容是该系列 # 部分中的第 # 部分： DB2 UDB 性能调优场景，第 1 部分

http://www.ibm.com/developerworks/cn/library/?series_title_by=**auto**
敬请期待该系列的后续内容。

此内容是该系列的一部分：DB2 UDB 性能调优场景，第 1 部分

敬请期待该系列的后续内容。

简介

性能是您使用数据库系统最重要的问题之一。有 3 个主要因素影响系统性能：CPU 使用、I/O 利用和内存使用，如图 1 所示：

图 1. 影响性能的因素
影响性能的因素

取决于您的系统资源，在进行调优决策时，CPU、内存和 I/O 之间总是存在某些权衡。您需要平衡这三个因素以调节获得最佳性能。我们将讨论一些达到该目标的方法。

数据库工作负载的理解对于有效配置数据库以获得最优性能也很关键。有三种类型的数据库工作负载：

  • 联机事务处理（Online Transaction Processing，OLTP），包含许多不同复杂性的小型事务。OLTP 事务包括 select、insert、update 和 delete，通常是以秒级或次秒级（sub-second）的速度完成的。
  • 决策支持系统（Decision Support System，DSS），通常是仅为 select 的事务，包含访问大量数据的大型查询。
  • 以上两种类型的混合。

无论数据库负载如何，通用的调优提示都是相同的，只是 OLTP 和 DSS 工作负载之间存在差别。本文仅仅说明 OLTP 环境中的监控和调优提示。

许多因素都可以影响数据库服务器的性能，例如硬件系统的设计、数据库对象的设计、数据存储器的管理、应用程序的设计等等。本文将关注如何调优 DB2 UDB 配置参数，以及用于捕获和修复“糟糕查询”的步骤。

本文中的示例是特定于 Microsoft Windows® 平台上运行的 DB2 UDB Enterprise Server Edition Version 8.2 的。但是，所提供的概念和信息在任何平台上都适用。

“PERFORMER”示例 Java 程序和“BANK”数据库

本文中，我们提供了性能示例，这些示例使用名为 BANK 的数据库以及一个名为 PERFORMER 的简单的 Java 独立程序。创建和填充 BANK 数据库以及运行 PERFORMER 所需的文件和指示可以在本文的 下载 小节中找到。您最多花费 5 分钟就可以准备好该环境。

出于简单性，BANK 数据库只包含了两个表，account 和 auditlog。PERFORMER 程序是一个 JDBC™ 应用程序，一旦调用该程序，它就使用 Type 2 Universal 驱动程序并建立到 BANK 的 10 次连接。其接口极其简单。只要指定测试的持续时间并单击 Run。当程序执行时，将在 10 次连接的每一次中用随机参数反复执行相同的事务。当您调优数据库时，Number of completed transactions 字段应随着测试的进行逐渐增加。这就是您的主要目标。

PERFORMER 所发出的事务包含下列查询：

  • SELECT NAME, BALANCE FROM ACCOUNT WHERE ACCT_ID = <value>
  • UPDATE ACCOUNT SET BALANCE = ? WHERE ACCT_ID = ?
  • INSERT INTO AUDITLOG VALUES (?, ?, ?, ?, ?)

我们鼓励您边浏览本文边操作 PERFORMER（在测试环境中！）。

建立基线

在调优数据库之前，您通常应建立基线（baseline）。对于这个特定的场景，让我们跟踪内存的消耗，以及未调优数据库时所完成的事务数目（number of completed transactions）。首先从一个干净的环境开始，执行下列操作：

  1. 关闭所有打开的窗口。
  2. 打开 DB2 命令窗口，并将之移至桌面中间的底部。然后发出该命令：db2stop force
  3. 打开 Windows 任务管理器（Task Manager），并将之放置在桌面右上角。选择 Performance 选项卡。
  4. 在 DB2 命令窗口中，发出：db2start。同时，查看任务管理器中的内存使用是多少。启动一个实例要消耗一些内存，但是不多。
  5. 在 DB2 命令窗口中，发出：db2 list applications。您应收到一条消息表明 Database System Monitor 未返回任何数据。
  6. 通过执行文件 performer.bat 启动 PERFORMER 程序，并查看任务管理器中的内存是如何增加的。正如前面所提到的，PERFORMER 一旦被调用，就建立 10 次连接。当对数据库建立第一次连接时，就分配数据库内存。此外，每次连接都与一个 DB2 代理相关，该代理也导致一些内存成本。将 PERFORMER 窗口移至左上角。
  7. 在 DB2 命令窗口中，发出：db2 list applications。现在，您将看到 java.exe 应用程序（PERFORMER）到 BANK 数据库的 10 次连接。

您的桌面将如图 2 所示。在阅读本文时请保持该窗口布局。

图 2. PERFORMER 程序和桌面布局
PERFORMER 程序和桌面布局

点击查看大图

PERFORMER 程序和桌面布局

为了建立基线，要运行 PERFORMER 10 秒（5 到 8 次），并写下结果。您将注意到在没有任何调优时，所完成事务的数目将随着一次运行到另一次提高，但最终会达到一个极限。这是正常的。在第一次运行期间，不会将太多信息缓存到内存中；因此，第一次运行付出的 I/O 代价就是将一些页面放入内存中。在调优实践中，短语“启动数据库（priming the database）”指的是分配所有内存并将一些数据页面调入内存的第一次运行。这些运行通常不包含在最后性能结果编号中。

此时，我们已经获得了未调优数据库时所完成的事务基线。您现在应获取完整的 DB2 快照；然而，我们不会在本文中讨论该主题。因此，这是您在阅读完本文之后需要进行尝试的，从头开始重复该实践。

DB2 UDB 监控

数据库系统的监控是调优数据库服务器的重要部分。监控数据的集合充当基线，可用于比较当前和过去的性能。这支持更早更快地检测性能问题。监控数据还有助于您理解参数和应用程序修改的影响，并支持随着系统的增长分析趋势。

监控数据库服务器的技术概述

DB2 UDB 提供了多个工具，可以用于监控数据库服务器的活动，或分析 SQL 语句如何访问数据；每个工具都服务于不同的目的。表 1 中展示了不同的监控工具。

表 1. DB2 UDB 监控工具概述
监控工具监控信息
快照监控器在特定时刻（获取快照的瞬间）及时地捕获数据库活动状态图片
事件监控器在发生诸如语句执行、事务完成，或者应用程序断开连接之类的特定数据事件时记录数据。STATEMENT 和 DEADLOCK 事件监控器常用于性能调优实践。
SQL 解释工具捕获关于 SQL 语句的访问计划和环境的信息，即如何执行单个 SQL 语句以访问数据
db2batch监控 SQL 语句的性能特点和执行持续时间。它从平面（flat）文件或标准输入中读取 SQL 语句，动态地准备和描述语句，并返回性能基准测试信息，如 SQL 语句的准备时间（Prepare Time）、执行时间（Execute Time）和读取时间（Fetch Time）等等

本文中，我们使用快照监控器和 SQL 解释工具。

快照监控器

快照监控器在不同的层次上收集信息，如表 2 中所示：

表 2. 快照监控器层次
层次所捕获的信息
数据库管理器捕获活动数据库管理器实例的统计信息
数据库为当前数据库分区上的所有活动数据库提供通用的统计信息
应用程序提供连接到当前数据库分区上数据库的所有活动应用程序的有关信息

在每个数据库中，快照监控器基于表 3 中所示的功能组层次收集信息。

表 3. 快照功能组层次
功能组层次所捕获的信息
缓冲池活动读写次数，以及所花时间
锁锁定的数目，死锁的次数
排序所使用的堆的数目，溢出，排序性能
SQL 语句启动时间，停止时间，语句标识
表活动测量活动（行读，行写）
工作单元开始时间，结束时间，完成状态

虽然默认情况下只为 表 3 中所示的每个功能组层次收集一些基本信息，但也可以通过打开快照监控器开关在每个层次上收集更多详细的统计信息。因为监控包含了开销，所以您应该只打开在监控任务中最要紧的那个监控器开关。另一方面，如果您使用测试系统，我们就建议您在调优系统时打开所有的监控器开关。

通过分别使用 UPDATE DBM CFG 或 UPDATE MONITOR SWITCHES 命令在实例或应用程序层打开或关闭监控器开关。当在应用程序层打开监控器开关时，如 DB2 命令窗口，该监控器就将仅仅应用于特定会话。例如，为了在应用程序层打开 BUFFERPOOL、SORT 和 STATEMENT 的监控器开关，就要从 DB2 命令窗口发出下列命令：

% db2 update monitor switches using BUFFERPOOL ON SORT ON STATEMENT ON

因为您将在测试系统中使用 PERFORMER，所以出于简单性的考虑（不必跟踪哪个会话打开了监控器），就通过从 DB2 命令窗口发出下列命令在实例层打开所有的监控器，如清单 1 中所示（默认情况下，TIMESTAMP 和 HEALTH MONITOR 的开关是 ON 的）：

清单 1. 在实例层打开所有监控器开关的命令
% db2 update dbm cfg using DFT_MON_BUFPOOL   ON 
% db2 update dbm cfg using DFT_MON_LOCK      ON 
% db2 update dbm cfg using DFT_MON_SORT      ON 
% db2 update dbm cfg using DFT_MON_STMT      ON 
% db2 update dbm cfg using DFT_MON_TABLE     ON 
% db2 update dbm cfg using DFT_MON_TIMESTAMP ON
% db2 update dbm cfg using DFT_MON_UOW       ON
% db2 update dbm cfg using HEALTH_MON        ON

您或许还需要增加监控器的堆大小，因此这是用于收集监控器信息的内存区域。推荐使用下列命令：

% db2 update dbm cfg using MON_HEAP_SZ 1024

上面的数据库管理器配置修改需要重新启动实例，以便修改生效。

为了找到当前的监控器开关设置，要发出：
% db2 get monitor switches
该命令的输出如清单 2 中所示。

清单 2. get monitor switches 命令的输出
Monitor Recording Switches

Switch list for db partition number 0
Buffer Pool Activity Information  (BUFFERPOOL) = ON  07/27/2005 09:48 
Lock Information                        (LOCK) = ON  07/27/2005 09:48 
Sorting Information                     (SORT) = ON  07/27/2005 09:48 
SQL Statement Information          (STATEMENT) = ON  07/27/2005 09:48  
Table Activity Information             (TABLE) = ON  07/27/2005 09:48
Take Timestamp Information         (TIMESTAMP) = ON  07/27/2005 09:48
Unit of Work Information                 (UOW) = ON  07/27/2005 09:48

在打开合适的监控器开关之后，您可以在同一 DB2 命令窗口使用 GET SNAPSHOT 命令来收集监控统计信息。例如，尝试使用 BANK 数据库来获取快照。在 DB2 命令窗口中，发出下列命令：


% db2 connect to bank
% db2 select * from sysibm.sysdummy1
% db2 get snapshot for all on bank

请注意，上面的 SELECT 语句是由快照所捕获的。我们将在下面的小节中更详细地讨论快照。

SQL 解释工具

SQL 解释工具提供查询优化器为 SQL 语句所选择的访问计划的有关详细信息。该信息存储在 EXPLAIN 表中，可以在稍后使用诸如 Visual Explain、db2expln、dynexpln 和 db2exfmt 的工具进行格式化，从而以友好的可视方式进行表示。

EXPLAIN 表可以在您第一次使用 Visual Explain 时自动进行创建。即使没有创建它们，您也可以手工进行创建，如下：

% cd <db2 install path>\sqllib\misc
% db2 connect to bank
% db2 -tvf EXPLAIN.DDL

本文中，我们使用 db2exfmt 工具。例如，使用 db2exfmt 解释动态 SQL 语句，在 DB2 命令窗口中按照下列步骤进行：


% db2 connect to <database_name>
% db2 set current explain mode explain
% db2 -tvf <Input file with an SQL statement ended with a semicolon>
% db2 set current explain mode no
% db2exfmt -d <dbname> -g TIC -w -1 -n % -s % -# 0 -o <output file>

db2exfmt 工具的输出包括表 4 所示信息：

表 4. db2exfmt 输出概述
区域名称内容
概述DB2 UDB 版本和发布级别，以及运行 explain 工具时的日期和时刻
数据库环境优化器为确定具有最少资源成本的访问计划所考虑的配置参数，包括并行度、CPU 速度、通信速度、缓冲池大小、排序堆大小、数据库堆大小、锁列表大小、最大锁列表、平均应用程序和可用锁
包环境SQL 类型（动态的或静态的）、优化级别、隔离级别以及语句所使用的区内并行度
初始语句应用程序所调用的 SQL 语句
优化语句优化器从初始语句进行转换的 SQL 语句的改写版本，这些语句具有相同查询结果，但允许最优性能
访问计划允许 DB2 UDB 访问数据以解决 SQL 语句的最小扩展路径
操作符描述它展示访问计划的每个阶段（操作符）发生什么
访问计划中使用的对象访问计划中使用的表和/或索引。

我们将在下面的小节中提供更多关于 db2exfmt 的例子。

调优 DB2 UDB 配置参数

DB2 UDB“开箱即用（out of the box）”的参数值是基于使用 256 MB RAM 和单个磁盘的系统的。如果您具有一个较大的系统，就需要修改这些参数，以便最好地利用系统资源。您可以通过使用 Configuration Advisor 确定调优配置的良好起点，Configuration Advisor 基于您的系统资源来推荐数据库参数值。为了运行 configuration advisor，要使用 autoconfigure 命令，或者通过 Control Center 调用它，右击想要配置的数据库，并选择 Configuration Advisor。

出于说明目的，我们不在本例中运行 Configuration Advisor，而是手工调优不同的参数。这里描述的配置参数包括：

  • 缓冲池
  • 异步页面清洗器和 I/O 服务器
  • 排序
  • 日志缓冲区大小
  • 分组 COMMIT 语句

缓冲池

在诸如读、写、更新和删除的所有事务性活动期间，缓冲池充当数据库执行大多数数据操作（除了大对象和长字段数据之外）“内存中”的工作区域。每个数据库都至少需要一个缓冲池。对于具有大于一个页面大小的表空间的数据库而言，需要创建具有匹配页面大小的附加缓冲池。

缓冲池竞争可以成为数据库性能中的重要因素。如果缓冲池大到足以在内存中保存所需数据，就会发生较少磁盘活动。相反，如果缓冲池不够大，那么数据库的整体性能就可能严重受损，而由于应用程序需要进行大量的磁盘活动，数据库管理器就可能成为 I/O 限制。

配置
为了确定数据库中的缓冲池大小，请查看清单 3。

清单 3。 确定缓冲池大小
% db2 connect to bank
% db2 "select BPNAME,NPAGES,PAGESIZE from SYSCAT.BUFFERPOOLS"

BPNAME		NPAGES      	PAGESIZE
------------- 	----------- 	-----------
IBMDEFAULTBP            250   	       4096

1 record(s) selected.

输出显示 BANK 数据库定义了一个缓冲池（IBMDEFAULTBP），该缓冲池具有 250 个页面（NPAGES），每个页面大小（PAGESIZE）为 4096 字节。Windows 上 IBMDEFAULTBP 的默认大小是 250 个页面，而在 UNIX® 平台上，它就是 1000 个页面。当 NPAGES 的值为 -1 时，缓冲池大小就是由数据库配置中的 BUFFPAGE 参数确定的。例如，


% db2 get db cfg for bank
...
Buffer pool size (pages) (BUFFPAGE) = 250

监控
因为缓冲池的目的就是为了在内存中保存页面，用于数据库服务器所需的数据操作，以免从磁盘读取页面，所以对于缓冲池有效性的一个重要测量就是看所请求的页面有多频繁地存在于缓冲池中。缓冲池命中率（hit ratio）测量该有效性，它可以按照下列方法进行计算：


(1- ((缓冲池数据物理读 + 缓冲池索引物理读) / (缓冲池数据逻辑读 + 缓冲池索引逻辑读))) * 100%

命中率越接近 100%，磁盘 I/O 的频率就越低，因而读取数据的开销就越少。

监控缓冲池活动的常用方法就是使用缓冲池快照，如下：（注意：请确保缓冲池监控器开关是　ON。否则，在快照监控期间，就不会收集大多数缓冲池统计信息。）


% db2 get snapshot for bufferpools on <database_name>

应该关注的缓冲池快照的重要元素如下：


Buffer pool data logical reads = 16359
Buffer pool data physical reads = 209
Buffer pool index logical reads = 90
Buffer pool index physical reads = 52

以上示例显示了 98.4% 的良好缓冲池命中率。

示例

从命令提示符启动 PERFORMER：
% performer

图 3 显示了用一个包含 250 个 4K 页面的缓冲池所完成的事务数目：（注意：您系统上的性能结果可能不同。）

图 3. 使用包含 250 个 4K 内存页面的缓冲池的性能结果
使用包含 250 个 4K 内存页面的缓冲池的性能结果

点击查看大图

使用包含 250 个 4K 内存页面的缓冲池的性能结果

在完成测试运行之后，就获取缓冲池快照，如下：


% db2 get snapshot for bufferpools on bank

Buffer pool data logical reads = 183925
Buffer pool data physical reads = 273548
Buffer pool index logical reads = 82
Buffer pool index physical reads = 52

缓冲池命中率 = (1- (273548 + 52) / (183925 + 82)) * 100% = 48.69%
通过 250 个页面的缓冲池大小，应用程序测试运行呈现了 48.69% 的糟糕的缓冲池命中率。

联机增加缓冲池大小，如下：


% db2 connect to bank
% db2 "alter bufferpool IBMDEFAULTBP immediate size 12000"

在发出以上命令时，请查看任务管理器，以便看到内存使用时如何增加的。

监控信息是累积的，除非您重置监控器。因为我们需要在修改缓冲池大小之后获得新的缓冲池命中率，所以要在下一次测试运行之前用该命令重新设置所有监控器：

% db2 reset monitor all

单击 PERFORMER 屏幕上的“Reset”按钮，然后单击“Run”。通过 12000 个 4K 页面的更大缓冲池，性能结果从每 10 秒完成 43 个事务增加到完成 66 个事务，如图 4 中所示。

图 4. 使用包含 12000 个 4K 内存页面的缓冲池的性能结果
使用包含 12000 个 4K 内存页面的缓冲池的性能结果

点击查看大图

使用包含 12000 个 4K 内存页面的缓冲池的性能结果

获取新的快照，并计算新的缓冲池命中率，如下：


% db2 connect to bank
% db2 get snapshot for bufferpools on bank

Buffer pool data logical reads = 269482
Buffer pool data physical reads = 1838
Buffer pool index logical reads = 82
Buffer pool index physical reads = 50

缓冲池命中率 = (1 - (1838 + 50) / (269482 + 82)) * 100% = 99.29%

现在的缓冲池命中率就显示了较好的结果。一般情况下，缓冲池命中率在 80% 以上就认为是良好的。如果您的系统具有较低的缓冲池命中率，您就可以通过增加缓冲池大小来进一步地取得更好的应用程序性能结果。如果将缓冲池大小增加到 20,000 个 4K 页面并重新运行 PERFORMER，那将会发生什么情况呢？性能会有所提高吗？

在这种特别的情况下，性能可能不会提高。BANK 数据库的大小约为 36 MB（数据库的完全备份可以提供快速方法来测量数据库大小）。这意味着整个数据库都可以进入内存中。12000 个页面（48 MB）或 20000 个页面（80 MB）的大小将没有区别，因为整个数据库都可以包含在 48MB 中。实际上，取决于应用程序所执行的 SQL 的类型，将缓冲池的大小增加到某个极限之后就可能不会再提高性能了。我们建议您不断增加缓冲池大小，直到看到不再有性能提高。

与其他内存缓冲区相比，缓冲池对数据库性能的影响最为显著。但是请记住，缓冲池是数据库共享内存集中的一部分。在 32 位的数据库环境中，DB2 UDB 具有数据库共享内存大小的限制，在 AIX® 上是 1.75 GB，Sun Solaris 上是 3.35 GB，HP-UX 上是 0.75 GB 到 1 GB 之间的值，Linux 上是 1.75 GB，Windows 上是 2 GB 或 3 GB（如果 boot.ini 中启用了 3 GB 开关）；因此，您需要平衡缓冲池与其他数据库共享内存缓冲区的配置。在 64 位的环境中，这是一个不需要考虑的问题。

异步 I/O 服务器和页面清洗器

DB2 UDB 鼓励缓冲池与磁盘之间页面读写的异步 I/O 访问，以便获得最优性能。

I/O 服务器将数据页异步地从磁盘读入参与应用程序需求的缓冲池中：这称作“预取（prefetching）”。预取可以提高数据库的性能，因为当代理访问这些页面时，将在缓冲池中找到它们，从而减少了应用程序等待页面从磁盘读入缓冲池的时间。

另一方面，数据库代理需要缓冲池中的空间之前，页面清洗器将已修改的页面从缓冲池写入磁盘。因此，数据库代理不必等待写出修改的页面，就可以使用缓冲池中的空间。这将提高数据库的整体性能。页面清洗器可以通过多种理由触发，例如，当达到已修改页面的阈值时。

配置
用于数据库的 I/O 服务器（预取器）的数目可以使用 NUM_IOSERVERS 数据库配置参数进行配置。为了充分利用系统中的所有 I/O 设备，使用较好的值通常是比数据库所驻留的物理设备的数目多一两个。最好配置附加的 I/O 服务器，因为每个 I/O 服务器相关的开销非常小，而未使用的 I/O 服务器仍然将保持空闲。

NUM_IOCLEANERS 数据库配置参数允许您为数据库指定异步页面清洗器的数目。在为该参数设置值时，要考虑下列因素：

  • 应用程序类型
    如果是一个不具有更新而仅仅查询的数据库，就将该参数设置为 0。如果查询工作负载导致创建许多 TEMP 表（您可以通过使用解释实用程序来确定）时除外。如果事务是对数据库运行的，那么就将该参数设置成 1 到用于该数据库的物理存储设备数目之间的值。
  • 工作负载
    具有较高事务更新率的环境可能需要配置更多页面清洗器。
  • 缓冲池大小
    具有大型缓冲池的环境也可能需要配置更多页面清洗器。

还请记住，配置太多页面清洗器可能会损害数据库服务器上的运行队列，并导致极大的性能下降。因此，根据经验法则，您可以考虑将页面清洗器的数目设置为数据库服务器上 CPU 的数目。

已修改页面的阈值参数（CHNGPGS_THRESH）决定异步页面清洗器应启动时已修改的页面的百分比。

监控
因为异步页面清洗器和 I/O 服务器的活动与缓冲池活动是如此紧密联系，所以您可以再次利用缓冲池快照来测量页面清洗器和预取器的有效性。

这此，缓冲池快照的重要元素将重点放在了下面：


Buffer pool data logical reads = 2700145
Buffer pool data writes = 0
Buffer pool index logical reads = 95
Asynchronous pool data page reads = 85
Asynchronous pool data page writes = 0
Buffer pool index writes = 0
Asynchronous pool index page reads = 0
Asynchronous pool index page writes = 0

预取活动可以通过异步和同步的读 I/O 来确认。异步读取比率按照下列公式计算： The prefetching activity can be validated by the amount of asynchronous versus synchronous read I/O. The asynchronous read ratio is calculated as follows:

((异步池数据页读 + 异步池索引页读) / (缓冲池数据逻辑读 + 缓冲池索引逻辑读)) * 100%

异步读取比率值较小可能是由多种原因导致的，例如：

  • 工作负载读写单行，因此它无法利用预取。
  • 为数据库配置的预取器太少。
  • 数据库中的表空间仅仅配置了一个容器，以致无法进行预取。

异步页面清洗器的有效性是由异步数据和索引页的写比率进行测量的。如果下列两个条件都成立，那么就可以减少异步页面清洗器的数目（NUM_IOCLEANERS）：

  • 缓冲池数据写约等于异步池数据页写。
  • 缓冲池索引写约等于异步池索引页写。

如果下列条件中有一个成立，就应增加该参数：

  • 缓冲池数据写远远大于异步池数据页写。
  • 缓冲池索引写远远大于异步池索引页写。

示例

首先，验证 NUM_IOSERVERS 和 NUM_IOCLEANERS 参数的当前设置：

% db2 get db cfg for bank

Number of asynchronous page cleaners (NUM_IOCLEANERS) = 1
Number of I/O servers (NUM_IOSERVERS) = 3

在 PERFORMER 的下一次运行之前，重置快照监控器：
% db2 reset monitor all

这里，缓冲池快照通过应用前面所讨论的规则，用于验证系统上是否正确配置了 NUM_IOSERVERS 和 NUM_IOCLEANERS 参数。

% db2 get snapshot for bufferpools on bank

Buffer pool data logical reads = 269482
Buffer pool data writes = 0
Buffer pool index logical reads = 82
Asynchronous pool data page reads = 1236
Asynchronous pool data page writes = 0
Buffer pool index writes = 0
Asynchronous pool index page reads = 0
Asynchronous pool index page writes = 0

异步读取比率 = ((1236 + 0) / (269482 + 82)) * 100% = 0.004%

异步读取比率如此小的原因是因为数据库中的表空间每个仅仅设置了一个容器，以致无法进行预取。因此，NUM_IOSERVERS 参数的当前设置可以保持相同。

然后，您需要检查缓冲池数据写（0）与异步池数据页写（0）以及缓冲池索引写（0）与异步池索引写（0）的监控数据。通过应用前面所讨论的规则，该健康检查实践表明 NUM_IOCLEANERS 参数不需要进行进一步的调优工作。

CHNGPGS_THRESH 的 60% 的默认值对于 OLTP 工作负载而言通常太高了。介于 20% 和 40% 之中的值更好一些。例如，如果您具有一个 2 GB 的缓冲池，当达到 60% 的修改页面时，在触发页面清洗器时就有 1.2 GB（2 GB 的 60%）将写入磁盘。当这发生时，可能导致系统整体速度的下降。通过将 CHNGPGS_THRESH 设置为一个较低的数，如 20%，将更频率地触发页面清洗器，但只有较少的数据写入磁盘，而用户也觉察不到这一延迟。

排序

DB2 UDB 具有两种基本类型的排序：共享排序和私有排序。

只有 INTRA_PARALLEL 数据库管理器配置参数是 ON 或启用集中器（concentrator）时（即当 MAX_CONNECTIONS 大于 MAX_COORDAGENTS 时），才可以使用共享排序。通常在需要具有多个子代理供给（subagent feeding）或从排序中进行预取时使用它们。用于共享排序的内存是从数据库共享内存集中分配的。

当 INTRA_PARALLEL 参数是 OFF，并且禁用集中器（concentrator）时，所有的排序就都是私有的。装入和创建索引的操作通常使用私有排序进行索引键的排序，无论 INTRA_PARALLEL 参数的值如何。用于私有排序的内存是从代理的私有内存中分配的。因此，私有排序只能被单个代理访问。

对于共享排序，SHEAPTHRES_SHR 数据库配置参数是数据库范围的可以用于任何一次排序的数据库共享内存总数的硬限制。当用于活动共享排序的共享内存总数达到了该极限，随后的排序就会以 SQL0955 失败。如果 SHEAPTHRES_SHR 的值是 0，那么共享排序内存的阈值将等于 SHEAPTHRES 数据库管理器配置参数的值，该参数也用于表示排序内存。

在共享排序和私有排序中，SORTHEAP 数据库配置参数是将用于单个排序的 4K 内存页面的最大数目。

同样，排序包含两个步骤：

  • 排序阶段
    排序可以是溢出的，也可以是非溢出的。如果无法将排序的数据整个放入排序堆中（排序堆是每次执行排序时分配的一块内存），它就会溢出到该数据库所有的临时表中。不溢出的排序通常执行得比那些溢出的好。
  • 排序结果的返回阶段
    返回可以是管道的（piped），也可以是非管道的（non-piped）。如果排序信息可以直接返回，而无需临时表来存储最终排序的数据列表，它就是管道排序（piped sort）。如果排序信息的返回需要临时表，那它就是非管道排序（non-piped sort）。管道排序通常执行得比非管道排序好。

配置
配置 SHEAPTHRES：

  • 通常应将 SHEAPTHRES 设置为 SORTHEAP 的倍数。
  • 通常的经验是将 SHEAPTHRES 至少设置为 10 * SORTHEAP。
  • 对于带有 INTRA_PARALLEL ON 的索引创建，请确保 SHEAPTHRES >= d * SORTHEAP，其中 d 是 SMP 度（degree）。
  • 对于 LOAD，所有索引键的排序都是在一个进程的私有内存空间中同时进行的：SMP 中的 db2lrid，串行中的 db2lfrm0。要确保 SHEAPTHRES >= n * SORTHEAP，其中，n 是装入的表上的索引数目。还请记住，AIX 上大约有最大为 250MB 的私有虚拟内存用于 LOAD 中的索引键排序（这是内存瓶颈）。

配置 SORTHEAP：

  • 增加 SORTHEAP 的值可以显著地提高排序性能，因为内存中完成的排序将更多，而磁盘 I/O 将减少。
  • 将 SORTHEAP 配置少了可能导致由于溢出和 I/O 的增加所带来的性能下降。
  • 将 SORTHEAP 配置多了则导致容量问题，因为将会更快地超出 SHEAPTHRES。同样，因为到达 SHEAPTHRES_SHR（用于共享排序）或超出 SHEAPTHRES（用于私有排序），分配给新排序的内存数目将不断减少，这可能也会导致性能下降。

监控
除了缓冲池，排序是影响数据库性能的另一重要因素。DB2 UDB 监控排序活动的很多方面。

数据库管理器快照展示下列监控元素：

% db2 get snapshot for database manager

Private Sort heap allocated = 0
Private Sort heap high water mark = 80
Post threshold sorts = 0
Piped sorts requested = 167
Piped sorts accepted = 167

数据库快照展示下列监控元素

% db2 get snapshot for database on <database_name>

Total Private Sort heap allocated = 0
Total Shared Sort heap allocated = 0
Shared Sort heap high water mark = 0
Total sorts = 170
Total sort time (ms) = 5015
Sort overflows = 93
Active sorts = 0
Commit statements attempted = 446
Rollback statements attempted = 24

下面列出了几个用于评估排序活动性能的关键公式：

阈值后排序（Post threshold sort）是在超出排序堆阈值之后，请求堆的排序数目。在到达排序堆阈值之后开始的排序可能不会接收到最合适的内存数量去执行。

阈值后排序率 = (阈值后排序 / 总排序) * 100%

如果 阈值后排序率很高，您就应该增加排序堆阈值（sheapthres），或通过修改 SQL 查询调整应用程序以使用较少或较小的排序。

管道排序率 = (接受的管道排序 / 请求的管道排序) * 100%

如果管道排序率的值很低，您就应该考虑增加排序堆阈值（sheapthres）以获得更高的排序性能。

排序溢出率 = (排序溢出 / 总排序) * 100%

如果排序溢出率的值很高，您就应该增加排序堆（SORTHEAP），并且/或者也增加排序堆阈值（sheapthres）。

每个事务的排序 = 总排序 / (尝试的提交语句 + 尝试的回滚语句)

当每个事务有三个或更多排序时，就不是调优排序堆或排序堆阈值，而是应该查找问题源，通过动态 SQL 快照确定执行较差的 SQL 语句，并在必要处添加合适的索引。

示例

PERFORMER 程序是一个 OLTP 应用程序，因此，它没有复杂的 SELECT 查询，排序也很少。这里的示例用于检查 PERFORMER 程序外部的排序调优技术。

这里的准备步骤是为了将 IBMDEFAULTBP 的大小重新减小到 250 个页面，SORTHEAP 减小到 16 个页面，以便您可以在小型数据库环境中查看相关的性能调优技术。

% db2 connect to bank
% db2 "alter bufferpool IBMDEFAULTBP size 250"
% db2 update db cfg for bank using SORTHEAP 16
% db2 force applications all
% db2 connect to bank

为了重置快照监控器，要发出：
% db2 reset monitor all

您可以创建一个 SQL DDL 文件 order_by.ddl，包含该 SQL：

SELECT NAME,BALANCE FROM ACCOUNT ORDER BY BALANCE;

为了在数据库中触发排序活动，要重复下列命令多次，例如 3 次：

% db2 -tvf order_by.ddl

数据库管理器快照可以用于确定阈值后排序活动：

% db2 get snapshot for database manager
...
Private Sort heap allocated = 0
Private Sort heap high water mark = 256
Post threshold sorts = 0
Piped sorts requested = 3
Piped sorts accepted = 3

其中没有阈值后排序活动。然而，管道排序是 100% 接受的，管道排序率 = (3 / 3) * 100% = 100%。

然后，您可以利用数据库快照来确定数据库层的排序活动：

% db2 get snapshot for database on bank
...
Total Private Sort heap allocated = 0
Total Shared Sort heap allocated = 0
Shared Sort heap high water mark = 0
Total sorts = 3
Total sort time (ms) = 1097
Sort overflows = 3
Active sorts = 0
Commit statements attempted = 4
Rollback statements attempted = 5

排序溢出率 = (3 / 3) * 100% = 100%。由于这个较高的排序溢出率，您需要增加排序堆（SORTHEAP）和/或排序堆阈值（SHEAPTHRES）。本示例中不需要对每个事务的排序进行评估。

按照通过快照监控进行的排序活动的健康检查，您现在需要增加排序堆（SORTHEAP）和/或排序堆阈值（SHEAPTHRES）。

为了增加 SORTHEAP 参数，要发出：

% db2 update db cfg for bank using SORTHEAP 400
% db2 force applications all

在每次修改中，使用快照监控来确定是否需要进一步的排序调优。如果是，就重复相同的步骤。

日志缓冲区大小

日志缓冲区作为内存中的分级区域（staging area）来保存日志记录，而不是让 DB2 UDB 引擎直接将每条日志记录写入磁盘中。

当发生下列条件之一时，将日志记录写入磁盘：

  • 按照 MINCOMMIT 配置参数所定义的，事务提交或事物组提交
  • 日志缓冲区已满
  • 发生了一些其他的内部数据库管理器事件。

配置
日志缓冲区大小是由 LOGBUFSZ 数据库参数定义的。如果在某个专用的日志磁盘上存在大量的读活动，或者具有较高的磁盘利用率，就增加日志缓冲区域的大小。在增加 LOGBUFSZ 参数的值时，您还应该考虑 DBHEAP 参数，因为日志缓冲区使用来自数据库堆区域中的空间。

监控
您可以通过查看下列快照元素，使用数据库快照来确定 LOGBUFSZ 数据库参数是否是最优的：

% db2 get snapshot for database on <database_name>
Log pages read = 0
Log pages written = 6721

日志页面读（Log pages read）是日志记录器（logger）从磁盘读取的日志页面的数目，而日志页面写（Log pages written）是日志记录器（logger）写入磁盘的日志页面的数目。日志页面读数目与日志页面写数目的比值应尽可能小。理想情况下，应该没有日志页面读。如果看到了较多数目的日志页面读，就表示应该增加 LOGBUFSZ 数据库参数的值。

示例
对于具有许多更新工作负载的数据库系统而言，下面日志缓冲区的默认大小（LOGBUFSZ）通常都太小。

% db2 get db cfg for bank

Log buffer size (4KB) (LOGBUFSZ) = 8

PERFORMER 程序没有许多更新。因此，您不会在这里的 PERFORMER 中实施 LOGBUFSZ 调优提示。但是，您仍然可以在自己的应用程序中进行尝试。如果您的应用程序碰巧具有高更新的工作负载，那么数据库快照监控就可以暴露大量的日志页面读。如果情况如此，您就可以考虑增加 LOGBUFSZ 数据库参数。通常应将它增加为不少于 256 页面。

组中提交数目

在发生许多较短并发事务的环境中，每条 COMMIT 语句默认触发对磁盘的一次日志缓冲区刷新（flush）。因此，日志记录器（logger）进程频繁地将少量日志数据写入磁盘中，其中具有日志 I/O 开销所导致的附加延迟。提交分组（Commit grouping）允许到达请求提交数目的最小值之后才将日志缓冲区数据写入磁盘。该功能可以通过减少日志 I/O 开销提高性能。

仅仅当 MINCOMMIT 参数的值大于 1 且连接到数据库的应用程序数目大于或等于该参数的值时，才发生这种提交分组。在执行提交分组时，可以将应用程序的提交请求挂起，直到 1 秒钟之后或提交请求的数目等于 MINCOMMIT 参数的值。

配置
如果多个读/写应用程序通常请求并发的数据库提交，就从其默认值开始增加 MINCOMMIT 参数。这将导致更高效的日志文件 I/O，因为它将发生得不那么频繁，并在每次发生时写入更多日志记录。

您也可以对每秒的事务数目进行取样，并调整该参数以适应每秒事务数目的峰值（或它的某些较大比例）。适应峰值活动将最小化事务密集期间日志记录的写开销。

监控
数据库快照监控器可以用于确定每秒所执行的事务数目，如下：

% db2 get snapshot for database on <database_name>

Last reset timestamp = 07/30/2005 15:54:22.392292
Snapshot timestamp = 07/30/2005 19:24:10.858723
Commit statements attempted = 13784
Rollback statements attempted = 134

每秒的事务数目：

((尝试的提交语句 + 尝试的回滚语句) / (快照时间戳 - 最后重置的时间戳))

示例
MINCOMMIT 参数可以对数据库性能带来正面和负面的影响，所以需要正确设置。

% db2 get snapshot for database on bank

Last reset timestamp = 07/30/2005 19:32:45.570089
Snapshot timestamp = 07/30/2005 19:33:14.650596
Commit statements attempted = 7374
Rollback statements attempted = 1

每秒的事务数目 = (7374 + 1) / (19:33:14.650596 - 19:32:45.570089) = 每秒 254.13 个事务。

考虑到 PERFORMER 程序仅仅调用较少的 10 个并发应用程序连接，而短事务的数目很大（即每秒 254 个事务），所以不应修改 MINCOMMIT 参数的默认值；否则，将显著增加那些短事务。您自己进行测试吧！

数据库查询优化器

DB2 UDB 中包含了一个功能强大基于成本的查询优化器，用于确定访问数据的最佳策略。DB2 UDB 查询优化器总是试图通过将初始查询改写成优化的形式，生成备选查询执行计划，对每种备选计划的 I/O、CPU、内存和通信使用进行建模，并选择成本最小的访问计划执行，来确定对数据库执行查询的最便宜的方法。

更新目录统计数据

查询优化器使用数据库中的 SYSSTAT 目录视图来检索数据库对象的统计信息，并确定访问数据库的最佳方法。如果无法获得当前的统计数据，优化器就可能会基于不准确的默认统计数据选择低效的访问计划。

默认情况下，对于新创建的数据库，直到执行了 runstats 命令之后，才会收集数据库对象统计数据并将之存储在 SYSSTAT 目录视图中。如果还没有填充目录统计数据，那么 SYSSTAT 目录视图中诸如 CARD、NPAGES、FPAGES 等列就具有值 -1。清单 4 中展示了一个示例：

清单 4. 确定是否运行了 RUNSTATS
% db2 connect to bank
% db2 describe table sysstat.tables
% db2 "select tabname,card,npages,fpages from sysstat.tables"
 
TABNAME       CARD      NPAGES      FPAGES
------------  --------  ----------  ----------
ACCOUNT             -1          -1          -1
AUDITLOG            -1          -1          -1

我们极力建议您使用 runstats 命令来收集表和索引上的当前统计数据，特别是当发生了大量的更新活动或由于上次执行 runstats 命令而创建了新的索引时。这就为优化器提供了用于确定最佳访问计划的最准确的信息。例如：

% db2 runstats on table <table_name> with distribution and detailed indexes all

其中，<table_name> 是全限定的表明，即其中包含了模式名。

有时候，您可能需要对数据库中的所有表执行 runstats 操作。这样做最容易的方法就是使用 reorgchk 命令：

% db2 reorgchk update statistics on table all

如果对于确定数据库是否包含表和索引上的最新统计数据有困难，您就可以发出下列命令来验证执行 runstats 操作的最近时间：

% db2 "select name, stats_time from sysibm.systables"

如果仍然还没有运行 runstats，您将会看到 stats_time 列为"-"。否则，它就返回最后一次运行 runstats 的时间戳。

在下面的小节中，我们将在运行 runstats 之后测试 PERFORMER。

缓存糟糕的查询

应用程序可以运行上百条不同的 SQL 语句；如果其中只有一条的代码不正确或未调至最优，那可能会影响整个系统的性能。您如何可以捕捉这些糟糕的查询呢？

动态 SQL 语句快照提供了应用程序所运行的动态 SQL 语句的有关信息。按照这些步骤重置监控器，运行 PERFORMER，然后获得动态 SQL 语句的快照：

% db2 reset monitor all
% performer (and click on the "Run" button)
% db2 get snapshot for dynamic sql on bank

清单 5 展示了部分动态 SQL 快照的输出。

清单 5. 动态 SQL 快照
      Dynamic SQL Snapshot Result 

Database name                      = BANK 
Database path                      = C:\DB2\NODE0000\SQL00006\ 

Number of executions               = 1 
Number of compilations             = 1 
Worst preparation time (ms)        = 0 
Best preparation time (ms)         = 0 
Internal rows deleted              = 0 
Internal rows inserted             = 0 
Rows read                          = 100000 
Internal rows updated              = 0 
Rows written                       = 0 
Statement sorts                    = 0 
Statement sort overflows           = 0 
Total sort time                    = 0 
Buffer pool data logical reads     = 1820 
Buffer pool data physical reads    = 1727 
Buffer pool temporary data logical reads   = 0 
Buffer pool temporary data physical reads  = 0 
Buffer pool index logical reads    = 0 
Buffer pool index physical reads   = 0 
Buffer pool temporary index logical reads  = 0 
Buffer pool temporary index physical reads = 0 
Total execution time (sec.ms)      = 0.493177 
Total user cpu time (sec.ms)       = 0.040057 
Total system cpu time (sec.ms)     = 0.010014 
Statement text                     = SELECT NAME, BALANCE FROM 
                                     ACCOUNT WHERE ACCT_ID =14680

Number of executions               = 1 
Number of compilations             = 1 
Worst preparation time (ms)        = 0 
Best preparation time (ms)         = 0 
Internal rows deleted              = 0 
Internal rows inserted             = 0 
Rows read                          = 100000 
Internal rows updated              = 0 
Rows written                       = 0 
Statement sorts                    = 0 
Statement sort overflows           = 0 
Total sort time                    = 0 
Buffer pool data logical reads     = 1820 
Buffer pool data physical reads    = 1527 
Buffer pool temporary data logical reads   = 0 
Buffer pool temporary data physical reads  = 0 
Buffer pool index logical reads    = 0 
Buffer pool index physical reads   = 0 
Buffer pool temporary index logical reads  = 0 
Buffer pool temporary index physical reads = 0 
Total execution time (sec.ms)      = 1.034426 
Total user cpu time (sec.ms)       = 0.040058 
Total system cpu time (sec.ms)     = 0.000000 
Statement text                     = SELECT NAME, BALANCE FROM 
                                     ACCOUNT WHERE ACCT_ID =47030

Number of executions               = 73 
Number of compilations             = 1 
Worst preparation time (ms)        = 132 
Best preparation time (ms)         = 132 
Internal rows deleted              = 0 
Internal rows inserted             = 0 
Rows read                          = 0 
Internal rows updated              = 0 
Rows written                       = 73 
Statement sorts                    = 0 
Statement sort overflows           = 0 
Total sort time                    = 0 
Buffer pool data logical reads     = 78 
Buffer pool data physical reads    = 28 
Buffer pool temporary data logical reads   = 0 
Buffer pool temporary data physical reads  = 0
Buffer pool index logical reads    = 0 
Buffer pool index physical reads   = 0 
Buffer pool temporary index logical reads  = 0 
Buffer pool temporary index physical reads = 0 
Total execution time (sec.ms)      = 1.383423 
Total user cpu time (sec.ms)       = 0.000000 
Total system cpu time (sec.ms)     = 0.000000 
Statement text                     = INSERT INTO 
                                     AUDITLOG VALUES (?,?,?,?,?)
 ...

我们提取了与三个不同查询相对应的三部分动态 SQL 快照：

SELECT NAME, BALANCE FROM ACCOUNT WHERE ACCT_ID =14680
SELECT NAME, BALANCE FROM ACCOUNT WHERE ACCT_ID =47030
INSERT INTO AUDITLOG VALUES (?,?,?,?,?)

请注意，除了 ACCT_ID 的值之外，这两条 SELECT 语句完全相同。有意将 PERFORMER 程序编码为不具有参数标志符（?），以显示您不应做什么！DB2 UDB 优化器将这两条 SELECT 语句看作不同的查询，因此将分别编译每条查询从而导致额外开销，如这些字段所示：

Number of executions = 1
Number of compilations = 1

另一方面，如果您查看 INSERT 语句，就可以看到它使用了 5 个参数标志符。字段：

Number of executions = 73
Number of compilations = 1

显示只需要一次编译，具有不同值的同一查询执行了 73 次。

接着，让我们捕获糟糕的 SQL 语句。通过操作系统命令，您可以将快照命令的输出重新定向到一个文件，如下：

% db2 get snapshot for dynamic sql on bank > snap1.txt

然后，您可以使用操作系统或编辑器命令来查找字段“Total execution time”。按照降序对结果进行排序，并开始分析具有最高值的查询。例如，在我们的 Windows 系统中，我们安装了一个 UNIX 模拟器；因此，我们可以使用 grep 命令，如下：

% grep -i 'Total execution' snap1.txt

这将提供一个列表，如清单 6 中所示：

清单 6. 查找糟糕的查询
Total execution time (sec.ms)      = 0.471460
Total execution time (sec.ms)      = 1.034426
Total execution time (sec.ms)      = 0.354776
Total execution time (sec.ms)      = 0.197684
Total execution time (sec.ms)      = 0.401673
Total execution time (sec.ms)      = 0.820391
Total execution time (sec.ms)      = 0.000000
Total execution time (sec.ms)      = 0.532227
Total execution time (sec.ms)      = 0.436514
Total execution time (sec.ms)      = 0.445380
Total execution time (sec.ms)      = 0.943996
Total execution time (sec.ms)      = 0.300309
Total execution time (sec.ms)      = 41.844554
Total execution time (sec.ms)      = 1.058051
Total execution time (sec.ms)      = 0.394608
...

虽然我们没有排序该列表，但可以立即看到具有最高执行时间的查询是 41.844554。清单 7 展示了动态 SQL 快照输出中针对该查询的那部分。

清单 7. 糟糕查询的动态 SQL 快照区域
Number of executions               = 77 
Number of compilations             = 1 
Worst preparation time (ms)        = 0 
Best preparation time (ms)         = 0 
Internal rows deleted              = 0 
Internal rows inserted             = 0 
Rows read                          = 7700000 
Internal rows updated              = 0 
Rows written                       = 77 
Statement sorts                    = 0 
Statement sort overflows           = 0 
Total sort time                    = 0 
Buffer pool data logical reads     = 140140 
Buffer pool data physical reads    = 124493 
Buffer pool temporary data logical reads   = 0 
Buffer pool temporary data physical reads  = 0 
Buffer pool index logical reads    = 0 
Buffer pool index physical reads   = 0 
Buffer pool temporary index logical reads  = 0 
Buffer pool temporary index physical reads = 0 
Total execution time (sec.ms)      = 41.844554 
Total user cpu time (sec.ms)       = 2.743941 
Total system cpu time (sec.ms)     = 1.061525 
Statement text                     = UPDATE ACCOUNT SET BALANCE = ? 
                                            WHERE ACCT_ID = ?

通过分析该输出，我们注意到该查询已经执行了 77 次，因此，41.844554 的总执行就是 77 次执行。用 41.844554 除以 77，我们得到每个查询的执行就是 0.5434358，因此，它并非和看上去的一样糟糕。是否可以提高该查询呢？当然可以。行读取的数目（77）表明很可能发生表扫描。然而，这并非一定是代价最高的查询。如果我们重复与前面相同的过程，就会发现具有该执行时间的查询：

Total execution time (sec.ms) = 1.034426

实际上是花费最多时间的查询。有问题的查询是：

SELECT NAME, BALANCE FROM ACCOUNT WHERE ACCT_ID =47030

快照中针对该查询的完整部分已经包含在前面的清单 5 中了。下一小节将展示如何修复该查询。

在该文章系列的第 2 部分中，我们将描述表快照函数的使用，它可以用于获得这一相同类型的信息，但存储在您可以用 SQL 语句进行操作的表中。

理解访问计划

在 DB2 UDB 监控小节中，您已经了解到如何使用 SQL 解释实用程序（db2exfmt）捕获给定查询的访问计划。本小节将关注如何理解访问计划本身，以便您可以确定迟钝查询的根本原因，然后解决该问题。

下面的示例通过分析我们在前面小节中所捕获的糟糕查询，介绍访问计划的基础知识。

创建脚本文件 select.ddl，其中包含下列 SELECT 语句：

SELECT NAME, BALANCE from ACCOUNT WHERE ACCT_ID=47030;

您可以执行下列步骤，用于为以上 SELECT 语句生成访问计划：

% db2 connect to bank
% db2 set current explain mode explain
% db2 -tvf select.ddl
% db2 set current explain mode no
% db2exfmt -d bank -g TIC -w -1 -n % -s % -# 0 -o exfmt_runstats.out

db2exfmt 输出的结构

在 exfmt_runstats.out 文件中，您可以看到 db2exfmt 的输出显示了许多关于数据库环境和查询的宝贵信息。下面列出了一些重要的区域。

Database Context 区域列出了优化器在确定具有最少资源成本的访问计划时所考虑的配置参数。如清单 8 中所示。

清单 8. db2exfmt 输出的 Database Context 区域
Database Context:
----------------
        Parallelism:            None
        CPU Speed:              9.053265e-007
        Comm Speed:             0
        Buffer Pool size:       250
        Sort Heap size:         400
        Database Heap size:     600
        Lock List size:         50
        Maximum Lock List:      22
        Average Applications:   1
        Locks Available:        1122

下一主要区域提供了包的细节，例如 SQL 是动态的还是静态的、优化级别以及隔离级别。更重要的是，您可以看到区域编号以及从何处发出的查询（QUERYTAG: CLP）。当您将访问计划重新匹配事件监控器输出时，这极其有用。它允许我们更好地跟踪特定的查询和有关事件。清单 9 展示该区域。

清单 9. db2exfmt 输出的 Package Context 区域
Package Context:
---------------
        SQL Type:               Dynamic
        Optimization Level:     5
        Blocking:               Block All Cursors
        Isolation Level:        Cursor Stability

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

清单 10 展示了初始查询语句及其优化版本。

清单 10. db2exfmt 输出的初始和优化区域
Original Statement:
------------------
select name,balance
from account
where acct_id=47030

Optimized Statement:
-------------------
SELECT Q1.NAME AS "NAME", Q1.BALANCE AS "BALANCE"
FROM CXWANG.ACCOUNT AS Q1
WHERE (Q1.ACCT_ID = 47030)

清单 11 展示了 SELECT 语句的访问计划。

清单 11. db2exfmt 输出的 Access Plan 区域
Access Plan:
-----------
        Total Cost:             3420.41
        Query Degree:           1

      Rows
     RETURN
     (   1)
      Cost
       I/O
       |
        1
     TBSCAN
     (   2)
     3420.41
      1820
       |
     100000
 TABLE: CXWANG
     ACCOUNT

其余的两个区域提供了关于访问计划的每个操作符以及查询所使用的表和/或索引的细节。

访问计划操作符
清单 12 展示了访问计划的基本组件。

清单 12. 访问计划的基本组件
    cardinality
<access plan operator>
      ( # ) 
       cost
     I/O cost

基数（cardinality）表示访问计划操作符所返回的行的估计数目。访问计划操作符要么是必须在数据上执行的动作，要么是表或索引的输出。成本表示该操作以及前面操作的 CPU 累计成本，而 I/O 成本表示 I/O 子系统中的操作符的成本。

成本单元（unit of cost）是 timeron。timeron 不直接等于真正的消耗时间，但是对数据库管理器所需的资源（成本）给出了一个相对粗略的估算。

下面是您可以在本文中看到的一些访问计划操作符例子：

  • RETURN：表示从查询到用户的数据返回。
  • FETCH：使用指定的记录标识符从表中读取列。
  • TBSCAN：通过直接从数据页中读取所有必要的数据来检索行。
  • IXSCAN：用可选的启动/停止条件扫描表的索引，生成有序的行流。

表扫描与索引扫描
如果没有创建合适的索引，或者如果索引扫描的成本更高一些，优化器通常就选择表扫描。当表十分小且索引集群率（index-clustering ratio）十分低，或查询需要很多表行时，索引扫描的成本可能更高。

前面的访问计划显示 SELECT 语句的总成本是 3420.41 timerons，这来自于表扫描操作符（TBSCAN）对 ACCOUNT 表进行的操作。因为该 SELECT 语句的结果集中只期望一行，所以表扫描在这里被认为是代价较高的操作。或者，可以尝试索引扫描，以便取得更好的性能。

在进行创建索引的工作之前，您可以运行 PERFORMER 来获得性能基线，以便稍后进行比较。本例中，10 秒中使用 12,000 个 4k 页面缓冲池完成的事务数目是 66，如图 4 所示。

在 ACCOUNT 表上创建索引，如下：

% db2 connect to bank
% db2 describe table account
% db2 "create index acct_id_inx on account (acct_id)"

用 runstats 命令更新目录统计数据，以便优化器可以考虑这个新创建的索引：

% db2 runstats on table cxwang.account with distribution and detailed indexes all

为了在 ACCOUNT 表上创建索引之后评估新的访问计划，要发出：

% db2 set current explain mode explain
% db2 -tvf select.ddl
% db2 set current explain mode no
% db2exfmt -d bank -g TIC -w -1 -n % -s % -# 0 -o exfmt_index.out

清单 13 展示了新的访问计划。

清单 13. 创建索引之后的访问计划
Access Plan:
-----------
        Total Cost:             38.6219
        Query Degree:           1

            Rows
           RETURN
           (   1)
            Cost
             I/O
             |
              1
           FETCH
           (   2)
           38.6219
              3
          /---+---\
        1         100000
     IXSCAN   TABLE: CXWANG
     (   3)       ACCOUNT
     25.7612
        2
       |
     100000
 INDEX: CXWANG
   ACCT_ID_INX

通过在 ACCT_ID_INX 列上添加索引，新的访问计划显示了从 3420.41 到 38.6219 timeron 的较大成本缩减。

图 5 展示了创建索引之后 PERFORMER 的极大的性能提高。从 66 个事务到 2051：

图 5. 用 ACCOUNT 表上的索引扫描的性能结果
影响性能的区域

点击查看大图

影响性能的区域

结束语

本文使用 Java 示例程序（PERFORMER）介绍了 DB2 UDB 性能监控和调优基础。您可以应用这些简单的一步步的性能调优示例来提高您自己的 DB2 UDB 数据库系统上的性能。此外，您也有机会了解如何评估和分析访问计划，并修复“糟糕的查询”。在该文章系列的第 2 部分中，我们将使用更完整的数据库场景，该场景是构建在本文中所使用的一个场景之上的。我们将讨论其他的性能监控和调优技术、Design Advisor 的使用、活动监控器、表快照函数和其他工具。第 2 部分还将介绍 OLTP、DSS 以及混合的工作负载。

下载资源

相关主题

评论

添加或订阅评论，请先登录或注册。

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Information Management, Java technology
ArticleID=93838
ArticleTitle=DB2 UDB 性能调优场景，第 1 部分: 用 Java 程序演示 DB2 UDB OLTP 调优
publish-date=08112005