看懂執(zhí)行計劃是作為處理數(shù)據(jù)庫性能問題的必備技能之一，接下來一系列的文章我 會告訴你怎么去做。

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

一些規(guī)則外的異常不可避免，所以在一些簡單的計劃后，我們會繼續(xù)了解更復雜的計劃，包括更新，刪除，子查詢分解和標量子查詢。 之后繼續(xù)學習分布式查詢，分區(qū)表對于計劃的影響，以及并行執(zhí)行。

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

什么是執(zhí)行計劃

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

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

這是簡單的兩表關聯(lián)； 單列關聯(lián)條件，有兩個單表謂詞過濾數(shù)據(jù)。 當我們查看這個語句時，我們希望能回答以下的問題 -這些問題不一定互相獨立：

? Oracle 會先訪問哪張表 ?

? Oracle 怎么訪問那張表，通過索引或者表掃描 ?

? 它能獲取多少數(shù)據(jù)？

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

? Oracle 會用哪種方式連接兩張表？

? 通過連接列謂詞條件會生成多少數(shù)據(jù)？

? 在連接后應用謂詞會有多少數(shù)據(jù)被過濾？

? Oracle 的預測以及真正執(zhí)行結果會有顯著的區(qū)別嗎？

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

? Oracle 的執(zhí)行策略符合我們的預期嗎？

? 我們可以找出為什么 Oracle 不執(zhí)行我們認為更好的策略的原因嗎？

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

這里有兩個關于這個查詢的執(zhí)行計劃，在我們開始解釋執(zhí)行計劃前需要了解一些我們必須考慮的問題。 這些計劃來自 11.2.0.4的數(shù)據(jù)庫環(huán)境。

預測的執(zhí)行計劃

---------------------------------------------------------------------------------------
| 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))

真實的執(zhí)行計劃

----------------------------------------------------------------------------------------
| 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))

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

成都創(chuàng)新互聯(lián)是一家集網(wǎng)站建設,徐匯企業(yè)網(wǎng)站建設,徐匯品牌網(wǎng)站建設,網(wǎng)站定制,徐匯網(wǎng)站建設報價,網(wǎng)絡營銷,網(wǎng)絡優(yōu)化,徐匯網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強企業(yè)競爭力?？沙浞譂M足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時我們時刻保持專業(yè)、時尚、前沿，時刻以成就客戶成長自我，堅持不斷學習、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實用型網(wǎng)站。

獲取執(zhí)行計劃

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

解釋計劃

SQL*Plus 會話 :

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

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

第一點，如果你的查詢包含綁定變量時 (類似上面的例子 )， "explain plan"的特點是不知道綁定變量的數(shù)據(jù)類型。 它假設它們是 char類型的，所以我第一個執(zhí)行計劃中的 Predicate Information會顯示強制轉換 to_number()，這會使優(yōu)化器不去考慮本來可以使用的索引，從而導致執(zhí)行計劃的巨大差距。

第二點， Oracle采用綁定變量窺探許多年了，當一條語句第一次被優(yōu)化時，會獲取到綁定變量真實的值。 但是 "explain plan"并不會去嘗試； 它不會去獲取真實的值。 它只會使用幾種基本的規(guī)則去預估謂詞中涉及到的綁定變量的選擇性。 有一些規(guī)則是比較正確的，但是有些就是純粹的猜測 ——通常用 1%或 5%作為選擇性。 對選擇性的錯誤預估會導致對于基數(shù)的錯誤語句最終導致錯誤的執(zhí)行計劃。

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

還有更多的關于" explain plan "的細節(jié)你可能會碰到。調用時有一些額外的選項，在官方手冊中記錄如下 :

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

statement_id默認為空，目標  table就是 plan_table(在新的版本中是全局臨時表 sys.plan_table$的同義詞 )。 可以使用 table和 statement_id參數(shù)來指定想要的語句的執(zhí)行計劃的輸出，函數(shù)的聲明如下：

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

如果無參數(shù)調用 dbms_xplan或者將前兩個參數(shù)賦值為 NULL，則輸出最近解釋的語句。 就輸出格式選項而言， plan table里有許多信息可供選擇顯示，我們會在后續(xù)的章節(jié)里介紹。 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后，可以輸出執(zhí)行的任意 SQL語句的執(zhí)行計劃和執(zhí)行統(tǒng)計信息。 你可以限制只輸出執(zhí)行計劃，只輸出執(zhí)行統(tǒng)計信息，或者全部輸出，你還可以不輸出語句的執(zhí)行結果 (使用 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

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

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

Dbms_xplan.display_cursor()

這是文章中唯一提及的，可以在語句執(zhí)行后從內存中獲取真實執(zhí)行計劃的函數(shù)的選項。 該函數(shù)的定義如下：

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

如果不帶參數(shù)調用，則會返回最近一次執(zhí)行的 SQL 執(zhí)行計劃。有許多原因會導致獲取不到執(zhí)行計劃，有可能是游標不可用了，不過最常見的原因是沒有設置 "set serveroutput off", 這個獲取失敗的執(zhí)行計劃是針對跟在執(zhí)行的語句后的對 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()來獲得上述的第二個真實的執(zhí)行計劃。 這是 Oracle根據(jù)我綁定變量的值、涉及對象的統(tǒng)計信息以及會話的環(huán)境所真正執(zhí)行的路徑。 但是它依然只是返回優(yōu)化器每一步預估的返回的行數(shù)，并不是在執(zhí)行時真正獲取的行數(shù)。 我們會在接下來的話題中繼續(xù)討論。

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

如果不了解最后的用戶做了什么，就會有很多原因導致你被執(zhí)行計劃欺騙。 在生產系統(tǒng)中，最常見的包括 :

?  真實的綁定變量值

?   優(yōu)化器環(huán)境和對象統(tǒng)計信息

?  名稱解析

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

結論

通過這篇文章我們了解了獲取執(zhí)行計劃其實非常簡單，但是計劃會有兩種類別 —預測的和真實的。 也了解到如果語句中存在綁定變量的話，預測的執(zhí)行計劃更傾向于是一個錯誤的執(zhí)行計劃。

一種普遍且相當準確的觀點認為，在生產庫上的執(zhí)行計劃會與在自己環(huán)境執(zhí)行語句后獲取的真正的執(zhí)行計劃一致，但這只能取決于你的環(huán)境是否與生產庫最后用戶執(zhí)行該語句時的環(huán)境十分相似。

我們從真實的執(zhí)行計劃中獲取到的關于 " 體積 "(rows ， bytes) 的信息仍然是通過預估得出的，下一章節(jié)我們會獲取到真實的 " 體積 " 數(shù)據(jù)，這也會幫助我們判斷為什么優(yōu)化器的選擇與我們預期的不符。

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

原文作者： Jonathan Lewis

| 譯者簡介

林錦森·沃趣科技數(shù)據(jù)庫技術專家

沃趣科技數(shù)據(jù)庫工程師，多年從事Oracle數(shù)據(jù)庫，較豐富的故障處理、性能調優(yōu)、數(shù)據(jù)遷移及備份恢復經(jīng)驗

分享名稱：執(zhí)行計劃-1：獲取執(zhí)行計劃
當前路徑：http://www.rwnh.cn/article46/jgpdeg.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)頁設計公司、自適應網(wǎng)站、網(wǎng)站營銷、域名注冊、移動網(wǎng)站建設、手機網(wǎng)站建設

廣告

聲明：本網(wǎng)站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經(jīng)允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)