第1 章DM8 性能優(yōu)化概述··············································································1
1.1 優(yōu)化的基本概念·················································································1
1.1.1 為什么優(yōu)化···············································································1
1.1.2 誰(shuí)來(lái)優(yōu)化··················································································2
1.1.3 優(yōu)化什么··················································································2
1.1.4 何時(shí)優(yōu)化··················································································4
1.1.5 優(yōu)化到什么程度·········································································6
1.2 優(yōu)化的基本思路·················································································7
1.2.1 數(shù)據(jù)庫(kù)全面健康檢查···································································7
1.2.2 優(yōu)化階段設(shè)計(jì)············································································7
1.2.3 數(shù)據(jù)庫(kù)配置···············································································8
1.2.4 添加新的應(yīng)用程序······································································8
1.2.5 運(yùn)行過(guò)程的優(yōu)化·········································································8
1.2.6 生產(chǎn)環(huán)境中優(yōu)化的特殊問(wèn)題··························································9
1.3 優(yōu)化的基本步驟··············································································.10
1.3.1 問(wèn)題分析···············································································.10
1.3.2 監(jiān)控系統(tǒng)性能·········································································.11
1.3.3 數(shù)據(jù)庫(kù)重演（REPLAY） ··························································.11
1.3.4 檢查數(shù)據(jù)物理一致性································································.12
1.3.5 優(yōu)化數(shù)據(jù)庫(kù)布局······································································.12
1.4 數(shù)據(jù)庫(kù)優(yōu)化誤區(qū)··············································································.13
第2 章 DM8 體系架構(gòu)·················································································.14
2.1 總體構(gòu)成·······················································································.14
2.1.1 數(shù)據(jù)庫(kù)··················································································.15
2.1.2 實(shí)例·····················································································.15
2.2 物理存儲(chǔ)結(jié)構(gòu)·················································································.15
2.2.1 配置文件···············································································.16
2.2.2 控制文件···············································································.16
2.2.3 數(shù)據(jù)文件···············································································.17
2.2.4 日志文件················································································18
2.2.5 備份文件················································································21
2.2.6 數(shù)據(jù)重演文件··········································································21
2.3 邏輯存儲(chǔ)結(jié)構(gòu)···················································································21
2.3.1 表空間···················································································21
2.3.2 段·························································································24
2.3.3 簇·························································································25
2.3.4 頁(yè)·························································································26
2.3.5 記錄······················································································27
2.4 DM8 內(nèi)存結(jié)構(gòu)··················································································27
2.4.1 內(nèi)存池···················································································27
2.4.2 緩沖區(qū)···················································································28
2.4.3 排序區(qū)···················································································30
2.4.4 哈希區(qū)···················································································31
2.5 DM8 線程結(jié)構(gòu)··················································································31
2.5.1 監(jiān)聽(tīng)線程················································································31
2.5.2 工作線程················································································31
2.5.3 I/O 線程··················································································32
2.5.4 調(diào)度線程················································································32
2.5.5 日志FLUSH 線程·····································································33
2.5.6 日志歸檔線程··········································································33
2.5.7 日志APPLY 線程······································································33
2.5.8 定時(shí)器線程·············································································33
2.5.9 邏輯日志歸檔線程····································································34
2.5.10 MAL 系統(tǒng)相關(guān)線程·································································34
2.5.11 其他線程···············································································34
2.5.12 線程信息的查看······································································34
2.6 工作過(guò)程·························································································35
2.6.1 DM8 的啟動(dòng)和關(guān)閉過(guò)程·····························································35
2.6.2 數(shù)據(jù)管理工作過(guò)程····································································37
第3 章DM8 調(diào)優(yōu)診斷工具············································································40
3.1 DEM 工具·······················································································40
3.1.1 DEM 工具部署·········································································40
3.1.2 DEM DMAgent 部署··································································42
3.1.3 功能操作················································································44
3.1.4 DEM 工具的應(yīng)用舉例································································46
3.2 SQL 跟蹤工具··················································································48
3.2.1 SQL 跟蹤日志··········································································48
3.2.2 SQL 性能分析工具····································································49
3.3 數(shù)據(jù)庫(kù)檢查工具（dmdbchk 工具） ······················································.50
3.3.1 功能簡(jiǎn)介···············································································.50
3.3.2 使用dmdbchk 工具··································································.51
3.3.3 查看dmdbchk 工具的參數(shù)·························································.51
3.3.4 dmdbchk 報(bào)告解讀···································································.52
3.3.5 應(yīng)用實(shí)例···············································································.52
3.4 性能統(tǒng)計(jì)信息·················································································.54
3.4.1 系統(tǒng)信息相關(guān)視圖···································································.55
3.4.2 存儲(chǔ)信息相關(guān)視圖···································································.55
3.4.3 內(nèi)存管理信息相關(guān)視圖·····························································.56
3.4.4 事務(wù)信息相關(guān)視圖···································································.56
3.4.5 線程信息相關(guān)視圖···································································.57
3.4.6 歷史模塊相關(guān)視圖···································································.57
3.4.7 緩存信息相關(guān)視圖···································································.57
3.4.8 會(huì)話信息相關(guān)視圖···································································.58
3.4.9 捕獲信息相關(guān)視圖···································································.58
3.5 AWR 報(bào)告······················································································.59
3.5.1 AWR 簡(jiǎn)介·············································································.59
3.5.2 DBMS_WORKLOAD_REPOSITORY 包········································.60
3.5.3 DBMS_WORKLOAD_REPOSITORY 的創(chuàng)建、檢測(cè)、刪除語(yǔ)句··········.63
3.5.4 AWR 快照應(yīng)用舉例·································································.64
第4 章DM8 實(shí)例優(yōu)化·················································································.65
4.1 實(shí)例優(yōu)化的相關(guān)概念········································································.65
4.1.1 達(dá)夢(mèng)優(yōu)化器············································································.65
4.1.2 統(tǒng)計(jì)信息···············································································.67
4.2 度量實(shí)例性能·················································································.69
4.2.1 數(shù)據(jù)庫(kù)命中率·········································································.70
4.2.2 數(shù)據(jù)庫(kù)等待統(tǒng)計(jì)數(shù)據(jù)································································.71
4.2.3 系統(tǒng)監(jiān)視···············································································.74
4.2.4 了解應(yīng)用程序·········································································.76
4.3 內(nèi)存調(diào)優(yōu)·························································································.77
4.3.1 共享內(nèi)存池············································································.77
4.3.2 BUFFER 緩沖區(qū)調(diào)優(yōu)································································.77
4.3.3 SQL 緩沖區(qū)調(diào)優(yōu)······································································.78
4.3.4 字典緩沖區(qū)調(diào)優(yōu)······································································.81
4.3.5 其他緩沖區(qū)調(diào)優(yōu)······································································.81
第5 章DM8 I/O 優(yōu)化··················································································.83
5.1 DM8 I/O 性能優(yōu)化概述·····································································.83
5.1.1 I/O 性能相關(guān)概念······································································83
5.1.2 影響I/O 性能的主要因素····························································85
5.1.3 I/O 優(yōu)化的主要措施···································································86
5.2 大表分區(qū)技術(shù)···················································································87
5.2.1 分區(qū)的概念·············································································87
5.2.2 分區(qū)的方法·············································································89
5.2.3 維護(hù)水平分區(qū)表·······································································98
5.2.4 水平分區(qū)表的限制·································································.101
5.2.5 生產(chǎn)環(huán)境下表分區(qū)實(shí)施方法·····················································.103
5.3 索引優(yōu)化技術(shù)················································································.104
5.3.1 索引的概念和分類(lèi)·································································.105
5.3.2 應(yīng)用索引進(jìn)行優(yōu)化·································································.106
5.4 數(shù)據(jù)庫(kù)空間碎片整理技術(shù)·································································.111
5.4.1 碎片整理的相關(guān)概念······························································.112
5.4.2 碎片評(píng)估方法·······································································.112
5.4.3 碎片整理方法·······································································.114
第6 章DM8 SQL 語(yǔ)句優(yōu)化········································································.116
6.1 DM8 SQL 語(yǔ)句優(yōu)化的相關(guān)概念·························································.116
6.1.1 DM8 SQL 語(yǔ)句的執(zhí)行過(guò)程·······················································.116
6.1.2 查詢(xún)優(yōu)化器··········································································.120
6.2 SQL 語(yǔ)句執(zhí)行計(jì)劃·········································································.122
6.2.1 執(zhí)行計(jì)劃的簡(jiǎn)介····································································.122
6.2.2 執(zhí)行計(jì)劃查看·······································································.123
6.2.3 常見(jiàn)的操作符介紹·································································.124
6.3 SQL 統(tǒng)計(jì)信息···············································································.124
6.3.1 統(tǒng)計(jì)信息簡(jiǎn)介·······································································.124
6.3.2 統(tǒng)計(jì)信息對(duì)執(zhí)行計(jì)劃的影響·····················································.126
6.3.3 統(tǒng)計(jì)信息的更新及查看···························································.129
6.4 DM8 的索引設(shè)計(jì)············································································.132
6.4.1 DM8 索引的存儲(chǔ)結(jié)構(gòu)·····························································.133
6.4.2 索引的更新及查看·································································.134
6.4.3 執(zhí)行計(jì)劃不使用索引的情況·····················································.135
6.5 DM8 SQL 優(yōu)化的基本步驟·······························································.138
6.5.1 確定優(yōu)化目標(biāo)·······································································.138
6.5.2 確定高負(fù)載的SQL 語(yǔ)句··························································.139
6.5.3 配置索引信息·······································································.139
6.6 DM8 SQL 優(yōu)化的基本方法·······························································.139
6.6.1 利用綁定變量提升性能···························································.139
6.6.2 開(kāi)發(fā)有效的SQL 語(yǔ)句·····························································.141
6.6.3 使用優(yōu)化器提示（HINT）·························································143
附錄A DM8 參數(shù)配置文件相關(guān)參數(shù)·······························································152
A.1 DM8 服務(wù)配置···············································································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 系統(tǒng)數(shù)據(jù)字典···········································································192
B.1 與Oracle 兼容數(shù)據(jù)字典····································································192
B.2 DM8 常用數(shù)據(jù)字典··········································································196
附錄C DM8 常用動(dòng)態(tài)性能視圖·····································································198
C.1 系統(tǒng)信息相關(guān)性能視圖·····································································198
C.2 進(jìn)程和線程相關(guān)性能視圖··································································198
C.3 數(shù)據(jù)庫(kù)信息性能視圖········································································199
C.4 數(shù)據(jù)庫(kù)配置參數(shù)相關(guān)性能視圖····························································200
C.5 會(huì)話信息相關(guān)性能視圖·····································································200
C.6 資源管理信息相關(guān)性能視圖·······························································202
C.7 段簇頁(yè)信息相關(guān)性能視圖··································································203
C.8 日志管理信息相關(guān)性能視圖·······························································203
C.9 事務(wù)和檢查點(diǎn)信息相關(guān)性能視圖·························································204
C.10 事件信息相關(guān)性能視圖···································································205
C.11 DSC 相關(guān)性能視圖·········································································205
C.12 數(shù)據(jù)守護(hù)相關(guān)性能視圖···································································206
C.13 其他性能視圖···············································································207
附錄D DM8 執(zhí)行計(jì)劃常用操作符··································································209