iperf udb_第1部分，用Java程序说明了DB2 UDB OLTP调优-CSDN博客

在使用数据库系统时，性能是最重要的方面之一。影响系统性能的三个主要方面是：CPU使用率，I / O使用率和内存使用率，如图1所示：

图1.影响性能的区域

根据您的系统资源，在您的调整决策中，CPU，内存和I / O之间总会有一些折衷。您将需要在这三个领域之间取得平衡，以进行调整以获得最佳性能。我们将讨论实现此目的的一些方法。

了解数据库工作负载对于有效配置数据库以获得最佳性能也至关重要。共有三种类型的数据库工作负载：

在线事务处理（OLTP），由许多复杂程度各异的事务组成，大多数情况下很小。 OLTP事务包括选择，插入，更新和删除，这些过程通常在几秒钟或几秒钟内完成
决策支持系统（DSS），通常是带有大型查询的仅选择事务，可访问大量数据
两者兼而有之。

不管数据库工作负载如何，常规调优技巧都是相同的，但是OLTP和DSS工作负载之间确实存在差异。本文仅介绍OLTP环境中的监视和调整技巧。

许多因素都会影响数据库服务器的性能，例如硬件系统设计，数据库对象设计，数据存储管理，应用程序设计等。本文重点介绍如何调整DB2 UDB配置参数，以及捕获和修复“不良查询”的步骤。

本文中的示例特定于在MicrosoftWindows®平台上运行的DB2 UDB企业服务器版8.2版。但是，提供的概念和信息与任何平台都有关。

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

在本文中，我们使用称为BANK的数据库和称为PERFORMER的简单独立Java程序提供性能示例。在本文的下载部分中，可以找到创建和填充BANK数据库以及运行PERFORMER所需的文件和说明。准备好环境最多需要五分钟。

为简单起见，BANK数据库仅包含两个表，即account和auditlog。 PERFORMER程序是JDBC™应用程序，它使用2型通用驱动程序并在调用后立即建立与BANK的10个连接。界面非常简单。只需指定测试的持续时间，然后单击“ 运行”即可 。程序执行时，将在10个连接中的每一个中一遍又一遍地执行带有随机参数的相同事务。调整数据库时，“ 完成的交易数”字段应从一项测试增加到另一项。这是您的主要目标。

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

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

在阅读本文时，我们鼓励您使用PERFORMER（在TEST环境中！）。

建立基线

调整数据库之前，应始终建立基线。对于这种特定情况，让我们跟踪内存消耗以及未调整数据库时已完成的事务数。要从干净的环境开始，请执行以下操作：

关闭所有打开的窗口。
打开DB2命令窗口，并将其移到桌面的底部中心。然后发出以下命令： db2stop force
打开Windows任务管理器，并将其放在桌面的右上角。选择性能选项卡。
在DB2命令窗口中，发出： db2start 。同时，在任务管理器中查看内存使用量增加了多少。启动实例会消耗一些内存，但不会占用太多内存。
从DB2命令窗口发出： db2 list applications 。您应该收到一条消息，指示数据库系统监视器未返回任何数据。
通过执行文件performer.bat启动PERFORMER程序，并在任务管理器中查看内存如何增加。如前所述，PERFORMER一旦被调用就建立10个连接。当针对数据库建立第一个连接时，将分配数据库内存。另外，每个连接都与一个DB2代理相关联，这也导致一些内存开销。将PERFORMER窗口移到左上角。
在DB2命令窗口中，发出： db2 list applications 。现在，您应该可以看到java.exe应用程序（PERFORMER）与BANK数据库的十个连接。

您的桌面应如图2所示。阅读本文时，请保持此窗口的排列。

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

要建立基线，请运行PERFORMER 10秒钟，五到八次，然后记下结果。您会注意到，无需进行任何调整，已完成的事务数就会从一次运行增加到另一次运行，但最终达到极限。这是正常的。在第一次运行期间，没有太多信息被缓存到内存中。因此，第一次运行要付出将某些页面存储到内存中的I / O价格。在调优练习中，短语“启动数据库”是指分配所有内存并将某些数据页带到内存的第一次运行。这些运行通常不包括在最终性能结果编号中。

至此，我们有了未调整数据库时已完成事务的基准。现在，您应该拍摄完整的DB2快照。但是，我们尚未讨论此主题。因此，在阅读整篇文章后，您可能想尝试一下，从头开始重复此练习。

DB2 UDB监视

