{"id":414,"date":"2015-08-24T05:34:24","date_gmt":"2015-08-24T05:34:24","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=414"},"modified":"2015-08-24T05:34:24","modified_gmt":"2015-08-24T05:34:24","slug":"oracle-lock-%ec%9d%b4%eb%9e%80","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=414","title":{"rendered":"oracle lock \uc774\ub780?"},"content":{"rendered":"

Latch\uac00 SGA\uc601\uc5ed\uc744 \ubcf4\ud638 \ud55c\ub2e4\uba74 Lock\uc740 \ub370\uc774\ud130 \ubca0\uc774\uc2a4\ub97c \ubcf4\ud638 \ud55c\ub2e4\uace0 \ubcf4\uba74 \ub41c\ub2e4.  Lock\uc740 Latch\uc640 \ub2e4\ub974\uac8c \ubcf5\uc7a1\ud55c \uba54\uce74\ub2c8\uc998\uc744 \ud1b5\ud558\uc5ec \uad00\ub9ac\uac00 \ub41c\ub2e4.<\/span><\/p>\n

\ub610, Lock\uc740 \uad00\ub9ac \ubc29\ubc95\uc5d0 \ub530\ub77c\uc11c \uc77c\ubc18 Lock\uacfc Enqueue Lock\uc73c\ub85c \ubd84\ub958\ub97c \ud560 \uc218 \uc788\ub2e4.<\/span><\/p>\n

 <\/span><\/p>\n

enqueue\uc758 \uad6c\uc870\ub294 \ubc84\ud37c \uce90\uc26c \uc544\ud0a4\ud14d\ucc98\uc640 \uc720\uc0ac\ud558\ub2e4.<\/span><\/p>\n

 <\/span><\/p>\n

enqueue hash chains latch (1: m) hash bucket (1:1) enqueue hash cahin <\/span><\/p>\n

\uc5ec\uae30\uc11c enqueue hash chains latch\uc758 \uac1c\uc218\ub294 \uae30\ubcf8\uc801\uc73c\ub85c CPU_COUNT\uc640 \ub3d9\uc77c\ud558\uace0 _ENQUEUE_HASH_CHAIN_LATCHES \ud30c\ub77c\uba54\ud130\ub85c<\/span><\/p>\n

\uc218\uc815\uc774 \uac00\ub2a5\ud558\ub2e4.<\/span><\/p>\n

 <\/span><\/p>\n

Enqueue\uc5d0 \ub300\ud574\uc11c \uc880\ub354 \uad6c\uc870\ub97c \ud655\uc778 \ud558\uace0 \uc2f6\uc73c\uba74 \ub2e4\uc74c \uba85\ub839\uc744 \uc774\uc6a9\ud558\uc5ec Dump\ub97c \uc218\ud589 \ud560 \uc218 \uc788\ub2e4.<\/span><\/p>\n

 <\/span><\/p>\n

alter session set events \u2019immediate trace name enqueues level 3\u2019;<\/span><\/p>\n

 <\/span><\/p>\n

Enqueue Lock\uc740 Enqueue\ub77c\ub294 \uad6c\uc870\uccb4\ub85c \uad00\ub9ac \ub418\ub294 lock\uc774\ub2e4. \uc774 \uad6c\uc870\uccb4\ub294 shared pool \uc601\uc5ed\uc5d0 \uc800\uc7a5\uc774 \ub41c\ub2e4.<\/span><\/p>\n

Enqueue \uad6c\uc870\uccb4\uc5d0 \uc758\ud574\uc11c \uad00\ub9ac\ub418\ub294 lock\uc740 \uc6b0\ub9ac\uac00 \uc77c\ubc18\uc801\uc73c\ub85c \uc544\ub294 Object \uad00\ub828 lock\uc774\ub77c\uace0 \uc0dd\uac01\ud558\uba74 \ub41c\ub2e4. \uc608\ub97c \ub4e4\uba74 TX,TM,UL, CF,US……<\/span><\/p>\n

\ub4f1\ub4f1\uc758 lock\uc774 Enqueue \uad6c\uc870\uccb4\ub85c \uad00\ub9ac\ub418\ub294 enqueue lock \uc774\ub2e4.<\/span><\/p>\n

enqueue lock \ud68d\ub4dd\uc744 \uc2dc\ub3c4\ud55c \ud504\ub85c\uc138\uc2a4\uac00 \ud68d\ub4dd\uc5d0 \uc2e4\ud328 \ud560 \uacbd\uc6b0 Enqueue \uad6c\uc870\uccb4 \uc548\uc5d0 waiter list\uc5d0 \uc790\uc2e0\uc744 \ub4f1\ub85d\ud55c\ub2e4.<\/span><\/p>\n

\uae30\uc874\uc5d0 \ud68d\ub4dd\ud588\ub358 \ud504\ub85c\uc138\uc2a4\uac00 lock\uc744 \ud574\uc81c \ud560 \uacbd\uc6b0 \uc774 waiter list\uc548\uc758 \ud504\ub85c\uc138\uc2a4\ub97c \uae68\uc6cc \uc900\ub2e4. waiter list\uc5d0 \ub4f1\ub85d\ub41c \ud504\ub85c\uc138\uc2a4\ub294 3\ucd08\ub4a4\uc5d0 \ub2e4\uc2dc \uae68\uc5b4\ub098\uc11c deadlock\uc774 \ubc1c\uc0dd\ud588\ub294\uc9c0 \uccb4\ud06c \ud6c4 \ub2e4\uc2dc \ub300\uae30 \uc0c1\ud0dc\ub85c \ub3cc\uc544\uac04\ub2e4. \uc774\ub294 latch\uc640\ub294 \ub2e4\ub974\uac8c lock\uc740 deadlock\uc774 \ubc1c\uc0dd \ud560 \uac00\ub2a5\uc131\uc774 \uc788\uae30 \ub54c\ubb38\uc774\ub2e4.<\/span><\/p>\n

 <\/span><\/p>\n

\uc77c\ubc18\uc801\uc778 lock \uad6c\uc870 \uc989 Owner list \/ Waiter list\ub97c \uc790\uccb4 \uad00\ub9ac\ud558\ub294 \ubc29\uc2dd\uc73c\ub85c\ub294 row cache lock , library cache lock , library cache pin ,<\/span><\/p>\n

buffer lock \ub4f1\uc774 \uc788\ub2e4. \uc77c\ubc18 lock\ub3c4 deadlock\uc774 \ubc1c\uc0dd \ud560 \uac00\ub2a5\uc131\uc774 \uc788\uc73c\ubbc0\ub85c \ud0c0\uc784\uc544\uc6c3 \uc2dc\uac04\uc744 \uac00\uc9c4 \ud6c4 \uae68\uc5b4\ub098\uc11c deadlock\uc744 \uccb4\ud06c \ud558\uac8c \ub418\ub294\ub370<\/span><\/p>\n

\uac01\uac01\uc758 lock \ub9c8\ub2e4 \uc2dc\uac04\uc774 \ud2c0\ub9ac\ub2e4. rowcache lock\uc740 60\ucd08 \uae4c\uc9c0 \uae30\ub2e4\ub9ac\uba70 buffer lock\uc740 \uae30\ubcf8\uc801\uc73c\ub85c 1\ucd08 \uc9c0\ub9cc \uc5f0\uc18d\uc801\uc778 buffer lock \ud68d\ub4dd\uc744 \uc704\ud55c \uacbd\uc6b0 3\ucd08\uc774\ub2e4. library cache lock\uacfc library cache pin\uc740 3\ucd08\ub97c \uae30\ub2e4\ub9b0\ub2e4.<\/span><\/p>\n

 <\/span><\/p>\n

Enqueue lock \uacbd\ud569\uc774 \ubc1c\uc0dd\ud558\uba74 v$lock \ubdf0\uc758 TYPE , ID1 , ID2\uac12\uc744 \ud655\uc778\ud558\uba74 \ub41c\ub2e4. \uc774 \uac12\uc740 \ud574\ub2f9 \uc2dc\uc810\uc5d0 v$session_wait\ubdf0\uc5d0\uc11c\ub3c4 enqueue wait\uc744 \ud558\ub294 \uc138\uc158\uc758 P1=name|mode , P2=ID1 , P2=ID2 \ub97c \ud655\uc778\ud574\ub3c4 \uc54c\uc218 \uc788\ub2e4.<\/span><\/p>\n

 <\/span><\/p>\n

\uadf8\ub7fc \uc77c\ubc18 lock \uacbd\ud569\uc740 \uc5b4\ub5bb\uac8c \ud655\uc778\uc774 \uac00\ub2a5\ud560\uae4c ? v$session_wait \ubdf0\uc5d0\uc11c\uc758 \ub300\uae30 \ud604\uc0c1\uc744 \ubaa8\ub2c8\ud130\ub9c1 \ud574\uc57c \ud55c\ub2e4.<\/span><\/p>\n

 <\/span><\/p>\n

V$LOCK \ubdf0\uc758 \uceec\ub7fc\ub4e4\uc744 \ud655\uc778\ud574 \ubcf4\uba74 \ub2e4\uc74c\uacfc \uac19\ub2e4.<\/span><\/p>\n

 <\/span><\/p>\n

ADDR    :  Address of lock state object<\/span>
KADDR   :  Address of lock<\/span>
SID     :  Identifier for session holding or acquiring the lock<\/span>
TYPE    :  Type of user of system lock<\/span>
           System lock\uc758 \uc885\ub958\ub294 \ubc11\uc5d0 \ub2e4\uc2dc \uc815\ub9ac \ud558\uaca0\uc2b4<\/span>
ID1     :  Lock identifier #1 (depend on type)<\/span>
ID2     :  Lock identifier #2 (depend on type)<\/span>
LMODE   :  Lock mode in which the session holds the lock<\/span>
           0 – none \/ 1 – null \/ 2 – row-S(SS) \/ 3 – row-X(SX) \/ 4 – share(S)<\/span>
           5 – S\/Row-X(SSX) \/ 6 – exclusive(X)<\/span>
REQUEST :  Lock mode in which the process requests the lock<\/span>
           0 – none \/ 1 – null \/ 2 – row-S(SS) \/ 3 – row-X(SX) \/ 4 – share(S)<\/span>
           5 – S\/Row-X(SSX) \/ 6 – exclusive(X)<\/span>
CTIME   :  Time since current mode was granted<\/span>
BLOCK   :  A value of the either 0 or 1. Depending on whether or not lock in question is the blocker<\/span><\/p>\n

System Lock\uc758 \uc885\ub958\ub294 \ub2e4\uc74c\uacfc \uac19\ub2e4.<\/span><\/p>\n

 <\/span><\/p>\n

BL :  Buffer hash table instance<\/span>
NA..NZ : Library cache pin instance (A..Z = namespace)<\/span>
CF : Control file schema global enqueue<\/span>
PF : Password File<\/span>
CI : Cross-instance function invocation instance<\/span>
PI, PS : Parallel operation<\/span>
CU : Cursor bind<\/span>
PR : Process startup<\/span>
DF : datafile instance<\/span>
QA..QZ : Row cache instance (A..Z = cache)<\/span>
DL : Direct loader parallel index create<\/span>
RT : Redo thread global enqueue<\/span>
DM : Mount\/startup db primary\/secondary instance<\/span>
SC : System change number instance<\/span>
DR : Distributed recovery process<\/span>
SM : SMON<\/span>
DX : Distributed transaction entry<\/span>
SN : Sequence number instance<\/span>
FS : File set<\/span>
SQ : Sequence number enqueue<\/span>
HW : Space management operations on a specific segment<\/span>
SS : Sort segment<\/span>
IN : Instance number<\/span>
ST : Space transaction enqueue<\/span>
IR : Instance recovery serialization global enqueue<\/span>
SV : Sequence number value<\/span>
IS : Instance state<\/span>
TA : Generic enqueue<\/span>
IV : Library cache invalidation instance<\/span>
TS : Temporary segment enqueue (ID2=0)<\/span>
JQ : Job queue<\/span>
TS : New block allocation enqueue (ID2=1)<\/span>
KK : Thread kick<\/span>
TT : Temporary table enqueue<\/span>
LA .. LP : Library cache lock instance lock (A..P = namespace)<\/span>
UN : User name<\/span>
MM : Mount definition global enqueue<\/span>
US : Undo segment DDL<\/span>
MR : Media recovery<\/span>
WL : Being-written redo log instance<\/span>
 <\/span>
v$enqueue_lock \uc544\ub77c\ub294 \ubdf0\ub3c4 \uc788\ub294\ub370 \uc774 \ubdf0\uc758 \uceec\ub7fc\ub4e4\ub3c4 v$lock \uacfc \uac19\ub2e4. \uadf8\ub7ec\ub098 v$enqueue_lock \ubdf0\ub294 \ube14\ub85c\ud0b9\uc774 \ubc1c\uc0dd\ud55c \uacbd\uc6b0 \ub300\uae30 \uc138\uc158\uc5d0 \ub300\ud55c \uc815\ubcf4\ub9cc \uc870\ud68c \ub41c\ub2e4. \uc989 \ud050\uc5d0\uc11c \ub300\uae30 \uc911\uc778 \uc138\uc158\uc758 \uc815\ubcf4\ub9cc \ubcf4\uc5ec\uc900\ub2e4. \ube14\ub85c\ud0b9 \uc138\uc158\uc5d0 \ub300\ud55c \uc815\ubcf4\ub294 dba_waiters\ubdf0\uc5d0\uc11c \uc870\ud68c\ud574 \ubcf4\uc544\ub3c4 \ub41c\ub2e4.<\/span><\/p>\n

\ub610 v$locked_object \ubdf0\ub294 \ud604\uc7ac \uc2dc\uc2a4\ud15c\uc5d0\uc11c \ud68d\ub4dd\uc911\uc778 TM\ub77d\uc5d0 \ub300\ud55c \uc815\ubcf4\ub97c \uc81c\uacf5\ud574 \uc8fc\ubbc0\ub85c dba_objects\uc640 \uc870\uc778\ud574\uc11c object \uc815\ubcf4 \ud655\uc778\uc774 \uac00\ub2a5\ud558\ub2e4.<\/span><\/p>\n

 <\/span><\/p>\n

\uadf8\ub7fc enqueue lock\uc774 \uc544\ub2cc\uacbd\uc6b0\ub294 \uc5b4\ub514\uc11c \ud655\uc778\uc744 \ud574\uc57c \ud558\ub294 \uac83\uc778\uac00 ? library cache lock \uac19\uc740 \uacbd\uc6b0\ub294 dba_ddl_locks\ub098 dba_kgllock , \ub610\ub294 x$kgllk \ubdf0\ub97c \ud1b5\ud574\uc11c \uad00\ucc30\uc774 \uac00\ub2a5\ud558\uace0 library cache pin \uac19\uc740 \uacbd\uc6b0\ub294 dba_kgllock \ubdf0\ub098 x$kglpn \ubdf0\ub97c \ud1b5\ud574\uc11c \uac00\ub2a5\ud558\ub2e4.<\/span><\/p>\n

 <\/span><\/p>\n

\uc77c\ubc18\uc801\uc778 lock\uc778\uacbd\uc6b0 v$sesion_wait\ubdf0\uc5d0\uc11c P1\uac12\uc744 \uac00\uc9c0\uace0 \ud655\uc778\ud574 \ubcfc \uc218 \uc788\ub294 \ucffc\ub9ac\uc774\ub2e4.<\/span><\/p>\n

 <\/span><\/p>\n

SELECT  chr(bitand(P1,-16777216)\/16777215)||chr(bitand(P1,16711680)\/65535) “Name” , bitand(P1,65535) “Mode” FROM dual ;<\/span><\/p>\n

 <\/span><\/p>\n

————————————————————————————————–<\/span><\/p>\n

 <\/span><\/p>\n

 Subject:  HOW TO FIND THE SESSION HOLDING A LIBRARY CACHE LOCK<\/span> <\/span>
  Doc ID:  Note:122793.1 Type:  TROUBLESHOOTING<\/span> <\/span>
  Last Revision Date:  25-JUL-2005 Status:  PUBLISHED<\/span><\/p>\n

PURPOSE<\/span>
——-<\/span><\/p>\n

 In some situations it may happen your session is ‘hanging’ and is awaiting for<\/span> <\/span>
 a ‘Library cache lock’. This document describes how to find the session that<\/span> <\/span>
 in fact has the lock you are waiting for.<\/span>
 <\/span><\/p>\n

SCOPE & APPLICATION<\/span>
——————-<\/span><\/p>\n

Support analysts, dba’s, ..<\/span><\/p>\n

HOW TO FIND THE SESSION HOLDING A A LIBRARY CACHE LOCK<\/span>
——————————————————<\/span>
 <\/span>
 Common situations:<\/span><\/p>\n

 * a DML operation that is hanging because the table which is accessed is currently<\/span> <\/span>
   undergoing changes (ALTER TABLE). This may take quite a long time depending on<\/span> <\/span>
   the size of the table and the type of the modification<\/span> <\/span>
   (e.g. ALTER TABLE x MODIFY (col1 CHAR(200) on thousands of records).<\/span><\/p>\n

* The compilation of package will hang on Library Cache Lock and Library Cache Pin<\/span> <\/span>
  if some users are executing any Procedure\/Function defined in the same package.<\/span><\/p>\n

 In the first situation the V$LOCK view will show that the session doing the<\/span> <\/span>
 ‘ALTER TABLE’ has an exclusive DML enqueue lock on the table object (LMODE=6,<\/span> <\/span>
 TYPE=TM and ID1 is the OBJECT_ID of the table). The waiting session however does<\/span> <\/span>
 not show up in V$LOCK yet so in an environment with a lot of concurrent sessions<\/span> <\/span>
 the V$LOCK information is insufficient to track down the culprit blocking your<\/span> <\/span>
 operation.<\/span><\/p>\n

METHOD 1: SYSTEMSTATE ANALYSIS<\/span>
——————————<\/span><\/p>\n

 One way of finding the session blocking you is to analyze the system state dump.<\/span>
 Using the systemstate event one can create a tracefile containing detailed<\/span> <\/span>
 information on every Oracle process. This information includes all the resources<\/span>
 held & requested by a specific process.<\/span><\/p>\n

 Whilst an operation is hanging, open a new session and launch the following<\/span>
 statement:<\/span><\/p>\n

 ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10’;<\/span><\/p>\n

 Oracle will now create a systemstate tracefile in your USER_DUMP_DEST directory.<\/span>
 Get the PID (ProcessID) of the ‘hanging’ session from the V$PROCESS by matching<\/span> <\/span>
 PADDR from V$SESSION with ADDR from V$PROCESS:<\/span><\/p>\n

 SELECT PID FROM V$PROCESS WHERE ADDR=<\/span>
        (SELECT PADDR FROM V$SESSION WHERE SID=sid_of_hanging_session);<\/span><\/p>\n

 The systemstate dump contains a separate section with information for each<\/span> <\/span>
 process. Open the tracefile and do a search for ‘PROCESS pid_from_select_stmt’.<\/span>
 In the process section look up the wait event by doing a search on ‘waiting for’.<\/span><\/p>\n

 Example output:<\/span><\/p>\n

 PROCESS 8:<\/span>
   —————————————-<\/span>
   SO: 50050b08, type: 1, owner: 0, flag: INIT\/-\/-\/0x00<\/span>
   (process) Oracle pid=8, calls cur\/top: 5007bf6c\/5007bf6c, flag: (0) –<\/span>
             int error: 0, call error: 0, sess error: 0, txn error 0<\/span>
   (post info) last post received: 82 0 4<\/span>
               last post received-location: kslpsr<\/span>
               last process to post me: 5004ff08 1 2<\/span>
               last post sent: 0 0 13<\/span>
               last post sent-location: ksasnd<\/span>
               last process posted by me: 5004ff08 1 2<\/span>
     (latch info) wait_event=0 bits=0<\/span>
     Process Group: DEFAULT, pseudo proc: 50058ac4<\/span>
     O\/S info: user: daemon, term: pts\/1, ospid: 15161<\/span>
     OSD pid info: 15161<\/span>
     —————————————-<\/span>
     SO: 5005f294, type: 3, owner: 50050b08, flag: INIT\/-\/-\/0x00<\/span>
     (session) trans: 0, creator: 50050b08, flag: (41) USR\/- BSY\/-\/-\/-\/-\/-<\/span>
               DID: 0001-0008-00000002, short-term DID: 0000-0000-00000000<\/span>
               txn branch: 0<\/span>
               oct: 6, prv: 0, user: 41\/LC<\/span>
     O\/S info: user: daemon, term: pts\/1, ospid: 15160, machine: goblin.forgotten.realms<\/span>
               program:<\/span> <\/span>sqlplus@goblin.forgotten.realms<\/span><\/a> <\/span>(TNS V1-V3)<\/span>
     application name: SQL*Plus, hash value=3669949024<\/span>
     waiting for ‘library cache lock’ blocking sess=0x0 seq=253 wait_time=0<\/span>
!>>              handle address=5023ef9c, lock address=5019cad4, 10*mode+namespace=15<\/span><\/p>\n

 Using the ‘handle address’ you can look up the process that is keeping a lock<\/span>
 on your resource by doing a search on the address within the same tracefile.<\/span><\/p>\n

 Example output:<\/span><\/p>\n

 PROCESS 9:<\/span>
   —————————————-<\/span>
   SO: 50050e08, type: 1, owner: 0, flag: INIT\/-\/-\/0x00<\/span>
   (process) Oracle pid=9, calls cur\/top: 5007bbac\/5007bbfc, flag: (0) –<\/span>
             int error: 0, call error: 0, sess error: 0, txn error 0<\/span>
 <\/span>
   <cut> ….<\/span>
 <\/span> <\/span>
        —————————————-<\/span>
         SO: 5019d5e4, type: 34, owner: 5015f65c, flag: INIT\/-\/-\/0x00<\/span>
!>>      LIBRARY OBJECT PIN: pin=5019d5e4 handle=5023ef9c mode=X lock=0<\/span>
         user=5005fad4 session=5005fad4 count=1 mask=0511 savepoint=118218 flags=[00]<\/span>
 <\/span> <\/span>
 From the output we can see that the Oracle process with PID 9 has an exclusive<\/span>
 lock on the object we are trying to access. Using V$PROCESS and V$SESSION we can<\/span>
 retrieve the sid,user,terminal,program,… for this process. The actual statement<\/span>
 that was launched by this session is also listed in the tracefile (statements and<\/span>
 other library cache objects are preceded by ‘name=’).<\/span><\/p>\n

METHOD 2: EXAMINE THE X$KGLLK TABLE<\/span>
———————————–<\/span><\/p>\n

 The X$KGLLK table (accessible only as SYS\/INTERNAL) contains all the<\/span> <\/span>
 library object locks (both held & requested) for all sessions and<\/span>
 is more complete than the V$LOCK view although the column names don’t<\/span>
 always reveal their meaning.<\/span><\/p>\n

 You can examine the locks requested (and held) by the waiting session<\/span> <\/span>
 by looking up the session address (SADDR) in V$SESSION and doing the<\/span> <\/span>
 following select:<\/span><\/p>\n

 select * from x$kgllk where KGLLKSES = ‘saddr_from_v$session’<\/span><\/p>\n

 This will show you all the library locks held by this session where<\/span>
 KGLNAOBJ contains the first 80 characters of the name of the object.<\/span>
 The value in KGLLKHDL corresponds with the ‘handle address’ of the<\/span>
 object in METHOD 1.<\/span><\/p>\n

 You will see that at least one lock for the session has KGLLKREQ > 0<\/span> <\/span>
 which means this is a REQUEST for a lock (thus, the session is waiting).<\/span> <\/span>
 If we now match the KGLLKHDL with the handles of other sessions in<\/span> <\/span>
 X$KGLLK that should give us the address of the blocking session since<\/span>
 KGLLKREQ=0 for this session, meaning it HAS the lock.<\/span>
 <\/span>
 SELECT * FROM X$KGLLK LOCK_A<\/span> <\/span>
 WHERE KGLLKREQ = 0<\/span>
   AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B<\/span>
               WHERE KGLLKSES = ‘saddr_from_v$session’ \/* BLOCKED SESSION *\/<\/span>
               AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL<\/span>
               AND KGLLKREQ > 0);<\/span><\/p>\n

 If we look a bit further we can then again match KGLLKSES with SADDR<\/span> <\/span>
 in v$session to find further information on the blocking session:<\/span><\/p>\n

 SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION<\/span>
 WHERE SADDR in<\/span> <\/span>
  (SELECT KGLLKSES FROM X$KGLLK LOCK_A<\/span> <\/span>
   WHERE KGLLKREQ = 0<\/span>
     AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B<\/span>
                 WHERE KGLLKSES = ‘saddr_from_v$session’ \/* BLOCKED SESSION *\/<\/span>
                 AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL<\/span>
                 AND KGLLKREQ > 0)<\/span>
  );<\/span><\/p>\n

 In the same way we can also find all the blocked sessions:<\/span><\/p>\n

 SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION<\/span>
 WHERE SADDR in<\/span> <\/span>
  (SELECT KGLLKSES FROM X$KGLLK LOCK_A<\/span> <\/span>
   WHERE KGLLKREQ > 0<\/span>
     AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B<\/span>
                 WHERE KGLLKSES = ‘saddr_from_v$session’ \/* BLOCKING SESSION *\/<\/span>
                 AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL<\/span>
                 AND KGLLKREQ = 0)<\/span>
  );<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

Latch\uac00 SGA\uc601\uc5ed\uc744 \ubcf4\ud638 \ud55c\ub2e4\uba74 Lock\uc740 \ub370\uc774\ud130 \ubca0\uc774\uc2a4\ub97c \ubcf4\ud638 \ud55c\ub2e4\uace0 \ubcf4\uba74 \ub41c\ub2e4.  Lock\uc740 Latch\uc640 \ub2e4\ub974\uac8c \ubcf5\uc7a1\ud55c \uba54\uce74\ub2c8\uc998\uc744 \ud1b5\ud558\uc5ec \uad00\ub9ac\uac00 \ub41c\ub2e4. \ub610, Lock\uc740 \uad00\ub9ac \ubc29\ubc95\uc5d0 \ub530\ub77c\uc11c \uc77c\ubc18 Lock\uacfc Enqueue Lock\uc73c\ub85c \ubd84\ub958\ub97c \ud560 \uc218 \uc788\ub2e4.   enqueue\uc758 \uad6c\uc870\ub294 \ubc84\ud37c \uce90\uc26c \uc544\ud0a4\ud14d\ucc98\uc640 \uc720\uc0ac\ud558\ub2e4.   enqueue hash chains latch (1: m) hash bucket (1:1) enqueue hash cahin  \uc5ec\uae30\uc11c enqueue hash chains […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[11],"tags":[125,152,153],"jetpack_featured_media_url":"","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/414"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=414"}],"version-history":[{"count":0,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/414\/revisions"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=414"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=414"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=414"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}