Oracle_특정 기간에 수행된 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('2018/11/26 00:00:00', 'YYYY/MM/DD HH24:MI:SS')
AND BEGIN_INTERVAL_TIME <= TO_DATE('2018/12/03 23:59:59', 'YYYY/MM/DD HH24:MI:SS')
)
, DBA_WITH_SQLSTAT
AS (
SELECT /*+ INLINE PARALLEL(1) */
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, 6) 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, 6) AS "총CPU시간(분)"
, ROUND(A.IOWAIT / 60, 6) AS "총IOWAIT(분)"
, ROUND(A.CLWAIT / 60, 6) AS "총CLWAIT(분)"
, ROUND(A.APWAIT / 60, 6) AS "총APWAIT(분)"
, ROUND(A.CCWAIT / 60, 6) 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
;