Oracle數據庫性能優化實踐指南（簡體書）

本書涉及了Oracle優化的所有方面，系統地講述了Oracle優化的相關內容。本書分為5個部分。第1部分總體介紹了性能計劃、實例優化和SQL優化，也簡單提及了優化過程中會用到的一些特性和工具。第2部分描述了在一般情況下和緊急情況下的性能優化的通用方法。第3部分對在性能計劃階段應該考慮的性能優化相關的事項進行了介紹。第4部分介紹了如何通過優化實例來提升數據庫性能。第5部分介紹了如何對SQL進行優化，SQL的優化重點在于對執行計劃的優化。

本書可以幫你叩開Oracle優化的大門，讓你直接看到想要的正確的、完整的答案。如果你想學習Oracle優化，想要成為這方面的專家，那麼本書將幫助你實現這個目標。

霜月琴寒，本名王鵬，東軟軟件公司工程師，1999年畢業后一直從事測試工作，最近6年一直在做性能測試，目前所在公司主要從事社保系統的研發，作者本人負責各地市性能測試項目，關于Oracle數據庫性能優化積累了很多經驗，從一開始根本不知道如何入手，到現在總結出了一些通用方法，中間花費了很多心思。為使后來者避開自己走過的彎路，特總結編寫了本書，這本書涉及了Oracle 優化的所有方面，系統地講述了Oracle 優化的相關內容。如果能對本書的內容融會貫通，那么在從事相應工作時肯定可以做到得心應手。

霜月琴寒，本名王鵬，東軟軟件公司工程師，1999年畢業后一直從事測試工作，最近6年一直在做性能測試，目前所在公司主要從事社保系統的研發，作者本人負責各地市性能測試項目，關于Oracle數據庫性能優化積累了很多經驗，從一開始根本不知道如何入手，到現在總結出了一些通用方法，中間花費了很多心思。為使后來者避開自己走過的彎路，特總結編寫了本書，這本書涉及了Oracle 優化的所有方面，系統地講述了Oracle 優化的相關內容。如果能對本書的內容融會貫通，那么在從事相應工作時肯定可以做到得心應手。

