查询oracle耗时sql,[oracle] oracle查看执行时间长的sql

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)