데이터베이스/Oracle

Oracle DB LOKC 조회

베어그릴스 2022. 8. 26. 10:10
반응형

 

데이터베이스 lock걸린 쿼리를 찾아서 해당세션을 종료시키는 방법에 대하여 알아보도록 하자.

lock이 걸리게 되면 해당 작업이 끝날때까지 해당테이블 insert/update/delete/select가 되지 않기때문에 성능에 문제가 생기게 된다. 

 

db lock

1. lock 유무 확인

SELECT OBJECT_ID
     , SESSION_ID       -- SID
     , ORACLE_USERNAME
     , OS_USER_NAME
  FROM V$LOCKED_OBJECT
;


OBJECT_ID	SESSION_ID	ORACLE_USERNAME		OS_USER_NAME
-----------------------------------------------------------------
163990		401		SCOTT			js
163966		401		SCOTT			js

이걸로는 자세한 내용을 아직 알기 어렵습니다.

그러면 다음으로 해당 sid 와 serial 번호로 락걸린 object name 을 확인해보겠습니다.

2. 해당 sid 와 serial 번호로 락걸린 object name 을 확인

SELECT A.SID
     , A.SERIAL#
     , object_name
     , A.SID || ', ' || A.SERIAL# AS KILL_TASK
  FROM V$SESSION A
 INNER JOIN V$LOCK B
    ON A.SID = B.SID
 INNER JOIN DBA_OBJECTS C
    ON B.ID1 = C.OBJECT_ID
 WHERE B.TYPE  = 'TM'
 ;
   

SID	SERIAL#	OBJECT_NAME	KILL_TASK
------------------------------------------------------------
401	12761	EMP		401, 12761
401	12761	EMP		401, 12761

자 이제 뭔가 OBJECT_NAME 이 보이면서 이 TABLE이 LOCK 이 걸렸구나 라는것을 확인할 수 있습니다.

그럼 LOCK 이 걸린것은 확인했고 LOCK 을 해제하려면 어떻게 해야 할까요?

정답은 마지막에 SID 와 SERIAL# 로 조회된 NUMBER 로 해당되는 SESSION 을 KILL 시키면 됩니다.          

3. sid 와 시리얼 번호로 세션 해제

 ALTER SYSTEM KILL SESSION '401, 12761';

뒤에 입력된 숫자인 401, 12761 는 위에서 말씀드린 SID와 SERIAL# 에 대한 값입니다.

편하게 KILL_TASK 라는 값을 복사해서 사용할 수 있게 미리 만들어주었습니다.

 ( ALTER 명령은 별도의 COMMIT 이 필요없는거 다들 아시리라 생각합니다. )

이 단계까지 왔으면 LOCK 걸린 테이블의 SESSION 은 해제되었을 것이라고 봅니다.

그런데 위에서 조회된 세션이 단순히 LOCK 걸린것이 아니라 아직 작업중인 SQL 일수도 있다는 생각이 들수도 있습니다.

그럴때는 LOCK 을 발생시킨 SQL 이 뭔지 확인해볼수 있습니다.

4. 락 발생 사용자 및 OBJECT 조회 + 어떤 sql 를 실행중하여 lock 을 걸고 있는지 확인

SELECT DISTINCT T1.SESSION_ID
     , T2.SERIAL#
     , T4.OBJECT_NAME
     , T2.MACHINE
     , T2.TERMINAL
     , T2.PROGRAM
     , T3.ADDRESS
     , T3.PIECE
     , T3.SQL_TEXT
  FROM V$LOCKED_OBJECT T1
     , V$SESSION T2
     , V$SQLTEXT T3
     , DBA_OBJECTS T4
 WHERE 1=1
   AND T1.SESSION_ID = T2.SID
   AND T1.OBJECT_ID = T4.OBJECT_ID
   AND T2.SQL_ADDRESS = T3.ADDRESS
   ORDER BY T3.ADDRESS, T3.PIECE
   ;
   

SESSION_ID	SERIAL#	OBJECT_NAME	MACHINE	    	TERMINAL	PROGRAM		    	ADDRESS		PIECE	SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------
401	    	12761	EMP	    	machine_com1	unknown		JDBC Thin Client	230134658	0    	INSERT /*+ APPEND */ INTO EMP
401	    	12761	EMP	    	machine_com1	unknown		JDBC Thin Client	230134658	1    	, EMPNO
401	    	12761	EMP	    	machine_com1	unknown		JDBC Thin Client	230134658	2    	, EMPNM
401	    	12761	EMP	    	machine_com1	unknown		JDBC Thin Client	230134658	3    	, DEPTNO
반응형