看懂執行計劃是作為處理資料庫效能問題的必備技能之一，接下來一系列的文章我 會告訴你怎麼去做。

我們會從一些文章開始討論幾種獲取執行計劃的方法，並且評估不同來源的執行計劃最適合哪種場景。 完成上述的內容後我們會繼續深入解釋簡單執行計劃的基礎，之後會介紹一個用來解釋大部分執行計劃的規則 -需要小心使用該規則。 同時也會重點說明因為最佳化器改寫我們的 SQL導致執行計劃與預期的不符的情況。

一些規則外的異常不可避免，所以在一些簡單的計劃後，我們會繼續瞭解更復雜的計劃，包括更新，刪除，子查詢分解和標量子查詢。 之後繼續學習分散式查詢，分割槽表對於計劃的影響，以及並行執行。

這些文章不會包含所有的執行計劃裡的操作和選項，但是會包含一些重要的足以處理大部分執行計劃的操作。

什麼是執行計劃

當我們寫了一條 SQL語句時，我們可能會思考 Oracle會怎麼產生結果。 執行計劃是 Oracle將 SQL語句轉變為一系列執行步驟，並且得出我們想要的結果的執行路徑。 無論在語句執行前就生成的執行計劃，還是真正執行時生成的計劃，我們解釋計劃的方法都是一樣的。 然而根據什麼時候以及如何獲取執行計劃，詳細程度 (甚至我們對正確執行計劃的信心程度 )都會不同。 例如，有以下查詢：

select
t1.v1, t2.v1
From
t1, t2
where
t1.n2= :b1
And t2.id= t1.id
And t2.n2 between :b2 and :b3;

這是簡單的兩表關聯； 單列關聯條件，有兩個單表謂詞過濾資料。 當我們檢視這個語句時，我們希望能回答以下的問題 -這些問題不一定互相獨立：

• Oracle 會先訪問哪張表 ?

• Oracle 怎麼訪問那張表，透過索引或者表掃描 ?

• 它能獲取多少資料？

• 接下來會以何種方式訪問哪張表？

• Oracle 會用哪種方式連線兩張表？

• 透過連線列謂詞條件會生成多少資料？

• 在連線後應用謂詞會有多少資料被過濾？

• Oracle 的預測以及真正執行結果會有顯著的區別嗎？

• 我們能不能快速確定為什麼會發生 ( 上一條內容 ) ，以及有何影響？

• Oracle 的執行策略符合我們的預期嗎？

• 我們可以找出為什麼 Oracle 不執行我們認為更好的策略的原因嗎？

這個例子中，我們假設 Oracle 利用 n2 上面的索引找到 t1 中的一小部分的行，接下來使用 nested loop 跟 t2 關聯，透過 t2 上的主鍵索引與 t1 中的過濾後的每行資料進行匹配，獲取匹配成功的行，最後丟棄大部分不符合 (t2.n2between :b2 and :b3) 條件的行。

這裡有兩個關於這個查詢的執行計劃，在我們開始解釋執行計劃前需要了解一些我們必須考慮的問題。 這些計劃來自 11.2.0.4的資料庫環境。

預測的執行計劃

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |    32 |   832 |    46   (3)| 00:00:01 |
|*  1 |  FILTER                       |       |       |       |            |          |
|*  2 |   HASH JOIN                   |       |    32 |   832 |    46   (3)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL          | T1    |    32 |   416 |    24   (5)| 00:00:01 |
|   4 |    TABLE ACCESS BY INDEX ROWID| T2    |   500 |  6500 |    22   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN          | T2_N2 |    45 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:B2)<=TO_NUMBER(:B3))
2 - access("T2"."ID"="T1"."ID")
3 - filter("T1"."N2"=TO_NUMBER(:B1))
5 - access("T2"."N2">=TO_NUMBER(:B2) AND "T2"."N2"<=TO_NUMBER(:B3))

真實的執行計劃

----------------------------------------------------------------------------------------
| Id  | Operation                      | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |       |       |       |    33 (100)|          |
|*  1 |  FILTER                        |       |       |       |            |          |
|   2 |   NESTED LOOPS                 |       |    17 |   442 |    33   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                |       |    17 |   442 |    33   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1    |    17 |   221 |    16   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | T1_N2 |    17 |       |     1   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN          | T2_PK |     1 |       |     0   (0)|          |
|*  7 |    TABLE ACCESS BY INDEX ROWID | T2    |     1 |    13 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:B3>=:B2)
5 - access("T1"."N2"=:B1)
6 - access("T2"."ID"="T1"."ID")
7 - filter(("T2"."N2">=:B2 AND "T2"."N2"<=:B3))

