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

主要特色◆ 本书从性能相关的基本原理入手，全面讲解了高性能系统设计、性能监控、调优工具、性能问题分析思路和解决方法。◆ 本书体现了作者多年调优经验的心得体会，精选了大量的实战案例，其中包含了近年大型银行系统实际用到的性能技巧，参考价值和实用性极高。◆ 涵盖了针对DB2 pureScale及GDPC(地理上分离的pureScale集群)的性能调优方法和实践。 

本书侧重于介绍DB2数据库的性能调优。性能调优是一个系统工程：全面监控分析操作系统、I/O性能、内存、应用及数据库才能快速找到问题根源；深刻理解DB2的锁及并发机制、索引原理、数据库参数、优化器原理、统计分析和碎片整理等内部机理才能针对性地快速提出解决问题的方法；快照、db2pd、db2expln以及各种管理视图和表函数等则是必须熟练掌握的工具。本书覆盖了进行DB2数据库性能调优所需的全部知识和工具，并提供了大量的性能调优的实际案例，这些案例都基于作者10多年积累的经验和总结，其中包括了近年来大型银行系统实际遇到的案例。本书还首次涵盖了针对DB2 pureScale及同城双活GDPC(地理上分离的pureScale集群)的性能调优方法和实践。
本书适合有一定DB2数据库基础知识和经验的数据库工程师，以及希望深入、全面地掌握DB2数据库性能分析和调优知识的读者，同时可以成为数据库软件开发人员开发高性能数据库软件的参考书。

牛新庄博士,现任中国民生银行信息科技部总经理，长期致力于银行业信息科技建设和管理工作。　　作为国内*的数据库资深专家，曾先后为建设银行、中信银行、交通银行、广东农信、青岛海尔等大型企业提供数据库顾问服务，为各行业的IT人才提供技术培训，在《程序员》杂志开辟数据库专栏，为从业者答疑解惑，广泛地推动了国内数据库技术的发展和信息技术的传播。　　作为国内信息科技领域的领军人物，积极推进移动互联、分布式、大数据、区块链等新技术领域创新，倾力打造满足业务飞速发展的信息系统。　　他还任浙江大学、武汉大学、对外经贸大学、北京交通大学等多家高校和科研机构的兼职教授，并担任银监会信息科技发展与风险管理专家、中国工程建设标准化协会信息通信专业委员会专家、中国DB2用户协会CDUG理事长、IBM信息管理客户顾问委员会主席、中央企业网络安全与信息化服务共享联盟智库首批专家等职务。　　此外，拥有OCP、AIX、DB2、HP-UX、MQ、CICS和WebSphere等二十多项国际认证，获得过国内数据库领域荣誉的“中国首届杰出数据库工程师”奖、首届IBM杰出软件专家奖、“IT 168技术卓越”奖等奖项，以及“中国杰出CIO”等荣誉称号。

第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进行优化 59

2.4  操作系统性能调整总结 64

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  性能管理视图及案例 97

3.4.1 
监控缓冲池命中率 99

3.4.2 
监控Package Cache大小 100

3.4.3 
监控执行成本的SQL

语句 100

3.4.4 
监控运行时间长的SQL

语句 101

3.4.5 
监控SQL准备和预编译

时间长的SQL语句 101

3.4.6 
监控执行次数多的SQL

语句 102

3.4.7 
监控排序次数多的SQL

语句 103

3.4.8 
监控锁等待时间 103

3.4.9 
监控Lock Chain 103

3.4.10 
监控锁内存的使用 106

3.4.11 
监控锁升级、死锁和

锁超时 107

3.4.12 
监控全表扫描的SQL 108

3.4.13 
检查页清理器是否足够 108

3.4.14 
监控prefecher是否足够 109

3.4.15 
监控数据库内存使用 110

3.4.16 
监控日志使用情况 111

3.4.17 
监控占用日志空间旧的

事务 111

3.4.18 
监控存储路径 112

3.4.19 
追踪监控历史 113

3.5 
db2pd 113

3.5.1 
常用db2pd监控选项和

示例 114

3.5.2 
使用db2pd监控死锁

案例 126

3.5.3 
db2pd使用问题总结 132

3.6  内存监控 133

3.6.1 
db2pd 内存监控 133

3.6.2 
db2mtrk 内存监控 137

3.7  本章小结 139

第4章  DB2配置参数调整 141

4.1  初识DB2配置参数 141

4.2  监控和调优实例级(DBM)

配置参数 143

4.2.1 
代理程序相关配置参数 143

4.2.2 
sheapthres 145

4.2.3 
fcm_num_buffers 145

4.2.4 
sheapthres_shr 146

4.2.5 
intra_parallel 146

4.2.6 
mon_heap_sz 147

