Oracle DB의 OS 상태 조회 스크립트
SELECT SNAP_TIME,
SUM (CASE STAT_NAME WHEN 'USER_TIME' THEN VALUE END) USER_TIME,
SUM (CASE STAT_NAME WHEN 'SYS_TIME' THEN VALUE END) SYS_TIME,
SUM (CASE STAT_NAME WHEN 'BUSY_TIME' THEN VALUE END) BUSY_TIME,
SUM (CASE STAT_NAME WHEN 'IOWAIT_TIME' THEN VALUE END) IOWAIT_TIME,
SUM (CASE STAT_NAME WHEN 'IDLE_TIME' THEN VALUE END) IDLE_TIME,
SUM (CASE STAT_NAME WHEN 'AVG_USER_TIME' THEN VALUE END) AVG_USER_TIME,
SUM (CASE STAT_NAME WHEN 'AVG_SYS_TIME' THEN VALUE END) AVG_SYS_TIME,
SUM (CASE STAT_NAME WHEN 'AVG_BUSY_TIME' THEN VALUE END) AVG_BUSY_TIME,
SUM (CASE STAT_NAME WHEN 'AVG_IOWAIT_TIME' THEN VALUE END)
AVG_IOWAIT_TIME,
SUM (CASE STAT_NAME WHEN 'AVG_IDLE_TIME' THEN VALUE END) AVG_IDLE_TIME,
SUM (CASE STAT_NAME WHEN 'LOAD' THEN VALUE END) LOAD,
SUM (CASE STAT_NAME WHEN 'NUM_CPUS' THEN VALUE END) NUM_CPUS,
SUM (CASE STAT_NAME WHEN 'NUM_CPU_SOCKETS' THEN VALUE END)
NUM_CPU_SOCKETS,
SUM (CASE STAT_NAME WHEN 'OS_CPU_WAIT_TIME' THEN VALUE END)
OS_CPU_WAIT_TIME,
SUM (CASE STAT_NAME WHEN 'PHYSICAL_MEMORY_BYTES' THEN VALUE END)
PHYSICAL_MEMORY_BYTES,
SUM (CASE STAT_NAME WHEN 'RSRC_MGR_CPU_WAIT_TIME' THEN VALUE END)
RSRC_MGR_CPU_WAIT_TIME,
SUM (CASE STAT_NAME WHEN 'VM_IN_BYTES' THEN VALUE END) VM_IN_BYTES,
SUM (CASE STAT_NAME WHEN 'VM_OUT_BYTES' THEN VALUE END) VM_OUT_BYTES
FROM (SELECT STAT_NAME,
START_TIME SNAP_TIME,
VALUE,
VALUE_DIFF
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 END_TIME SNAP_TIME_C1,
LEAD (
END_TIME)
OVER (PARTITION BY INSTANCE_NUMBER, STAT_NAME
ORDER BY SNAP_ID)
SNAP_TIME_C2,
VALUE VALUE_1,
LEAD (
VALUE)
OVER (PARTITION BY INSTANCE_NUMBER, STAT_NAME
ORDER BY SNAP_ID)
VALUE_2,
STAT_NAME,
SNAP_ID,
INSTANCE_NUMBER
FROM (SELECT SNAP.END_INTERVAL_TIME END_TIME,
SNAP.SNAP_ID,
DHO.STAT_NAME,
DHO.VALUE,
DHO.INSTANCE_NUMBER
FROM DBA_HIST_OSSTAT DHO,
DBA_HIST_SNAPSHOT SNAP
WHERE DHO.SNAP_ID = SNAP.SNAP_ID
AND DHO.DBID = SNAP.DBID
AND DHO.INSTANCE_NUMBER =
SNAP.INSTANCE_NUMBER
AND DHO.DBID = (select dbid from v$database) --변경
AND DHO.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance) --변경
AND DHO.SNAP_ID >= :3
AND DHO.SNAP_ID <= :4))))
GROUP BY SNAP_TIME
ORDER BY SNAP_TIME
'1. IT Story > Scripts' 카테고리의 다른 글
| Oracle 테이블스페이스 용량확인 스크립트 (0) | 2019.02.04 |
|---|---|
| Oracle 권한확인 스크립트 (0) | 2019.02.03 |
| Oracle SEGMENT STAT 조회 스크립트 (0) | 2019.01.29 |
| Oracle 인덱스 정리 대상 추출 스크립트 (0) | 2019.01.28 |
| OS(리눅스) 5초단위로 서버 및 DB 상태 출력 스크립트 (0) | 2019.01.24 |
| Oracle Alert Log Viewer (11gR2) (0) | 2019.01.19 |
| Oracle Wait Event 조회 스크립트 (0) | 2019.01.16 |
| Oracle Rawdevice구성 RMAN Backup스크립트 (0) | 2019.01.15 |
운명을바꾸는자
IT와 함께 살아가는 삶
