Oracle Top 10 Event 추출 스크립트
SELECT SNAP_TIME, TO_CHAR (SUM (DECODE (RNK, 1, VALUE)), 'FM999,999,999,999.999') "VALUE Per SEC_1", TO_CHAR (SUM (DECODE (RNK, 2, VALUE)), 'FM999,999,999,999.999') "VALUE Per SEC_2", TO_CHAR (SUM (DECODE (RNK, 3, VALUE)), 'FM999,999,999,999.999') "VALUE Per SEC_3", TO_CHAR (SUM (DECODE (RNK, 4, VALUE)), 'FM999,999,999,999.999') "VALUE Per SEC_4", TO_CHAR (SUM (DECODE (RNK, 5, VALUE)), 'FM999,999,999,999.999') "VALUE Per SEC_5", TO_CHAR (SUM (DECODE (RNK, 6, VALUE)), 'FM999,999,999,999.999') "VALUE Per SEC_6", TO_CHAR (SUM (DECODE (RNK, 7, VALUE)), 'FM999,999,999,999.999') "VALUE Per SEC_7", TO_CHAR (SUM (DECODE (RNK, 8, VALUE)), 'FM999,999,999,999.999') "VALUE Per SEC_8", TO_CHAR (SUM (DECODE (RNK, 9, VALUE)), 'FM999,999,999,999.999') "VALUE Per SEC_9", TO_CHAR (SUM (DECODE (RNK, 10, VALUE)), 'FM999,999,999,999.999') "VALUE Per SEC_10", TO_CHAR (SUM (DECODE (RNK, 11, VALUE)), 'FM999,999,999,999.999') "VALUE Per SEC_ETC", TO_CHAR (SUM (DECODE (RNK, 1, VALUE_DIFF)), 'FM999,999,999,999,999') VALUE_DIFF_1, TO_CHAR (SUM (DECODE (RNK, 2, VALUE_DIFF)), 'FM999,999,999,999,999') VALUE_DIFF_2, TO_CHAR (SUM (DECODE (RNK, 3, VALUE_DIFF)), 'FM999,999,999,999,999') VALUE_DIFF_3, TO_CHAR (SUM (DECODE (RNK, 4, VALUE_DIFF)), 'FM999,999,999,999,999') VALUE_DIFF_4, TO_CHAR (SUM (DECODE (RNK, 5, VALUE_DIFF)), 'FM999,999,999,999,999') VALUE_DIFF_5, TO_CHAR (SUM (DECODE (RNK, 6, VALUE_DIFF)), 'FM999,999,999,999,999') VALUE_DIFF_6, TO_CHAR (SUM (DECODE (RNK, 7, VALUE_DIFF)), 'FM999,999,999,999,999') VALUE_DIFF_7, TO_CHAR (SUM (DECODE (RNK, 8, VALUE_DIFF)), 'FM999,999,999,999,999') VALUE_DIFF_8, TO_CHAR (SUM (DECODE (RNK, 9, VALUE_DIFF)), 'FM999,999,999,999,999') VALUE_DIFF_9, TO_CHAR (SUM (DECODE (RNK, 10, VALUE_DIFF)), 'FM999,999,999,999,999') VALUE_DIFF_10, TO_CHAR (SUM (DECODE (RNK, 11, VALUE_DIFF)), 'FM999,999,999,999,999') VALUE_DIFF_ETC FROM (SELECT A.EVENT_NAME, B.EVENT_NAME B_EVENT_NAME, A.SNAP_TIME, A.VALUE, A.VALUE_DIFF, B.RNK RNK FROM (SELECT EVENT_NAME, DECODE (G1, 1, 'SUB AVG', SNAP_TIME) SNAP_TIME, VALUE, VALUE_DIFF FROM ( SELECT EVENT_NAME, END_TIME SNAP_TIME, AVG (NVL (VALUE, 0)) VALUE, AVG (NVL (VALUE_DIFF, 0)) VALUE_DIFF, GROUPING (END_TIME) G1, GROUPING (EVENT_NAME) G2 FROM (SELECT EVENT_NAME, TO_CHAR (SNAP_TIME_C1, 'MM.DD HH24:MI') START_TIME, TO_CHAR (SNAP_TIME_C2, 'MM.DD HH24:MI') END_TIME, DECODE ( SNAP_TIME_C2, NULL, 0, (CASE WHEN VALUE_2 < VALUE_1 THEN 0 ELSE VALUE_2 - VALUE_1 END) / (1000000 * (EXTRACT ( DAY FROM SNAP_TIME_C2 - SNAP_TIME_C1) * 86400 + EXTRACT ( HOUR FROM SNAP_TIME_C2 - SNAP_TIME_C1) * 3600 + EXTRACT ( MINUTE FROM SNAP_TIME_C2 - SNAP_TIME_C1) * 60 + EXTRACT ( SECOND FROM SNAP_TIME_C2 - SNAP_TIME_C1)))) VALUE, (CASE WHEN VALUE_2 < VALUE_1 THEN 0 ELSE VALUE_2 - VALUE_1 END) / 1000000 VALUE_DIFF, ROW_NUMBER () OVER ( PARTITION BY INSTANCE_NUMBER, EVENT_NAME ORDER BY SNAP_ID) RNUM, SNAP_ID, INSTANCE_NUMBER FROM ( SELECT SNAP.END_INTERVAL_TIME SNAP_TIME_C1, LEAD ( SNAP.END_INTERVAL_TIME) OVER ( PARTITION BY DBI.INSTANCE_NUMBER, EVENT_NAME ORDER BY SNAP.SNAP_ID) SNAP_TIME_C2, EVENT.EVENT_NAME, EVENT.TIME_WAITED_MICRO VALUE_1, LEAD ( EVENT.TIME_WAITED_MICRO) OVER ( PARTITION BY DBI.INSTANCE_NUMBER, EVENT_NAME ORDER BY SNAP.SNAP_ID) VALUE_2, SNAP.SNAP_ID, DBI.INSTANCE_NUMBER FROM (SELECT DI.DBID, DI.INSTANCE_NUMBER, DI.STARTUP_TIME FROM DBA_HIST_DATABASE_INSTANCE DI WHERE DI.DBID = (select dbid from v$database) --변경 AND DI.INSTANCE_NUMBER =(select INSTANCE_NUMBER from v$instance) --변경 AND ROWNUM <= 1) DBI, DBA_HIST_SNAPSHOT SNAP, DBA_HIST_SYSTEM_EVENT EVENT WHERE DBI.DBID = SNAP.DBID AND DBI.INSTANCE_NUMBER = SNAP.INSTANCE_NUMBER AND SNAP.SNAP_ID >= :3 AND SNAP.SNAP_ID <= :4 AND SNAP.DBID = EVENT.DBID AND SNAP.INSTANCE_NUMBER = EVENT.INSTANCE_NUMBER AND SNAP.SNAP_ID = EVENT.SNAP_ID AND UPPER (EVENT.WAIT_CLASS) <> 'IDLE' ORDER BY SNAP.SNAP_ID)) WHERE START_TIME IS NOT NULL AND END_TIME IS NOT NULL GROUP BY ROLLUP (EVENT_NAME, END_TIME)) WHERE NOT (G1 = 1 AND G2 = 1)) A, (SELECT EVENT_NAME, RNK FROM (SELECT EVENT_NAME, RNK FROM (SELECT EVENT_NAME, RANK () OVER ( ORDER BY VALUE_DIFF DESC, EVENT_NAME ASC NULLS LAST) RNK FROM (SELECT EVENT_NAME, DECODE (G1, 1, 'SUB AVG', SNAP_TIME) SNAP_TIME, VALUE, VALUE_DIFF FROM ( SELECT EVENT_NAME, END_TIME SNAP_TIME, AVG (NVL (VALUE, 0)) VALUE, AVG ( NVL (VALUE_DIFF, 0)) VALUE_DIFF, GROUPING (END_TIME) G1, GROUPING (EVENT_NAME) G2 FROM (SELECT EVENT_NAME, TO_CHAR ( SNAP_TIME_C1, 'MM.DD HH24:MI') START_TIME, TO_CHAR ( SNAP_TIME_C2, 'MM.DD HH24:MI') END_TIME, DECODE ( SNAP_TIME_C2, NULL, 0, (CASE WHEN VALUE_2 < VALUE_1 THEN 0 ELSE VALUE_2 - VALUE_1 END) / (1000000 * (EXTRACT ( DAY FROM SNAP_TIME_C2 - SNAP_TIME_C1) * 86400 + EXTRACT ( HOUR FROM SNAP_TIME_C2 - SNAP_TIME_C1) * 3600 + EXTRACT ( MINUTE FROM SNAP_TIME_C2 - SNAP_TIME_C1) * 60 + EXTRACT ( SECOND FROM SNAP_TIME_C2 - SNAP_TIME_C1)))) VALUE, (CASE WHEN VALUE_2 < VALUE_1 THEN 0 ELSE VALUE_2 - VALUE_1 END) / 1000000 VALUE_DIFF, ROW_NUMBER () OVER ( PARTITION BY INSTANCE_NUMBER, EVENT_NAME ORDER BY SNAP_ID) RNUM, SNAP_ID, INSTANCE_NUMBER FROM ( SELECT SNAP.END_INTERVAL_TIME SNAP_TIME_C1, LEAD ( SNAP.END_INTERVAL_TIME) OVER ( PARTITION BY DBI.INSTANCE_NUMBER, EVENT_NAME ORDER BY SNAP.SNAP_ID) SNAP_TIME_C2, EVENT.EVENT_NAME, EVENT.TIME_WAITED_MICRO VALUE_1, LEAD ( EVENT.TIME_WAITED_MICRO) OVER ( PARTITION BY DBI.INSTANCE_NUMBER, EVENT_NAME ORDER BY SNAP.SNAP_ID) VALUE_2, SNAP.SNAP_ID, DBI.INSTANCE_NUMBER FROM (SELECT DI.DBID, DI.INSTANCE_NUMBER, DI.STARTUP_TIME FROM DBA_HIST_DATABASE_INSTANCE DI WHERE DI.DBID =(select dbid from v$database) --변경 AND DI.INSTANCE_NUMBER =(select INSTANCE_NUMBER from v$instance) --변경 AND ROWNUM <= 1) DBI, DBA_HIST_SNAPSHOT SNAP, DBA_HIST_SYSTEM_EVENT EVENT WHERE DBI.DBID = SNAP.DBID AND DBI.INSTANCE_NUMBER = SNAP.INSTANCE_NUMBER AND SNAP.SNAP_ID >= :7 AND SNAP.SNAP_ID <= :8 AND SNAP.DBID = EVENT.DBID AND SNAP.INSTANCE_NUMBER = EVENT.INSTANCE_NUMBER AND SNAP.SNAP_ID = EVENT.SNAP_ID AND UPPER ( EVENT.WAIT_CLASS) <> 'IDLE' ORDER BY SNAP.SNAP_ID)) WHERE START_TIME IS NOT NULL AND END_TIME IS NOT NULL GROUP BY ROLLUP (EVENT_NAME, END_TIME)) WHERE NOT (G1 = 1 AND G2 = 1)) WHERE SNAP_TIME = 'SUB AVG') WHERE RNK <= 10)) B WHERE A.EVENT_NAME = B.EVENT_NAME UNION ALL SELECT ' 나머지 Event Sum' EVENT_NAME, NULL B_EVENT_NAME, A.SNAP_TIME, SUM (A.VALUE) VALUE, SUM (A.VALUE_DIFF) VALUE_DIFF, 11 RNK FROM (SELECT EVENT_NAME, DECODE (G1, 1, 'SUB AVG', SNAP_TIME) SNAP_TIME, VALUE, VALUE_DIFF FROM ( SELECT EVENT_NAME, END_TIME SNAP_TIME, AVG (NVL (VALUE, 0)) VALUE, AVG (NVL (VALUE_DIFF, 0)) VALUE_DIFF, GROUPING (END_TIME) G1, GROUPING (EVENT_NAME) G2 FROM (SELECT EVENT_NAME, TO_CHAR (SNAP_TIME_C1, 'MM.DD HH24:MI') START_TIME, TO_CHAR (SNAP_TIME_C2, 'MM.DD HH24:MI') END_TIME, DECODE ( SNAP_TIME_C2, NULL, 0, (CASE WHEN VALUE_2 < VALUE_1 THEN 0 ELSE VALUE_2 - VALUE_1 END) / (1000000 * (EXTRACT ( DAY FROM SNAP_TIME_C2 - SNAP_TIME_C1) * 86400 + EXTRACT ( HOUR FROM SNAP_TIME_C2 - SNAP_TIME_C1) * 3600 + EXTRACT ( MINUTE FROM SNAP_TIME_C2 - SNAP_TIME_C1) * 60 + EXTRACT ( SECOND FROM SNAP_TIME_C2 - SNAP_TIME_C1)))) VALUE, (CASE WHEN VALUE_2 < VALUE_1 THEN 0 ELSE VALUE_2 - VALUE_1 END) / 1000000 VALUE_DIFF, ROW_NUMBER () OVER ( PARTITION BY INSTANCE_NUMBER, EVENT_NAME ORDER BY SNAP_ID) RNUM, SNAP_ID, INSTANCE_NUMBER FROM ( SELECT SNAP.END_INTERVAL_TIME SNAP_TIME_C1, LEAD ( SNAP.END_INTERVAL_TIME) OVER ( PARTITION BY DBI.INSTANCE_NUMBER, EVENT_NAME ORDER BY SNAP.SNAP_ID) SNAP_TIME_C2, EVENT.EVENT_NAME, EVENT.TIME_WAITED_MICRO VALUE_1, LEAD ( EVENT.TIME_WAITED_MICRO) OVER ( PARTITION BY DBI.INSTANCE_NUMBER, EVENT_NAME ORDER BY SNAP.SNAP_ID) VALUE_2, SNAP.SNAP_ID, DBI.INSTANCE_NUMBER FROM (SELECT DI.DBID, DI.INSTANCE_NUMBER, DI.STARTUP_TIME FROM DBA_HIST_DATABASE_INSTANCE DI WHERE DI.DBID = (select dbid from v$database) --변경 AND DI.INSTANCE_NUMBER =(select INSTANCE_NUMBER from v$instance) --변경 AND ROWNUM <= 1) DBI, DBA_HIST_SNAPSHOT SNAP, DBA_HIST_SYSTEM_EVENT EVENT WHERE DBI.DBID = SNAP.DBID AND DBI.INSTANCE_NUMBER = SNAP.INSTANCE_NUMBER AND SNAP.SNAP_ID >= :11 AND SNAP.SNAP_ID <= :12 AND SNAP.DBID = EVENT.DBID AND SNAP.INSTANCE_NUMBER = EVENT.INSTANCE_NUMBER AND SNAP.SNAP_ID = EVENT.SNAP_ID AND UPPER (EVENT.WAIT_CLASS) <> 'IDLE' ORDER BY SNAP.SNAP_ID)) WHERE START_TIME IS NOT NULL AND END_TIME IS NOT NULL GROUP BY ROLLUP (EVENT_NAME, END_TIME)) WHERE NOT (G1 = 1 AND G2 = 1)) A WHERE A.EVENT_NAME NOT IN (SELECT EVENT_NAME FROM (SELECT EVENT_NAME, RNK FROM (SELECT EVENT_NAME, RNK FROM (SELECT EVENT_NAME, RANK () OVER ( ORDER BY VALUE_DIFF DESC, EVENT_NAME ASC NULLS LAST) RNK FROM (SELECT EVENT_NAME, DECODE ( G1, 1, 'SUB AVG', SNAP_TIME) SNAP_TIME, VALUE, VALUE_DIFF FROM ( SELECT EVENT_NAME, END_TIME SNAP_TIME, AVG ( NVL ( VALUE, 0)) VALUE, AVG ( NVL ( VALUE_DIFF, 0)) VALUE_DIFF, GROUPING ( END_TIME) G1, GROUPING ( EVENT_NAME) G2 FROM (SELECT EVENT_NAME, TO_CHAR ( SNAP_TIME_C1, 'MM.DD HH24:MI') START_TIME, TO_CHAR ( SNAP_TIME_C2, 'MM.DD HH24:MI') END_TIME, DECODE ( SNAP_TIME_C2, NULL, 0, (CASE WHEN VALUE_2 < VALUE_1 THEN 0 ELSE VALUE_2 - VALUE_1 END) / (1000000 * (EXTRACT ( DAY FROM SNAP_TIME_C2 - SNAP_TIME_C1) * 86400 + EXTRACT ( HOUR FROM SNAP_TIME_C2 - SNAP_TIME_C1) * 3600 + EXTRACT ( MINUTE FROM SNAP_TIME_C2 - SNAP_TIME_C1) * 60 + EXTRACT ( SECOND FROM SNAP_TIME_C2 - SNAP_TIME_C1)))) VALUE, (CASE WHEN VALUE_2 < VALUE_1 THEN 0 ELSE VALUE_2 - VALUE_1 END) / 1000000 VALUE_DIFF, ROW_NUMBER () OVER ( PARTITION BY INSTANCE_NUMBER, EVENT_NAME ORDER BY SNAP_ID) RNUM, SNAP_ID, INSTANCE_NUMBER FROM ( SELECT SNAP.END_INTERVAL_TIME SNAP_TIME_C1, LEAD ( SNAP.END_INTERVAL_TIME) OVER ( PARTITION BY DBI.INSTANCE_NUMBER, EVENT_NAME ORDER BY SNAP.SNAP_ID) SNAP_TIME_C2, EVENT.EVENT_NAME, EVENT.TIME_WAITED_MICRO VALUE_1, LEAD ( EVENT.TIME_WAITED_MICRO) OVER ( PARTITION BY DBI.INSTANCE_NUMBER, EVENT_NAME ORDER BY SNAP.SNAP_ID) VALUE_2, SNAP.SNAP_ID, DBI.INSTANCE_NUMBER FROM (SELECT DI.DBID, DI.INSTANCE_NUMBER, DI.STARTUP_TIME FROM DBA_HIST_DATABASE_INSTANCE DI WHERE DI.DBID =(select dbid from v$database) --변경 AND DI.INSTANCE_NUMBER =(select INSTANCE_NUMBER from v$instance) --변경 AND ROWNUM <= 1) DBI, DBA_HIST_SNAPSHOT SNAP, DBA_HIST_SYSTEM_EVENT EVENT WHERE DBI.DBID = SNAP.DBID AND DBI.INSTANCE_NUMBER = SNAP.INSTANCE_NUMBER AND SNAP.SNAP_ID >= :15 AND SNAP.SNAP_ID <= :16 AND SNAP.DBID = EVENT.DBID AND SNAP.INSTANCE_NUMBER = EVENT.INSTANCE_NUMBER AND SNAP.SNAP_ID = EVENT.SNAP_ID AND UPPER ( EVENT.WAIT_CLASS) <> 'IDLE' ORDER BY SNAP.SNAP_ID)) WHERE START_TIME IS NOT NULL AND END_TIME IS NOT NULL GROUP BY ROLLUP (EVENT_NAME, END_TIME)) WHERE NOT (G1 = 1 AND G2 = 1)) WHERE SNAP_TIME = 'SUB AVG') WHERE RNK <= 10)) B) GROUP BY A.SNAP_TIME, A.SNAP_TIME ORDER BY RNK, SNAP_TIME) GROUP BY SNAP_TIME ORDER BY SNAP_TIME; |
'1. IT Story > Scripts' 카테고리의 다른 글
| 오라클 SQL TRACE 추출 스크립트 (0) | 2019.01.13 |
|---|---|
| Oracle DB 기본 점검 스크립트 (0) | 2019.01.10 |
| Oracle RMAN FULL BACKUP 스크립트 (0) | 2019.01.09 |
| Oracle SQL Stat 조회 스크립트 (0) | 2019.01.08 |
| Oracle DB AWR 활용하여 IO 성능 조회 스크립트 (0) | 2019.01.07 |
| Oracle DB 기본 상태 조회 스크립트 (0) | 2019.01.07 |
| Oracle AWR을 활용한 주요 Stats 지표 추출 스크립트 (0) | 2019.01.07 |
| Oracle 테이블 사이즈 및 테이블별 카운트 출력 스크립트 (0) | 2019.01.07 |
운명을바꾸는자
IT와 함께 살아가는 삶