4.3  监控和调优数据库级配置

参数 147

4.3.1 
缓冲池大小 147

4.3.2 
日志缓冲区大小(logbufsz) 152

4.3.3 
应用程序堆大小

(applheapsz) 153

4.3.4 
sortheap和sheapthres_shr 154

4.3.5 
锁相关配置参数 157

4.3.6 
活动应用程序的数目

(maxappls) 160

4.3.7 
pckcachesz 161

4.3.8 
catalogcache_sz 161

4.3.9 
异步页清除程序的数目

(num_iocleaners) 161

4.3.10 
异步I/O服务器的数目

(num_ioservers) 163

4.3.11 
avg_appls 163

4.3.12 
chngpgs_thresh(DB) 164

4.3.13 
maxfilop 164

4.3.14 
logprimary、logsecond和

logfilsz 164

4.3.15 
stmtheap 165

4.3.16 
dft_queryopt 165

4.3.17 
util_heap_sz (DB) 165

4.4  调整DB2概要注册变量 166

4.4.1 
db2_parallel_io 166

4.4.2 
db2_evaluncommitted 168

4.4.3  db2_skipdeleted 168

4.4.4 
db2_skipinserted 168

4.4.5 
db2_use_page_container_tag 168

4.4.6 
db2_selectivity 169

4.4.7 
db2maxfscrsearch 169

4.5  内存自动调优 169

4.5.1 
内存自动调优示例 170

4.5.2 
启用内存自动调优及

相关参数 171

4.5.3 
内存配置参数的配置原则 173

4.6  本章小结 174

第5章  锁和并发 175

5.1  锁的概念 176

5.1.1 
数据一致性 176

5.1.2 
事务和事务边界 176

5.1.3 
锁的概念 178

5.2  锁的属性、策略及模式
183

5.2.1 
锁的属性 183

5.2.2 
加锁策略 183

5.2.3 
锁的模式 184

5.2.4 
如何获取锁 186

5.2.5 
锁的兼容性 188

5.3  隔离级别(Isolation
Levels) 189

5.3.1 
可重复读(RR—Repeatable

Read) 189

5.3.2 
读稳定性(RS—Read

Stability) 191

5.3.3 
游标稳定性(CS—Cursor

Stability) 192

5.3.4 
当前已提交(Currently

Committed) 194

5.3.5 
未提交读(UR—Uncommitted

Read) 194

5.3.6 
隔离级别的摘要 196

5.4  锁转换、锁等待、锁升级和

死锁 198

5.4.1 
锁转换及调整案例 198

5.4.2 
锁升级及调整案例 200

5.4.3 
锁等待及调整案例 203

5.4.4 
死锁及调整案例 205

5.5  锁相关的性能问题总结
209

5.6  锁与应用程序设计 211

5.7  锁监控工具 214

5.8  化并发性 218

5.8.1 
选择合适的隔离级别 218

5.8.2 
尽量避免锁等待、锁升级和

死锁 218

5.8.3 
设置合理的注册变量 218

5.9  本章小结 227

第6章  索引设计与优化 229

6.1  索引概念 229

6.1.1 
索引优点 229

6.1.2 
索引类型 231

6.2  索引结构 231

6.3  理解索引访问机制 234

6.4  索引设计 237

6.4.1 
创建索引 237

6.4.2 
创建集群索引 238

6.4.3 
创建双向索引 239

6.4.4 
完全索引访问 240

6.4.5 
与创建索引相关的问题 241

6.4.6 
创建索引示例 241

6.5  索引创建原则与示例 242

6.5.1 
索引与谓词 242

6.5.2 
根据查询使用的列建立

索引 244

6.5.3 
根据条件语句中谓词的选择

度创建索引 245

6.5.4 
避免在建有索引的列上使用

函数 246

6.5.5 
在那些需要被排序的列上

创建索引 246

6.5.6 
合理使用INCLUDE关键词

创建索引 248

6.5.7 
指定索引的排序属性 249

6.6  影响索引性能的相关配置
250

6.6.1 
设置影响索引性能的配置

参数 250

6.6.2 
为索引指定不同的表空间 250

6.6.3 
确保索引的集群度 251

6.6.4 
使表和索引统计信息保持

251

6.7  索引维护 251

6.7.1 
异步索引清除(AIC) 252

6.7.2 
联机索引整理碎片 254

6.7.3 
查找使用率低下的索引 254

6.7.4 
索引压缩 256

6.8 
DB2 Design Advisor

(db2advis) 256

6.9  本章小结 260

第7章  DB2优化器 265

7.1 
DB2编译器介绍 266

7.2 
SQL语句编译过程 268

7.3  优化器组件和工作原理
271

7.3.1 
查询重写示例：谓词移动、

合并和转换 271

