ORACLE 모니터링
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
- 7. 모니터링
-
- 38 연결되어 있는 OS 사용자 및 프로그램 조회
SELECT SID
, SERIAL#
, OSUSER
, SUBSTRB(USERNAME, 1, 10) AS USER_NAME
, SUBSTRB(PROGRAM, 1, 30) AS PROGRAM_NAME
, STATUS
, TO_CHAR(LOGON_TIME, 'YYYY/MM/DD HH:MI') AS LOGON_TIME
FROM V$SESSION WHERE TYPE!= ‘BACKGROUND’ AND STATUS = ‘ACTIVE’;-
- 39 1시간 이상 유휴 상태인 세션
SELECT SID
, SERIAL#
, USERNAME
, TRUNC(LAST_CALL_ET / 3600, 2) || ' HR' LAST_CALL_ET
FROM V$SESSION WHERE LAST_CALL_ET > 3600 AND USERNAME IS NOT NULL;-
- 40 Active Session 중 Idle Time이 긴 작업
SELECT VS.SID || ',' || VS.SERIAL# " SID"
, VP.SPID
, VS.MACHINE
, VS.PROGRAM
, VS.MODULE
, VS.STATUS
, TO_CHAR(VS.LOGON_TIME, 'MM/DD HH24:MI') LOGIN_TIME
, ROUND(VS.LAST_CALL_ET / 60) "IDLE"
FROM V$SESSION VS
, V$PROCESS VP
WHERE VS.STATUS = 'ACTIVE' AND VS.SID NOT IN (1, 2, 3, 4, 5, 6, 7) AND VS.PADDR = VP.ADDR ORDER BY 8;-
- 41 DBUser 별로 Session 정보를 조회
SELECT S.USERNAME
, S.SID
, S.SERIAL#
, P.SPID
, S.OSUSER
, S.MACHINE
, S.PROGRAM
, TO_CHAR(S.LOGON_TIME, 'MM/DD HH24:MI') "LOGON_TIME"
, ROUND(S.LAST_CALL_ET / 60) "IDLE"
FROM V$SESSION S
, V$PROCESS P
WHERE S.PADDR = P.ADDR AND S.USERNAME LIKE UPPER('&DBUSER%') ORDER BY 9;-
- 42 Session별 사용 명령어
SELECT SESS.SID
, SESS.SERIAL#
, SUBSTR(SESS.USERNAME, 1, 10) "USER NAME"
, SUBSTR(OSUSER, 1, 11) "OS USER"
, SUBSTR(SESS.MACHINE, 1, 15) "MACHINE NAME"
, STATUS
, UPPER(
DECODE(NVL(COMMAND, 0)
, 0, '---'
, 1, 'CREATE TABLE'
, 2, 'INSERT -'
, 3, 'SELECT -'
, 4, 'CREATE CLUST'
, 5, 'ALTER CLUST'
, 6, 'UPDATE -'
, 7, 'DELETE -'
, 8, 'DROP -'
, 9, 'CREATE INDEX'
, 10, 'DROP INDEX'
, 11, 'ALTER INDEX'
, 12, 'DROP TABLE'
, 13, 'CREATE SEQ'
, 14, 'ALTER SEQ'
, 15, 'ALTER TABLE'
, 16, 'DROP SEQ'
, 17, 'GRANT'
, 18, 'REVOKE'
, 19, 'CREATE SYN'
, 20, 'DROP SYN'
, 21, 'CREATE VIEW'
, 22, 'DROP VIEW'
, 23, 'VALIDATE IX'
, 24, 'CREATE PROC'
, 25, 'ALTER PROC'
, 26, 'LOCK TABLE'
, 27, 'NO OPERATION'
, 28, 'RENAME'
, 29, 'COMMENT'
, 30, 'AUDIT'
, 31, 'NOAUDIT'
, 32, 'CREATE DBLINK'
, 33, 'DROP DB LINK'
, 34, 'CREATE DATABASE'
, 35, 'ALTER DATABASE'
, 36, 'CREATE RBS'
, 37, 'ALTER RBS'
, 38, 'DROP RBS'
, 39, 'CREATE TABLESPACE'
, 40, 'ALTER TABLESPACE'
, 41, 'DROP TABLESPACE'
, 42, 'ALTER SESSION'
, 43, 'ALTER USER'
, 44, 'COMMIT'
, 45, 'ROLLBACK'
, 47, 'PL/SQL EXEC'
, 48, 'SET TRANSACTION'
, 49, 'SWITCH LOG'
, 50, 'EXPLAIN'
, 51, 'CREATE USER'
, 52, 'CREATE ROLE'
, 53, 'DROP USER'
, 54, 'DROP ROLE'
, 55, 'SET ROLE'
, 56, 'CREATE SCHEMA'
, 58, 'ALTER TRACING'
, 59, 'CREATE TRIGGER'
, 61, 'DROP TRIGGER'
, 62, 'ANALYZE TABLE'
, 63, 'ANALYZE INDEX'
, 69, 'DROP PROCEDURE'
, 71, 'CREATE SNAP LOG'
, 72, 'ALTER SNAP LOG'
, 73, 'DROP SNAP LOG'
, 74, 'CREATE SNAPSHOT'
, 75, 'ALTER SNAPSHOT'
, 76, 'DROP SNAPSHOT'
, 85, 'TRUNCATE TABLE'
, 88, 'ALTER VIEW'
, 91, 'CREATE FUNCTION'
, 92, 'ALTER FUNCTION'
, 93, 'DROP FUNCTION'
, 94, 'CREATE PACKAGE'
, 95, 'ALTER PACKAGE'
, 96, 'DROP PACKAGE'
, 46, 'SAVEPOINT'
)
)
COMMAND
, SESS.PROCESS "C.PROC"
, PROC.SPID "S.PROC"
, TO_CHAR(SESS.LOGON_TIME, 'YYYY-MM-DD HH24:MI')
FROM V$SESSION SESS
, V$SESSTAT STAT
, V$STATNAME NAME
, V$PROCESS PROC
WHERE SESS.SID = STAT.SID AND STAT.STATISTIC# = NAME.STATISTIC# AND SESS.USERNAME IS NOT NULL AND NAME.NAME = 'RECURSIVE CALLS' AND SESS.PADDR = PROC.ADDR ORDER BY 3, 1, 2;-
- 43 사용자 session 중에서 2시간 이상 idle 상태가 지속되는 session을 kill
SET PAGESIZE 0 SPOOL KILLIDLE3.SQL
SELECT DISTINCT '!KILL -9 ' || B.SPID, 'ALTER SYSTEM KILL SESSION '''|| A.SID || ',' || A.SERIAL# || ''';' FROM V$SESSION A
, V$PROCESS B
WHERE A.PADDR IN (SELECT S.PADDR
FROM V$SESSION S
WHERE STATUS = 'INACTIVE'
GROUP BY S.PADDR
HAVING MIN(ROUND(LAST_CALL_ET / 60)) > 120)
AND A.PADDR = B.ADDR AND A.STATUS = 'INACTIVE';
SPOOL OFF-
- 44 Oracle Process의 정보
SELECT S.STATUS "STATUS"
, S.SERIAL# "SERIAL#"
, S.TYPE "TYPE"
, S.USERNAME "DB USER"
, S.OSUSER "CLIENT USER"
, S.SERVER "SERVER"
, S.MACHINE "MACHINE"
, S.MODULE "MODULE"
, S.TERMINAL "TERMINAL"
, S.PROGRAM "PROGRAM"
, P.PROGRAM "O.S. PROGRAM"
, S.LOGON_TIME "CONNECT TIME"
, LOCKWAIT "LOCK WAIT"
, SI.PHYSICAL_READS "PHYSICAL READS"
, SI.BLOCK_GETS "BLOCK GETS"
, SI.CONSISTENT_GETS "CONSISTENT GETS"
, SI.BLOCK_CHANGES "BLOCK CHANGES"
, SI.CONSISTENT_CHANGES "CONSISTENT CHANGES"
, S.PROCESS "PROCESS"
, P.SPID
, P.PID
, S.SERIAL#
, SI.SID
, S.SQL_ADDRESS "ADDRESS"
, S.SQL_HASH_VALUE "SQL HASH"
, S.ACTION
FROM V$SESSION S
, V$PROCESS P
, SYS.V_$SESS_IO SI
WHERE S.PADDR = P.ADDR(+) AND SI.SID(+) = S.SID AND S.USERNAME IS NOT NULL AND NVL(S.OSUSER, 'X') <> 'SYSTEM' AND S.TYPE <> 'BACKGROUND' ORDER BY 3;-
- 45 오브젝트에 접속되어 있는 프로그램 조회
SELECT SUBSTR(B.OBJECT, 1, 15) AS OBJECT, SUBSTR(A.PROGRAM, 1, 15) AS PROGRAM, COUNT(*) AS CNT FROM V$SESSION A
, V$ACCESS B
WHERE A.SID = B.SID AND B.OWNER NOT IN ('SYS') AND A.TYPE!= 'BACKGROUND' AND B.OBJECT LIKE UPPER('&OBJECT_NAME') || '%' GROUP BY B.OBJECT, SUBSTR(A.PROGRAM, 1, 15);-
- 46 롤백 세그먼트 경합 조회
SELECT NAME T0
, GETS T1
, WAITS T2
, TO_CHAR(TRUNC(WAITS / GETS * 100, 2), 099.99) || '%' T3
, TO_CHAR(ROUND(RSSIZE / 1024)) T4
, SHRINKS T5
, EXTENDS T6
FROM V$ROLLSTAT
, V$ROLLNAME
WHERE V$ROLLSTAT.USN = V$ROLLNAME.USN;-
- 50 System 테이블스페이스에 비시스템 세그먼트 조회
SELECT OWNER
, SEGMENT_NAME
, SEGMENT_TYPE
, TABLESPACE_NAME
FROM DBA_SEGMENTS WHERE OWNER NOT IN ('SYS', 'SYSTEM') AND TABLESPACE_NAME = 'SYSTEM';