Oracle DB AWR 활용하여 IO 성능 조회 스크립트
SELECT SNAP_TIME, SNAP_TIME_RANGE, TO_CHAR (SUM (DECODE (STAT_NAME, 'session logical reads', VALUE)), 'FM999,999,999,999.9') "Session Logical Reads/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'physical reads', VALUE)), 'FM999,999,999.9') "Physical Reads/Sec", TO_CHAR ( SUM (DECODE (STAT_NAME, 'physical read total IO requests', VALUE)) + SUM ( DECODE (STAT_NAME, 'physical write total IO requests', VALUE)), 'FM999,999,999.9') "IOPS", TO_CHAR ( (SUM (DECODE (STAT_NAME, 'physical read total bytes', VALUE)) + SUM (DECODE (STAT_NAME, 'physical write total bytes', VALUE))) / 1024 / 1024, 'FM999,999,999.99') "MBPS", TO_CHAR ( GREATEST ( LEAST ( (1 - (SUM (DECODE (STAT_NAME, 'physical reads', VALUE)) - SUM ( DECODE (STAT_NAME, 'physical reads direct', VALUE)) - SUM ( DECODE (STAT_NAME, 'physical reads direct (lob)', VALUE))) / DECODE ( (SUM ( DECODE (STAT_NAME, 'session logical reads', VALUE)) - SUM ( DECODE (STAT_NAME, 'physical reads direct', VALUE)) - SUM ( DECODE (STAT_NAME, 'physical reads direct (lob)', VALUE))), 0, 1, (SUM ( DECODE (STAT_NAME, 'session logical reads', VALUE)) - SUM ( DECODE (STAT_NAME, 'physical reads direct', VALUE)) - SUM ( DECODE (STAT_NAME, 'physical reads direct (lob)', VALUE))))) * 100, 100), 0), 'FM999.99') "Buffer Cache Hit Ratio(%)", TO_CHAR ( NVL (SUM (DECODE (STAT_NAME, 'db file sequential read', WAITS)), 0), 'FM999,999,999.99') "db file sequential r avg.(ms)", TO_CHAR ( NVL (SUM (DECODE (STAT_NAME, 'db file scattered read', WAITS)), 0), 'FM999,999,999.99') "db file scattered r avg.(ms)", TO_CHAR ( NVL (SUM (DECODE (STAT_NAME, 'db file parallel write', WAITS)), 0), 'FM999,999,999.99') "db file parallel w avg.(ms)", TO_CHAR ( NVL (SUM (DECODE (STAT_NAME, 'log file parallel write', WAITS)), 0), 'FM999,999,999.99') "log file parallel w avg.(ms)", TO_CHAR (NVL (SUM (DECODE (STAT_NAME, 'log file sync', WAITS)), 0), 'FM999,999,999.99') "log file sync avg.(ms)", TO_CHAR ( SUM (DECODE (STAT_NAME, 'physical read total IO requests', VALUE)), 'FM999,999,999,999.9') "Physical R total IO req./Sec", TO_CHAR ( SUM (DECODE (STAT_NAME, 'physical write total IO requests', VALUE)), 'FM999,999,999,999.9') "Physical W total IO req./Sec", TO_CHAR ( SUM (DECODE (STAT_NAME, 'physical read total bytes', VALUE)) / 1024 / 1024, 'FM999,999,999,999.9') "Physical R total MB/Sec", TO_CHAR ( SUM (DECODE (STAT_NAME, 'physical write total bytes', VALUE)) / 1024 / 1024, 'FM999,999,999,999.9') "Physical W total MB/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'physical reads direct', VALUE)), 'FM999,999,999,999.9') "Physical Reads Direct/Sec", TO_CHAR ( SUM (DECODE (STAT_NAME, 'physical reads direct (lob)', VALUE)), 'FM999,999,999,999.9') "Physical Reads Direct(LOB)/Sec" FROM (SELECT STAT_NAME, DECODE (G1, 1, 'SUB AVG', SUBSTR (SNAP_TIME, 1, INSTR (SNAP_TIME, '-') - 1)) SNAP_TIME, SNAP_TIME SNAP_TIME_RANGE, NVL (VALUE, 0) VALUE, NVL (VALUE_DIFF, 0) VALUE_DIFF, NVL (WAITS, 0) WAITS, NVL (WAITS_DIFF, 0) WAITS_DIFF FROM ( SELECT STAT_NAME, START_TIME || '-' || END_TIME SNAP_TIME, ROUND (AVG (NVL (VALUE, 0)), 3) VALUE, ROUND (AVG (NVL (VALUE_DIFF, 0)), 3) VALUE_DIFF, ROUND (AVG (NVL (WAITS, 0)), 3) WAITS, ROUND (AVG (NVL (WAITS_DIFF, 0)), 3) WAITS_DIFF, GROUPING (START_TIME || '-' || END_TIME) G1, GROUPING (STAT_NAME) G2 FROM (SELECT STAT_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, ROUND ( (CASE WHEN VALUE_2 < VALUE_1 THEN 0 ELSE VALUE_2 - VALUE_1 END) / (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)), 1)) VALUE, (CASE WHEN VALUE_2 < VALUE_1 THEN 0 ELSE VALUE_2 - VALUE_1 END) VALUE_DIFF, NULL WAITS, NULL WAITS_DIFF, SNAP_ID, INSTANCE_NUMBER FROM ( SELECT /*+ LEADING(DBI) USE_HASH(SNAP STAT) */ SNAP.END_INTERVAL_TIME SNAP_TIME_C1, LEAD ( SNAP.END_INTERVAL_TIME) OVER ( PARTITION BY DBI.INSTANCE_NUMBER, STAT_NAME ORDER BY SNAP.SNAP_ID) SNAP_TIME_C2, STAT.STAT_NAME, STAT.VALUE VALUE_1, LEAD ( STAT.VALUE) OVER ( PARTITION BY DBI.INSTANCE_NUMBER, STAT_NAME ORDER BY SNAP.SNAP_ID) VALUE_2, SNAP.SNAP_ID, DBI.INSTANCE_NUMBER FROM (SELECT /*+ NO_MERGE */ 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_SYSSTAT STAT WHERE DBI.DBID = SNAP.DBID AND DBI.INSTANCE_NUMBER = SNAP.INSTANCE_NUMBER AND DBI.DBID = SNAP.DBID AND SNAP.SNAP_ID >= :3 AND SNAP.SNAP_ID <= :4 AND SNAP.DBID = STAT.DBID AND SNAP.INSTANCE_NUMBER = STAT.INSTANCE_NUMBER AND SNAP.SNAP_ID = STAT.SNAP_ID AND STAT.STAT_NAME IN ('session logical reads', 'physical reads', 'physical read total IO requests', 'physical write total IO requests', 'physical read total bytes', 'physical write total bytes', 'physical reads direct', 'physical reads direct (lob)') ORDER BY SNAP.SNAP_ID) WHERE SNAP_TIME_C2 <> SNAP_TIME_C1 UNION ALL SELECT STAT_NAME, TO_CHAR (SNAP_TIME_C1, 'MM.DD HH24:MI') START_TIME, TO_CHAR (SNAP_TIME_C2, 'MM.DD HH24:MI') END_TIME, ROUND ( DECODE ( SNAP_TIME_C2, NULL, 0, (CASE WHEN VALUE_2 < VALUE_1 THEN 0 ELSE VALUE_2 - VALUE_1 END) / (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))) / 1000000, 3) VALUE, ROUND ( (CASE WHEN VALUE_2 < VALUE_1 THEN 0 ELSE VALUE_2 - VALUE_1 END) / 1000000, 1) VALUE_DIFF, ROUND ( DECODE ( SNAP_TIME_C2, NULL, 0, (CASE WHEN WAITS_2 <= WAITS_1 THEN 0 ELSE VALUE_2 - VALUE_1 END) / (WAITS_2 - WAITS_1)) / 1000, 3) WAITS, ROUND ( (CASE WHEN WAITS_2 <= WAITS_1 THEN 0 ELSE WAITS_2 - WAITS_1 END), 1) WAITS_DIFF, 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, STAT.EVENT_NAME STAT_NAME, STAT.TIME_WAITED_MICRO VALUE_1, LEAD ( STAT.TIME_WAITED_MICRO) OVER ( PARTITION BY DBI.INSTANCE_NUMBER, EVENT_NAME ORDER BY SNAP.SNAP_ID) VALUE_2, STAT.TOTAL_WAITS WAITS_1, LEAD ( STAT.TOTAL_WAITS) OVER ( PARTITION BY DBI.INSTANCE_NUMBER, EVENT_NAME ORDER BY SNAP.SNAP_ID) WAITS_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 STAT 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 = STAT.DBID AND SNAP.INSTANCE_NUMBER = STAT.INSTANCE_NUMBER AND SNAP.SNAP_ID = STAT.SNAP_ID AND STAT.EVENT_NAME IN ('db file sequential read', 'db file scattered read', 'db file parallel write', 'log file parallel write', 'log file sync') ORDER BY SNAP.SNAP_ID) WHERE SNAP_TIME_C2 <> SNAP_TIME_C1 AND WAITS_2 <> WAITS_1) WHERE START_TIME IS NOT NULL AND END_TIME IS NOT NULL GROUP BY ROLLUP (STAT_NAME, START_TIME || '-' || END_TIME)) WHERE NOT (G1 = 1 AND G2 = 1)) GROUP BY SNAP_TIME, SNAP_TIME_RANGE ORDER BY SNAP_TIME |
'1. IT Story > Scripts' 카테고리의 다른 글
| Oracle DB 기본 점검 스크립트 (0) | 2019.01.10 |
|---|---|
| Oracle RMAN FULL BACKUP 스크립트 (0) | 2019.01.09 |
| Oracle SQL Stat 조회 스크립트 (0) | 2019.01.08 |
| Oracle Top 10 Event 추출 스크립트 (0) | 2019.01.08 |
| Oracle DB 기본 상태 조회 스크립트 (0) | 2019.01.07 |
| Oracle AWR을 활용한 주요 Stats 지표 추출 스크립트 (0) | 2019.01.07 |
| Oracle 테이블 사이즈 및 테이블별 카운트 출력 스크립트 (0) | 2019.01.07 |
| Oracle Cold(콜드) 백업 스크립트 (0) | 2019.01.07 |
운명을바꾸는자
IT와 함께 살아가는 삶
