執行計劃-5：第一個子操作的變化

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

我提出了“子操作按先後順序執行，遞迴向下”的解釋執行計劃的基本策略，但是這並不是一個完美的規則，即使遵循基本的“子操作按先後順序執行”，也很容易對執行過程中發生的事情產生錯誤判斷。

在這部分中，我們將看到幾個例子，其中我們仍將在某些情況下使用“子操作按先後順序執行”，一個BUG的例子使規則看起來是錯誤的，還有一個示例不適用“子操作按先後順序執行”。 實際上，有幾種不同的情況並不適用該規則，但是我們必須等到第6部分才能瞭解更多的情況。

一、子查詢更新

這篇文章中介紹的例子如下： 更新，select列表中的標量子查詢和子查詢分解。 為了涵蓋儘可能多的例子，我將提供一個帶有計劃的示例語句並給出一些註釋;我不會提供重新建立表和資料的完整程式碼。 第一個例子是帶有子查詢的更新： 部分原因是DML計劃很少出現在執行計劃的文字中，另一部分原因是可以將它與我的第二個示例進行對比。 示例語句：

update t1 set 
    n1 = (
        select  max(mod100)
        from        t2
        where       t2.id = t1.id
    ),
    n2 = (
        select  max(trunc100)
        from        t3
        where       t3.id = t1.id
    )
where
    id between 101 and 200
;

這個語句有三個直觀的步驟。 第一我們需要找到更新的行，對每一行，我們執行t2的子查詢，之後是t3的子查詢。 所以執行計劃如下所示：

---------------------------------------------------------------------------------------
| Id  | Operation                    |Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |      |   101 |  1212 |  610  (34)| 00:00:04 |
|   1 |  UPDATE                      |T1    |       |      |           |         |
|*  2 |   INDEX RANGE SCAN           | T1_I1 |  101 |  1212 |     2   (0)|00:00:01 |
|   3 |   SORT AGGREGATE             |      |     1 |    7 |            |         |
|   4 |    FIRST ROW                  |      |     1 |    7 |     2   (0)| 00:00:01|
|*  5 |     INDEX RANGE SCAN (MIN/MAX)|T2_I1 |     1 |     7 |    2   (0)| 00:00:01 |
|   6 |   SORT AGGREGATE             |      |     1 |    7 |            |         |
|   7 |    FIRST ROW                 |      |     1 |    7 |     2   (0)| 00:00:01|
|*  8 |     INDEX RANGE SCAN (MIN/MAX)|T3_I1 |     1 |     7 |    2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID">=101 AND"ID"<=200)
   5 - access("T2"."ID"=:B1)
   8 - access("T3"."ID"=:B1)

就像你看到的，這個計劃遵循了“子操作按先後順序執行”。 第1行的更新操作有三個子操作:第2，3和6行。 第一個子操作INDEX RANGE SCAN(索引範圍掃描)幫助我們找到需要更新的行的rowid，第二個子操作生成的子計劃(3-5行)表示子查詢更新列n1，第三個子操作生成的子計劃(6-8行)表示子查詢更新列n2。

二、標量子查詢

對於第二個例子，我將把這個語句轉換成一個查詢語句，該查詢顯示更新操作將如何更改資料。 我所要做的就是從更新語句中獲取每個子查詢，並將其作為一個標量子查詢加入select列表中。 在這個例子中，我們看到“子操作按先後順序執行”規則幾乎被顛倒過來：

select
    n1, n2,
    (
        select  max(mod100)
        from    t2
        where   t2.id = t1.id
    ) new_n1,
    (
        select  max(trunc100)
        from    t3
        where   t3.id = t1.id
    ) new_n2
from
    t1
where
    t1.id between 101 and 200
