《DB2数据库性能调整和优化》侧重于介绍DB2数据库的性能调优。性能调优是一个系统工程：全面监控分析操作系统、I/O性能、内存、应用及数据库才能快速找到问题根源；深刻理解DB2的锁及并发机制、索引原理、数据库参数、优化器原理、SQL语句调优等内部机理才能有针对性地快速提出解决问题的方法；快照、db2pd、db2expln及事件监控器等则是必须熟练掌握的工具。这本书正是覆盖了性能调优所需要的全部领域，并提供了大量的性能调优的实际案例。
　　本书系统性地总结了DB2数据库性能调整的方法、流程、思路和保持系统良好性能的注意要点。最难得的是作者分享了10年积累的DB2性能调优案例和经验总结。

　　存储I/O设计
　　对于任何程序的运行来说，最慢、最花费时间的操作实际上是从磁盘中检索数据。这主要缘于磁盘 I/O 访问中存在的物理机械过程(磁头旋转和寻道)。尽管磁盘存储技术在最近几年取得了极大的进步，但磁盘的旋转速度却没有太大的提高。您必须清楚这样一个事实：在一定条件下，RAM 访问仅需要大概 540个 CPU时钟周期，而磁盘访问则需要花费大概20 000 000个CPU 时钟周期。很明显，系统中访问数据最薄弱的环节就是磁盘 I/O 存储系统，从性能调整的角度来说，就是确保磁盘数据布局不会成为更严重的瓶颈。糟糕的数据布局将会给 I/O 性能带来更大的影响。在对系统进行任何优化活动之前，首先应该了解您的存储 I/O 系统的物理体系结构，因为如果您所设计的存储 I/O 系统非常糟糕，并且其中包含慢速磁盘，或者适配器的使用非常低效，那么其他的任何优化工作都无法提供帮助。
　　数据库的作用就是实现对数据的管理和查询。任何一个数据库系统，必然存在对数据的大量读、写操作。所以I/O问题也往往是导致数据库性能问题的重要原因。要控制好数据库的整体I/O性能，在规划数据库架构时就需要做好存储I/O系统的设计和配置。例如，将对I/O要求不同的文件放置在不同的存储设备上；规划表空间容器的分布、均衡I/O负担、使用并行I/O访问等。在一个应用系统的逻辑部署和物理部署图中，存储和操作系统位于应用系统体系结构的最底层。而数据库是部署在操作系统和存储层之上的，所以我们在做数据库的物理设计和逻辑设计之前，必须先做好存储I/O设计。存储I/O设计中最大的一个原则就是将I/O访问的分布最大限度地平衡在所有可以利用的物理设备上。
　　本章主要内容包括：
　　? 存储基本概念
　　? 存储架构
　　? 存储相关性能调整案例
　　? 存储设计性能相关问题
　　? 存储I/O设计总结
　　2.1  存储基本概念
　　关于存储的概念太多，许多已经超出了本书的讨论范围。本章主要讲解最常见的几个概念，它们是我们进行存储I/O设计所必须掌握的。
　　2.1.1  硬盘
　　硬盘处于整个存储系统的最底层，核心的业务数据通常都存放在硬盘上。我们从硬盘上读取一次I/O要花费的时间如下：
　　硬盘上一次I/O时间=磁盘寻道时间+磁头旋转到特定扇区时间+传输时间+延迟
　　图2-1所示的硬盘I/O传输图中标识了磁头在不同位置的寻道时间。衡量一个磁盘的I/O能力有如下几个指标：
　　图2-1  磁盘I/O传输图
　　● 硬盘的转速(Rotational Speed)：也就是硬盘电机主轴的转速，转速是决定硬盘内部传输率的关键因素之一，它的快慢在很大程度上影响了硬盘的速度。同时，转速的快慢也是区分硬盘档次的重要标志之一。硬盘的主轴马达带动盘片高速旋转，产生浮力使磁头飘浮在盘片上方。要将所要存取资料的扇区带到磁头下方，转速越快，等待时间也就越短。因此，转速在很大程度上决定了硬盘的速度。目前市场上常见的硬盘转速一般有5400rpm、7200rpm和15000rpm。理论上，转速越快越好，因为较高的转速可缩短硬盘的平均寻道时间和实际读写时间。但是转速越快发热量越大，不利于散热。现在的主流硬盘转速一般为15000rpm以上。
　　● 平均寻道时间(Average Seek Time)：指硬盘在盘面上移动读写头至指定磁道寻找相应目标数据所用的时间，它描述硬盘读取数据的能力，单位为毫秒。当单碟片容量增大时，磁头的寻道动作和移动距离减少，从而使平均寻道时间减少，加快硬盘速度。
　　● 平均延迟时间(Average Latency　Time)：指当磁头移动到数据所在的磁道后，然后等待所要的数据块继续转动到磁头下所用的时间。
　　● 平均访问时间(Average Access Time)：指磁头找到指定数据的平均时间，通常是平均寻道时间和平均延迟时间之和。平均访问时间最能够代表硬盘找到某一数据所用的时间，越短的平均访问时间越好。
　　为什么要讲硬盘呢？因为DB2数据库在工作时，一条SQL语句在经过优化器编译时，优化器会读取统计信息、数据库配置参数和相关硬件信息来为该条SQL语句生成最优的执行计划。其中的硬件信息包括表空间的transrate和overhead。这两个参数的计算就是由硬盘的相关属性来决定的。它们计算公式如下：
　　transrate=(1/传送速率)*1000/1024000*4096(假设用4KB页大小)
　　overhead=平均寻道时间+(((1/磁盘转速)*60*1000)/2)
　　而平均寻道时间、磁盘旋转速度和传送速率是由硬盘本身决定的。所以我们必须做合理的存储I/O设计以使优化器更好地工作。
　　2.1.2  磁盘阵列技术
　　RAID的全称是独立磁盘冗余阵列(Redundant Array of Independent Disks)。它通过将多个相对比较便宜的磁盘组合起来，并相互连接，同时都连到一个或多个计算机上，以组成一个磁盘组，从而使其性能和容量达到或超过一个价格更昂贵的大型磁盘。20年来，RAID 推出了一系列级别，包括RAID 0、RAID 1、RAID 2、RAID 3、RAID4、RAID 5，以及各种组合如 RAID 1+0等，其中应用最广泛的是RAID5和RAID10。
　　RAID 1+0
　　RAID-0能提供更好的性能，RAID-1能提供最佳的数据保护。如果把两者结合在一起，就能同时提供高性能和数据保护，但这也会同时提高磁盘阵列造价。
　　RAID-5
　　RAID-5不做全磁盘镜像，但它会对每一个写操作做奇偶校验计算并写入奇偶校验数据。奇偶校验磁盘避免了像RAID-1那样完全重复写数据。当一个磁盘失效，校验数据被用来重建数据，从而保证系统不会崩溃。为避免磁盘瓶颈，奇偶校验和数据都会被分布到阵列中的各个磁盘。尽管读的效率提高了，但是RAID-5需要为每个写操作做奇偶校验，因此它的写的效率很差。
　　2.1.3  存储的Cache
　　高端存储系统中除了要有高性能、高扩展性的结构外，Cache的设计将直接影响到存储系统的性能表现和可靠性。
　　Cache的主要作用是什么呢？作为缓存，Cache的作用具体体现在读与写两个不同的方面：作为写，一般存储阵列只要求数据写到Cache就算完成了写操作，当写Cache的数据积累到一定程度，阵列才把数据刷到磁盘，这样可以实现批量的写入。所以，阵列的写是非常快速的。至于Cache数据的保护，一般都依赖于镜像与电池(或者是UPS)。
　　Cache在读数据方面的作用同样不可忽视，因为如果所要读取的数据能在Cache中命中的话，将大大减少磁盘寻道所需要的时间。因为磁盘从开始寻道到找到数据，一般都在6ms以上，而这个时间，对于那些密集型I/O的应用可能不是太理想。但是，如果能在Cache保存的数据中命中，一般响应时间则可以缩短在1ms以内。存储的Cache大小对整个I/O性能的影响是非常大的。
　　2.1.4  IOPS
　　IOPS，即I/O Per Second，也就是每秒进行读写(I/O)操作的次数，它是衡量存储性能的一个重要指标。
　　2.1.5  网络存储技术
　　FC SAN(Storage Area Network，存储区域网)是一个高速的子网，这个子网中的设备可以从您的主网卸载流量。通常，SAN由RAID阵列连接光纤通道(Fibre Channel)组成，SAN同服务器和客户机的数据通信是通过SCSI命令而非TCP/IP来实现的，数据处理是“块级”(block level)。
　　SAN通过特定的互联方式连接若干台存储服务器而组成一个单独的数据网络，提供企业级的数据存储服务，如图2-2所示。 SAN是一种特殊的高速网络，连接网络服务器和诸如大磁盘阵列或备份磁带库的存储设备，SAN置于LAN之下，而不涉及LAN。利用SAN，不仅可以提供大容量的存储数据，而且地域上可以分散，缓解了大量数据传输对局域网的影响。SAN的结构允许任何服务器连接到任何存储阵列，不管数据存放在哪里，服务器都可直接存取所需的数据。
　　图2-2  SAN存储示意图
　　NAS是Network Attached Storage(网络附加存储)的简称。在NAS存储结构中，存储系统不再通过I/O总线附属于某个服务器或客户机，而是直接通过网络接口与网络直接相连，由用户通过网络访问。它是连接到一个计算机网络的文件层的数据存储，可以为不同网络的客户端提供数据存储服务。NAS的硬件与传统的专用文件服务器相似，它们的不同点在于软件端。NAS中的操作系统和其他软件只提供数据存储、数据访问功能，以及对这些功能的管理。与传统以服务器为中心的存储系统相比，数据不再通过服务器内存转发，而是直接在客户机和存储设备间传送，服务器仅起控制管理的作用。

