執行計劃-4：謂詞的選擇時機與使用細節

沃趣科技發表於2019-11-06

在第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執行統計資訊"中發現更多資訊。現在我們開始執行查詢並使用對 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資料庫，較豐富的故障處理、效能調優、資料遷移及備份恢復經驗。


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28218939/viewspace-2662835/，如需轉載，請註明出處，否則將追究法律責任。

相關文章