基於Oracle的SQL最佳化

基於Oracle的SQL最佳化（社群萬眾期待 資料庫最佳化扛鼎鉅著）

崔華 編  

ISBN 978-7-121-21758-6

2014年1月出版

定價：128.00元

856頁

16開

編輯推薦

本土Oracle資料庫效能最佳化頂級大師泣血力作

集十數年實戰修行與潛心鑽研之大成

蓋國強等國內資料庫一線名家聯合推薦

囊括資料庫效能最佳化技術所有分支與脈絡，講解通俗，例項經典

內容提要

《基於Oracle的SQL最佳化》是一本與眾不同的書，它的目的是使讀者真正掌握如何在 Oracle資料庫裡寫出高質量的 SQL語句，以及如何在 Oracle資料庫裡對有效能問題的 SQL做診斷和調整。

《基於Oracle的SQL最佳化》從 Oracle處理 SQL的本質和原理入手，由淺入深、系統地介紹了 Oracle資料庫裡的最佳化器、執行計劃、Cursor和繫結變數、查詢轉換、統計資訊、Hint和並行等這些與 SQL最佳化息息相關的本質性內容，並輔以大量極具借鑑意義的一線 SQL最佳化例項，闡述了作者倡導的“從本質和原理入手，以不變應萬變”的最佳化思路，最後還介紹了作者在實際工作中總結出來的 Oracle資料庫裡 SQL最佳化的方法論。

《基於Oracle的SQL最佳化》適用於使用 Oracle資料庫的開發人員、Oracle DBA和其他對 Oracle資料庫感興趣的人員，也可以作為各院校相關專業的教學輔導和參考用書，或作為相關培訓機構的培訓教材。

目錄

第 1章  Oracle裡的最佳化器 ...................................1

1.1 什麼是 Oracle裡的最佳化器 ...........................1

1.1.1 基於規則的最佳化器............................2

1.1.2 基於成本的最佳化器........................9

1.1.2.1 集的勢 ..............................11

1.1.2.2 可選擇率 ...............................11

1.1.2.3 可傳遞性..............................16

1.1.2.4 CBO的侷限性...................................18

1.2 最佳化器的基礎知識 .......................19

1.2.1 最佳化器的模式..................................19

1.2.2 結果集............................21

1.2.3 訪問資料的方法................................22

1.2.3.1 訪問表的方法.............................22

1.2.3.1.1 全表掃描 ....................................22

1.2.3.1.2 ROWID掃描 .....................................23

1.2.3.2 訪問索引的方法 .....................................24

1.2.3.2.1 索引唯一性掃描 ...............................25

1.2.3.2.2 索引範圍掃描 .......................................25

1.2.3.2.3 索引全掃描 ......................................28

1.2.3.2.4 索引快速全掃描 ...............................29

1.2.3.2.5 索引跳躍式掃描 ...............................31

1.2.4 表連線..........................33

1.2.4.1 表連線的型別......................................34

1.2.4.1.1 內連線.............................................34

1.2.4.1.2 外連線.................................................37

1.2.4.2 表連線的方法............................................47

1.2.4.2.1 排序合併連線 ..................................47

1.2.4.2.2 巢狀迴圈連線 ...................................48

1.2.4.2.3 雜湊連線 ..........................................51

1.2.4.2.4 笛卡兒連線 ......................................56

1.2.4.3 反連線 ...........................................58

1.2.4.4 半連線 ................................63

1.2.4.5 星型連線............................................65

1.3 最佳化器模式對 CBO計算成本帶來巨大影響的例項...............66

1.4 總結.......................80

第 2章  Oracle裡的執行計劃 ........................... 82

2.1 什麼是執行計劃.................................82

2.2 如何檢視執行計劃 ..........................85

2.2.1 explain plan命令....................................86

2.2.2 DBMS_XPLAN包............................89

2.2.3 AUTOTRACE開關 ...............................95

2.2.4 10046事件與 tkprof命令.............................99

2.3 如何得到真實的執行計劃..................................102

2.4 如何檢視執行計劃的執行順序.............................118

2.5 Oracle裡的常見執行計劃 .......................122

2.5.1 與表訪問相關的執行計劃.................................122

2.5.2 與 B樹索引相關的執行計劃.................................124

