執行計劃-3：解釋規則

    create table t1
as
with generator as (
    select  --+ materialize
        rownum id 
    from dual 
    connect by 
        level <= 1e4
)
select
    rownum          id,
    mod(rownum,1000)    n_1000,
    lpad(rownum,6,'0')  v1,
    rpad('x',100,'x')   padding
from
    generator
;
alter table t1 add constraint t1_pk primary key(id);
create index t1_i1 on t1(n_1000);
begin
    dbms_stats.gather_table_stats(
        ownname      => user,
        tabname      =>'T1',
        method_opt   => 'for all columns size 1'
    );
end;
/

我按照同樣的指令碼新建了一張 t2 表，這裡就不重複了。接下來我會使用 explain plan 解釋一條簡單的 SQL 語句來介紹執行計劃中的要點。

explain plan for
select
       t1.v1,t2.v1
from
       t1,t2
where
       t1.n_1000= 1
andt2.id     = t1.id
andt2.n_1000= 100
;
select * from table(dbms_xplan.display);
select
       id,parent_id, position,
       depth,level ? 1 old_depth,
       rpad('',level - 1) ||
              operation|| ' ' ||
              lower(options)|| ' ' ||
              object_name               text_line
from
       plan_table
start with
       id= 0
connect by
       parent_id= prior id
order siblings by
       id,position
;

我用兩種不同的方法生成執行計劃 --- 一種是對 dbms_xplan.display() 的呼叫，另一種是在 9i 或者更早版本種對於 plan table 的簡化查詢。

以下是兩種方法生成的結果 ：

--------------------------------------------------------------------------------------
| Id | Operation                    |Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0| SELECT STATEMENT             |       |   10 |   300 |    22  (0)| 00:00:01 |
|*  1|  HASH JOIN                   |       |   10 |   300 |    22  (0)| 00:00:01 |
|   2|   TABLE ACCESS BY INDEX ROWID| T1    |   10 |   150 |    11  (0)| 00:00:01 |
|*  3|    INDEX RANGE SCAN          | T1_I1 |    10 |      |     1   (0)| 00:00:01 |
|   4|   TABLE ACCESS BY INDEX ROWID| T2    |   10 |   150 |    11  (0)| 00:00:01 |
|*  5|    INDEX RANGE SCAN          | T2_I1 |    10 |      |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
   1- access("T2"."ID"="T1"."ID")
   3- access("T1"."N_1000"=1)
   5- access("T2"."N_1000"=100)
 Id  Par  Pos DEPTH OLD_DEPTH TEXT_LINE
---- ---- ---- ----- -----------------------------------------------------------
  0        22     0        0 SELECT STATEMENT
  1    0    1    1         1  HASH JOIN
  2    1    1    2         2   TABLE ACCESS by index rowid T1
  3    2    1    3         3    INDEX range scan T1_I1
  4    1    2    2         2   TABLE ACCESS by index rowid T2
  5    4    1    3         3    INDEX range scan T2_I1

我之所以列出較早版本的獲取執行計劃的方法，是因為這樣可以讓你將呼叫dbms_xplan.display 的結果的直觀展示與plan table 中的被隱藏的細節相關聯。我們總是看到執行計劃都是呈鋸齒狀，這種方式是為了告訴我們執行計劃中操作的關係。也直觀的展示id ，parent_id 和position 列之間的關係。

計劃中每個操作都有 id ，這個實際上告訴我們每行輸出的順序。每行都有可能是 1 個或多個子操作的父操作，每行的 parent_id 列都會指向這個 id 的父 id 。這裡例子中我們看 Id 為 2 跟 4 的行，都是 Id 為 1 的子操作。如果一行有多個子操作， position 列就會列出子操作的順序，我們看到 Id 為 2 的行的 position 是 1 ，代表他是 Id 為 1 的行下第 1 個子操作， Id 為 4 則是第 2 個子操作。在 dbms_xplan 生成的執行計劃中我們看不到 parent_id 和 position 列，我們需要在很多操作中根據順序和鋸齒的形狀來判斷父 / 子關係。

