select t1.v1, t2.v1 From t1, t2 where t1.n2= :b1 And t2.id= t1.id And t2.n2 between :b2 and :b3;
---------------------------------------------------------------------------------------
| 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
| 譯者簡介
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