這兩個執行計劃明顯不同 ——即使我是在相同的會話裡分別產生的。 主要的不同體現在兩方面； 第一，計劃主體行數不同，一個 6行，另一個有 8行； 另一個就是 Predicate Information(謂詞資訊 )，一個明確顯示了所有繫結變數的強制轉換 (e.g. to_number(:B3))，另一個並沒有這種資訊。 如果我們要根據執行計劃來幫助我們提高系統的效率，我們必須知道為什麼會有這種自相矛盾現象發生，並且需要確定可以多大程度上相信 Oracle給我們的執行計劃。 接下來一起看看常見的獲取執行計劃的方法以及每種方法的侷限性。

獲取執行計劃

即使有許多可以透過圖形化介面生成執行計劃的工具，我更偏向於在 SQL命令列使用 Oracle的 dbms_xplan包。 不論你使用什麼工具，得到的資訊都是幾乎一樣的，但是如果你需要在不同的論壇或者會議上分享你的執行計劃的話，那麼透過 dbms_xplan生成的執行計劃的格式是大部分人所熟悉和接受的。

解釋計劃

SQL*Plus 會話 :

explain plan for  (your select statement)
select * from table(dbms_xplan.display);

這是最簡單也是最基本的方法獲取到當你執行語句時， Oracle"預測 "的可能會採用的執行計劃； 上述第一種執行計劃就是透過這種方式得到的。 這種獲取的方法存在一些問題，一些明顯的問題上面已經闡述了，下面說些更深層的原因。

第一點，如果你的查詢包含繫結變數時 (類似上面的例子 )， "explain plan"的特點是不知道繫結變數的資料型別。 它假設它們是 char型別的，所以我第一個執行計劃中的 Predicate Information會顯示強制轉換 to_number()，這會使最佳化器不去考慮本來可以使用的索引，從而導致執行計劃的巨大差距。

第二點， Oracle採用繫結變數窺探許多年了，當一條語句第一次被最佳化時，會獲取到繫結變數真實的值。 但是 "explain plan"並不會去嘗試； 它不會去獲取真實的值。 它只會使用幾種基本的規則去預估謂詞中涉及到的繫結變數的選擇性。 有一些規則是比較正確的，但是有些就是純粹的猜測 ——通常用 1%或 5%作為選擇性。 對選擇性的錯誤預估會導致對於基數的錯誤語句最終導致錯誤的執行計劃。

附註 :第一個執行計劃就是猜測產生自相矛盾的例子 :在第 4行和第 5行， index range scan預估會有 45個 rowid會被獲取到，但是 tableaccess預估返回 500行資料， 45個 rowid不可能對應 500行資料。 這是索引基於 range_based的預測，並使用了最小的選擇性 0.45%，但是同等情況下表的最小選擇性為 5%，所以導致了這種情況的發生。

還有更多的關於" explain plan "的細節你可能會碰到。呼叫時有一些額外的選項，在官方手冊中記錄如下 :

explain plan
set statement_id = ‘{string}’
into {schema}.{table}@{db_link}
for {statement};

statement_id預設為空，目標  table就是 plan_table(在新的版本中是全域性臨時表 sys.plan_table$的同義詞 )。 可以使用 table和 statement_id引數來指定想要的語句的執行計劃的輸出，函式的宣告如下：

dbms_xplan.display({plan_table},{statement_id},{formatoptions},{filter option})

如果無引數呼叫 dbms_xplan或者將前兩個引數賦值為 NULL，則輸出最近解釋的語句。 就輸出格式選項而言， plan table裡有許多資訊可供選擇顯示，我們會在後續的章節裡介紹。 filter選項允許你限制 plan_table返回的行 —這幾乎用不到。

Autotrace

這是嵌入到 SQL*PLUS 裡的 "explain plan" 的特殊變體，可以使用 set 命令使 autotrace 選項生效：

set autotrace on
set autotrace traceonly
set autotrace traceonly explain
set autotrace traceonly statistics
set autotrace off

