DB2调优摘要

2022-06-11 06:15:37

感谢原博主

http://yishueitian326.blog.163.com/blog/static/2858637520106695733719/

性能是关系到随需应变型应用程序成功与否的关键。当那些应用程序使用 IBM? DB2 Universal Database? 作为数据存储时，至关重要的是，从一开始就应该知道有关如何在 DB2 UDB 上取得尽可能好的性能的基础知识。在本文中，我将给出关于调优 DB2 UDB V8 系统的一些比较深入的建议。

我们将谈论这一过程中自始至终存在的性能问题。您可以遵循从创建一个新数据库到运行应用程序这之间的流程。通过本文可以看到如何使用 DB2 自动配置实用程序来初始配置数据库管理器和数据库环境。接着，我将讨论创建缓冲池、表空间、表和索引的最佳实践。另外，您可能还想改变一些重要配置参数的初始值，以便更好地支持应用程序，因此我们还将简介这些配置参数。

我们将论述基于监视器（monitor）细节输出的调优，从而展示如何使用快照监视（snapshot monitoring）帮助调优 SQL、缓冲池和各种不同的数据库管理器以及数据库配置参数。接着，我们将进一步研究应用程序发送给 DB2 的 SQL。通过使用 Explain，可以生成 SQL 采用的访问计划（access plan），并寻找可以进一步优化的机会。我们将考察 Design Advisor 这样一个工具，它可以根据所提供的 SQL 负载推荐出新的索引，或者评估现有的索引。最后，我们还将讨论一些 DB2 SQL 选项。

此外，持续（on-going）维护对于维持最佳性能非常重要。所以我们将讨论一些可以帮助我们进行持续维护的实用程序。对于那些正使用 DB2 ESE Database Partitioning Feature (DPF) 的读者，我会用一节的篇幅谈论为使数据库高效运行而应该考虑的一些问题。有时候可能会存在某种外在的瓶颈（来自 DB2）而使您无法达到性能目标，本文列出了一些常见的瓶颈，以及用于监视这些瓶颈的实用程序。最后，本文列出了一些有价值的 IBM 资源，以帮助您发现有价值的 DB2 信息。

本文是为那些在 DB2 数据库管理方面有中级技能的人而写的。

读前须知

在开始性能调优过程之前，应确保您已经应用了最新的 DB2 修订包（fix pack）。修订包常常会带来性能的提高。我们要使用 DB2 FixPak 4 作为本文的基础。如果您使用的是 FP4 之前的版本，那么这种环境可能不能提供这里讨论的所有选项。

在进行调优时，最好是有一个关于数据库使用（即应用程序运行在 DB2 上的工作负载）的可再现场景，这样就可以利用这种可再现场景来量身定制调优效果。例如，如果工作负载在不同的运行期间所经历的时间上有 10% 的变化量，那么就很难知道调优的真正效果如何。此外，如果在两次运行中各自的工作负载不一样，也就难于衡量数据库管理器和数据库配置参数所发生的变化。

坚持跟踪所有的变化。这样有助于开发调优脚本或者建议，以作为供其他 DBA 参考的历史，同时也有助于防止遵循不良的变化。

在大多数小节的最后，都有一些指向 DB2 v8 HTML Documentation 中相关小节的链接。在线文档可以在 http://publib.boulder.ibm.com/infocenter/db2help/index.jsp上找到。

回页首

“十大”性能增强推动器

做了下面十件事情，您就几乎可以使数据库获得最佳性能。通常您会发现，通过大约 10% 的配置变化，就可以达到最佳性能的 90%。我将在下面适当的小节（在圆括号中标出）中详细讨论其中的每一条：

确保有足够的磁盘（每个 CPU 有 6-10 个磁盘才是一个好的开端）。每个表空间的容器应该跨越所有可用的磁盘。有些表空间，例如 SYSCATSPACE 以及那些表数量不多的表空间，不需要展开到所有磁盘上，而那些具有大型用户或临时表的表空间则应该跨越所有磁盘。（表空间）。
缓冲池应该占用可用内存的大约 75% （OLTP）或 50% （OLAP）（缓冲池）。
应该对所有表执行 runstats，包括系统编目表（ Runstats）。
使用 Design Advisor 为 SQL 工作负载推荐索引和检查索引（ Design Advisor）。
使用 Configuration Advisor 为应用程序环境配置数据库管理器和数据库（Configuration Advisor）。
日志记录应该在一个独立的高速驱动器上进行，该驱动器由 NEWLOGPATH 数据库配置参数指定（ Experimenting）。
通过频繁的提交可以增加并发性（ SQL 语句调优）。
应该增加 SORTHEAP，以避免排序溢出（ DBM 和 DB 配置）。
对于系统编目表空间和临时表空间，表空间类型应该为 SMS，而对于其他表空间，表空间类型应为 DMS（ raw device 或者是文件）。运行 db2empfa，以便支持用于 SMS 表空间的多页（multi-page ）文件的空间分配。这将允许 SMS 表空间一次增长一个区段（Extend），而不是一页，从而可以加快那些大型的插入操作和溢出磁盘的排序操作（表空间）。
对于重复的语句，使用参数标记（ SQL 语句调优）。

创建数据库

创建一个数据库时，系统会缺省地创建 3 个系统管理存储（System Managed Storage，SMS）表空间（SYSCATSPACE、TEMPSPACE1 和 USERSPACE），以及一个 4 MB 的缓冲池（IBMDEFAULTBP），这些表空间和缓冲池的页面大小都是 4 KB 。根据下面的建议，先删除 TEMPSPACE1 和 USERSPACE 然后再重新创建它们，通常这是一种可取的做法。几乎在所有情况下， SYSCATSPACE 都不需要再作进一步的优化，但是如果将其容器展开到几个磁盘上，性能上可能会有少量提升。（稍后讨论）。

在创建数据库时，您可以利用自动配置选项来根据环境对数据库进行最初的配置。当应用程序以编程方式创建 DB2 数据库时，这样做很方便，因为可以将这些选项从应用程序提供给 DB2。在自动配置数据库时不得不用到的另一个选项是更强大的 Configuration Advisor GUI，它不但可以配置数据库，而且还可以配置实例。不过，要使用 Configuration Advisor，数据库必须首先存在。我们将在随后的小节中讨论 Configuration Advisor。

在清单 1中，我们使用 CREATE DATABASE 命令的自动配置选项在 Windows 中创建了一个数据库，该数据库有一个跨越两个可用磁盘的 SYSCATSPACE。

清单 1. 使用自动配置选项创建数据库

create database prod1 catalog tablespace            managed by system using (‘c:\\proddb\\cattbs\\01‘,‘d:\\proddb\\cattbs\\02‘)            extentsize 16 prefetchsize 32            autocon图 using mem_percent 50 workload_type simple num_stmts 10            tpm 20 admin_priority performance num_local_apps 2 num_remote_apps            200 isolation CS bp_resizeable yes apply db and dbm

表 1显示了有效的自动配置输入关键字以及值：

表 1. 自动配置选项

关键字	有效值	缺省值	描述
mem_percent	1-100	25	分配给数据库的物理存储空间的百分比。如果本服务器（不包括操作系统）上运行有其他应用程序，那么将其设为小于 100 的某个值
workload_type	simple, mixed, complex	mixed	simple 型工作负载倾向于 I/O 密集型，并且大多数是事务处理（OLTP），而 complex 型工作负载则倾向于 CPU 密集型，并且大多数是查询（OLAP/DSS）
num_stmts	1-1000000	25	每个工作单元包含的语句条数
tpm	1-200000	60	每分钟的事务数。
admin_priority	performance, recovery, both	both	优化以获得更好性能（每分钟更多的事务数）或更好的回复时间
num_local_apps	0-5000	0	连接的本地应用程序的数目
num_remote_apps	0-5000	100	连接的远程应用程序的数目
isolation	RR, RS, CS, UR	RR	连接到该数据库的应用程序的隔离级别（Repeatable Read、Read Stability、Cursor Stability 和 Uncommitted Read）。
bp_resizeable	yes, no	yes	是否可以在线更改缓冲池大小

Configuration Advisor