第1部分　性能優化是怎么回事兒
第1章　了解性能優化 2
1.1　性能優化包括幾個方面 2
1.1.1　性能計劃 2
1.1.2　實例優化 2
1.1.3　SQL優化 4
1.2　哪些工具和特性可以幫助進行性能優化 5
1.2.1　自動性能調優特性 5
1.2.2　其他Oracle數據庫工具 6
第2部分　性能優化的通用方法
第2章 性能優化的通用方法 8
2.1　Oracle性能提升方法 8
2.1.1　Oracle性能提升方法的步驟 9
2.1.2　性能概念建模的決策過程 10
2.1.3　Oracle系統中最常發生的十大錯誤 11
2.2　緊急情況下的性能提升方法 12
第3部分　性能優化三件事之一：設計者如何優化設計和開發
第3章 性能優化從設計和開發開始 16
3.1　Oracle方法論 16
3.2　理解投資選項 16
3.3　理解延展性 17
3.3.1　什么是延展性 17
3.3.2　系統延展性 17
3.3.3　阻礙延展性的因素 18
3.4　系統架構 19
3.4.1　硬件和軟件組件 19
3.4.2　為需求配置匹配的系統架構 22
3.5　應用程序設計原理 24
3.5.1　簡單的應用程序設計 24
3.5.2　數據建模 24
3.5.3　表和索引設計 25
3.5.4　使用視圖 27
3.5.5　SQL執行效率 27
3.5.6　應用程序的實現 28
3.5.7　應用程序開發的趨勢 29
3.5.8　負載測試、建模和實現 30
3.5.9　部署新的應用程序 32
第4部分　性能優化三件事之二：DBA如何合理使用資源而改善實例性能
第4章　資源有哪些 36
4.1　配置和使用內存 36
4.1.1　理解內存分配 36
4.1.2　配置和使用緩存 40
4.1.3　配置及使用共享池和大池 49
4.1.4　配置和使用重做日志緩存 68
4.1.5　PGA內存管理 70
4.1.6　管理服務器和客戶端結果緩沖 82
4.2　I/O的配置和設計 92
4.2.1　關于I/O 92
4.2.2　I/O配置 93
4.2.3　數據庫的I/O校準 100
4.2.4　使用Oracle Orion校準工具進行I/O校準 102
4.3　管理操作系統資源 113
4.3.1　理解操作系統性能問題 113
4.3.2　解決操作系統問題 116
4.3.3　理解CPU 117
4.3.4　解決CPU的問題 118
第5章 配置數據庫如何使用資源 123
5.1　初始實例配置時的性能考慮 123
5.1.1　初始化參數 123
5.1.2　撤銷（undo）空間的配置 124
5.1.3　指定重做日志文件的大小 125
5.1.4　創建后續的表空間 125
5.2　創建表和維護表時的性能考慮 126
5.2.1　表壓縮 126
5.2.2　回收不用的空間 127
5.2.3　創建數據索引 127
5.3　共享服務器的性能考慮 128
5.3.1　使用與分配器相關的視圖識別爭用 128
5.3.2　識別共享服務器的爭用 129
第6章　統計資源使用的效果 131
6.1　數據收集概覽 131
6.1.1　數據庫統計信息 132
6.1.2　操作系統統計信息 134
6.1.3　解釋統計信息 136
6.2　AWR概覽 137
6.2.1　快照 138
6.2.2　基線 138
6.3　AWR管理 141
6.3.1　管理快照 141
6.3.2　管理基線 143
6.3.3　管理基線模板 145
6.3.4　轉移AWR數據 147
6.3.5　使用AWR視圖 149
6.3.6　生成AWR報告 150
6.3.7　生成AWR時間段比較報告 155
6.3.8　生成活動會話歷史報告 161
6.3.9　使用活動會話歷史報告 165
第7章　實例優化：更合理地使用資源 169
7.1　自動化的性能診斷 169
7.1.1　自動數據庫診斷監視器（ADDM）概覽 169
7.1.2　設置ADDM 173
7.1.3　使用ADDM診斷數據庫性能問題 173
7.1.4　查看ADDM信息 176
7.2　使用性能視圖進行實例調優 176
7.2.1　實例調優步驟 177
7.2.2　解釋Oracle數據庫統計信息 185
7.2.3　等待事件統計信息 191
7.2.4　實時SQL監控 211
7.2.5　優化實例的恢復性能：快速啟動錯誤恢復 215
第5部分　性能優化三件事之三：開發人員如何優化SQL執行計劃
第8章　好的數據結構產生好的執行計劃：索引和簇 224
8.1　理解索引的性能 224
8.1.1　優化邏輯結構 225
8.1.2　使用SQL Access Advisor優化索引 225
8.1.3　為索引選擇列和表達式 225
8.1.4　選擇混合索引 226
8.1.5　書寫使用索引的語句 227
8.1.6　書寫避免使用索引的語句 227
8.1.7　重建索引 228
8.1.8　精簡索引 228
8.1.9　使用非唯一索引實現唯一性 228
8.1.10　使用啟用的非驗證性約束 229
8.2　為性能而使用基于函數的索引 230
8.3　為性能而使用分區索引 230
8.4　為性能而使用索引組織表 231
8.5　為性能而使用位圖索引 231
8.6　為性能而使用位圖連接索引 232
8.7　為性能而使用域索引 232
8.8　為性能而使用表簇 232
8.9　為性能而使用哈希簇 233
第9章　執行計劃 235
9.1　查詢優化器統計信息：選擇執行計劃的依據 235
9.1.1　查詢優化器統計信息概覽 235
9.1.2　管理“自動查詢優化器統計信息收集” 236
9.1.3　手工收集統計信息 239
9.1.4　系統統計信息 244
9.1.5　管理統計信息 247
9.1.6　控制動態統計信息 255
9.1.7　查看統計信息 259
9.2　查詢優化器：選擇執行計劃 262
9.2.1　查詢優化器概覽 262
9.2.2　查詢優化器訪問路徑概覽 274
9.2.3　連接概覽 282
9.2.4　讀懂執行計劃 292
9.2.5　控制查詢優化器的行為 294
9.3　解釋計劃：顯示執行計劃 297
9.3.1　理解EXPLAIN PLAN 297
9.3.2　PLAN_TABLE輸出表 300
9.3.3　運行EXPLAIN PLAN 300
9.3.4　顯示PLAN_TABLE的輸出 301
9.3.5　閱讀EXPLAIN PLAN的輸出 302
9.3.6　使用EXPLAIN PLAN查看并行執行 303
9.3.7　使用EXPLAIN PLAN查看位圖索引 306
9.3.8　使用EXPLAIN PLAN查看結果緩沖 307
9.3.9　使用EXPLAIN PLAN查看分區對象 307
9.3.10　PLAN_TABLE列 314
9.4　查詢優化器提示：“大綱”的建筑材料，讓查詢優化器按指示行事 322
9.4.1　查詢優化器提示概覽 322
9.4.2　指定提示 328
9.4.3　使用視圖的提示 332
9.5　計劃穩定性：大綱，以“查詢優化器提示”記錄和存儲計劃 334
9.5.1　通過計劃穩定性以保留執行計劃 335
9.5.2　在查詢優化器升級中使用計劃穩定性 340
9.6　SQL計劃管理器：“計劃基線”，大綱的升級版 342
9.6.1　SQL計劃基線概覽 342
9.6.2　管理SQL計劃基線 344
9.6.3　在SQL優化顧問中使用SQL計劃基線 348
9.6.4　使用固定的SQL計劃基線 349
9.6.5　顯示SQL計劃基線 349
9.6.6　SQL管理基地 350
9.6.7　導入和導出SQL計劃基線 352
9.6.8　將存儲大綱遷移到SQL計劃基線 353
第10章　對SQL和執行計劃進行跟蹤與分析 363
10.1　端到端應用程序跟蹤：得到客戶端跟蹤信息 363
10.1.1　為端到端應用程序跟蹤啟用和禁用統計信息的收集 364
10.1.2　查看為端到端應用程序跟蹤收集的統計信息 365
10.1.3　啟用和禁用端到端跟蹤 365
10.1.4　查看啟用的端到端跟蹤 368
10.2　使用trcsess工具：組織跟蹤信息 368
10.2.1　trcsess工具的語法 368
10.2.2　trcsess輸出樣例 369
10.3　了解SQL Trace和TKPROF 370
10.3.1　了解SQL Trace工具 370
10.3.2　了解TKPROF 370
10.4　使用SQL Trace和TKPROF 371
10.4.1　第1步：為跟蹤文件管理設置初始化參數 371
10.4.2　第2步：啟用SQL Trace工具 372
10.4.3　第3步：使用TKPROF格式化跟蹤文件 373
10.4.4　第4步：解釋TKPROF輸出 376
10.4.5　第5步：保存SQL Trace工具統計信息 380
10.5　避免TKPROF解釋錯誤 382
10.5.1　避免綁定變量陷阱 382
10.5.2　避免讀一致性陷阱 383
10.5.3　避免模式陷阱 383
10.5.4　避免時間陷阱 384
10.6　TKPROF輸出樣例 385
10.6.1　TKPROF頭樣例 385
10.6.2　TKPROF正文樣例 385
10.6.3　TKPROF總結樣例 388
第11章　SQL優化：得到更好的執行計劃 389
11.1　SQL優化概覽 389
11.1.1　SQL優化介紹 389
11.1.2　優化目標 389
11.1.3　識別高負載SQL語句 390
11.1.4　自動SQL優化特性 392
11.1.5　開發高效的SQL語句 393
11.1.6　構建SQL測試用例 401
11.2　SQL調優顧問：針對SQL語句改善執行計劃 403
11.2.1　自動調優優化器概覽 403
11.2.2　管理自動SQL調優顧問 406
11.2.3　使用SQL調優顧問進行優化 410
11.2.4　管理SQL調優集合 416
11.2.5　管理SQL Profile 420
11.2.6　SQL調優視圖 427
11.3　SQL訪問顧問：針對整體負載改善執行計劃 428
11.3.1　SQL訪問顧問概覽 428
11.3.2　使用SQL訪問顧問 431
11.3.3　為“快速刷新”和“查詢重寫”優化物化視圖 453
附錄A　名詞解釋 462

