TOP SQL 평균수행시간

WITH    DBA_WITH_SNAPSHOT
AS     (
        SELECT  MIN(SNAP_ID) AS BEGIN_SNAP_ID, MAX(SNAP_ID) AS END_SNAP_ID
        FROM    DBA_HIST_SNAPSHOT
        WHERE   INSTANCE_NUMBER = 1
        AND     END_INTERVAL_TIME   >= TO_DATE('2016/01/18 09:00:00', 'YYYY/MM/DD HH24:MI:SS')
        AND     BEGIN_INTERVAL_TIME <= TO_DATE('2016/01/18 10:00:00', 'YYYY/MM/DD HH24:MI:SS')
       )
      ,  DBA_WITH_SQLSTAT
AS     (
        SELECT  /*+ INLINE PARALLEL(4) */
                A.*
        FROM    DBA_HIST_SQLSTAT    A
              , DBA_WITH_SNAPSHOT   X
        WHERE   1 = 1
        AND     A.SNAP_ID >= X.BEGIN_SNAP_ID
        AND     A.SNAP_ID <= X.END_SNAP_ID
        --AND     A.PARSING_SCHEMA_NAME IN ('PBAT' , 'PONL')
        --AND     A.SQL_ID = 'gwq78agmyw3bj'
        --AND     UPPER(A.MODULE) LIKE '%' || UPPER('JDBC Thin Client') || '%'    /* <== 여기에 모듈명을 입력할 것 */
        --AND    (
        --        INSTR(UPPER(A.MODULE), 'BAT01') > 0
        --OR      INSTR(UPPER(A.MODULE), 'BAT02') > 0
        --       )
       )

SELECT  /*+ LEADING(C, A, B) USE_HASH(A, B) FULL(B) NO_MERGE(C) NO_MERGE(A) NO_MERGE(B) */
        A.SQL_ID
      , A.PARSING_SCHEMA_NAME
      , A.MODULE
      , SUBSTR(A.MODULE, INSTR(A.MODULE, '[') + 1, 13)                                          AS JOB_NAME

      , B.SQL_TEXT

      , A.EXECUTIONS                                                                            AS "총수행횟수"
      , A.ROWS_PROCESSED                                                                        AS "총로우수"
      , ROUND(A.ELAPSED_TIME   / 60, 1)                                                         AS "총수행시간(분)"

      , ROUND(A.ROWS_PROCESSED / (CASE WHEN EXECUTIONS = 0 THEN 1 ELSE A.EXECUTIONS END), 0)    AS "평균로우수"
      , ROUND(A.ELAPSED_TIME   / (CASE WHEN EXECUTIONS = 0 THEN 1 ELSE A.EXECUTIONS END), 6)    AS "평균수행시간(초)"
      , ROUND(A.BUFFER_GETS    / (CASE WHEN EXECUTIONS = 0 THEN 1 ELSE A.EXECUTIONS END), 0)    AS "평균블록I/O(개수)"
      , ROUND(A.DISK_READS     / (CASE WHEN EXECUTIONS = 0 THEN 1 ELSE A.EXECUTIONS END), 1)    AS "평균DISK_READS"

      , ROUND(A.CPU_TIME       / 60)                                                            AS "총CPU시간(분)"
      , ROUND(A.IOWAIT         / 60)                                                            AS "총IOWAIT(분)"
      , ROUND(A.CLWAIT         / 60)                                                            AS "총CLWAIT(분)"
      , ROUND(A.APWAIT         / 60)                                                            AS "총APWAIT(분)"
      , ROUND(A.CCWAIT         / 60)                                                            AS "총CCWAIT(분)"

      , ROUND(A.CPU_TIME       / (CASE WHEN EXECUTIONS = 0 THEN 1 ELSE A.EXECUTIONS END), 6)    AS "평균CPU시간(초)"
      , ROUND(A.IOWAIT         / (CASE WHEN EXECUTIONS = 0 THEN 1 ELSE A.EXECUTIONS END), 6)    AS "평균IOWAIT(초)"
      , ROUND(A.CLWAIT         / (CASE WHEN EXECUTIONS = 0 THEN 1 ELSE A.EXECUTIONS END), 6)    AS "평균CLWAIT(초)"
      , ROUND(A.APWAIT         / (CASE WHEN EXECUTIONS = 0 THEN 1 ELSE A.EXECUTIONS END), 6)    AS "평균APWAIT(초)"
      , ROUND(A.CCWAIT         / (CASE WHEN EXECUTIONS = 0 THEN 1 ELSE A.EXECUTIONS END), 6)    AS "평균CCWAIT(초)"

      , C."최종실행일시"
      , C."최초실행일시"