2.5.3 與點陣圖索引相關的執行計劃...................................129

2.5.4 與表連線相關的執行計劃.......................................138

2.5.5 其他典型的執行計劃...............................................146

2.5.5.1 AND-EQUAL（INDEX MERGE）.....................146

INDEX JOIN ................................148

2.5.5.2 VIEW.......................149

2.5.5.3FILTER ..................................151

2.5.5.4 SORT ....................................154

2.5.5.5

2.5.5.6 UNION/UNION ALL......................167

CONCAT..................................................168

2.5.5.7

CONNECT BY...................................................171

2.5.5.8

2.6 Oracle裡執行計劃的穩定 ................................172

2.6.1 使用 SQL Profile來穩定執行計劃 ...........................173

2.6.1.1 Automatic型別的 SQL Profile ...............................173

2.6.1.2 Manual型別的 SQL Profile......................................179

2.6.2 使用 SPM來穩定執行計劃........................................190

2.7 總結..............................203

第 3章  Oracle裡的 Cursor和繫結變數 ......................... 204

3.1 Oracle裡的 Cursor.............................204

3.1.1 Oracle裡的 Shared Cursor.............................204

3.1.1.1 Shared Cursor的含義 ..........................................204

3.1.1.2 硬解析 ................................................212

3.1.1.3 軟解析 ...................................214

3.1.2 Oracle裡的 Session Cursor..............................215

3.1.2.1 Session Cursor的含義 .....................................215

3.1.2.2 Session Cursor的相關引數解析...........................218

OPEN_CURSORS.........................................218

3.1.2.2.1 SESSION_CACHED_CURSORS ............................219

3.1.2.2.2CURSOR_SPACE_FOR_TIME ..............................221

3.1.2.2.3

3.1.2.3 Session Cursor的種類和用法 .............................222

3.1.2.3.1 隱式遊標 ........................................222

3.1.2.3.2 顯式遊標 ........................................225

3.1.2.3.3 參考遊標 ........................................230

3.2 Oracle裡的繫結變數 ...............................237

3.2.1 繫結變數的作用....................................237

3.2.2 繫結變數的典型用法.........................................238

3.2.3 繫結變數的使用原則和最佳實踐..............................245

3.2.3.1 PL/SQL批次繫結模板一 ...................................245

3.2.3.2 PL/SQL批次繫結模板二 .....................................247

3.2.4 繫結變數窺探............................................258

3.2.5 繫結變數分級..................................270

3.2.6 繫結變數的個數不宜太多........................................276

3.2.7 批次繫結時如何處理錯誤.......................................280

3.2.8 如何得到已執行的目標 SQL中繫結變數的值 ..........283

3.3 Oracle裡的遊標共享 .......288

3.3.1 常規遊標共享..............................289

3.3.2 自適應遊標共享........................297

3.4 Oracle裡的應用型別 ...............................320

3.4.1 Session Cursor的生命週期.........................................320

3.4.2 應用型別一（硬解析）................................................322

3.4.3 應用型別二（軟解析）...............................................323

3.4.4 應用型別三（軟軟解析）..........................................323

3.4.5 應用型別四（一次解析、多次執行）....................324

3.4.6 四種應用型別的實測效能對比..............................325

3.5 總結.............................333

第 4章  Oracle裡的查詢轉換 .......................... 335

4.1 Oracle裡查詢轉換的作用 ................335

4.2 子查詢展開.................................336

4.3 檢視合併............................344

4.3.1 簡單檢視合併...........................345

4.3.2 外連線檢視合併..................351

4.3.3 複雜檢視合併.....................354

4.4 星型轉換.....................365

4.5 連線謂詞推入 ................372

4.6 連線因式分解 ................379

4.7 表擴充套件 .......................387

4.8 表移除 .............396

4.9 Oracle如何處理 SQL語句中的 IN.............401

IN-List Iterator .................402

4.9.1

4.9.2 IN-List Expansion / OR Expansion ................404

IN-List Filter .............409

4.9.3

4.9.4 對 IN做子查詢展開/檢視合併 .............410

4.10 查詢轉換的綜合應用例項（邏輯讀從 200萬降到 6） .....413

4.11總結..............................420

第 5章  Oracle裡的統計資訊 ........ 422

