DB2 數據庫性能調整和優化, 3/e

牛新莊博士,現任中國民生銀行信息科技部總經理，長期致力於銀行業信息科技建設和管理工作。
作為的數據庫資深專家，曾先後為建設銀行、中信銀行、交通銀行、廣東農信、青島海爾等大型企業提供數據庫顧問服務，為各行業的IT人才提供技術培訓，在《程序員》雜誌開闢數據庫專欄，為從業者答疑解惑，廣泛地推動了數據庫技術的發展和信息技術的傳播。
作為信息科技領域的領軍人物，積極推進移動互聯、分佈式、大數據、區塊鍊等新技術領域創新，傾力打造滿足業務飛速發展的信息系統。
他還任浙江大學、武漢大學、對外經貿大學、北京交通大學等多家高校和科研機構的兼職教授，並擔任銀監會信息科技發展與風險管理專家、中國工程建設標準化協會信息通信專業委員會專家、中國DB2用戶協會CDUG理事長、IBM信息管理客戶顧問委員會主席、中央企業網絡安全與信息化服務共享聯盟智庫首批專家等職務。
此外，擁有OCP、AIX、DB2、HP-UX、MQ、CICS和WebSphere等二十多項國際認證，獲得過數據庫領域榮譽的“中國首屆傑出數據庫工程師”獎、首屆IBM傑出軟件專家獎、“IT 168技術”獎等獎項，以及“中國傑出CIO”等榮譽稱號。

第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
1.7.1輸入17
1.7.2處理17
1.7.3輸出23
1.8 本章小結24

第2章操作系統及存儲的性能調優27
2.1AIX性能監控綜述29
2.1.1監控工具29
2.1.2監控系統總體運行狀態30
2.1.3監控CPU性能34
2.1.4監控內存使用38
2.1.5監控存儲系統狀態40
2.1.6監控網絡狀態42
2.2 操作系統性能優化43
2.2.1直接I/O和並發I/O 44
2.2.2異步I/O和同步I/O 45
2.2.3minpout和maxpout 47
2.2.4文件系統和裸設備47
2.2.5負載均衡及條帶化(Striping) 48
2.3 邏輯捲和lvmo優化53
2.3.1使用lvmo進行優化54
2.3.2卷組pbuf池55
2.3.3pbuf設置不合理導致性能問題調整案例56
2.3.4使用ioo進行優化59
2.4 操作系統性能調整總結64
2.5 存儲I/O設計65
2.6 存儲基本概念65
2.6.1硬盤65
2.6.2磁盤陣列技術67
2.6.3存儲的Cache 67
2.6.4網絡存儲技術68
2.7 存儲架構69
2.7.1存儲I/O處理過程69
2.7.2RAID IOPS 70
2.7.3RAID 10和RAID 5的比較71
2.8 良好存儲規劃的目標74
2.9 良好存儲規劃的設計原則75
2.10存儲相關性能調整案例76
2.11存儲I/O性能調整總結79
2.12本章小結80

第3章DB2性能監控81
3.1 快照監視器案例81
3.1.1監控動態SQL語句81
3.1.2監控臨時表空間使用84
3.2 事件監視器及監控案例87
3.3 利用表函數監控93
3.4 性能管理視圖及案例97
3.4.1監控緩衝池命中率99
3.4.2監控Package Cache大小100
3.4.3監控執行成本的SQL語句100
3.4.4監控運行時間的SQL語句101
3.4.5監控SQL準備和預編譯時間的SQL語句101
3.4.6監控執行次數多的SQL語句102
3.4.7監控排序次數多的SQL語句103
3.4.8監控鎖等待時間103
3.4.9監控Lock Chain 103
3.4.10監控鎖內存的使用106
3.4.11監控鎖升級、死鎖和鎖超時107
3.4.12監控全表掃描的SQL 108
3.4.13檢查頁清理器是否足夠108
3.4.14監控prefecher是否足夠109
3.4.15監控數據庫內存使用110
3.4.16監控日誌使用情況111
3.4.17監控佔用日誌空間舊的事務111
3.4.18監控存儲路徑112
3.4.19追踪監控歷史113
3.5db2pd 113
3.5.1常用db2pd監控選項和示例114
3.5.2使用db2pd監控死鎖案例126
3.5.3db2pd使用問題總結132
3.6 內存監控133
3.6.1db2pd 內存監控133
3.6.2db2mtrk 內存監控137
3.7 本章小結139