根據id 順序列出的行總是能給我們正確計劃的順序( 但是這並不是獲取跟運算元據的順序); 而且計算在鋸齒狀中的行的方法在9i 跟10g 中也不一樣。以前的版本中，來自分級connect by 查詢的派生列級別允許我們向文字新增適當大小的縮排--- 但是當Oracle 允許我們從記憶體中獲取執行計劃的版本(v$sql_plan) 時，這種方法就變得十分低效，因此，動態效能檢視中包含了一個預先計算的level 列( 與level 相差1 ，稱為depth) ，其值在語句被最佳化時獲得，並最終將其新增到plan 表中( 即使在12c 中也有一些生成的值不正確的情況，所以記住怎麼根據connect by 查詢獲得執行計劃是很有幫助的)。

First Rule for Reading Plans

這篇文章我們不涉及謂詞部分，即使它們對於執行計劃來說也很重要，我們重點在怎麼遍歷執行計劃主體，並瞭解 Oracle 獲取和運算元據的順序。

計劃中的每一行都代表一個生成一些 "rowsource"( 行源 ) 的行操作。這裡的行也可能只是從索引中獲取到的 rowid 。一個操作需要幾個動作生成一個 rowsource ，之後將 rowsource 傳遞給父操作。如果一個父操作有多個子操作，它會依次呼叫每個子操作，並透過一些工作將它們提供的 rowsource 進行合併。你需要學習的最重要的一點是每個操作做了什麼，以及該操作 " 合併 "rowsources 意味著什麼。還有一個稍微複雜的問題是，儘管父操作“依次”呼叫其子操作，但它可能會多次呼叫每個子程式，而且重複呼叫的方式會隨著父程式的操作而變化。

這篇文章不可能包含所有的變化和異常情況，但是如果我們暫時忽略特殊情況，那麼閱讀執行計劃的基本方法總結為 :" 子操作按先後順序執行，遞迴向下 " 。我們透過一個 hash join 的執行計劃來看看是如何工作的。

第 0 行告訴我們這是一個 select 語句。我們需要定義它的子集和操作順序來為這個 select 語句生成 rowsource 。在用 SQL 語句輸出的執行計劃中可以看到，第 1 行是第一個也是唯一的屬於第 0 行的子行。如果我們沒有 parent_id 和 position 列幫助我們判斷，我們也可以用直觀的方法，一個操作的第一個子操作都是在下一行 ( 它會縮排一格 ) ，之後透過該行畫條垂直的線，直到執行計劃底部，有相同縮排的行就是這個操作的子操作，並且行的順序就是子操作的順序。

透過第 1 行我們知道這是個 hash join 操作，這裡我們可能需要去檢視手冊來了解什麼是 hash join 以及它是如何工作的。從 SQL 生成的執行計劃中可以很方便的看到第 2 行和第 4 行是 hash join 的子項，從直觀展示中也能看到，第 2 行是一個子操作，之後垂直對齊到第 4 行的 "table access" 的 T 字母，說明它是第二個子操作。這些資訊足夠告訴我們，將要在記憶體里根據 t1( 第一個子操作 ) 中的一些行建立 hash 表，並根據 t2( 第 2 個子操作 ) 中的行對 hash 表進行探測，如果符合條件，則構造一個結果集，並返回給在第 0 行 ( 第 1 行的父行 ) 。這一系列的 hash join 操作是一個很好的例子，告訴我們為什麼需要考慮子操作的順序，這些物理上的操作順序會告訴我們哪些表是作為 build 表 ( 即需要在記憶體中建立的表 ) ，哪些是作為 probe 表 ( 即探測表，不需要放到記憶體裡 ) 。

此刻我們還不知道如何判斷 t1 ， t2 中所需要的行，我們所做的就是將計劃的最頂層的一部分作為開始，瞭解執行計劃整體的工作。我們還不能說 : “這是 Oracle 獲得的第一個資料集 / 這是 Oracle 訪問的第一個表”。但是我們可以透過重複我們目前所採用的方法來達到這一點。

我們將用第 2 行中的 rowsource 構建一個記憶體中的 hash 表，然後用第 4 行中的 rowsource 探測 hash 表 ; 我們先來看看第一個子項。從第 2 行開始，我們可以確定整個“子計劃”，其中 rowsource 正是我們需要的最終結果：