如果您在创建数据库的时候已经使用了自动配置，那么这一步就不是很重要了。Configuration Advisor 是一个 GUI 工具，它允许根据您针对一系列问题给出的回答自动配置数据库和实例。通过使用这种工具，常常可以取得相当可观的性能提升。这个工具可以从 Control Center 中通过右键单击数据库并选择 "Configuration Advisor" 来打开。当您回答完所有问题后，就可以生成结果，还可以选择应用结果。图 1展示了结果页面的屏幕快照：

图 1. Configuration Advisor Results 屏幕
DB2调优摘要

回页首

创建缓冲池

恰当地定义缓冲池是拥有一个运行良好的系统的关键之一。对于 32 位操作系统，知道共享存储器的界限十分重要，因为这种界限将限制数据库的缓冲池（即数据库的全局存储器），使其不能超出以下界限（64 位系统没有这样的界限）：

AIX - 1.75 GB
Linux - 1.75 GB
Sun - 3.35 GB
HP-UX - approximately 800 MB
Windows - 2-3 GB （在 NT/2000 上的 boot.ini 中使用的是 ‘ 3GB‘ switch）

用下面的公式计算近似的数据库全局存储器的使用：

清单 2. 计算全局存储器的使用（共享存储器）

buffer pools + dbheap + util_heap_sz + pkgcachesz + aslheapsz + locklist + approx 10% overhead

如果启用了 INTRA_PARALLEL，那么将 sheapthres_shr 的值加到总和中。

确定有多少缓冲池

对于数据库中一个表空间所使用的每一种页面大小，都需要至少一个缓冲池。通常，缺省的 IBMDEFAULTBP 缓冲池是留给系统编目的。为处理表空间的不同页面大小和行为，须创建新的缓冲池。

对于初学者，一开始为每种页面大小使用一个缓冲池，对于 OLAP/DSS 类型的工作负载更是如此。DB2 在其缓冲池的自我调优方面十分擅长，并且会将经常被访问的行放入内存，因此一个缓冲池就足够了。（这一选择也避免了管理多个缓冲池的复杂性。）

如果时间允许，并且需要进行改进，那么您可能希望使用多个缓冲池。其思想是将访问最频繁的行放入一个缓冲池中。在那些随机访问或者很少访问的表之间共享一个缓冲池可能会给缓冲池带来“污染”，因为有时候要为一个本来可能不会再去访问的行消耗空间，甚至可能将经常访问的行挤出到磁盘上。如果将索引保留在它们自己的缓冲池中，那么在索引使用频繁的时候（例如，索引扫描）还可以显著地提高性能。

这与我们对表空间的讨论是紧密联系的，因为要根据表空间中表的行为来分配缓冲池。如果采用多缓冲池的方法，对于初学者来说使用 4 个缓冲池比较合适：

一个中等大小的缓冲池，用于临时表空间。
一个大型的缓冲池，用于索引表空间。
一个大型的缓冲池，用于那些包含经常要访问的表的表空间。
一个小型的缓冲池，用于那些包含访问不多的表、随机访问的表或顺序访问的表的表空间。

对于 DMS 只包含 LOB 数据的表空间，可以为其分配任何缓冲池，因为 LOB 不占用缓冲池空间。

确定为缓冲池分配的内存

千万不要为缓冲池分配多于所能提供的内存，否则就会招致代价不菲的 OS 内存分页（memory paging）。通常来讲，如果没有进行监控，要想知道一开始为每个缓冲池分配多少内存是十分困难的。

对于 OLTP 类型的工作负载，一开始将 75% 的可用内存分配给缓冲池比较合适。

对于 OLAP/DSS，经验法则告诉我们，应该将 50% 的可用内存分配给一个缓冲池（假设只有一种页面大小），而将剩下的 50% 分配给 SORTHEAP。

使用基于块（block-based）的缓冲池

倚重于预取技术的 OLAP 查询可以得益于基于块的缓冲池。缺省情况下，所有缓冲池都是基于页的，这意味着预取操作将把磁盘上相邻的页放入到不相邻的内存中。而如果采用基于块的缓冲池，则 DB2 将使用块 I/O 一次将多个页读入缓冲池中，这样可以显著提高顺序预取的性能。

一个基于块的缓冲池由标准页区和一个块区同时组成。CREATE 和 altER BUFFERPOOL SQL 语句的 NUMBLOCKPAGES 参数用于定义块内存的大小，而 BLOCKSIZE 参数则指定每个块的大小，即在一次块 I/O 中从一个磁盘读取的页的数量。

共享相同区段大小的表空间应该成为一个特定的基于块的缓冲池的专门用户。将 BLOCKSIZE 设置为等于正在使用该缓冲池的表空间的 EXTENT SIZE。

确定分配多少内存给缓冲区内的块区要更为复杂一些。如果要碰到大量的顺序预取操作，那么您很可能会想要更多基于块的缓冲池。NUMBLOCKPAGES 应该是 BLOCKSIZE 的倍数，并且不能大于缓冲池页面数量的 98%。先将它设小一点（不大于缓冲池总共大小的 15% 或刚好 15%）。在后面还可以根据快照监视（snapshot monitoring）对其进行调整。

DB2 v8 Documentation:

Concepts ==> Administration ==> Database objects ==> Buffer Pool Management
Reference ==> SQL ==> SQL Statements ==> CREATE BUFFERPOOL
Reference ==> SQL ==> SQL Statements ==> altER BUFFERPOOL
Concepts ==> Administration ==> Performance tuning ==> Operational performance ==> Memory-use organization

回页首

创建表空间

既然要为表空间分配缓冲池，关于缓冲池的上一节就跟涉及表空间的性能问题十分相关。使用 DB2 Control Center 是创建和配置表空间的最容易的方法，也是我们推荐的方法（右键单击数据库的 Table Spaces文件夹并选择 Create...）。

确定要创建的表空间的类型（DMS 或 SMS）

对于系统临时表空间和系统编目表空间，应该使用 System Managed Storage(SMS)，因为它允许表空间动态地增长和收缩。如果有大量临时表要刷新到磁盘上（或者是没有足够的排序空间，或者是显式地创建临时表），则 DMS 会更有效一些。当使用 SMS 时，应该运行实用程序 ‘db2empfa‘，这个实用程序将支持多页文件分配，从而一次一个区段地增长表空间，而不是一次一页地增长表空间。

对于所有其他的表空间，应该使用 Database Managed Storage(DMS)。DMS 允许一个表跨越多个表空间（索引、用户数据和 LOB），这样就减少了在预取和更新操作时索引、用户和 LOB 数据之间的争用，从而缩短了数据访问的时间。通过使用 DMS raw 甚至还可以挤出额外的 5-10% 的性能提升。

确定页面大小

为了创建一个表，必须有一个表空间，其页面大小应足以容纳一行。您可以选择使用 4、8、16 或 32 KB 这几种页面大小。有时候必须使用较大的页面大小，以回避某些数据库管理器的限制。例如，表空间的最大尺寸与表空间的页面大小成比例。如果使用 4K 的页面大小，那么表空间的大小（每个分区）最大是 64 GB，如果使用 32K 的页面大小，那么最大是 512 GB。

对于执行随机更新操作的 OLTP 应用程序，采用较小的页面大小更为可取，因为这样消耗的缓冲池中的空间更少。

对于要一次访问大量连续行的 OLAP 应用程序，通常使用较大页面大小效果会更好些，因为这样可以减少在读取特定数量的行时发出的 I/O 请求的数量。较大的页面大小还允许您减少索引中的层数，因为在一页中可以保留更多的行指针。然而，也有例外情况。如果行长度小于页面大小的 255 分之 1，则每一页中都将存在浪费的空间，因为每页最多只能有 255 行（对于索引数据页不适用）。在这种情况下，采用较小的页面大小或许更合适一些。

例如，如果要使用 32K 的页面大小来存储平均大小为 100 字节的行，则一个 32 KB 的页只能存储 100 * 255 = 25500 byte （24.9 KB）。这意味着每 32 KB 中就有大约 7 KB 要浪费掉。

确定表空间的数量

与缓冲池一样，一开始应该为每种页面大小使用一个缓冲池。对于所使用的每种页面大小，必须存在一个具有匹配页面大小的系统临时表空间（以支持排序和重组）。然后将所有享用匹配页面大小的表空间指派给具有相同页面大小的缓冲池。

如果您还关心性能问题，并且有时间投入，那么可以使用 DMS 表空间，并且根据使用情况来组织表。另外，还要遵循前面给出的关于使用多个缓冲池的建议。