5.1 什麼是 Oracle裡的統計資訊...........422

5.2 Oracle裡收集與檢視統計資訊的方法 .............423

5.2.1 收集統計資訊...................423

5.2.1.1 用 ANALYZE命令收集統計資訊...........423

5.2.1.2 用 DBMS_STATS包收集統計資訊..........428

5.2.1.3 ANALYZE和 DBMS_STATS的區別........432

5.2.2 檢視統計資訊................433

5.3 表的統計資訊 ..............435

5.3.1 表統計資訊的種類和含義.........435

5.3.2 表統計資訊不準導致 SQL效能問題的例項 .................437

5.4 索引的統計資訊..............440

5.4.1 索引統計資訊的種類和含義.................440

5.4.2 聚簇因子的含義及重要性............442

5.5 列的統計資訊 ..................450

5.5.1 列統計資訊的種類和含義..............450

5.5.2 列統計資訊不準導致謂詞越界的例項..........454

5.5.3 直方圖........................460

5.5.3.1 直方圖的含義...............460

5.5.3.2 直方圖的型別.................462

5.5.3.2.1 Frequency型別的直方圖 ...........463

5.5.3.2.2 Height Balanced型別的直方圖 ..................471

5.5.3.3 直方圖的收集方法................................475

5.5.3.4 直方圖對 CBO的影響 ....................477

5.5.3.4.1 直方圖對 Shared Cursor的影響 .............477

5.5.3.4.2 直方圖對可選擇率的影響 ................482

5.5.3.5 使用直方圖的注意事項...........................495

5.6 全域性統計資訊 ............................496

5.7 動態取樣............................507

5.8 多列統計資訊 ...........................516

5.9 系統統計資訊 .............................519

5.10 資料字典統計資訊..................536

5.11內部物件統計資訊 .............................539

5.12 Oracle裡的自動統計資訊收集 .........................546

5.13 Oracle裡應如何收集統計資訊 ..........................563

5.14 總結.........................567

第 6章  Oracle裡的 Hint..................... 568

6.1 什麼是 Hint .....................568

6.2 Hint的用法 ...............................576

6.3 Hint被 Oracle忽略的常見情形 ...............590

6.3.1 情形一：使用的 Hint有語法或者拼寫錯誤.................591

6.3.2 情形二：使用的 Hint無效.........................592

6.3.3 情形三：使用的 Hint自相矛盾..............597

6.3.4 情形四：使用的 Hint受到了查詢轉換的干擾...........599

6.3.5 情形五：使用的 Hint受到了保留關鍵字的干擾.........602

6.4 常見的 Hint ...............605

6.4.1 與最佳化器模式相關的 Hint................606

6.4.1.1 ALL_ROWS ...............606

6.4.1.2 FIRST_ROWS(n)...................606

RULE........................608

6.4.1.3

6.4.2 與表訪問相關的 Hint...................615

FULL .......................615

6.4.2.1 ROWID .............615

6.4.2.2

6.4.3 與索引訪問相關的 Hint...................615

INDEX .........................615

6.4.3.1 NO_INDEX .....................616

6.4.3.2 INDEX_DESC ...............617

6.4.3.3 INDEX_COMBINE................618

6.4.3.4 INDEX_FFS ..................620

6.4.3.5

6.4.3.6 INDEX_JOIN.............621

6.4.3.7 AND_EQUAL.....................622

6.4.4 與表連線順序相關的 Hint...................624

ORDERED ................................624

6.4.4.1 LEADING ........................626

6.4.4.2

6.4.5 與表連線方法相關的 Hint.............628

6.4.5.1 USE_MERGE ............628

NO_USE_MERGE .............631

6.4.5.2 USE_NL............633

6.4.5.3 NO_USE_NL .................634

6.4.5.4 USE_HASH.............635

6.4.5.5 NO_USE_HASH ..........635

6.4.5.6

6.4.5.7 MERGE_AJ.........636

NL_AJ............................637

6.4.5.8 HASH_AJ.................637

6.4.5.9 MERGE_SJ....................637

6.4.5.10 NL_SJ...........................638

6.4.5.11HASH_SJ .......................639

6.4.5.12

6.4.6 與查詢轉換相關的 Hint .....................639

