| 소스코드 | {code:SQL} col NAME format a60 |
SELECT NAME
FROM V$EVENT_NAME
WHERE PARAMETER1 = 'file#'
AND PARAMETER2 = 'block#'
;
|
|수행내역|{code:SQL}
KENZO:xsoftdb:SYSTEM >
1 col NAME format a60
KENZO:xsoftdb:SYSTEM >
1 SELECT NAME
2 FROM V$EVENT_NAME
3 WHERE PARAMETER1 = 'file#'
4 AND PARAMETER2 = 'block#'
5 ;
NAME
------------------------------------------------------------
control file sequential read
control file single write
free buffer waits
local write wait
write complete waits
write complete waits: flash cache
buffer read retry
buffer busy waits
gc buffer busy acquire
gc buffer busy release
read by other session
pi renounce write complete
db file sequential read
db file scattered read
db file single write
gc current request
gc cr request
gc cr disk request
gc cr multi block request
gc current multi block request
gc block recovery request
gc remaster
gc domain validation
gc recovery quiesce
buffer exterminate
cr request retry
recovery buffer pinned
Auto BMR completion
28 개의 행이 선택되었습니다.
|
| 소스코드 | {code:SQL} – 0. SPID 확인 @mysess |
– 1. 테이블 생성
CREATE TABLE T1 AS
SELECT LEVEL AS C1,
RPAD('X', 100) AS C2
FROM DUAL
CONNECT BY LEVEL <= 100
;
– 2. 통계정보 생성
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1');
– 3. Buffer Cache Flush
ALTER SYSTEM FLUSH BUFFER_CACHE;
– 4. 트레이스 활성화
ALTER SESSION SET EVENTS 'sql_trace wait=true';
– 5. SQL 수행
SELECT COUNT(*)
FROM T1
;
– 6. 트레이스 비 활성화
ALTER SESSION SET EVENTS 'sql_trace off';
|
|수행내역|{code:SQL}
KENZO:xsoftdb:SYSTEM >
1 @mysess
MY_SESSION_INFO
-------------------------------------------------------------------------------
DB Name : xsoftdb
Sid, Serial# : 137, 110
OS Process : 3060:716 (CPID), 3396 (SPID)
DB User : SYSTEM
OS User : ken-zo
Module Info : 01@ mysess.sql
Program Info : 01@ mysess.sql
Machine[Term] : WORKGROUP\KENZO [KENZO]
1 개의 행이 선택되었습니다.
KENZO:xsoftdb:SYSTEM >
1 CREATE TABLE T1 AS
2 SELECT LEVEL AS C1,
3 RPAD('X', 100) AS C2
4 FROM DUAL
5 CONNECT BY LEVEL <= 100
6 ;
테이블이 생성되었습니다.
KENZO:xsoftdb:SYSTEM >
1 EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T1');
PL/SQL 처리가 정상적으로 완료되었습니다.
KENZO:xsoftdb:SYSTEM >
1 ALTER SYSTEM FLUSH BUFFER_CACHE;
시스템이 변경되었습니다.
KENZO:xsoftdb:SYSTEM >
1 ALTER SESSION SET EVENTS 'sql_trace wait=true';
세션이 변경되었습니다.
KENZO:xsoftdb:SYSTEM >
1 SELECT COUNT(*)
2 FROM T1
3 ;
COUNT(*)
----------
100
1 개의 행이 선택되었습니다.
KENZO:xsoftdb:SYSTEM >
1 ALTER SESSION SET EVENTS 'sql_trace off';
세션이 변경되었습니다.
|
PARSING IN CURSOR #10 len=26 dep=0 uid=5 oct=3 lid=5 tim=1724632090 hv=3296225404 ad='33bd66d4' sqlid='dx4udkv27hu3w'
SELECT COUNT(*)
FROM T1
END OF STMT
PARSE #10:c=0,e=73707,p=26,cr=5,cu=0,mis=1,r=0,dep=0,og=1,plh=3724264953,tim=1724632086
EXEC #10:c=0,e=65,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3724264953,tim=1724632298
WAIT #10: nam='SQL*Net message to client' ela= 12 driver id=1413697536 #bytes=1 p3=0 obj#=0 tim=1724632426
-----------------------------------------------------------------------------------------------------------
-- file#, block# 항목
WAIT #10: nam='db file sequential read' ela= 5794 file#=1 block#=86576 blocks=1 obj#=74630 tim=1724638340
WAIT #10: nam='db file scattered read' ela= 437 file#=1 block#=86577 blocks=2 obj#=74630 tim=1724638987
-----------------------------------------------------------------------------------------------------------
FETCH #10:c=0,e=6723,p=3,cr=4,cu=0,mis=0,r=1,dep=0,og=1,plh=3724264953,tim=1724639216
STAT #10 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=4 pr=3 pw=0 time=0 us)'
STAT #10 id=2 cnt=100 pid=1 pos=1 obj=74630 op='TABLE ACCESS FULL T1 (cr=4 pr=3 pw=0 time=297 us cost=2 size=0 card=100)'
WAIT #10: nam='SQL*Net message from client' ela= 432 driver id=1413697536 #bytes=1 p3=0 obj#=74630 tim=1724639876
FETCH #10:c=0,e=5,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=3724264953,tim=1724639958
WAIT #10: nam='SQL*Net message to client' ela= 7 driver id=1413697536 #bytes=1 p3=0 obj#=74630 tim=1724640017
*** 2012-04-01 16:20:50.203
WAIT #10: nam='SQL*Net message from client' ela= 41276 driver id=1413697536 #bytes=1 p3=0 obj#=74630 tim=1724681337
| 소스코드 | {code:SQL} – 1. OBJD 수집 SELECT FILE#, BLOCK#, CLASS#, OBJD AS DATA_OBJ_ID, STATUS FROM V$BH WHERE ((FILE# = 1 AND BLOCK# = 86576) OR (FILE# = 1 AND BLOCK# = 86577)) --AND STATUS = 'xcur' ORDER BY BLOCK# ; |
– 2. OBJECT 확인
SELECT OWNER,
OBJECT_NAME,
OBJECT_TYPE
FROM ALL_OBJECTS
WHERE DATA_OBJECT_ID IN (SELECT OBJD AS DATA_OBJ_ID
FROM V$BH
WHERE ((FILE# = 1 AND BLOCK# = 86576) OR
(FILE# = 1 AND BLOCK# = 86577))
--AND STATUS = 'xcur'
)
;
|
|수행내역|{code:SQL}
KENZO:xsoftdb:SYSTEM >
1 SELECT FILE#,
2 BLOCK#,
3 CLASS#,
4 OBJD AS DATA_OBJ_ID,
5 STATUS
6 FROM V$BH
7 WHERE ((FILE# = 1 AND BLOCK# = 86576) OR
8 (FILE# = 1 AND BLOCK# = 86577))
9 --AND STATUS = 'xcur'
10 ORDER BY BLOCK#
11 ;
FILE# BLOCK# CLASS# DATA_OBJ_ID STATUS
---------- ---------- ---------- ----------- ----------
1 86576 4 74625 free
1 86576 4 74630 free
1 86576 4 74629 free
1 86576 4 74629 free
1 86576 4 74625 free
1 86576 4 74630 free
1 86576 4 74629 free
1 86577 1 74630 free
1 86577 1 74630 free
1 86577 1 74629 free
1 86577 1 74629 free
11 개의 행이 선택되었습니다.
KENZO:xsoftdb:SYSTEM >
1 SELECT OWNER,
2 OBJECT_NAME,
3 OBJECT_TYPE
4 FROM ALL_OBJECTS
5 WHERE DATA_OBJECT_ID IN (SELECT OBJD AS DATA_OBJ_ID
6 FROM V$BH
7 WHERE ((FILE# = 1 AND BLOCK# = 86576) OR
8 (FILE# = 1 AND BLOCK# = 86577))
9 --AND STATUS = 'xcur'
10 )
11 ;
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -------------------
SYSTEM T1 TABLE
1 개의 행이 선택되었습니다.
|
| 소스코드 | {code:SQL} SELECT ROWNUM, CLASS FROM V$WAITSTAT ; {code} |
| 수행내역 | {code:SQL} KENZO:xsoftdb:SYSTEM > 1 SELECT ROWNUM, 2 CLASS 3 FROM V$WAITSTAT 4 ; |
ROWNUM CLASS
18 개의 행이 선택되었습니다.
|
* V$Bh 뷰 정의(REFERENCE site : http://docs.oracle.com/cd/B13789_01/server.101/b10755/dynviews_1035.htm)
|| COLUMN || DATATYPE || DESCRIPTION ||
| FILE# | NUMBER | Datafile identifier number (to find the filename, query DBA_DATA_FILES or V$DBFILE) |
| BLOCK# | NUMBER | Block NUMBER |
| CLASS# | NUMBER | Class number |
| STATUS | VARCHAR2(6) | Status of the buffer:
=> free - Not currently in use
=> xcur - Exclusive
=> scur - Shared current
=> cr - Consistent read
=> read - Being read from disk
=> mrec - In media recovery mode
=> irec - In instance recovery MODE |
| XNC | NUMBER | Number of PCM x to null lock conversions due to contention with another instance. This column is obsolete and maintained for backward compatibility. |
| LOCK_ELEMENT_ADDR | RAW(4,8) | Address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock. |
| LOCK_ELEMENT_NAME | NUMBER | The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock. |
| LOCK_ELEMENT_CLASS | NUMBER | The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock. |
| FORCED_READS | NUMBER | Number of times the block had to be reread from the cache because another instance has forced it out of this instances cache by requesting the lock on the block in exclusive mode |
| FORCED_WRITES | NUMBER | Number of times GCS had to write this block to cache because this instance had used the block and another instance had requested the lock on the block in a conflicting mode |
| DIRTY | VARCHAR2(1) | Y - block modified |
| TEMP | VARCHAR2(1) | Y - temporary block |
| PING | VARCHAR2(1) | Y - block pinged |
| STALE | VARCHAR2(1) | Y - block is stale |
| DIRECT | VARCHAR2(1) | Y - direct block |
| NEW | VARCHAR2(1) | Always set to N. This column is obsolete and maintained for backward compatibility. |
| OBJD | NUMBER | Database object number of the block that the buffer represents |
| TS# | NUMBER | Tablespace number of block |
* 만약, 해당하는 블록이 버퍼 캐시에서 이미 사라졌다면, V$BH 뷰로부터 정보를 얻는 것이 불가능함
* 이런 경우 블록 덤프(Block Dump) 기능을 이용해서 블록 정보를 트레이스 파일에 기록하고, 이 정보를 이용할 수 있음
|소스코드|{code:SQL}
ALTER SYSTEM DUMP DATAFILE 1 BLOCK 86576;
|
| 수행내역 | {code:SQL} KENZO:xsoftdb:SYSTEM > 1 ALTER SYSTEM DUMP DATAFILE 1 BLOCK 86576; |
시스템이 변경되었습니다.
|
* BLOCK DUMP 확인
{code:SQL}
Trace file c:\app\ken-zo\diag\rdbms\xsoftdb\xsoftdb\trace\xsoftdb_ora_4048.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows XP Version V5.1 Service Pack 3
CPU : 2 - type 586, 2 Physical Cores
Process Affinity : 0x0x00000000
Memory (Avail/Total): Ph:1576M/3069M, Ph+PgF:3158M/4960M, VA:943M/2047M
Instance name: xsoftdb
Redo thread mounted by this instance: 1
Oracle process number: 41
Windows thread id: 4048, image: ORACLE.EXE (SHAD)
*** 2012-04-01 17:29:28.187
*** SESSION ID:(146.360) 2012-04-01 17:29:28.187
*** CLIENT ID:() 2012-04-01 17:29:28.187
*** SERVICE NAME:(xsoftdb) 2012-04-01 17:29:28.187
*** MODULE NAME:(SQL*Plus) 2012-04-01 17:29:28.187
*** ACTION NAME:() 2012-04-01 17:29:28.187
Start dump data blocks tsn: 0 file#:1 minblk 86576 maxblk 86576
Block dump from cache:
Dump of buffer cache at level 4 for tsn=0, rdba=4280880
BH (0x297E4E24) file#: 1 rdba: 0x00415230 (1/86576) class: 4 ba: 0x2945E000
set: 6 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
dbwrid: 0 obj: 74630 objn: 74630 tsn: 0 afn: 1 hint: f
hash: [0x297D131C,0x3D7A9D60] lru: [0x2AFDA014,0x257EB15C]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x297D12A0) file#: 1 rdba: 0x00415230 (1/86576) class: 4 ba: 0x29164000
set: 5 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
dbwrid: 0 obj: 74630 objn: 74630 tsn: 0 afn: 1 hint: f
hash: [0x26FE9BA4,0x297E4EA0] lru: [0x26FE440C,0x267DBD10]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x26FE9B28) file#: 1 rdba: 0x00415230 (1/86576) class: 4 ba: 0x26D18000
set: 5 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
dbwrid: 0 obj: 74629 objn: 0 tsn: 0 afn: 1 hint: f
hash: [0x25FF9340,0x297D131C] lru: [0x25FF9370,0x297D1C68]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x25FF92C4) file#: 1 rdba: 0x00415230 (1/86576) class: 4 ba: 0x25F6E000
set: 5 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
dbwrid: 0 obj: 74629 objn: 74629 tsn: 0 afn: 1 hint: f
hash: [0x25FCFF3C,0x26FE9BA4] lru: [0x25FE2B34,0x26FE9BD4]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x25FCFEC0) file#: 1 rdba: 0x00415230 (1/86576) class: 4 ba: 0x25934000
set: 6 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
dbwrid: 0 obj: 74629 objn: 74629 tsn: 0 afn: 1 hint: f
hash: [0x25FDBF5C,0x25FF9340] lru: [0x25FD0CAC,0x2A7E6D74]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x25FDBEE0) file#: 1 rdba: 0x00415230 (1/86576) class: 4 ba: 0x25B04000
set: 6 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
dbwrid: 0 obj: 74625 objn: 0 tsn: 0 afn: 1 hint: f
hash: [0x25FF4070,0x25FCFF3C] lru: [0x117E1D20,0x27FDFE7C]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x25FF3FF4) file#: 1 rdba: 0x00415230 (1/86576) class: 4 ba: 0x25EA6000
set: 5 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
dbwrid: 0 obj: 74625 objn: 0 tsn: 0 afn: 1 hint: f
hash: [0x3D7A9D60,0x25FDBF5C] lru: [0x117C80B8,0x2D7D8170]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 0 rdba: 0x00415230 (1/86576)
scn: 0x0000.0014d8b1 seq: 0x01 flg: 0x04 tail: 0xd8b11001
frmt: 0x02 chkval: 0x97d6 type: 0x10=DATA SEGMENT HEADER - UNLIMITED
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x40B40200 to 0x40B42200
40B40200 0000A210 00415230 0014D8B1 04010000 [....0RA.........]
40B40210 000097D6 00000000 00000000 00000000 [................]
40B40220 00000000 00000001 00000007 00001020 [............ ...]
40B40230 00000000 00000002 00000007 00415233 [............3RA.]
40B40240 00000000 00000000 00000000 00000002 [................]
40B40250 00000000 00000000 00000000 00000001 [................]
40B40260 00000000 00012386 40000000 00415231 [.....#.....@1RA.]
40B40270 00000007 00000000 00000000 00000000 [................]
40B40280 00000000 00000000 00000000 00000000 [................]
Repeat 250 times
40B41230 00000000 00010000 00010001 00000000 [................]
40B41240 00000000 00000000 00000000 00000000 [................]
Repeat 250 times
40B421F0 00000000 00000000 00000000 D8B11001 [................]
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 7
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x00415233 ext#: 0 blk#: 2 ext size: 7
#blocks in seg. hdrs freelists: 0
#blocks below: 2
mapblk 0x00000000 offset: 0
Unlocked
Map Header:: next 0x00000000 #extents: 1 obj#: 74630 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x00415231 length: 7
nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 0
SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
End dump data blocks tsn: 0 file#: 1 minblk 86576 maxblk 86576
| 소스코드 | {code:SQL} ALTER SYSTEM DUMP DATAFILE 1 BLOCK 86577; {code} |
| 수행내역 | {code:SQL} KENZO:xsoftdb:SYSTEM > 1 ALTER SYSTEM DUMP DATAFILE 1 BLOCK 86577; |
시스템이 변경되었습니다.
|
* 블록 덤프 확인
{code:SQL}
Block header dump: 0x00415231
Object id on Block? Y
seg/obj: 0x12386 csc: 0x00.14d8ac itc: 3 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.0014d8ac
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
| 소스코드 | {code:SQL} SELECT TO_DEC('12386') FROM DUAL; {code} |
| 수행내역 | {code:SQL} KENZO:xsoftdb:SYSTEM > 1 SELECT TO_DEC('12386') FROM DUAL; |
TO_DEC('12386')
1 개의 행이 선택되었습니다.
|
h1. 2. Enqueue 관련 대기 이벤트
h3. 1) Enqueue 대기 이벤트 소개
* Enqueue(또는 간략하게 Lock)는 테이블이나 로우와 같은 데이터베이스의 오브젝트를 보호하는 동기화 객체
* 특정 오브젝트를 변경하기 위해서는 반드시 해당 오브젝트를 보호하는 Enqueue를 획득해야 하며, 다른 세션이 해당 오브젝트를 변경 중이어서 Enqueue를 획득하지 못하는 경우 'enq:XXX'류의 이벤트를 대기함
* V$EVENT 뷰에서 Enqueue와 관련된 대기 이벤트는 아래와 같음
|소스코드|{code:SQL}
SELECT NAME
FROM V$EVENT_NAME
WHERE NAME LIKE 'enq:%'
;
|
| 수행내역 | {code:SQL} SQL > 1 SELECT NAME 2 FROM V$EVENT_NAME 3 WHERE NAME LIKE 'enq:%' 4 ; |
NAME
...(중략)...
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를 획득하지 못해서 대기한다는 것을 의미함
h3. 2) V$LOCK_TYPE
* Enqueue 관련 대기 이벤트를 해석하려면 Enqueue 자체에 대한 정보를 알아야 하는데, V$LOCK_TYPE 뷰를 사용하면 확인 가능함
{code:SQL}
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 에 대한 상세한 설명 |
| 소스코드 | {code:SQL} BEGIN PRINT_TABLE('SELECT * FROM V$LOCK_TYPE WHERE TYPE = ''TM'''); END; {code} |
| 수행내역 | {code:SQL} 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.
|
h3. 3) V$LOCK
* V$LOCK_TYPE 뷰에서 제공하는 내용을 이해하면, V$LOCK 뷰를 손쉽게 사용할 수 있음
* V$LOCK 뷰는 특정 세션이 현재 획득하고 있거나 획득하기 위해 대기하고 있는 Enqueue의 목록을 보여줌
* V$LOCK 뷰는 Enqueue 관련 대기 이벤트를 분석하는데 있어서 가장 기본적이고 즁요한 뷰임
{code:SQL}
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 |
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 | 현재 세션을 블로킹하고 있는 세선의 세션 아이디 |
| 소스코드 | {code:SQL} – 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;
|
|수행내역|{code:SQL}
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;
|
| 소스코드 | {code:SQL} EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_2'); |
UPDATE T1 SET C1 = 1;
|
|수행내역|{code:SQL}
SQL >
1 EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_2');
PL/SQL procedure successfully completed.
SQL >
1 UPDATE T1 SET C1 = 1;
|
| 소스코드 | {code:SQL} EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_3'); |
UPDATE T1 SET C1 = 1;
|
|수행내역|{code:SQL}
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.
|
| 소스코드 | {code:SQL} – 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
;
|
|수행내역|{code:SQL}
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.
|