数据库系统监视是调优数据库服务器的重要组成部分。监视数据的收集可以用作比较当前性能和过去性能的基准。这样可以更轻松，更快速地检测性能问题。监视数据还可以帮助您了解参数和应用程序更改的影响，并可以随着系统的增长分析趋势。

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

DB2 UDB提供了几种工具，可用于监视数据库服务器活动或分析SQL语句如何访问数据。每个都有不同的目的。表1显示了不同的监视工具。

表1. DB2 UDB监视工具概述

监控工具	监控信息
快照监控器	捕获特定时间点（拍摄快照的那一刻）的数据库活动状态的图片
事件监控	在发生特定的数据库事件（例如语句执行，事务完成或应用程序断开连接）时记录数据。 STATEMENT和DEADLOCK事件监视器通常在性能调整实践中使用。
SQL解释工具	捕获有关SQL语句的访问计划和环境的信息，即有关如何执行单个SQL语句以访问数据的信息
db2batch	监视SQL语句的性能特征和执行持续时间。它从平面文件或标准输入中读取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 switch命令的输出

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之类的工具进行格式化，以直观的方式呈现它。

第一次使用Visual Explain时，会自动创建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版本和发行版级别，以及运行解释工具的日期和时间
数据库上下文	优化程序在确定具有最低资源成本的访问计划时会考虑的配置参数，包括并行性，CPU速度，通信速度，缓冲池大小，排序堆大小，数据库堆大小，锁定列表大小，最大锁定列表，平均应用程序和可用的锁
包上下文	SQL类型（动态或静态），优化级别，隔离级别以及该语句使用的帧内并行度
原始陈述	应用程序调用SQL语句
优化语句	优化程序从原始语句转换SQL语句的重写版本，具有相同的查询结果，但允许最佳性能
访问计划	允许DB2 UDB访问数据以解析SQL语句的最便宜的路径
操作员说明	它显示了访问计划的每个阶段（操作员）正在发生的情况
访问计划中使用的对象	访问计划中使用的表和/或索引。

我们在以下各节中提供有关db2exfmt的更多示例。

调优DB2 UDB配置参数

DB2 UDB“开箱即用”参数值基于使用256 MB RAM和单个磁盘的系统。如果您有较大的系统，则需要修改这些参数以最佳利用系统资源。您可以使用Configuration Advisor来确定调整配置的良好起点，Configuration Advisor会根据您的系统资源来建议数据库参数值。要运行配置顾问程序，请使用autoconfigure命令，或通过在控制中心上调用它，方法是右键单击所需的数据库进行配置，然后选择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）和4096字节的页面大小（PAGESIZE）。在Windows上，IBMDEFAULTBP的缺省大小为250页，而在UNIX®平台上，缺省大小为1000页。当NPAGES的值为-1时，缓冲池大小由数据库配置中的BUFFPAGE参数确定。例如，
% db2 get db cfg for bank ... Buffer pool size (pages) (BUFFPAGE) = 250

监控方式
由于缓冲池旨在将页面保留在内存中，以便数据库服务器进行数据操作，而不是从磁盘读取页面，因此，衡量缓冲池有效性的一项重要指标是查看请求的页面在缓冲池中的出现频率。缓冲池命中率衡量了这种有效性，可以计算如下：
(1- ((Buffer pool data physical reads + Buffer pool index physical reads) / (Buffer pool data logical reads + Buffer pool index logical reads))) * 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内存页面的缓冲池的性能结果

测试运行完成后，按如下所示获取缓冲池快照：
% 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

单击“执行器”屏幕上的“重置”按钮，然后单击“运行”。使用更大的12000 4K页缓冲池，性能结果从每10秒43个已完成事务提高到66个已完成事务，如图4所示。

图4.一个缓冲池包含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的boot.ini中启用了3 GB开关，则为2 GB或3 GB；因此，您需要平衡缓冲池和其他数据库共享内存缓冲区的配置。在64位环境中，这不是问题。

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

DB2 UDB鼓励在缓冲池和磁盘之间读取和写入页面的异步I / O访问，以实现最佳性能。

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

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

配置中
可以使用NUM_IOSERVERS数据库配置参数来配置数据库的I / O服务器（预取器）的数量。为了充分利用系统中的所有I / O设备，使用时通常要比数据库所在的物理设备数量多一到两个。最好配置其他I / O服务器，因为与每个I / O服务器相关的开销最少，并且所有未使用的I / O服务器都将保持空闲状态。

NUM_IOCLEANERS数据库配置参数使您可以指定数据库的异步页面清除程序的数量。在设置此参数的值时，请考虑以下因素：