USE_CONCAT ...............639

6.4.6.1 NO_EXPAND ...............640

6.4.6.2 MERGE................642

6.4.6.3 NO_MERGE................642

6.4.6.4 UNNEST ....................643

6.4.6.5

6.4.6.6 NO_UNNEST........................643

EXPAND_TABLE .................................644

6.4.6.7 NO_EXPAND_TABLE .......................644

6.4.6.8

6.4.7 與並行相關的 Hint ...........................645

PARALLEL...................................645

6.4.7.1 NO_PARALLEL ................................652

6.4.7.2

6.4.7.3 PARALLEL_INDEX ................................654

NO_PARALLEL_INDEX......................................656

6.4.7.4

6.4.8 其他常見 Hint ............................656

DRIVING_SITE..............................656

6.4.8.1 APPEND ...........................................659

6.4.8.2 APPEND_VALUES ........................................662

6.4.8.3

6.4.8.4 PUSH_PRED .......................664

NO_PUSH_PRED ................................666

6.4.8.5

6.4.8.6 PUSH_SUBQ....................................666

6.4.8.7 NO_PUSH_SUBQ................669

OPT_PARAM ..............................................670

6.4.8.8 OPTIMIZER_FEATURES_ENABLE ...............................672

6.4.8.9

6.4.8.10 QB_NAME ........................................674

CARDINALITY .................................674

6.4.8.11SWAP_JOIN_INPUTS .............................677

6.4.8.12

6.5 用 Cardinality Hint解決 ORA-01555錯誤的例項...............682

6.6 總結...............................693

第 7章  Oracle裡的並行 ..................... 695

7.1 Oracle裡並行的基本概念 ...............695

7.1.1 為什麼要用並行.................695

7.1.2 並行的理論基礎....................696

7.1.3 Oracle裡能夠並行執行的操作...............697

7.1.4 Oracle裡與並行有關的術語及解釋 ..................707

7.1.4.1 Query Coordinator .............708

7.1.4.2 Query Slaves和 Query Slave Set.............708

7.1.4.3 Table Queues.........716

7.1.4.4 資料傳遞方法.............721

7.1.4.5 granules............735

7.1.4.6 直接讀取 ............737

7.1.5 深入解析並行執行計劃的例項.............746

7.2 Oracle裡並行的控制 ..........755

7.2.1 Oracle裡如何開啟並行 .............755

7.2.2 Oracle裡並行度的控制 .............760

7.2.3 Oracle RAC環境下的並行...........771

7.2.4 Oracle裡與並行相關的引數 ..............775

PARALLEL_MAX_SERVERS .........775

7.2.4.1 PARALLEL_MIN_SERVERS ......................776

7.2.4.2

7.2.4.3 自動並行相關的引數 ...................776

PARALLEL_DEGREE_POLICY ................................776

7.2.4.3.1 PARALLEL_MIN_TIME_THRESHOLD ...................................776

7.2.4.3.2

7.2.4.3.3 PARALLEL_DEGREE_LIMIT ..................777

PARALLEL_SERVERS_TARGET..................................777

7.2.4.3.4

7.2.4.4 自適應並行相關的引數...............778

PARALLEL_ADAPTIVE_MULTI_USER ...............778

7.2.4.4.1 PARALLEL_MIN_PERCENT ........................778

7.2.4.4.2 PARALLEL_AUTOMATIC_TUNING .........778

7.2.4.4.3

7.2.4.5 其他引數 ............................778

PARALLEL_THREADS_PER_CPU ...................................778

7.2.4.5.1 PARALLEL_EXECUTION_MESSAGE_SIZE.............779

7.2.4.5.2 PARALLEL_FORCE_LOCAL.........................779

7.2.4.5.3

7.2.5 繞開 Oracle並行執行 Bug大幅提升效能的例項 ................779

7.3 總結..........................805

第 8章  Oracle裡 SQL最佳化的方法論 ................... 807

8.1 Oracle裡如何做 SQL最佳化...................807

8.1.1 Oracle裡 SQL最佳化的本質是基於對 CBO和執行計劃的深刻理解 ......807

8.1.2 Oracle裡 SQL最佳化需要聯絡實際的業務 ...................819

