從一條問題SQL優化看SQL Transformation

Oracle對接受到的描述性SQL語句，要進行一系列的驗證處理工作。其中，有一個重要過程稱為“SQL Transformation”，作用就是在不改變原有資料集結果的情況下，對SQL語句進行規則化改寫，使之可以生成更好的執行計劃。對一些執行計劃較差的SQL進行改寫，配合優化器的SQL變換功能，很多時候可以為SQL尋找到更好的執行計劃。

1、 問題提出

早上使用AWR報告進行開發環境診斷，發現開發組正進行開發模組中出現問題SQL。從AWR儲存中抽取出SQL如下：

select a.trans_id, a.trnn, a.tdnr, a.agent_code, a.tacn, a.trnc

from bsd_ticket a

where a.inc_file_seq = :1

and exists (select null

from bsd_ticket t

where t.inc_file_seq <> :2

and a.tdnr = t.tdnr

and a.tacn = t.tacn

and t.del_flag = 'N'

and t.doctype_code <> 30

group by t.tdnr, t.tacn

having count(*) > 1)

and not exists (select null

from bsd_trans_error e

where e.trans_id = a.trans_id

and e.err_code = '239')

and a.doctype_code = '10'

從診斷情況來看，該SQL執行的時間user wait過長，而且帶來大量的物理邏輯讀。在開發環節進行關鍵用例、關鍵SQL的優化幫助，是筆者工作範疇中的內容。

首先從業務需求入手，從開發組獲知，該SQL的作用是在大作業Job中進行的重複票證檢查。要求查詢出“不同輸入檔案inc_file_seq中，票號相同出現兩次的重複票資訊”。重複票證條件就是tdnr和tacn兩個欄位相同，inc_file_seq使用的繫結變數，在實際中輸入的是相同的檔案編號值。

從AWR中抽取出繫結變臉的peeking值，兩個均為數字number型別1。填補繫結變數位置之後，單獨執行發現執行時間的確過長。

SQL> select a.trans_id, a.trnn, a.tdnr, a.agent_code, a.tacn, a.trnc

2 from bsd_ticket a

3 where a.inc_file_seq = 1

4 and exists (select null

5 from bsd_ticket t

6 where t.inc_file_seq <> 1

7 and a.tdnr = t.tdnr

8 and a.tacn = t.tacn

9 and t.del_flag = 'N'

10 and t.doctype_code <> 30

11 group by t.tdnr, t.tacn

12 having count(*) > 1)

13 and not exists (select null

14 from bsd_trans_error e

15 where e.trans_id = a.trans_id

16 and e.err_code = '239')

17 and a.doctype_code = '10'

18 ;

TRANS_ID TRNN TDNR AGENT_CODE TACN TRNC

-------------- ------ --------------- ---------- ----- ----

Executed in 9.189 seconds

而且從inc_file_seq的分佈情況看，取值1的列值不是合乎比例的取值。如果我們替換上其他資料量值（如358），執行時間呈現出不可出結果的狀態。

SQL> select inc_file_seq, count(*) from bsd_ticket group by inc_file_seq;

INC_FILE_SEQ COUNT(*)

-------------- ----------

352 477616

729001 2

1 19

357 5

358 1885

124119 8

根據該種情況，筆者計劃首先從SQL語句角度進行修改改寫。

2、問題分析

首先，使用AWR獲取到該SQL使用的執行計劃和執行路徑資訊。

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------

Plan hash value: 4086380271

-----------------------------------------------------------------------

------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 |

|* 1 | FILTER | | |

| 2 | NESTED LOOPS ANTI | | 29 |

|* 3 | TABLE ACCESS BY INDEX ROWID| BSD_TICKET | 42 |

|* 4 | INDEX RANGE SCAN | IDX_BSD_TICKET_INC_FILESEQ | 42 |

|* 5 | TABLE ACCESS BY INDEX ROWID| BSD_TRANS_ERROR | 5 |

|* 6 | INDEX RANGE SCAN | IDX_BSD_TRANS_ERROR_TRANS_ID | 1 |

|* 7 | FILTER | | |

| 8 | SORT GROUP BY NOSORT | | 1 |

|* 9 | TABLE ACCESS FULL | BSD_TICKET | 1 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter( EXISTS (SELECT 0 FROM "BSD_TICKET" "T" WHERE "T"."TDNR"=:B1 AND "

TO_NUMBER("T"."DOCTYPE_CODE")<>30 AND "T"."DEL_FLAG"='N' AND "T"."

"T"."TDNR","T"."TACN" HAVING COUNT(*)>1))

3 - filter("A"."DOCTYPE_CODE"='10')

