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

第1章　性能調整概述 1
1.1　性能概述 2
1.2　性能評估 4
1.3　建立性能目標 7
1.4　什么時候需要做性能調整 8
1.5　性能調整準則 9
1.6　性能調整的方法和過程 10
1.6.1　性能調整的步驟 10
1.6.2　性能調整的限制 11
1.6.3　向客戶了解情況 11
1.6.4　性能調整流程圖 12
1.7　性能調整總結 15
第2章　存儲I/O設計 19
2.1　存儲基本概念 20
2.1.1　硬盤 20
2.1.2　磁盤陣列技術 21
2.1.3　存儲的Cache 22
2.1.4　IOPS 22
2.1.5　網(wǎng)絡存儲技術 23
2.2　存儲架構 24
2.2.1　存儲I/O處理過程 24
2.2.2　應用系統(tǒng)I/O流動圖 24
2.2.3　RAID IOPS 26
2.2.4　RAID 10和RAID 5的比較 28
2.3　存儲相關性能調整案例 31
2.4　存儲I/O設計總結 32
第3章　操作系統(tǒng)相關性能問題 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)控內存使用 55
3.2.5　監(jiān)控存儲系統(tǒng)狀態(tài) 57
3.2.6　監(jiān)控網(wǎng)絡狀態(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)和裸設備 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設置不合理導致性能問題調整案例 74
3.4.4　使用 ioo 進行優(yōu)化 78
3.5　總結 83
第4章　數(shù)據(jù)庫物理設計和邏輯設計 85
4.1　數(shù)據(jù)庫物理設計 85
4.1.1　表空間容器放置原則 85
4.1.2　數(shù)據(jù)庫物理設計原則 86
4.2　數(shù)據(jù)庫邏輯設計 86
4.2.1　緩沖池設計原則 86
4.2.2　表空間設計原則 91
4.3　使用Autoconfig設計數(shù)據(jù)庫 99
4.4　其他高級設計技術 102
4.4.1　表分區(qū)及應用案例 102
4.4.2　數(shù)據(jù)庫分區(qū)及應用案例 104
4.4.3　多維群集(MDC)及應用案例 106
4.4.4　物化查詢表及應用案例 110
4.4.5　MDC、數(shù)據(jù)庫分區(qū)、MQT和表分區(qū)配合使用 114
4.4.6　表壓縮及應用案例 125
4.4.7　表壓縮應用案例二 132
4.4.8　XML及應用案例 140
4.5　數(shù)據(jù)庫設計總結 142
4.5.1　表空間與表設計方面的考慮 142
4.5.2　索引設計方面的考慮 146
4.5.3　緩沖池方面的考慮 147
4.5.4　總結 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準備和預編譯時間最長的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)控鎖內存使用 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ù)庫內存使用 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使用問題總結 196
5.6　db2mtrk及監(jiān)控案例 197
5.7　本章小結 200
第6章　數(shù)據(jù)庫配置參數(shù)調整 201
6.1　數(shù)據(jù)庫配置參數(shù) 201
6.2　監(jiān)控和調優(yōu)實例(DBM)配置參數(shù) 203
6.2.1　代理程序相關配置參數(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)控和調優(yōu)DB配置參數(shù) 208
6.3.1　緩沖池大小 209
6.3.2　日志緩沖區(qū)大小(LOGBUFSZ) 215
6.3.3　應用程序堆大小(APPHEAPSZ) 216
6.3.4　SORTHEAP和SHEAPTHRES 217
6.3.5　鎖相關配置參數(shù) 219
6.3.6　活動應用程序的最大數(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 服務器的數(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　調整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　內存自動調優(yōu) 233
6.5.1　內存自動調優(yōu)示例 234
6.5.2　啟用內存自動調優(yōu)及相關參數(shù) 235
6.6　總結 237
第7章　鎖和并發(fā) 239
7.1　鎖等待及調整案例 239
7.1.1　鎖等待問題解決流程和步驟 240
7.1.2　捕獲引起鎖等待的SQL 242
7.1.3　利用db2pd捕獲鎖超時 244
7.2　鎖升級及調整案例 248
7.2.1　監(jiān)控鎖升級 249
7.2.2　鎖升級調整 250
7.3　死鎖及調整案例 252
7.3.1　利用事件監(jiān)視器監(jiān)控死鎖 253
7.3.2　死鎖案例 255
7.3.3　最小化死鎖建議 257
7.4　隔離級別與鎖 257
7.4.1　可重復讀(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　隔離級別加鎖總結 265
7.4.6　隔離級別總結 269
7.5　最大化并發(fā)性 271
7.5.1　選擇合適的隔離級別 271
7.5.2　盡量避免鎖等待、鎖升級和死鎖 271
7.5.3　設置合理的注冊變量 271
7.6　鎖相關的性能問題總結 280
7.7　鎖與應用程序開發(fā) 282
7.8　本章小結 285
第8章　索引設計與優(yōu)化 287
8.1　索引概念 287
8.1.1　索引優(yōu)點 287
8.1.2　索引類型 289
8.2　索引結構 290
8.3　理解索引訪問機制 293
8.4　索引設計 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)建索引相關的問題 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關鍵詞創(chuàng)建索引 307
8.5.7　指定索引的排序屬性 308
8.6　影響索引性能的相關配置 309
8.6.1　設置影響索引性能的配置參數(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　索引調整總結 319
8.9.1　索引設計總結 319
8.9.2　索引性能總結 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　查詢重寫方法和示例：謂詞移動、合并和轉換 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　構建適當?shù)乃饕?349
9.7.3　配置合理的數(shù)據(jù)庫配置參數(shù) 350
9.7.4　選擇合適的優(yōu)化級別 351
9.7.5　合理的存儲I/O設計 351
9.7.6　良好的應用程序設計和編碼 352
9.8　優(yōu)化器總結 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　碎片產生機制和影響 379
10.2.2　確定何時重組表和索引 380
10.2.3　執(zhí)行表、索引檢查是否需要做REORG 383
10.3　重新綁定程序包 385
10.4　本章小結 386
第11章　SQL語句調優(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調優(yōu)案例 413
11.4.1　用一條語句即可做到時避免使用多條語句 413
11.4.2　合理使用NOT IN和NOT EXISTS 414
11.4.3　合理使用子查詢減少數(shù)據(jù)掃描和利用索引 417
11.4.4　調整表的連接順序，減小中間結果集的數(shù)據(jù)量 418
11.4.5　在有偏差數(shù)據(jù)的情況下使用參數(shù)標記時，指定選擇性 420
11.4.6　SQL使用UDF代替查詢中復雜的部分 420
11.4.7　從多個SQL語句到一個 SQL表達式 422
11.4.8　使用SQL一次處理一個集合語義 423
11.4.9　在無副作用的情況下，請使用SQL 函數(shù) 425
11.4.10　小結 426
11.5　提高應用程序性能 426
11.5.1　良好的SQL編碼規(guī)則 426
11.5.2　提高SQL編程性能 428
11.5.3　改進游標性能 430
11.5.4　根據(jù)業(yè)務邏輯選擇最低粒度的隔離級別 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)絡開銷 433
11.5.9　高并發(fā)環(huán)境下使用連接池 433
11.5.10　使用Design Advisor(db2advis)建議索引 434
11.5.11　提高批量刪除、插入和更新速度 434
第12章　DB2調優(yōu)案例、問題總結和技巧 437
12.1　調優(yōu)案例一：某移動公司存儲設計不當和SQL引起的I/O瓶頸 437
12.2　調優(yōu)案例二：某銀行知識庫系統(tǒng)鎖等待、鎖升級引起性能瓶頸 444
12.3　調優(yōu)案例三：某汽車制造商ERP系統(tǒng)通過調整統(tǒng)計信息提高性能 453
12.4　調優(yōu)案例四：某農信社批量代收電費批處理慢調優(yōu)案例 464
12.5　調優(yōu)學習案例：利用壓力測試程序學習DB2調優(yōu) 468
后　　記 501
參考文獻 503