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

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

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

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

什么是執(zhí)行計(jì)劃

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

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

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

? Oracle 會(huì)先訪問哪張表 ?

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

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

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

? Oracle 會(huì)用哪種方式連接兩張表？

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

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

? Oracle 的預(yù)測(cè)以及真正執(zhí)行結(jié)果會(huì)有顯著的區(qū)別嗎？

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

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

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

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

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

預(yù)測(cè)的執(zhí)行計(jì)劃

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

真實(shí)的執(zhí)行計(jì)劃

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

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

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

獲取執(zhí)行計(jì)劃

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

解釋計(jì)劃

SQL*Plus 會(huì)話 :

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

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

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

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

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

還有更多的關(guān)于" explain plan "的細(xì)節(jié)你可能會(huì)碰到。調(diào)用時(shí)有一些額外的選項(xiàng)，在官方手冊(cè)中記錄如下 :

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

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

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

如果無參數(shù)調(diào)用 dbms_xplan或者將前兩個(gè)參數(shù)賦值為 NULL，則輸出最近解釋的語句。 就輸出格式選項(xiàng)而言， plan table里有許多信息可供選擇顯示，我們會(huì)在后續(xù)的章節(jié)里介紹。 filter選項(xiàng)允許你限制 plan_table返回的行 —這幾乎用不到。

Autotrace

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

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

在 SQL*PLUS中開啟 autotrace后，可以輸出執(zhí)行的任意 SQL語句的執(zhí)行計(jì)劃和執(zhí)行統(tǒng)計(jì)信息。 你可以限制只輸出執(zhí)行計(jì)劃，只輸出執(zhí)行統(tǒng)計(jì)信息，或者全部輸出，你還可以不輸出語句的執(zhí)行結(jié)果 (使用 traceonly選項(xiàng) )。 下面是一個(gè)我使用 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

這對(duì)于只想知道執(zhí)行一條語句 Oracle 做了多少工作量來說十分方便，因?yàn)槲也恍枰@取結(jié)果集，或者將結(jié)果集存儲(chǔ)在客戶端。

對(duì)于獲取真實(shí)的執(zhí)行計(jì)劃來說， autotrace依然做不到，它只是簡(jiǎn)單的在后臺(tái)執(zhí)行 "explain plan"并且調(diào)用 "dbms_xplan.display"； 此外，如果你設(shè)置 "setautotrace traceonly explain"并且執(zhí)行一條查詢語句，該選項(xiàng)因?yàn)椴⒉环祷卣鎸?shí)的結(jié)果，所以 Oracle根本不會(huì)執(zhí)行這條語句。 但是如果是 insert、 update、 delete或者 merge語句，會(huì)真實(shí)執(zhí)行，并輸出影響的行數(shù)，提醒你是否需要回滾。

Dbms_xplan.display_cursor()

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

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

如果不帶參數(shù)調(diào)用，則會(huì)返回最近一次執(zhí)行的 SQL 執(zhí)行計(jì)劃。有許多原因會(huì)導(dǎo)致獲取不到執(zhí)行計(jì)劃，有可能是游標(biāo)不可用了，不過最常見的原因是沒有設(shè)置 "set serveroutput off", 這個(gè)獲取失敗的執(zhí)行計(jì)劃是針對(duì)跟在執(zhí)行的語句后的對(duì) dbms_output(BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;) 的調(diào)用，可以看到如下信息 :

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.

我調(diào)用 dbms_xplan.display_cursor()來獲得上述的第二個(gè)真實(shí)的執(zhí)行計(jì)劃。 這是 Oracle根據(jù)我綁定變量的值、涉及對(duì)象的統(tǒng)計(jì)信息以及會(huì)話的環(huán)境所真正執(zhí)行的路徑。 但是它依然只是返回優(yōu)化器每一步預(yù)估的返回的行數(shù)，并不是在執(zhí)行時(shí)真正獲取的行數(shù)。 我們會(huì)在接下來的話題中繼續(xù)討論。

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

如果不了解最后的用戶做了什么，就會(huì)有很多原因?qū)е履惚粓?zhí)行計(jì)劃欺騙。 在生產(chǎn)系統(tǒng)中，最常見的包括 :

?  真實(shí)的綁定變量值

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

?  名稱解析

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

結(jié)論

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

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

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

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

原文作者： Jonathan Lewis

| 譯者簡(jiǎn)介

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

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

分享名稱：執(zhí)行計(jì)劃-1：獲取執(zhí)行計(jì)劃
當(dāng)前路徑：http://chinadenli.net/article46/jgpdeg.html

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

廣告

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