"락 처리 절차"의 두 판 사이의 차이
DB CAFE
1번째 줄: | 1번째 줄: | ||
+ | 0. 락 모니터링 | ||
+ | |||
+ | <source lang=sql> | ||
+ | -- DROP VIEW RTIS_DBA.VW_DBA_LOCKS; | ||
+ | |||
+ | CREATE OR REPLACE FORCE VIEW VW_DBA_LOCKS | ||
+ | ( | ||
+ | LVL, | ||
+ | LCK_HOLD, | ||
+ | LCK_WAIT, | ||
+ | USERNAME, | ||
+ | OSUSER, | ||
+ | STATUS, | ||
+ | TYPE, | ||
+ | LOCK_TM, | ||
+ | HOLD, | ||
+ | OBJECT, | ||
+ | ID1, | ||
+ | ID2, | ||
+ | REQUEST, | ||
+ | EVENT, | ||
+ | MACHINE, | ||
+ | MODULE, | ||
+ | PROGRAM, | ||
+ | SQL_ID, | ||
+ | BLOCKING_SESSION, | ||
+ | CTIME, | ||
+ | OBJ#, | ||
+ | TYPE_NM, | ||
+ | ID1_TAG, | ||
+ | ID2_TAG, | ||
+ | SID, | ||
+ | SERIAL#, | ||
+ | CMD_KILL | ||
+ | ) | ||
+ | BEQUEATH DEFINER | ||
+ | AS | ||
+ | SELECT /*+ ordered */ | ||
+ | LEVEL | ||
+ | lvl, | ||
+ | NVL (s.blocking_session, s.sid) | ||
+ | lck_hold, | ||
+ | CASE WHEN s.blocking_session IS NOT NULL THEN s.sid END | ||
+ | lck_wait, | ||
+ | s.username, | ||
+ | s.osuser, | ||
+ | s.status, | ||
+ | l.TYPE | ||
+ | TYPE, | ||
+ | fn_sec_to_time (l.ctime) | ||
+ | lock_tm, | ||
+ | DECODE (lmode, | ||
+ | 0, 'NONE', | ||
+ | 1, 'NULL', | ||
+ | 2, 'row-S(SS)', | ||
+ | 3, 'row-X(SX)', | ||
+ | 4, 'share(S)', | ||
+ | 5, 'S/Row-X(SSX)', | ||
+ | 6, 'exclusive(X)') | ||
+ | HOLD, | ||
+ | o.name | ||
+ | object --, decode(l.id2, 0, null, trunc(l.id1 / power(2,16))) undo# | ||
+ | --, decode(l.id2, 0, null, bitand(l.id1, to_number('ffff', 'xxxx')) + 0 ) slot# | ||
+ | , | ||
+ | l.id1, | ||
+ | l.id2, | ||
+ | DECODE (l.request, | ||
+ | 0, 'NONE', | ||
+ | 1, 'NULL', | ||
+ | 2, 'row-S(SS)', | ||
+ | 3, 'row-X(SX)', | ||
+ | 4, 'share(S)', | ||
+ | 5, 'S/Row-X(SSX)', | ||
+ | 6, 'exclusive(X)') | ||
+ | REQUEST, | ||
+ | s.event, | ||
+ | s.machine, | ||
+ | s.module, | ||
+ | s.program, | ||
+ | s.sql_id, | ||
+ | s.blocking_session, | ||
+ | l.ctime, | ||
+ | o.obj#, | ||
+ | t.name | ||
+ | TYPE_NM, | ||
+ | t.id1_tag, | ||
+ | t.id2_tag, | ||
+ | s.sid, | ||
+ | s.serial#, | ||
+ | 'EXEC RDSADMIN.RDSADMIN_UTIL.KILL(' | ||
+ | || TO_CHAR (S.SID) | ||
+ | || ',' | ||
+ | || TO_CHAR (S.SERIAL#) | ||
+ | || ');' | ||
+ | CMD_KILL | ||
+ | FROM v$lock l, | ||
+ | v$session s, | ||
+ | sys.obj$ o, | ||
+ | v$lock_type t | ||
+ | WHERE 1 = 1 | ||
+ | AND s.TYPE != 'BACKGROUND' | ||
+ | -- and s.username = 'EP_APP' | ||
+ | -- and s.sid = 4179 | ||
+ | AND l.sid = s.sid | ||
+ | AND l.TYPE IN ('TM', 'TX', 'UL') | ||
+ | AND o.obj#(+) = DECODE (L.ID2, 0, L.ID1, -1) | ||
+ | AND t.TYPE = l.TYPE | ||
+ | CONNECT BY PRIOR s.sid = s.blocking_session | ||
+ | AND PRIOR l.id1 = l.id1 | ||
+ | AND PRIOR l.id2 = l.id2 | ||
+ | START WITH s.blocking_session IS NULL | ||
+ | ORDER SIBLINGS BY lock_tm DESC, lck_hold | ||
+ | --order by lck_hold, l.ctime desc, l.sid;; | ||
+ | ; | ||
+ | |||
+ | COMMENT ON TABLE RTIS_DBA.VW_DBA_LOCKS IS 'DBA_락_확인'; | ||
+ | |||
+ | COMMENT ON COLUMN RTIS_DBA.VW_DBA_LOCKS.LVL IS '레벨'; | ||
+ | |||
+ | </sql> | ||
1. lock 확인 | 1. lock 확인 | ||
-- lock 걸린 SERIAL_NO,SESSION_ID, OBJECT | -- lock 걸린 SERIAL_NO,SESSION_ID, OBJECT |
2019년 7월 18일 (목) 13:27 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
0. 락 모니터링
-- DROP VIEW RTIS_DBA.VW_DBA_LOCKS;
CREATE OR REPLACE FORCE VIEW VW_DBA_LOCKS
(
LVL,
LCK_HOLD,
LCK_WAIT,
USERNAME,
OSUSER,
STATUS,
TYPE,
LOCK_TM,
HOLD,
OBJECT,
ID1,
ID2,
REQUEST,
EVENT,
MACHINE,
MODULE,
PROGRAM,
SQL_ID,
BLOCKING_SESSION,
CTIME,
OBJ#,
TYPE_NM,
ID1_TAG,
ID2_TAG,
SID,
SERIAL#,
CMD_KILL
)
BEQUEATH DEFINER
AS
SELECT /*+ ordered */
LEVEL
lvl,
NVL (s.blocking_session, s.sid)
lck_hold,
CASE WHEN s.blocking_session IS NOT NULL THEN s.sid END
lck_wait,
s.username,
s.osuser,
s.status,
l.TYPE
TYPE,
fn_sec_to_time (l.ctime)
lock_tm,
DECODE (lmode,
0, 'NONE',
1, 'NULL',
2, 'row-S(SS)',
3, 'row-X(SX)',
4, 'share(S)',
5, 'S/Row-X(SSX)',
6, 'exclusive(X)')
HOLD,
o.name
object --, decode(l.id2, 0, null, trunc(l.id1 / power(2,16))) undo#
--, decode(l.id2, 0, null, bitand(l.id1, to_number('ffff', 'xxxx')) + 0 ) slot#
,
l.id1,
l.id2,
DECODE (l.request,
0, 'NONE',
1, 'NULL',
2, 'row-S(SS)',
3, 'row-X(SX)',
4, 'share(S)',
5, 'S/Row-X(SSX)',
6, 'exclusive(X)')
REQUEST,
s.event,
s.machine,
s.module,
s.program,
s.sql_id,
s.blocking_session,
l.ctime,
o.obj#,
t.name
TYPE_NM,
t.id1_tag,
t.id2_tag,
s.sid,
s.serial#,
'EXEC RDSADMIN.RDSADMIN_UTIL.KILL('
|| TO_CHAR (S.SID)
|| ','
|| TO_CHAR (S.SERIAL#)
|| ');'
CMD_KILL
FROM v$lock l,
v$session s,
sys.obj$ o,
v$lock_type t
WHERE 1 = 1
AND s.TYPE != 'BACKGROUND'
-- and s.username = 'EP_APP'
-- and s.sid = 4179
AND l.sid = s.sid
AND l.TYPE IN ('TM', 'TX', 'UL')
AND o.obj#(+) = DECODE (L.ID2, 0, L.ID1, -1)
AND t.TYPE = l.TYPE
CONNECT BY PRIOR s.sid = s.blocking_session
AND PRIOR l.id1 = l.id1
AND PRIOR l.id2 = l.id2
START WITH s.blocking_session IS NULL
ORDER SIBLINGS BY lock_tm DESC, lck_hold
--order by lck_hold, l.ctime desc, l.sid;;
;
COMMENT ON TABLE RTIS_DBA.VW_DBA_LOCKS IS 'DBA_락_확인';
COMMENT ON COLUMN RTIS_DBA.VW_DBA_LOCKS.LVL IS '레벨';
</sql>
1. lock 확인
-- lock 걸린 SERIAL_NO,SESSION_ID, OBJECT
<source lang=sql>
SELECT DISTINCT t1.session_id AS session_id
,t2.serial# AS serial_no
,t1.os_user_name AS os_user_name
,t1.oracle_username AS oracle_username
,t2.status AS status
,t3.object_name
,DECODE( locked_mode
,2, 'ROW SHARE'
,3, 'ROW EXCLUSIVE'
,4, 'SHARE'
,5, 'SHARE ROW EXCLUSIVE'
,6, 'EXCLUSIVE'
,'UNKNOWN'
) lock_mode
FROM v$locked_object t1
, v$session t2
, dba_objects t3
WHERE t1.session_id = t2.SID
AND t1.object_id = t3.object_id;
1-1.락 걸린 세션 과 락을 잡은 세션
SELECT S.USERNAME,
L.SID,
L.ID1,
TRUNC(L.ID1 / POWER(2, 16)) RBS,
BITAND(L.ID1, TO_NUMBER('ffff', 'xxxx')) + 0 SLOT,
L.ID2 SEQ,
L.LMODE,
L.REQUEST
FROM V$LOCK L,
V$SESSION S
WHERE L.SID = S.SID
AND L.TYPE = 'TX'
AND S.USERNAME = USER;
@ REQUEST 0 : 락을 보유
@ REQUEST 6 : 락으로 인해 대기중인 상태
1-2. 락 경합 사항 보기
- V$LOCK 셀프조인 모니터링
SELECT (SELECT USERNAME
FROM V$SESSION
WHERE SID = A.SID) BLOCKER,
A.SID,
' is blocking ',
(SELECT USERNAME
FROM V$SESSION
WHERE SID = B.SID) BLOCKER,
B.SID
FROM V$LOCK A,
V$LOCK B
WHERE A.ID1 = B.ID1
AND A.ID2 = B.ID2
AND A.BLOCK = 1 -- Lock을 잡고 있는 세션
AND B.REQUEST > 0 ; -- Lock으로 인해 대기하는 세션
2. session_id를 *SESSION_ID*에 넣어 락 sql 찾기
-- lock 걸린 sql
SELECT b.username username
,c.SID AS session_id
,c.owner object_owner
,c.OBJECT OBJECT
,b.lockwait
,a.sql_text SQL
,piece
FROM v$sqltext a
, v$session b
, v$access c
WHERE a.address = b.sql_address
AND a.hash_value = b.sql_hash_value
AND b.SID = c.SID
AND c.owner != 'SYS'
and c.SID = '&SESSION_ID'
ORDER BY b.username, c.SID, c.owner, c.OBJECT, piece;
3. 아래 쿼리 *SESSION_ID*,*SERIAL_NO* 부분에 위 1번 쿼리에서 확인한 session_id,serial_no을 사용하여 lock 걸린 session을 kill 한다.
-- lock 걸린 SESSION kill
alter system kill session 'SESSION_ID ,SERIAL_NO' ;
4. 3번의 alter system kill session을 사용하여도 죽지 않는 경우 아래 쿼리를 사용하여 PROCESS ID를 확인하여 unix에서 직접 PROCESS를 kill한다.
* 주의!!! 실수하기 쉽고 위험한 방법으로 lock걸린 내용을 정확히 알지 못할 경우 kill하지 말것!!!
-- lock 걸린 PROCESS ID 찾기
SELECT DISTINCT s.username "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'
-- unix에 로그인 하여 process kill
kill -9 프로세스아이디
5. 락걸린 테이블 찾아서 kill
-- 1) 헤당 테이블 찾기
SELECT * FROM v$lock
WHERE id1 = (SELECT object_id
FROM all_objects
WHERE owner ='오너명'
AND object_name ='테이블명'
)
-- 2) 죽이기
SELECT * FROM v$session WHERE sid = 찾은 sid값