第4章DB2配置參數調整141
4.1 初識DB2配置參數141
4.2 監控和調優實例級(DBM)配置參數143
4.2.1代理程序相關配置參數143
4.2.2sheapthres 145
4.2.3fcm_num_buffers 145
4.2.4sheapthres_shr 146
4.2.5intra_parallel 146
4.2.6mon_heap_sz 147
4.3 監控和調優數據庫級配置參數147
4.3.1緩衝池大小147
4.3.2日誌緩衝區大小(logbufsz) 152
4.3.3應用程序堆大小(applheapsz) 153
4.3.4sortheap和sheapthres_shr 154
4.3.5鎖相關配置參數157
4.3.6活動應用程序的數目(maxappls) 160
4.3.7pckcachesz 161
4.3.8catalogcache_sz 161
4.3.9異步頁清除程序的數目(num_iocleaners) 161
4.3.10異步I/O服務器的數目(num_ioservers) 163
4.3.11avg_appls 163
4.3.12chngpgs_thresh(DB) 164
4.3.13maxfilop 164
4.3.14logprimary、logsecond和logfilsz 164
4.3.15stmtheap 165
4.3.16dft_queryopt 165
4.3.17util_heap_sz (DB) 165
4.4 調整DB2概要註冊變量166
4.4.1db2_parallel_io 166
4.4.2db2_evalunmitted 168
4.4.3 db2_skipdeleted 168
4.4.4db2_skipinserted 168
4.4.5db2_use_page_container_tag 168
4.4.6db2_selectivity 169
4.4.7db2maxfscrsearch 169
4.5 內存自動調優169
4.5.1內存自動調優示例170
4.5.2啟用內存自動調優及相關參數171
4.5.3內存配置參數的配置原則173
4.6 本章小結174

第5章鎖和並發175
5.1 鎖的概念176
5.1.1數據一致性176
5.1.2事務和事務邊界176
5.1.3鎖的概念178
5.2 鎖的屬性、策略及模式183
5.2.1鎖的屬性183
5.2.2加鎖策略183
5.2.3鎖的模式184
5.2.4如何獲取鎖186
5.2.5鎖的兼容性188
5.3 隔離級別(IsolationLevels) 189
5.3.1可重複讀(RR—RepeatableRead) 189
5.3.2讀穩定性(RS—ReadStability) 191
5.3.3游標穩定性(CS—CursorStability) 192
5.3.4當前已提交(CurrentlyCommitted) 194
5.3.5未提交讀(UR—UnmittedRead) 194
5.3.6隔離級別的摘要196
5.4 鎖轉換、鎖等待、鎖升級和死鎖198
5.4.1鎖轉換及調整案例198
5.4.2鎖升級及調整案例200
5.4.3鎖等待及調整案例203
5.4.4死鎖及調整案例205
5.5 鎖相關的性能問題總結209
5.6 鎖與應用程序設計211
5.7 鎖監控工具214
5.8 化並發性218
5.8.1選擇合適的隔離級別218
5.8.2盡量避免鎖等待、鎖升級和死鎖218
5.8.3設置合理的註冊變量218
5.9 本章小結227

第6章索引設計與優化229
6.1 索引概念229
6.1.1索引優點229
6.1.2索引類型231
6.2 索引結構231
6.3 理解索引訪問機制234
6.4 索引設計237
6.4.1創建索引237
6.4.2創建集群索引238
6.4.3創建雙向索引239
6.4.4完全索引訪問240
6.4.5與創建索引相關的問題241
6.4.6創建索引示例241
6.5 索引創建原則與示例242
6.5.1索引與謂詞242
6.5.2根據查詢使用的列建立索引244
6.5.3根據條件語句中謂詞的選擇度創建索引245
6.5.4避免在建有索引的列上使用函數246
6.5.5在那些需要被排序的列上創建索引246
6.5.6合理使用INCLUDE關鍵詞創建索引248
6.5.7指定索引的排序屬性249
6.6 影響索引性能的相關配置250
6.6.1設置影響索引性能的配置參數250
6.6.2為索引指定不同的表空間250
6.6.3確保索引的集群度251
6.6.4使表和索引統計信息保持251
6.7 索引維護251
6.7.1異步索引清除(AIC) 252
6.7.2聯機索引整理碎片254
6.7.3查找使用率低下的索引254
6.7.4索引壓縮256
6.8DB2 Design Advisor(db2advis) 256
6.9 本章小結260

第7章DB2優化器265
7.1DB2編譯器介紹266
7.2SQL語句編譯過程268
7.3 優化器組件和工作原理271
7.3.1查詢重寫示例：謂詞移動、合併和轉換271
7.3.2優化器成本評估276
7.3.3本地謂詞基數(cardinality)估計277
7.3.4連接基數(cardinality)估計279
7.3.5分佈統計信息283
7.3.6列組統計信息對基數的影響287
7.4 數據訪問方式297
7.4.1全表掃描297
7.4.2索引掃描298
7.4.3掃描共享301
7.5 連接方法302
7.5.1嵌套循環連接303
7.5.2合併連接305
7.5.3哈希連接306
7.5.4選擇連接的策略307
7.6 優化級別307
7.6.1優化級別概述308
7.6.2選擇優化級別311
7.6.3設置優化級別312
7.7 基於規則的優化314
7.7.1優化器概要文件概述314
7.7.2啟用優化概要文件316
7.7.3優化概要文件使用示例317
7.8 如何影響優化器來提高性能324
7.8.1使DB2統計信息保持324
7.8.2構建適當的索引324
7.8.3配置合理的數據庫配置參數325
7.8.4選擇合適的優化級別326
7.8.5合理的存儲I/O設計326
7.8.6良好的應用程序設計和編碼327
7.9 本章小結329