7.3.2 
优化器成本评估 276

7.3.3 
本地谓词基数(cardinality)

估计 277

7.3.4 
连接基数(cardinality)估计 279

7.3.5 
分布统计信息 283

7.3.6 
列组统计信息对基数的

影响 287

7.4  数据访问方式 297

7.4.1 
全表扫描 297

7.4.2 
索引扫描 298

7.4.3 
扫描共享 301

7.5  连接方法 302

7.5.1 
嵌套循环连接 303

7.5.2 
合并连接 305

7.5.3 
哈希连接 306

7.5.4 
选择连接的策略 307

7.6  优化级别 307

7.6.1 
优化级别概述 308

7.6.2 
选择优化级别 311

7.6.3 
设置优化级别
312

7.7  基于规则的优化 314

7.7.1 
优化器概要文件概述 314

7.7.2 
启用优化概要文件 316

7.7.3 
优化概要文件使用示例 317

7.8  如何影响优化器来提高性能
324

7.8.1 
使DB2统计信息保持

324

7.8.2 
构建适当的索引 324

7.8.3 
配置合理的数据库配置

参数 325

7.8.4 
选择合适的优化级别 326

7.8.5 
合理的存储I/O设计 326

7.8.6 
良好的应用程序设计和

编码 327

7.9  本章小结 329

第8章  统计信息更新与碎片整理
331

8.1  统计信息更新 332

8.1.1 
统计信息的重要性 332

8.1.2 
如何更新统计信息 333

8.1.3 
统计信息更新示例 335

8.1.4 
LIKE STATISTICS统计信息

更新 339

8.1.5 
列组统计信息更新 340

8.1.6 
分布统计信息更新 349

8.1.7 
统计信息更新策略 355

8.2  自动统计信息更新 357

8.2.1 
自动RUNSTATS的基本

概念 357

8.2.2 
如何打开auto  runstats 359

8.2.3 
如何监控auto  runstats 361

8.2.4 
自动收集统计视图的统计

信息 362

8.3  碎片整理 363

8.3.1 
碎片产生机制和影响 363

8.3.2 
确定何时重组表和索引 364

8.3.3 
执行表、索引检查是否需要

做REORG 367

8.3.4 
REORG的用法和使用

策略 368

8.4  重新绑定程序包 371

8.5  本章小结 373

第9章  SQL语句调优 375

9.1  通过监控找出消耗资源

的SQL语句 376

9.2  通过解释工具分析SQL语句

执行计划 376

9.2.1 
解释表 377

9.2.2 
db2expln 378

9.2.3 
db2exfmt 380

9.2.4 
各种解释工具的比较 382

9.2.5 
如何从解释信息中获取有

价值的建议 382

9.3  理解SQL语句如何工作 383

9.3.1 
理解谓词类型 383

9.3.2 
排序和分组 387

9.3.3 
连接方法 388

9.3.4 
扫描方式 389

9.4 
SQL调优案例 390

 

9.4.1 
尽量使用单条语句完成

逻辑 390

9.4.2 
合理使用NOT IN和NOT

EXISTS 391

9.4.3 
利用子查询进行优化 392

9.4.4 
调整表连接顺序使JOIN

394

9.4.5 
数据非均匀分布时手工指定

选择性 395

9.4.6 
使用UDF代替查询中的

复杂部分 396

9.4.7 
合并多条SQL语句到单个

SQL表达式 397

9.4.8 
使用SQL一次处理一个

集合语义 398

9.4.9 
在无副作用的情况下使用

SQL函数 400

9.4.10 
小结 401

9.5  提高应用程序性能 401

9.5.1 
良好的SQL编码规则 401

9.5.2 
提高SQL编程性能 403

9.5.3 
改进游标性能 405

9.5.4 
根据业务逻辑选择粒度

的隔离级别 406

9.5.5 
通过REOPT绑定选项来

提高性能 406

9.5.6 
统计信息、碎片整理和重新

绑定 407

9.5.7 
避免不必要的排序 408

9.5.8 
在C/S环境中利用SQL存储

过程降低网络开销 408

9.5.9 
在高并发环境下使用

连接池 408

9.5.10 
使用Design Advisor(db2advis)

建议索引 408

9.5.11 
提高批量删除、插入和更新

速度 409

9.5.12 
提高插入性能 409

9.5.13 
高效的SELECT语句 410

9.6  高性能SQL语句注意事项 412

9.6.1 
避免在搜索条件中使用复杂

的表达式 412

9.6.2 
将 OPTIMIZE FOR n ROWS

子句与FETCH FIRST n

ROWS ONLY子句配合

使用 412

9.6.3 
避免使用冗余的谓词 412

9.6.4 
避免使用多个带有DISTINCT

关键字的聚集操作 413

