오라클 성능 트러블슈팅의 기초 (2012년)
Enqueue 관련 대기 이벤트
Enqueue 대기 이벤트 소개
- Enqueue(또는 간략하게 Lock)는 테이블이나 로우와 같은 데이터베이스의 오브젝트를 보호하는 동기화 객체
- 특정 오브젝트를 변경하기 위해서는 반드시 해당 오브젝트를 보호하는 Enqueue를 획득해야 하며, 다른 세션이 해당 오브젝트를 변경 중이어서 Enqueue를 획득하지 못하는 경우 'enq:XXX'류의 이벤트를 대기함
- V$EVENT 뷰에서 Enqueue와 관련된 대기 이벤트는 아래와 같음
소스코드
SELECT NAME
FROM V$EVENT_NAME
WHERE NAME LIKE 'enq:%'
;
수행내역
SQL >
1 SELECT NAME
2 FROM V$EVENT_NAME
3 WHERE NAME LIKE 'enq:%'
4 ;
NAME
----------------------------------------------------------
enq: PW - flush prewarm buffers
enq: RO - contention
enq: RO - fast object reuse
enq: KO - fast object checkpoint
enq: TM - contention
enq: ST - contention
enq: HW - contention
enq: SS - contention
...(중략)...
enq: JS - queue lock
enq: JS - sch locl enqs
enq: JS - q mem clnup lck
enq: JS - evtsub add
enq: JS - evtsub drop
enq: JS - wdw op
enq: JS - evt notify
209 rows selected.
Elapsed: 00:00:00.95
- 위의 결과를 보면 Enqueue 관련 대기 이벤트는 'enq:<Lock Type> - Reason'의 형태를 지니고 있음
- 'enq: TX - row lock contention' : 로우 레벨 락 경합 때문에 TX(Transaction) Enqueue를 획득하지 못해서 대기한다는 것을 의미함
- 'enq: TX - allocate ITL entry' : ITL 엔트리를 할당받을 수 없기 때문에 TX(Transaction) Enqueue를 획득하지 못해서 대기한다는 것을 의미함
V$LOCK_TYPE
- Enqueue 관련 대기 이벤트를 해석하려면 Enqueue 자체에 대한 정보를 알아야 하는데, V$LOCK_TYPE 뷰를 사용하면 확인 가능함
DESC v$lock_type
Name Null? Type
--------------- -------- ---------------
TYPE VARCHAR2(64)
NAME VARCHAR2(64)
ID1_TAG VARCHAR2(64)
ID2_TAG VARCHAR2(64)
IS_USER VARCHAR2(3)
DESCRIPTION VARCHAR2(4000)
| TYPE | Enqueue 의 유형. 가렁 TM 은 테이블을 보호하는 Enqueue 이고 TX 는 로우(더 정확하게 말하면 트랜잭션)를 보호하는 Enqueue |
| NAME | Enqueue 의 이름. |
| ID1_TAG | V$LOCK.ID1 컬럼 값의 정의 |
| ID2_TAG | V$LOCK.ID2 컬럼 값의 정의. ID1 과 ID2 컬럼의 정보를 조합하면 Enqueue 가 보호하는 자원을 알 수 있음 |
| IS_USER | YES 이면 유저 타입의 Enqueue(유저의 작업과 관련된 Enqueue), NO 이먼 시스템 타입의 Enqueue(오라클 내부 작업과 관련된 Enqueue). 가령 트랜잭션을 보호하는 TX Enqueue 는 유저 타입의 Enqueue 이며， 하이워터마크(HWM)를 보호하는 HW Enqueue 는 시스템 타입의 Enqueue |
| DESCRIPTION | Enqueue 에 대한 상세한 설명 |
- 'TM Enqueue'의 정보를 V$LOCK_TYPE 뷰에서 검색하면 아래와 같음
소스코드
BEGIN
PRINT_TABLE('SELECT *
FROM V$LOCK_TYPE
WHERE TYPE = ''TM''');
END;
수행내역
SQL >
1 BEGIN
2 PRINT_TABLE('SELECT *
3 FROM V$LOCK_TYPE
4 WHERE TYPE = ''TM''');
5 END;
6 /
TYPE : TM
NAME : DML
ID1_TAG : object #
ID2_TAG : table/partition
IS_USER : YES
DESCRIPTION : Synchronizes accesses to an object
---------------------------------------------------------------------------
PL/SQL procedure successfully completed.
V$LOCK
- V$LOCK_TYPE 뷰에서 제공하는 내용을 이해하면, V$LOCK 뷰를 손쉽게 사용할 수 있음
- V$LOCK 뷰는 특정 세션이 현재 획득하고 있거나 획득하기 위해 대기하고 있는 Enqueue의 목록을 보여줌
- V$LOCK 뷰는 Enqueue 관련 대기 이벤트를 분석하는데 있어서 가장 기본적이고 즁요한 뷰임
DESC V$LOCK
Name Null? Type
---------- -------- ------------
ADDR RAW(8)
KADDR RAW(8)
SID NUMBER
TYPE VARCHAR2(2)
ID1 NUMBER
ID2 NUMBER
LMODE NUMBER
REQUEST NUMBER
CTIME NUMBER
BLOCK NUMBER
SELECT *
FROM V$LOCK
WHERE ROWNUM <= 10
;
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
07000002BD1A1460 07000002BD1A1480 9993 XR 4 0 1 0 117369 0
07000002BD1A14F8 07000002BD1A1518 9993 CF 0 0 2 0 117369 0
07000002BD1A1628 07000002BD1A1648 9993 RS 25 1 2 0 117353 0
07000002BD1A1758 07000002BD1A1778 9994 RT 1 0 6 0 117353 0
07000002BD1A1920 07000002BD1A1940 9992 TS 2 1 3 0 103683 0
07000002BD1A19B8 07000002BD1A19D8 9997 MR 1 0 4 0 117305 0
07000002BD1A1A50 07000002BD1A1A70 9997 MR 2 0 4 0 117305 0
07000002BD1A1AE8 07000002BD1A1B08 9997 MR 3 0 4 0 117305 0
07000002BD1A1B80 07000002BD1A1BA0 9997 MR 4 0 4 0 117305 0
07000002BD1A1C18 07000002BD1A1C38 9997 MR 5 0 4 0 117305 0
| SID | Enqueue 를 획득하고 있거나 대기하고 있는 세션의 세션 아이디 |
| TYPE | Enqueue 유형. V$LOCK_TYPE.TYPE 컬럼 참조 => TO(Temp) : OBJECT Synchronizes DDL and DML operations on a temp object => RT(Redo Thread) : Thread locks held by LGWR, DBW0, and RVWR to indicate mounted or open status => PS(PX Process Reservation) : Parallel Execution Server Process reservation and synchronization => XR(Quiesce / Force Logging) : Lock held during database quiesce or for database force logging => TS(Temporary Segment) : Serializes accesses to temp segments => TX(Transaction) : Lock held by a transaction to allow other transactions to wait for it => RS(Reclaimable Space) : Lock held by a space reclaimable operation to allow other operations to wait for it => MR(Media Recovery) : Lock used to coordinate media recovery with other uses of datafiles => CF(Controlfile Transaction) : Synchronizes accesses to the controlfile |
| ID1 | V$LOCK_TYPE.IDl_TAG 참조 |
| ID2 | V$LOCK_TYPE.ID2_TAG 참조 |
| LMODE | Lock Mode. Enqueue 를 획득하고 있는 경우 획득 모드 => 1 : NULL => 2 : Row Share => 3 : Row Exclusive => 4 : Share => 5 : Shared Row Exclusive => 6 : Exclusive |
| REQUEST | Request Mode. Enqueue 를 획득하기 위해 대기하고 있는 경우 대기 모드 => 1 : NULL => 2 : Row Share => 3 : Row Exclusive => 4 : Share => 5 : Shared Row Exclusive => 6 : Exclusive |
| CTIME | Current Mode Time. 현재 모드로 Enqueue 를 획득한 이후의 시간 |
| BLOCK | 다른 세션을 블로킹하고 있는 경우에는 1. 그렇지 않은 경우에는 O |
V$SESSION
- V$SESSION 뷰에서도 Enquene와 관련된 정보 확인 가능
DESC V$SESSION
Name Null? Type
-------------------------- -------- ----------------
SADDR RAW(8)
SID NUMBER
...(중략)...
ROW_WAIT_OBJ# NUMBER
ROW_WAIT_FILE# NUMBER
ROW_WAIT_BLOCK# NUMBER
ROW_WAIT_ROW# NUMBER
...(중략)...
BLOCKING_SESSION_STATUS VARCHAR2(11)
BLOCKING_INSTANCE NUMBER
BLOCKING_SESSION NUMBER
...(중략)...
;
SELECT SADDR,
SID,
ROW_WAIT_OBJ#,
ROW_WAIT_FILE#,
ROW_WAIT_BLOCK#,
ROW_WAIT_ROW#,
BLOCKING_SESSION_STATUS,
BLOCKING_INSTANCE,
BLOCKING_SESSION
FROM V$SESSION
WHERE ROWNUM <= 10
;
SADDR SID ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# BLOCKING_SESSION_STATU BLOCKING_INSTANCE BLOCKING_SESSION
---------------- ---------- ------------- -------------- --------------- ------------- ---------------------- ----------------- ----------------
07000002C1C0C830 9613 -1 0 0 0 NO HOLDER
07000002C2C26C60 9618 23691487 1912 1721473 0 NO HOLDER
07000002BFBFA388 9626 484182 361 2507607 0 NO HOLDER
07000002C1C10640 9631 23691489 460 2087281 0 NO HOLDER
07000002BFBFB838 9632 -1 0 0 0 NO HOLDER
07000002BFBFCCE8 9638 -1 0 0 0 NO HOLDER
07000002C0C222D0 9639 -1 0 0 0 NO HOLDER
07000002C4C422A0 9640 23691494 2628 1797665 0 NO HOLDER
07000002C1C12FA0 9643 -1 0 0 0 NO HOLDER
07000002BFBFE198 9644 23691483 3193 1890049 0 NO HOLDER
| ROW_WAIT_OBJ# | 현재 세션이 로우 레벨 락(TX Enqueue)을 획득하기 위해 대기하는 경우，경합 대상이 되는 로우가 위치하는 오브젝트의 데이터 오브젝트 아이디 |
| ROW_WAIT_FILE# | 헌재 세션이 로우 레벨 락(TX Enqueue)을 획득하기 위해 대기하는 경우，경합 대상이 되는 로우가 위치하는 파일 번호 |
| ROW_WAIT_BLOCK# | 현재 세션이 로우 레벨 락(TX Enqueue)을 획득하기 위해 대기하는 경우，경합 대상이 되는 로우가 위치하는 블록 번호 |
| ROW_WAIT_ROW# | 현재 세션이 로우 레벨 락(TX Enqueue)을 획득하기 위해 대기하는 경우，경합 대상이 되는 로우가 위치하는 로우 번호 |
| BLOCKING_SESSION_STATUS | 현재 세션을 블로킹하고 있는 세선의 상태 |
| BLOCKING_INSTANCE | 현재 세션을 블로킹하고 있는 세션의 인스턴스 번호 RAC 환경에서 인스틴스 간에 Enqueue 경합이 발생하는 경우에 사용 |
| BLOCKING_SESSION | 현재 세션을 블로킹하고 있는 세선의 세션 아이디 |
- ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#
- 이 4개 컬럼을 조합하면 ROWID 값이 되기 때문에, 이 값을 이용하여 어떤 로우에서 로우 레벨 락 경합이 발생하는지 알 수 있음
- 이 값들은 현재 세션이 로우 레벨 락 경합에 의해 TX Enqueue를 획득하기 위해 대기하고 있는 경우에만 의미가 있음
- 예를들어, 현재 세션이 'enq: TX - row lock contention' 이벤트를 대기하고 있다면, 이 값들에 대해 조회 가능함
- BLOCKING_SESSION_STATUS, BLOCKING_INSTANCE, BLOCKING_SESSION
- 이 3개 컬럼은 V$SESSION 뷰를 조회하는 것만으로 블로커(Blocker)를 알 수 있다는데서 대단히 유용하게 사용할 수 있으며, 특히 RAC에서 대단히 유용함
- 단 Enqueue 환경에서만 완벽한 데이터를 제공하며, Enqueue가 아닌 다른 종류의 락(Library Cache Lock, Library Cache Pin, Row Cache Lock)과 같은 종류의 락들은 RAC에서 완전한 형태의 정보가 제공되지 않음
Enqueue 관련 대기 이벤트 테스트
SESSION #1
- 테이블과 데이터를 생성하고 UPDATE를 해서 LOCK을 걸음
소스코드
-- 1. 테이블 생성
DROP TABLE T1;
CREATE TABLE T1(C1 NUMBER);
INSERT INTO T1 VALUES(1);
COMMIT;
-- 2. UPDATE
EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_1');
UPDATE T1 SET C1 = 1;
수행내역
SQL >
1 DROP TABLE T1;
DROP TABLE T1
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL >
1 CREATE TABLE T1(C1 NUMBER);
Table created.
SQL >
1 INSERT INTO T1 VALUES(1);
1 row created.
SQL >
1 COMMIT;
Commit complete.
SQL >
1 EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_1');
PL/SQL procedure successfully completed.
SQL >
1 UPDATE T1 SET C1 = 1;
SESSION #2
- 세션 #2에서 동일한 로우 업데이트
소스코드
EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_2');
UPDATE T1 SET C1 = 1;
수행내역
SQL >
1 EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_2');
PL/SQL procedure successfully completed.
SQL >
1 UPDATE T1 SET C1 = 1;
SESSION #3
- 세션 #3에서 동일한 로우 업데이트
소스코드
EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_3');
UPDATE T1 SET C1 = 1;
수행내역
SQL >
1 EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_3');
PL/SQL procedure successfully completed.
SQL >
1 UPDATE T1 SET C1 = 1;
1 row updated.
SESSION #4에서 SESSION #2 모니터링
- 세션 #1에서 UPDATE 한 로우를 세션 #2에서 동일한 로우를 UPDATE하기 때문에 락 경합이 발생되며, 이에 대해 모니터링함
소스코드
-- 1. V$SESSION_WAIT 뷰로 SESSION #2 모니터링
set serveroutput on
col sid new_value sid
SELECT SID
FROM V$SESSION
WHERE CLIENT_INFO = 'SESSION_2';
BEGIN
PRINT_TABLE('SELECT *
FROM V$SESSION_WAIT
WHERE SID = &SID');
END;
/
-- 2. V$SESSION 뷰로 SESSION #2 모니터링
col objn new_value objn
col filen new_value filen
col blockn new_value blockn
col rown new_value rown
SELECT ROW_WAIT_OBJ# AS OBJN,
ROW_WAIT_FILE# AS FILEN,
ROW_WAIT_BLOCK# AS BLOCKN,
ROW_WAIT_ROW# AS ROWN,
BLOCKING_INSTANCE AS B_INST,
BLOCKING_SESSION AS B_SESS,
BLOCKING_SESSION_STATUS AS B_STA
FROM V$SESSION
WHERE SID = &SID
;
-- 3. V$SESSION 뷰에서 얻은 ROW_WAIT_OBJ# 값을 이용해서 경합 대상 오브젝트 정보 확인
SELECT OBJECT_NAME
FROM DBA_OBJECTS
WHERE DATA_OBJECT_ID = &OBJN
;
-- 4. ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# 값을 이용하여 ROWID를 얻고 이를통해 T1 테이블의 로우를 확인
SELECT *
FROM T1
/*
WHERE ROWID = DBMS_ROWID.ROWID_CREATE(rowid_type => 1,
object_number => &objn,
relative_fno => &filen,
block_number => &blockn,
row_number => &rown
)
*/
-- 파라미터 지정 시 에러가 나서 직접 값 입력
WHERE ROWID = DBMS_ROWID.ROWID_CREATE(1,
&objn,
&filen,
&blockn,
&rown
)
;
-- 5. V$LOCK 뷰로 SESSION #2 모니터링
col sid format 9999
col name format a15
col id1_tag format a10
col id2_tag format a10
col lmode format 99
SELECT H.SID,
H.TYPE,
H.ID1,
H.ID2,
H.REQUEST,
T.NAME,
T.ID1_TAG,
T.ID2_TAG
FROM V$LOCK H,
V$LOCK W,
V$LOCK_TYPE T
WHERE H.ID1 = W.ID1
AND H.ID2 = W.ID2
AND H.TYPE = T.TYPE
AND H.LMODE > 0
AND H.BLOCK > 0
AND W.SID = &SID
;
-- 6. V$LOCK 뷰를 이용하여 Lcok Tree 형태로 Enqueue 경합 관계 확인
col id1 format a20
col id2 format a10
col lmode format a5
col request format a5
col h_mode format a5
col w_mode format a5
WITH
HOLDER AS (
SELECT SID,
TYPE,
(CASE WHEN (TYPE = 'TM') THEN (SELECT OBJECT_NAME || '(' || ID1 || ')'
FROM ALL_OBJECTS
WHERE OBJECT_ID = ID1)
ELSE ID1 || ''
END) AS ID1,
ID2 || '' AS ID2,
LMODE,
REQUEST,
CTIME
FROM V$LOCK
WHERE BLOCK = 1
),
WAITER AS (
SELECT SID,
TYPE,
(CASE WHEN (TYPE = 'TM') THEN (SELECT OBJECT_NAME || '(' || ID1 || ')'
FROM ALL_OBJECTS
WHERE OBJECT_ID = ID1)
ELSE ID1 || ''
END) AS ID1,
ID2 || '' AS ID2,
LMODE,
REQUEST,
CTIME
FROM V$LOCK
WHERE BLOCK = 0
AND REQUEST > 0
ORDER BY CTIME DESC
)
SELECT H.TYPE,
H.SID AS H_SID,
DECODE(H.LMODE, 6, 'X(6)',
5, 'SRX(5)',
4, 'S(4)',
3, 'RX(3)',
2, 'RS(2)',
1, 'N(1)',
H.LMODE) AS H_MODE,
W.SID AS W_SID,
DECODE(W.REQUEST, 6, 'X(6)',
5, 'SRX(5)',
4, 'S(4)',
3, 'RX(3)',
2, 'RS(2)',
1, 'N(1)',
W.REQUEST) AS W_MODE,
H.ID1,
H.ID2,
W.CTIME AS "W_TIME(CS)"
FROM WAITER W,
HOLDER H
WHERE W.ID1 = H.ID1
AND W.ID2 = H.ID2
ORDER BY W.CTIME DESC
;
-- 7. @LOCK 스크립트로 테스트
set pagesize 3000
set linesize 500
col "Hold Sid" heading "(Node)H-Sid" format a11
col "Wait Sid" heading "(Node)W-Sid" format a11
col "HW type" heading "Lock Status" format a11
col "Instance" heading "Node" format 9999
col "Wait Time" heading "W-Time" format 999999
col "Lock Type" format a9
col "Hold Lock Mode" heading "H L-Mode" format a8
col "Request Lock Mode" heading "R L-Mode" format a8
col locked_obj format a32
col "ID1" format 99999999
col "ID2" format 99999999
col username1 heading "UserName" format a8
col sid1 heading "SID/SER#" format a12
col status1 heading "S" format a1
col sql_trace1 heading "TR/w/b" format a6
col blocking1 heading "BLOCKING" format a11
col wait_event1 heading "WAIT_EVENT" format a25
col pga1 heading "PGA" format 9999
col lce1 heading "LCET" format 99999
col module1 heading "MODULE" format a23
col pgm1 heading "PGM" format a4
col sql_text1 heading "SQL " format a27
SELECT /*+ NO_MERGE(V) ORDERED */
DECODE(V.HOLD_SID, NULL, '', '(' || V.INST_ID || ')' || V.HOLD_SID) "Hold Sid",
DECODE(V.WAIT_SID,
NULL,
'',
'^',
'▽',
'(' || V.INST_ID || ')' || V.WAIT_SID) "Wait Sid",
V.GB "HW type",
SW.SECONDS_IN_WAIT "Wait Time",
V.TYPE "Lock Type",
DECODE(V.LMODE, 0, 'None', -- 'None' ,
1, 'Null', -- 'Null' ,
2, 'Row Sh', -- 'Row Share' ,
3, 'Row Ex', -- 'Row Exclusive' ,
4, 'Share', -- 'Share' ,
5, 'Sh R X', -- 'Share Row Exclusive' ,
6, 'Ex', -- 'Exclusive' ,
TO_CHAR(V.LMODE)) "Hold Lock Mode",
DECODE(V.REQUEST, 0, 'None', -- 'None' ,
1, 'Null', -- 'Null' ,
2, 'Row Sh', -- 'Row Share' ,
3, 'Row Ex', -- 'Row Exclusive' ,
4, 'Share', -- 'Share' ,
5, 'Sh R X', -- 'Share Row Exclusive' ,
6, 'Ex', -- 'Exclusive' ,
TO_CHAR(V.REQUEST)) "Request Lock Mode",
(SELECT OBJECT_NAME || '(' || SUBSTR(OBJECT_TYPE, 1, 1) || ')'
FROM DBA_OBJECTS DO
WHERE DO.OBJECT_ID = S.ROW_WAIT_OBJ#) LOCKED_OBJ,
SUBSTR(S.USERNAME, 1, 8) AS USERNAME1,
TO_CHAR(S.SID) || ',' || TO_CHAR(S.SERIAL#) AS SID1,
SUBSTR(STATUS, 1, 1) AS STATUS1,
S.MODULE AS MODULE1,
SUBSTR(DECODE(SIGN(LENGTHB(S.PROGRAM) - 13),
1,
SUBSTR(S.PROGRAM, 1, 13) || '..',
S.PROGRAM),
1,
4) AS PGM1,
S.SECONDS_IN_WAIT AS SECONDS_IN_WAIT1,
SUBSTR(S.EVENT, 1, 25) AS WAIT_EVENT1,
LAST_CALL_ET AS LCE1,
TRIM((SELECT SUBSTR(SQL_TEXT, 1, 20)
FROM GV$SQL SQ
WHERE SQ.INST_ID = S.INST_ID
AND SQ.SQL_ID = S.SQL_ID
AND ROWNUM = 1)) AS SQL_TEXT1
FROM (SELECT ROWNUM,
INST_ID,
DECODE(REQUEST, 0, TO_CHAR(SID)) HOLD_SID,
DECODE(REQUEST, 0, '^', TO_CHAR(SID)) WAIT_SID,
SID,
DECODE(REQUEST, 0, 'holding', 'waiting') GB,
ID1,
ID2,
LMODE,
REQUEST,
TYPE
FROM GV$LOCK
WHERE (ID1, ID2, TYPE) IN (SELECT ID1,
ID2,
TYPE
FROM GV$LOCK
WHERE (REQUEST != 0))
) V,
GV$SESSION S,
GV$SESSION_WAIT SW,
GV$PROCESS P
WHERE V.SID = S.SID
AND V.INST_ID = S.INST_ID
AND S.SID = SW.SID
AND S.INST_ID = SW.INST_ID
AND S.PADDR = P.ADDR
AND S.INST_ID = P.INST_ID
ORDER BY V.ID1,
V.REQUEST,
SW.SECONDS_IN_WAIT DESC
;
수행내역
SQL >
1 set serveroutput on
SQL >
1 col sid new_value sid
SQL >
1 SELECT SID
2 FROM V$SESSION
3 WHERE CLIENT_INFO = 'SESSION_2';
SID
----------
9629
1 row selected.
SQL >
1 BEGIN
2 PRINT_TABLE('SELECT *
3 FROM V$SESSION_WAIT
4 WHERE SID = &SID');
5 END;
6 /
SID : 9629
SEQ# : 79
EVENT : enq: TX - row lock contention
P1TEXT : name|mode
P1 : 1415053318
P1RAW : 0000000054580006
P2TEXT : usn<<16 | slot
P2 : 1366032409
P2RAW : 00000000516C0019
P3TEXT : sequence
P3 : 15
P3RAW : 000000000000000F
WAIT_CLASS_ID : 4217450380
WAIT_CLASS# : 1
WAIT_CLASS : Application
WAIT_TIME : 0
SECONDS_IN_WAIT : 18
STATE : WAITING
---------------------------------------------------------------------------
PL/SQL procedure successfully completed.
SQL >
1 col objn new_value objn
SQL >
1 col filen new_value filen
SQL >
1 col blockn new_value blockn
SQL >
1 col rown new_value rown
SQL >
1 SELECT ROW_WAIT_OBJ# AS OBJN,
2 ROW_WAIT_FILE# AS FILEN,
3 ROW_WAIT_BLOCK# AS BLOCKN,
4 ROW_WAIT_ROW# AS ROWN,
5 BLOCKING_INSTANCE AS B_INST,
6 BLOCKING_SESSION AS B_SESS,
7 BLOCKING_SESSION_STATUS AS B_STA
8 FROM V$SESSION
9 WHERE SID = &SID
10 ;
OBJN FILEN BLOCKN ROWN B_INST B_SESS B_STA
---------- ---------- ---------- ---------- ---------- ---------- ----------------------
83508150 387 244179 0 1 9675 VALID
1 row selected.
SQL >
1 SELECT OBJECT_NAME
2 FROM DBA_OBJECTS
3 WHERE DATA_OBJECT_ID = &OBJN
4 ;
OBJECT_NAME
---------------
T1
1 row selected.
SQL >
1 SELECT *
2 FROM T1
3 /*
4 WHERE ROWID = DBMS_ROWID.ROWID_CREATE(rowid_type => 1,
5 object_number => &objn,
6 relative_fno => &filen,
7 block_number => &blockn,
8 row_number => &rown
9 )
10 */
11 -- 파라미터 지정 시 에러가 나서 직접 값 입력
12 WHERE ROWID = DBMS_ROWID.ROWID_CREATE(1,
13 &objn,
14 &filen,
15 &blockn,
16 &rown
17 )
18 ;
C1
----------
1
1 row selected.
SQL >
1 col sid format 9999
SQL >
1 col name format a15
SQL >
1 col id1_tag format a10
SQL >
1 col id2_tag format a10
SQL >
1 col lmode format 99
SQL >
1 SELECT H.SID,
2 H.TYPE,
3 H.ID1,
4 H.ID2,
5 H.REQUEST,
6 T.NAME,
7 T.ID1_TAG,
8 T.ID2_TAG
9 FROM V$LOCK H,
10 V$LOCK W,
11 V$LOCK_TYPE T
12 WHERE H.ID1 = W.ID1
13 AND H.ID2 = W.ID2
14 AND H.TYPE = T.TYPE
15 AND H.LMODE > 0
16 AND H.BLOCK > 0
17 AND W.SID = &SID
18 ;
SID TYPE ID1 ID2 REQUEST NAME ID1_TAG ID2_TAG
----- ---- ---------- ---------- ---------- --------------- ---------- ----------
9675 TX 1366032409 15 0 Transaction usn<<16 | sequence
slot
1 row selected.
SQL >
1 col id1 format a20
SQL >
1 col id2 format a10
SQL >
1 col lmode format a5
SQL >
1 col request format a5
SQL >
1 col h_mode format a5
SQL >
1 col w_mode format a5
SQL >
1 WITH
2 HOLDER AS (
3 SELECT SID,
4 TYPE,
5 (CASE WHEN (TYPE = 'TM') THEN (SELECT OBJECT_NAME || '(' || ID1 || ')'
6 FROM ALL_OBJECTS
7 WHERE OBJECT_ID = ID1)
8 ELSE ID1 || ''
9 END) AS ID1,
10 ID2 || '' AS ID2,
11 LMODE,
12 REQUEST,
13 CTIME
14 FROM V$LOCK
15 WHERE BLOCK = 1
16 ),
17 WAITER AS (
18 SELECT SID,
19 TYPE,
20 (CASE WHEN (TYPE = 'TM') THEN (SELECT OBJECT_NAME || '(' || ID1 || ')'
21 FROM ALL_OBJECTS
22 WHERE OBJECT_ID = ID1)
23 ELSE ID1 || ''
24 END) AS ID1,
25 ID2 || '' AS ID2,
26 LMODE,
27 REQUEST,
28 CTIME
29 FROM V$LOCK
30 WHERE BLOCK = 0
31 AND REQUEST > 0
32 ORDER BY CTIME DESC
33 )
34 SELECT H.TYPE,
35 H.SID AS H_SID,
36 DECODE(H.LMODE, 6, 'X(6)',
37 5, 'SRX(5)',
38 4, 'S(4)',
39 3, 'RX(3)',
40 2, 'RS(2)',
41 1, 'N(1)',
42 H.LMODE) AS H_MODE,
43 W.SID AS W_SID,
44 DECODE(W.REQUEST, 6, 'X(6)',
45 5, 'SRX(5)',
46 4, 'S(4)',
47 3, 'RX(3)',
48 2, 'RS(2)',
49 1, 'N(1)',
50 W.REQUEST) AS W_MODE,
51 H.ID1,
52 H.ID2,
53 W.CTIME AS "W_TIME(CS)"
54 FROM WAITER W,
55 HOLDER H
56 WHERE W.ID1 = H.ID1
57 AND W.ID2 = H.ID2
58 ORDER BY W.CTIME DESC
59 ;
TYPE H_SID H_MOD W_SID W_MOD ID1 ID2 W_TIME(CS)
---- ---------- ----- ---------- ----- -------------------- ---------- ----------
TX 9675 X(6) 9629 X(6) 1366032409 15 790
TX 9675 X(6) 9672 X(6) 1366032409 15 787
2 rows selected.
SQL >
1 @LOCK
(Node)H-Sid (Node)W-Sid Lock Status W-Time Lock Type H L-Mode R L-Mode LOCKED_OBJ UserName SID/SER# S MODULE PGM W_T WAIT_EVENT LCET SQL
----------- ----------- ----------- ------- --------- -------- -------- ------------ -------- ------------ - ----------- ---- ------- ------------------------- ------ ---------------------------
(1)9675 ▽ holding 1136 TX Ex None APPS 9675,25 I SQL*Plus sqlp 1136 SQL*Net message from clie 1136
(1)9629 waiting 1129 TX None Ex T1(T) APPS 9629,219 A SQL*Plus sqlp 1129 enq: TX - row lock conten 1129 UPDATE T1 SET C1 = 1
(1)9672 waiting 1126 TX None Ex T1(T) APPS 9672,71 A SQL*Plus sqlp 1126 enq: TX - row lock conten 1126 UPDATE T1 SET C1 = 1
3 rows selected.
"데이터베이스 스터디모임" 에서 2012년에 "오라클 성능 트러블슈팅의 기초
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4301
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.