[转帖]使用TOAD优化复杂SQL

时间:2021-11-15 02:20:02

独家秘笈!看下如何一键优化Oracle数据库复杂sql,DBA必备

https://www.toutiao.com/i6741208493644055053/

原来toad 还有这种功能 感觉类似于 JAVA的 JIT编译 根据实际数据进行优化SQL

自己SQL功底太垃圾了。知道有TOAD这个功能 以后可以学习尝试一下。

 

原创 波波说运维 2019-09-30 00:03:00

概述

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

[转帖]使用TOAD优化复杂SQL

 

可以看到执行计划如下:

[转帖]使用TOAD优化复杂SQL

 

2、 Get statistics/Auto Trace

开启自动trace跟踪:

[转帖]使用TOAD优化复杂SQL

 

或者在sql编辑区右键选择去开启自动跟踪:

[转帖]使用TOAD优化复杂SQL

 

3、Get statistics after executed sql.

点击执行后可以看到sql相关统计信息:

[转帖]使用TOAD优化复杂SQL

 

4、 Tuning SQL

选择自动优化sql:

[转帖]使用TOAD优化复杂SQL

 

执行sql:

[转帖]使用TOAD优化复杂SQL

 

可以看到正在自动优化:

[转帖]使用TOAD优化复杂SQL

 

优化完成后如下:

[转帖]使用TOAD优化复杂SQL

 

5、 Compare result

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

[转帖]使用TOAD优化复杂SQL

 

最终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方面的内容,感兴趣的朋友可以关注下~