DB2数据库性能调整和优化（第2版）

第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
1.7.1  输入 17
1.7.2  处理 17
1.7.3  输出 23
1.8  本章小结 24
第2章  操作系统及存储的性能调优 27
2.1  AIX性能监控综述 29
2.1.1  监控工具 29
2.1.2  监控系统总体运行状态 30
2.1.3  监控CPU性能 34
2.1.4  监控内存使用 38
2.1.5  监控存储系统状态 40
2.1.6  监控网络状态 42
2.2  操作系统性能优化 43
2.2.1  直接I/O和并发I/O 44
2.2.2  异步I/O和同步I/O 45
2.2.3  minpout和maxpout 47
2.2.4  文件系统和裸设备 47
2.2.5  负载均衡及条带化
(Striping) 48
2.3  逻辑卷和lvmo优化 53
2.3.1  使用lvmo进行优化 54
2.3.2  卷组pbuf池 55
2.3.3  pbuf设置不合理导致性能
问题调整案例 56
2.3.4  使用ioo进行优化 60
2.4  操作系统性能调整总结 65
2.5  存储I/O设计 65
2.6  存储基本概念 65
2.6.1  硬盘 65
2.6.2  磁盘阵列技术 67
2.6.3  存储的Cache 67
2.6.4  网络存储技术 68
2.7  存储架构 69
2.7.1  存储I/O处理过程 69
2.7.2  RAID IOPS 70
2.7.3  RAID 10和RAID 5的
比较 71
2.8  良好存储规划的目标 74
2.9  良好存储规划的设计原则 75
2.10  存储相关性能调整案例 76
2.11  存储I/O性能调整总结 79
2.12  本章小结 80
第3章  DB2性能监控 81
3.1  快照监视器案例 81
3.1.1  监控动态SQL语句 81
3.1.2  监控临时表空间使用 84
3.2  事件监视器及监控案例 87
3.3  利用表函数监控 93
3.4  性能管理视图及案例 99
3.4.1  监控缓冲池命中率 100
3.4.2  监控Package Cache大小 101
3.4.3  监控执行成本最高的SQL
语句 102
3.4.4  监控运行时间最长的SQL
语句 102
3.4.5  监控SQL准备和预编译
时间最长的SQL语句 103
3.4.6  监控执行次数最多的SQL
语句 103
3.4.7  监控排序次数最多的SQL
语句 104
3.4.8  监控锁等待时间 104
3.4.9  监控Lock Chain 105
3.4.10  监控锁内存的使用 108
3.4.11  监控锁升级、死锁和
锁超时 108
3.4.12  监控全表扫描的SQL 109
3.4.13  检查页清理器是否足够 110
3.4.14  监控prefecher是否足够 110
3.4.15  监控数据库内存使用 111
3.4.16  监控日志使用情况 112
3.4.17  监控占用日志空间最旧的
事务 112
3.4.18  监控存储路径 113
3.4.19  追踪监控历史 114
3.5  db2pd 114
3.5.1  常用db2pd监控选项和
示例 115
3.5.2  使用db2pd监控死锁
案例 127
3.5.3  db2pd使用问题总结 132
3.6  内存监控 134
3.6.1  db2pd 内存监控 134
3.6.2  db2mtrk 内存监控 138
3.7  本章小结 140
第4章  DB2配置参数调整 141
4.1  初识DB2配置参数 141
4.2  监控和调优实例级(DBM)
配置参数 143
4.2.1  代理程序相关配置参数 143
4.2.2  sheapthres 146
4.2.3  fcm_num_buffers 146
4.2.4  sheapthres_shr 147
4.2.5  intra_parallel 147
4.2.6  mon_heap_sz 148
4.2.7  query_heap_sz 148
4.3  监控和调优数据库级配置
参数 148
4.3.1  缓冲池大小 149
4.3.2  日志缓冲区大小(logbufsz) 155
4.3.3  应用程序堆大小
(applheapsz) 156
4.3.4  sortheap和sheapthres_shr 157
4.3.5  锁相关配置参数 159
4.3.6  活动应用程序的最大数目
(maxappls) 163
4.3.7  pckcachesz 163
4.3.8  catalogcache_sz 164
4.3.9  异步页清除程序的数目
(num_iocleaners) 164 4.3.10  异步I/O 服务器的数目
(num_ioservers) 166
4.3.11  组提交数目(mincommit) 166
4.3.12  avg_appls 168
4.3.13  chngpgs_thresh(DB) 168
4.3.14  maxfilop 169
4.3.15  logprimary、logsecond和
logfilsz 169
4.3.16  stmtheap 170
4.3.17  dft_queryopt 170
4.3.18  util_heap_sz (DB) 170
4.4  调整DB2概要注册变量 170
4.4.1  db2_parallel_io 171
4.4.2  db2_evaluncommitted 173
4.4.3  db2_skipdeleted 173
4.4.4  db2_skipinserted 173
4.4.5  db2_use_page_
container_tag 173
4.4.6  db2_selectivity 173
4.4.7  db2_logger_non_
buffered_io 174
4.5  内存自动调优 174
4.5.1  内存自动调优示例 175
4.5.2  启用内存自动调优及
相关参数 176
4.5.3  内存配置参数的配置原则 178
4.6  本章小结 179
第5章  高级锁与优化 181
5.1  隔离级别与锁 181
5.1.1  可重复读(RR—Repeatable
Read) 182
5.1.2  读稳定性(RS—Read
Stability) 184
5.1.3  游标稳定性(CS—Cursor
Stability) 186
5.1.4  当前提交
(Currently Committed) 188
5.1.5  未提交读
(UR—Uncommitted Read) 190
5.1.6  隔离级别总结 193
5.2  加锁总结 195
5.2.1  如何获取锁 195
5.2.2  意图锁和非意图锁 196
5.2.3  读锁和写锁 197
5.2.4  LRB(Lock Resource Block) 197
5.2.5  USE AND KEEP LOCKS 198
5.2.6  索引类型和下一键锁 199
5.2.7  扫描方式与加锁 201
5.3  乐观锁 204
5.3.1  悲观锁和乐观锁 204
5.3.2  DB2 V9.5中的乐观锁 205
5.3.3  乐观锁应用案例 212
5.4  内部锁 221
5.4.1  内部方案锁
(Internal Plan Lock) 221
5.4.2  内部V锁(Internal Variation
Lock) 222
5.4.3  内部S锁 223
5.4.4  内部C锁 224
5.4.5  其他内部锁 225
5.5  锁等待及调整案例 228
5.5.1  锁等待问题解决流程和
步骤 228
5.5.2  捕获引起锁等待的SQL
语句 230
5.5.3  利用db2pd捕获锁超时 233
5.5.4  利用事件监视器捕获锁
超时 236
5.6  锁升级及调整案例 238
5.6.1  监控锁升级 239
5.6.2  锁升级调整 239
5.7  死锁及调整案例 241
5.7.1  利用事件监视器监控死锁 242
5.7.2  死锁案例 243
5.7.3  最小化死锁建议 246
5.8  最大化并发性 246
5.8.1  选择合适的隔离级别 246
5.8.2  尽量避免锁等待、锁升级和
死锁 246
5.8.3  设置合理的注册表变量 247
5.9  锁相关的性能问题总结 255
5.10  锁与应用程序开发 256
5.11  本章小结 259
第6章  索引设计与优化 261
6.1  索引概念 261
6.1.1  索引优点 261
6.1.2  索引类型 263
6.2  索引结构 263
6.3  理解索引访问机制 266
6.4  索引设计 269
6.4.1  创建索引 269
6.4.2  创建集群索引 270
6.4.3  创建双向索引 271
6.4.4  完全索引访问 272
6.4.5  与创建索引相关的问题 273
6.4.6  创建索引示例 273
6.5  索引创建原则与示例 274
6.5.1  索引与谓词 274 6.5.2  根据查询使用的列建立
索引 276
6.5.3  根据条件语句中谓词的
选择度创建索引 277
6.5.4  避免在建有索引的列上使用
函数 278
6.5.5  在那些需要被排序的列上
创建索引 278
6.5.6  合理使用INCLUDE关键词
创建索引 280
6.5.7  指定索引的排序属性 281
6.6  影响索引性能的相关配置 282
6.6.1  设置影响索引性能的配置
参数 282
6.6.2  为索引指定不同的表空间 282
6.6.3  确保索引的集群度 283
6.6.4  使表和索引统计信息保持
最新 283
6.6.5  重组索引 283
6.7  索引维护 284
6.7.1  异步索引清除(AIC) 285
6.7.2  联机索引整理碎片 287
6.7.3  查找使用率低下的索引 287
6.7.4  索引压缩 289
6.8  DB2 Design Advisor
(db2advis) 289
6.9  本章小结 293
第7章  DB2优化器 299
7.1  DB2优化器介绍 300
7.2  SQL语句执行过程 302
7.3  优化器组件和工作原理 304
7.3.1  查询重写示例：谓词移动、
合并和转换 305
7.3.2  优化器成本评估 310
7.3.3  本地谓词基数(cardinality)
估计 311
7.3.4  连接基数(cardinality)估计 313
7.3.5  分布统计信息 317
7.3.6  列组统计信息对基数的
影响 321
7.4  扫描方式 330
7.4.1  全表扫描 331
7.4.2  索引扫描 331
7.4.3  RID SCAN 334
7.5  连接方法 335
7.5.1  嵌套循环连接 336
7.5.2  合并连接 338
7.5.3  哈希连接 339
7.5.4  选择最佳连接的策略 340
7.6  优化级别 340
7.6.1  优化级别概述 341
7.6.2  选择优化级别 344
7.6.3  设置优化级别 345
7.7  基于规则的优化 347
7.7.1  优化器概要文件概述 347
7.7.2  启用优化概要文件 349
7.7.3  优化概要文件使用示例 350
7.8  如何影响优化器来提高性能 357
7.8.1  使DB2统计信息保持
最新 357
7.8.2  构建适当的索引 357
7.8.3  配置合理的数据库配置
参数 358
7.8.4  选择合适的优化级别 359
7.8.5  合理的存储I/O设计 359 7.8.6  良好的应用程序设计和
编码 360
7.9  本章小结 363
第8章  统计信息更新与碎片整理 365
8.1  统计信息更新 365
8.1.1  统计信息的重要性 365
8.1.2  统计信息更新示例 369
8.1.3  LIKE STATISTICS统计
信息更新 372
8.1.4  列组统计信息更新 374
8.1.5  分布统计信息更新 383
8.1.6  统计信息更新策略 389
8.2  自动统计信息更新 391
8.2.1  自动RUNSTATS的
基本概念 391
8.2.2  如何打开auto runstats 393
8.2.3  如何监控auto runstats 395
8.2.4  DB2 V10新特性——自动
收集统计视图的统计信息 396
8.3  碎片整理 397
8.3.1  碎片产生机制和影响 397
8.3.2  确定何时重组表和索引 398
8.3.3  执行表、索引检查是否
需要做REORG 401
8.3.4  REORG的用法和使用
策略 402
8.4  重新绑定程序包 405
8.5  本章小结 407
第9章  SQL语句调优 409
9.1  通过监控找出最消耗资源的
SQL语句 409
9.2  通过解释工具分析SQL语句
执行计划 410
9.2.1  解释表 411
9.2.2  Visual Explain
(可视化解释) 412
9.2.3  db2expln 419
9.2.4  db2exfmt 422
9.2.5  各种解释工具的比较 424
9.2.6  如何从解释信息中获取有
价值的建议 424
9.3  理解SQL语句如何工作 425
9.3.1  理解谓词类型 425
9.3.2  排序和分组 428
9.3.3  连接方法 430
9.3.4  扫描方式 431
9.4  SQL调优案例 431
9.4.1  尽量使用单条语句完成
逻辑 431
9.4.2  合理使用NOT IN和NOT
EXISTS 432
9.4.3  利用子查询进行优化 434
9.4.4  调整表连接顺序使JOIN
最优 436
9.4.5  数据非均匀分布时手工指定
选择性 437
9.4.6  使用UDF代替查询中的
复杂部分 438
9.4.7  合并多条SQL语句到单个
SQL表达式 439
9.4.8  使用SQL一次处理一个集合
语义 440
9.4.9  在无副作用的情况下使用
SQL函数 442
9.4.10  小结 443
9.5  提高应用程序性能 443
9.5.1  良好的SQL编码规则 443
9.5.2  提高SQL编程性能 445
9.5.3  改进游标性能 447
9.5.4  根据业务逻辑选择最低粒
度的隔离级别 448
9.5.5  通过REOPT绑定选项来
提高性能 448
9.5.6  统计信息、碎片整理和重新
绑定 449
9.5.7  避免不必要的排序 449
9.5.8  在C/S环境中利用SQL存储
过程降低网络开销 450
9.5.9  在高并发环境下使用
连接池 450
9.5.10  使用Design Advisor(db2advis)
建议索引 450
9.5.11  提高批量删除、插入和
更新速度 451
9.5.12  提高插入性能 451
9.5.13  高效的SELECT语句 452
9.6  高性能SQL语句注意事项 453
9.6.1  避免在搜索条件中使用
复杂的表达式 453
9.6.2  将 OPTIMIZE FOR n ROWS
子句与FETCH FIRST n
ROWS ONLY子句配合
使用 454
9.6.3  避免使用冗余的谓词 454
9.6.4  避免使用多个带有DISTINCT
关键字的聚集操作 455
9.6.5  避免连接列之间数据类型
不匹配 456
9.6.6  避免对表达式使用连接
谓词 456
9.6.7  避免在谓词中使用空操作表
达式来更改优化器估算 456
9.6.8  确保查询符合星型模式连接
的必需条件 457
9.6.9  避免使用非等式连接谓词 458
9.6.10  避免使用不必要的
外连接 459
9.6.11  使用参数标记来缩短动态
查询的编译时间 459
9.6.12  使用约束来提高查询优化
程度 460
9.7  本章小结 461
第10章  DB2调优案例、问题总结和
技巧 463
10.1  调优案例1：某移动公司存储
设计不当和SQL引起的I/O
瓶颈 463
10.2  调优案例2：某银行知识库系统
锁等待、锁升级引起性能
瓶颈 470
10.3  调优案例3：某汽车制造商ERP
系统通过调整统计信息提高
性能 478
10.4  调优案例4：某农信社批量代收
电费批处理慢调优案例 488
10.5  调优案例5：某银行系统SQL
执行慢，通过跟踪信息获取
调整信息 492
10.6  调优案例6：某银行系统字段
类型定义错误导致SQL执行
时间变长 495
10.7  调优学习案例：利用压力测试
程序学习DB2调优 498