应用类型
如果它是不具有更新的仅查询数据库，则将此参数设置为零（0）。如果查询工作负载导致创建了许多TEMP表，则可能是个例外（您可以使用explain实用程序来确定）。如果对数据库运行事务，则将此参数设置为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与同步读取I / O的数量来验证预取活动。异步读取率的计算如下：
((Asynchronous pool data page reads + Asynchronous pool index page reads) / (Buffer pool data logical reads + Buffer pool index logical reads)) * 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参数不需要进一步的调整。

对于OLTP工作负载，CHNGPGS_THRESH的默认值通常为60％太高。介于20％和40％之间的值更好。例如，如果您有2 GB的缓冲池，则当达到60％的已更改页面时，将在触发页面清除程序时将1.2 GB（2 GB的60％）写入磁盘。发生这种情况时，这可能会导致系统整体速度变慢。通过将CHNGPGS_THRESH设置为较低的值（例如20％），页面清理器将被更频繁地触发，但是更少的数据将被写入磁盘，并且用户可能不会注意到这种减速。

排序

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

仅当INTRA_PARALLEL数据库管理器配置参数为ON或启用了集中器（即，MAX_CONNECTIONS大于MAX_COORDAGENTS）时，共享排序才可用。当需要有多个子代理从某种类别中进行馈送或获取时，通常使用它们。用于共享排序的内存是从数据库共享内存集中分配的。

当两个INTRA_PARALLEL参数都为OFF并且禁用了集中器时，所有排序都是私有的。不管INTRA_PARALLEL参数的值如何，装入和创建索引操作始终将私有排序用于索引键排序。用于私有排序的内存是从代理的私有内存中分配的。因此，私有排序只能由单个代理访问。

对于共享排序，SHEAPTHRES_SHR数据库配置参数是可一次用于排序的数据库共享内存总量的数据库范围的硬限制。当活动共享排序的共享内存总量达到此限制时，后续排序将因SQL0955而失败。如果SHEAPTHRES_SHR的值为零，则共享排序内存的阈值将等于SHEAPTHRES数据库管理器配置参数的值，该参数也用于表示排序内存。

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

此外，排序涉及两个步骤：

排序阶段
排序可以溢出或不溢出。如果排序后的数据不能完全容纳在排序堆中，排序堆是每次执行排序时都会分配的一块内存，则它将溢出到数据库拥有的临时表中。不会溢出的排序总是比那些更好的排序。
返回排序阶段的结果
回流可以是管道的，也可以是非管道的。如果排序后的信息可以直接返回而无需临时表来存储最终的排序后的数据列表，则这是管道排序。如果排序后的信息需要返回临时表，则它是非管道排序。管道排序始终比非管道排序执行得更好。

配置中
配置SHEAPTHRES：

通常，SHEAPTHRES应该设置为SORTHEAP的倍数
一般的经验法则是将SHEAPTHRES设置为至少10 * SORTHEAP。
对于在INTRA_PARALLEL为ON的情况下创建索引，请确保SHEAPTHRES> = d * SORTHEAP，其中d是SMP度。
对于LOAD，所有索引键排序在一个进程的专用内存空间中同时发生：SMP中的db2lrid，串行中的db2lfrm0。确保SHEAPTHRES> = n * SORTHEAP，其中n是要加载的表上的索引数。还请记住，在AIX上，LOAD中最多有250MB的私有虚拟内存用于索引键排序（这是一个内存瓶颈）。

配置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 sorts ratio = (Post threshold sorts / Total sorts) * 100%

如果“ 后阈值”排序比率的值很高，则应增加排序堆阈值（sheapthres），或通过SQL查询更改来调整应用程序以使用更少或更少的排序。
Piped sorts ratio = (Piped sorts accepted / Piped sorts requested) * 100%

如果管道排序比率的值较低，则应考虑增加排序堆阈值（sheapthres），以获得更好的排序性能。
Sort overflow ratio = (Sort overflows / Total sorts) * 100%

如果“ 排序溢出率”的值较高，则应增加排序堆（SORTHEAP）和/或排序堆阈值（sheapthres）。
Sorts per transaction = Total sorts / (Commit statements attempted + Rollback statements attempted)

如果每个事务有三个或三个以上的排序，而不是调整排序堆或排序堆阈值，则应通过动态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

您可以创建一个包含以下SQLSQL DDL文件order_by.ddl：
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