FROM   (
        SELECT  /*+ LEADING(B, A) USE_HASH(A) FULL(B) FULL(A) NO_MERGE(B) NO_MERGE(A) */
                A.SQL_ID
              , A.PARSING_SCHEMA_NAME
              , A.MODULE

              , SUM(EXECUTIONS_DELTA)                       AS EXECUTIONS
              , SUM(DISK_READS_DELTA)                       AS DISK_READS
              , SUM(BUFFER_GETS_DELTA)                      AS BUFFER_GETS
              , SUM(ROWS_PROCESSED_DELTA)                   AS ROWS_PROCESSED

              , SUM(CPU_TIME_DELTA)     / 1000000           AS CPU_TIME
              , SUM(ELAPSED_TIME_DELTA) / 1000000           AS ELAPSED_TIME

              , SUM(IOWAIT_DELTA)       / 1000000           AS IOWAIT
              , SUM(CLWAIT_DELTA)       / 1000000           AS CLWAIT
              , SUM(APWAIT_DELTA)       / 1000000           AS APWAIT
              , SUM(CCWAIT_DELTA)       / 1000000           AS CCWAIT

        FROM    DBA_WITH_SQLSTAT    A
              , DBA_HIST_SNAPSHOT   B
        WHERE   A.SNAP_ID           = B.SNAP_ID
        AND     A.DBID              = B.DBID
        AND     A.INSTANCE_NUMBER   = B.INSTANCE_NUMBER
        GROUP BY
                A.SQL_ID
              , A.PARSING_SCHEMA_NAME
              , A.MODULE
       )                    A
      , DBA_HIST_SQLTEXT    B
      ,(
        SELECT  /*+ LEADING(A, B, C) USE_HASH(B, C) NO_MERGE(A) NO_MERGE(B) NO_MERGE(C) */
                A.SQL_ID
              , B."실행일시"    AS "최종실행일시"
              , C."실행일시"    AS "최초실행일시"
        FROM   (
                SELECT  SQL_ID
                      , MAX(SNAP_ID) AS MAX_SNAP_ID
                      , MIN(SNAP_ID) AS MIN_SNAP_ID
                FROM    DBA_HIST_SQLSTAT
                GROUP BY
                        SQL_ID
               ) A
              ,(
                SELECT  SNAP_ID, MAX(END_INTERVAL_TIME)     AS "실행일시"
                FROM    DBA_HIST_SNAPSHOT
                GROUP BY
                        SNAP_ID
               ) B
              ,(
                SELECT  SNAP_ID, MAX(END_INTERVAL_TIME)     AS "실행일시"
                FROM    DBA_HIST_SNAPSHOT
                GROUP BY
                        SNAP_ID
               ) C
        WHERE   A.MAX_SNAP_ID = B.SNAP_ID(+)
        AND     A.MIN_SNAP_ID = C.SNAP_ID(+)
       )                    C
WHERE   A.SQL_ID = B.SQL_ID
AND     B.DBID = (SELECT DBID FROM V$DATABASE WHERE ROWNUM <= 1)
AND     A.SQL_ID = C.SQL_ID
AND     A.MODULE = 'JDBC Thin Client'
ORDER BY
        --"총CPU시간(분)"     DESC, "총수행횟수"          DESC
        "평균수행시간(초)"  DESC, "평균블록I/O(개수)"   DESC
        --"평균블록I/O(개수)" DESC, "총수행횟수"          DESC
        --"총수행횟수"        DESC, "평균블록I/O(개수)"   DESC
        --"평균수행시간(초)"  DESC, "총수행횟수"          DESC
;