9.6.5 
避免连接列之间数据类型

不匹配 414

9.6.6 
避免对表达式使用连接

谓词 414

9.6.7 
避免在谓词中使用空操作

表达式来更改优化器估算 415

9.6.8 
确保查询符合星型模式连接

的必需条件 415

9.6.9 
避免使用非等式连接谓词 416

9.6.10 
避免使用不必要的外

连接 417

9.6.11 
使用参数标记来缩短动态

查询的编译时间 418

9.6.12 
使用约束来提高查询优化

程度 418

9.7  本章小结 419

第10章  DB2集群调优 421

10.1 
DB2集群介绍 421

10.2 
DB2集群参数解析 423

10.2.1 
组缓冲池 423

10.2.2 
全局锁管理器 425

10.2.3 
DB2 pureScale集群相关

参数 425

10.3 
DB2集群性能监控 429

10.3.1 
查看CF资源利用 429

10.3.2 
查看各个成员的负载

情况 430

10.3.3 
查看缓冲池命中率 430

10.3.4 
查看全局锁性能 438

10.3.5 
查看页回收(Page Reclaiming)

行为 441

10.4 
DB2集群设计调优 442

10.4.1 
使用小的pagesize 442

10.4.2 
使用大的extentsize 442

10.4.3 
使用LOB inline方法 442

10.4.4 
使用大的pctfree设置 443

10.4.5 
巧用CURRENT

MEMBER 443

10.4.6 
巧用随机索引 444

10.5 
同城双活集群介绍 444

10.6 
同城双活集群调优 446

10.6.1 
减少存储影响 446

10.6.2 
减少通信影响 447

10.6.3 
热点表调优案例 447

10.7 
本章小结 450

第11章  DB2调优案例、问题总结和

技巧 451

11.1 
调优案例1：某移动公司存储

设计不当和SQL引起的I/O

瓶颈 451

11.2 
调优案例2：某银行知识库

系统锁等待、锁升级引起

性能瓶颈 458

11.3 
调优案例3：某汽车制造商

ERP系统通过调整统计信息

提高性能 466

11.4 
调优案例4：某农信社批量代收

电费批处理慢调优案例 476

11.5 
调优案例5：某银行系统

字段类型定义错误导致

SQL执行时间变长 480

11.6 
调优案例6：某银行客户回单

系统CPU使用率高 483

11.7 
调优案例7：某银行手机银行

系统latch竞争导致active

session高、性能慢问题 488

11.8 
调优学习案例：利用压力

测试程序学习DB2调优 492

自1999年左右我开始从事数据库有关的技术工作到现在已近20年时间，此期间信息科技飞速发展，从无纸化办公和数据大集中到移动互联和大数据、人工智能、云计算等信息技术改变了生活，并颠覆了传统商业模式。信息科技的发展离不开数据处理技术的进步，在这一轮信息化浪潮中，数据处理技术也发生了翻天覆地的变化，对企业经营发展和对外服务的意义越来越重要。一方面，传统企业级数据库的能力，在原有的道路和方向上不断地持续提升演进，以满足企业市场不断迸发的各类需求。另一方面，互联网场景孕育的各种新兴的数据处理技术亦不断涌现，例如NewSQL、NoSQL、Hadoop等大数据处理技术，这些技术成为传统数据库产品的必然补充，同时也对传统数据库产品产生了一定的冲击。但是以我长期从事企业数据处理相关工作的经验看，在企业级市场尤其是金融企业市场里面，传统数据库产品的能力依然是解决企业主要业务需求的不二选择。因此，传统数据库技术的研究和应用仍然是信息科技工作的重点。

近年来传统数据库产品在不断改进升级，以支持更快的处理能力和更高的可用性，满足不同场景下的用户需求。DB2作为一款主流数据库产品，在这些方面也都进步明显，例如Purescale集群技术、跨数据中心的GDPC技术、列存储的BLU技术等创新功能就表现不俗，满足了特定业务场景需求，给企业带来了很大的价值提升。特别是GDPC技术，帮助企业搭建关键业务系统同城对等全双活生产架构，为终用户提供高等级容灾的连续服务，对企业对外服务的提升意义非凡，也使数据库从业者们领略了DB2产品创新的精华。

基于DB2产品的演进以及近些年的思考和实践，我重新梳理了之前编写的第2版的3本DB2系列技术图书，对其进行了大篇幅的修改和重写，力图对近些年实践的精华和DB2产品的新趋势进行总结。在此奉献给各位数据库从业的同仁，在技术的路上共勉。

由于本人水平有限，时间有限，书中不免有这样或者那样的错误，希望广大读者朋友不吝赐教指正！

后，感谢我的家人和同事在本书重写过程中的帮助，谢谢你们！

 

牛新庄