如何在 SAP 系统中监控和分析 DB2 UDB 性能

对于数据库来说，最消耗时间的操作实际上是从磁盘中检索数据。这是由磁盘的物理特性决定的。尽管磁盘存储技术已经取得了极大的进步，但磁盘的读写速度与内存的读写速度仍然相差几个数量级。从性能调整的角度来说，如果一个机器的磁盘出现问题，那么其他的任何优化工作都无法提供帮助。因此我们应该保证运行数据库的磁盘系统是健康的。SAP 系统为我们提供了监控磁盘读写速度的功能，让我们可以直接了解当前磁盘的性能状况。

我们首先进入 SAP 的 DBA Cockpit ( 可以直接输入 st04)，然后在 Performance 的目录下双击 Database, Buffer Pool 的标签内，可以看到当前数据库磁盘的读写状况。

图 1. 磁盘 I/O 信息

从图中我们可以看到磁盘物理平均读速度为 3.25ms，写速度为 7.45ms。

磁盘物理读写速度反应了磁盘子系统的性能。一般情况下，磁盘读写速度应该小于 5ms，读速度一般要大于写速度，但在一些具有大量缓存的存储系统中，写速度可能会快于读速度。磁盘性能的优化已经超出了本文讨论的范围，这里只是提供一些基本的指导。

缓冲池监控

缓冲池是数据库单独开辟的一块存储区域，这片区域用来缓存从磁盘读出的包含数据表和索引的数据页。当数据第一次被检索出来以后便被暂时缓存在缓冲池中，当数据下次被访问时，数据库将直接从缓冲池中读取数据，这样减少了相对缓慢的磁盘 I/O 操作。因此，缓冲池的配置对于数据库性能十分重要。在缓冲池中，目前还不支持存储大对象和长数据记录。

反映缓冲池质量的一个重要性能指标是缓冲池命中率。缓冲池命中率指数据库管理器不需从磁盘读入页就能处理页请求的时间百分比。其计算方法为：

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

另外两个重要性能指标是索引命中率和数据命中率。索引命中率反映了可以在缓冲池中找到的页面能够满足的对索引页的所有读请求所占的百分比。其计算方法为：

索引命中率 = (1 - ( 缓冲池索引物理读 / 缓冲池索引逻辑读 ) ) ) * 100%

数据命中率说明了可以在缓冲池中找到的页面能够满足的对数据页的所有读请求所占的百分比。其计算方法为：

数据命中率 = (1 - ( 缓冲池数据物理读 / 缓冲池数据逻辑读 ) ) ) * 100%

我们可以从三个级别来看缓冲池的质量，这三个层次分别是数据库级，缓冲池级，表空间级。在 SAP 系统中我们可以使用 DBA Cockpit 来查看不同级别的缓冲池质量。首先可以在数据库级查看缓冲池的质量，我们进入 SAP 的 DBA Cockpit，然后在 Performance 的目录下双击 Database, 在 Buffer Pool 的标签内，可以看到当前数据库总体的缓冲池质量。

图 2. 缓冲池总体状况

我们从图中可以看出，数据库缓冲池的总体命中率是 99.81%，数据命中率为 99.86%，索引命中率为 99.70%。

如果在 st04 中选中 Performance -> Buffer pools，我们也可以在缓冲池级别看到命中率，如果一个数据库只有一个缓冲池，那么这个命中率与我们在数据库级别看到的命中率相同。

图 3. 数据库级别缓冲池信息

如果在 st04 中选中 Performance -> Tablespaces，我们就可以在表空间级别看到缓冲池的命中率。

图 4. 表空间级别缓冲池信息

缓冲池的理想命中率对于索引应该大于 90%, 对于数据应该大于 95%。要提高缓冲池的命中率，可以增加缓冲池的大小，也可以为不同类型数据分配不同缓冲池，可以为每个经常访问的具有自己的表空间的大型表使用一个缓冲池，也可以为一组小型表使用一个缓冲池。

缓存监控

数据库的缓存主要有包缓存 (Package Cache) 和编目录缓存 (Catalog Cache)。它们与数据库的查询性能息息相关。

包缓存(Package Cache) ：SQL 语句编译通常消耗的资源比较大，为了提高系统性能，动态 SQL 语句在被编译后一般存放于包缓存中。当用户下一次请求同一条 SQL 语句，就无需再次编译 SQL 语句。包缓存的质量一般通过包缓存命中率来衡量，它表明了包缓存的设置是否成功的避免了 SQL 语句的重新编译。其计算方法为：

包缓存命中率 = (1 - ( 在包缓存中的插入次数 / 查询包缓存的次数 )) * 100

编目录缓存 (Catalog Cache)：编目录缓存用来缓存系统编目录信息，如系统表，权限，系统存储过程。系统编目录的访问速度对于系统的性能有着十分重要的影响。在 DPF 环境下，系统编目录的访问速度至关重要。通过使用编目录缓存可以大大提高访问系统编目录的速度。编目录缓存质量一般通过编目录命中率来衡量，它表明了编目录缓存是否成功的避免了从磁盘中读取编目录信息。其计算方法为：

