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

            一些額外的步驟可以幫助我們模擬真實的環境來判斷執行計劃是否存在問題。這部分我們會熟悉整體解釋執行計劃的基本準則。在第四部分前,我們不用考慮使用謂詞的部分。

            The Shape of a plan

            我們會從一個簡單的例子開始-創建幾張表,連接它們,在我們檢查執行計劃的時候需要回答提出的問題。這里是數據生成的腳本:


                create table t1
            as
            with generator as (
                select  --+ materialize
                    rownum id 
                from dual 
                connect by 
                    level <= 1e4 ) select rownum id, mod(rownum,1000) n_1000, lpad(rownum,6,"0") v1, rpad("x",100,"x") padding from generator ; alter table t1 add constraint t1_pk primary key(id); create index t1_i1 on t1(n_1000); begin dbms_stats.gather_table_stats( ownname => user,
                    tabname      =>"T1",
                    method_opt   => "for all columns size 1"
                );
            end;
            /


            我按照同樣的腳本新建了一張t2表,這里就不重復了。接下來我會使用explain plan解釋一條簡單的SQL語句來介紹執行計劃中的要點。


            explain plan for
            select
                   t1.v1,t2.v1
            from
                   t1,t2
            where
                   t1.n_1000= 1
            andt2.id     = t1.id
            andt2.n_1000= 100
            ;
            
            select * from table(dbms_xplan.display);
            
            select
                   id,parent_id, position,
                   depth,level ? 1 old_depth,
                   rpad("",level - 1) ||
                          operation|| " " ||
                          lower(options)|| " " ||
                          object_name               text_line
            from
                   plan_table
            start with
                   id= 0
            connect by
                   parent_id= prior id
            order siblings by
                   id,position
            ;


            我用兩種不同的方法生成執行計劃---一種是對dbms_xplan.display()的調用,另一種是在9i或者更早版本種對于plan table的簡化查詢。

            以下是兩種方法生成的結果:


            --------------------------------------------------------------------------------------
            | Id | Operation                    |Name  | Rows  | Bytes | Cost (%CPU)| Time     |
            --------------------------------------------------------------------------------------
            |   0| SELECT STATEMENT             |       |   10 |   300 |    22  (0)| 00:00:01 |
            |*  1|  HASH JOIN                   |       |   10 |   300 |    22  (0)| 00:00:01 |
            |   2|   TABLE ACCESS BY INDEX ROWID| T1    |   10 |   150 |    11  (0)| 00:00:01 |
            |*  3|    INDEX RANGE SCAN          | T1_I1 |    10 |      |     1   (0)| 00:00:01 |
            |   4|   TABLE ACCESS BY INDEX ROWID| T2    |   10 |   150 |    11  (0)| 00:00:01 |
            |*  5|    INDEX RANGE SCAN          | T2_I1 |    10 |      |     1   (0)| 00:00:01 |
            --------------------------------------------------------------------------------------
            
            Predicate Information (identified byoperation id):
            ---------------------------------------------------
               1- access("T2"."ID"="T1"."ID")
               3- access("T1"."N_1000"=1)
               5- access("T2"."N_1000"=100)
            
             Id  Par  Pos DEPTH OLD_DEPTH TEXT_LINE
            ---- ---- ---- ----- -----------------------------------------------------------
              0        22     0        0 SELECT STATEMENT
              1    0    1    1         1  HASH JOIN
              2    1    1    2         2   TABLE ACCESS by index rowid T1
              3    2    1    3         3    INDEX range scan T1_I1
              4    1    2    2         2   TABLE ACCESS by index rowid T2
              5    4    1    3         3    INDEX range scan T2_I1


            我之所以列出較早版本的獲取執行計劃的方法,是因為這樣可以讓你將調用dbms_xplan.display的結果的直觀展示與plan table中的被隱藏的細節相關聯。我們總是看到執行計劃都是呈鋸齒狀,這種方式是為了告訴我們執行計劃中操作的關系。也直觀的展示id,parent_id和position列之間的關系。

            計劃中每個操作都有id,這個實際上告訴我們每行輸出的順序。每行都有可能是1個或多個子操作的父操作,每行的parent_id列都會指向這個id的父id。這里例子中我們看Id為2跟4的行,都是Id為1的子操作。如果一行有多個子操作,position列就會列出子操作的順序,我們看到Id為2的行的position是1,代表他是Id為1的行下第1個子操作,Id為4則是第2個子操作。在dbms_xplan生成的執行計劃中我們看不到parent_id和position列,我們需要在很多操作中根據順序和鋸齒的形狀來判斷父/子關系。


            根據id順序列出的行總是能給我們正確計劃的順序(但是這并不是獲取跟操作數據的順序);而且計算在鋸齒狀中的行的方法在9i跟10g中也不一樣。以前的版本中,來自分級connect by查詢的派生列級別允許我們向文本添加適當大小的縮進---但是當Oracle允許我們從內存中獲取執行計劃的版本(v$sql_plan)時,這種方法就變得十分低效,因此,動態性能視圖中包含了一個預先計算的level列(與level相差1,稱為depth),其值在語句被優化時獲得,并最終將其添加到plan表中(即使在12c中也有一些生成的值不正確的情況,所以記住怎么根據connect by查詢獲得執行計劃是很有幫助的)。

            First Rule for Reading Plans

            這篇文章我們不涉及謂詞部分,即使它們對于執行計劃來說也很重要,我們重點在怎么遍歷執行計劃主體,并了解Oracle獲取和操作數據的順序。


            計劃中的每一行都代表一個生成一些"rowsource"(行源)的行操作。這里的行也可能只是從索引中獲取到的rowid。一個操作需要幾個動作生成一個rowsource,之后將rowsource傳遞給父操作。如果一個父操作有多個子操作,它會依次調用每個子操作,并通過一些工作將它們提供的rowsource進行合并。你需要學習的最重要的一點是每個操作做了什么,以及該操作"合并"rowsources意味著什么。還有一個稍微復雜的問題是,盡管父操作“依次”調用其子操作,但它可能會多次調用每個子進程,而且重復調用的方式會隨著父進程的操作而變化。


            這篇文章不可能包含所有的變化和異常情況,但是如果我們暫時忽略特殊情況,那么閱讀執行計劃的基本方法總結為:"子操作按先后順序執行,遞歸向下"。我們通過一個hash join的執行計劃來看看是如何工作的。


            第0行告訴我們這是一個select語句。我們需要定義它的子集和操作順序來為這個select語句生成rowsource。在用SQL語句輸出的執行計劃中可以看到,第1行是第一個也是唯一的屬于第0行的子行。如果我們沒有parent_id和position列幫助我們判斷,我們也可以用直觀的方法,一個操作的第一個子操作都是在下一行(它會縮進一格),之后通過該行畫條垂直的線,直到執行計劃底部,有相同縮進的行就是這個操作的子操作,并且行的順序就是子操作的順序。


            通過第1行我們知道這是個hash join操作,這里我們可能需要去查看手冊來了解什么是hash join以及它是如何工作的。從SQL生成的執行計劃中可以很方便的看到第2行和第4行是hash join的子項,從直觀展示中也能看到,第2行是一個子操作,之后垂直對齊到第4行的"table access"的T字母,說明它是第二個子操作。這些信息足夠告訴我們,將要在內存里根據t1(第一個子操作)中的一些行建立hash表,并根據t2(第2個子操作)中的行對hash表進行探測,如果符合條件,則構造一個結果集,并返回給在第0行(第1行的父行)。這一系列的hash join操作是一個很好的例子,告訴我們為什么需要考慮子操作的順序,這些物理上的操作順序會告訴我們哪些表是作為build表(即需要在內存中建立的表),哪些是作為probe表(即探測表,不需要放到內存里)。


            此刻我們還不知道如何判斷t1,t2中所需要的行,我們所做的就是將計劃的最頂層的一部分作為開始,了解執行計劃整體的工作。我們還不能說:“這是Oracle獲得的第一個數據集/這是Oracle訪問的第一個表”。但是我們可以通過重復我們目前所采用的方法來達到這一點。


            我們將用第2行中的rowsource構建一個內存中的hash表,然后用第4行中的rowsource探測hash表;我們先來看看第一個子項。從第2行開始,我們可以確定整個“子計劃”,其中rowsource正是我們需要的最終結果:


            --------------------------------------------------------------------------------------
            | Id | Operation                    |Name  | Rows  | Bytes | Cost (%CPU)| Time     |
            --------------------------------------------------------------------------------------
            |   2|   TABLE ACCESS BY INDEX ROWID| T1    |   10 |   150 |    11  (0)| 00:00:01 |
            |*  3|    INDEX RANGE SCAN          | T1_I1 |    10 |      |     1   (0)| 00:00:01 |
            --------------------------------------------------------------------------------------


            從上面分離出來部分的執行計劃可以看到,第2行有個單獨的子操作進行調用,這個子操作是index range scan。我們可以很容易的看到,通過index range scan生成的rowsource可能包含rowid,而且第2行根據rowid進行表掃描---當試圖理解更復雜的執行計劃時,類似這樣的完整性檢查(將父級的需求與子級的提供的數據進行比較)非常有用。

            同樣的我們也可以看看第4行的子計劃:


            --------------------------------------------------------------------------------------
            | Id | Operation                    |Name  | Rows  | Bytes | Cost (%CPU)| Time     |
            --------------------------------------------------------------------------------------
            |   4|   TABLE ACCESS BY INDEX ROWID| T2    |   10 |   150 |    11  (0)| 00:00:01 |
            |*  5|    INDEX RANGE SCAN          | T2_I1 |    10 |      |     1   (0)| 00:00:01 |
            --------------------------------------------------------------------------------------


            同樣這也是一個很簡單的計劃,我們調用第5行的操作進行index range scan,并且在第4行中根據第5行提供的rowid進行訪問表。

            將所有的片段整合一起,我們可以將執行步驟標識出來:


            ------------------------------------------------------
            | Id | Operation                    |Name  | Order |
            ------------------------------------------------------
            |   0| SELECT STATEMENT             |       |    6 |
            |*  1|  HASH JOIN                   |       |    5 |
            |   2|   TABLE ACCESS BY INDEX ROWID| T1    |    2 |
            |*  3|    INDEX RANGE SCAN          | T1_I1 |     1 |
            |   4|   TABLE ACCESS BY INDEX ROWID| T2    |    4 |
            |*  5|    INDEX RANGE SCAN          | T2_I1 |     3 |
            ------------------------------------------------------


            我們根據如下順序解讀執行計劃:

            第0行調用第1行(第1個子操作)。


            第1行調用第2行(第1個子操作)。


            第2行調用第3行(第1個子操作)。


            第3行通過index range scan生成一些rowsource,并將其傳遞到第2行。


            第2行根據rowid訪問t1表并生成rowsource,并傳遞到第1行。


            第1行用這些行源建立內存里的hash表,之后調用第4行(第2個子操作)開始提供用作探測表的rowsource。


            第4行調用第5行(第1個子操作)。


            第5行根據index range scan生成rowsource,并傳遞給第4行。


            第4行根據rowid訪問t2表并生成rowsource,傳遞給第1行。


            第1行探測hash表,并找出符合條件的行,根據結果集生成第5個rowsource,之后傳遞給第0行,這也就是傳遞給客戶端程序的結果。


            計劃中還有比這更多的內容——特別是我們需要更多地考慮操作的時機:有些是“批量”處理,有些是“單行”處理;我們需要引入謂詞部分并考慮“訪問”和“篩選器”的相關性;這些細節我們將在下一篇文章中看到。

            Closing thoughts

            我想強調的是,將復雜的執行計劃分解成簡單的部分是非常方便的。我們例子中的執行計劃很短,所以分塊處理的好處并不是特別明顯,但是想想我們是如何從整體看執行計劃,并選出最前兩行,之后查看一些子計劃。我們可以在任何計劃中使用這個方法,不管它有多復雜,并單獨檢查計劃的小部分。


            原文鏈接:https://www.red-gate.com/simple-talk/sql/oracle/execution-plans-part-3-the-rule/


            原文作者:Jonathan Lewis

            | 譯者簡介
            林錦森·沃趣科技數據庫技術專家
            沃趣科技數據庫工程師,多年從事Oracle數據庫,較豐富的故障處理、性能調優、數據遷移及備份恢復經驗



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