目前在生産環境中有一個sql語句執行時間長達7分鐘,而且執行頻率極高。
其中PROC_INST中有将近6千萬的資料。其中STEP_INST是一個物化視圖,裡面還有5千多條資料。
可以看到這個語句已經有了一些調優的痕迹,可以從裡面的子查詢和hint能夠看出一些資訊。
SELECT PROC_INST.OBJID, PROC_INST.CREATION_TIME
FROM PROC_INST,
(SELECT / * +leading(PROC_INST LIST table_bpm_step_inst)
FULL(PROC_INST) hash_aj(LIST) use_nl(table_bpm_step_inst) * /
PROC_INST.OBJID, PROC_INST.CREATION_TIME, ROW_NUMBER() OVER(ORDER BY creation_time) rn
FROM PROC_INST,
(SELECT / * +materialize
FULL(in_step) PARALLEL(in_step, 8) * / in_step.root2proc_inst
FROM STEP_INST in_step
WHERE in_step.status NOT IN (?)
OR in_step.WAIT_TIME IS NOT NULL) LIST,
STEP_INST
WHERE STEP_INST.ROOT2PROC_INST =
PROC_INST.OBJID
AND PROC_INST.ROOT_STATUS = ?
AND PROC_INST.STATUS = ?
AND STEP_INST.OBJID =
PROC_INST.BEGIN2STEP_INST
AND STEP_INST.COMMITTER = ?
AND STEP_INST.STATUS IN (?)
AND STEP_INST.WAIT_TIME IS NULL
AND STEP_INST.ASSIGNEE = ?
AND PROC_INST.OBJID = list.root2proc_inst(+)
AND list.root2proc_inst IS NULL
ORDER BY CREATION_TIME) INNER_QRY
WHERE INNER_QRY.OBJID = PROC_INST.OBJID
AND INNER_QRY.rn > ?
AND INNER_QRY.rn
上面的查詢有幾個地方需要注意
首先是分析函數row_number的使用。其實在大量的資料查詢中直接使用rownum要高效一些,而且在子查詢的結果又加了一層order by的排序操作,是以可以考慮去除row_number()
關于AND list.root2proc_inst IS NULL的查詢條件,如果看得仔細一點就會發現,這個過濾條件完全可以放在子查詢list裡面,盡可能排除較多的資料。
子查詢的輸出結果集PROC_INST.OBJID, PROC_INST.CREATION_TIME,可以考慮直接使用rowid來代替對應的字段值,這樣可能對于索引來說就可以是的索引的使用更加高效,如果是range scan就可以從某種程度上提升為fast scan.
最後的這個地方看似沒有問題,其實是最需要做改進的地方。如果輸出1000~2000行的資料,那麼子查詢就會先得查出2000條資料。
AND INNER_QRY.rn > ?
如果輸出100000~101000 這樣的話,就得先得到101000行的資料,然後再排除過濾。這樣的話每個查詢的執行代價都會不同,可以考慮在回表的資料上進行一個統一的規劃。
基本思路就是先在order by之後的子查詢之後做一個rownum 然後隻輸出rowid。再上一層的子查詢中繼續拍段rownum>? 這樣基于rowid的排除更加清晰。
改進之後的sql語句類似下面的樣子。
FROM PROC_INST where rid in (
rid
FROM
(select rowid rid,rownum rn from
(select PROC_INST.rowid from
PROC_INST,
OR in_step.WAIT_TIME IS NOT NULL
AND list.root2proc_inst IS NULL) LIST,
where rownum)
AND INNER_QRY.rn >= ?)
最後優化的結果稍後奉上。