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;
</source>
-
- 50 System 테이블스페이스에 비시스템 세그먼트 조회
SELECT OWNER
, SEGMENT_NAME
, SEGMENT_TYPE
, TABLESPACE_NAME
FROM DBA_SEGMENTS WHERE OWNER NOT IN ('SYS', 'SYSTEM') AND TABLESPACE_NAME = 'SYSTEM';
</source>
- Buffer Cache Hit Ratio
SELECT ROUND(((1-(SUM(DECODE(name, 'physical reads', value,0))/
(SUM(DECODE(name, 'db block gets', value,0))+
(SUM(DECODE(name, 'consistent gets', value, 0))))))*100),2) || '%' "Buffer Cache Hit Ratio"
FROM V$SYSSTAT;
- Library Cache Hit Ratio
SELECT (1-SUM (reloads)/SUM(pins))*100 "Library Cache Hit Ratio"
From V$LIBRARYCACHE;
- Data Dictionary Cache Hit Ratio
SELECT (1-SUM(getmisses)/SUM(gets))*100 "Data Dictionary Hit Ratio"
FROM V$ROWCACHE;
- 테이블 스페이스 사용량
SELECT a.tablespace_name,
a.total "Total(Mb)",
a.total - b.free "Used(Mb)",
nvl(b.free,0) "Free(Mb)",
round((a.total - nvl(b.free,0))*100/total,0) "Used(%)"
from ( select tablespace_name,
round((sum(bytes)/1024/1024),0) as total
from dba_data_files
group by tablespace_name) a,
( select tablespace_name,
round((sum(bytes)/1024/1024),0) as free
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
order by a.tablespace_name;
- 오라클서버의 메모리
select * from v$sgastat
select pool, sum(bytes) "SIZE"
from v$sgastat
where pool = 'shared pool'
group by pool
- cpu를 많이 사용하는 쿼리문과 프로세스아이디,시리얼번호,머신 알아내기
select c.sql_text
,b.SID
, b.SERIAL#
,b.machine
,b.OSUSER
,b.logon_time --이 쿼리를 호출한 시간
from v$process a, v$session b, v$sqltext c
where a.addr = b.paddr
and b.sql_hash_value = c.hash_value
--and a.spid = '675958'
order by c.PIECE
- cpu를 많이 사용하는 쿼리문과 프로세스아이디,시리얼번호,머신 알아내기
select c.sql_text
from v$process a, v$session b, v$sqltext c
where a.addr = b.paddr
and b.sql_hash_value = c.hash_value
and a.spid = '171'
order by c.PIECE
- 프로세스 아이디를 이용하여 쿼리문 알아내기
select c.sql_text
,b.SID
, b.SERIAL#
,b.machine
,b.OSUSER
,b.logon_time --이 쿼리를 호출한 시간
from v$process a, v$session b, v$sqltext c
where a.addr = b.paddr
and b.sql_hash_value = c.hash_value
and a.spid = '1708032' --1912870/
order by c.PIECE
- 세션 죽이기(SID,SERAIL#)
ALTER SYSTEM KILL SESSION '8,4093'
## 오라클 세션과 관련된 테이블*/
<source lang="sql">
--select count(*) from v$session where machine ='머신이름' and schemaname ='스키마이름'
- 현재 커서 수 확인
SELECT sid, count(sid) cursor
FROM V$OPEN_CURSOR
WHERE user_name = 'ilips'
GROUP BY sid
ORDER BY cursor DESC
SELECT sql_text, count(sid) cnt
FROM v$OPEN_CURSOR
GROUP BY sql_text
ORDER BY cnt DESC
select * from v$session_wait
select sid, serial#, username, taddr, used_ublk, used_urec
from v$transaction t, v$session s
where t.addr = s.taddr;
select * from sys.v_$open_cursor
- V$LOCK 을 사용한 잠금 경합 모니터링
SELECT s.username, s.sid, s.serial#, s.logon_time,
DECODE(l.type, 'TM', 'TABLE LOCK',
'TX', 'ROW LOCK',
NULL) "LOCK LEVEL",
o.owner, o.object_name, o.object_type
FROM v$session s, v$lock l, dba_objects o
WHERE s.sid = l.sid
AND o.object_id = l.id1
AND s.username IS NOT NULL
- 락이 걸린 세션 자세히 알아보기
select a.sid, a.serial#,a.username,a.process,b.object_name,
decode(c.lmode,2,'RS',3,'RX',4,'S',5,'SRX',8,'X','NO') "TABLE LOCK",
decode (a.command,2,'INSERT',3,'SELECT',6,'UPDATE',7,'DELETE',12,'DROP TABLE',26,'LOCK TABLE','UNknown') "SQL",
decode(a.lockwait, NULL,'NO wait','Wait') "STATUS"
from v$session a,dba_objects b, v$lock c
where a.sid=c.sid and b.object_id=c.id1
and c.type='TM'
- 락이 걸린 세션 간단히 알아보기
select a.sid, a.serial#, b.type, c.object_name, a.program, a.lockwait,
a.logon_time, a.process, a.osuser, a.terminal
from v$session a, v$lock b, dba_objects c
where a.sid = b.sid
and b.id1 = c.object_id
and b.type = 'TM';
select a.sid, a.serial#, a.username, a.process, b.object_name
from v$session a , dba_objects b, v$lock c
where a.sid=c.sid and b.object_id = c.id1
and c.type = 'TM'
--락이 걸린 세션을 찾아 내어 세션을 죽이려고 해도 죽지 않는 경우 --아래 쿼리문으로 OS단의 PROCESS ID를 찾아내어 OS에서 죽인다 --kill -9 프로세스아이디
select substr(s.username,1,11) "ORACLE USER", p.pid "PROCESS ID",
s.sid "SESSION ID", s.serial#, osuser "OS USER",
p.spid "PROC SPID",s.process "SESS SPID", s.lockwait "LOCK WAIT"
from v$process p, v$session s, v$access a
where a.sid=s.sid and
p.addr=s.paddr and
s.username != 'SYS'
- 위 쿼리문의 결과가 있다면 락이 걸린 세션이 있다는것이므로 아래의 쿼리문으로 세션을 죽인다
ALTER SYSTEM KILL SESSION '11,39061'
alter session으로 죽지않는 프로세스 죽이기
1.oracle이 설치된 서버에 텔넷으로 root로 접속한다
2.su -오라클계정
3.sqlplus '/as sysdba''
4.connect system/sys
5.ALTER SYSTEM KILL SESSION '137,1723'