概述

It is very easy for us to implement sql tuning by toad. We need to do is just give complex sql statement to toad.

相信很多朋友都會碰到那些幾十行幾百行的sql,像這種複雜的sql單單去做分析都很耗費我們的時間了，有沒有一種辦法可以一鍵優化這種複雜的sql語句呢？今天主要分享一下怎麼通過toad工具去優化那些複雜的sql，目的是幫助我們減少優化的時間。

原始sql

SELECT mm.inst_id,
 mm.sid,
 mm.TYPE,
 mm.id1,
 mm.id2,
 LPAD (TRUNC (mm.ctime / 60 / 60), 3)
 || ' Hour '
 || LPAD (
 TO_CHAR (
 TRUNC (mm.ctime / 60) - TRUNC (mm.ctime / 60 / 60) * 60,
 'fm09'),
 2)
 || ' Min '
 || LPAD (TO_CHAR (mm.ctime - TRUNC (mm.ctime / 60) * 60, 'fm09'), 2)
 || ' Sec'
 ctime,
 CASE
 WHEN mm.block = 1 AND mm.lmode != 0 THEN 'holder'
 WHEN mm.block = 0 AND mm.request != 0 THEN 'waiter'
 ELSE NULL
 END
 role,
 CASE
 WHEN ee.blocking_session IS NOT NULL
 THEN
 'waiting for SID ' || ee.blocking_session
 ELSE
 NULL
 END
 blocking_session,
 dd.sql_text sql_text,
 cc.event wait_event
 FROM gv$lock mm,
 gv$session ee,
 gv$sqlarea dd,
 gv$session_wait cc
 WHERE mm.sid IN
 (SELECT nn.sid
 FROM (SELECT tt.*,
 COUNT (1)
 OVER (PARTITION BY tt.TYPE, tt.id1, tt.id2)
 cnt,
 MAX (tt.lmode)
 OVER (PARTITION BY tt.TYPE, tt.id1, tt.id2)
 lmod_flag,
 MAX (tt.request)
 OVER (PARTITION BY tt.TYPE, tt.id1, tt.id2)
 request_flag
 FROM gv$lock tt) nn
 WHERE nn.cnt > 1
 AND nn.lmod_flag != 0
 AND nn.request_flag != 0)
 AND mm.sid = ee.sid(+)
 AND ee.sql_id = dd.sql_id(+)
 AND mm.sid = cc.sid(+)
 AND ( (mm.block = 1 AND mm.lmode != 0)
 OR (mm.block = 0 AND mm.request != 0))
ORDER BY mm.TYPE,
 mm.id1,
 mm.id2,
 mm.lmode DESC,
 mm.ctime DESC

1、Get execution plan

Editor --> Explain plan current SQL or CTRL + E

可以看到執行計劃如下：

2、 Get statistics/Auto Trace

開啟自動trace跟蹤：

或者在sql編輯區右鍵選擇去開啟自動跟蹤：

3、Get statistics after executed sql.

點擊執行後可以看到sql相關統計信息：

4、 Tuning SQL

選擇自動優化sql:

執行sql:

可以看到正在自動優化：

優化完成後如下：

5、 Compare result

這裡我們可以看到其中一條sql從3.7秒優化到0.04秒

最終sql

SELECT /*+ NO_CPU_COSTING */ mm.inst_id, 
 mm.sid, 
 mm.TYPE, 
 mm.id1, 
 mm.id2, 
 LPAD(TRUNC(mm.ctime / 60 / 60), 3) || ' Hour ' || LPAD(TO_CHAR(TRUNC(mm.ctime / 60) - TRUNC(mm.ctime / 60 / 60) * 60, 'fm09'), 2) || ' Min ' || LPAD(TO_CHAR(mm.ctime - TRUNC(mm.ctime / 60) * 60, 'fm09'), 2) || ' Sec' ctime, 
 CASE WHEN mm.block = 1 
 AND mm.lmode != 0 THEN 'holder' 
 WHEN mm.block = 0 
 AND mm.request != 0 THEN 'waiter' 
 ELSE NULL END role, 
 CASE WHEN ee.blocking_session IS NOT NULL THEN 'waiting for SID ' || ee.blocking_session 
 ELSE NULL END blocking_session, 
 dd.sql_text sql_text, 
 cc.event wait_event 
 FROM gv$lock mm, 
 gv$session ee, 
 gv$sqlarea dd, 
 gv$session_wait cc 
 WHERE EXISTS (SELECT 'X' 
 FROM (SELECT tt.*, 
 COUNT(1) OVER (PARTITION BY tt.TYPE, 
 tt.id1, 
 tt.id2) cnt, MAX(tt.lmode) OVER (PARTITION BY tt.TYPE, 
 tt.id1, 
 tt.id2) lmod_flag, MAX(tt.request) OVER (PARTITION BY tt.TYPE, 
 tt.id1, 
 tt.id2) request_flag 
 FROM gv$lock tt) nn 
 WHERE nn.cnt > 1 
 AND nn.lmod_flag != 0 
 AND nn.request_flag != 0 
 AND nn.sid = mm.sid) 
 AND mm.sid = ee.sid (+) 
 AND ee.sql_id = dd.sql_id (+) 
 AND mm.sid = cc.sid (+) 
 AND (mm.block = 1 
 AND mm.lmode <> 0 
 OR mm.block = 0 
 AND mm.request <> 0) 
 ORDER BY mm.TYPE, mm.id1, mm.id2, mm.lmode DESC, 
 mm.ctime DESC

雖然花上一些時間我們也可以優化到我們想要的結果，但是通過工具去幫助我們減少這些時間，何樂而不為呢？

覺得有用的朋友多幫忙轉發哦！後面會分享更多devops和DBA方面的內容，感興趣的朋友可以關注下~