:DB2数据库性能调整和优化牛新庄 pdf下载

章 性能调整概述 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.1AIX性能监控综述 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.3minpout和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.3pbuf设置不合理导致性能

问题调整案例 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.2RAID IOPS 70

2.7.3RAID 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.5db2pd 113

3.5.1常用db2pd监控选项和

示例 114

3.5.2使用db2pd监控死锁

案例 126

3.5.3db2pd使用问题总结 132

3.6 内存监控 133

3.6.1db2pd 内存监控 133

3.6.2db2mtrk 内存监控 137

3.7 本章小结 139

第4章 DB2配置参数调整 141

4.1 初识DB2配置参数 141

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

配置参数 143

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

4.2.2sheapthres 145

4.2.3fcm_num_buffers 145

4.2.4sheapthres_shr 146

4.2.5intra_parallel 146

4.2.6mon_heap_sz 147

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

参数 147

4.3.1缓冲池大小 147

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

4.3.3应用程序堆大小

(applheapsz) 153

4.3.4sortheap和sheapthres_shr 154

4.3.5锁相关配置参数 157

4.3.6活动应用程序的大数目

(maxappls) 160

4.3.7pckcachesz 161

4.3.8catalogcache_sz 161

4.3.9异步页清除程序的数目

(num_iocleaners) 161

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

(num_ioservers) 163

4.3.11avg_appls 163

4.3.12chngpgs_thresh(DB) 164

4.3.13maxfilop 164

4.3.14logprimary、logsecond和

logfilsz 164

4.3.15stmtheap 165

4.3.16dft_queryopt 165

4.3.17util_heap_sz (DB) 165

4.4 调整DB2概要注册变量 166

4.4.1db2_parallel_io 166

4.4.2db2_evalunmitted 168

4.4.3 db2_skipdeleted 168

4.4.4db2_skipinserted 168

4.4.5db2_use_page_container_tag 168

4.4.6db2_selectivity 169

4.4.7db2maxfscrsearch 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 隔离级别(IsolationLevels) 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—Unmitted

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.8DB2 Design Advisor

(db2advis) 256

6.9 本章小结 260

第7章 DB2优化器 265

7.1DB2编译器介绍 266

7.2SQL语句编译过程 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.4LIKE 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.4REORG的用法和使用

策略 368

8.4 重新绑定程序包 371

8.5 本章小结 373

第9章 SQL语句调优 375

9.1 通过监控找出消耗资源

的SQL语句 376

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

执行计划 376

9.2.1解释表 377

9.2.2db2expln 378

9.2.3db2exfmt 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.4SQL调优案例 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

0章 DB2集群调优 421

10.1DB2集群介绍 421

10.2DB2集群参数解析 423

10.2.1组缓冲池 423

10.2.2全局锁管理器 425

10.2.3DB2 pureScale集群相关

参数 425

10.3DB2集群性能监控 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.4DB2集群设计调优 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

1章 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