对于每种页面大小，创建一个：

系统临时表空间。
用于索引的常规表空间。
用于频繁访问的表的常规表空间。
用于访问不多的表、随机访问的表以及顺序访问的表的常规表空间。
用于 LOB 数据的大型表空间。

容器布局

一开始最好是对于每个 CPU 分配 6-10 个磁盘给表空间。每个表空间应该跨越多个磁盘，也就是说，在每个可用磁盘上有一个（且不多于一个）容器。

有多少个表空间，就应该在每个磁盘上创建相同数量的逻辑卷（UNIX）。这样一来，每个表空间在每个磁盘上都有自己的逻辑卷（logical volume），用以放置容器。如果不是使用 raw device，那么就需要在每个逻辑卷内创建一个文件系统。

磁盘阵列和存储子系统

对于大型磁盘系统，应该使用单个容器。此外，还需要为表空间设置 DB2 Profile Registry 变量 DB2_PARALLEL_IO。这一点放在概要注册表一节中讨论。

区段大小

Extent Size 指定在跳到下一个容器之前，可以写入到一个容器中的 PAGESIZE 页面的数量，这个参数是在创建表空间时定义的（之后不能轻易修改）。处理较小的表时，使用较小的区段效率会更高一些。

下面的经验法则是建立在表空间中每个表的平均大小的基础上的：

如果小于 25 MB，Extent Size 为 8
如果介于 25 到 250 MB 之间，则 Extent Size 为 16
如果介于 250 MB 到 2 GB 之间，则 Extent Size 为 32
如果大于 2 GB，则 Extent Size 为 64

对于 OLAP 数据库和大部分都要扫描（仅限于查询）的表，或者增长速度很快的表，应使用较大的值。

如果表空间驻留在一个磁盘阵列上，则应将区段大小设置成条纹大小（也就是说，写入到阵列中一个磁盘上的数据）。

预取大小

