[oracle] oracle查看执行时间长的sql
【摘要】 oracle查看执行时间长sql1.查看发生时间的snap_idselect snap_id,dbid,instance_number,startup_time,begin_interval_time,end_interval_time from dba_hist_snapshot where begin_interval_time between to_date('20200525 08:...
oracle查看执行时间长sql
1.查看发生时间的snap_id
select snap_id,dbid,instance_number,startup_time,begin_interval_time,end_interval_time from dba_hist_snapshot where begin_interval_time between to_date('20200525 08:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('20200601 08:00:00','yyyy-mm-dd hh24:mi:ss') and instance_number=1 order by snap_id;
2.查看执行时间top10的sql
SELECT a.*
FROM ( SELECT trunc(NVL ( (sqt.elap / 1000000), TO_NUMBER (NULL)),3) elap_time,
trunc(NVL ( (sqt.cput / 1000000), TO_NUMBER (NULL)),3) cpu_time,
sqt.exec,
trunc(DECODE (sqt.exec,
0, TO_NUMBER (NULL),
(sqt.elap / sqt.exec / 1000000)),3)
avg_time,
trunc(( 100
* ( sqt.elap
/ (SELECT SUM (e.VALUE) - SUM (b.VALUE)
FROM DBA_HIST_SYSSTAT b, DBA_HIST_SYSSTAT e
WHERE B.SNAP_ID = 42304
AND E.SNAP_ID = 42486
AND B.DBID = 3230440875
AND E.DBID = 3230440875
--AND B.INSTANCE_NUMBER = 1
--AND E.INSTANCE_NUMBER = 1
AND e.STAT_NAME = 'DB time'
AND b.stat_name = 'DB time'))),3)
norm_val,
(DECODE (sqt.module, NULL, NULL, 'Module: ' || sqt.module))
connecte_modul,
sqt.sql_id,
NVL (TO_CHAR (SUBSTR (st.sql_text, 1, 3800)),' ** SQL Text Not Available ** ')SQL_TEXT
FROM ( SELECT sql_id,
MAX (module) module,
SUM (elapsed_time_delta) elap,
SUM (cpu_time_delta) cput,
SUM (executions_delta) exec
FROM dba_hist_sqlstat
WHERE dbid = 3230440875
--AND instance_number = 1
AND 42304 < snap_id
AND snap_id <= 42486
GROUP BY sql_id) sqt,
dba_hist_sqltext st
WHERE st.sql_id(+) = sqt.sql_id AND st.dbid(+) = 3230440875
ORDER BY NVL (sqt.elap, -1) DESC, sqt.sql_id) a
WHERE (ROWNUM <= 10 OR a.norm_val > 1)
【声明】本内容来自华为云开发者社区博主,不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱:
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
作者其他文章
评论(0)