第8章統計信息更新與碎片整理331
8.1 統計信息更新332
8.1.1統計信息的重要性332
8.1.2如何更新統計信息333
8.1.3統計信息更新示例335
8.1.4LIKE STATISTICS統計信息更新339
8.1.5列組統計信息更新340
8.1.6分佈統計信息更新349
8.1.7統計信息更新策略355
8.2 自動統計信息更新357
8.2.1自動RUNSTATS的基本概念357
8.2.2如何打開auto runstats 359
8.2.3如何監控auto runstats 361
8.2.4自動收集統計視圖的統計信息362
8.3 碎片整理363
8.3.1碎片產生機制和影響363
8.3.2確定何時重組表和索引364
8.3.3執行表、索引檢查是否需要做REORG 367
8.3.4REORG的用法和使用策略368
8.4 重新綁定程序包371
8.5 本章小結373

第9章SQL語句調優375
9.1 通過監控找出消耗資源的SQL語句376
9.2 通過解釋工具分析SQL語句執行計劃376
9.2.1解釋表377
9.2.2db2expln 378
9.2.3db2exfmt 380
9.2.4各種解釋工具的比較382
9.2.5如何從解釋信息中獲取有價值的建議382
9.3 理解SQL語句如何工作383
9.3.1理解謂詞類型383
9.3.2排序和分組387
9.3.3連接方法388
9.3.4掃描方式389
9.4SQL調優案例390
9.4.1盡量使用單條語句完成邏輯390
9.4.2合理使用NOT IN和NOTEXISTS 391
9.4.3利用子查詢進行優化392
9.4.4調整表連接順序使JOIN394
9.4.5數據非均勻分佈時手工指定選擇性395
9.4.6使用UDF代替查詢中的複雜部分396
9.4.7合併多條SQL語句到單個SQL表達式397
9.4.8使用SQL一次處理一個集合語義398
9.4.9在無副作用的情況下使用SQL函數400
9.4.10小結401
9.5 提高應用程序性能401
9.5.1良好的SQL編碼規則401
9.5.2提高SQL編程性能403
9.5.3改進游標性能405
9.5.4根據業務邏輯選擇粒度的隔離級別406
9.5.5通過REOPT綁定選項來提高性能406
9.5.6統計信息、碎片整理和重新綁定407
9.5.7避免不必要的排序408
9.5.8在C/S環境中利用SQL存儲過程降低網絡開銷408
9.5.9在高並發環境下使用連接池408
9.5.10使用Design Advisor(db2advis)建議索引408
9.5.11提高批量刪除、插入和更新速度409
9.5.12提高插入性能409
9.5.13高效的SELECT語句410
9.6 高性能SQL語句注意事項412
9.6.1避免在搜索條件中使用複雜的表達式412
9.6.2將OPTIMIZE FOR n ROWS子句與FETCH FIRST nROWS ONLY子句配合使用412
9.6.3避免使用冗餘的謂詞412
9.6.4避免使用多個帶有DISTINCT關鍵字的聚集操作413
9.6.5避免連接列之間數據類型不匹配414
9.6.6避免對錶達式使用連接謂詞414
9.6.7避免在謂詞中使用空操作表達式來更改優化器估算415
9.6.8確保查詢符合星型模式連接的必需條件415
9.6.9避免使用非等式連接謂詞416
9.6.10避免使用不必要的外連接417
9.6.11使用參數標記來縮短動態查詢的編譯時間418
9.6.12使用約束來提高查詢優化程度418
9.7 本章小結419

10章DB2集群調優421
10.1DB2集群介紹421
10.2DB2集群參數解析423
10.2.1組緩衝池423
10.2.2全局鎖管理器425
10.2.3DB2 pureScale集群相關參數425
10.3DB2集群性能監控429
10.3.1查看CF資源利用429
10.3.2查看各個成員的負載情況430
10.3.3查看緩衝池命中率430
10.3.4查看全局鎖性能438
10.3.5查看頁回收(Page Reclaiming)行為441
10.4DB2集群設計調優442
10.4.1使用小的pagesize 442
10.4.2使用大的extentsize 442
10.4.3使用LOB inline方法442
10.4.4使用大的pctfree設置443
10.4.5巧用CURRENTMEMBER 443
10.4.6巧用索引444
10.5同城雙活集群介紹444
10.6同城雙活集群調優446
10.6.1減少存儲影響446
10.6.2減少通信影響447
10.6.3熱點表調優案例447
10.7本章小結450

11章DB2調優案例、問題總結和技巧451
11.1調優案例1：某移動公司存儲設計不當和SQL引起的I/O瓶頸451
11.2調優案例2：某銀行知識庫系統鎖等待、鎖升級引起性能瓶頸458
11.3調優案例3：某汽車製造商ERP系統通過調整統計信息提高性能466
11.4調優案例4：某農信社批量代收電費批處理慢調優案例476
11.5調優案例5：某銀行系統字段類型定義錯誤導致SQL執行時間變長480
11.6調優案例6：某銀行客戶回單系統CPU使用率高483
11.7調優案例7：某銀行手機銀行系統latch競爭導致activesession高、性能慢問題488
11.8調優學習案例：利用壓力測試程序學習DB2調優492