8.1.3 Oracle裡 SQL最佳化需要適時使用繫結變數 ............824

8.2 Oracle裡 SQL最佳化的方法論在實戰中的驗證...............824

8.3 總結......................841

 

作者簡介

崔華，網名dbsnake，Oracle ACE, ACOUG成員。

資深Oracle資料庫工程師，擁有Oracle資料庫技術各個領域的經驗，尤其是在Oracle資料庫效能最佳化與備份恢復方面經驗豐富。

他與其他人合作編寫了多本Oracle技術書籍，他經常在Oracle相關活動中發表演講並在自己的部落格上撰寫了大量技術文章。

前言

為什麼寫這本書

寫這本書純屬偶然。

2010年 12月 11日，我在中國軟體技術大會上做了一個關於 Oracle資料庫備份與恢復機制揭密的主題演講。可能是因為這個演講的緣故，電子工業出版社的編輯畢寧隨後多次邀請我寫一本關於 Oracle資料庫備份恢復方面的書，但均被我以各種理由搪塞、推脫。這一拖就拖了大半年（為什麼會推脫？一來是因為我認為備份恢復這個點相對來說較窄，不具備普適性；二來市面上已經有不少關於備份恢復方面的書，這意味著如果我想寫出有新意、有不一樣的內容的話，會有相當的難度）。

這種推脫一直持續到 2011年 10月，那個時候我正好在公司內部主講一個基於 Oracle資料庫的 SQL最佳化的系列課程。我開設這門課程的初衷是因為當時恰逢公司開始研發新一代系統，而我深知對於使用 Oracle資料庫的應用系統而言，SQL語句的質量會直接影響系統的效能，甚至可以說大部分基於 Oracle資料庫的應用系統的效能問題都是由於開發人員不懂 Oracle資料庫，不懂如何在 Oracle資料庫裡寫出高質量的 SQL所致。這樣的系統效能問題，單靠高水準的 Oracle DBA來調整是非常痛苦的，很多時候是事倍功半。如果能把我在 SQL最佳化方面的經驗分享給大家，告訴大家如何避免在 Oracle資料庫中寫出很爛的 SQL，如何在 Oracle資料庫中做 SQL最佳化，那麼就可以從源頭上提升新一代系統在資料庫端的效能，這也算是我為公司新一代系統的研發所做的一份貢獻。

這門課程一經推出，就取得了很好的反響，同事們紛紛反饋說這門課程很實用，課程裡的不少方法和知識點在實際的工作中都能用上，這使我意識到自己是在做一件非常有意義的事情，雖然辛苦，但是確實能幫助到同事。

這門課程大約是 30個學時，我才開始講沒多久，畢寧就再次打電話給我，他還是希望我能寫一本關於 Oracle資料庫備份恢復方面的書。我清楚地記得那天下午接到畢寧電話的時候，腦海裡突然閃現了一個念頭——為什麼不把現在講的這門 SQL最佳化的課程寫成一本書呢？這樣一來可以對畢寧有個交待，二來也可以幫到更多的人，而不僅僅是我的同事。因為只要是用 Oracle資料庫的，只要是構建在 Oracle資料庫上的應用系統就必然會涉及 SQL最佳化，也就是說 SQL最佳化不同於備份恢復，它是具備普適性的。另外，市面上系統闡述 Oracle資料庫中 SQL最佳化的書非常少，這意味著我有很大的發揮空間。如果能寫一本系統的、從本質上闡述如何在 Oracle資料庫裡做 SQL最佳化的書，能夠透過這本書教會開發人員如何在 Oracle資料庫裡寫出高質量的 SQL，以及如何對有效能問題的 SQL做診斷與調整，那麼也許就可以從源頭上保證，由這些開發人員所開發出來的基於 Oracle資料庫的應用系統在 SQL上是沒有效能問題的，而那些由於 SQL撰寫不當而導致的各種效能問題也就不復存在了。如果真能做到這一點，那真是一件功德無量的事情。

在和畢寧溝透過幾次後，我的上述想法獲得了他的支援，於是從 2011年 10月份開始，我就正式開始撰寫這本書。只是我萬萬沒有想到，這一寫就寫了 17個月。

