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

            在第3部分(引用沃趣科技——執行計劃-3:解釋規則),我們用一個簡單的hash join介紹了閱讀執行計劃最基本的規則---”子操作按先后順序執行,遞歸向下“。這讓我們了解查詢中Oracle生成rowsource的順序和訪問中不同物理對象的順序(隱式的)。


            這個系列的開頭,我就強調過規則不是適用于所有的場景,下一部分(第5部分)里我們會來看幾個我們需要特別注意的場景。但是在這部分中,我們將繼續使用更簡單的例子,來了解應用規則時謂詞的選擇時機和使用的一些細節。


            Basics

            這里列出上個部分文章中兩表hash join的執行計劃:


            --------------------------------------------------------------------------------------
            | 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 by operation id):
            ---------------------------------------------------
               1 - access("T2"."ID"="T1"."ID")
               3 - access("T1"."N_1000"=1)
               5 - access("T2"."N_1000"=100)


            通過"子操作按先后順序執行"這個規則,我們可以知道運行引擎會從第3行的范圍掃描索引t1_i1開始,之后第2行根據第3行獲取到的rowid從t1表中選擇行列,之后第1行根據第2行中的數據在內存里建立hash表;然后是第5行的范圍掃描索引t1_i2,第4行根據第5行獲取到的rowid從表t2中選擇行列,最后在第1行中根據T2表中的行列去探測內存中的hash表,如果有匹配項則建立一個新的含有結果集的rowsource,最后傳遞給客戶端程序。

            通過上述過程,我們可以有以下描述:Oracle在處理Hash Join的兩個子操作的方式上是不一樣的。第二個子操作(訪問T2)只有在第一個子操作(訪問T1)完成后才能開始---hash join是一個"阻塞"操作的例子。只有內存里的hash表建立之后,Oracle才能調用第2個子操作每次返回一行去探測hash表,之后將匹配的行傳遞給父操作,從這個時刻開始就有了分段的數據流。


            我偶爾也會看到這樣的說法,因為hash join屬于阻塞操作,所以當優化器處于first_rows(n)模式下時,不能進行hash join。這是不對的,如果優化器認為能很迅速的建立起hash表,并且從第2張表返回前N行的代價很低,那么hash join可能仍然會是返回前N列代價最低的路徑。


            我們可以通過hash join和使用hint讓優化器使用merge join這兩種不同執行計劃,來比較阻塞的效果。這里是merge join的計劃:


            ---------------------------------------------------------------------------------------
            | Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
            ---------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT              |       |    10 |   300 |    24   (9)| 00:00:01 |
            |   1 |  MERGE JOIN                   |       |    10 |   300 |    24   (9)| 00:00:01 |
            |   2 |   SORT JOIN                   |       |    10 |   150 |    12   (9)| 00:00:01 |
            |   3 |    TABLE ACCESS BY INDEX ROWID| T1    |    10 |   150 |    11   (0)| 00:00:01 |
            |*  4 |     INDEX RANGE SCAN          | T1_I1 |    10 |       |     1   (0)| 00:00:01 |
            |*  5 |   SORT JOIN                   |       |    10 |   150 |    12   (9)| 00:00:01 |
            |   6 |    TABLE ACCESS BY INDEX ROWID| T2    |    10 |   150 |    11   (0)| 00:00:01 |
            |*  7 |     INDEX RANGE SCAN          | T2_I1 |    10 |       |     1   (0)| 00:00:01 |
            ---------------------------------------------------------------------------------------
            
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
               4 - access("T1"."N_1000"=1)
               5 - access("T2"."ID"="T1"."ID")
                   filter("T2"."ID"="T1"."ID")
               7 - access("T2"."N_1000"=100)


            這個計劃里我們可以看到第1行的merge join操作有兩個子操作,分別是第2行的sort join(第1個子操作)和第5行的sort join(第2個子操作)。運用"子操作按先后順序執行"的規則,我們可以知道Oracle從范圍掃描索引t1_i1開始,從t1中獲取可能需要的數據并且在第2行中對它們進行排序(對id列進行排序,因為它們是連接列)。如果運氣好的話,第1行中排序后的數據集會在內存中(在會話的PGA中):第一個子操作是一個阻塞操作,所以在排序完成前我們不能調用第二個子操作。


            之后開始調用第二個子操作,同樣的規則,從范圍掃描索引t2_i1開始,訪問t2表,對結果集排序:第二個子操作仍然是一個阻塞操作,排序完成前,merge join操作自身并不會進行。


            當準備好兩個完成排序的rowsource時,merge join從第一個rowsource一次取一條,去探測第二個rowsource,如果匹配上就構造結果行并向上傳遞給父操作。由于對第二個rowsource進行了排序,所以Oracle查找每個匹配行的最壞情況是o(log(N))—其中N是第二個rowsource中的行數;Oracle使用二分法(使用 log2(N)檢查)來找到第一個匹配的行,之后按照順序從該行向下掃描。實際上代碼會更靈活,因為探測的行也是從一個排好序的結果集得來的,所以可以通過"記住"上次探測開始的行的位置來降低工作量。


            實際上,這種修改連接為merge join的方法也為我們提供了一個執行計劃可能不是如展示的那樣執行的例子,我們可以從"rowsource執行統計信息"中發現更多信息?,F在我們開始執行查詢并使用對dbms_xplan更復雜的調用來查看計劃中調用不同步驟的次數。


            alter session set statistics_level = all;
            set linesize 156
            set trimspool on
            set pagesize 60
            set serveroutput off
            
            
            select
                    /*+
                            leading(t1, t2)
                            use_merge(t2)
                    */
                    t1.v1, t2.v1
            from
                    t1, t2
            where
                    t1.n_1000 = 1
            and     t2.id     = t1.id
            and     t2.n_1000 = 100
            ;
            
            
            select * from table(dbms_xplan.display_cursor(null,null,"iostats last"));
            
            
            --------------------------------------------------------------------------
            | Id  | Operation                     | Name  | Starts | E-Rows | A-Rows |
            --------------------------------------------------------------------------
            |   0 | SELECT STATEMENT              |       |      1 |        |      0 |
            |   1 |  MERGE JOIN                   |       |      1 |     10 |      0 |
            |   2 |   SORT JOIN                   |       |      1 |     10 |     10 |
            |   3 |    TABLE ACCESS BY INDEX ROWID| T1    |      1 |     10 |     10 |
            |*  4 |     INDEX RANGE SCAN          | T1_I1 |      1 |     10 |     10 |
            |*  5 |   SORT JOIN                   |       |     10 |     10 |      0 |
            |   6 |    TABLE ACCESS BY INDEX ROWID| T2    |      1 |     10 |     10 |
            |*  7 |     INDEX RANGE SCAN          | T2_I1 |      1 |     10 |     10 |
            --------------------------------------------------------------------------
            
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
               4 - access("T1"."N_1000"=1)
               5 - access("T2"."ID"="T1"."ID")
                   filter("T2"."ID"="T1"."ID")
               7 - access("T2"."N_1000"=100)輸出中我們應該關注Starts列,第5行的starts列的值可能是會引起問題的一個小細節(第二個排序操作)。顯然Oracle把第二個表中的數據排序了10次,但由于Oracle的開發人員是相當聰明的,所以我們有理由認為這不是真實發生的,我們需要對sort join操作有更好的解釋,并更清楚的理解它是如何在執行計劃中展示的。


            第二個sort join操作其實包含兩部分,一部分根據提供的值探測排序好的數據集,另一部分確實是對數據集進行排序??赡苓@個說明更適合這個操作,"探測內存中已經排序好的數據集,但如果數據集不在內存中,則獲取并排序它"。執行計劃中的行可能包含類似"如果滿足條件X,則執行A,否則執行B"的高級邏輯,sort join具備做或者不做的能力,排序就是一個這樣的例子。

            第二行的A-rows的值是10(這個行數是從第一個子操作返回的),解釋了為什么Oracle需要調用10次第二個子操作,就像是nested loop的操作一樣,第一個子操作返回多少行就需要調用相應次數的第二個子操作。我們獲取并排序整個數據集一次,之后重用排序后的數據,最終總共探測10次。


            在這里我們可以對謂詞信息的解釋做一個初步介紹。第5行中同時使用了"access"和"filter"謂詞,而且這兩個謂詞使用完全一樣的表達式。


            簡單來說,這兩種類型的謂詞的區別在于,access謂詞告訴我們怎么找到數據行,filter謂詞告訴我們怎么在找到數據行后檢查這些數據行是否是我們所需要的。


            以第二個sort join操作為例子,access謂詞告訴我們怎么在排好序的數據集中找到第一條匹配的行,filter謂詞告訴我們,當我們按照順序遍歷已排序的數據集時,如何檢查每一行,以便在不符合filter表達式條件的行前停止。


            平常存在這樣一種情況,你認為你已經通過正確的索引正確的順序訪問所有的表,但是執行查詢時似乎工作量還是很大,這種情況有可能時訪問了很多的數據但使用filter謂詞過濾了很大一部分。


            如果我們有合適的索引,特別是在連接列上的索引,我們可以進一步研究merge join以及阻塞和計時的問題。這里有個例子(還是通過hint,因為優化器對于merge join的選擇性不高),執行計劃如下:


            --------------------------------------------------------------------------
            | Id  | Operation                     | Name  | Starts | E-Rows | A-Rows |
            --------------------------------------------------------------------------
            |   0 | SELECT STATEMENT              |       |      1 |        |      0 |
            |   1 |  MERGE JOIN                   |       |      1 |     10 |      0 |
            |*  2 |   TABLE ACCESS BY INDEX ROWID | T1    |      1 |     10 |     10 |
            |   3 |    INDEX FULL SCAN            | T1_PK |      1 |  10000 |  10000 |
            |*  4 |   SORT JOIN                   |       |     10 |     10 |      0 |
            |*  5 |    TABLE ACCESS BY INDEX ROWID| T2    |      1 |     10 |     10 |
            |   6 |     INDEX FULL SCAN           | T2_PK |      1 |  10000 |  10000 |
            --------------------------------------------------------------------------
            
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
               2 - filter("T1"."N_1000"=1)
               4 - access("T2"."ID"="T1"."ID")
                   filter("T2"."ID"="T1"."ID")
               5 - filter("T2"."N_1000"=100)


            例子中"PK"索引是基于id列,表示第3行和第6行中第index full scan操作訪問數據的順序正是我們所需要的排序的順序,這也避免了排序操作。我們從計劃中可以看到,Oracle并沒有對第一個數據集進行排序,只是簡單的按照順序讀取行,之后在第2行中使用filter謂詞過濾了所有不需要的行。

            比較第3行中A-rows和第2行中A-rows的值,我們可以看到先生成了包含10000行的rowsource,之后在根據filter謂詞舍棄掉了大部分只留下10行數據,這顯然是一個十分低效的行為。同樣低效的行為也發生在第5行和第6行中,我們執行index full scan,返回10000個rowid,通過rowid訪問表后根據filter舍棄了9990行。然而,關于表t2的處理最奇怪的事情是,我們在第4行中對產生的rowsource進行排序——盡管我們應該知道它已經按照merge join的正確排序順序排序了。對于這種明顯的冗余排序的解釋是,它是一種將數據從緩沖區緩存中取出并放入私有工作區域的簡便方法,這并不是為了將數據按正確的順序重新排列。

            該計劃顯示了阻塞操作可能出現的另一種情況。我們訪問第一張表的操作并不是一個阻塞操作,只有訪問第二張表的操作才是阻塞操作(排序)。這個計劃的步驟如下:


            1、在表t1上通過index full scan查找第一條符合連接條件的行。


            2、在表t2上通過index full scan查找所有符合條件的行,把它們傳輸到私有工作區;探測工作區的第一條匹配行并將后續匹配行進行合并。


            3、從t1中獲取第二條匹配的行(遍歷索引訪問表,并過濾不需要的數據)。


            4、探測私有工作區尋找匹配第二行的數據。


            5、從第三步開始重復。


            這篇文章的關鍵點是:時機很重要;執行計劃中的操作并不總是準確地描述正在發生的事情;謂詞部分對于理解優化正在執行的工作是一個重要的幫助,而rowsource(運行時)的統計信息對于查看實際發生的情況是一個非常大的幫助。


            原文鏈接:https://www.red-gate.com/simple-talk/sql/oracle/execution-plans-part-4-precision-and-timing/


            原文作者:Jonathan Lewis

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



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