TOP SQL 추출

/* 
**  특정 모듈에서 수행된 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/06 16:00:00', 'YYYY/MM/DD HH24:MI:SS') 
        AND     BEGIN_INTERVAL_TIME <= TO_DATE('2016/01/06 17: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 
ORDER BY 
        --"평균수행시간(초)"  DESC, "평균블록I/O(개수)"   DESC 
        --"평균블록I/O(개수)"  DESC, "총수행횟수"         DESC 
        "총수행횟수"        DESC, "평균블록I/O(개수)"   DESC 
        --"평균수행시간(초)"  DESC, "총수행횟수"          DESC 
;