Upon each change, use snapshot monitoring to determine whether further sort tuning is required. If so, repeat the same steps.

Log buffer size

The log buffer acts as a staging area in memory to hold log records instead of having the DB2 UDB engine write each log record directly to disk.

The log records are written to disk when one of the following occurs:

A transaction commits or a group of transactions commit, as defined by the MINCOMMIT configuration parameter
The log buffer is full
As a result of some other internal database manager event.

Configuring
The log buffer size is defined by the LOGBUFSZ database parameter. Increase the size of the log buffer area if there is considerable read activity on a dedicated log disk, or there is high disk utilization. When increasing the value of the LOGBUFSZ parameter, you should also consider the DBHEAP parameter since the log buffer uses space from the database heap area.

Monitoring
You can use the database snapshot to determine whether the LOGBUFSZ database parameter is optimal by viewing the following snapshot elements:
% db2 get snapshot for database on <database_name> Log pages read = 0 Log pages written = 6721

Log pages read is the number of log pages read from disk by the logger, while Log pages written is the number of log pages written to disk by the logger. The ratio of the number of Log pages read to the number of Log pages written should be as small as possible. Ideally, there should be no Log pages read. If you see a large number of Log pages read, it indicates that the value of the LOGBUFSZ database parameter should be increased.

例子
The default size of the log buffer (LOGBUFSZ) below is normally too small for database systems with many update workloads.
% db2 get db cfg for bank Log buffer size (4KB) (LOGBUFSZ) = 8

The PERFORMER program doesn't have many updates. So you won't exercise the LOGBUFSZ tuning tips with PERFORMER here. However, you can still try it with your own application(s). If your application happens to have a high update workload, the database snapshot monitoring may reveal a large number of log pages read. If so, you can consider increasing the LOGBUFSZ database parameter. Typically, it should be increased to a minimum of 256 pages.

Number of commits to group

In an environment in which many short concurrent transactions occur, by default each COMMIT statement triggers one log buffer flush to disk. As a result, the logger process frequently writes small amounts of log data to disk, with additional delay caused by log I/O overhead. Commit grouping allows the writing of the log buffer data to disk until a minimum number of commits have been requested. This feature can result in improved performance by reducing the log I/O overhead.

This grouping of commits will only occur when the value of the MINCOMMIT parameter is greater than one, and when the number of applications connected to the database is greater than or equal to the value of this parameter. When commit grouping is being performed, application commit requests can be held until either one second has elapsed or the number of commit requests equals the value of the MINCOMMIT parameter.

Configuring
Increase the MINCOMMIT parameter from its default value if multiple read/write applications typically request concurrent database commits. This will result in more efficient logging file I/O as it will occur less frequently and write more log records each time it does occur.

You could also sample the number of transactions per second and adjust this parameter to accommodate the peak number of transactions per second (or some large percentage of it). Accommodating peak activity would minimize the overhead of writing log records during transaction intensive periods.

Monitoring
The database snapshot monitor can be used to determine the number of transactions performed per second, as follows:
% 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

The number of transactions per second:
((Commit statements attempted + Rollback statements attempted) / (Snapshot timestamp - Last reset timestamp))

例子
The MINCOMMIT parameter can have both positive and negative impact on database performance, and needs to be set correctly.
% 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

The number of transactions per second = (7374 + 1) / (19:33:14.650596 - 19:32:45.570089) = 254.13 transactions per second.

Considering that the PERFORMER program invokes the small number of 10 concurrent application connections only, while there are a large number of short transactions (that is, 254 transactions per second), the default value of MINCOMMIT parameter should not be changed; otherwise, those short transactions will be dramatically increased. Test it yourself!

Database query optimizer

DB2 UDB comes with a powerful cost-based query optimizer that determines the best strategy to access data. The DB2 UDB query optimizer always attempts to determine the cheapest way to perform a query against a database by rewriting the original query into an optimized form, generating alternative query execution plans, modeling I/O, CPU, memory, and communication usage of each alternative, and selecting the minimal cost access plan for execution.

Updating catalog statistics

The query optimizer uses the SYSSTAT catalog views in the database to retrieve the statistical information of the database objects and determine the best way to access the data. If current statistics are not available, the optimizer may choose an inefficient access plan based on inaccurate default statistics.

By default for a newly created database, none of the database object statistics is collected and stored in SYSSTAT catalog views until the runstats command is performed. If the catalog statistics have not been populated, columns like CARD, NPAGES, FPAGES, and so on, in the SYSSTAT catalog views, would have a value of -1. An example is shown in Listing 4:

