在上周末優化班的時候一個朋友拿了一個SQL出來，讓我現場優化，因為當時太忙，我安排七年老師幫忙處理。跑得慢的SQL如下：
with temp as (select sgd.detail_id id,
wmsys.wm_concat(distinct(sg.gp_name)) groupnames,
wmsys.wm_concat(distinct(su.user_name)) usernames
from sgd
left join sg
on sg.id = sgd.gp_id
left join sug
on sg.id = sug.gp_id
left join su
on sug.user_id = su.id
group by sgd.detail_id)
select zh.id,
zh.id detailid,
zh.name detailname,
zh.p_level hospitallevel,
zh.type hospitaltype,
dza.name region,
temp.groupnames,
temp.usernames,
(case
when gd.gp_id is null then
0
else
1
end) isalloted
from zh
left join dza
on zh.area_id = dza.id
left join temp
on zh.id = temp.id
left join (select gp_id, detail_id from sys_gp_detail where gp_Id = :0) gd
on zh.id = gd.detail_id order by length(id),zh.id asc
該SQL返回20779行數據，要跑4分32秒。
該執行計劃中全是HASH JOIN，我就不貼了。
大家看我分析思路：
1. 首先這SQL最終返回20779行數據，該SQL語句最後部分沒有GROUP BY，僅僅是表關聯，並且是外連接
2. 那麼我可以判定zh也就差不多20779行數據，因為它是外連接的主表
3. 我也可以判定整個SQL裡面的表都不大，因為最終只返回20779行數據，並且沒有最終是沒有GROUP BY
4. 問題來了，既然都是小表，那為啥跑4分32秒？
遇到這種奇怪問題，我喜歡把SQL拆了。並且喜歡拆子查詢部分。所以你懂的
我們需要單獨跑with as裡面的SQL語句，跑了一下，發現居然要跑1--2分鐘
with as 的子查詢我們單獨拿出來看看
select sgd.detail_id id,
wmsys.wm_concat(distinct(sg.gp_name)) groupnames,
wmsys.wm_concat(distinct(su.user_name)) usernames
from sgd
left join sg
on sg.id = sgd.gp_id
left join sug
on sg.id = sug.gp_id
left join su
on sug.user_id = su.id
group by sgd.detail_id
這個子查詢裡面就多了2個列轉行函數wmsys.wm_concat
把它給注釋掉單獨跑一下列，發現SQL秒殺了
現在基本上定位問題所在，就是這個wmsys.wm_concat列轉行函數引起的性能問題
於是我將上面SQL進行了部分拆分
select sgd.detail_id id, wmsys.wm_concat(distinct(sg.gp_name)) groupnames
from sys_gp_detail sgd
left join sys_gp sg on sg.id = sgd.gp_id
group by sgd.detail_id
已用時間: 00: 00: 58.04
執行計劃
---
Plan hash value: 3491823204
Predicate Information (identified by operation id):
-
2 - access("SG"."ID"(+)="SGD"."GP_ID")
統計信息
---
1 recursive calls
249348 db block gets
44447 consistent gets
0 physical reads
0 redo size
9993548 bytes sent via SQL*Net to client
6067828 bytes received via SQL*Net from client
83118 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
請注意，select語句居然產生了 db block gets。db block gets一般情況下只有DML語句才會產生
select語句中除了延遲塊清除，或者有with as 產生的臨時表之外，實在想不通哪裡還能產生 db block gets
那麼這個SQL跑得慢就是慢在db block gets，相信大家對這塊沒有異議
要想成為所謂的技術大牛，必須通讀官方文檔。不管你是搞Oracle,MySQL,Hadoop,Java....等等，都必須通讀官方文檔
Oracle11g/Oracle10.2.0.5之後，wmsys.wm_concat 返回的是Clob，之前返回的是Varchar2
這就是為什麼會產生大量的db block gets，知道了這個原因，立即將這個SQL進行等價改寫，使用Listagg分析函數代替wmsys.wm_concat
wmsys.wm_concat 函數是可以支持 distinct 的，但是listagg分析函數是不支持 distinct的，所以改寫SQL的時候，需要先去重，再進行列轉行
select detail_id, listagg(gp_name, ',') within
group(
order by null)
from (select sgd.detail_id, sg.gp_name
from sys_gp_detail sgd
left join sys_gp sg on sg.id = sgd.gp_id
group by sgd.detail_id, sg.gp_name)
group by detail_id;
已用時間: 00: 00: 01.12
執行計劃
---
Plan hash value: 147456425
Predicate Information (identified by operation id):
-
4 - access("SG"."ID"(+)="SGD"."GP_ID")
統計信息
---
1 recursive calls
0 db block gets
2775 consistent gets
0 physical reads
0 redo size
450516 bytes sent via SQL*Net to client
15595 bytes received via SQL*Net from client
1387 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
20779 rows processed
可以看到，SQL等價改寫之後，可以秒殺，之前需要58秒。改寫完了一個 wmsys.wm_concat ，還有另外一個 wmsys.wm_concat 需要改寫。思路一樣
select detail_id, listagg(user_name, ',') within
group(
order by null)
from (select sgd.detail_id id, su.user_name
from sgd
left join sg on sg.id = sgd.gp_id
left join sug on sg.id = sug.gp_id
left join su on sug.user_id = su.id
group by sgd.detail_id, su.user_name)
group by detail_id;
最終將改寫好的2個結果集合併，得到等價的with as 語句，再替換原始SQL的with as語句，就可以秒殺了。
等價的with as 語句如下：
select a.detail_id id , a.groupnames, b.usernames
from (select detail_id, listagg(gp_name, ',') within
group(
order by null) groupnames
from (select sgd.detail_id, sg.gp_name
from sys_gp_detail sgd
left join sys_gp sg on sg.id = sgd.gp_id
group by sgd.detail_id, sg.gp_name)
group by detail_id) a,
(select detail_id, listagg(user_name, ',') within
group(
order by null) usernames
from (select sgd.detail_id, su.user_name
from sgd
left join sg on sg.id = sgd.gp_id
left join sug on sg.id = sug.gp_id
left join su on sug.user_id = su.id
group by sgd.detail_id, su.user_name)
group by detail_id) b
where a.. detail_id = b.detail_id;
結語： 道森學院 建議各位開發人員以後在開發過程中，儘量使用listagg函數代替wmsys.wm_concat