;
--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |   101 |  1212 |     4   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |       |     1 |     7 |            |          |
|   2 |   FIRST ROW                  |       |     1 |     7 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| T2_I1 |     1 |     7 |     2   (0)| 00:00:01 |
|   4 |  SORT AGGREGATE              |       |     1 |     7 |            |          |
|   5 |   FIRST ROW                  |       |     1 |     7 |     2   (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN (MIN/MAX)| T3_I1 |     1 |     7 |     2   (0)| 00:00:01 |
|   7 |  TABLE ACCESS BY INDEX ROWID | T1    |   101 |  1212 |     4   (0)| 00:00:01 |
|*  8 |   INDEX RANGE SCAN           | T1_I1 |   101 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T2"."ID"=:B1)
   6 - access("T3"."ID"=:B1)
   8 - access("T1"."ID">=101 AND "T1"."ID"<=200)

這個計劃中可以看出，第0行的select語句有3個子查詢(1，4，7行)，但是，當我們直觀感覺肯定會發生的跟實際發生的子操作的順序做比較，我們會看到，最後一個子操作代表了驅動查詢語句的起點。 當你語句的select列表中有標量子查詢時，最後一個子操作會被當作第一個子操作進行呼叫，剩下的表示標量子查詢的子操作，會按照順序被呼叫。

三、BUG演示

編寫越來越複雜的SQL是不可避免的，只需要一點小改動就能命中與標量子查詢相關的顯示錯誤—如果我們想根據表t2的資料更新t1裡的某些行，並根據表t3更新t1裡的其他行，我們可能會使用decode()來實現。 這裡有一個查詢語句用來演示原理和相關bug：

select
    n1,
    decode(mod(n1,4),
        0,  (
            select  max(mod100)
            from    t2
            where   t2.id = t1.id
            ),
            (
            select  max(trunc100)
            from    t3
            where   t3.id = t1.id
            )
    )
from
    t1
where
    t1.id between 101 and 200
;
-----------------------------------------------------------------------------------------
| Id  | Operation                       | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |       |   101 |   808 |     4   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                 |       |     1 |     7 |            |          |
|   2 |   FIRST ROW                     |       |     1 |     7 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)   | T2_I1 |     1 |     7 |     2   (0)| 00:00:01 |
|   4 |     SORT AGGREGATE              |       |     1 |     7 |            |          |
|   5 |      FIRST ROW                  |       |     1 |     7 |     2   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN (MIN/MAX)| T3_I1 |     1 |     7 |     2   (0)| 00:00:01 |
|   7 |  TABLE ACCESS BY INDEX ROWID    | T1    |   101 |   808 |     4   (0)| 00:00:01 |
|*  8 |   INDEX RANGE SCAN              | T1_I1 |   101 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T2"."ID"=:B1)
   6 - access("T3"."ID"=:B1)
   8 - access("T1"."ID">=101 AND "T1"."ID"<=200)

這個查詢語句中，如果n1是4的倍數則返回t2中的值，否則返回t3的值。 我們有理由相信這兩個子查詢在某些方面是“相同的”，但是當我們檢視執行計劃時，情況就不一樣了。

我們首先可以看到最後一個子操作是作為驅動這條查詢語句出現的，但是注意這個查詢語句只剩下另外一個子操作，如果我們運用基本規則，我們很顯然能看到，對t3的子查詢似乎是對t2子查詢的一個子操作。 這告訴我們我們必須先執行4-6行生成rowsource，之後才能把結果集向上傳遞給3，2和1行。

我們很容易就能寫出另外一個類似這種執行計劃的查詢語句，但是我要說的是，這個執行計劃的執行順序是錯誤的。 (12c中也是相同的執行計劃，也同樣是錯誤的)

我更早的部分中我解釋過，Oracle會對計劃中的每一行計算depth，我們可以從plan table(或動態效能是圖)中選擇該列生成執行計劃的縮排，但是有時候優化器會計算錯誤的depth值。 上面的例子就是其中一種情況，我們需要使用自己的程式碼(對parent_id和id列執行connectby查詢)來生成正確的執行計劃。

我使用了比寫查詢程式碼更簡單的方法來展示正確的執行計劃。 用sql_tract跟蹤sql執行過程並用tkprod對trace檔案格式化。 這裡是結果：

Rows (1st)  Row Source Operation
----------  ---------------------------------------------------
        25  SORT AGGREGATE (cr=11 pr=0 pw=0 time=126 us)
        25   FIRST ROW  (cr=11 pr=0 pw=0 time=83 us cost=2 size=7 card=1)
        25    INDEX RANGE SCAN (MIN/MAX) T2_I1 (cr=11 pr=0 pw=0 time=74 us cost=2 size=7 card=1)
        75  SORT AGGREGATE (cr=11 pr=0 pw=0 time=241 us)
        75   FIRST ROW  (cr=11 pr=0 pw=0 time=166 us cost=2 size=7 card=1)
        75    INDEX RANGE SCAN (MIN/MAX) T3_I1 (cr=11 pr=0 pw=0 time=140 us cost=2 size=7 card=1)
       100  TABLE ACCESS BY INDEX ROWID T1 (cr=13 pr=0 pw=0 time=82 us cost=4 size=808 card=101)
       100   INDEX RANGE SCAN T1_I1 (cr=6 pr=0 pw=0 time=654 us cost=2 size=0 card=101)