在 SQL*PLUS中開啟 autotrace後，可以輸出執行的任意 SQL語句的執行計劃和執行統計資訊。 你可以限制只輸出執行計劃，只輸出執行統計資訊，或者全部輸出，你還可以不輸出語句的執行結果 (使用 traceonly選項 )。 下面是一個我使用 set autotrace to traceonly statistics的輸出例子：

1 row selected.
Statistics
----------------------------------------------------------
   1  recursive calls
   0  db block gets
   36  consistent gets
   0  physical reads
   0  redo size
   471  bytes sent via SQL*Net to client 
   415  bytes received via SQL*Net from client
   2  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
   1  rows processed

這對於只想知道執行一條語句 Oracle 做了多少工作量來說十分方便，因為我不需要獲取結果集，或者將結果集儲存在客戶端。

對於獲取真實的執行計劃來說， autotrace依然做不到，它只是簡單的在後臺執行 "explain plan"並且呼叫 "dbms_xplan.display"； 此外，如果你設定 "setautotrace traceonly explain"並且執行一條查詢語句，該選項因為並不返回真實的結果，所以 Oracle根本不會執行這條語句。 但是如果是 insert、 update、 delete或者 merge語句，會真實執行，並輸出影響的行數，提醒你是否需要回滾。

Dbms_xplan.display_cursor()

這是文章中唯一提及的，可以在語句執行後從記憶體中獲取真實執行計劃的函式的選項。 該函式的定義如下：

dbms_xplan.display_cursor({sql_id},{child_number},{formatoptions})

如果不帶引數呼叫，則會返回最近一次執行的 SQL 執行計劃。有許多原因會導致獲取不到執行計劃，有可能是遊標不可用了，不過最常見的原因是沒有設定 "set serveroutput off", 這個獲取失敗的執行計劃是針對跟在執行的語句後的對 dbms_output(BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;) 的呼叫，可以看到如下資訊 :

SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  b3s1x9zqrvzvc, child number 0
BEGIN DBMS_OUTPUT.ENABLE(1000000); END;
NOTE: cannot fetch plan for SQL_ID: b3s1x9zqrvzvc, CHILD_NUMBER: 0
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)
8 rows selected.

我呼叫 dbms_xplan.display_cursor()來獲得上述的第二個真實的執行計劃。 這是 Oracle根據我繫結變數的值、涉及物件的統計資訊以及會話的環境所真正執行的路徑。 但是它依然只是返回最佳化器每一步預估的返回的行數，並不是在執行時真正獲取的行數。 我們會在接下來的話題中繼續討論。

雖然還有許多關於 dbms_xplan.display_cursor要說的，也有很多使用它的方式。 但是介紹先到此，並且有個小提醒。 雖然它會根據你提供的繫結變數輸出真正的執行計劃 (大部分情況 )，但是不同環境下不能保證是一致的，或者同樣的環境下始終都是一樣的執行計劃。

如果不瞭解最後的使用者做了什麼，就會有很多原因導致你被執行計劃欺騙。 在生產系統中，最常見的包括 :

•  真實的繫結變數值

•   最佳化器環境和物件統計資訊

•  名稱解析

雖然 "dbms_xplan.display_cursor()" 的呼叫結果相對於呼叫 "explain plan" 以及 "dbms_xplan.display()" 的結果來說會真實很多，但是如果你想確保沒有獲取到錯誤的執行計劃，仍然需要一些明智的判斷。

結論

透過這篇文章我們瞭解了獲取執行計劃其實非常簡單，但是計劃會有兩種類別 —預測的和真實的。 也瞭解到如果語句中存在繫結變數的話，預測的執行計劃更傾向於是一個錯誤的執行計劃。

一種普遍且相當準確的觀點認為，在生產庫上的執行計劃會與在自己環境執行語句後獲取的真正的執行計劃一致，但這隻能取決於你的環境是否與生產庫最後使用者執行該語句時的環境十分相似。

我們從真實的執行計劃中獲取到的關於 " 體積 "(rows ， bytes) 的資訊仍然是透過預估得出的，下一章節我們會獲取到真實的 " 體積 " 資料，這也會幫助我們判斷為什麼最佳化器的選擇與我們預期的不符。

原文連結： https://www.red-gate.com/simple-talk/sql/oracle/execution-plans-part-1-finding-plans/

原文作者： Jonathan Lewis

| 譯者簡介

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

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