oracle sql性能分析：分享一款Oracle数据库复杂sql一键优化工具--toad
oracle sql性能分析：分享一款Oracle数据库复杂sql一键优化工具--toad执行sql:选择自动优化sql:开启自动trace跟踪：或者在sql编辑区右键选择去开启自动跟踪：点击执行后可以看到sql相关统计信息：
概述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方面的内容，感兴趣的朋友可以关注下~