從這個輸出中可以看到，這兩個子查詢在select語句中是相等的，就像前面的select語句中一樣。 trace檔案中不包含depth資訊； 它的STAT行只包含id跟parent_id，所以tkprod必須獲取深度並給我們提供了正確的執行計劃。

tkprof輸出中另一個有用的特性是，我們可以通過”Rows(1st)”列看到對t2的子查詢一共返回了25行，對t3的子查詢一共返回了75行。

回顧最初的查詢，我們期望(或希望)一個查詢執行25次，其他查詢執行75次，因此在這個例子中，我們有一些確證的證據。

Note： 實際上，在輸出中我們並沒有足夠的資訊來了解實際情況—我們根據我們對資料以及查詢的理解直接跳到了結論:原則上所有的子查詢可能都執行了100次，分別返回了25%和75%的資料，我們實際上需要內部檢視v$sql_plan_statistics中”starts”的統計資訊，但是如果我們通過dbms_xplan查詢這個檢視，它同樣會生成錯誤的執行計劃，這也是我們為什麼不使用connectby查詢的原因，所以我們必須從這兩個地方(正確的統計資訊和正確的執行路徑)去獲取正確的執行計劃。

四、 子查詢分解

我提出12c會使用新的轉換將標量子查詢轉換為連線。 讓我們回到前面的查詢—包含兩個簡單內聯標量子查詢的查詢，並在11g中模擬這個計劃。 我們可以這麼做：

with sq2 as (
    select  /*+ materialize */
        t2.id, max(t2.mod100)   new_n1
    from    t2
    where   t2.id between 101 and 200
    group by t2.id
),
sq3 as (
    select  /*+ materialize */
        t3.id, max(t3.trunc100) new_n2
    from    t3
    where   t3.id between 101 and 200
    group by t3.id
)
select
    t1.n1, t1.n2,
    sq2.new_n1,
    sq3.new_n2
from
    t1, sq2, sq3
where
    t1.id between 101 and 200
and    sq2.id(+) = t1.id
and    sq3.id(+) = t1.id
;
-----------------------------------------------------------------------------------------
| Id | Operation                      | Name     | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT               |          |  101 |  6464 |    12   (0)| 00:00:01 |
|  1 |  TEMP TABLE TRANSFORMATION     |          |      |       |            |          |
|  2 |   LOAD AS SELECT               | SYS_TEMP |      |       |            |          |
|  3 |    SORT GROUP BY NOSORT        |          |  101 |   707 |     2   (0)| 00:00:01 |
|* 4 |     INDEX RANGE SCAN           | T2_I1    |  101 |   707 |     2   (0)| 00:00:01 |
|  5 |   LOAD AS SELECT               | SYS_TEMP |      |       |            |          |
|  6 |    SORT GROUP BY NOSORT        |          |  101 |   707 |     2   (0)| 00:00:01 |
|* 7 |     INDEX RANGE SCAN           | T3_I1    |  101 |   707 |     2   (0)| 00:00:01 |
|* 8 |   HASH JOIN OUTER              |          |  101 |  6464 |     8   (0)| 00:00:01 |
|* 9 |    HASH JOIN OUTER             |          |  101 |  3838 |     6   (0)| 00:00:01 |
| 10 |     TABLE ACCESS BY INDEX ROWID| T1       |  101 |  1212 |     4   (0)| 00:00:01 |
|*11 |      INDEX RANGE SCAN          | T1_I1    |  101 |       |     2   (0)| 00:00:01 |
|*12 |     VIEW                       |          |  101 |  2626 |     2   (0)| 00:00:01 |
| 13 |      TABLE ACCESS FULL         | SYS_TEMP |  101 |   707 |     2   (0)| 00:00:01 |
|*14 |    VIEW                        |          |  101 |  2626 |     2   (0)| 00:00:01 |
| 15 |     TABLE ACCESS FULL          | SYS_TEMP |  101 |   707 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T2"."ID">=101 AND "T2"."ID"<=200)
   7 - access("T3"."ID">=101 AND "T3"."ID"<=200) 
   8 - access("SQ3"."ID"(+)="T1"."ID") 
   9 - access("SQ2"."ID"(+)="T1"."ID") 
  11 - access("T1"."ID">=101 AND "T1"."ID"<=200) 
  12 - filter("SQ2"."ID"(+)>=101 AND "SQ2"."ID"(+)<=200) 
  14 - filter("SQ3"."ID"(+)>=101 AND "SQ3"."ID"(+)<=200)

