從一條問題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
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | B
------------------------------------------------------------------------------
| 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
----------------------------------------------------------------------------
| Id | Operation | Name | Rows |
-----------------------------------------------------------------------------
| 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看基於Oracle的SQL最佳化SQLOracle
- SQL優化--用各種hints優化一條SQLSQL優化
- SQL優化引出的問題（一）SQL優化
- SQL優化案例-從執行計劃定位SQL問題（三）SQL優化
- SQL優化34條SQL優化
- 一條sql的優化過程SQL優化
- 一條sql語句的優化SQL優化
- 一個SQL效能問題的優化探索SQL優化
- MYSQL 阿里的一個sql優化問題MySql阿里優化
- SQL優化--not in和or出的問題SQL優化
- SQL優化引出的問題（二）SQL優化
- 一條SQL語句的優化過程SQL優化
- SQL優化經驗總結34條(一)SQL優化
- Oracle優化案例-從執行計劃定位SQL問題（三）Oracle優化SQL
- 對sql語句的優化問題SQL優化
- oracle效能問題：sql語句優化OracleSQL優化
- SQL優化（一）SQL優化
- sql優化專題SQL優化
- [20170104]一條sql優化.txtSQL優化
- 通過新增條件優化SQL優化SQL
- 一條大sql的調優SQL
- sql 查詢條件問題SQL
- 一條sql語句優化不出來, 哭了一鼻子SQL優化
- 一個SQL優化SQL優化
- 資料庫sql的優化問題的面試題資料庫SQL優化面試題
- SQL最佳化案例-從執行計劃定位SQL問題（三）SQL
- 記錄一次SQL函式和優化的問題SQL函式優化
- Oracle優化案例-緊急處理一條sql引起cpu使用率99%的問題（十六）Oracle優化SQL
- 【sql調優之執行計劃】temp table transformationSQLORM
- SQL 優化經驗總結34條SQL優化
- SQL語句優化－－十條經驗SQL優化
- SQL優化經驗總結34條SQL優化
- java面試一日一題：如何優化sqlJava面試優化SQL
- 【SQL優化】SQL優化工具SQL優化
- Oracle效能優化-SQL優化(案例一)Oracle優化SQL
- MySQL SQL優化案例(一)MySql優化
- 一個sql的優化SQL優化
- oracle sql like優化（一）OracleSQL優化