sqlmax同一行_分享一款Oracle数据库复杂sql一键优化工具toadfororacle

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单单去做分析都很耗费我们的时间了&＃xff0c;有没有一种办法可以一键优化这种复杂的sql语句呢&＃xff1f;今天主要分享一下怎么通过toad工具去优化那些复杂的sql&＃xff0c;目的是帮助我们减少优化的时间。

SELECT mm.inst_id, mm.sid, mm.TYPE, mm.id1, mm.id2, LPAD (TRUNC (mm.ctime / 60 / 60), 3) || &＃39; Hour &＃39; || LPAD ( TO_CHAR ( TRUNC (mm.ctime / 60) - TRUNC (mm.ctime / 60 / 60) * 60, &＃39;fm09&＃39;), 2) || &＃39; Min &＃39; || LPAD (TO_CHAR (mm.ctime - TRUNC (mm.ctime / 60) * 60, &＃39;fm09&＃39;), 2) || &＃39; Sec&＃39; ctime, CASE WHEN mm.block &＃61; 1 AND mm.lmode !&＃61; 0 THEN &＃39;holder&＃39; WHEN mm.block &＃61; 0 AND mm.request !&＃61; 0 THEN &＃39;waiter&＃39; ELSE NULL END role, CASE WHEN ee.blocking_session IS NOT NULL THEN &＃39;waiting for SID &＃39; || 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 !&＃61; 0 AND nn.request_flag !&＃61; 0) AND mm.sid &＃61; ee.sid(&＃43;) AND ee.sql_id &＃61; dd.sql_id(&＃43;) AND mm.sid &＃61; cc.sid(&＃43;) AND ( (mm.block &＃61; 1 AND mm.lmode !&＃61; 0) OR (mm.block &＃61; 0 AND mm.request !&＃61; 0))ORDER BY mm.TYPE, mm.id1, mm.id2, mm.lmode DESC, mm.ctime DESC

Editor --> Explain plan current SQL or CTRL &＃43; E

可以看到执行计划如下&＃xff1a;

开启自动trace跟踪&＃xff1a;

或者在sql编辑区右键选择去开启自动跟踪&＃xff1a;

点击执行后可以看到sql相关统计信息&＃xff1a;

选择自动优化sql:

执行sql:

可以看到正在自动优化&＃xff1a;

优化完成后如下&＃xff1a;

这里我们可以看到其中一条sql从3.7秒优化到0.04秒

SELECT /*&＃43; NO_CPU_COSTING */ mm.inst_id, mm.sid, mm.TYPE, mm.id1, mm.id2, LPAD(TRUNC(mm.ctime / 60 / 60), 3) || &＃39; Hour &＃39; || LPAD(TO_CHAR(TRUNC(mm.ctime / 60) - TRUNC(mm.ctime / 60 / 60) * 60, &＃39;fm09&＃39;), 2) || &＃39; Min &＃39; || LPAD(TO_CHAR(mm.ctime - TRUNC(mm.ctime / 60) * 60, &＃39;fm09&＃39;), 2) || &＃39; Sec&＃39; ctime, CASE WHEN mm.block &＃61; 1 AND mm.lmode !&＃61; 0 THEN &＃39;holder&＃39; WHEN mm.block &＃61; 0 AND mm.request !&＃61; 0 THEN &＃39;waiter&＃39; ELSE NULL END role, CASE WHEN ee.blocking_session IS NOT NULL THEN &＃39;waiting for SID &＃39; || 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 &＃39;X&＃39; 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 !&＃61; 0 AND nn.request_flag !&＃61; 0 AND nn.sid &＃61; mm.sid) AND mm.sid &＃61; ee.sid (&＃43;) AND ee.sql_id &＃61; dd.sql_id (&＃43;) AND mm.sid &＃61; cc.sid (&＃43;) AND (mm.block &＃61; 1 AND mm.lmode <> 0 OR mm.block &＃61; 0 AND mm.request <> 0) ORDER BY mm.TYPE, mm.id1, mm.id2, mm.lmode DESC, mm.ctime DESC

虽然花上一些时间我们也可以优化到我们想要的结果&＃xff0c;但是通过工具去帮助我们减少这些时间&＃xff0c;何乐而不为呢&＃xff1f;

觉得有用的朋友多帮忙转发哦&＃xff01;后面会分享更多devops和DBA方面的内容&＃xff0c;感兴趣的朋友可以关注下~