第1章  性能调整概述 1
1.1  性能概述 2
1.2  性能评估 4
1.3  建立性能目标 7
1.4  什么时候需要做性能调整 8
1.5  性能调整准则 9
1.6  性能调整的方法和过程 10
1.6.1  性能调整的步骤 10
1.6.2  性能调整的限制 11
1.6.3  向客户了解情况 11
1.6.4  性能调整流程图 12
1.7  性能调整总结 15
第2章  存储I/O设计 19
2.1  存储基本概念 20
2.1.1  硬盘 20
2.1.2  磁盘阵列技术 21
2.1.3  存储的Cache 22
2.1.4  IOPS 22
2.1.5  网络存储技术 23
2.2  存储架构 24
2.2.1  存储I/O处理过程 24
2.2.2  应用系统I/O流动图 24
2.2.3  RAID IOPS 26
2.2.4  RAID 10和RAID 5的比较 28
2.3  存储相关性能调整案例 31
2.4  存储I/O设计总结 32
第3章  操作系统相关性能问题 35
3.1  HP-UX系统性能监控综述 35
3.1.1  监控资源对象和标准 35
3.1.2  监控工具 36
3.1.3  监控系统总体运行状态 36
3.1.4  性能状态的判定流程和监控命令 38
3.2  AIX性能监控综述 47
3.2.1  监控工具 47
3.2.2  监控系统总体运行状态 48
3.2.3  监控CPU性能 51
3.2.4  监控内存使用 55
3.2.5  监控存储系统状态 57
3.2.6  监控网络状态 58
3.3  操作系统性能优化 60
3.3.1  直接I/O和并发I/O 61
3.3.2  异步I/O和同步I/O 62
3.3.3  minpout和maxpout 65
3.3.4  文件系统和裸设备 65
3.3.5  负载均衡及条带化(Striping) 66
3.4  逻辑卷和lvmo优化 72
3.4.1  使用lvmo进行优化 73
3.4.2  卷组 pbuf 池 73
3.4.3  pbuf设置不合理导致性能问题调整案例 74
3.4.4  使用 ioo 进行优化 78
3.5  总结 83
第4章  数据库物理设计和逻辑设计 85
4.1  数据库物理设计 85
4.1.1  表空间容器放置原则 85
4.1.2  数据库物理设计原则 86
4.2  数据库逻辑设计 86
4.2.1  缓冲池设计原则 86
4.2.2  表空间设计原则 91
4.3  使用Autoconfig设计数据库 99
4.4  其他高级设计技术 102
4.4.1  表分区及应用案例 102
4.4.2  数据库分区及应用案例 104
4.4.3  多维群集(MDC)及应用案例 106
4.4.4  物化查询表及应用案例 110
4.4.5  MDC、数据库分区、MQT和表分区配合使用 114
4.4.6  表压缩及应用案例 125
4.4.7  表压缩应用案例二 132
4.4.8  XML及应用案例 140
4.5  数据库设计总结 142
4.5.1  表空间与表设计方面的考虑 142
4.5.2  索引设计方面的考虑 146
4.5.3  缓冲池方面的考虑 147
4.5.4  总结 148
第5章  DB2性能监控 149
5.1  快照监视器案例 149
5.1.1  监控动态SQL语句 149
5.1.2  监控临时表空间使用 152
5.2  事件监视器及监控案例 153
5.3  利用表函数监控 158
5.4  性能管理视图及案例 163
5.4.1  监控缓冲池命中率 165
5.4.2  监控Package Cache大小 165
5.4.3  监控执行成本最高的SQL语句 166
5.4.4  监控运行最长的SQL语句 166
5.4.5  监控SQL准备和预编译时间最长的SQL语句 167
5.4.6  监控执行次数最多的SQL语句 167
5.4.7  监控排序次数最多的SQL语句 168
5.4.8  监控LOCK WAIT等待时间 168
5.4.9  监控LOCK CHAIN 169
5.4.10  监控锁内存使用 170
5.4.11  监控锁升级、死锁和锁超时 170
5.4.12  监控全表扫描的SQL 171
5.4.13  检查page cleaners是否足够 171
5.4.14  监控prefecher是否足够 172
5.4.15  监控数据库内存使用 173
5.4.16  监控日志使用情况 173
5.4.17  监控占用日志空间最旧的交易 174
5.4.18  用SQL监控健康指示器 174
5.4.19  监控存储路径 175
5.4.20  追踪监控历史 176
5.5  db2pd 176
5.5.1  常用db2pd监控选项和示例 177
5.5.2  使用db2pd监控死锁案例 191
5.5.3  db2pd使用问题总结 196
5.6  db2mtrk及监控案例 197
5.7  本章小结 200
第6章  数据库配置参数调整 201
6.1  数据库配置参数 201
6.2  监控和调优实例(DBM)配置参数 203
6.2.1  代理程序相关配置参数 203
6.2.2  SHEAPTHRES 206
6.2.3  FCM_NUM_BUFFERS 206
6.2.4  SHEAPTHRES_SHR 207
6.2.5  INTRA_PARALLEL 208
6.2.6  MON_HEAP_SZ 208
6.2.7  QUERY_HEAP_SZ 208
6.3  监控和调优DB配置参数 208
6.3.1  缓冲池大小 209
6.3.2  日志缓冲区大小(LOGBUFSZ) 215
6.3.3  应用程序堆大小(APPHEAPSZ) 216
6.3.4  SORTHEAP和SHEAPTHRES 217
6.3.5  锁相关配置参数 219
6.3.6  活动应用程序的最大数目(MAXAPPLS) 223
6.3.7  PKGCACHESZ 224
6.3.8  CATALOGCACHE_SZ 224
6.3.9  异步页清除程序的数目(NUM_IOCLEANERS) 224
6.3.10  异步I/O 服务器的数目(NUM_IOSERVERS) 226
6.3.11  组提交数目(MINCOMMIT) 226
6.3.12  AVG_APPLS 228
6.3.13  CHNGPGS_THRESH(DB) 228
6.3.14  MAXFILOP 229
6.3.15  LOGPRIMARY、LOGSECOND和LOGFILSZ 229
6.3.16  STMTHEAP 229
6.3.17  DFT_QUERYOPT 229
6.3.18  UTIL_HEAP_SZ (DB) 230
6.4  调整DB2概要注册变量 230
6.4.1  DB2_PARALLEL_IO 230
6.4.2  DB2_EVALUNCOMMITTED 232
6.4.3  DB2_SKIPDELETED 232
6.4.4  DB2_SKIPINSERTED 232
6.4.5  DB2_USE_PAGE_CONTAINER_TAG 233
6.4.6  DB2_SELECTIVITY 233
6.5  内存自动调优 233
6.5.1  内存自动调优示例 234
6.5.2  启用内存自动调优及相关参数 235
6.6  总结 237
第7章  锁和并发 239
7.1  锁等待及调整案例 239
7.1.1  锁等待问题解决流程和步骤 240
7.1.2  捕获引起锁等待的SQL 242
7.1.3  利用db2pd捕获锁超时 244
7.2  锁升级及调整案例 248
7.2.1  监控锁升级 249
7.2.2  锁升级调整 250
7.3  死锁及调整案例 252
7.3.1  利用事件监视器监控死锁 253
7.3.2  死锁案例 255
7.3.3  最小化死锁建议 257
7.4  隔离级别与锁 257
7.4.1  可重复读(RR—Repeatable Read) 258
7.4.2  读稳定性(RS—Read Stability) 259
7.4.3  游标稳定性(CS—Cursor Stability) 261
7.4.4  未提交读(UR—Uncommitted Read) 263
7.4.5  隔离级别加锁总结 265
7.4.6  隔离级别总结 269
7.5  最大化并发性 271
7.5.1  选择合适的隔离级别 271
7.5.2  尽量避免锁等待、锁升级和死锁 271
7.5.3  设置合理的注册变量 271
7.6  锁相关的性能问题总结 280
7.7  锁与应用程序开发 282
7.8  本章小结 285
第8章  索引设计与优化 287
8.1  索引概念 287
8.1.1  索引优点 287
8.1.2  索引类型 289
8.2  索引结构 290
8.3  理解索引访问机制 293
8.4  索引设计 296
8.4.1  创建索引 296
8.4.2  创建集群索引 297
8.4.3  创建双向索引 297
8.4.4  完全索引访问(index access only) 299
8.4.5  与创建索引相关的问题 299
8.4.6  创建索引示例 300
8.5  索引创建原则与示例 301
8.5.1  索引与谓词 301
8.5.2  根据查询所使用的列建立索引 303
8.5.3  根据条件语句中谓词的选择度创建索引 304
8.5.4  避免在建有索引的列上使用函数 305
8.5.5  在那些需要被排序的列上创建索引 306
8.5.6  合理使用INCLUDE关键词创建索引 307
8.5.7  指定索引的排序属性 308
8.6  影响索引性能的相关配置 309
8.6.1  设置影响索引性能的配置参数 309
8.6.2  为索引指定不同的表空间 309
8.6.3  确保索引的集群度 310
8.6.4  使表和索引统计信息保持最新 310
8.6.5  重组索引 311
8.7  索引维护 311
8.7.1  异步索引清除(AIC) 312
8.7.2  联机索引整理碎片 314
8.8  DB2 Design Advisor(db2advis) 315
8.9  索引调整总结 319
8.9.1  索引设计总结 319
8.9.2  索引性能总结 321
第9章  DB2优化器 325
9.1  DB2优化器介绍 326
9.2  SQL语句执行过程 328
9.3  优化器组件和工作原理 331
9.3.1  查询重写方法和示例：谓词移动、合并和转换 331
9.3.2  查询重写示例：视图合并 332
9.3.3  查询器重写示例：消除DISTINCT 335
9.3.4  查询器重写示例：隐含谓词 336
9.4  扫描方式 337
9.4.1  全表扫描 337
9.4.2  索引扫描 338
9.5  连接方法 341
9.5.1  嵌套循环连接 343
9.5.2  合并连接 344
9.5.3  哈希(hash)连接 345
9.5.4  选择最佳连接的策略 346
9.6  优化级别 347
9.7  如何影响优化器来提高性能 348
9.7.1  使DB2统计信息保持最新 348
9.7.2  构建适当的索引 349
9.7.3  配置合理的数据库配置参数 350
9.7.4  选择合适的优化级别 351
9.7.5  合理的存储I/O设计 351
9.7.6  良好的应用程序设计和编码 352
9.8  优化器总结 352
第10章  统计信息更新与碎片整理 353
10.1  统计信息更新 353
10.1.1  统计信息的重要性 353
10.1.2  统计信息更新示例 357
10.1.3  LIKE STATISTICS统计信息更新 360
10.1.4  列组统计信息更新 361
10.1.5  分布统计信息更新 371
10.1.6  统计信息更新策略 377
10.2  碎片整理 379
10.2.1  碎片产生机制和影响 379
10.2.2  确定何时重组表和索引 380
10.2.3  执行表、索引检查是否需要做REORG 383
10.3  重新绑定程序包 385
10.4  本章小结 386
第11章  SQL语句调优 389
11.1  通过监控找出最消耗资源的SQL语句 389
11.2  通过解释工具分析SQL语句执行计划 390
11.2.1  解释表 391
11.2.2  Visual Explain(可视化解释) 392
11.2.3  db2expln 400
11.2.4  db2exfmt 403
11.2.5  各种解释工具比较 405
11.2.6  如何从解释信息中获取有价值的建议 406
11.3  理解SQL语句如何工作 406
11.3.1  理解谓词类型 406
11.3.2  排序和分组 410
11.3.3  连接方法 412
11.3.4  扫描方式 413
11.4  SQL调优案例 413
11.4.1  用一条语句即可做到时避免使用多条语句 413
11.4.2  合理使用NOT IN和NOT EXISTS 414
11.4.3  合理使用子查询减少数据扫描和利用索引 417
11.4.4  调整表的连接顺序，减小中间结果集的数据量 418
11.4.5  在有偏差数据的情况下使用参数标记时，指定选择性 420
11.4.6  SQL使用UDF代替查询中复杂的部分 420
11.4.7  从多个SQL语句到一个 SQL表达式 422
11.4.8  使用SQL一次处理一个集合语义 423
11.4.9  在无副作用的情况下，请使用SQL 函数 425
11.4.10  小结 426
11.5  提高应用程序性能 426
11.5.1  良好的SQL编码规则 426
11.5.2  提高SQL编程性能 428
11.5.3  改进游标性能 430
11.5.4  根据业务逻辑选择最低粒度的隔离级别 431
11.5.5  通过REOPT绑定选项来提高性能 431
11.5.6  统计信息、碎片整理和重新绑定 432
11.5.7  避免不必要的排序 433
11.5.8  在C/S环境中利用SQL存储过程降低网络开销 433
11.5.9  高并发环境下使用连接池 433
11.5.10  使用Design Advisor(db2advis)建议索引 434
11.5.11  提高批量删除、插入和更新速度 434
第12章  DB2调优案例、问题总结和技巧 437
12.1  调优案例一：某移动公司存储设计不当和SQL引起的I/O瓶颈 437
12.2  调优案例二：某银行知识库系统锁等待、锁升级引起性能瓶颈 444
12.3  调优案例三：某汽车制造商ERP系统通过调整统计信息提高性能 453
12.4  调优案例四：某农信社批量代收电费批处理慢调优案例 464
12.5  调优学习案例：利用压力测试程序学习DB2调优 468
后    记 501
参考文献 503