Listing 4. Determining if RUNSTATS has been run

% 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

It is highly recommended that you use the runstats command to collect current statistics on tables and indexes, especially if significant update activity has occurred or new indexes have been created since the last time the runstats command was executed. This provides the optimizer with the most accurate information with which to determine the best access plan. 例如：
% db2 runstats on table <table_name> with distribution and detailed indexes all

where <table_name> is a fully qualified table name, that is, it includes the schema name.

Sometimes you may need to perform the runstats operation against all the tables within the database. The easiest way to do so is by using the reorgchk command:
% db2 reorgchk update statistics on table all

If you have trouble identifying whether your database contains up-to-date statistics on tables and indexes, you can issue the following command to verify the most recent time you performed the runstats operation:
% db2 "select name, stats_time from sysibm.systables"

If the runstats has not yet been run, you will see "-" for the stats_time column. Otherwise, it returns the time stamp of the last time that runstats was run.

We will test PERFORMER after running runstats in a section below.

Catching bad queries

An application can run hundreds of different SQL statements; if only one of them is incorrectly coded or not tuned to its best, the entire system's performance can be impacted. How can you catch these bad queries?

The dynamic SQL statement snapshot provides information about the dynamic SQL statements run by your application. Follow these steps to reset the monitors, run PERFORMER, and then obtain a dynamic SQL statement snapshot:
% db2 reset monitor all % performer (and click on the "Run" button) % db2 get snapshot for dynamic sql on bank

Listing 5 shows part of the dynamic SQL snapshot output.

Listing 5. The Dynamic SQL snapshot

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 (?,?,?,?,?)
 ...

We have extracted three sections of the dynamic SQL snapshot corresponding to three different queries:
SELECT NAME, BALANCE FROM ACCOUNT WHERE ACCT_ID =14680 SELECT NAME, BALANCE FROM ACCOUNT WHERE ACCT_ID =47030 INSERT INTO AUDITLOG VALUES (?,?,?,?,?)

Note the two SELECT statements are exactly the same except for the value of ACCT_ID. The PERFORMER program was purposely coded without a parameter marker (?) to show what you should not be doing! The two SELECT statements will be treated as different queries by the DB2 UDB optimizer, and therefore each query will be compiled separately incurring an extra overhead as shown by these fields:
Number of executions = 1 Number of compilations = 1

On the other hand, if you review the INSERT statement you can see it uses five parameter markers. The fields:
Number of executions = 73 Number of compilations = 1

show that only one compilation was required, and that the same query with different values is executed 73 times.

Next, let's capture the bad SQL statement. Using operating system commands, you can redirect the output of the snapshot command to a file as follows:
% db2 get snapshot for dynamic sql on bank > snap1.txt

You can then use operating system or editor commands to look for the field "Total execution time." Sort the results in descending order, and start analyzing the query that has the highest value. For example, in our Windows system we have a UNIX simulator installed; therefore, we can use the grep command as follows:
% grep -i 'Total execution' snap1.txt

This would provide a list as shown in Listing 6:

Listing 6. Looking for bad queries

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
...

Though we did not sort this list, we immediately can see the query with the highest execution time is 41.844554. Listing 7 shows the section in the Dynamic SQL snapshot output for this query.

Listing 7. Dynamic SQL snapshot section for a bad query

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 = ?

Analyzing this output, we notice that the query has executed 77 times, so the total execution of 41.844554 is for the 77 executions. Dividing 41.844554 by 77 we get that the execution per query is 0.5434358, so it is not as bad as it looks. Can the query be improved? 当然。 The number of rows read (7700000) versus the number of rows written (77) show that a table scan is likely happening. This is not necessarily the most expensive query, however. If we repeat the same procedure as before, we find that the query with this execution time:
Total execution time (sec.ms) = 1.034426

is actually the query that took the most time. The query in question is:
SELECT NAME, BALANCE FROM ACCOUNT WHERE ACCT_ID =47030

and the full section for that query in the snapshot was included earlier in Listing 5. The next section will show how this query can be fixed.

In Part 2 of this article series, we describe the use of table snapshot functions that can be used to obtain this same type of information but stored in tables that you can manipulate using SQL statements.

Understanding access plan

You've learned how to capture the access plan of a given query using the SQL explain utility (db2exfmt) in the DB2 UDB monitoring section. This section focuses on how to understand the access plan itself so that you can identify the root cause(s) of the slow query, and then fix the problem.

