基于Oracle的SQL優(yōu)化

第 1章 Oracle里的優(yōu)化器 ...................................1
1.1 什么是 Oracle里的優(yōu)化器 ...........................1
1.1.1 基于規(guī)則的優(yōu)化器............................2
1.1.2 基于成本的優(yōu)化器........................9
1.1.2.1 集的勢(shì) ..............................11
1.1.2.2 可選擇率 ...............................11
1.1.2.3 可傳遞性..............................16
1.1.2.4 CBO的局限性...................................18
1.2 優(yōu)化器的基礎(chǔ)知識(shí) .......................19
1.2.1 優(yōu)化器的模式..................................19
1.2.2 結(jié)果集............................21
1.2.3 訪問(wèn)數(shù)據(jù)的方法................................22
1.2.3.1 訪問(wèn)表的方法.............................22
1.2.3.1.1 全表掃描 ....................................22
1.2.3.1.2 ROWID掃描 .....................................23
1.2.3.2 訪問(wèn)索引的方法 .....................................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 表連接的類(lèi)型......................................34
1.2.4.1.1 內(nèi)連接.............................................34
1.2.4.1.2 外連接.................................................37
1.2.4.2 表連接的方法............................................47
1.2.4.2.1 排序合并連接 ..................................47
1.2.4.2.2 嵌套循環(huán)連接 ...................................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 優(yōu)化器模式對(duì) CBO計(jì)算成本帶來(lái)巨大影響的實(shí)例...............66
1.4 總結(jié).......................80
第 2章 Oracle里的執(zhí)行計(jì)劃 ........................... 82
2.1 什么是執(zhí)行計(jì)劃.................................82
2.2 如何查看執(zhí)行計(jì)劃 ..........................85
2.2.1 explain plan命令....................................86
2.2.2 DBMS_XPLAN包............................89
2.2.3 AUTOTRACE開(kāi)關(guān) ...............................95
2.2.4 10046事件與 tkprof命令.............................99
2.3 如何得到真實(shí)的執(zhí)行計(jì)劃..................................102
2.4 如何查看執(zhí)行計(jì)劃的執(zhí)行順序.............................118
2.5 Oracle里的常見(jiàn)執(zhí)行計(jì)劃 .......................122
2.5.1 與表訪問(wèn)相關(guān)的執(zhí)行計(jì)劃.................................122
2.5.2 與 B樹(shù)索引相關(guān)的執(zhí)行計(jì)劃.................................124
2.5.3 與位圖索引相關(guān)的執(zhí)行計(jì)劃...................................129
2.5.4 與表連接相關(guān)的執(zhí)行計(jì)劃.......................................138
2.5.5 其他典型的執(zhí)行計(jì)劃...............................................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里執(zhí)行計(jì)劃的穩(wěn)定 ................................172
2.6.1 使用 SQL Profile來(lái)穩(wěn)定執(zhí)行計(jì)劃 ...........................173
2.6.1.1 Automatic類(lèi)型的 SQL Profile ...............................173
2.6.1.2 Manual類(lèi)型的 SQL Profile......................................179
2.6.2 使用 SPM來(lái)穩(wěn)定執(zhí)行計(jì)劃........................................190
2.7 總結(jié)..............................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的相關(guān)參數(shù)解析...........................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的種類(lèi)和用法 .............................222
3.1.2.3.1 隱式游標(biāo) ........................................222
3.1.2.3.2 顯式游標(biāo) ........................................225
3.1.2.3.3 參考游標(biāo) ........................................230
3.2 Oracle里的綁定變量 ...............................237
3.2.1 綁定變量的作用....................................237
3.2.2 綁定變量的典型用法.........................................238
3.2.3 綁定變量的使用原則和最佳實(shí)踐..............................245
3.2.3.1 PL/SQL批量綁定模板一 ...................................245
3.2.3.2 PL/SQL批量綁定模板二 .....................................247
3.2.4 綁定變量窺探............................................258
3.2.5 綁定變量分級(jí)..................................270
3.2.6 綁定變量的個(gè)數(shù)不宜太多........................................276
3.2.7 批量綁定時(shí)如何處理錯(cuò)誤.......................................280
3.2.8 如何得到已執(zhí)行的目標(biāo) SQL中綁定變量的值 ..........283
3.3 Oracle里的游標(biāo)共享 .......288
3.3.1 常規(guī)游標(biāo)共享..............................289
3.3.2 自適應(yīng)游標(biāo)共享........................297
3.4 Oracle里的應(yīng)用類(lèi)型 ...............................320
3.4.1 Session Cursor的生命周期.........................................320
3.4.2 應(yīng)用類(lèi)型一（硬解析）................................................322
3.4.3 應(yīng)用類(lèi)型二（軟解析）...............................................323
3.4.4 應(yīng)用類(lèi)型三（軟軟解析）..........................................323
3.4.5 應(yīng)用類(lèi)型四（一次解析、多次執(zhí)行）....................324
3.4.6 四種應(yīng)用類(lèi)型的實(shí)測(cè)性能對(duì)比..............................325
3.5 總結(jié).............................333
第 4章 Oracle里的查詢轉(zhuǎn)換 .......................... 335
4.1 Oracle里查詢轉(zhuǎn)換的作用 ................335
4.2 子查詢展開(kāi).................................336
4.3 視圖合并............................344
4.3.1 簡(jiǎn)單視圖合并...........................345
4.3.2 外連接視圖合并..................351
4.3.3 復(fù)雜視圖合并.....................354
4.4 星型轉(zhuǎn)換.....................365
4.5 連接謂詞推入 ................372
4.6 連接因式分解 ................379
4.7 表擴(kuò)展 .......................387
4.8 表移除 .............396
4.9 Oracle如何處理 SQL語(yǔ)句中的 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 對(duì) IN做子查詢展開(kāi)/視圖合并 .............410
4.10 查詢轉(zhuǎn)換的綜合應(yīng)用實(shí)例（邏輯讀從 200萬(wàn)降到 6） .....413
4.11總結(jié)..............................420
第 5章 Oracle里的統(tǒng)計(jì)信息 ........ 422
5.1 什么是 Oracle里的統(tǒng)計(jì)信息...........422
5.2 Oracle里收集與查看統(tǒng)計(jì)信息的方法 .............423
5.2.1 收集統(tǒng)計(jì)信息...................423
5.2.1.1 用 ANALYZE命令收集統(tǒng)計(jì)信息...........423
5.2.1.2 用 DBMS_STATS包收集統(tǒng)計(jì)信息..........428
5.2.1.3 ANALYZE和 DBMS_STATS的區(qū)別........432
5.2.2 查看統(tǒng)計(jì)信息................433
5.3 表的統(tǒng)計(jì)信息 ..............435
5.3.1 表統(tǒng)計(jì)信息的種類(lèi)和含義.........435
5.3.2 表統(tǒng)計(jì)信息不準(zhǔn)導(dǎo)致 SQL性能問(wèn)題的實(shí)例 .................437
5.4 索引的統(tǒng)計(jì)信息..............440
5.4.1 索引統(tǒng)計(jì)信息的種類(lèi)和含義.................440
5.4.2 聚簇因子的含義及重要性............442
5.5 列的統(tǒng)計(jì)信息 ..................450
5.5.1 列統(tǒng)計(jì)信息的種類(lèi)和含義..............450
5.5.2 列統(tǒng)計(jì)信息不準(zhǔn)導(dǎo)致謂詞越界的實(shí)例..........454
5.5.3 直方圖........................460
5.5.3.1 直方圖的含義...............460
5.5.3.2 直方圖的類(lèi)型.................462
5.5.3.2.1 Frequency類(lèi)型的直方圖 ...........463
5.5.3.2.2 Height Balanced類(lèi)型的直方圖 ..................471
5.5.3.3 直方圖的收集方法................................475
5.5.3.4 直方圖對(duì) CBO的影響 ....................477
5.5.3.4.1 直方圖對(duì) Shared Cursor的影響 .............477
5.5.3.4.2 直方圖對(duì)可選擇率的影響 ................482
5.5.3.5 使用直方圖的注意事項(xiàng)...........................495
5.6 全局統(tǒng)計(jì)信息 ............................496
5.7 動(dòng)態(tài)采樣............................507
5.8 多列統(tǒng)計(jì)信息 ...........................516
5.9 系統(tǒng)統(tǒng)計(jì)信息 .............................519
5.10 數(shù)據(jù)字典統(tǒng)計(jì)信息..................536
5.11內(nèi)部對(duì)象統(tǒng)計(jì)信息 .............................539
5.12 Oracle里的自動(dòng)統(tǒng)計(jì)信息收集 .........................546
5.13 Oracle里應(yīng)如何收集統(tǒng)計(jì)信息 ..........................563
5.14 總結(jié).........................567
第 6章 Oracle里的 Hint..................... 568
6.1 什么是 Hint .....................568
6.2 Hint的用法 ...............................576
6.3 Hint被 Oracle忽略的常見(jiàn)情形 ...............590
6.3.1 情形一：使用的 Hint有語(yǔ)法或者拼寫(xiě)錯(cuò)誤.................591
6.3.2 情形二：使用的 Hint無(wú)效.........................592
6.3.3 情形三：使用的 Hint自相矛盾..............597
6.3.4 情形四：使用的 Hint受到了查詢轉(zhuǎn)換的干擾...........599
6.3.5 情形五：使用的 Hint受到了保留關(guān)鍵字的干擾.........602
6.4 常見(jiàn)的 Hint ...............605
6.4.1 與優(yōu)化器模式相關(guān)的 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 與表訪問(wèn)相關(guān)的 Hint...................615
FULL .......................615
6.4.2.1 ROWID .............615
6.4.2.2
6.4.3 與索引訪問(wèn)相關(guān)的 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 與表連接順序相關(guān)的 Hint...................624
ORDERED ................................624
6.4.4.1 LEADING ........................626
6.4.4.2
6.4.5 與表連接方法相關(guān)的 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 與查詢轉(zhuǎn)換相關(guān)的 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 與并行相關(guān)的 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 其他常見(jiàn) 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錯(cuò)誤的實(shí)例...............682
6.6 總結(jié)...............................693
第 7章 Oracle里的并行 ..................... 695
7.1 Oracle里并行的基本概念 ...............695
7.1.1 為什么要用并行.................695
7.1.2 并行的理論基礎(chǔ)....................696
7.1.3 Oracle里能夠并行執(zhí)行的操作...............697
7.1.4 Oracle里與并行有關(guān)的術(shù)語(yǔ)及解釋 ..................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 數(shù)據(jù)傳遞方法.............721
7.1.4.5 granules............735
7.1.4.6 直接讀取 ............737
7.1.5 深入解析并行執(zhí)行計(jì)劃的實(shí)例.............746
7.2 Oracle里并行的控制 ..........755
7.2.1 Oracle里如何開(kāi)啟并行 .............755
7.2.2 Oracle里并行度的控制 .............760
7.2.3 Oracle RAC環(huán)境下的并行...........771
7.2.4 Oracle里與并行相關(guān)的參數(shù) ..............775
PARALLEL_MAX_SERVERS .........775
7.2.4.1 PARALLEL_MIN_SERVERS ......................776
7.2.4.2
7.2.4.3 自動(dòng)并行相關(guān)的參數(shù) ...................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 自適應(yīng)并行相關(guān)的參數(shù)...............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 其他參數(shù) ............................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 繞開(kāi) Oracle并行執(zhí)行 Bug大幅提升性能的實(shí)例 ................779
7.3 總結(jié)..........................805
第 8章 Oracle里 SQL優(yōu)化的方法論 ................... 807
8.1 Oracle里如何做 SQL優(yōu)化...................807
8.1.1 Oracle里 SQL優(yōu)化的本質(zhì)是基于對(duì) CBO和執(zhí)行計(jì)劃的深刻理解 ......807
8.1.2 Oracle里 SQL優(yōu)化需要聯(lián)系實(shí)際的業(yè)務(wù) ...................819
8.1.3 Oracle里 SQL優(yōu)化需要適時(shí)使用綁定變量 ............824
8.2 Oracle里 SQL優(yōu)化的方法論在實(shí)戰(zhàn)中的驗(yàn)證...............824
8.3 總結(jié)......................841