DB2數(shù)據(jù)庫性能調(diào)整和優(yōu)化

第1章　性能調(diào)整概述 1
1.1　性能概述 2
1.2　性能評估 4
1.3　建立性能目標 7
1.4　什么時候需要做性能調(diào)整 8
1.5　性能調(diào)整準則 9
1.6　性能調(diào)整的方法和過程 10
1.6.1　性能調(diào)整的步驟 10
1.6.2　性能調(diào)整的限制 11
1.6.3　向客戶了解情況 11
1.6.4　性能調(diào)整流程圖 12
1.7　性能調(diào)整總結(jié) 15
第2章　存儲I/O設(shè)計 19
2.1　存儲基本概念 20
2.1.1　硬盤 20
2.1.2　磁盤陣列技術(shù) 21
2.1.3　存儲的Cache 22
2.1.4　IOPS 22
2.1.5　網(wǎng)絡(luò)存儲技術(shù) 23
2.2　存儲架構(gòu) 24
2.2.1　存儲I/O處理過程 24
2.2.2　應(yīng)用系統(tǒng)I/O流動圖 24
2.2.3　RAID IOPS 26
2.2.4　RAID 10和RAID 5的比較 28
2.3　存儲相關(guān)性能調(diào)整案例 31
2.4　存儲I/O設(shè)計總結(jié) 32
第3章　操作系統(tǒng)相關(guān)性能問題 35
3.1　HP-UX系統(tǒng)性能監(jiān)控綜述 35
3.1.1　監(jiān)控資源對象和標準 35
3.1.2　監(jiān)控工具 36
3.1.3　監(jiān)控系統(tǒng)總體運行狀態(tài) 36
3.1.4　性能狀態(tài)的判定流程和監(jiān)控命令 38
3.2　AIX性能監(jiān)控綜述 47
3.2.1　監(jiān)控工具 47
3.2.2　監(jiān)控系統(tǒng)總體運行狀態(tài) 48
3.2.3　監(jiān)控CPU性能 51
3.2.4　監(jiān)控內(nèi)存使用 55
3.2.5　監(jiān)控存儲系統(tǒng)狀態(tài) 57
3.2.6　監(jiān)控網(wǎng)絡(luò)狀態(tài) 58
3.3　操作系統(tǒng)性能優(yōu)化 60
3.3.1　直接I/O和并發(fā)I/O 61
3.3.2　異步I/O和同步I/O 62
3.3.3　minpout和maxpout 65
3.3.4　文件系統(tǒng)和裸設(shè)備 65
3.3.5　負載均衡及條帶化(Striping) 66
3.4　邏輯卷和lvmo優(yōu)化 72
3.4.1　使用lvmo進行優(yōu)化 73
3.4.2　卷組 pbuf 池 73
3.4.3　pbuf設(shè)置不合理導致性能問題調(diào)整案例 74
3.4.4　使用 ioo 進行優(yōu)化 78
3.5　總結(jié) 83
第4章　數(shù)據(jù)庫物理設(shè)計和邏輯設(shè)計 85
4.1　數(shù)據(jù)庫物理設(shè)計 85
4.1.1　表空間容器放置原則 85
4.1.2　數(shù)據(jù)庫物理設(shè)計原則 86
4.2　數(shù)據(jù)庫邏輯設(shè)計 86
4.2.1　緩沖池設(shè)計原則 86
4.2.2　表空間設(shè)計原則 91
4.3　使用Autoconfig設(shè)計數(shù)據(jù)庫 99
4.4　其他高級設(shè)計技術(shù) 102
4.4.1　表分區(qū)及應(yīng)用案例 102
4.4.2　數(shù)據(jù)庫分區(qū)及應(yīng)用案例 104
4.4.3　多維群集(MDC)及應(yīng)用案例 106
4.4.4　物化查詢表及應(yīng)用案例 110
4.4.5　MDC、數(shù)據(jù)庫分區(qū)、MQT和表分區(qū)配合使用 114
4.4.6　表壓縮及應(yīng)用案例 125
4.4.7　表壓縮應(yīng)用案例二 132
4.4.8　XML及應(yīng)用案例 140
4.5　數(shù)據(jù)庫設(shè)計總結(jié) 142
4.5.1　表空間與表設(shè)計方面的考慮 142
4.5.2　索引設(shè)計方面的考慮 146
4.5.3　緩沖池方面的考慮 147
4.5.4　總結(jié) 148
第5章　DB2性能監(jiān)控 149
5.1　快照監(jiān)視器案例 149
5.1.1　監(jiān)控動態(tài)SQL語句 149
5.1.2　監(jiān)控臨時表空間使用 152
5.2　事件監(jiān)視器及監(jiān)控案例 153
5.3　利用表函數(shù)監(jiān)控 158
5.4　性能管理視圖及案例 163
5.4.1　監(jiān)控緩沖池命中率 165
5.4.2　監(jiān)控Package Cache大小 165
5.4.3　監(jiān)控執(zhí)行成本最高的SQL語句 166
5.4.4　監(jiān)控運行最長的SQL語句 166
5.4.5　監(jiān)控SQL準備和預(yù)編譯時間最長的SQL語句 167
5.4.6　監(jiān)控執(zhí)行次數(shù)最多的SQL語句 167
5.4.7　監(jiān)控排序次數(shù)最多的SQL語句 168
5.4.8　監(jiān)控LOCK WAIT等待時間 168
5.4.9　監(jiān)控LOCK CHAIN 169
5.4.10　監(jiān)控鎖內(nèi)存使用 170
5.4.11　監(jiān)控鎖升級、死鎖和鎖超時 170
5.4.12　監(jiān)控全表掃描的SQL 171
5.4.13　檢查page cleaners是否足夠 171
5.4.14　監(jiān)控prefecher是否足夠 172
5.4.15　監(jiān)控數(shù)據(jù)庫內(nèi)存使用 173
5.4.16　監(jiān)控日志使用情況 173
5.4.17　監(jiān)控占用日志空間最舊的交易 174
5.4.18　用SQL監(jiān)控健康指示器 174
5.4.19　監(jiān)控存儲路徑 175
5.4.20　追蹤監(jiān)控歷史 176
5.5　db2pd 176
5.5.1　常用db2pd監(jiān)控選項和示例 177
5.5.2　使用db2pd監(jiān)控死鎖案例 191
5.5.3　db2pd使用問題總結(jié) 196
5.6　db2mtrk及監(jiān)控案例 197
5.7　本章小結(jié) 200
第6章　數(shù)據(jù)庫配置參數(shù)調(diào)整 201
6.1　數(shù)據(jù)庫配置參數(shù) 201
6.2　監(jiān)控和調(diào)優(yōu)實例(DBM)配置參數(shù) 203
6.2.1　代理程序相關(guān)配置參數(shù) 203
6.2.2　SHEAPTHRES 206
6.2.3　FCM_NUM_BUFFERS 206
6.2.4　SHEAPTHRES_SHR 207
6.2.5　INTRA_PARALLEL 208
6.2.6　MON_HEAP_SZ 208
6.2.7　QUERY_HEAP_SZ 208
6.3　監(jiān)控和調(diào)優(yōu)DB配置參數(shù) 208
6.3.1　緩沖池大小 209
6.3.2　日志緩沖區(qū)大小(LOGBUFSZ) 215
6.3.3　應(yīng)用程序堆大小(APPHEAPSZ) 216
6.3.4　SORTHEAP和SHEAPTHRES 217
6.3.5　鎖相關(guān)配置參數(shù) 219
6.3.6　活動應(yīng)用程序的最大數(shù)目(MAXAPPLS) 223
6.3.7　PKGCACHESZ 224
6.3.8　CATALOGCACHE_SZ 224
6.3.9　異步頁清除程序的數(shù)目(NUM_IOCLEANERS) 224
6.3.10　異步I/O 服務(wù)器的數(shù)目(NUM_IOSERVERS) 226
6.3.11　組提交數(shù)目(MINCOMMIT) 226
6.3.12　AVG_APPLS 228
6.3.13　CHNGPGS_THRESH(DB) 228
6.3.14　MAXFILOP 229
6.3.15　LOGPRIMARY、LOGSECOND和LOGFILSZ 229
6.3.16　STMTHEAP 229
6.3.17　DFT_QUERYOPT 229
6.3.18　UTIL_HEAP_SZ (DB) 230
6.4　調(diào)整DB2概要注冊變量 230
6.4.1　DB2_PARALLEL_IO 230
6.4.2　DB2_EVALUNCOMMITTED 232
6.4.3　DB2_SKIPDELETED 232
6.4.4　DB2_SKIPINSERTED 232
6.4.5　DB2_USE_PAGE_CONTAINER_TAG 233
6.4.6　DB2_SELECTIVITY 233
6.5　內(nèi)存自動調(diào)優(yōu) 233
6.5.1　內(nèi)存自動調(diào)優(yōu)示例 234
6.5.2　啟用內(nèi)存自動調(diào)優(yōu)及相關(guān)參數(shù) 235
6.6　總結(jié) 237
第7章　鎖和并發(fā) 239
7.1　鎖等待及調(diào)整案例 239
7.1.1　鎖等待問題解決流程和步驟 240
7.1.2　捕獲引起鎖等待的SQL 242
7.1.3　利用db2pd捕獲鎖超時 244
7.2　鎖升級及調(diào)整案例 248
7.2.1　監(jiān)控鎖升級 249
7.2.2　鎖升級調(diào)整 250
7.3　死鎖及調(diào)整案例 252
7.3.1　利用事件監(jiān)視器監(jiān)控死鎖 253
7.3.2　死鎖案例 255
7.3.3　最小化死鎖建議 257
7.4　隔離級別與鎖 257
7.4.1　可重復(fù)讀(RR—Repeatable Read) 258
7.4.2　讀穩(wěn)定性(RS—Read Stability) 259
7.4.3　游標穩(wěn)定性(CS—Cursor Stability) 261
7.4.4　未提交讀(UR—Uncommitted Read) 263
7.4.5　隔離級別加鎖總結(jié) 265
7.4.6　隔離級別總結(jié) 269
7.5　最大化并發(fā)性 271
7.5.1　選擇合適的隔離級別 271
7.5.2　盡量避免鎖等待、鎖升級和死鎖 271
7.5.3　設(shè)置合理的注冊變量 271
7.6　鎖相關(guān)的性能問題總結(jié) 280
7.7　鎖與應(yīng)用程序開發(fā) 282
7.8　本章小結(jié) 285
第8章　索引設(shè)計與優(yōu)化 287
8.1　索引概念 287
8.1.1　索引優(yōu)點 287
8.1.2　索引類型 289
8.2　索引結(jié)構(gòu) 290
8.3　理解索引訪問機制 293
8.4　索引設(shè)計 296
8.4.1　創(chuàng)建索引 296
8.4.2　創(chuàng)建集群索引 297
8.4.3　創(chuàng)建雙向索引 297
8.4.4　完全索引訪問(index access only) 299
8.4.5　與創(chuàng)建索引相關(guān)的問題 299
8.4.6　創(chuàng)建索引示例 300
8.5　索引創(chuàng)建原則與示例 301
8.5.1　索引與謂詞 301
8.5.2　根據(jù)查詢所使用的列建立索引 303
8.5.3　根據(jù)條件語句中謂詞的選擇度創(chuàng)建索引 304
8.5.4　避免在建有索引的列上使用函數(shù) 305
8.5.5　在那些需要被排序的列上創(chuàng)建索引 306
8.5.6　合理使用INCLUDE關(guān)鍵詞創(chuàng)建索引 307
8.5.7　指定索引的排序?qū)傩?308
8.6　影響索引性能的相關(guān)配置 309
8.6.1　設(shè)置影響索引性能的配置參數(shù) 309
8.6.2　為索引指定不同的表空間 309
8.6.3　確保索引的集群度 310
8.6.4　使表和索引統(tǒng)計信息保持最新 310
8.6.5　重組索引 311
8.7　索引維護 311
8.7.1　異步索引清除(AIC) 312
8.7.2　聯(lián)機索引整理碎片 314
8.8　DB2 Design Advisor(db2advis) 315
8.9　索引調(diào)整總結(jié) 319
8.9.1　索引設(shè)計總結(jié) 319
8.9.2　索引性能總結(jié) 321
第9章　DB2優(yōu)化器 325
9.1　DB2優(yōu)化器介紹 326
9.2　SQL語句執(zhí)行過程 328
9.3　優(yōu)化器組件和工作原理 331
9.3.1　查詢重寫方法和示例：謂詞移動、合并和轉(zhuǎn)換 331
9.3.2　查詢重寫示例：視圖合并 332
9.3.3　查詢器重寫示例：消除DISTINCT 335
9.3.4　查詢器重寫示例：隱含謂詞 336
9.4　掃描方式 337
9.4.1　全表掃描 337
9.4.2　索引掃描 338
9.5　連接方法 341
9.5.1　嵌套循環(huán)連接 343
9.5.2　合并連接 344
9.5.3　哈希(hash)連接 345
9.5.4　選擇最佳連接的策略 346
9.6　優(yōu)化級別 347
9.7　如何影響優(yōu)化器來提高性能 348
9.7.1　使DB2統(tǒng)計信息保持最新 348
9.7.2　構(gòu)建適當?shù)乃饕?349
9.7.3　配置合理的數(shù)據(jù)庫配置參數(shù) 350
9.7.4　選擇合適的優(yōu)化級別 351
9.7.5　合理的存儲I/O設(shè)計 351
9.7.6　良好的應(yīng)用程序設(shè)計和編碼 352
9.8　優(yōu)化器總結(jié) 352
第10章　統(tǒng)計信息更新與碎片整理 353
10.1　統(tǒng)計信息更新 353
10.1.1　統(tǒng)計信息的重要性 353
10.1.2　統(tǒng)計信息更新示例 357
10.1.3　LIKE STATISTICS統(tǒng)計信息更新 360
10.1.4　列組統(tǒng)計信息更新 361
10.1.5　分布統(tǒng)計信息更新 371
10.1.6　統(tǒng)計信息更新策略 377
10.2　碎片整理 379
10.2.1　碎片產(chǎn)生機制和影響 379
10.2.2　確定何時重組表和索引 380
10.2.3　執(zhí)行表、索引檢查是否需要做REORG 383
10.3　重新綁定程序包 385
10.4　本章小結(jié) 386
第11章　SQL語句調(diào)優(yōu) 389
11.1　通過監(jiān)控找出最消耗資源的SQL語句 389
11.2　通過解釋工具分析SQL語句執(zhí)行計劃 390
11.2.1　解釋表 391
11.2.2　Visual Explain(可視化解釋) 392
11.2.3　db2expln 400
11.2.4　db2exfmt 403
11.2.5　各種解釋工具比較 405
11.2.6　如何從解釋信息中獲取有價值的建議 406
11.3　理解SQL語句如何工作 406
11.3.1　理解謂詞類型 406
11.3.2　排序和分組 410
11.3.3　連接方法 412
11.3.4　掃描方式 413
11.4　SQL調(diào)優(yōu)案例 413
11.4.1　用一條語句即可做到時避免使用多條語句 413
11.4.2　合理使用NOT IN和NOT EXISTS 414
11.4.3　合理使用子查詢減少數(shù)據(jù)掃描和利用索引 417
11.4.4　調(diào)整表的連接順序，減小中間結(jié)果集的數(shù)據(jù)量 418
11.4.5　在有偏差數(shù)據(jù)的情況下使用參數(shù)標記時，指定選擇性 420
11.4.6　SQL使用UDF代替查詢中復(fù)雜的部分 420
11.4.7　從多個SQL語句到一個 SQL表達式 422
11.4.8　使用SQL一次處理一個集合語義 423
11.4.9　在無副作用的情況下，請使用SQL 函數(shù) 425
11.4.10　小結(jié) 426
11.5　提高應(yīng)用程序性能 426
11.5.1　良好的SQL編碼規(guī)則 426
11.5.2　提高SQL編程性能 428
11.5.3　改進游標性能 430
11.5.4　根據(jù)業(yè)務(wù)邏輯選擇最低粒度的隔離級別 431
11.5.5　通過REOPT綁定選項來提高性能 431
11.5.6　統(tǒng)計信息、碎片整理和重新綁定 432
11.5.7　避免不必要的排序 433
11.5.8　在C/S環(huán)境中利用SQL存儲過程降低網(wǎng)絡(luò)開銷 433
11.5.9　高并發(fā)環(huán)境下使用連接池 433
11.5.10　使用Design Advisor(db2advis)建議索引 434
11.5.11　提高批量刪除、插入和更新速度 434
第12章　DB2調(diào)優(yōu)案例、問題總結(jié)和技巧 437
12.1　調(diào)優(yōu)案例一：某移動公司存儲設(shè)計不當和SQL引起的I/O瓶頸 437
12.2　調(diào)優(yōu)案例二：某銀行知識庫系統(tǒng)鎖等待、鎖升級引起性能瓶頸 444
12.3　調(diào)優(yōu)案例三：某汽車制造商ERP系統(tǒng)通過調(diào)整統(tǒng)計信息提高性能 453
12.4　調(diào)優(yōu)案例四：某農(nóng)信社批量代收電費批處理慢調(diào)優(yōu)案例 464
12.5　調(diào)優(yōu)學習案例：利用壓力測試程序?qū)W習DB2調(diào)優(yōu) 468
后　　記 501
參考文獻 503