包缓存命中率 = (1 - ( 在编目录缓存中的插入次数 / 查询编目录缓存的次数 )) * 100

我们进入 SAP 的 DBA Cockpit，然后在 Performance 的目录下双击 Database, 在 Cache 的标签内，可以看到当前数据库缓存的统计信息。

图 5. 数据库缓存信息

从图中我们可以看到编目录缓存的质量是 99.93%，在图中的 quality 就是我们前面所说的命中率。当前数据库编目录缓存的大小为 10240KB，没有缓存溢出。在左边一栏，我们可以看到，包缓存的质量是 97.64%，包缓存的大小为 62080KB，没有缓存溢出。

包缓存的理想命中率应该大于 98%，用户通常不用关注包缓存的大小，如果 PCKCACHESZ 被设置为 automatic，其大小由 DB2 自动调节。编目录缓存的理想命中率也应该大于 98%，其大小应该保证编目录缓存不应该发生任何溢出。我们可以调整数据库配置参数 CATALOGCACHE_SZ 来改变编目录缓存大小，由于编目录缓存是从数据库堆中分配的，因此，在改变 CATALOGCACHE_SZ 变量的同时，应该注意到数据库堆的大小也会相应改变。

排序监控

DB2 在运行过程中时经常要做排序操作。一般说来，在 OLTP 类型的数据库中，排序操作通常少于 OLAP 类型的数据库环境。排序操作通常会在三种情况下发生，第一种情况是数据的查询处理，比如 order by, group, 哈希连接，索引操作，内存的表操作等等。第二种是当我们载入操作的对象是带有索引的表时，再载入操作过程中就会涉及到对索引键的列表和排序，这样就会产生排序操作。第三种情况发生在创建索引的时候。排序的效率因而直接影响到数据库的响应时间，我们必须对排序进行有效监控。

我们进入 SAP 的 DBA Cockpit，然后在 Performance 的目录下双击 Database, 在 Sorts 的标签内，可以看到当前数据库的排序状况。

图 6. 数据库排序状况

可以从图中看出，共享排序堆的大小为 1676KB, 私有排序堆的大小为 1340KB。如果没有索引满足所取的行的要求顺序，或者 DB2 查询优化器认为排序的代价低于索引扫描，那么就需要在排序堆中进行排序。DB2 的排序分为私有排序和共享排序。私有排序发生在代理的私有代理内存中，而共享排序发生在数据库的数据库共享内存中。我们还可以看出，排序堆溢出次数 1174 次，总的排序次数为 310642 次。

如果数据库分配的排序堆大小不够大，就会出现排序溢出的情况，这样就需要动用临时表空间来辅助排序的进行，由于临时表空间存在于磁盘，这将大大影响排序的速度。理想情况下，排序溢出率 ((Sort overflows * 100) / Total sorts ) 不应该超过 1%。如果这个溢出率过高，那么数据库中很可能发生了大的排序，我们就需要调查出现过度排序的原因。在发现根源之前，一个简易的解决方案是增加 SORTHEAP 的大小。然而，这样做通常是治标不治本并且掩盖了真实的性能问题。比较彻底的解决方案应该是确定引起排序的 SQL 并更改该语句，或通过增加索引来避免或减少排序开销。

并发性和锁的监控

数据库的锁是数据库管理器用来控制应用程序并发访问数据库数据并且保证数据库数据的一致性的重要机制，数据库中行和表都可以上锁。数据库的锁在保证了数据库数据一致性同时也在一定程度上降低了数据库的响应速度。锁等待和死锁是影响数据库相应速度的重要因素，糟糕的应用程序设计和不合理的 SQL 查询计划的生成都会导致锁等待和死锁。

锁升级 (Lock Escalation)：一个锁通常作为一个记录存储在内存锁表中，锁表的大小可以由数据库自动调节。锁升级一般发生在锁的数量超过了数据库配置参数 MAXLOCKS 所指定的大小，为了减少锁的数量，数据库会把若干行一级的锁合并为表锁。这样数据库的并发性就会受到影响。

我们进入 SAP 的 DBA Cockpit，然后在 Performance 的目录下双击 Database, 在 Locks and Deadlocks 的标签内，可以看到当前数据库的锁的状况。

图 7. 数据库锁状况

从图中可以看到，当前锁表的大小为 22144KB，已经使用了 94KB。锁等待的平均时间为 10.40ms，没有锁升级和死锁被检测到。

影响数据性能的有关锁的问题主要集中在锁等待，死锁和锁升级。这些问题的根源很可能是数据库应用的设计问题。因此，我们应该仔细调查造成死锁，锁等待和锁升级的应用程序，以及其用到的 SQL 语句。同时，在问题定位之前，我们也可以通过下面方法来解决数据库锁造成的性能问题。

