本书以达梦数据库DM8 为蓝本，围绕DM8 性能为什么优化、谁来优化、优化什么、何时优化、优化到什么程度等问题进行详细介绍，从DM8 体系架构入手，分析了DM8 的运行机制，从DM8 的实例优化、I/O 优化、SQL 语句优化3 个方面详细地介绍了影响DM8 性能的核心问题。全书共6 章，内容包括DM8 性能优化概述、DM8体系架构、DM8 调优诊断工具、DM8 实例优化、DM8 I/O 优化和DM8 SQL 语句优化等。同时，本书还将DM8 参数配置文件相关参数、DM8 系统数据字典和DM8 常用动态性能视图作为附录，便于广大读者查阅。本书内容实用、示例丰富、语言通俗、格式规范，可以作为计算机、数据库等专业的教材，也可以作为工程技术人员的参考用书。


曾昭文，教授，主要从事数据工程和知识管理相关研究，主持军队重大科研项目10余项，先后获军队科技进步二等奖1项、三等奖多项，获军队教学成果二等奖1项，出版专著4部。


可作为计算机、数据库等专业的教材，也可作为工程技术人员的参考用书。

第1 章DM8 性能优化概述··············································································1
1.1 优化的基本概念·················································································1
1.1.1 为什么优化···············································································1
1.1.2 谁来优化··················································································2
1.1.3 优化什么··················································································2
1.1.4 何时优化··················································································4
1.1.5 优化到什么程度·········································································6
1.2 优化的基本思路·················································································7
1.2.1 数据库全面健康检查···································································7
1.2.2 优化阶段设计············································································7
1.2.3 数据库配置···············································································8
1.2.4 添加新的应用程序······································································8
1.2.5 运行过程的优化·········································································8
1.2.6 生产环境中优化的特殊问题··························································9
1.3 优化的基本步骤··············································································.10
1.3.1 问题分析···············································································.10
1.3.2 监控系统性能·········································································.11
1.3.3 数据库重演（REPLAY） ··························································.11
1.3.4 检查数据物理一致性································································.12
1.3.5 优化数据库布局······································································.12
1.4 数据库优化误区··············································································.13
第2 章 DM8 体系架构·················································································.14
2.1 总体构成·······················································································.14
2.1.1 数据库··················································································.15
2.1.2 实例·····················································································.15
2.2 物理存储结构·················································································.15
2.2.1 配置文件···············································································.16
2.2.2 控制文件···············································································.16
2.2.3 数据文件···············································································.17
2.2.4 日志文件················································································18
2.2.5 备份文件················································································21
2.2.6 数据重演文件··········································································21
2.3 逻辑存储结构···················································································21
2.3.1 表空间···················································································21
2.3.2 段·························································································24
2.3.3 簇·························································································25
2.3.4 页·························································································26
2.3.5 记录······················································································27
2.4 DM8 内存结构··················································································27
2.4.1 内存池···················································································27
2.4.2 缓冲区···················································································28
2.4.3 排序区···················································································30
2.4.4 哈希区···················································································31
2.5 DM8 线程结构··················································································31
2.5.1 监听线程················································································31
2.5.2 工作线程················································································31
2.5.3 I/O 线程··················································································32
2.5.4 调度线程················································································32
2.5.5 日志FLUSH 线程·····································································33
2.5.6 日志归档线程··········································································33
2.5.7 日志APPLY 线程······································································33
2.5.8 定时器线程·············································································33
2.5.9 逻辑日志归档线程····································································34
2.5.10 MAL 系统相关线程·································································34
2.5.11 其他线程···············································································34
2.5.12 线程信息的查看······································································34
2.6 工作过程·························································································35
2.6.1 DM8 的启动和关闭过程·····························································35
2.6.2 数据管理工作过程····································································37
第3 章DM8 调优诊断工具············································································40
3.1 DEM 工具·······················································································40
3.1.1 DEM 工具部署·········································································40
3.1.2 DEM DMAgent 部署··································································42
3.1.3 功能操作················································································44
3.1.4 DEM 工具的应用举例································································46
3.2 SQL 跟踪工具··················································································48
3.2.1 SQL 跟踪日志··········································································48
3.2.2 SQL 性能分析工具····································································49
3.3 数据库检查工具（dmdbchk 工具） ······················································.50
3.3.1 功能简介···············································································.50
3.3.2 使用dmdbchk 工具··································································.51
3.3.3 查看dmdbchk 工具的参数·························································.51
3.3.4 dmdbchk 报告解读···································································.52
3.3.5 应用实例···············································································.52
3.4 性能统计信息·················································································.54
3.4.1 系统信息相关视图···································································.55
3.4.2 存储信息相关视图···································································.55
3.4.3 内存管理信息相关视图·····························································.56
3.4.4 事务信息相关视图···································································.56
3.4.5 线程信息相关视图···································································.57
3.4.6 历史模块相关视图···································································.57
3.4.7 缓存信息相关视图···································································.57
3.4.8 会话信息相关视图···································································.58
3.4.9 捕获信息相关视图···································································.58
3.5 AWR 报告······················································································.59
3.5.1 AWR 简介·············································································.59
3.5.2 DBMS_WORKLOAD_REPOSITORY 包········································.60
3.5.3 DBMS_WORKLOAD_REPOSITORY 的创建、检测、删除语句··········.63
3.5.4 AWR 快照应用举例·································································.64
第4 章DM8 实例优化·················································································.65
4.1 实例优化的相关概念········································································.65
4.1.1 达梦优化器············································································.65
4.1.2 统计信息···············································································.67
4.2 度量实例性能·················································································.69
4.2.1 数据库命中率·········································································.70
4.2.2 数据库等待统计数据································································.71
4.2.3 系统监视···············································································.74
4.2.4 了解应用程序·········································································.76
4.3 内存调优·························································································.77
4.3.1 共享内存池············································································.77
4.3.2 BUFFER 缓冲区调优································································.77
4.3.3 SQL 缓冲区调优······································································.78
4.3.4 字典缓冲区调优······································································.81
4.3.5 其他缓冲区调优······································································.81
第5 章DM8 I/O 优化··················································································.83
5.1 DM8 I/O 性能优化概述·····································································.83
5.1.1 I/O 性能相关概念······································································83
5.1.2 影响I/O 性能的主要因素····························································85
5.1.3 I/O 优化的主要措施···································································86
5.2 大表分区技术···················································································87
5.2.1 分区的概念·············································································87
5.2.2 分区的方法·············································································89
5.2.3 维护水平分区表·······································································98
5.2.4 水平分区表的限制·································································.101
5.2.5 生产环境下表分区实施方法·····················································.103
5.3 索引优化技术················································································.104
5.3.1 索引的概念和分类·································································.105
5.3.2 应用索引进行优化·································································.106
5.4 数据库空间碎片整理技术·································································.111
5.4.1 碎片整理的相关概念······························································.112
5.4.2 碎片评估方法·······································································.112
5.4.3 碎片整理方法·······································································.114
第6 章DM8 SQL 语句优化········································································.116
6.1 DM8 SQL 语句优化的相关概念·························································.116
6.1.1 DM8 SQL 语句的执行过程·······················································.116
6.1.2 查询优化器··········································································.120
6.2 SQL 语句执行计划·········································································.122
6.2.1 执行计划的简介····································································.122
6.2.2 执行计划查看·······································································.123
6.2.3 常见的操作符介绍·································································.124
6.3 SQL 统计信息···············································································.124
6.3.1 统计信息简介·······································································.124
6.3.2 统计信息对执行计划的影响·····················································.126
6.3.3 统计信息的更新及查看···························································.129
6.4 DM8 的索引设计············································································.132
6.4.1 DM8 索引的存储结构·····························································.133
6.4.2 索引的更新及查看·································································.134
6.4.3 执行计划不使用索引的情况·····················································.135
6.5 DM8 SQL 优化的基本步骤·······························································.138
6.5.1 确定优化目标·······································································.138
6.5.2 确定高负载的SQL 语句··························································.139
6.5.3 配置索引信息·······································································.139
6.6 DM8 SQL 优化的基本方法·······························································.139
6.6.1 利用绑定变量提升性能···························································.139
6.6.2 开发有效的SQL 语句·····························································.141
6.6.3 使用优化器提示（HINT）·························································143
附录A DM8 参数配置文件相关参数·······························································152
A.1 DM8 服务配置···············································································152
A.1.1 dm.ini···················································································152
A.1.2 dmmal.ini ··············································································182
A.1.3 dmarch.ini ·············································································184
A.1.4 dm_svc.conf ···········································································185
A.1.5 sqllog.ini ···············································································187
A.1.6 dmrep.ini···············································································189
A.1.7 dmllog.ini ··············································································190
A.1.8 dmtimer.ini ············································································191
附录B DM8 系统数据字典···········································································192
B.1 与Oracle 兼容数据字典····································································192
B.2 DM8 常用数据字典··········································································196
附录C DM8 常用动态性能视图·····································································198
C.1 系统信息相关性能视图·····································································198
C.2 进程和线程相关性能视图··································································198
C.3 数据库信息性能视图········································································199
C.4 数据库配置参数相关性能视图····························································200
C.5 会话信息相关性能视图·····································································200
C.6 资源管理信息相关性能视图·······························································202
C.7 段簇页信息相关性能视图··································································203
C.8 日志管理信息相关性能视图·······························································203
C.9 事务和检查点信息相关性能视图·························································204
C.10 事件信息相关性能视图···································································205
C.11 DSC 相关性能视图·········································································205
C.12 数据守护相关性能视图···································································206
C.13 其他性能视图···············································································207
附录D DM8 执行计划常用操作符··································································209