<form id="tznrh"><form id="tznrh"><th id="tznrh"></th></form></form>

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

            我們會從一些文章開始討論幾種獲取執行計劃的方法,并且評估不同來源的執行計劃最適合哪種場景。完成上述的內容后我們會繼續深入解釋簡單執行計劃的基礎,之后會介紹一個用來解釋大部分執行計劃的規則-需要小心使用該規則。同時也會重點說明因為優化器改寫我們的SQL導致執行計劃與預期的不符的情況。一些規則外的異常不可避免,所以在一些簡單的計劃后,我們會繼續了解更復雜的計劃,包括更新,刪除,子查詢分解和標量子查詢。之后繼續學習分布式查詢,分區表對于計劃的影響,以及并行執行。這些文章不會包含所有的執行計劃里的操作和選項,但是會包含一些重要的足以處理大部分執行計劃的操作。


            什么是執行計劃

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


            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 loopt2關聯,通過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命令行使用Oracledbms_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預估會有45rowid會被獲取到,但是tableaccess預估返回500行數據,45rowid不可能對應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$的同義詞)。可以使用tablestatement_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數據庫,較豐富的故障處理、性能調優、數據遷移及備份恢復經驗






            沃趣科技,讓客戶用上更好的數據庫技術!
            三分快3