我在子查詢分解中使用hint/*+ materialize*/強制Oracle建立了兩張內部全域性臨時表，表中包含t2和t3中我們所需要的結果，之後剩下的程式碼就是將t1和兩個結果集做外連線。 實際上我可以去掉hint，Oracle會將“分解的子查詢”進行內聯複製，從而產生一組類似與兩個聚合結果集進行outer hash join的結果集，並儲存在會話記憶體的工作區中。 我使用materialize選項只是為了顯示帶有物化子查詢的計劃。 如果我們將執行計劃簡化下，那麼我們會看到如下：

-----------------------------------------------------------------------------------------
| Id | Operation                     |Name     | Rows | Bytes | Cost (%CPU)| Time    |
-----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT              |         |  101 |  6464|    12   (0)| 00:00:01 |
|  1 |  TEMP TABLE TRANSFORMATION    |         |     |       |           |         |
|  2 |   LOAD AS SELECT              |SYS_TEMP |      |      |           |         |
|  5 |   LOAD AS SELECT              |SYS_TEMP |      |      |           |         |
|* 8 |   HASH JOIN OUTER             |         |  101 |  6464|     8   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

我想說這並不是我想看到的順序，我更想看到第8行(hash join)作為第0行(select statement)的一個子操作。 我們可以看到計劃中三個主要的階段都是temptable transormation的子操作，我們先建立了兩張臨時表，之後做hash join。 如果我們展開第2行，我們看到的是聚合表t2;如果我們展開第5行，我們看到的是聚合表t3;如果我們展開第8行，我們看到的是t1和兩個臨時表之間的hashjoin。

-----------------------------------------------------------------------------------------
| Id | Operation                      | Name     | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|* 8 |   HASH JOIN OUTER              |          |  101 |  6464 |     8   (0)| 00:00:01 |
|* 9 |    HASH JOIN OUTER             |          |  101 |  3838 |     6   (0)| 00:00:01 |
| 10 |     TABLE ACCESS BY INDEX ROWID| T1       |  101 |  1212 |     4   (0)| 00:00:01 |
|*12 |     VIEW                       |          |  101 |  2626 |     2   (0)| 00:00:01 |
|*14 |    VIEW                        |          |  101 |  2626 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

除了計劃中執行查詢被向右移動了一點外，在我們真正執行查詢前可能會生成很多資料，就像我們計劃中在select列表中的標量子查詢一樣，在子查詢中仍然遵循基本規則,可以簡單地通過摺疊多餘的文字進行分析，直到可以看到計劃中每個操作的第一個操作為止。

五、結  論

第5部分我們看了一些例子，基本都是關於一個大查詢中包含一些子查詢，來觀察”子操作按先後順序執行，遞迴向下”這個規則是怎麼在複雜的查詢中輸出的。 我們也檢查了我們已經找到的一個BUG，這也告訴我們不能盲目的遵循規則，必要時需要交叉引用查詢生成執行計劃的其他方式來獲得正確的計劃。 我們同樣也看了一些標量子查詢的特殊例子，作為驅動查詢的操作往往都是最後一個子操作。

在下一部分，我們將看到一些查詢案例，其中優化器執行的子查詢確實打破了“子操作按先後順序執行，遞迴向下“的規則，並使你完全錯誤的理解執行計劃。

原文連結 :

https://www.red-gate.com/simple-talk/sql/oracle/execution-plans-part-5-first-child-variations/

原文作者 : Jonathan Lewis

 

| 譯者簡介

林錦森·沃趣科技資料庫技術專家

沃趣科技資料庫工程師，多年從事Oracle資料庫，較豐富的故障處理、效能調優、資料遷移及備份恢復經驗。


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

相關文章