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

第1章　性能調(diào)整概述 1
1.1　性能概述 2
1.2　性能評(píng)估 4
1.3　建立性能目標(biāo) 7
1.4　什么時(shí)候需要做性能調(diào)整 8
1.5　性能調(diào)整準(zhǔn)則 9
1.6　性能調(diào)整的方法和過(guò)程 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章　存儲(chǔ)I/O設(shè)計(jì) 19
2.1　存儲(chǔ)基本概念 20
2.1.1　硬盤(pán) 20
2.1.2　磁盤(pán)陣列技術(shù) 21
2.1.3　存儲(chǔ)的Cache 22
2.1.4　IOPS 22
2.1.5　網(wǎng)絡(luò)存儲(chǔ)技術(shù) 23
2.2　存儲(chǔ)架構(gòu) 24
2.2.1　存儲(chǔ)I/O處理過(guò)程 24
2.2.2　應(yīng)用系統(tǒng)I/O流動(dòng)圖 24
2.2.3　RAID IOPS 26
2.2.4　RAID 10和RAID 5的比較 28
2.3　存儲(chǔ)相關(guān)性能調(diào)整案例 31
2.4　存儲(chǔ)I/O設(shè)計(jì)總結(jié) 32
第3章　操作系統(tǒng)相關(guān)性能問(wèn)題 35
3.1　HP-UX系統(tǒng)性能監(jiān)控綜述 35
3.1.1　監(jiān)控資源對(duì)象和標(biāo)準(zhǔn) 35
3.1.2　監(jiān)控工具 36
3.1.3　監(jiān)控系統(tǒng)總體運(yùn)行狀態(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)總體運(yùn)行狀態(tài) 48
3.2.3　監(jiān)控CPU性能 51
3.2.4　監(jiān)控內(nèi)存使用 55
3.2.5　監(jiān)控存儲(chǔ)系統(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　負(fù)載均衡及條帶化(Striping) 66
3.4　邏輯卷和lvmo優(yōu)化 72
3.4.1　使用lvmo進(jìn)行優(yōu)化 73
3.4.2　卷組 pbuf 池 73
3.4.3　pbuf設(shè)置不合理導(dǎo)致性能問(wèn)題調(diào)整案例 74
3.4.4　使用 ioo 進(jìn)行優(yōu)化 78
3.5　總結(jié) 83
第4章　數(shù)據(jù)庫(kù)物理設(shè)計(jì)和邏輯設(shè)計(jì) 85
4.1　數(shù)據(jù)庫(kù)物理設(shè)計(jì) 85
4.1.1　表空間容器放置原則 85
4.1.2　數(shù)據(jù)庫(kù)物理設(shè)計(jì)原則 86
4.2　數(shù)據(jù)庫(kù)邏輯設(shè)計(jì) 86
4.2.1　緩沖池設(shè)計(jì)原則 86
4.2.2　表空間設(shè)計(jì)原則 91
4.3　使用Autoconfig設(shè)計(jì)數(shù)據(jù)庫(kù) 99
4.4　其他高級(jí)設(shè)計(jì)技術(shù) 102
4.4.1　表分區(qū)及應(yīng)用案例 102
4.4.2　數(shù)據(jù)庫(kù)分區(qū)及應(yīng)用案例 104
4.4.3　多維群集(MDC)及應(yīng)用案例 106
4.4.4　物化查詢(xún)表及應(yīng)用案例 110
4.4.5　MDC、數(shù)據(jù)庫(kù)分區(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ù)庫(kù)設(shè)計(jì)總結(jié) 142
4.5.1　表空間與表設(shè)計(jì)方面的考慮 142
4.5.2　索引設(shè)計(jì)方面的考慮 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)控動(dòng)態(tài)SQL語(yǔ)句 149
5.1.2　監(jiān)控臨時(shí)表空間使用 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語(yǔ)句 166
5.4.4　監(jiān)控運(yùn)行最長(zhǎng)的SQL語(yǔ)句 166
5.4.5　監(jiān)控SQL準(zhǔn)備和預(yù)編譯時(shí)間最長(zhǎng)的SQL語(yǔ)句 167
5.4.6　監(jiān)控執(zhí)行次數(shù)最多的SQL語(yǔ)句 167
5.4.7　監(jiān)控排序次數(shù)最多的SQL語(yǔ)句 168
5.4.8　監(jiān)控LOCK WAIT等待時(shí)間 168
5.4.9　監(jiān)控LOCK CHAIN 169
5.4.10　監(jiān)控鎖內(nèi)存使用 170
5.4.11　監(jiān)控鎖升級(jí)、死鎖和鎖超時(shí) 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)控?cái)?shù)據(jù)庫(kù)內(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)控存儲(chǔ)路徑 175
5.4.20　追蹤監(jiān)控歷史 176
5.5　db2pd 176
5.5.1　常用db2pd監(jiān)控選項(xiàng)和示例 177
5.5.2　使用db2pd監(jiān)控死鎖案例 191
5.5.3　db2pd使用問(wèn)題總結(jié) 196
5.6　db2mtrk及監(jiān)控案例 197
5.7　本章小結(jié) 200
第6章　數(shù)據(jù)庫(kù)配置參數(shù)調(diào)整 201
6.1　數(shù)據(jù)庫(kù)配置參數(shù) 201
6.2　監(jiān)控和調(diào)優(yōu)實(shí)例(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　活動(dòng)應(yīng)用程序的最大數(shù)目(MAXAPPLS) 223
6.3.7　PKGCACHESZ 224
6.3.8　CATALOGCACHE_SZ 224
6.3.9　異步頁(yè)清除程序的數(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概要注冊(cè)變量 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)存自動(dòng)調(diào)優(yōu) 233
6.5.1　內(nèi)存自動(dòng)調(diào)優(yōu)示例 234
6.5.2　啟用內(nèi)存自動(dòng)調(diào)優(yōu)及相關(guān)參數(shù) 235
6.6　總結(jié) 237
第7章　鎖和并發(fā) 239
7.1　鎖等待及調(diào)整案例 239
7.1.1　鎖等待問(wèn)題解決流程和步驟 240
7.1.2　捕獲引起鎖等待的SQL 242
7.1.3　利用db2pd捕獲鎖超時(shí) 244
7.2　鎖升級(jí)及調(diào)整案例 248
7.2.1　監(jiān)控鎖升級(jí) 249
7.2.2　鎖升級(jí)調(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　隔離級(jí)別與鎖 257
7.4.1　可重復(fù)讀(RR—Repeatable Read) 258
7.4.2　讀穩(wěn)定性(RS—Read Stability) 259
7.4.3　游標(biāo)穩(wěn)定性(CS—Cursor Stability) 261
7.4.4　未提交讀(UR—Uncommitted Read) 263
7.4.5　隔離級(jí)別加鎖總結(jié) 265
7.4.6　隔離級(jí)別總結(jié) 269
7.5　最大化并發(fā)性 271
7.5.1　選擇合適的隔離級(jí)別 271
7.5.2　盡量避免鎖等待、鎖升級(jí)和死鎖 271
7.5.3　設(shè)置合理的注冊(cè)變量 271
7.6　鎖相關(guān)的性能問(wèn)題總結(jié) 280
7.7　鎖與應(yīng)用程序開(kāi)發(fā) 282
7.8　本章小結(jié) 285
第8章　索引設(shè)計(jì)與優(yōu)化 287
8.1　索引概念 287
8.1.1　索引優(yōu)點(diǎn) 287
8.1.2　索引類(lèi)型 289
8.2　索引結(jié)構(gòu) 290
8.3　理解索引訪問(wèn)機(jī)制 293
8.4　索引設(shè)計(jì) 296
8.4.1　創(chuàng)建索引 296
8.4.2　創(chuàng)建集群索引 297
8.4.3　創(chuàng)建雙向索引 297
8.4.4　完全索引訪問(wèn)(index access only) 299
8.4.5　與創(chuàng)建索引相關(guān)的問(wèn)題 299
8.4.6　創(chuàng)建索引示例 300
8.5　索引創(chuàng)建原則與示例 301
8.5.1　索引與謂詞 301
8.5.2　根據(jù)查詢(xún)所使用的列建立索引 303
8.5.3　根據(jù)條件語(yǔ)句中謂詞的選擇度創(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)計(jì)信息保持最新 310
8.6.5　重組索引 311
8.7　索引維護(hù) 311
8.7.1　異步索引清除(AIC) 312
8.7.2　聯(lián)機(jī)索引整理碎片 314
8.8　DB2 Design Advisor(db2advis) 315
8.9　索引調(diào)整總結(jié) 319
8.9.1　索引設(shè)計(jì)總結(jié) 319
8.9.2　索引性能總結(jié) 321
第9章　DB2優(yōu)化器 325
9.1　DB2優(yōu)化器介紹 326
9.2　SQL語(yǔ)句執(zhí)行過(guò)程 328
9.3　優(yōu)化器組件和工作原理 331
9.3.1　查詢(xún)重寫(xiě)方法和示例：謂詞移動(dòng)、合并和轉(zhuǎn)換 331
9.3.2　查詢(xún)重寫(xiě)示例：視圖合并 332
9.3.3　查詢(xún)器重寫(xiě)示例：消除DISTINCT 335
9.3.4　查詢(xún)器重寫(xiě)示例：隱含謂詞 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)化級(jí)別 347
9.7　如何影響優(yōu)化器來(lái)提高性能 348
9.7.1　使DB2統(tǒng)計(jì)信息保持最新 348
9.7.2　構(gòu)建適當(dāng)?shù)乃饕?349
9.7.3　配置合理的數(shù)據(jù)庫(kù)配置參數(shù) 350
9.7.4　選擇合適的優(yōu)化級(jí)別 351
9.7.5　合理的存儲(chǔ)I/O設(shè)計(jì) 351
9.7.6　良好的應(yīng)用程序設(shè)計(jì)和編碼 352
9.8　優(yōu)化器總結(jié) 352
第10章　統(tǒng)計(jì)信息更新與碎片整理 353
10.1　統(tǒng)計(jì)信息更新 353
10.1.1　統(tǒng)計(jì)信息的重要性 353
10.1.2　統(tǒng)計(jì)信息更新示例 357
10.1.3　LIKE STATISTICS統(tǒng)計(jì)信息更新 360
10.1.4　列組統(tǒng)計(jì)信息更新 361
10.1.5　分布統(tǒng)計(jì)信息更新 371
10.1.6　統(tǒng)計(jì)信息更新策略 377
10.2　碎片整理 379
10.2.1　碎片產(chǎn)生機(jī)制和影響 379
10.2.2　確定何時(shí)重組表和索引 380
10.2.3　執(zhí)行表、索引檢查是否需要做REORG 383
10.3　重新綁定程序包 385
10.4　本章小結(jié) 386
第11章　SQL語(yǔ)句調(diào)優(yōu) 389
11.1　通過(guò)監(jiān)控找出最消耗資源的SQL語(yǔ)句 389
11.2　通過(guò)解釋工具分析SQL語(yǔ)句執(zhí)行計(jì)劃 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　如何從解釋信息中獲取有價(jià)值的建議 406
11.3　理解SQL語(yǔ)句如何工作 406
11.3.1　理解謂詞類(lèi)型 406
11.3.2　排序和分組 410
11.3.3　連接方法 412
11.3.4　掃描方式 413
11.4　SQL調(diào)優(yōu)案例 413
11.4.1　用一條語(yǔ)句即可做到時(shí)避免使用多條語(yǔ)句 413
11.4.2　合理使用NOT IN和NOT EXISTS 414
11.4.3　合理使用子查詢(xún)減少數(shù)據(jù)掃描和利用索引 417
11.4.4　調(diào)整表的連接順序，減小中間結(jié)果集的數(shù)據(jù)量 418
11.4.5　在有偏差數(shù)據(jù)的情況下使用參數(shù)標(biāo)記時(shí)，指定選擇性 420
11.4.6　SQL使用UDF代替查詢(xún)中復(fù)雜的部分 420
11.4.7　從多個(gè)SQL語(yǔ)句到一個(gè) SQL表達(dá)式 422
11.4.8　使用SQL一次處理一個(gè)集合語(yǔ)義 423
11.4.9　在無(wú)副作用的情況下，請(qǐng)使用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　改進(jìn)游標(biāo)性能 430
11.5.4　根據(jù)業(yè)務(wù)邏輯選擇最低粒度的隔離級(jí)別 431
11.5.5　通過(guò)REOPT綁定選項(xiàng)來(lái)提高性能 431
11.5.6　統(tǒng)計(jì)信息、碎片整理和重新綁定 432
11.5.7　避免不必要的排序 433
11.5.8　在C/S環(huán)境中利用SQL存儲(chǔ)過(guò)程降低網(wǎng)絡(luò)開(kāi)銷(xiāo) 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)案例、問(wèn)題總結(jié)和技巧 437
12.1　調(diào)優(yōu)案例一：某移動(dòng)公司存儲(chǔ)設(shè)計(jì)不當(dāng)和SQL引起的I/O瓶頸 437
12.2　調(diào)優(yōu)案例二：某銀行知識(shí)庫(kù)系統(tǒng)鎖等待、鎖升級(jí)引起性能瓶頸 444
12.3　調(diào)優(yōu)案例三：某汽車(chē)制造商ERP系統(tǒng)通過(guò)調(diào)整統(tǒng)計(jì)信息提高性能 453
12.4　調(diào)優(yōu)案例四：某農(nóng)信社批量代收電費(fèi)批處理慢調(diào)優(yōu)案例 464
12.5　調(diào)優(yōu)學(xué)習(xí)案例：利用壓力測(cè)試程序?qū)W習(xí)DB2調(diào)優(yōu) 468
后　　記 501
參考文獻(xiàn) 503