通过使用 altER TABLESPACE 可以轻易地修改预取大小。最优设置差不多是这样的：

 Prefetch Size = (# Containers of the table space on different physical disks) * Extent Size

如果表空间驻留在一个磁盘阵列上，则设置如下：

  PREFETCH SIZE = EXTENT SIZE * (# of non-parity disks in array）。

DB2 v8 Documentation:

Concepts ==> Administration ==> Database design ==> Physical ==> Table Space Design
Reference ==> SQL ==> SQL Statements ==> CREATE TABLESPACE
Reference ==> SQL ==> SQL Statements ==> altER TABLESPACE

回页首

创建表

多维群集（Multidimensional clustering ，MDC）

MDC 提供了数据在多个维上的灵活的、连续的和自动的多维群集。它提升了查询的性能，并且减少了在插入、更新和删除期间对 REORG 和索引维护的需要。

多维群集从物理上把表数据同时沿着多个维群集起来，这与使用表上的多个独立的群集的索引类似。MDC 通常用于帮助提高对大型表的复杂查询的性能。这里不需要使用 REORG 来重新群集索引，因为 MDC 会自动地、动态地维护每个维上的群集。

对于一个 MDC，最合适的是那些具有范围、相等和连接谓词的访问多行的查询。千万不要使用具有惟一性的列作为一个维，因为这样会导致一个表不必要地变大。如果具有每种维值组合（即单元）的行不是很多，应避免使用太多的维。为获得最佳的性能，那么至少需要有足够的行来填满每个单元的块，也就是该表所在表空间的区段大小。

DB2 v8 Documentation:

Concepts ==> Administration ==> Database design ==> Logical ==> Multidimensional clustering (MDC)
Concepts ==> Administration ==> Database objects ==> Tables ==> Multidimensional clustering (MDC) tables

物化查询表（MQT）

MQT 可用于提升使用 GROUP BY、GROUPING、RANK 或 ROLLUP OLAP 函数的查询的性能。MQT 的使用对用户来说是透明的，DB2 选择何时使用 MQT 来达到优化的目的。DB2 使用 MQT 在内部维护被查询的分组的总结结果，这样用户就可以直接访问 DB2 维护的分组，而不必去读动辄数 GB 的数据来寻找答案。这些 MQT 还可以在分区间复制，以避免这种信息在分区间的散播，从而帮助提升合并连接（collocated jion）的性能。

CREATE TABLE 选项

对于 30 字节或更少字节的列，应避免使用 VARCHAR 数据类型，因为这种情况下，VARCHAR 类型通常会浪费空间，所以建议使用 CHAR 类型。如果数据量很大，那么空间的浪费往往会对查询时间造成影响。

当使用 IDENTITY或 SEQUENCE时，应至少使用缺省的大小为 20 的缓存（除非编号中的间隔很有干系）。这样一来，就不必在每次需要的时候请求 DBM 生成一个数字，同时也避免了在生成数字时要做的日志记录。

当一个表使用很多空值和系统缺省值时， VALUE COMPRESSION和 COMPRESS SYSTEM DEFAULT可以节省磁盘空间。系统缺省值是指在没有指定特定的值时，为某个数据类型而使用的缺省值。如果量很大，这样还可以帮助缩短查询时间。如果插入或者更新一个值，压缩的列只会招致很少的开销。

ALTER TABLE 选项

对于有大量插入操作的表，使用 APPEND ON以避免在插入过程中搜索空闲空间，而只需将行附加在表的最后。如果要依赖于处于某种特殊顺序中的表，并且无法承受执行 REORG 的开销，那么应避免使用 APPEND ON。

对于只读的表或者独占访问的表，将 LOCKSIZE设置为 TABLE。这样就避免了为行加锁时所费的时间，并且减少了所需的 LOCKLIST 数量。

使用 PCTFREE来维护空闲空间，以助将来的 INSERT、LOAD 和 REORG 一臂之力。PCTFREE 的缺省值是 10；对于具有群集索引和插入量很大的表，可以尝试使用 20-35。如果使用 APPEND ON，则将 PCTFREE 设置为 0。

使用 VOLATILE来鼓励索引扫描。易变（volatile）表明表的基数可以在很大的范围内显著变化，从一个很大的数一直到空。这样就促使优化器不管表的统计数字如何，都使用索引扫描，而不是使用表扫描。不过，只有在索引包含所有被引用的列，或者索引可以在索引扫描时应用谓词的情况下，上述情况才会出现。

使用 NOT LOGGED INITIALLY将事务执行期间（也就是直到 COMMIT）的日志记录关闭掉。

VALUE COMPRESSION和 COMPRESS SYSTEM DEFAULT还可以用在 altER TABLE 命令中。

DB2 v8 Documentation:

Reference ==> SQL ==> SQL Statements ==> CREATE TABLE
Reference ==> SQL ==> SQL Statements ==> altER TABLE

创建索引

由于有了 Design Advisor，设计索引的负担已经消除。Design Advisor 用于为特定的 SQL 工作负载（即一组 SQL 语句）推荐和评估索引，很快我们就将讨论这个工具。

下面仍然是一些您应该知道的跟索引有关的问题：

当要在一个合理的时间内结束查询时，应避免添加索引，因为索引会降慢更新操作的速度并消耗额外的空间。有时候还可能存在覆盖好几个查询的大型索引。
基数较大的列很适合用来做索引。
考虑到管理上的开销，应避免在索引中使用多于 5 个的列。
对于多列索引，将查询中引用最多的列放在定义的前面。
避免添加与已有的索引相似的索引。因为这样会给优化器带来更多的工作，并且会降慢更新操作的速度。相反，我们应该修改已有的索引，使其包含附加的列。例如，假设在一个表的 (c1,c2) 上有一个索引 i1。您注意到查询中使用了 "where c2=?"，于是又创建一个 (c2) 上的索引 i2。但是这个相似的索引没有添加任何东西，它只是 i1 的冗余，而现在反而成了额外的开销。
如果表是只读的，并且包含很多的行，那么可以尝试定义一个索引，通过 CREATE INDEX 中的 INCLUDE 子句使该索引包含查询中引用的所有列（被 INCLUDE 子句包含的列并不是索引的一部分，而只是作为索引页的一部分来存储，以避免附加的数据 FETCHES）。

群集索引

我们可以创建群集索引来为表中的行排序，并且是按照所需结果集的物理顺序来排序。群集索引可以用 CREATE INDEX 语句的 CLUSTER 选项来创建。为获得最佳性能，应该在那些小型的数据类型（比如整型和 char(10)）、具有惟一性的列以及在范围搜索中经常要用到的列上创建索引。

群集索引允许对数据页采用更线性的访问模式，允许更有效的预取，并且有助于避免排序。这意味着插入操作要花更多的时间，但是查询操作会更快。当使用群集索引时，应考虑将数据页和索引页上的空闲空间增加到大约 15-35（而不是 PCTFREE 的缺省值 10），以允许大容量的插入。对于会受到大量插入操作的表，考虑使用单维的 MDC 表（或许是使用像 idcolumn/1000 或 INT(date)/100 这样的生成列）。这将导致对数据（在维上）的块索引，而不是按行索引。这样产生的索引会更小一些，并且在插入期间的日志内容也大大减少。

CREATE INDEX 选项

对于只读表上的索引，使 PCTFREE为 0，对于其他索引使 PCTFREE 为 10，以提供可用的空间，从而加快插入操作的速度。此外，对于有群集索引的表，这个值应该更大一些，以确保群集索引不会被分成太多的碎片。如果存在大量的插入操作，那么使用 15 ‘ 35 之间的值或许更合适一些。

使用 ALLOW REVERSE SCANS以便可以对一个索引进行双向（bi-directionally）扫描，也就是说，可以对按升序排列的结果集和按降序排列的结果集进行更快速的检索。这样做还没有负面的性能影响，因为在为这个特性提供支持的过程中，并没有在内部改变索引的结构。

可以使用 INCLUDE将其他没有被索引的列包括到索引页中来，以促进纯索引访问，并避免了从数据页取数据。

可以使用 UNIQUE 列来有效地实施一个列或一组列的惟一性。

TYPE-2 INDEXES可以大大减少 next-key 锁，允许索引列大于缺省的 255 字节，允许在线 REORG 和 RUNSTATS，并且支持新的多维群集功能。在 v8 中，所有新的索引都是以 type-2 类型创建的，只有已经在表上定义了（迁移前） type-1 索引的时候才是例外。可以使用 REORG INDEXES 将 type-1 索引转换为 type-2 索引。

DB2 v8 Documentation:

Concepts ==> Administration ==> Performance tuning ==> Operational performance ==> Table and index management ==> Index planning
Concepts ==> Administration ==> Performance tuning ==> Operational performance ==> Table and index management ==> Performance tips for indexes
Reference ==> SQL ==> SQL Statements ==> CREATE INDEX

回页首

概要注册表配置

DB2 概要注册表变量通常要影响优化器和 DB2 引擎本身的行为。虽然概要注册表变量有很多，但是其中的大部分都有其非常特定的用途，因而在大部分的 DB2 环境中都不会用到。下面是一些常用的概要注册表变量。

表 2列出了用于概要注册表一些基本管理命令：

表 2. 概要注册表管理

命令	描述
db2set -all	列出所有当前设置的 DB2 注册表变量
db2set -g \| -ivariable=value	设置指定的 DB2 注册表变量，使其或者处于全局（-g）级，或者处于实例（-i）级

注意：在变量和值之间不要有空格，否则变量又会重新被设置成缺省值。

DB2_PARALLEL_IO
这将帮助促使对驻留在磁盘阵列上的任何表空间采用并行访问。如果所有表空间都在磁盘阵列上，则将该变量设置成等于 *。如果只有一些表空间在磁盘阵列上，则使用 "db2 list tablespaces" 检索这些表空间的 ID，并将该变量设置成这些 ID（使用逗号将各 ID 分隔开）。为获得最佳性能，应确保表空间的预取大小明显大于它的区段大小。

DB2_EVALUNCOMMITTED
缺省值是 OFF，如果将其改为 ON，则会将锁操作推迟到谓词演算。启用这个变量对于减少从 Oracle 移植过来的应用程序中存在的锁争用十分有用。

DB2_SKIPDELETED
缺省值是 OFF，如果将其改为 ON，则允许使用 CS 或 RS 的语句略过索引中被删除的键以及表中被删除的行。同样，启用这个变量对于减少从 Oracle 移植过来的应用程序中存在的锁争用十分有用。

DB2_HASH_JOIN
缺省值是 Enabled。如果禁用 DB2_HASH_JOIN（NO），则 OLTP 可能受益。

(AIX): DB2_FORCE_FCM_BP
缺省值是 NO。如果使用了 DB2 的 Database Partitioning Feature (DPF) 功能，并且有多个逻辑分区，那么将该变量设置为 YES 可以改善分区间的通信，代价是可供缓冲池使用的共享内存段要少掉一个。如果没有使用数据库分区功能，则应使用 NO 值。

(AIX 4.3) DB2_MMAP_READ 和 DB2_MMAP_WRITE
缺省情况下两者都处于启用状态的。如果使用 AIX 4.3，32 位的 DB2，并且内存会限制增加缓冲池的大小，那么应将此变量设置成 OFF，以便多释放一个内存段。这样大约可以释放 256 MB 的共享内存（这样就可以将其中一部分用于缓冲池）。可以进行一些测试，以确信这一更改的确提升了性能，因为有时候对磁盘使用内存映射的读和写比起增加缓冲池大小来可以获得更好的性能，虽然这种做法并不常见。

DB2 v8 Documentation:

Reference ==> Registry and environment variables

回页首

通过配置避免运行时错误

应用程序开始运行的时候，通常会暴露出与某些配置参数有关的问题。如果在应用程序运行期间没有收到任何错误或警告信息，那么就是安全的。如果收到了这样的信息，那么请参阅本文后面对数据库管理器和数据库配置参数管理的讨论。如果没有足够的内存来处理 SQL，下面的一些配置参数就会出问题：

MON_HEAP_SZ (DBM)
这是为数据库系统监视器（system monitor）数据分配的内存数量。当执行诸如快照监视或激活一个事件监视器之类的数据库监控活动时，就要从监视器堆中分配内存。如果没有足够的可用内存，并且 DB2 返回一个错误，则可以试着将这个值设为 256。如果还是遇到错误，一次一次地增加 256，直到错误消失。

QUERY_HEAP_SZ (DBM)
这是为了将每个查询存储到代理的私有内存时可以分配的最大内存量。查询堆还可用于为块游标（blocking cursor）提供内存分配。查询堆的大小必须大于或等于 ASLHEAPSZ。如果收到 DB2 返回的一个错误，表明性能可能不是最优，而处于最低状态，那么可将此参数设为至少大于 ASLHEAPSZ 的五倍，这样就允许查询大于 ASLHEAPSZ，并且为 3 个或 4 个并发的块游标足够的内存。

MAXAPPLS (DB)
该参数指定可以连接（包括本地连接和远程连接）到一个数据库的并发的应用程序的最大数目。在绝对最小值情况下，将此参数设置为 >= (用户连接的数量）。要了解详细信息，请参阅本文后面对 MAXAGENTS的讨论。

STMTHEAP (DB)
语句堆用于在 SQL 语句的编译期间作为编译器的工作区。对于每一条要处理的 SQL 语句，都要从该区域分配和释放空间。如果收到警告信息或错误信息，那么可以按 256 逐次增加，直到错误消失。

APPLHEAPSZ (DB)
应用程序堆是供数据库管理器代表某个特定代理使用的私有内存。当代理或子代理要为应用程序而初始化时，就要从这个堆中分配内存，并且所分配的内存数量是处理请求时所需的最小内存量，如果需要更多的内存，则最多可以从堆中分配由该参数指定的一个最大值那么多的内存。按 256 逐次增加，直到错误消失。

回页首

为提高性能进行快照监视

使用快照监视来识别数据库在一段时间里的行为，显示一些诸如内存使用情况和锁的获得过程之类的信息。监控是用于微调配置和识别问题（例如语句执行时间较长）的一种方法。如果已经使用了 Configuration Advisor，那么这里可能无法在性能上获得什么好处。

要收集供分析的数据，最容易的方法是在应用程序运行的时候用一个脚本来执行抽样的快照监视。像清单 3中或者清单 4中显示的脚本将会收集您进入下一步之前所需的所有信息。首先在 60 秒的一段时间内运行该脚本，其中有几次间歇；这样应该可以对应用程序行为的一个较好的抽样，并且不会有太多的信息要处理。

清单 3. getsnap.ksh (UNIX)

#!/usr/bin/ksh            #  take a snapshot after specified sleep period for a number of iterations            #  parameters: (1) database name            #                (2) directory for output            #              (3) interval between iterations (seconds)            #              (4) maximum number of iterations            #            #  Note: You may receive an error about the monitor heap being too small. You may            #        want to set mon_heap_sz to 2048 while monitoring.            if [ $# -ne 4 ]            then echo "4 parameters required: dbname output_dir sleep_interval iterations"; exit            fi            dbname=$1            runDir=$2            sleep_interval=$3            iterations=$4            stat_interval=3            stat_iterations=$(($sleep_interval/$stat_interval))            if [[ -d $runDir ]]; then            echo "dir: $runDir already exists, either remove it or 使用 another directory name"            exit            fi            mkdir  $runDir            cd     $runDir            db2 update monitor switches using bufferpool on lock on sort on statement on \\            table on uow on            # repeat the snapshot loop for the specified iterations            let i=1            while [ i -le $iterations ]            do            if [ $i -le 9 ]            then            i2="0$i"            else            i2="$i"            fi            echo "Iteration $i2 (of $iterations) starting at `date`"            vmstat $stat_interval $stat_iterations > vmstat_$i2            iostat $stat_interval $stat_iterations > iostat_$i2            db2 -v reset monitor all            sleep $sleep_interval            db2 -v get snapshot for dbm > snap_$i2            db2 -v get snapshot for all on $dbname >> snap_$i2            echo "Iteration $i2 (of $iterations) complete at `date`"            let i=$i+1            done            db2 update monitor switches using bufferpool off lock off sort off statement off \\            table off uow off            db2 terminate

清单 4. getsnap.bat (Windows)

@echo off            REM            REM  take a snapshot after specified sleep period for a number of iterations            REM  parameters: (1) database name            REM                 (2) file name id            REM              (3) interval between iterations (seconds)            REM              (4) maximum number of iterations            REM            REM   Note: You may receive an error about the monitor heap being too small. You may            REM         want to set mon_heap_sz to 2048 while monitoring.            :CHECKINPUT            IF ""=="%4" GOTO INPUTERROR            GOTO STARTPRG            :INPUTERROR            echo %0 requires 4 parameters: dbname filename_id sleep_interval iterations            echo e.g. "getsnap.bat sample 0302 60 3"            GOTO END            :STARTPRG            SET dbname=%1            SET fileid=%2            SET sleep_interval=%3            SET iterations=%4            db2 update monitor switches using bufferpool on lock on sort on statement on table on uow on            REM repeat the snapshot loop for the specified iterations            SET i=1            :SNAPLOOP            IF %i% LSS 10 SET i2=0%i%            IF %i% GTR 9 SET i2=%i%            echo Starting Iteration %i2% (of %iterations%)            db2 -v reset monitor all            sleep %sleep_interval%            db2 -v get snapshot for dbm > snap%i2%_%fileid%            db2 -v get snapshot for all on %dbname% >> snap%i2%_%fileid%            echo Completing Iteration %i2% (of %iterations%)            SET /a i+=1            IF %i% GTR %iterations% GOTO ENDLOOP            GOTO SNAPLOOP            :ENDLOOP            db2 update monitor switches using bufferpool off lock off sort off statement off table off uow off            db2 terminate            :END

注意，这两个脚本在行为上稍有不同，但是都可以产生所需的快照输出。

在后面的一些小节中，快照监视可用作寻找 DBM 和 DB 配置参数的最优设置的一种方式。

DB2 v8 Documentation:

Reference ==> System monitor ==> Snapshot monitor

动态 SQL 语句

清单 3和清单 4中显示的脚本将发出一个 "get snapshot for all on dbname" 命令，该命令包括 "get snapshot for dynamic SQL on dbname" 命令的所有输出。如果您发现不会捕获很多的 SQL 语句，那么可以增加监控的历时。一条语句的输出的 "Dynamic SQL Snapshot Result" 部分看上去如清单 5所示：

清单 5. 示例动态 SQL 快照

      Dynamic SQL Snapshot Result            Database name                      = SAMPLE            Database path                      = C:\\DB2\\NODE0000\\SQL00003\\             Number of executions           = 1            Number of compilations             = 1            Worst preparation time (ms)        = 1624            Best preparation time (ms)         = 1624            Internal rows deleted              = 0            Internal rows inserted             = 0             Rows read                      = 41            Internal rows updated              = 0            Rows written                       = 0            Statement sorts                    = 0             Total execution time (sec.ms)  = 0.134186            Total user cpu time (sec.ms)       = 0.000000            Total system cpu time (sec.ms)     = 0.000000             Statement text                 = select * from sales            ...

您可以看到，在输出中可以搜索一些很有用的字符串。

"Number of executions"- 可以帮助您找到应该调优的那些重要语句。它对于帮助计算语句的平均执行时间也很有用。

对于执行时间很长的语句，单独执行一次或许对系统要求不多，但是累加起来的结果就会大大降低性能。应尽量理解应用程序如何使用该 SQL，或许只需对应用程序逻辑稍微重新设计一下就可以提高性能。

看看是否可以使用参数标记（parameter marker），以便只需为语句创建一个包，这一点也很管用。参数标记可用作动态准备的语句（生成访问计划时）中的占位符。在执行的时候，就可以将值提供给这些参数标记 marker，从而使语句得以运行。

例如，要搜索执行得最频繁的语句：

UNIX:

grep -n " Number of executions"            snap.out | grep -v "= 0" | sort -k 5,5rn | more

Windows:

findstr /C:" Number of executions"            snap.out | findstr /V /C:"= 0"

"Rows read"- 可帮助识别读取行数最多的 Dynamic SQL 语句。如果读取的行数很多，通常意味着要进行表扫描。如果这个值很高，也可能表明要进行索引扫描，扫描时选择性很小，或者没有选择性，这跟表扫描一样糟糕。

您可用使用 Explain 来查看是否真的如此。如果有表扫描发生，那么可以对表执行一次 RUNSTATS，或者将 SQL 语句提供给 DB2 Design Advisor，以便令其推荐一个更好的索引，以此来进行弥补。如果是选择性很差的索引扫描，或许需要一个更好的索引。可以试试 Design Advisor。

grep -n " Rows read"            snap.out | grep -v "= 0" | sort -k 5,5rn

findstr /C:" Rows read"            snap.out | findstr /V /C:"= 0"

"Total execution time" - 这是将语句每次执行时间加起来得到的总执行时间。我们可以很方便地将这个数字除以执行的次数。如果发现语句的平均执行时间很长，那么可能是因为表扫描和/ 或出现锁等待（lock-wait）的情况。索引扫描和页面获取导致的大量 I/O 活动也是一个原因。通过使用索引，通常可以避免表扫描和锁等待。锁会在提交的时候解除，因此如果提交得更频繁一些，或许可以弥补锁等的问题。

grep -n " Total execution time"            snap.out | grep -v "= 0.0" | sort -k 5,5rn | more

findstr /C:" Total execution time"            snap.out | findstr /V /C:"= 0.0" |sort /R

"Statement text"显示语句文本。如果注意到了重复的语句，这些语句除了 WHERE 子句中谓词的值有所不同以外，其他地方都是一致的，那么就可以使用参数标记，以避免重新编译语句。这样可以使用相同的包，从而帮助避免重复的语句准备，而这种准备的消耗是比较大的。还可以将语句文本输入到 Design Advisor 中，以便生成最优的索引。

grep -n " Statement text"            snap.out | more

findstr /C:"Statement text"            snap.out

缓冲池大小的设置

通过使用 "get snapshot for all on dbname" 可以为数据库上的每个缓冲池生成一个快照。清单 6展示了那样一个快照：

清单 6. 示例缓冲池快照

             Bufferpool Snapshot            Bufferpool name                            = IBMDEFAULTBP            Database name                              = SAMPLE            Database path                              = C:\\DB2\\NODE0000\\SQL00002\\            Input database alias                       = SAMPLE            Snapshot timestamp                         = 02-20-2004 06:24:45.991065            Buffer pool data logical reads        = 370            
Buffer pool data physical reads       = 54            Buffer pool temporary data logical reads   = 0            Buffer pool temporary data physical reads  = 0            Buffer pool data writes                    = 3            Buffer pool index logical reads       = 221            
Buffer pool index physical reads      = 94            Buffer pool temporary index logical reads  = 0            Buffer pool temporary index physical reads = 0            Total buffer pool read time (ms)           = 287            Total buffer pool write time (ms)          = 1            Asynchronous pool data page reads          = 9            Asynchronous pool data page writes         = 0            Buffer pool index writes                   = 0            Asynchronous pool index page reads         = 0            Asynchronous pool index page writes        = 0            Total elapsed asynchronous read time       = 0            Total elapsed asynchronous write time      = 0            Asynchronous data read requests            = 3            Asynchronous index read requests           = 0            No victim buffers available                = 0            Direct reads                               = 86            Direct writes                              = 4            Direct read requests                       = 14            Direct write requests                      = 2            Direct reads elapsed time (ms)             = 247            Direct write elapsed time (ms)             = 56            Database files closed                      = 0            Data pages copied to extended storage      = 0            Index pages copied to extended storage     = 0            Data pages copied from extended storage    = 0            Index pages copied from extended storage   = 0            Unread prefetch pages                      = 0            Vectored IOs                               = 3            Pages from vectored IOs                    = 9            Block IOs                             = 0            Pages from block IOs                       = 0            Physical page maps                         = 0            Node number                                = 0            Tablespaces using bufferpool               = 4            Alter bufferpool information:            Pages left to remove                      = 0             Current size                         = 250            Post-alter size                           = 250

为了判断一个缓冲池的效率，需要计算它的缓冲池命中率（BPHR）。您所需的重要信息在上面已经用粗体标出来了。如果可能的话，一个理想的 BPHR 在某些地方应超过 90%。公式如下：

BPHR (%) = (1 - (("Buffer pool data physical reads" + "Buffer pool index physical reads") /            ("Buffer pool data logical reads" + "Buffer pool index logical reads"))) * 100

在 IBMDEFAULTBP 缓冲池的以上快照中，我们可以这样来计算 BPHR：

            = (1-((54 + 94) / (370 + 221))) * 100            = (1-(148 / 591)) * 100            = (1- 0.2504) * 100            = 74.96

在这种情况下，BPHR 大约等于 75%。当前，缓冲池只有 250 * 4KB 页（1MB）。增加该缓冲池的大小，看看 BPHR 是否会随之增加，这样做是值得的。如果 BPHR 还是比较低，那么可能就需要像创建缓冲池和创建表空间这两节中讨论的那样重新设计逻辑布局。

基于块的缓冲池的效率
如果是一个基于块的缓冲池，并且看到 "Block IOs" 的值较低，那么应考虑修改缓冲池，增加 NUMBLOCKPAGES 的大小。如果这时看到 "Block IOs" 的值更大了，则可以考虑将 NUMBLOCKPAGES 再增大一些。如果结果适得其反，则应减小 NUMBLOCKPAGES 的大小。

DBM 和 DB 配置

DB2 有几十个配置参数。很多参数都是由 DB2 自动配置的，而其他一些参数都有它们的缺省值，这些缺省值都被证明在大多数环境中能够发挥得很好。接下来，我们只描述那些常常需要另外进行配置的参数。

有些数据库管理器（即实例）配置参数可以在线更改（立即生效），而另一些参数则要求对实例实行再循环（即 DB2STOP 之后接着又是 DB2START）。对于数据库配置参数也是一样。有些参数的更改可以立即生效，而另一些参数则要求先停止数据库，再重新激活数据库。关于每种配置参数的文档都规定了参数是否可以在线配置。

数据库管理器和数据库配置文件的基本管理命令如表 3所示：

表 3. 数据库管理器和数据库配置管理

命令	描述
GET DBM CFG [SHOW DETAIL]	列出数据库管理器配置文件中的当前值
UPDATE DBM CFG USINGconfig_param value	将指定的数据库管理器配置参数设置成指定的值
GET DB CFG FORdb_name[SHOW DETAIL]	列出某个特定数据库的配置文件中的当前值
UPDATE DB CFG FORdb_nameUSING config_param value	将指定的数据库管理器配置参数设置成指定的值

当您对一个配置参数作了更改时，就可以用下面的 DB2 CLP 命令查看该设置是否立即生效（在线）：

GET DBM CFG SHOW DETAIL

GET DB CFG FOR dbname SHOW DETAIL

例如，在接下来的情况中，MAX_QUERYDEGREE 和 MAXTOTFILOP 分别增加到了 3 和 19000。如果参数是在线配置的，则 Delayed Value 跟 Current Value 应该是一样的。否则，就需要重新启动实例，或者重新激活数据库。

清单 7. Show Details 实例

          Database Manager Configuration            Node type = Enterprise Server Edition with local and remote clients            Description                                   Parameter   Current Value      Delayed Value            -------------------------------------------------------------------------------------------            Maximum query degree of parallelism   (MAX_QUERYDEGREE) = 3                  3            Maximum total of files open               (MAXTOTFILOP) = 16000              19000

下面的配置参数中，有些是从共享内存分配空间的，所以应该记住 OS 的限制（在前面已讨论）。您必须确保没有过度分配内存。如果过度分配内存，就会导致操作系统发生换页（page），这对于性能来说是灾难性的。

DB2 v8 Documentation:

Reference ==> Configuration parameters ==> Database manager
Reference ==> Configuration parameters ==> Database

清单 8和清单 9显示了一个数据库管理器和数据库快照的示例。顺着右边（顺带提一下），可以看到能根据输出进行调优的配置参数。

清单 8. 数据库管理器快照

            Database Manager Snapshot            Node name                                      =            Node type                                      = Enterprise Server Edition with            local and remote clients            Instance name                                  = DB2            Number of database partitions in DB2 instance  = 1            Database manager status                        = Active            Product name                                   = DB2 v8.1.4.341            Service level                                  = s031027 (WR21326)            Private Sort heap allocated               = 0                                (SHEAPTHRES            
Private Sort heap high water mark         = 1024            
Post threshold sorts                      = 0                                     and            
Piped sorts requested                     = 0            
Piped sorts accepted                      = 0                                  SORTHEAP)            
            Start Database Manager timestamp               = 02-17-2004 14:24:37.107003            Last reset timestamp                           =            Snapshot timestamp                             = 02-20-2004 06:19:53.272049            Remote connections to db manager          = 0       (MAXAGENTS,MAXAPPLS,MAX_COORDAGENTS)            
            Remote connections executing in db manager     = 0            Local connections                         = 1       (MAXAGENTS,MAXAPPLS,MAX_COORDAGENTS)            
            Local connections executing in db manager      = 0            Active local databases                    = 1                                     (NUMDB)            
High water mark for agents registered     = 8                                 (MAXAGENTS)            
            High water mark for agents waiting for a token = 0            Agents registered                         = 8                                 (MAXAGENTS)            
            Agents waiting for a token                     = 0            Idle agents                               = 6         (NUM_POOLAGENTS and NUM_INITAGENTS)            
            Committed private Memory (Bytes)               = 46645248            Switch list for db partition number 0            Buffer Pool Activity Information  (BUFFERPOOL) = ON  02-20-2004 06:18:57.403336            Lock Information                        (LOCK) = ON  02-20-2004 06:18:57.403338            Sorting Information                     (SORT) = ON  02-20-2004 06:18:57.403339            SQL Statement Information          (STATEMENT) = ON  02-20-2004 06:18:57.403333            Table Activity Information             (TABLE) = ON  02-20-2004 06:18:57.403335            Take Timestamp Information         (TIMESTAMP) = ON  02-17-2004 14:24:37.107003            Unit of Work Information                 (UOW) = ON  02-20-2004 06:18:57.403328            Agents assigned from pool                 = 26        (NUM_POOLAGENTS and NUM_INITAGENTS)            
Agents created from empty pool            = 10        (NUM_POOLAGENTS and NUM_INITAGENTS)            
Agents stolen from another application    = 0                                 (MAXAGENTS)            High water mark for coordinating agents        = 8            Max agents overflow                       = 0                                 (MAXAGENTS)            Hash joins after heap threshold exceeded       = 0            Total number of gateway connections            = 0            Current number of gateway connections          = 0            Gateway connections waiting for host reply     = 0            Gateway connections waiting for client request = 0            Gateway connection pool agents stolen          = 0            Node FCM information corresponds to            = 2            Free FCM buffers                               = 4093            Free FCM buffers low water mark           = 4087                       (FCM_NUM_BUFFERS)            Free FCM message anchors                       = 1279            Free FCM message anchors low water mark        = 1276            Free FCM connection entries                    = 1280            Free FCM connection entries low water mark     = 1276            Free FCM request blocks                        = 2031            Free FCM request blocks low water mark         = 2026            Number of FCM nodes                            = 4            Node         Total Buffers       Total Buffers       Connection         (FCM_NUM_BUFFERS)            Number       Sent                Received            Status            -----------  ------------------  ------------------  -----------------            0          282                 275         Active            1           51                  48         Active            2            0                   0         Active            3            1                   1         Active                        Memory usage for database manager:                Memory Pool Type                      = Backup/Restore/Util Heap      (UTIL_HEAP_SZ*)            Current size (bytes)               = 16384            High water mark (bytes)            = 16384            Maximum size allowed (bytes)       = 20660224            Memory Pool Type                      = Package Cache Heap              (PCKCACHESZ*)            Current size (bytes)               = 327680            High water mark (bytes)            = 327680            Maximum size allowed (bytes)       = 1071644672            Memory Pool Type                      = Catalog Cache Heap         (CATALOGCACHE_SZ*)            Current size (bytes)               = 81920            High water mark (bytes)            = 81920            Maximum size allowed (bytes)       = 1071644672             Memory Pool Type                           = Buffer Pool Heap            Current size (bytes)                    = 1179648            High water mark (bytes)                 = 1179648            Maximum size allowed (bytes)            = 1071644672                Memory Pool Type                   = Lock Manager Heap                    (LOCKLIST*)            Current size (bytes)               = 278528            High water mark (bytes)            = 278528            Maximum size allowed (bytes)       = 425984            Memory Pool Type                      = Database Heap                       (DBHEAP*)            Current size (bytes)               = 3342336            High water mark (bytes)            = 3342336            Maximum size allowed (bytes)       = 6275072            Memory Pool Type                      = Database Monitor Heap           (MON_HEAP_SZ)            Current size (bytes)               = 180224            High water mark (bytes)            = 425984            Maximum size allowed (bytes)       = 442368            Memory Pool Type                           = Other Memory            Current size (bytes)                    = 8060928            High water mark (bytes)                 = 8159232            Maximum size allowed (bytes)            = 1071644672

快照总是显示 Current size (bytes) = High water mark (bytes)，因为内存是在数据库激活的时候分配的。

清单 9. 数据库快照

                          Database Snapshot            Database name                              = SAMPLE            Database path                              = C:\\DB2\\NODE0000\\SQL00002\\            Input database alias                       = SAMPLE            Database status                            = Active            Catalog database partition number          = 0            Catalog network node name                  =            Operating system running at database server= NT            Location of the database                   = Local            First database connect timestamp           = 02-20-2004 06:19:00.847979            Last reset timestamp                       =            Last backup timestamp                      =            Snapshot timestamp                         = 02-20-2004 06:23:17.252491            High water mark for connections       = 1                (MAXAPPLS)            Application connects                       = 1            Secondary connects total                   = 0            Applications connected currently      = 1                 (AVG_APPLS)            Appls. executing in db manager currently   = 0            Agents associated with applications        = 1            Maximum agents associated with applications= 1            Maximum coordinating agents                = 1            Locks held currently                       = 0            Lock waits                                 = 0            Time database waited on locks (ms)         = 0            Lock list memory in 使用 (Bytes)       = 1000    (LOCKLIST and MAXLOCKS)            Deadlocks detected                         = 0            Lock escalations                      = 0         (LOCKLIST and MAXLOCKS)            
Exclusive lock escalations            = 0          (LOCKLIST and MAXLOCKS)            Agents currently waiting on locks          = 0            Lock Timeouts                         = 0                (LOCKTIMEOUT)            Number of indoubt transactions             = 0            Total Private Sort heap allocated     = 0      (SHEAPTHRES and SORTHEAP)            
Total Shared Sort heap allocated      = 0    (SHEAPTHRES_SHR and SORTHEAP)            
Shared Sort heap high water mark      = 0   (SHEAPTHRES_SHR and SORTHEAP)            
            Total sorts                                = 0            Total sort time (ms)                       = 0            Sort overflows                        = 0              (SORTHEAP)            Active sorts                               = 0            Buffer pool data logical reads             = 370            Buffer pool data physical reads            = 54            Buffer pool temporary data logical reads   = 0            Buffer pool temporary data physical reads  = 0            Asynchronous pool data page reads     = 9           (NUM_IOSERVERS)            
Buffer pool data writes               = 3      (CHNGPGS_THRESH and NUM_IOCLEANERS)            
Asynchronous pool data page writes    = 0      (CHNGPGS_THRESH and NUM_IOCLEANERS)            
            Buffer pool index logical reads            = 221            Buffer pool index physical reads           = 94            Buffer pool temporary index logical reads  = 0            Buffer pool temporary index physical reads = 0            Asynchronous pool index page reads    = 0         (NUM_IOSERVERS)            
Buffer pool index writes              = 0      (CHNGPGS_THRESH and NUM_IOCLEANERS)            
Asynchronous pool index page writes   = 0     (CHNGPGS_THRESH and NUM_IOCLEANERS)            Total buffer pool read time (ms)           = 287            Total buffer pool write time (ms)          = 1            Total elapsed asynchronous read time       = 0            Total elapsed asynchronous write time      = 0            Asynchronous data read requests            = 3            Asynchronous index read requests           = 0            No victim buffers available                = 0            LSN Gap cleaner triggers                   = 0            Dirty page steal cleaner triggers     = 0            (CHNGPGS_THRESH)            
Dirty page threshold cleaner triggers = 0           (CHNGPGS_THRESH)            
Time waited for prefetch (ms)         = 0            (NUM_IOSERVERS)            Unread prefetch pages                      = 0            Direct reads                               = 86            Direct writes                              = 4            Direct read requests                       = 14            Direct write requests                      = 2            Direct reads elapsed time (ms)             = 247            Direct write elapsed time (ms)             = 56            Database files closed                 = 0          (MAXFILOP)            Data pages copied to extended storage      = 0            Index pages copied to extended storage     = 0            Data pages copied from extended storage    = 0            Index pages copied from extended storage   = 0            Host execution elapsed time                = 0.000039            Commit statements attempted                = 6            Rollback statements attempted              = 1            Dynamic statements attempted               = 281            Static statements attempted                = 7            Failed statement operations                = 1            Select SQL statements executed             = 4            Update/Insert/Delete statements executed   = 0            DDL statements executed                    = 2            Internal automatic rebinds                 = 0            Internal rows deleted                      = 0            Internal rows inserted                     = 0            Internal rows updated                      = 0            Internal commits                           = 1            Internal rollbacks                         = 0            Internal rollbacks due to deadlock         = 0            Rows deleted                               = 0            Rows inserted                              = 0            Rows updated                               = 0            Rows selected                              = 336            Rows read                                  = 375            Binds/precompiles attempted                = 0            Log space available to the database (Bytes)= 5095757   (LOGPRIMARY and LOGSECOND)            Log space used by the database (Bytes)     = 4243            Maximum secondary log space used (Bytes)   = 0            Maximum total log space used (Bytes)  = 6498    (LOGPRIMARY and LOGSECOND)            
Secondary logs allocated currently    = 0      (LOGPRIMARY and LOGSECOND)            
Log pages read                        = 0              (LOGBUFSZ)            
Log pages written                     = 5              (LOGBUFSZ)            Appl id holding the oldest transaction     = 38            Package cache lookups                 = 10        (PKGCACHESZ)            
Package cache inserts                 = 8         (PKGCACHESZ)            
Package cache overflows               = 0         (PKGCACHESZ)            
Package cache high water mark (Bytes) = 191140    (PKGCACHESZ)            Application section lookups                = 281            Application section inserts                = 6            Catalog cache lookups                 = 18       (CATALOGCACHE_SZ)            
Catalog cache inserts                 = 9        (CATALOGCACHE_SZ)            
Catalog cache overflows               = 0        (CATALOGCACHE_SZ)            
Catalog cache high water mark         = 0        (CATALOGCACHE_SZ)            Workspace Information            Shared high water mark                    = 0            Corresponding shared overflows            = 0            Total shared section inserts              = 0            Total shared section lookups              = 0            Private high water mark                   = 21102            Corresponding private overflows           = 0            Total private section inserts             = 6            Total private section lookups             = 6            Number of hash joins                       = 0            Number of hash loops                       = 0            Number of hash join overflows         = 0         (SORTHEAP)            
Number of small hash join overflows   = 0         (SORTHEAP)

有时候，为了某些问题使用 "grep" (UNIX) 和 "findstr" (Windows) 对快照输出执行初步的搜索非常方便。如果发现了什么东西，就可以通过打开快照输出并找到问题所在，以便作进一步调查。

例如，为了识别是否存在死锁：

UNIX:

grep -n "Deadlocks detected" snap.out | grep -v "= 0" | more

Windows:

findstr /C:"Deadlocks detected" snap.out | findstr /V /C:"= 0"

SHEAPTHRES (DBM)
这是实例中所有数据库的并发私有排序所消耗的内存总量。另外传入的排序只能得到更少量的可用内存。对于 OLTP，一开始最好是使用大约 20000，而对于 OLAP 40000-60000 会工作得更好一些。

当 "Piped sorts accepted"的值与 "Piped sorts requested" 比起来较低时，通过增加 SHEAPTHRES 的大小，常常可以提高性能。如果 "Post threshold sorts" （在 SHEAPTHRES 已经被超出之后，请求堆的排序）的值较高（也就是有两位数），应尝试增加 SHEAPTHRES 的大小。 "Total Private Sort heap allocated"应该小于 SHEAPTHRES。如果不是这样，则应增加 SHEAPTHRES。

MAXAGENTS (DBM)
这是可用于接受对实例中所有数据库的应用程序请求的数据库管理器代理的最多数量。在受限内存（memory constrained）的环境中，这个参数对于限制数据库管理器的总内存使用量很有用，因为每个附加的代理都需要附加的内存。

如果机器是受限内存的，那么可以增加 MAXAGENTS，直到 "Agents stolen from another application"为 0。此外， "Local connections"+ "Remote connections to db manager"将指出连接到实例上的并发连接的数量。 "High water mark for agents registered"将报告在某一次连接到数据库管理器的代理曾出现的最大数量。"Max agents overflow"报告当已经达到 MAXAGENTS 时，所收到的创建一个新代理的请求的次数。最后， "Agents Registered"显示在被监控的数据库管理器实例中当前注册的代理的数量。

NUMDB (DBM)
指定可以同时激活的本地数据库的数量。在生产系统中，建议每个实例有一个数据库，因此应该将这个值设为 1。否则，将其设为同时激活的数据库的最大数量。如果不确定的话，使用 "Active local databases"。

NUM_INITAGENTS 和 NUM_POOLAGENTS (DBM)
NUM_INITAGENTS 指定在 db2start 上的池中创建的空闲代理的数量，它可以帮助加快在开始使用数据库时的连接。NUM_POOLAGENTS 也是相关的，但是如果数据库已经运行了一段时间，那么它对性能会有更大的影响。当 Connections Concentrator 为 OFF（缺省值；MAX_CONNECTIONS = MAX_COORDAGENTS）时，NUM_POOLAGENTS 指定代理池的最大大小。当 Concentrator 为 ON （MAXCONNECTIONS > MAX_COORDAGENTS）时，可以参考这个值来决定在系统工作负载较低时代理池应该有多大。

NUM_INITAGENTS 和 NUM_POOLAGENTS 应该设为预期的并发实例级连接的平均数量，对于 OLAP 这个值通常比较低，而对于 OLTP 就要高一些。对于存在大量 ramp up 连接情况下的性能基准，将 NUM_INITAGENTS 设置成预期的连接数量（这将减少资源争用，从而显著地减少 ramp up 连接所需的时间）。在使用了连接池的 3 层环境中，NUM_INITAGENTS 和 NUM_POOLAGENTS 对性能的影响很小，因为即使在应用程序空闲的时候，应用服务器也会连续不断地维护连接。

"Idle agents"显示了在代理池中空闲代理的数量，而 "Agents assigned from pool"则显示从代理池中将一个代理分配出去的次数。 "Agents created from empty pool"显示在空池情况下必须创建的代理的数量，这可能会让人误解为刚好在 db2start 之后。而在 db2start 之后，它只是显示被创建的代理的数量。如果 "Agents created from empty pool"/ "Agents assigned from pool"的比例比较高（5:1 或更大），则可能表明应该增加 NUM_POOLAGENTS。这也可能表明系统的总体工作负载太高。这时可以通过降低 MAXCAGENTS 来调整工作负载。如果这个比例较低，则暗示着 NUM_POOLAGENTS 可能被设得太高，有些代理就会浪费系统资源。

FCM_NUM_BUFFERS (DBM)
只在有多个逻辑分区的 DPF 环境中使用。它指定用于内部通信的大小为 4 KB 的缓冲区的数量。如果没有使用 DPF，那么这个值甚至不会出现在快照输出中。此外，该信息将来自其上运行了快照的分区。例如，在 DBM 快照之前的那个快照中， "Node FCM information corresponds to"显示了一个值 2，因此它是从 2 号分区那里得来的。"Get snapshot for dbm global" 可用于获得所有分区值的群集。

DBM 快照的 FCM Node 部分可用于查看主要的分区间通信发生的地点，以用于调查的目的。如果通信量很大，则表明需要更多的 FCM 缓冲区内存，需要不同的分区键，或者需要不同的表来分派表空间。如果 "Free FCM buffers low water mark"小于 FCM_NUM_BUFFERS 的百分之 15，那么可以增加 FCM_NUM_BUFFERS，直到 "Free FCM buffers low water mark"大于或等于 FCM_NUM_BUFFERS 的百分之 15，以确保总有足够的 FCM 资源可供使用。

AVG_APPLS (DB)
只有在应用程序发出复杂的 SQL（例如连接、函数、递归等等）时才更改它，否则让它一直为 1。这可以帮助估计在运行时可以为一个访问计划提供多少的缓冲池。它应该设为一个较低的值，即 "Applications connected currently"的平均数量乘以复杂 SQL 的百分比。

LOCKLIST 和 MAXLOCKS (DB)
对于每个数据库都有一个锁列表，该列表包含所有同时连接到数据库的应用程序所持有的锁。在 32 位的平台上，一个对象上的第一个锁要求占 72 字节，而其他的锁要求占 36 字节。在 64 位平台上，第一个锁要求占 112 字节，而其他锁要求占 56 字节。

当一个应用程序使用的 LOCKLIST 的百分比达到 MAXLOCKS 时，数据库管理器将执行一次锁升级（lock escalation），在这个操作中将使得行锁换成单独的一个表锁。而且，如果 LOCKLIST 快要耗尽，数据库管理器将找出持有一个表上的最多行锁的连接，并将这些行锁换成表锁，以释放 LOCKLIST 内存。锁整个表可以大大减少并发性，死锁的几率也增加了。

如果 "Lock list memory in 使用 (Bytes)"超出了定义的 LOCKLIST 大小的 50％，那么应增加 LOCKLIST 4 KB 大小的页面的数量。如果发生了 "Lock escalations"或 鈥淓xclusive lock escalations鈥_，则应该或者增加 LOCKLIST，或者增加 MAXLOCKS，抑或同时增加两者。

关于锁的数据库快照部分包含大量有价值的信息。看看 "Locks held currently"、"Lock waits"、 "Time database waited on locks (ms)"、 "Agents currently waiting on locks"和 "Deadlocks detected"中是否存在高值，如果有的话，就可能是差于最优访问计划、事务时间较长或者应用程序并发问题的症状。如果要发现死锁，那么需要创建一个针对死锁的事件监视器，事件监视器带有详细信息，以便查看当前正在发生的事情。

要了解关于锁问题的详细信息，请参阅 Bill Wilkins 撰写的文章 Diagnosing and Resolving Lock Problems with DB2 Universal Database。

您可以做下列事情来减少锁：

确保应用程序正在使用最低的隔离级别。
经常执行 COMMIT。
当执行很多更新时，在更新前显式地锁整个表（使用 LOCK TABLE 语句）。
尽量使用 Cursor Stability 隔离级别（缺省情况），以便减少被持有的共享锁的数量。（如果应用程序能够承受脏读，那么 Uncommitted Read 可以进一步减少锁。）

DB2调优摘要,布布扣,bubuko.com

DB2调优摘要

码农公寓

相关文章