The example below introduces the access plan basics by analyzing the bad SQL statement we captured in the previous section.

Create a script file, select.ddl containing the following SELECT statement:
SELECT NAME, BALANCE from ACCOUNT WHERE ACCT_ID=47030;

You may execute the following steps in order to generate the access plan for the above SELECT statement:
% 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

Structure of the db2exfmt output

From the exfmt_runstats.out file, you can see that the output of db2exfmt shows a lot of valuable information about the database environment and the query. Some important sections are listed below.

The Database Context section lists the configuration parameters that the optimizer takes into account to determine the access plan with the least resource cost. This is shown in Listing 8.

Listing 8. The Database Context section of a db2exfmt output

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

The next major section provides package details, such as whether the SQL is dynamic or static, the optimization level, as well as the isolation level. More importantly you can see the section number, and where the query was issued from (QUERYTAG: CLP). This is quite useful when you match the access plan back to the event monitor outputs. It allows us to better track a particular query and the related events. Listing 9 shows this section.

Listing 9. The Package Context section of a db2exfmt output

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

Listing 10 shows shows the original query statement and its optimized version.

Listing 10. The Original and optimized sections of a db2exfmt output

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)

Listing 11 shows the access plan for the SELECT statement.

Listing 11. The Access Plan section of a db2exfmt output

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

The remaining two sections provide details on each operator of the access plan, and the tables and/or indexes used by the query.

Access plan operator
Listing 12 shows the basic component of an access plan.

Listing 12. The basic component of an access plan

cardinality
<access plan operator>
      ( # ) 
       cost
     I/O cost

The cardinality represents the estimated number of rows returned from the access plan operator. The access plan operator is either an action that must be performed on data, or the output from a table or an index. The cost represents the cumulative CPU cost of this and previous operations, and the I/O cost represents the cost of the operator with I/O subsystems.

The unit of cost is the timeron . A timeron does not directly equate to any actual elapsed time, but gives a rough relative estimate of the resources (cost) required by the database manager.

Here are a few examples of the access plan operators you may see in this article:

RETURN: Represents the return of data from the query to the user.
FETCH: Fetches columns from a table using a specific record identifier.
TBSCAN: Retrieves rows by reading all required data directly from the data pages.
IXSCAN: Scans an index of a table with optional start/stop conditions, producing an ordered stream of rows.

Table scan versus index scan
The optimizer normally chooses a table scan if no appropriate index has been created or if an index scan would be more costly. An index scan might be more costly when the table is small and the index-clustering ratio is low, or the query requires most of the table rows.

The earlier access plan shows that the total cost of the SELECT statement is 3420.41 timerons , which is from the table scan operator (TBSCAN) against the ACCOUNT table. Since only one row is expected in the result set from this SELECT statement, the table scan here is considered an expensive operation. Alternatively, an index scan can be attempted in order to achieve better performance.

Prior to your index creation effort, you may run PERFORMER to get a performance baseline for later comparison. In our case the number of completed transaction using a 12,000 4k page bufferpool was 66 in 10 seconds as shown in Figure 4.

Create an index on the ACCOUNT table as follows:
% db2 connect to bank % db2 describe table account % db2 "create index acct_id_inx on account (acct_id)"

Update the catalog statistics with the runstats command so that the optimizer can take this newly created index into account:
% db2 runstats on table cxwang.account with distribution and detailed indexes all

To evaluate the new access plan after the index creation on the ACCOUNT table issue:
% 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

Listing 13 shows the new access plan.

Listing 13. Access Plan after an index was created

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

By adding the index on the ACCT_ID_INX column, the new access plan shows a significant cost reduction from 3420.41 to 38.6219 timerons .

Figure 5 shows the significant performance improvement of PERFORMER after the index creation. From 66 transactions to 2051!:

Figure 5. Performance results with index scan on the ACCOUNT table

摘要

This article introduced DB2 UDB performance monitoring and tuning fundamentals using a sample Java program (PERFORMER). You can apply the simple step-by-step performance tuning examples to improve the performance on your own DB2 UDB database system. Moreover, you had the opportunity to learn how to evaluate and analyze access plans and fix "bad queries." In Part 2 of this article series, we will use a more complete database scenario that builds on top of the one used in this article. We will discuss other performance monitoring and tuning techniques, and the use of the Design Advisor, the activity monitor, table snapshot functions, and other tools. Part 2 also covers OLTP, DSS, and mixed workloads.

翻译自: https://www.ibm.com/developerworks/data/library/techarticle/dm-0508chong/index.html