最近碰到一個單條SQL運作效率不佳導緻資料庫整體運作負載較高的問題。
分析、定位資料庫的主要負載是這條語句引起的過程相對簡單，通過AWR報告就可以比較容易的完成定位，這裡就不贅述了。
現在直接看一下這個導緻性能問題的SQL語句，其對應的SQL REPORT統計如下：
從執行計劃看，Oracle選擇了HASH JOIN ANTI，JOIN的兩張表都是T_NUM，且都采用了全表掃描，并未選擇索引。僅靠執行計劃也隻等得到上面的結論，至于為什麼不選擇索引，以及為什麼執行時間過長，還需要進一步的分析。
将原SQL進行簡單脫密改寫後， SQL文本類似如下：
SELECT BEGIN, END, ROWID, LENGTH(BEGIN)
FROM T_NUM A
WHERE NOT EXISTS (
SELECT 1
FROM T_NUM B
WHERE B.BEGIN <= A.BEGIN
AND B.END >= A.END
AND B.ROWID != A.ROWID
AND LENGTH(B.BEGIN) = LENGTH(A.BEGIN)); 如果分析 SQL語句，會發現這是一個自關聯語句，在BEGIN字段長度相等的前提下，想要找到那些不存在 BEGIN比目前記錄 BEGIN小且 END比目前記錄 END大的記錄。
簡單一點說，表中的記錄表示的是由 BEGIN開始到 END截至的範圍，那麼目前想要擷取的結果是找出哪些沒有範圍所包含的範圍。需要注意的是，對于目前的 SQL邏輯，如果存在兩條範圍完全相同的記錄，那麼最終這兩條記錄都會被舍棄。
業務的邏輯并不是特别複雜，但是要解決一條記錄與其他記錄進行比較，多半采用的方法是自關聯，而在這個自關聯中，既有大于等于又有小于等于，還有不等于，僅有的一個等于的關聯條件，來自範圍段 BEGIN的長度的比較。
顯而易見的是，如果是範圍段本身的比較，其選擇度一般還是不錯的，但是如果隻是比較其長度，那麼無疑容易産生大量的重複，比如在這個例子中：
SQL> select length(begin), count(*) from t_num group by length(begin) order by 2 desc;
LENGTH(BEGIN) COUNT(*)
————- ———-
12 22096
11 9011
13 8999
14 8186
16 49
9 45
8 41
7 27 大量重複的資料出現在長度為11到14的範圍上，在這種情況下，僅有的一個等值判斷條件 LENGTH(BEGIN)是非常低效的，這時一條記錄根據這個等值條件會關聯到近萬條記錄，甚至關聯到兩萬多條記錄，顯然大量的實踐消耗在低效的連接配接過程中。
再來看一下具體的 SQL語句，會發現幾乎沒有辦法建立索引，因為LENGTH(BEGIN)的選擇度非常查，而其他的條件都是不等查詢，選擇度也不會好，即使建立索引，強制執行選擇索引，效率也不會好。
那麼如果想要繼續優化這個SQL，就隻剩下一個辦法，那就是SQL的改寫。對于自關聯查詢而言，最佳的改寫方法是利用分析函數，其強大的行級處理能力，可以在一次掃描過程中獲得一條記錄與其他記錄的關系，進而消除了自關聯的必要性。
SQL改寫結果如下：
SELECT BEGIN, OLDEND END, LENGTH(BEGIN)
FROM (
SELECT BEGIN, OLDEND, END, LENGTH(BEGIN), COUNT(*) OVER(PARTITION BY LENGTH(BEGIN), BEGIN, OLDEND) CN,
ROW_NUMBER() OVER(PARTITION BY LENGTH(BEGIN), END ORDER BY BEGIN) RN
FROM
(
SELECT BEGIN, END OLDEND, MAX(END) OVER(PARTITION BY LENGTH(BEGIN) ORDER BY BEGIN, END DESC) END
FROM T_NUM
)
)
WHERE RN = 1
AND CN = 1; 簡單的說，内層的分析函數MAX用來根據BEGIN從小到大，END從大到小的條件，确定每個範圍對應的最大的END的值。而外層的兩個分析函數，COUNT用來去掉完全重複的記錄，而ROW_NUMBER用來擷取範圍最大的記錄（也就是沒有被其他記錄的範圍所涵蓋）。
改寫後，這個 SQL避免了自關聯，也就不存在關聯條件重複值過高的性能隐患了。在模拟環境中，性能對比如下：
SQL> SELECT BEGIN, END, ROWID, LENGTH(BEGIN)
2 FROM T_NUM A
3 WHERE NOT EXISTS (
4 SELECT 1
5 FROM T_NUM B
6 WHERE B.BEGIN <= A.BEGIN
7 AND B.END >= A.END
8 AND B.ROWID != A.ROWID
9 AND LENGTH(B.BEGIN) = LENGTH(A.BEGIN))
10 ;
48344 rows selected.
Elapsed: 00:00:57.68
Execution Plan
———————————————————-
Plan hash value: 2540751655
————————————————————————————
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
————————————————————————————
| 0 | SELECT STATEMENT | | 48454 | 1703K| | 275 (1)| 00:00:04 |
|* 1 | HASH JOIN ANTI | | 48454 | 1703K| 1424K| 275 (1)| 00:00:04 |
| 2 | TABLE ACCESS FULL| T_NUM | 48454 | 851K| | 68 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T_NUM | 48454 | 851K| | 68 (0)| 00:00:01 |
————————————————————————————
Predicate Information (identified by operation id):
—————————————————
1 – access(LENGTH(TO_CHAR(“B”.”BEGIN”))=LENGTH(TO_CHAR(“A”.”BEGIN”)))
filter(“B”.”BEGIN”<=”A”.”BEGIN” AND “B”.”END”>=”A”.”END” AND
“B”.ROWID<>”A”.ROWID)
Statistics
———————————————————-
0 recursive calls
0 db block gets
404 consistent gets
0 physical reads
0 redo size
2315794 bytes sent via SQL*Net to client
35966 bytes received via SQL*Net from client
3224 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
48344 rows processed
SQL> SELECT BEGIN, OLDEND END, LENGTH(BEGIN)
2 FROM (
3 SELECT BEGIN, OLDEND, END, LENGTH(BEGIN), COUNT(*) OVER(PARTITION BY LENGTH(BEGIN), BEGIN, OLDEND) CN,
4 ROW_NUMBER() OVER(PARTITION BY LENGTH(BEGIN), END ORDER BY BEGIN) RN
5 FROM
6 (
7 SELECT BEGIN, END OLDEND, MAX(END) OVER(PARTITION BY LENGTH(BEGIN) ORDER BY BEGIN, END DESC) END
8 FROM T_NUM
9 )
10 )
11 WHERE RN = 1
12 AND CN = 1;
48344 rows selected.
Elapsed: 00:00:00.72
Execution Plan
———————————————————-
Plan hash value: 1546715670
——————————————————————————————
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
——————————————————————————————
| 0 | SELECT STATEMENT | | 48454 | 2460K| | 800 (1)| 00:00:10 |
|* 1 | VIEW | | 48454 | 2460K| | 800 (1)| 00:00:10 |
|* 2 | WINDOW SORT PUSHED RANK| | 48454 | 1845K| 2480K| 800 (1)| 00:00:10 |
| 3 | WINDOW BUFFER | | 48454 | 1845K| | 800 (1)| 00:00:10 |
| 4 | VIEW | | 48454 | 1845K| | 311 (1)| 00:00:04 |
| 5 | WINDOW SORT | | 48454 | 662K| 1152K| 311 (1)| 00:00:04 |
| 6 | TABLE ACCESS FULL | T_NUM | 48454 | 662K| | 68 (0)| 00:00:01 |
——————————————————————————————
Predicate Information (identified by operation id):
—————————————————
1 – filter(“RN”=1 AND “CN”=1)
2 – filter(ROW_NUMBER() OVER ( PARTITION BY LENGTH(TO_CHAR(“BEGIN”)),”END”
ORDER BY “BEGIN”)<=1)
Statistics
———————————————————-
0 recursive calls
0 db block gets
202 consistent gets
0 physical reads
0 redo size
1493879 bytes sent via SQL*Net to client
35966 bytes received via SQL*Net from client
3224 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
48344 rows processed 原SQL運作時間接近1分鐘，而改寫後的SQL語句隻需要0.72秒，執行時間變為原本的1/80，邏輯讀減少一半。
原文釋出時間為：2018-10-13
本文作者：楊廷琨
本文來自雲栖社群合作夥伴“
資料和雲”，了解相關資訊可以關注“
”。