"ORACLE 모니터링"의 두 판 사이의 차이
DB CAFE
1번째 줄: | 1번째 줄: | ||
+ | <big>DB 모니터링</big> | ||
− | + | ---- | |
+ | == 세션/접속사용자 정보 == | ||
− | ---- | + | === 세션 죽이기(SID,SERAIL#) === |
+ | <source lang="sql"> | ||
+ | ALTER SYSTEM KILL SESSION '8,4093' | ||
+ | |||
+ | === 오라클 세션과 관련된 테이블 === | ||
+ | * v$session | ||
+ | <source lang="sql"> | ||
+ | select count(*) | ||
+ | from v$session | ||
+ | where machine ='머신이름' | ||
+ | and schemaname ='스키마이름' | ||
+ | </source> | ||
+ | |||
+ | |||
+ | === 현재 커서 수 확인 === | ||
+ | * V$OPEN_CURSOR | ||
+ | * v$session_wait | ||
+ | * v$transaction | ||
+ | * v$session_wait | ||
+ | |||
+ | <source lang="sql"> | ||
+ | -- 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; | ||
+ | </source> | ||
− | |||
− | |||
+ | === 연결되어 있는 OS 사용자 및 프로그램 조회 === | ||
+ | * V$SESSION | ||
<source lang="sql"> | <source lang="sql"> | ||
17번째 줄: | 61번째 줄: | ||
, STATUS | , STATUS | ||
, TO_CHAR(LOGON_TIME, 'YYYY/MM/DD HH:MI') AS LOGON_TIME | , TO_CHAR(LOGON_TIME, 'YYYY/MM/DD HH:MI') AS LOGON_TIME | ||
+ | FROM V$SESSION | ||
+ | WHERE TYPE!= ‘BACKGROUND’ AND STATUS = ‘ACTIVE’; </source> | ||
− | + | === 1시간 이상 유휴 상태인 세션 === | |
− | + | * V$SESSION | |
− | |||
− | |||
− | |||
− | |||
− | |||
+ | <source lang="sql"> | ||
+ | SELECT SID | ||
, SERIAL# | , SERIAL# | ||
, USERNAME | , USERNAME | ||
, TRUNC(LAST_CALL_ET / 3600, 2) || ' HR' LAST_CALL_ET | , TRUNC(LAST_CALL_ET / 3600, 2) || ' HR' LAST_CALL_ET | ||
+ | FROM V$SESSION | ||
+ | WHERE LAST_CALL_ET > 3600 | ||
+ | AND USERNAME IS NOT NULL; | ||
+ | </source> | ||
− | + | === 프로세스 아이디를 이용하여 쿼리문 알아내기 === | |
− | + | * v$process | |
− | # | + | * v$session |
− | + | * v$sqltext | |
+ | <source lang="sql"> | ||
+ | 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 | ||
+ | </source> | ||
+ | === Active Session 중 Idle Time이 긴 작업 === | ||
+ | * V$SESSION | ||
+ | * V$PROCESS | ||
<source lang="sql"> | <source lang="sql"> | ||
− | |||
SELECT VS.SID || ',' || VS.SERIAL# " SID" | SELECT VS.SID || ',' || VS.SERIAL# " SID" | ||
− | |||
, VP.SPID | , VP.SPID | ||
, VS.MACHINE | , VS.MACHINE | ||
47번째 줄: | 108번째 줄: | ||
, TO_CHAR(VS.LOGON_TIME, 'MM/DD HH24:MI') LOGIN_TIME | , TO_CHAR(VS.LOGON_TIME, 'MM/DD HH24:MI') LOGIN_TIME | ||
, ROUND(VS.LAST_CALL_ET / 60) "IDLE" | , ROUND(VS.LAST_CALL_ET / 60) "IDLE" | ||
− | |||
FROM V$SESSION VS | FROM V$SESSION VS | ||
− | |||
, V$PROCESS VP | , 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; | ||
+ | </source> | ||
− | + | === DBUser 별로 Session 정보를 조회 === | |
− | + | * V$SESSION | |
− | + | * V$PROCESS | |
− | |||
− | |||
− | |||
<source lang="sql"> | <source lang="sql"> | ||
− | |||
SELECT S.USERNAME | SELECT S.USERNAME | ||
− | |||
, S.SID | , S.SID | ||
, S.SERIAL# | , S.SERIAL# | ||
70번째 줄: | 128번째 줄: | ||
, TO_CHAR(S.LOGON_TIME, 'MM/DD HH24:MI') "LOGON_TIME" | , TO_CHAR(S.LOGON_TIME, 'MM/DD HH24:MI') "LOGON_TIME" | ||
, ROUND(S.LAST_CALL_ET / 60) "IDLE" | , ROUND(S.LAST_CALL_ET / 60) "IDLE" | ||
− | |||
FROM V$SESSION S | FROM V$SESSION S | ||
− | |||
, V$PROCESS P | , V$PROCESS P | ||
+ | WHERE S.PADDR = P.ADDR | ||
+ | AND S.USERNAME LIKE UPPER('&DBUSER%') | ||
+ | ORDER BY 9; | ||
+ | </source> | ||
− | + | === Session별 사용 명령어 === | |
− | + | * V$SESSION SESS | |
− | + | * V$SESSTAT STAT | |
− | + | * V$STATNAME NAME | |
− | + | * V$PROCESS PROC | |
<source lang="sql"> | <source lang="sql"> | ||
− | |||
SELECT SESS.SID | SELECT SESS.SID | ||
− | |||
, SESS.SERIAL# | , SESS.SERIAL# | ||
, SUBSTR(SESS.USERNAME, 1, 10) "USER NAME" | , SUBSTR(SESS.USERNAME, 1, 10) "USER NAME" | ||
175번째 줄: | 233번째 줄: | ||
, PROC.SPID "S.PROC" | , PROC.SPID "S.PROC" | ||
, TO_CHAR(SESS.LOGON_TIME, 'YYYY-MM-DD HH24:MI') | , TO_CHAR(SESS.LOGON_TIME, 'YYYY-MM-DD HH24:MI') | ||
− | |||
FROM V$SESSION SESS | FROM V$SESSION SESS | ||
− | |||
, V$SESSTAT STAT | , V$SESSTAT STAT | ||
, V$STATNAME NAME | , V$STATNAME NAME | ||
, V$PROCESS PROC | , 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; | ||
+ | </source> | ||
− | + | === 사용자 session 중에서 2시간 이상 idle 상태가 지속되는 session을 kill === | |
− | + | * V$SESSION | |
− | + | * V$PROCESS | |
− | |||
− | |||
− | |||
<source lang="sql"> SET PAGESIZE 0 SPOOL KILLIDLE3.SQL | <source lang="sql"> SET PAGESIZE 0 SPOOL KILLIDLE3.SQL | ||
− | + | SELECT DISTINCT '!KILL -9 ' || B.SPID, 'ALTER SYSTEM KILL SESSION '''|| A.SID || ',' || A.SERIAL# || ''';' | |
− | SELECT DISTINCT '!KILL -9 ' || B.SPID, 'ALTER SYSTEM KILL SESSION '''|| A.SID || ',' || A.SERIAL# || ''';' FROM V$SESSION A | + | FROM V$SESSION A |
− | |||
, V$PROCESS B | , V$PROCESS B | ||
− | |||
WHERE A.PADDR IN (SELECT S.PADDR | 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 | ||
+ | </source> | ||
+ | ---- | ||
+ | == 프로세스 정보 == | ||
+ | === Oracle Process의 정보 === | ||
+ | * V$SESSION | ||
+ | * V$PROCESS | ||
+ | * SYS.V_$SESS_IO | ||
− | + | <source lang="sql"> | |
− | + | SELECT S.STATUS "STATUS" | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | <source lang="sql"> SELECT S.STATUS "STATUS" | ||
− | |||
, S.SERIAL# "SERIAL#" | , S.SERIAL# "SERIAL#" | ||
, S.TYPE "TYPE" | , S.TYPE "TYPE" | ||
236번째 줄: | 294번째 줄: | ||
, S.SQL_HASH_VALUE "SQL HASH" | , S.SQL_HASH_VALUE "SQL HASH" | ||
, S.ACTION | , S.ACTION | ||
− | |||
FROM V$SESSION S | FROM V$SESSION S | ||
− | |||
, V$PROCESS P | , V$PROCESS P | ||
, SYS.V_$SESS_IO SI | , 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; | ||
+ | </source> | ||
− | + | === 오브젝트에 접속되어 있는 프로그램 조회 == | |
− | + | * V$SESSION | |
− | + | * V$ACCESS | |
− | |||
− | |||
− | |||
<source lang="sql"> | <source lang="sql"> | ||
− | + | SELECT SUBSTR(B.OBJECT, 1, 15) AS OBJECT, SUBSTR(A.PROGRAM, 1, 15) AS PROGRAM, COUNT(*) AS CNT | |
− | SELECT SUBSTR(B.OBJECT, 1, 15) AS OBJECT, SUBSTR(A.PROGRAM, 1, 15) AS PROGRAM, COUNT(*) AS CNT FROM V$SESSION A | + | FROM V$SESSION A |
− | |||
, V$ACCESS B | , 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); | ||
+ | </source> | ||
− | + | ---- | |
− | + | == 부하 발생 모니터링 == | |
− | + | === cpu를 많이 사용하는 쿼리문과 프로세스아이디,시리얼번호,머신 알아내기 == | |
+ | * v$process | ||
+ | * v$session | ||
+ | * v$sqltext | ||
+ | <source lang="sql"> | ||
+ | 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 | ||
+ | </source> | ||
+ | |||
+ | === 롤백 세그먼트 경합 조회 === | ||
+ | * V$ROLLSTAT | ||
+ | * V$ROLLNAME | ||
<source lang="sql"> | <source lang="sql"> | ||
− | |||
SELECT NAME T0 | SELECT NAME T0 | ||
− | |||
, GETS T1 | , GETS T1 | ||
, WAITS T2 | , WAITS T2 | ||
270번째 줄: | 355번째 줄: | ||
, SHRINKS T5 | , SHRINKS T5 | ||
, EXTENDS T6 | , EXTENDS T6 | ||
− | |||
FROM V$ROLLSTAT | FROM V$ROLLSTAT | ||
− | |||
, V$ROLLNAME | , V$ROLLNAME | ||
− | + | WHERE V$ROLLSTAT.USN = V$ROLLNAME.USN; | |
− | WHERE V$ROLLSTAT.USN = V$ROLLNAME.USN; | ||
</source> | </source> | ||
− | |||
− | + | === Buffer Cache Hit Ratio === | |
− | + | * V$SYSSTAT | |
− | + | <source lang="sql"> | |
− | + | 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" | |
− | <source lang="sql"> SELECT | + | FROM V$SYSSTAT; |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
</source> | </source> | ||
− | |||
− | |||
− | + | === Library Cache Hit Ratio === | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
<source lang="sql"> | <source lang="sql"> | ||
SELECT (1-SUM (reloads)/SUM(pins))*100 "Library Cache Hit Ratio" | SELECT (1-SUM (reloads)/SUM(pins))*100 "Library Cache Hit Ratio" | ||
307번째 줄: | 373번째 줄: | ||
</source> | </source> | ||
− | + | === Data Dictionary Cache Hit Ratio === | |
+ | V$ROWCACHE | ||
<source lang="sql"> | <source lang="sql"> | ||
SELECT (1-SUM(getmisses)/SUM(gets))*100 "Data Dictionary Hit Ratio" | SELECT (1-SUM(getmisses)/SUM(gets))*100 "Data Dictionary Hit Ratio" | ||
− | FROM V$ROWCACHE; | + | FROM V$ROWCACHE; |
</source> | </source> | ||
− | + | ---- | |
− | + | == DB 용량 관리 == | |
+ | === 테이블 스페이스 사용량 === | ||
+ | * dba_data_files | ||
+ | * dba_free_space | ||
<source lang="sql"> | <source lang="sql"> | ||
SELECT a.tablespace_name, | SELECT a.tablespace_name, | ||
333번째 줄: | 403번째 줄: | ||
</source> | </source> | ||
− | + | == 오라클 서버 스펙 == | |
+ | === 오라클서버의 메모리 === | ||
+ | * v$sgastat | ||
<source lang="sql"> | <source lang="sql"> | ||
− | select * from v$sgastat | + | select * from v$sgastat; |
select pool, sum(bytes) "SIZE" | select pool, sum(bytes) "SIZE" | ||
− | from v$sgastat | + | from v$sgastat |
− | where pool = 'shared pool' | + | where pool = 'shared pool' |
− | group by pool | + | group by pool |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
</source> | </source> | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
492번째 줄: | 490번째 줄: | ||
4.connect system/sys | 4.connect system/sys | ||
5.ALTER SYSTEM KILL SESSION '137,1723' | 5.ALTER SYSTEM KILL SESSION '137,1723' | ||
+ | </source> | ||
+ | |||
+ | == 테이블 스페이스 == | ||
+ | === System 테이블스페이스에 비시스템 세그먼트 조회 === | ||
+ | * DBA_SEGMENTS | ||
+ | <source lang="sql"> | ||
+ | SELECT OWNER | ||
+ | , SEGMENT_NAME | ||
+ | , SEGMENT_TYPE | ||
+ | , TABLESPACE_NAME | ||
+ | FROM DBA_SEGMENTS | ||
+ | WHERE OWNER NOT IN ('SYS', 'SYSTEM') | ||
+ | AND TABLESPACE_NAME = 'SYSTEM'; | ||
</source> | </source> |
2019년 4월 2일 (화) 13:20 판
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'