達夢數據庫性能優化

第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