Oracle AWR을 활용한 주요 Stats 지표 추출 스크립트
해당 시점의 SNAP.SNAP_ID를 설정하여 추출한 데이터를 엑셀 또는 시각화 Tool로 뽑아내여 주요 Stats 상태 분석이 가능하도록 해준다. 각 Stats의 명칭을 조정하여 원하는 DB 상태를 조회해볼수 있다. 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, 'execute count', VALUE)), 'FM999,999,999.9') "Execute Count/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'user calls', VALUE)), 'FM999,999,999.9') "User Calls/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'user commits', VALUE)), 'FM999,999,999.9') "User Commits/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'user rollbacks', VALUE)), 'FM999,999,999.9') "User Rollbacks/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'CPU used by this session', VALUE)), 'FM999,999,999.9') "CPU used by this session/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'DB time', VALUE)), 'FM999,999,999.9') "DB time/Sec", TO_CHAR ( (SUM (DECODE (STAT_NAME, 'DB time', VALUE)) * 10) / DECODE (SUM (DECODE (STAT_NAME, 'user calls', VALUE)), 0, 1, SUM (DECODE (STAT_NAME, 'user calls', VALUE))), 'FM999,999,999.999') "User Response Time(ms)/Sec", TO_CHAR ( (SUM (DECODE (STAT_NAME, 'DB time', VALUE)) * 10) / DECODE ( (SUM (DECODE (STAT_NAME, 'user commits', VALUE)) + SUM (DECODE (STAT_NAME, 'user rollbacks', VALUE))), 0, 1, (SUM (DECODE (STAT_NAME, 'user commits', VALUE)) + SUM (DECODE (STAT_NAME, 'user rollbacks', VALUE)))), 'FM999,999,999.999') "TX Response Time(ms)/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'application wait time', VALUE)), 'FM999,999,999.9') "Application Wait Time(cs)/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'cluster wait time', VALUE)), 'FM999,999,999.9') "Cluster Wait Time(cs)/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'concurrency wait time', VALUE)), 'FM999,999,999.9') "Concurrency Wait Time(cs)/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'user I/O wait time', VALUE)), 'FM999,999,999.9') "User I/O Wait Time(cs)/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'CR blocks created', VALUE)), 'FM999,999,999.9') "CR blocks created/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'consistent gets', VALUE)), 'FM999,999,999.9') "Consistent Gets/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'consistent changes', VALUE)), 'FM999,999,999.9') "Consistent Changes/Sec", TO_CHAR ( SUM ( DECODE ( STAT_NAME, 'data blocks consistent reads - undo records applied', VALUE)), 'FM999,999,999.9') "DB Blocks cr-undo rec appl/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'db block gets', VALUE)), 'FM999,999,999.9') "DB Block Gets/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'db block changes', VALUE)), 'FM999,999,999.9') "DB Block Changes/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'enqueue releases', VALUE)), 'FM999,999,999.9') "Enqueue Releases/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'enqueue waits', VALUE)), 'FM999,999,999.9') "Enqueue Waits/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'free buffer inspected', VALUE)), 'FM999,999,999.9') "Free Buffer Inspected/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'free buffer requested', VALUE)), 'FM999,999,999.9') "Free Buffer Requested/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'DBWR checkpoints', VALUE)), 'FM999,999,999.9') "DBWR checkpoints/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'DBWR lru scans', VALUE)), 'FM999,999,999.9') "DBWR lru scans/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'DBWR undo block writes', VALUE)), 'FM999,999,999.9') "DBWR undo block writes/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'physical writes', VALUE)), 'FM999,999,999.9') "Physical Writes/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'physical writes direct', VALUE)), 'FM999,999,999.9') "Physical Writes Direct/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'branch node splits', VALUE)), 'FM999,999,999.9') "Branch Node Splits/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'leaf node splits', VALUE)), 'FM999,999,999.9') "Leaf Node Splits/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'leaf node 90-10 splits', VALUE)), 'FM999,999,999.9') "Leaf Node 90-10 Splits/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'commit cleanouts', VALUE)), 'FM999,999,999.9') "Commit Cleanouts/Sec", TO_CHAR ( SUM ( DECODE (STAT_NAME, 'cleanouts only - consistent read gets', VALUE)), 'FM999,999,999.9') "Cleanouts Only-CRgets/Sec", TO_CHAR ( SUM ( DECODE (STAT_NAME, 'cleanouts and rollbacks - consistent read gets', VALUE)), 'FM999,999,999.9') "Cleanouts&Rollbacks-CRgets/Sec", TO_CHAR ( SUM (DECODE (STAT_NAME, 'index fast full scans (full)', VALUE)), 'FM999,999,999.9') "Index FastFullScans(full)/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'table fetch by rowid', VALUE)), 'FM999,999,999.9') "Table Fetch By Rowid/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'table fetch continued row', VALUE)), 'FM999,999,999.9') "Table Fetch Continued Row/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'table scans (long tables)', VALUE)), 'FM999,999,999.9') "Table Scans (long tables)/Sec", TO_CHAR ( SUM (DECODE (STAT_NAME, 'table scans (short tables)', VALUE)), 'FM999,999,999.9') "Table Scans (short tables)/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'table scan blocks gotten', VALUE)), 'FM999,999,999.9') "Table Scan Blocks gotten/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'table scan rows gotten', VALUE)), 'FM999,999,999.9') "Table Scan Rows gotten/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'logons cumulative', VALUE)), 'FM999,999,999.9') "Logons Cumulative/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'opened cursors cumulative', VALUE)), 'FM999,999,999.9') "Opened Cursors Cumulative/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'parse count (total)', VALUE)), 'FM999,999,999.9') "Parse Count (total)/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'parse count (hard)', VALUE)), 'FM999,999,999.9') "Parse Count (hard)/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'parse count (failures)', VALUE)), 'FM999,999,999.9') "Parse Count (failures)/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'parse time cpu', VALUE)), 'FM999,999,999.9') "Parse Time CPU/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'parse time elapsed', VALUE)), 'FM999,999,999.9') "Parse Time Elapsed/Sec", TO_CHAR ( SUM (DECODE (STAT_NAME, 'session cursor cache count', VALUE)), 'FM999,999,999.9') "Session Cursor Cache Count/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'session cursor cache hits', VALUE)), 'FM999,999,999.9') "Session Cursor Cache Hits/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'redo entries', VALUE)), 'FM999,999,999.9') "Redo Entries/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'redo size', VALUE)), 'FM999,999,999.9') "Redo Size/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'redo write time', VALUE)), 'FM999,999,999.9') "Redo Write Time/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'sorts (disk)', VALUE)), 'FM999,999,999.9') "Sorts (disk)/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'sorts (memory)', VALUE)), 'FM999,999,999.9') "Sorts (memory)/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'sorts (rows)', VALUE)), 'FM999,999,999.9') "Sorts (rows)/Sec", TO_CHAR ( SUM ( DECODE ( STAT_NAME, 'transaction tables consistent reads - undo records applied', VALUE)), 'FM999,999,999.9') "TX cr Undo Record applied/Sec", TO_CHAR ( SUM ( DECODE (STAT_NAME, 'transaction tables consistent read rollbacks', VALUE)), 'FM999,999,999.9') "TX cr read rollbacks/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'undo change vector size', VALUE)), 'FM999,999,999.9') "Undo Change Vector Size/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'logons current', VALUE)), 'FM999,999,999.9') "Logons Current", TO_CHAR (SUM (DECODE (STAT_NAME, 'opened cursors current', VALUE)), 'FM999,999,999.9') "Opened Cursors Current", TO_CHAR ( SUM ( DECODE (STAT_NAME, 'SQL*Net roundtrips to/from client', VALUE)), 'FM999,999,999.9') "SQLNet RoundTrip Client/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'physical read total bytes', VALUE)), 'FM999,999,999.9') "Physical Read Total Bytes/Sec", TO_CHAR ( SUM ( DECODE (STAT_NAME, 'cell physical IO interconnect bytes', VALUE)), 'FM999,999,999.9') "CellPhy.IOInterconnectByte/Sec", TO_CHAR ( SUM ( DECODE (STAT_NAME, 'cell physical IO bytes saved by storage index', VALUE)), 'FM999,999,999.9') "CellPhy.IOBytesSavedbySInd/Sec", TO_CHAR ( SUM ( DECODE ( STAT_NAME, 'cell physical IO interconnect bytes returned by smart scan', VALUE)), 'FM999,999,999.9') "CellPhy.IOByteReturnedbySS/Sec", TO_CHAR ( SUM (DECODE (STAT_NAME, 'physical read total IO requests', VALUE)), 'FM999,999,999.9') "Physical Read Total IO Req/Sec", TO_CHAR ( SUM (DECODE (STAT_NAME, 'physical read requests optimized', VALUE)), 'FM999,999,999.9') "Physical Read Req Opt./Sec", TO_CHAR ( SUM (DECODE (STAT_NAME, 'cell flash cache read hits', VALUE)), 'FM999,999,999.9') "Cell Flash Cache Read Hits/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'cell scans', VALUE)), 'FM999,999,999.9') "Cell Scans/Sec", TO_CHAR (SUM (DECODE (STAT_NAME, 'cell index scans', VALUE)), 'FM999,999,999.9') "Cell Index Scans/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, VALUE, VALUE_DIFF FROM ( SELECT STAT_NAME, START_TIME || '-' || END_TIME SNAP_TIME, ROUND (AVG (NVL (VALUE, 0)), 1) VALUE, ROUND (AVG (NVL (VALUE_DIFF, 0)), 1) VALUE_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, ROW_NUMBER () OVER (PARTITION BY INSTANCE_NUMBER, STAT_NAME ORDER BY SNAP_ID) RNUM, 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', 'execute count', 'user calls', 'user commits', 'user rollbacks', 'CPU used by this session', 'DB time', 'application wait time', 'cluster wait time', 'concurrency wait time', 'user I/O wait time', 'CR blocks created', 'consistent changes', 'consistent gets', 'db block changes', 'data blocks consistent reads - undo records applied', 'db block gets', 'enqueue releases', 'enqueue waits', 'DBWR checkpoints', 'DBWR lru scans', 'DBWR undo block writes', 'free buffer inspected', 'free buffer requested', 'physical writes', 'physical writes direct', 'branch node splits', 'leaf node 90-10 splits', 'leaf node splits', 'cleanouts and rollbacks - consistent read gets', 'cleanouts only - consistent read gets', 'commit cleanouts', 'index fast full scans (full)', 'table fetch by rowid', 'table fetch continued row', 'table scan blocks gotten', 'table scan rows gotten', 'table scans (long tables)', 'table scans (short tables)', 'logons cumulative', 'opened cursors cumulative', 'parse count (total)', 'parse count (hard)', 'parse count (failures)', 'parse time cpu', 'parse time elapsed', 'session cursor cache count', 'session cursor cache hits', 'redo entries', 'redo size', 'redo sync time', 'redo write time', 'sorts (disk)', 'sorts (memory)', 'sorts (rows)', 'sql area evicted', 'sql area purged', 'undo change vector size', 'SQL*Net roundtrips to/from client', 'transaction tables consistent reads - undo records applied', 'transaction tables consistent read rollbacks', 'physical read total bytes', 'physical read total IO requests', 'physical read requests optimized', 'cell physical IO interconnect bytes', 'cell physical IO bytes saved by storage index', 'cell physical IO interconnect bytes returned by smart scan', 'cell flash cache read hits', 'cell scans', 'cell index scans') ORDER BY SNAP.SNAP_ID) WHERE SNAP_TIME_C2 <> SNAP_TIME_C1) 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) UNION ALL SELECT STAT_NAME, DECODE (G1, 1, 'SUB AVG', SUBSTR (SNAP_TIME, 1, INSTR (SNAP_TIME, '-') - 1)) SNAP_TIME, SNAP_TIME SNAP_TIME_RANGE, VALUE, NULL VALUE_DIFF FROM ( SELECT STAT_NAME, START_TIME || '-' || END_TIME SNAP_TIME, ROUND ( AVG (DECODE (SIGN (VALUE), -1, 0, NVL (VALUE, 0))), 1) VALUE, GROUPING (START_TIME || '-' || END_TIME) G1, GROUPING (STAT_NAME) G2 FROM (SELECT STAT_NAME, TO_CHAR (SNAP_TIME_1, 'MM.DD HH24:MI') START_TIME, TO_CHAR (SNAP_TIME_2, 'MM.DD HH24:MI') END_TIME, DECODE (SNAP_TIME_2, NULL, 0, ROUND ( (VALUE_1 - VALUE_2), 1)) VALUE, ROW_NUMBER () OVER (PARTITION BY INSTANCE_NUMBER, STAT_NAME ORDER BY SNAP_ID) RNUM, SNAP_ID, INSTANCE_NUMBER FROM ( SELECT SNAP.BEGIN_INTERVAL_TIME SNAP_TIME_1, STAT.STAT_NAME, STAT.VALUE VALUE_1, 0 VALUE_2, SNAP.END_INTERVAL_TIME SNAP_TIME_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_SYSSTAT 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.STAT_NAME IN ('opened cursors current', 'logons current', 'session pga memory', 'session uga memory', 'session pga memory max', 'session uga memory max') ORDER BY SNAP.SNAP_ID)) WHERE RNUM > 1 GROUP BY ROLLUP (STAT_NAME, START_TIME || '-' || END_TIME)) WHERE NOT (G1 = 1 AND G2 = 1) ORDER BY STAT_NAME, SNAP_TIME) 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 AWR 활용하여 IO 성능 조회 스크립트 (0) | 2019.01.07 |
| Oracle DB 기본 상태 조회 스크립트 (0) | 2019.01.07 |
| Oracle 테이블 사이즈 및 테이블별 카운트 출력 스크립트 (0) | 2019.01.07 |
| Oracle Cold(콜드) 백업 스크립트 (0) | 2019.01.07 |
운명을바꾸는자
IT와 함께 살아가는 삶