2.1　Oracle性能提升方法
Oracle性能方法論幫助你在Oracle數據庫中識別性能問題，包括識別瓶頸和修復它們。建議只有在已經確認存在一個瓶頸時才可以做出相應的改變。
性能提升是一個迭代的過程。因為解決掉一個瓶頸可能不會立即讓性能得到提升，可能會發現另一個瓶頸。另外，在一些情況下，如果串行點移動到另一個更低效的組件那里，那么性能甚至會降低。
性能問題一般表現為吞吐量不足，或者用戶/任務的響應時間不可接受，或者二者兼具。而問題可能位于應用程序模塊之中或者存在于整個系統中。在查看任何數據庫或者操作系統統計信息前，得到系統最重要的用戶反饋是最重要的。這里的“最重要的用戶”是指系統用戶或者最終為應用程序付費的人。典型的用戶反饋可能包括下面這些話：
“在線系統太慢了，以至于它讓我的員工根本無法工作。”
“賬單的產生實在太慢了。”
“當我面對巨大的網絡流量時，響應時間不能接受，我丟失了客戶。”
“現在我們一天會完成500筆生意，系統已經用到最大限度了，下個月會讓所有用戶開始使用我們的產品，用戶量會增加到4倍。”
……
從這些坦率的反饋中，很容易看出用戶認為什么才是決定性能的最重要指標。確定好這個性能指標和終止調優過程的標準，可以讓性能過程的管理更簡單，而且在每個層面上都更成功。
這些重要的性能指標最好以真實的業務目標來定義，而不是以系統的統計數據來定義。
對于真實的業務目標，以典型的用戶語言來說可能是：
賬單系統必須在3小時內可以處理100萬個賬戶。
在網站峰值期間，刷新一個頁面一定不要超過5秒。
系統必須能夠在8小時內處理25000筆生意。
終極的成功是用戶對系統性能的認可。性能工程師的角色就是要消除任何讓性能降低的瓶頸，這些瓶頸可能是由于低效地使用了有限的共享資源或者濫用了共享資源引起的，這引起了串行化。由于共享資源是有限的，性能工程師的目標就是要高效地使用共享資源，并使系統可以處理的業務量達到最大化。在一個很高的層面上，整個數據庫就是一個共享的資源。相應的，在較低層次上，一個CPU或者一個磁盤也可以被看成一個共享的資源。
你可以不斷循環地使用Oracle性能提升方法，直到達到性能目標，或者確認目標根本不可能達到。這個過程是高度迭代的。有時，某些調查對改善系統性能可能只有很小的幫助或者根本沒有任何幫助。要想能夠精準、快速地發現系統最嚴重的瓶頸，時間和經驗都是必要的。但是，對于某些有經驗的性能工程師來說，經驗優先的原則可能導致他們忽略一些有價值的數據和統計結果。這類行為更傾向于通過神話和傳說來調優。這是非常危險的、昂貴的、不會成功的數據庫調優方法。
自動化數據庫診斷監視器（ADDM）使用一些性能提升方法，并為主要的性能問題的自動診斷提供分析和統計功能。使用ADDM，能大幅縮短提升系統性能所需要的時間。對于ADDM的描述，請參考第7章中的 “自動化的性能診斷”。
系統是如此的不同和復雜，以至于找到有力且快速的規則是不可能的。本質上，Oracle性能提升方法只是定義了一個工作方向，而不是一堆最終的規則。最棒的性能工程師會利用系統提供的數據和靈活的思考來確定每個具體的性能問題。
2.1.1　Oracle性能提升方法的步驟
（1）做如下常規檢查。
① 從用戶那里得到坦率的反饋。確定性能項目的工作范圍，以及后續的性能目標。這個過程對將來做性能計劃是非常關鍵的。
② 得到系統中的操作系統、數據庫和應用程序在性能良好和性能糟糕時的完整的統計信息。如果不能全部得到，那就盡量得到可以得到的統計信息。丟失統計信息就像在犯罪現場丟失證據一樣，這使得偵測工作更難、更耗時。
③ 檢查所有與用戶性能相關的計算機的操作系統。通過檢查操作系統，可以找到那些被耗盡的硬件資源和系統資源。請列出任何被過度使用的資源以作為癥狀，等待今后的進一步分析。另外，檢查所有硬件是否都沒有報錯信息。
（2）在當前系統中檢查Oracle常見的10大錯誤，如果發現問題就將其列出來作為今后分析的癥狀。這是因為它們是最可能出現的問題。Oracle ADDM可以對這10個問題中的9個進行自動報告。請看第7章中的“自動化的性能診斷”，以及本章中的“Oracle系統中最常發生的10大錯誤”。
（3）使用癥狀作為線索建立一個模型，用以模擬系統中正在發生的事情，以此來了解是什么原因導致了性能問題。請看本章中的“性能概念建模的決策過程”。
（4）準備一系列補救措施，并對措施的效果進行預估，然后將這些措施以對系統最適合的順序應用于系統。ADDM的每個建議都帶有相應的效果說明。在性能工作中一個黃金定律就是一次只改變一件事情，然后測試改變前后的區別。然而，遺憾的是，系統宕機時間必須越短越好，這不允許進行特別縝密的調查。所以，如果不得不同時實施多個改變，那么試著確保它們是相互隔離互不影響的，以便于獨立驗證每個改變的效果。
（5）確認實施的改變是否已經產生了期望的效果，并且看看用戶的接受程度是否提高。如果效果不佳，就得找到更多的瓶頸，并且不斷地完善模型，直到你對應用程序的理解變得更精確。
（6）重復后3步，直到性能目標被達到，或者由于一些其他的限制達到性能目標變得不可能。
這個方法可以識別最大的瓶頸，其焦點在于通過提高應用程序的效率和消除資源的短缺，讓性能大幅提升。在這個過程中，估計只能從針對實例的調整中得到很少的性能提升（小于10%），而巨大的性能提升則來自于針對低效的應用程序的調整。