질문 : TX-LOCK 이나 Library Cache Lock/Pin 발생 시, 빠르게 파악 및 해소하고 싶습니다.
답변 : GV$SESSION.FINAL_BLOCKING_SESSION_STATUS='VALID' 시,
GV$SESSION.FINAL_BLOCKING_INSTANCE 와 GV$SESSION.FINAL_BLOCKING_SESSION
통해서 빠르게 파악 해보자
Hang Manager Trace 를 통한 파악 ( 지난 시간 이력 파악 시, 실시간이 파악은 SQL 을 통해서 )
2022.11.19 - [Oracle/Oh Oracle ] - Hang Manager(HM)
Database Reference
V$SESSION displays session information for each current session.
docs.oracle.com
CASE 1 ) TX-LOCK
: 다수 세션의 Update Lock 파익 및 해소
CREATE TABLE CHECK_LOCK ( A1 NUMBER, A2 VARCHAR2(300)) ;
INSERT INTO CHECK_LOCK VALUES ( 1, 'A') ;
COMMIT ;
-- SESSION 1 << 이 세션으로 인핸서 TX-LOCK 발생함
UPDATE CHECK_LOCK SET A2='B'
WHERE A1= 1 ;
-- SESSION 2
UPDATE CHECK_LOCK SET A2='C'
WHERE A1= 1 ;
-- SESSION 3
UPDATE CHECK_LOCK SET A2='D'
WHERE A1= 1 ;
SQL> set linesize 180
SQL> col inst for 9999
SQL> col inst for a12
SQL> col username for a12
SQL> col sid for 999999
SQL> col sid2 for a14
SQL> col status for a14
SQL> col state for a14
SQL> col FINAL_BLOCKER_INFO for a60
SQL> SELECT level,
LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
s.status
FROM gv$session s
WHERE level > 1
OR EXISTS ( SELECT 1
FROM gv$session
WHERE BLOCKING_INSTANCE = S.INST_ID
AND blocking_session = s.sid
) CONNECT BY PRIOR s.sid = s.blocking_session AND S.INST_ID = S.BLOCKING_INSTANCE
START WITH s.blocking_session IS NULL;
LEVEL USERNAME OSUSER SID SERIAL# STATUS
---------- ------------ -------------------- ------- ---------- --------------
1 SCOTT TGHOME 37 36717 INACTIVE << FINAL LOCK HOLDER
2 SCOTT TGHOME 36 51665 ACTIVE << WAITER
2 SCOTT TGHOME 292 49124 ACTIVE << WAITER
SQL> SELECT s.inst_id as Inst
,substr(s.username,1,12 ) as username
,to_char(s.sid)||','||to_char(s.serial# ) as sid2
,status
,state
,s.sql_id
,S.EVENT
,last_call_et as lce
, case when s.FINAL_BLOCKING_SESSION_STATUS ='VALID' THEN
( SELECT --'Final Blocker Info :'||SS.INST_ID||' : '||SS.SID||'.'||SS.SERIAL#||
'ALTER SYSTEM KILL SESSION '''||SS.sid||','||SS.serial#||',@'||SS.INST_ID||''' IMMEDIATE;' AS KILL_IMMEIDATE
FROM GV$SESSION SS
WHERE SS.INST_ID = s.FINAL_BLOCKING_INSTANCE
AND SS.SID = s.FINAL_BLOCKING_SESSION )
END AS FINAL_BLOCKER_INFO
FROM gv$session s
,gv$process p
WHERE s.paddr = p.addr
AND s.inst_id = p.inst_id
AND s.username in ('SCOTT' )
AND s.event not in ('queue messages','pipe get','jobq slave wait','Streams AQ: waiting for messages in the queue')
AND s.username is not null
AND type ='USER'
order by lce desc ;
INST USERNAME SID2 STATUS STATE SQL_ID EVENT LCE FINAL_BLOCKER_INFO
----- ------------ -------------- -------------- -------------- ------------- ------------------------------ ---------- ------------------------------------------------------------
1 SCOTT 37,36717 INACTIVE WAITING SQL*Net message from client 53
1 SCOTT 36,51665 ACTIVE WAITING 7q04qvxh8vgw1 enq: TX - row lock contention 48 ALTER SYSTEM KILL SESSION '37,36717,@1' IMMEDIATE;
<< FINAL LOCK HOLDER
1 SCOTT 292,49124 ACTIVE WAITING 18n6djbras9y3 enq: TX - row lock contention 43 ALTER SYSTEM KILL SESSION '37,36717,@1' IMMEDIATE;
<< FINAL LOCK HOLDER
1 SCOTT 237,59754 ACTIVE WAITED SHORT T 8qqsv498fyfs2 PGA memory operation 0
IME
SQL>
SQL> ALTER SYSTEM KILL SESSION '37,36717,@1' IMMEDIATE;
System altered.
SQL> SELECT level,
LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
s.status
FROM gv$session s
WHERE level > 1
OR EXISTS ( SELECT 1
FROM gv$session
WHERE BLOCKING_INSTANCE = S.INST_ID
AND blocking_session = s.sid
) CONNECT BY PRIOR s.sid = s.blocking_session AND S.INST_ID = S.BLOCKING_INSTANCE
START WITH s.blocking_session IS NULL;
---------- ------------ -------------------- ------- ---------- --------------
1 SCOTT TGHOME 36 51665 INACTIVE << FINAL LOCK HOLDER
2 SCOTT TGHOME 292 49124 ACTIVE
SQL> SELECT s.inst_id as Inst
,substr(s.username,1,12 ) as username
,to_char(s.sid)||','||to_char(s.serial# ) as sid2
,status
,state
,s.sql_id
,S.EVENT
,last_call_et as lce
, case when s.FINAL_BLOCKING_SESSION_STATUS ='VALID' THEN
( SELECT --'Final Blocker Info :'||SS.INST_ID||' : '||SS.SID||'.'||SS.SERIAL#||
'ALTER SYSTEM KILL SESSION '''||SS.sid||','||SS.serial#||',@'||SS.INST_ID||''' IMMEDIATE;' AS KILL_IMMEIDATE
FROM GV$SESSION SS
WHERE SS.INST_ID = s.FINAL_BLOCKING_INSTANCE
AND SS.SID = s.FINAL_BLOCKING_SESSION )
END AS FINAL_BLOCKER_INFO
FROM gv$session s
,gv$process p
WHERE s.paddr = p.addr
AND s.inst_id = p.inst_id
AND s.username in ('SCOTT' )
AND s.event not in ('queue messages','pipe get','jobq slave wait','Streams AQ: waiting for messages in the queue')
AND s.username is not null
AND type ='USER'
order by lce desc ;
INST USERNAME SID2 STATUS STATE SQL_ID EVENT LCE FINAL_BLOCKER_INFO
----- ------------ -------------- -------------- -------------- ------------- ------------------------------ ---------- ------------------------------------------------------------
1 SCOTT 292,49124 ACTIVE WAITING 18n6djbras9y3 enq: TX - row lock contention 150 ALTER SYSTEM KILL SESSION '36,51665,@1' IMMEDIATE;
1 SCOTT 36,51665 INACTIVE WAITING SQL*Net message from client 68
1 SCOTT 237,59754 ACTIVE WAITED SHORT T fdjqwvxhp9x26 PGA memory operation 0
IME
SQL>
Hang Manager Trace 를 통해서 파악 해보기
vi `ls -lrt *dia*base*.trc | tail -n 1 | awk '{print $9}'`
: Tree 구조에서 반대로 기술, 즉 Leaf -> Branch -> Root ( Final Blocker ) 순으로 기술됨
CASE 1 ) TX-LOCK
*** 2022-12-31T16:24:12.221561+09:00
HM: Hung Sessions (local detect) - output local chains
===============================================================================
Non-intersecting chains:
-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 1 (oracle19.oracle19)
os id: 9135
process id: 50, oracle@oel7
session id: 36
session serial #: 49742
module name: 0 (Orange for ORACLE DBA)
}
is waiting for 'enq: TX - row lock contention' with wait info:
{
p1: 'name|mode'=0x54580006
p2: 'usn<<16 | slot'=0x80014
p3: 'sequence'=0x2230
time in wait: 1 min 36 sec
timeout after: never
wait id: 64
blocking: 0 sessions
current sql_id: 2063636191
wait history:
* time between current wait and wait #1: 0.002302 sec
1. event: 'SQL*Net message from client'
time waited: 0.713088 sec
wait id: 63 p1: 'driver id'=0x54435000
p2: '#bytes'=0x1
* time between wait #1 and #2: 0.000008 sec
2. event: 'SQL*Net message to client'
time waited: 0.000001 sec
wait id: 62 p1: 'driver id'=0x54435000
p2: '#bytes'=0x1
* time between wait #2 and #3: 0.000001 sec
3. event: 'SQL*Net break/reset to client'
time waited: 0.000178 sec
wait id: 61 p1: 'driver id'=0x54435000
p2: 'break?'=0x0
}
and is blocked by
=> Oracle session identified by:
{
instance: 1 (oracle19.oracle19)
os id: 9125
process id: 48, oracle@oel7
session id: 37
session serial #: 38779
module name: 0 (Orange for ORACLE DBA)
}
which is waiting for 'SQL*Net message from client' with wait info:
{
p1: 'driver id'=0x54435000
p2: '#bytes'=0x1
time in wait: 1 min 26 sec
timeout after: never
wait id: 170
blocking: 1 session
current sql_id: 1208101921
wait history:
* time between current wait and wait #1: 0.000003 sec
1. event: 'SQL*Net message to client'
time waited: 0.000001 sec
wait id: 169 p1: 'driver id'=0x54435000
p2: '#bytes'=0x1
* time between wait #1 and #2: 0.000020 sec
2. event: 'SQL*Net message from client'
time waited: 0.000773 sec
wait id: 168 p1: 'driver id'=0x54435000
p2: '#bytes'=0x1
* time between wait #2 and #3: 0.000260 sec
3. event: 'SQL*Net message to client'
time waited: 0.000002 sec
wait id: 167 p1: 'driver id'=0x54435000
p2: '#bytes'=0x1
}
Chain 1 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention'
Chain 1 Signature Hash: 0x38c48850
-------------------------------------------------------------------------------
CASE 2 ) Libary Cache Pin (컴파일 시) / lock ( 프로시져 수행 시)
: 프로시져 장시간 수행 -> 컴파일 수행 대기 -> 프로시져 수행 대기 파악 및 해소
--1. 프로시져 생성
create or replace procedure sp_lock_test1
is
begin
-- run by sys
-- grant execute on dbms_lock to scott;
null ;
dbms_lock.sleep(180);
end ;
--2. 세션1 , 프로시셔 수행
exec sp_lock_test1
--3. 세션2. 프로시져 컴파일
create or replace procedure sp_lock_test1
is
begin
-- run by sys
-- grant execute on dbms_lock to scott;
null ;
-- dbms_lock.sleep(180);
dbms_lock.sleep(1800);
end ;
--4. 세션 3, 프로시져 수행
exec sp_lock_test1
--5. 결과 모니터링 및 조치
SQL> SELECT level,
LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
s.status
FROM gv$session s
WHERE level > 1
OR EXISTS ( SELECT 1
FROM gv$session
WHERE BLOCKING_INSTANCE = S.INST_ID
AND blocking_session = s.sid
) CONNECT BY PRIOR s.sid = s.blocking_session AND S.INST_ID = S.BLOCKING_INSTANCE
START WITH s.blocking_session IS NULL;
LEVEL USERNAME OSUSER SID SERIAL# STATUS
---------- ------------ -------------------- ------- ---------- --------------
1 SCOTT TGHOME 20 22760 ACTIVE <<< 프로시져 수행 중 세션
2 SCOTT TGHOME 292 49124 ACTIVE <<< 프로시져 컴파일 세션
3 SCOTT TGHOME 36 51665 ACTIVE <<< 프로시져 수행 시도 세션 ( 컴파일 시도 이후 )
SQL> SELECT s.inst_id as Inst
,substr(s.username,1,12 ) as username
,to_char(s.sid)||','||to_char(s.serial# ) as sid2
,status
,state
,s.sql_id
,S.EVENT
,last_call_et as lce
, case when s.FINAL_BLOCKING_SESSION_STATUS ='VALID' THEN
( SELECT --'Final Blocker Info :'||SS.INST_ID||' : '||SS.SID||'.'||SS.SERIAL#||
'ALTER SYSTEM KILL SESSION '''||SS.sid||','||SS.serial#||',@'||SS.INST_ID||''' IMMEDIATE;' AS KILL_IMMEIDATE
FROM GV$SESSION SS
WHERE SS.INST_ID = s.FINAL_BLOCKING_INSTANCE
AND SS.SID = s.FINAL_BLOCKING_SESSION )
END AS FINAL_BLOCKER_INFO
FROM gv$session s
,gv$process p
WHERE s.paddr = p.addr
AND s.inst_id = p.inst_id
AND s.username in ('SCOTT' )
AND s.event not in ('queue messages','pipe get','jobq slave wait','Streams AQ: waiting for messages in the queue')
AND s.username is not null
AND type ='USER'
order by lce desc ;
INST USERNAME SID2 STATUS STATE SQL_ID EVENT LCE FINAL_BLOCKER_INFO
----- ------------ -------------- -------------- -------------- ------------- ------------------------------ ---------- ------------------------------------------------------------
1 SCOTT 20,22760 ACTIVE WAITING 4059sqacczqbs PL/SQL lock timer 96
<<< 프로시져 수행 중 세션
1 SCOTT 292,49124 ACTIVE WAITING 0aumrmvtht58v library cache pin 85 ALTER SYSTEM KILL SESSION '20,22760,@1' IMMEDIATE;
<<< 프로시져 컴파일 세션
1 SCOTT 36,51665 ACTIVE WAITING 4059sqacczqbs library cache lock 78 ALTER SYSTEM KILL SESSION '20,22760,@1' IMMEDIATE;
<<< 프로시져 수행 시도 세션 ( 컴파일 시도 이후 )
1 SCOTT 237,59754 ACTIVE WAITED SHORT T 1r1aaq2dfnj91 SQL*Net message to client 0
IME
SQL> ALTER SYSTEM KILL SESSION '20,22760,@1' IMMEDIATE;
System altered.
--
-- <<< 프로시져 수행 중 세션 <<< 세션 kill 조치 됨
-- <<< 프로시져 컴파일 세션 <<< 바로 컴파일 됨
-- <<< 프로시져 수행 시도 세션 ( 컴파일 시도 이후 ) <<< 컴파일 후, 바로 수행 진행
SQL> SELECT level,
LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
s.status
FROM gv$session s
WHERE level > 1
OR EXISTS ( SELECT 1
FROM gv$session
WHERE BLOCKING_INSTANCE = S.INST_ID
AND blocking_session = s.sid
) CONNECT BY PRIOR s.sid = s.blocking_session AND S.INST_ID = S.BLOCKING_INSTANCE
START WITH s.blocking_session IS NULL;
no rows selected
SQL> SELECT s.inst_id as Inst
,substr(s.username,1,12 ) as username
,to_char(s.sid)||','||to_char(s.serial# ) as sid2
,status
,state
,s.sql_id
,S.EVENT
,last_call_et as lce
, case when s.FINAL_BLOCKING_SESSION_STATUS ='VALID' THEN
( SELECT --'Final Blocker Info :'||SS.INST_ID||' : '||SS.SID||'.'||SS.SERIAL#||
'ALTER SYSTEM KILL SESSION '''||SS.sid||','||SS.serial#||',@'||SS.INST_ID||''' IMMEDIATE;' AS KILL_IMMEIDATE
FROM GV$SESSION SS
WHERE SS.INST_ID = s.FINAL_BLOCKING_INSTANCE
AND SS.SID = s.FINAL_BLOCKING_SESSION )
END AS FINAL_BLOCKER_INFO
FROM gv$session s
,gv$process p
WHERE s.paddr = p.addr
AND s.inst_id = p.inst_id
AND s.username in ('SCOTT' )
AND s.event not in ('queue messages','pipe get','jobq slave wait','Streams AQ: waiting for messages in the queue')
AND s.username is not null
AND type ='USER'
order by lce desc ;
INST USERNAME SID2 STATUS STATE SQL_ID EVENT LCE FINAL_BLOCKER_INFO
----- ------------ -------------- -------------- -------------- ------------- ------------------------------ ---------- ------------------------------------------------------------
1 SCOTT 36,51665 ACTIVE WAITING 4059sqacczqbs PL/SQL lock timer 120
1 SCOTT 292,49124 INACTIVE WAITING SQL*Net message from client 35
1 SCOTT 237,59754 ACTIVE WAITED SHORT T 1r1aaq2dfnj91 SQL*Net message to client 0
SQL>
Hang Manager Trace 를 통해서 파악 해보기
vi `ls -lrt *dia*base*.trc | tail -n 1 | awk '{print $9}'`
CASE 2 ) Libary Cache Pin
: Tree 구조에서 반대로 기술, 즉 Leaf -> Branch -> Root ( Final Blocker ) 순으로 기술됨
*** 2022-12-31T16:32:13.822659+09:00
HM: Hung Sessions (local detect) - output local chains
===============================================================================
Non-intersecting chains:
-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 1 (oracle19.oracle19)
os id: 9252
process id: 50, oracle@oel7
session id: 36
session serial #: 51665
module name: 0 (Orange for ORACLE DBA)
}
is waiting for 'library cache lock' with wait info:
{
p1: 'handle address'=0x617ddb60
p2: 'lock address'=0x618be040
p3: '100*mode+namespace'=0x67f000010002
Name: SCOTT.SP_LOCK_TEST1 [Nsp: 1 Typ: 7 Id: 26608]
time in wait: 1 min 20 sec
timeout after: 13 min 39 sec
wait id: 69
blocking: 0 sessions
current sql_id: 1075132594
wait history:
* time between current wait and wait #1: 0.000196 sec
1. event: 'SQL*Net message from client'
time waited: 3 min 18 sec
wait id: 68 p1: 'driver id'=0x54435000
p2: '#bytes'=0x1
* time between wait #1 and #2: 0.000001 sec
2. event: 'SQL*Net message to client'
time waited: 0.000000 sec
wait id: 67 p1: 'driver id'=0x54435000
p2: '#bytes'=0x1
* time between wait #2 and #3: 0.000053 sec
3. event: 'log file sync'
time waited: 0.000630 sec
wait id: 66 p1: 'buffer#'=0x141
p2: 'sync scn'=0x8b49cb
}
and is blocked by
=> Oracle session identified by:
{
instance: 1 (oracle19.oracle19)
os id: 9259
process id: 51, oracle@oel7
session id: 292
session serial #: 49124
module name: 0 (Orange for ORACLE DBA)
}
which is waiting for 'library cache pin' with wait info:
{
p1: 'handle address'=0x617ddb60
p2: 'pin address'=0x6a2f11f8
p3: '100*mode+namespace'=0x67f000010003
Name: SCOTT.SP_LOCK_TEST1 [Nsp: 1 Typ: 7 Id: 26608]
time in wait: 1 min 28 sec
timeout after: 13 min 31 sec
wait id: 410
blocking: 1 session
current sql_id: 90863518
wait history:
* time between current wait and wait #1: 0.000799 sec
1. event: 'SQL*Net message from client'
time waited: 1.392477 sec
wait id: 409 p1: 'driver id'=0x54435000
p2: '#bytes'=0x1
* time between wait #1 and #2: 0.000003 sec
2. event: 'SQL*Net message to client'
time waited: 0.000001 sec
wait id: 408 p1: 'driver id'=0x54435000
p2: '#bytes'=0x1
* time between wait #2 and #3: 0.000015 sec
3. event: 'SQL*Net message from client'
time waited: 0.000797 sec
wait id: 407 p1: 'driver id'=0x54435000
p2: '#bytes'=0x1
}
and is blocked by
=> Oracle session identified by:
{
instance: 1 (oracle19.oracle19)
os id: 9671
process id: 52, oracle@oel7
session id: 20
session serial #: 22760
module name: 0 (Orange for ORACLE DBA)
}
which is waiting for 'PL/SQL lock timer' with wait info:
{
p1: 'duration'=0x0
time in wait: 1 min 38 sec
timeout after: 1 min 21 sec
wait id: 70
blocking: 2 sessions
current sql_id: 1075132594
wait history:
* time between current wait and wait #1: 0.002701 sec
1. event: 'SQL*Net message from client'
time waited: 1.267175 sec
wait id: 69 p1: 'driver id'=0x54435000
p2: '#bytes'=0x1
* time between wait #1 and #2: 0.000009 sec
2. event: 'SQL*Net message to client'
time waited: 0.000001 sec
wait id: 68 p1: 'driver id'=0x54435000
p2: '#bytes'=0x1
* time between wait #2 and #3: 0.000002 sec
3. event: 'SQL*Net break/reset to client'
time waited: 0.000195 sec
wait id: 67 p1: 'driver id'=0x54435000
p2: 'break?'=0x0
}
Chain 1 Signature: 'PL/SQL lock timer'<='library cache pin'<='library cache lock'
Chain 1 Signature Hash: 0x38f0a7dd
-------------------------------------------------------------------------------
===============================================================================