ORACLE 모니터링
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
DB 모니터링
목차
1 세션/접속사용자 정보[편집]
1.1 세션 죽이기(SID,SERAIL#)[편집]
ALTER SYSTEM KILL SESSION '8,4093'
=== 오라클 세션과 관련된 테이블 ===
* v$session
<source lang="sql">
select count(*)
from v$session
where machine ='머신이름'
and schemaname ='스키마이름'
1.2 현재 커서 수 확인[편집]
- V$OPEN_CURSOR
- v$session_wait
- v$transaction
- v$session_wait
-- sid별 열린커셔
SELECT sid, count(sid) cursor
FROM V$OPEN_CURSOR
WHERE user_name = 'ilips'
GROUP BY sid
ORDER BY cursor DESC;
-- sql 별 열린 커서
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;
1.3 연결되어 있는 OS 사용자 및 프로그램 조회[편집]
- V$SESSION
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’;
1.4 1시간 이상 유휴 상태인 세션[편집]
- V$SESSION
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;
1.5 프로세스 아이디를 이용하여 쿼리문 알아내기[편집]
- v$process
- v$session
- v$sqltext
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
1.6 Active Session 중 Idle Time이 긴 작업[편집]
- V$SESSION
- V$PROCESS
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;
1.7 DBUser 별로 Session 정보를 조회[편집]
- V$SESSION
- V$PROCESS
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;
1.8 Session별 사용 명령어[편집]
- V$SESSION SESS
- V$SESSTAT STAT
- V$STATNAME NAME
- V$PROCESS PROC
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;
1.9 사용자 session 중에서 2시간 이상 idle 상태가 지속되는 session을 kill[편집]
- V$SESSION
- V$PROCESS
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
2 프로세스 정보[편집]
2.1 Oracle Process의 정보[편집]
- V$SESSION
- V$PROCESS
- SYS.V_$SESS_IO
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;
3 = 오브젝트에 접속되어 있는 프로그램 조회[편집]
- V$SESSION
- V$ACCESS
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);
4 부하 발생 모니터링[편집]
5 = cpu를 많이 사용하는 쿼리문과 프로세스아이디,시리얼번호,머신 알아내기[편집]
- v$process
- v$session
- v$sqltext
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
5.1 롤백 세그먼트 경합 조회[편집]
- V$ROLLSTAT
- V$ROLLNAME
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;
5.2 Buffer Cache Hit Ratio[편집]
- V$SYSSTAT
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;
5.3 Library Cache Hit Ratio[편집]
SELECT (1-SUM (reloads)/SUM(pins))*100 "Library Cache Hit Ratio"
From V$LIBRARYCACHE;
5.4 Data Dictionary Cache Hit Ratio[편집]
V$ROWCACHE
SELECT (1-SUM(getmisses)/SUM(gets))*100 "Data Dictionary Hit Ratio"
FROM V$ROWCACHE;
6 DB 용량 관리[편집]
6.1 테이블 스페이스 사용량[편집]
- dba_data_files
- dba_free_space
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;
7 오라클 서버 스펙[편집]
7.1 오라클서버의 메모리[편집]
- v$sgastat
select * from v$sgastat;
select pool, sum(bytes) "SIZE"
from v$sgastat
where pool = 'shared pool'
group by pool
- 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'