锁等待和死锁：如果要避免锁等待和死锁的问题我们需要注意数据库参数中的 DLCHKTIME 和 LOCKTIMEOUT 两个参数的设置。其中 DLCHKTIME 单位是毫秒，是 DB2 检查死锁的间隔时间，如果该值为 10000ms，则表明每隔 10 秒钟数据库会检查一下有无死锁存在，如有死锁，会选择回滚其中的某一个事务，让另外一个事务完成交易。LOCKTIMEOUT 单位是秒，是锁等待最长时间，超过该时间仍未获得锁，则返回错误。LOCKTIMEOUT 的默认值为 -1，这意味着锁等待时间无限大，一般不推荐这种设置。DLCHKTIME 时间通常要设得比 LOCKTIMEOUT 时间小一些，否则还未发现死锁，就会返回锁等待超时错误 (SQL0911N 返回码 68) 。

锁升级：要避免锁升级，我们应该正确设置数据库参数 LOCKLIST 和 MAXLOCKS。LOCKLIST 表明分配给锁表的内存大小。每个数据库都有一个锁表，锁表包含了并发连接到该数据库的所有应用程序所持有的锁。MAXLOCKS 定义了应用程序可以占有锁表空间的百分比，当一个应用程序所使用的锁表百分比达到 MAXLOCKS 时，数据库管理器会升级这些锁，用表锁代替行锁，从而减少列表中锁的数量。我们一般可以通过增加锁表大小的方法解决锁升级问题。

日志性能监控

DB2 事务日志对于恢复来说极其重要。它们记录对数据库对象和数据所做的更改。在 DB2 中数据和索引的改变都会先被写入日志缓冲区，保证对数据所做的修改在记录到数据库之前，总是被具体化为日志文件。日志缓冲区的数据由日志处理器写入磁盘。在下列情况下，查询处理必须等待日志数据写入磁盘后才能进行：

事务提交时。
在将相应数据页写入磁盘之前，因为 DB2 使用预写日志记录。预写日志记录的好处是当执行 COMMIT 语句完成事务之后，并非所有更改的数据和索引页都需要写入磁盘。
在元数据更改（一般通过执行 DDL 语句产生的）之前。
日志缓冲区已满。

DB2 以这种方法管理向磁盘写入日志数据的目的是尽可能地缩短处理延迟时间。在存在许多

较小的并发事务的环境中，许多处理延迟是由 COMMIT 造成的，因为它必须等待

日志数据写入磁盘后才能进行。因此，日志处理器进程频繁地将少量日志数据写入磁盘会造成大量处理延迟，另外一些延迟是由日志 I/O 开销造成的。

我们进入 SAP 的 DBA Cockpit，然后在 Performance 的目录下双击 Database, 在 Logging 的标签内，可以看到当前数据库日志的状况。

图 8. 数据库日志状况

我们在图中可以看到日志文件以及日志缓冲区的情况。包括日志文件的数量，大小，数据库使用的日志空间以及可用日志空间的大小。还可以看到日志缓冲区的情况，当前日志缓冲区的命中率为 100%。

由于数据库中的处理必须等待日志数据写入磁盘才能进行，日志文件的读写速度对数据库的响应速度也会产生很大影响。因此，应该把日志文件放到速度比较快的磁盘上，以减少磁盘 I/O 开销。日志文件写入的性能可以通过平均写时间来观察。另外，我们可以通过调整数据库配置参数 LOGBUFSZ 来指定日志缓冲区的大小。如果数据库对于日志磁盘有相当多的读操作，或者希望有较高的磁盘利用率。一般来说，如果日志缓冲区的命中率小于 98%，那么可以增加这个缓冲区的大小以提高命中率。当增加这个参数的值时，也要考虑 DBHEAP 参数，日志缓冲区使用的空间是 DBHEAP 参数所定义的内存空间的一部分。

数据库统计信息监控

数据库的统计信息反映了表及其相关索引的物理特点。统计信息主要包含：

表信息：表的行数，使用的数据页，溢出的行数，列的平均长度，列的最大最小值等。
索引信息：索引条目的数量，索引所关联列的不同值的数量，索引的层数等。

这些信息被数据库查询优化器用来生成查询计划。好的访问计划对于 SQL 语句的快速执行至关重要。我们需要想办法保证统计信息准确地反映了当前数据库的状况。由于数据库的表和索引总是随着数据库处理各种更新请求而不断发生变化的，因此，总会出现统计信息过时，而不能正确反映数据库数据现状的情况。这时，数据库查询优化器生成的查询计划可能不是最优的，这将大大影响数据库的查询性能。我们应该经常检查数据库的统计信息是否为最新的，并及时更新统计信息。SAP 系统为我们提供了监控和执行统计信息收集的方法。

我们进入 SAP 的 DBA Cockpit，然后在 Performance 的目录下双击 Tables, 在 Table 列表中双击要监控的表，在 Table 的标签中，我们可以看到当前表的基本信息。这时，如果我们点击 Count 按钮，就会看到统计信息的质量。

图 9. 表的统计信息状况

从图中我们看到，表的当前行数为 27，Deviation 为 8%。