這本書的撰寫過程是極為艱苦的。一來是因為我對自己要求很高，希望寫出來的書通俗易懂（普通的使用 Oracle資料庫的開發人員就能看懂），但同時又要具備一定的深度；二來是因為我倡導“從本質和原理入手，以不變應萬變”的 SQL最佳化思路，必然涉及深入介紹 Oracle資料庫裡的最佳化器，但 Oracle資料庫裡的最佳化器實在是太複雜了。

現在回想起來，我為這本書付出了太多太多。在這一年多的撰寫過程中，由於長期熬夜，我能明顯感覺到身體越來越差，到了撰寫後期更是頻繁往醫院跑，但無論如何，我還是堅持下來了。

本書的主要內容

本書共 8章。

第 1章“Oracle裡的最佳化器”，詳細介紹了 Oracle資料庫中與最佳化器相關的各個方面的內容，包括最佳化器的模式、結果集（Row Source）、集的勢（Cardinality）、可選擇率（Selectivity）、可傳遞性（Transitivity）、各種資料訪問的方法，以及與表連線相關的內容。

第 2章“Oracle裡的執行計劃”，詳細介紹了 Oracle資料裡與執行計劃有關的各個方面的內容，包括執行計劃的含義，如何檢視執行計劃，如何得到目標 SQL真實的執行計劃，如何檢視執行計劃的執行順序， Oracle資料庫裡各種常見的執行計劃的含義，以及如何在 Oracle資料庫中穩定執行計劃。

第 3章“Oracle裡的 Cursor和繫結變數”，詳細介紹了 Oracle資料庫中與 Cursor和繫結變數相關的各個方面的內容，包括 Shared Cursor、Session Cursor、繫結變數、遊標共享、硬解析、軟解析、軟軟解析，以及與它們息息相關的 Oracle資料庫裡的四種應用型別。

第 4章“Oracle裡的查詢轉換”，詳細介紹了 Oracle資料庫中與查詢轉換有關的各個方面的內容，包括子查詢展開、檢視合併、星型轉換、連線謂詞推入、連線因式分解、表擴充套件、表移除，以及 Oracle如何處理 SQL語句中的 IN。

第 5章“Oracle裡的統計資訊”，詳細介紹了 Oracle資料庫裡與統計資訊相關的各個方面的內容，包括 Oracle資料庫中各種統計資訊的分類、含義、收集和檢視方法，以及如何在 Oracle資料庫里正確地收集統計資訊。

第 6章“ Oracle裡的 Hint”，詳細介紹了 Oracle資料庫中與 Hint有關的各個方面的內容，包括什麼是 Hint，如何用 Hint，Hint什麼情況下會失效，以及 Oracle資料庫中常見的各種 Hint。

第 7章“Oracle裡的並行”，詳細介紹了 Oracle資料庫裡並行的基本概念以及在 Oracle資料庫裡如何控制並行，包括在 Oracle資料庫裡開啟並行、控制並行度等。

第 8章“Oracle裡 SQL最佳化的方法論”，介紹了在 Oracle資料庫裡如何做 SQL最佳化，提出了我們總結出來的 Oracle資料庫裡 SQL最佳化的方法論，並結合例項驗證了上述方法論。

本書的讀者物件

本書適用於使用 Oracle資料庫的開發人員、Oracle DBA和其他對 Oracle資料庫感興趣的人員。

本書也可以作為各大中專院校相關專業的教學輔導和參考用書，或作為相關培訓機構的培訓教材。

本書程式碼下載

本書使用的所有指令碼和範例程式碼均可以透過我網站上的 Books專欄下載，網址為 。

本書約定

本書介紹的 SQL最佳化方法論是通用的方法，並不侷限於 Oracle資料庫的某個具體的版本，但本書的例項和測試結果絕大部分是基於 Oracle 11gR2的（除個別特別註明的例項之外）。而 Oracle資料庫不同的版本之間在某些方面可能會差別很大，所以即便是同樣的例項，在 Oracle資料庫不同的版本上的測試結果也有可能不同，在此特別說明，一切以實際情況為準。

由於我的水準和經驗所限，書中的錯誤之處在所難免，在此誠摯期待大家閱讀後的指正。可以透過電子郵件與我取得聯絡（allantreycn@gmail.com），歡迎與我交流任何關於本書的問題。