AWR_近期平均耗时长SQL

WITH SQLSTAT AS
  (SELECT *
     FROM (SELECT SQL_ID,
                  MODULE,
                  ROUND (SUM (ELAPSED_TIME_DELTA) / 1000, 2) ELAPSED_MS,
                  ROUND (SUM (CPU_TIME_DELTA) / 1000, 2)     CPU_TIME_MS,
                  ROUND (SUM (ELAPSED_TIME_DELTA) / 1000
                          / DECODE(SUM(EXECUTIONS_DELTA),
                             0,1,SUM(EXECUTIONS_DELTA)),2)   ELAPSED_MS_PER,
                  SUM (BUFFER_GETS_DELTA)
                  / DECODE (SUM (EXECUTIONS_DELTA),
                            0, 1, SUM (EXECUTIONS_DELTA))    Buffer_PER_EXEC,
                  SUM (EXECUTIONS_DELTA)                     EXECUTIONS,
                  SUM (BUFFER_GETS_DELTA)                    BUFFER_GETS,
                  SUM (DISK_READS_DELTA)                     PHYSICAL_READS,
                  A.PLAN_HASH_VALUE,
                  A.SQL_PROFILE
             FROM DBA_HIST_SQLSTAT A,
                  (SELECT MIN (HS.SNAP_ID)     MIN_SNAP_ID,
                          MAX (HS.SNAP_ID)     MAX_SNAP_ID
                     FROM DBA_HIST_SNAPSHOT HS
                    WHERE HS.BEGIN_INTERVAL_TIME BETWEEN  SYSDATE - 1 / 2 AND SYSDATE) B
            WHERE SNAP_ID > B.MIN_SNAP_ID AND SNAP_ID <= B.MAX_SNAP_ID AND A.PARSING_SCHEMA_ID>10
           GROUP BY SQL_ID, MODULE, PLAN_HASH_VALUE, SQL_PROFILE
           ORDER BY 5 DESC)
    WHERE ROWNUM <= 1000)
SELECT *
  FROM (  SELECT SQ.ELAPSED_MS,
                 SQ.CPU_TIME_MS,
                 SQ.EXECUTIONS,
                 SQ.ELAPSED_MS_PER,
                 SQ.BUFFER_GETS,
                 SQ.PHYSICAL_READS,
                 SQ.SQL_ID,
                 SQ.PLAN_HASH_VALUE,
                 SQ.MODULE,
                 SQL_PROFILE,
                 DBMS_LOB.SUBSTR (ST.SQL_TEXT, 3000, 1) SQL_TEXT
            FROM SQLSTAT SQ, DBA_HIST_SQLTEXT ST
           WHERE     ST.SQL_ID = SQ.SQL_ID
                 AND DBMS_LOB.SUBSTR (ST.SQL_TEXT, 100, 1) NOT LIKE 'declare -- procedure for foreground wait event history statistic%'
                 AND DBMS_LOB.SUBSTR (ST.SQL_TEXT, 100, 1) NOT LIKE '%<font color=%'
                 AND SQ.MODULE NOT LIKE 'sqlplus%'
                 AND SQ.MODULE NOT LIKE 'DBMS_SCHEDUL%'
                 AND SQ.MODULE NOT LIKE 'SQL Developer%' AND SQ.MODULE NOT LIKE 'Toad%'
                 AND SQ.ELAPSED_MS IS NOT NULL
                 AND SQ.ELAPSED_MS_PER >= 30000                 -- 平均执行时间 ms
                 AND SQ.EXECUTIONS >= 2                        -- 期间SQL_ID执行次数
        ORDER BY 4 DESC)
 WHERE ROWNUM <= 100;