--------------------------------------------------------------------------------------
| Id | Operation                    |Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   2|   TABLE ACCESS BY INDEX ROWID| T1    |   10 |   150 |    11  (0)| 00:00:01 |
|*  3|    INDEX RANGE SCAN          | T1_I1 |    10 |      |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

從上面分離出來部分的執行計劃可以看到，第 2 行有個單獨的子操作進行呼叫，這個子操作是 index range scan 。我們可以很容易的看到，透過 index range scan 生成的 rowsource 可能包含 rowid ，而且第 2 行根據 rowid 進行表掃描 --- 當試圖理解更復雜的執行計劃時，類似這樣的完整性檢查 ( 將父級的需求與子級的提供的資料進行比較 ) 非常有用。

同樣的我們也可以看看第 4 行的子計劃：

--------------------------------------------------------------------------------------
| Id | Operation                    |Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   4|   TABLE ACCESS BY INDEX ROWID| T2    |   10 |   150 |    11  (0)| 00:00:01 |
|*  5|    INDEX RANGE SCAN          | T2_I1 |    10 |      |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

同樣這也是一個很簡單的計劃，我們呼叫第 5 行的操作進行 index range scan ，並且在第 4 行中根據第 5 行提供的 rowid 進行訪問表。

將所有的片段整合一起，我們可以將執行步驟標識出來 :

------------------------------------------------------
| Id | Operation                    |Name  | Order |
------------------------------------------------------
|   0| SELECT STATEMENT             |       |    6 |
|*  1|  HASH JOIN                   |       |    5 |
|   2|   TABLE ACCESS BY INDEX ROWID| T1    |    2 |
|*  3|    INDEX RANGE SCAN          | T1_I1 |     1 |
|   4|   TABLE ACCESS BY INDEX ROWID| T2    |    4 |
|*  5|    INDEX RANGE SCAN          | T2_I1 |     3 |
------------------------------------------------------

我們根據如下順序解讀執行計劃：

第 0 行呼叫第 1 行 ( 第 1 個子操作 )。

第 1 行呼叫第 2 行 ( 第 1 個子操作 )。

第 2 行呼叫第 3 行 ( 第 1 個子操作 )。

第 3 行透過 index range scan 生成一些 rowsource ，並將其傳遞到第 2 行。

第 2 行根據 rowid 訪問 t1 表並生成 rowsource ，並傳遞到第 1 行。

第 1 行用這些行源建立記憶體裡的 hash 表，之後呼叫第 4 行 ( 第 2 個子操作 ) 開始提供用作探測表的 rowsource。

第 4 行呼叫第 5 行 ( 第 1 個子操作 )。

第 5 行根據 index range scan 生成 rowsource ，並傳遞給第 4 行。

第 4 行根據 rowid 訪問 t2 表並生成 rowsource ，傳遞給第 1 行。

第 1 行探測 hash 表，並找出符合條件的行，根據結果集生成第 5 個 rowsource ，之後傳遞給第 0 行，這也就是傳遞給客戶端程式的結果。

計劃中還有比這更多的內容——特別是我們需要更多地考慮操作的時機: 有些是“批次”處理，有些是“單行”處理; 我們需要引入謂詞部分並考慮“訪問”和“篩選器”的相關性; 這些細節我們將在下一篇文章中看到。

Closing thoughts

我想強調的是，將複雜的執行計劃分解成簡單的部分是非常方便的。我們例子中的執行計劃很短，所以分塊處理的好處並不是特別明顯，但是想想我們是如何從整體看執行計劃，並選出最前兩行，之後檢視一些子計劃。我們可以在任何計劃中使用這個方法，不管它有多複雜，並單獨檢查計劃的小部分。

原文連結 :

https://www.red-gate.com/simple-talk/sql/oracle/execution-plans-part-3-the-rule/

原文作者 : Jonathan Lewis

| 譯者簡介

林錦森·沃趣科技資料庫技術專家

沃趣科技資料庫工程師，多年從事Oracle資料庫，較豐富的故障處理、效能調優、資料遷移及備份恢復經驗