《達夢資料庫性能最佳化》是2022年4月電子工業出版社出版的圖書，作者是曾昭文 等。
基本介紹
- 書名：達夢資料庫性能最佳化
- 作者：曾昭文 等
- 出版社：電子工業出版社
- 出版時間：2022年4月
- 頁數：232 頁
- 定價：98 元
- 開本：16 開
- ISBN：9787121431135
內容簡介,圖書目錄,
內容簡介
本書以達夢資料庫DM8 為藍本，圍繞DM8 性能為什麼最佳化、誰來最佳化、最佳化什麼、何時最佳化、最佳化到什麼程度等問題進行詳細介紹，從DM8 體系架構入手，分析了DM8 的運行機制，從DM8 的實例最佳化、I/O 最佳化、SQL 語句最佳化3 個方面詳細地介紹了影響DM8 性能的核心問題。全書共6 章，內容包括DM8 性能最佳化概述、DM8體系架構、DM8 調優診斷工具、DM8 實例最佳化、DM8 I/O 最佳化和DM8 SQL 語句最佳化等。同時，本書還將DM8 參數配置檔案相關參數、DM8 系統數據字典和DM8 常用動態性能視圖作為附錄，便於廣大讀者查閱。本書內容實用、示例豐富、語言通俗、格式規範，可以作為計算機、資料庫等專業的教材，也可以作為工程技術人員的參考用書。
圖書目錄
第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