4 - access("A"."INC_FILE_SEQ"=1)

5 - filter("E"."ERR_CODE"='239')

6 - access("E"."TRANS_ID"="A"."TRANS_ID")

7 - filter(COUNT(*)>1)

9 - filter("T"."TDNR"=:B1 AND "T"."TACN"=:B2 AND TO_NUMBER("T"."DOCTYPE_CODE"

"T"."DEL_FLAG"='N' AND "T"."INC_FILE_SEQ"<>1)

從原SQL的本意看，開發者意識到了該SQL的兩個難點：

ü 首先是存在兩張以上的重票。這就意味著無論如何不容易逃開子查詢exists中的group by。BSD_TICKET資料表是票庫表，資料量巨大，進行group by操作要消耗大量的時間和空間；

ü 進行重票檢索的時候，相當於進行表的自連線操作；

於是，從開發者的角度，進行這樣的設計：首先使用連線條件深入到exists子查詢語句中，用來剔除一部分的資料集合。之後再進行group by操作，用exists來判斷。

但是從實際的執行計劃來看，開發者用心的考量似乎沒有起作用。在上面的執行計劃中，操作順序為：4-3-5-6-2-9-8-7-1-0。結合Predication Information中每個步驟的操作內容條件，可以看出執行計劃如下：

ü step4+step3：走索引路徑，將bsd_ticket表中對應輸入檔案的票證記錄全部檢索出來。由於bsd_ticket上存在索引，索引使用index range scan；

ü 繞開bsd_ticket資料表的條件，回溯到bsd_trans_error資料表。執行step5+step6操作，將具有’239’錯誤的交易編號獲取到；

ü 上面兩個步驟的結果，進行step2：nest loop anti的逆向不匹配操作。找到沒有對應239錯誤的交易；

ü step9：對資料表bsd_ticket進行第二次全表掃描，加入了抽象連線條件和檔案編號條件。再對結果進行group by操作；

ü 兩大部分結果集合，進行count(*)>1條件的篩查和其他檢索的應用；

該執行計劃在資料集合大的時候，特別是inc_file_seq檔案中票量稍稍增加之後，都會帶來nest loop操作巨大的效能抖動。這也就是為什麼我們替換inc_file_seq之後，不容易跑出結果的原因。

同時，我們應該注意到：執行的SQL語句和我們輸入的SQL執行順序有所差異。這就是Oracle內部的SQL Transformation過程的結果。我們希望進行的連線Join，在執行計劃中沒有出現，特別是沒有在子查詢中出現。取代Join的是純Filter操作。

3、 問題解決

筆者嘗試對該SQL進行改寫。首先就是group by能否去除，因為group by操作對效能的影響巨大，如果沒有特殊的理由，我們通常是不要輕易的group by或者sort的。

但是需求方面很明確，要求看重票兩次以上的票證。所以考慮將連線條件拿出subquery，嘗試一下能不能帶來效能的提升。因為一邊進行group by，一邊進行連線會增加SQL的複雜程度，反而不容易讓優化器入手。

select a.trans_id, a.trnn, a.tdnr, a.agent_code, a.tacn, a.trnc

from bsd_ticket a

where a.inc_file_seq = 1

and (tdnr, tacn) in (select tdnr, tacn

from bsd_ticket t

where t.inc_file_seq <> 1

and t.del_flag = 'N'

and t.doctype_code <> '30'

group by tdnr, tacn

having count(*) > 1)

and not exists (select null

from bsd_trans_error e

where e.trans_id = a.trans_id

and e.err_code = '239')

and a.doctype_code = '10';

改寫SQL最大的變化，就是將子查詢內部的連結條件遷移出去，替代為一個集合in操作配比。group by 和having條件沒有變化。我們首先觀察一下執行效率，採用相同的inc_file_seq取值。

SQL> select a.trans_id, a.trnn, a.tdnr, a.agent_code, a.tacn, a.trnc

2 from bsd_ticket a

3 where a.inc_file_seq = 1

4 and (tdnr, tacn) in (select tdnr, tacn

5 from bsd_ticket t

6 where t.inc_file_seq <> 1

9 and t.del_flag = 'N'

10 and t.doctype_code <> '30'

11 group by tdnr, tacn

12 having count(*) > 1)

13 and not exists (select null

14 from bsd_trans_error e

15 where e.trans_id = a.trans_id

16 and e.err_code = '239')

17 and a.doctype_code = '10';

TRANS_ID TRNN TDNR AGENT_CODE TACN TRNC

-------------- ------ --------------- ---------- ----- ----

Executed in 2.402 seconds

執行時間由原來的9秒多減少到2.4秒。下面我們來看下執行計劃：

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------------------

Plan hash value: 1971600865

----------------------------------------------------------------------------

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2 |

|* 1 | FILTER | |

| 2 | HASH GROUP BY | | 2 |

|* 3 | HASH JOIN | | 29 |

| 4 | NESTED LOOPS ANTI | | 29 |

|* 5 | TABLE ACCESS BY INDEX ROWID| BSD_TICKET | 42 |

|* 6 | INDEX RANGE SCAN | IDX_BSD_TICKET_INC_FILESEQ | 42 |

|* 7 | TABLE ACCESS BY INDEX ROWID| BSD_TRANS_ERROR | 5 |

|* 8 | INDEX RANGE SCAN | IDX_BSD_TRANS_ERROR_TRANS_ID | 1 |

|* 9 | TABLE ACCESS FULL | BSD_TICKET | 477K|

------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(COUNT(*)>1)

3 - access("TDNR"="TDNR" AND "TACN"="TACN")

5 - filter("A"."DOCTYPE_CODE"='10')

6 - access("A"."INC_FILE_SEQ"=1)

7 - filter("E"."ERR_CODE"='239')

8 - access("E"."TRANS_ID"="A"."TRANS_ID")

9 - filter("T"."DOCTYPE_CODE"<>'30' AND "T"."DEL_FLAG"='N' AND "T"."INC_FILE_

在執行計劃中，我們發現了Hash Join和Hash group by的操作痕跡。在CBO時代，Hash Join是實用性較好的連結方式。執行計劃的順序為：6-5-8-7-4-9-3-2-1。具體來看，如下：

ü step6-step4：內容和功能和修改前SQL的內容一樣。都是獲取指定檔案中沒有出現’239’編號錯誤的交易列表。nested loop anti操作是一種not in操作的體現；

ü step9中：開始對bsd_ticket資料表操作，此時沒有直接的group by，而是將一些常量篩選條件加以應用；

ü 在step3中，上面步驟中的獲取到的兩個資料集合，進行hash join操作，也就是進行連線操作；

ü 最後，在step2-step1中，才對資料集合進行group by和count(*)條件的採用；

綜合來看，改寫後的SQL執行計劃也被改寫。之前的SQL中，我們嘗試將連線條件寫入子查詢，期望以連線的方式減少一部分的資料集合。但是執行計劃中沒有出現join操作。而之後的SQL中，我們沒有顯示的進行連線描述，但是執行計劃中出現了Hash Join操作。這些都意味著SQL在輸入優化器之後，進行了SQL Transformation操作，對執行計劃影響重大。

原先SQL另一個重要問題就是當資料集合偏大的時候，效能變化劇烈。我們新改寫SQL如何呢？

SQL> select a.trans_id, a.trnn, a.tdnr, a.agent_code, a.tacn, a.trnc

2 from bsd_ticket a

3 where a.inc_file_seq = 358

4 and (tdnr, tacn) in (select tdnr, tacn

5 from bsd_ticket t

6 where t.inc_file_seq <> 358

7 and t.del_flag = 'N'

8 and t.doctype_code <> '30'

9 group by tdnr, tacn

10 having count(*) > 1)

11 and not exists (select null

12 from bsd_trans_error e

13 where e.trans_id = a.trans_id

14 and e.err_code = '239')

15 and a.doctype_code = '10';

已選擇1885行。

已用時間: 00: 00: 01.40

使用Autotrace對比，發現執行時間抖動性弱，比較適應資料變化。這也是Hash Join的特點。

4、結論與思考

這個案例給我們最大思考就是在於優化器的SQL變換功能。從DSI404中的資訊看，此處Oracle使用了Subquery Unnested技術，對輸入的SQL進行了改寫。Subquery Unnested主要是針對in、exists後出現的子查詢操作進行的查詢展開和合並。這個過程是很負責的內部改寫過程。

在我們的案例中，原有SQL雖然將連線條件寫入了子查詢中，期望能夠借用連線條件減少資料集合處理量，從而提高效能。但是事與願違，執行計劃中沒有出現Join的連線資訊，說明Oracle在改寫中將這個連線改寫出了子查詢，而且將group by留置其中。

改寫的SQL中，我們沒有強調連線條件。只是通過平緩化子查詢的職能，給了Oracle SQL Transformation發揮的空間。在改寫過的執行計劃中，出現了Hash Join操作。

Oracle是一個複雜的系統。在CBO時代，執行計劃路徑受到諸多因素的作用和影響。對一些關鍵SQL，我們最好的手段是先嚐試書寫好合適的SQL，再整合到應用中，減少日後的優化壓力。

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

從一條問題SQL優化看SQL Transformation

相關文章