[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)