3. Library Cache Lock 관련 대기 이벤트
4. Library Cache Pin 관련 대기 이벤트
| 소스코드 | {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.
|
DESC SYS.X$KGLLK
Name Null? Type
------------ -------- -------------------
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
KGLLKADR RAW(8)
KGLLKUSE RAW(8)
KGLLKSES RAW(8)
KGLLKSNM NUMBER
KGLLKHDL RAW(8)
KGLLKPNC RAW(8)
KGLLKPNS RAW(8)
KGLLKCNT NUMBER
KGLLKMOD NUMBER
KGLLKREQ NUMBER
KGLLKFLG NUMBER
KGLLKSPN NUMBER
KGLLKHTB RAW(8)
KGLNAHSH NUMBER
KGLLKSQLID VARCHAR2(13)
KGLHDPAR RAW(8)
KGLHDNSP NUMBER
USER_NAME VARCHAR2(30)
KGLNAOBJ VARCHAR2(60)
;
SELECT KGLLKUSE,
KGLLKHDL,
KGLNAOBJ
FROM SYS.X$KGLLK
WHERE ROWNUM <= 10
;
KGLLKUSE KGLLKHDL KGLNAOBJ
---------------- ---------------- --------------------------------------------------------------
07000002C0C6E760 07000002A0FD4788 select priority from resource_mapping_priority$ where attrib
07000002C0C6E760 07000002A0FA7C90 select priority from resource_mapping_priority$ where attrib
07000002C0C6E760 070000029EF55060 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.fla
07000002C0C6E760 070000029EF8A408 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.fla
07000002C0C6E760 070000029DF6F3C8 select value, consumer_group from resource_group_mapping$ wh
07000002C0C6E760 070000029DF8E930 select value, consumer_group from resource_group_mapping$ wh
07000002C0C6E760 07000002A0FE9490 select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oi
07000002C0C6E760 07000002A0FC1070 select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oi
07000002C0C6E760 07000002A0FD3918 select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
07000002C0C6E760 07000002A0FC0EF0 select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
| COLUMN | TYPE | DESCRIPTION |
|---|---|---|
| ADDR | RAW(4) | address of this row/entry in the array or SGA |
| INDX | NUMBER | index number of this row in the fixed table array |
| INST_ID | NUMBER | 8.x oracle instance number |
| KGLLKADR | RAW(4) | address of the lock |
| KGLLKUSE | RAW(4) | user session holding the lock. VIEW:X$KSUSE ADDR |
| KGLLKSES | RAW(4) | session holding the lock |
| KGLLKSNM | NUMBER | session number of session holding lock |
| KGLLKHDL | RAW(4) | object handle for the lock |
| KGLLKPNC | RAW(4) | pin for the object for the call |
| KGLLKPNS | RAW(4) | pin for the object for the session |
| KGLLKCNT | NUMBER | reference count |
| KGLLKMOD | NUMBER | lock mode held => 1 : NULL => 2 : S => 3 : X => ELSE : Mode |
| KGLLKREQ | NUMBER | lock mode requested => 1 : NULL => 2 : S => 3 : X => ELSE : Req |
| KGLLKFLG | NUMBER | status flags => KGLLKBRO 0x01 this lock is broken => KGLLKCBB 0x02 this lock can be broken, see Note 1 => KGLLKPNC 0x04 "kgllkpnc" is a valid pin for the call => KGLLKPNS 0x08 "kgllkpns" is a valid pin for the session => KGLLKCGA 0x10 this lock is in CGA memory |
| KGLLKSPN | NUMBER | savepoint number when lock acquired |
| KGLNAHSH | NUMBER | hash val. of obj. locked |
| KGLHDPAR | RAW(4) | address of parent handle or self, if none |
| KGLHDNSP | NUMBER | namespace of obj. locked |
| USER_NAME | VARCHAR2(30) | user name of the session owner |
| KGLNAOBJ | VARCHAR2(60) | name of object |
| 소스코드 | {code:SQL} – 1. 테이블 1억건 생성 EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_1'); |
DROP TABLE XSOFT_T;
CREATE TABLE XSOFT_T AS
SELECT TO_CHAR(LEVEL) CNT
FROM DUAL
CONNECT BY LEVEL <= 10000
;
DROP TABLE XSOFT_T2;
CREATE TABLE XSOFT_T2(CNT VARCHAR2(100))
;
ALTER SESSION ENABLE PARALLEL DML ;
INSERT /*+ APPEND */
INTO XSOFT_T2
SELECT /*+ FULL(A) PARALLEL(A 8) FULL(B) PARALLEL(B 8) */
A.CNT + B.CNT
FROM XSOFT_T A,
XSOFT_T B
;
– 2. MODIFY 수행
ALTER TABLE XSOFT_T2 MODIFY (CNT VARCHAR2(3000));
ALTER TABLE XSOFT_T2 MODIFY (CNT VARCHAR2(100));
|
|수행내역|{code:SQL}
SQL >
1 EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_1');
PL/SQL procedure successfully completed.
SQL >
1 DROP TABLE XSOFT_T;
Table dropped.
SQL >
1 CREATE TABLE XSOFT_T AS
2 SELECT TO_CHAR(LEVEL) CNT
3 FROM DUAL
4 CONNECT BY LEVEL <= 10000
5 ;
Table created.
SQL >
1 DROP TABLE XSOFT_T2;
Table dropped.
SQL >
1 CREATE TABLE XSOFT_T2(CNT VARCHAR2(100))
2 ;
Table created.
SQL >
1 ALTER SESSION ENABLE PARALLEL DML ;
Session altered.
SQL >
1 INSERT /*+ APPEND */
2 INTO XSOFT_T2
3 SELECT /*+ FULL(A) PARALLEL(A 8) FULL(B) PARALLEL(B 8) */
4 A.CNT + B.CNT
5 FROM XSOFT_T A,
6 XSOFT_T B
7 ;
100000000 rows created.
SQL >
1 ALTER TABLE XSOFT_T2 MODIFY (CNT VARCHAR2(3000));
Table altered.
SQL >
1 ALTER TABLE XSOFT_T2 MODIFY (CNT VARCHAR2(100));
|
| 소스코드 | {code:SQL} EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_2'); |
UPDATE XSOFT_T2 SET CNT = 10000000;
|
|수행내역|{code:SQL}
SQL >
1 EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_2');
PL/SQL procedure successfully completed.
SQL >
1 UPDATE XSOFT_T2 SET CNT = 10000000;
|
| 소스코드 | {code:SQL} – 1. V$SESSION_WAIT 뷰로 SESSION #2 모니터링 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. X$KGLLK 뷰를 통해 확인
col kglnaobj format a10
col lkmode format a10
col sql_text format a30
SELECT H.SID AS H_SID,
SUBSTR(S.SQL_TEXT, 1, 40) AS SQL_TEXT,
K.KGLHDNSP,
K.KGLNAOBJ,
DECODE(K.KGLLKMOD, 3, '3(X)',
2, '2(S)',
1, '1(N)',
K.KGLLKMOD) AS LKMODE
FROM SYS.X$KGLLK K,
V$SESSION_WAIT W,
V$SESSION H,
V$SQLAREA S
WHERE H.SADDR = K.KGLLKUSE
AND H.SQL_ID = S.SQL_ID(+)
AND W.SID = &SID
AND W.EVENT = 'library cache lock'
AND K.KGLLKHDL = (CASE WHEN W.P1RAW LIKE '00000000%' THEN SUBSTR(W.P1RAW, 9, 8)
ELSE W.P1RAW || ''
END)
AND K.KGLLKMOD > 0 -- 이 조건에서 데이터 필터링이 되서 한건도 안나옴(값은 0임)
;
– 3. @LIBLOCK 스크립트로 확인
-- 이 스크립트는 X$KGLLK를 바라보지 않고 V$SESSION에서 Waiting Session과 Blocking Session(Node 포함)을 한번에 찾음
set linesize 300
col sid heading "Sid,Ser#|Spid" format a13
col module heading "Module|Program" format a25
col w_time format 99999
col node format a5
col holder format a15
col sql_text format a35
col p1 heading "s.p1|s.p1Text" format a25
col p2 heading "P2|P2Text" format a25
col p3 heading "P3|P3Text" format a25
col kill_script format a50
col kill_script2 format a20
SELECT /*+ ordered use_nl( sw s p) use_hash( a b s2 ) */
S.SID || ',' || S.SERIAL# AS SID,
S.MODULE AS MODULE,
S.SECONDS_IN_WAIT W_TIME,
TO_CHAR(S.BLOCKING_INSTANCE) NODE,
TO_CHAR(S.BLOCKING_SESSION) HOLDER,
(SELECT SUBSTR(SQL_TEXT, 1, 65)
FROM V$SQL SQ
WHERE SQ.ADDRESS = S.SQL_ADDRESS
AND SQ.HASH_VALUE = S.SQL_HASH_VALUE
AND ROWNUM = 1) AS SQL_TEXT,
NVL(S.P1TEXT, 'Null') || CHR(10) || NVL(TO_CHAR(S.P1), 'Null') P1,
NVL(S.P2TEXT, 'Null') || CHR(10) || NVL(TO_CHAR(S.P2), 'Null') P2,
NVL(S.P3TEXT, 'Null') || CHR(10) || NVL(TO_CHAR(S.P3), 'Null') P3,
'alter system kill session ' || '''' || S.SID || ',' || S.SERIAL# || '''' ||
' ; ' KILL_SCRIPT,
'kill -9 ' || P.SPID KILL_SCRIPT2
FROM V$SESSION S,
V$PROCESS P
WHERE S.PADDR = P.ADDR
AND S.EVENT = 'library cache lock'
ORDER BY S.SECONDS_IN_WAIT
/
|
|수행내역|{code:SQL}
SQL >
1 col sid new_value sid
SQL >
1 SELECT SID
2 FROM V$SESSION
3 WHERE CLIENT_INFO = 'SESSION_2'
4 ;
SID
----------
9875
1 row selected.
SQL >
1 BEGIN
2 PRINT_TABLE('SELECT *
3 FROM V$SESSION_WAIT
4 WHERE SID = &SID');
5 END;
6 /
SID : 9857
SEQ# : 6
EVENT : library cache lock
P1TEXT : handle address
P1 : 504403169284868160
P1RAW : 0700000290CE4840
P2TEXT : lock address
P2 : 504403169440959360
P2RAW : 070000029A1C0B80
P3TEXT : 100*mode+namespace
P3 : 201
P3RAW : 00000000000000C9
WAIT_CLASS_ID : 3875070507
WAIT_CLASS# : 4
WAIT_CLASS : Concurrency
WAIT_TIME : 0
SECONDS_IN_WAIT : 6
STATE : WAITING
---------------------------------------------------------------------------
PL/SQL procedure successfully completed.
SQL >
1 col kglnaobj format a10
SQL >
1 col lkmode format a10
SQL >
1 col sql_text format a30
SQL >
1 SELECT H.SID AS H_SID,
2 SUBSTR(S.SQL_TEXT, 1, 40) AS SQL_TEXT,
3 K.KGLHDNSP,
4 K.KGLNAOBJ,
5 DECODE(K.KGLLKMOD, 3, '3(X)',
6 2, '2(S)',
7 1, '1(N)',
8 K.KGLLKMOD) AS LKMODE
9 FROM SYS.X$KGLLK K,
10 V$SESSION_WAIT W,
11 V$SESSION H,
12 V$SQLAREA S
13 WHERE H.SADDR = K.KGLLKUSE
14 AND H.SQL_ID = S.SQL_ID(+)
15 AND W.SID = &SID
16 AND W.EVENT = 'library cache lock'
17 AND K.KGLLKHDL = (CASE WHEN W.P1RAW LIKE '00000000%' THEN SUBSTR(W.P1RAW, 9, 8)
18 ELSE W.P1RAW || ''
19 END)
20 AND K.KGLLKMOD > 0 -- 이 조건에서 데이터 필터링이 되서 한건도 안나옴(값은 0임)
21 ;
no rows selected
SQL >
1 set linesize 300
SQL >
1 col sid heading "Sid,Ser#|Spid" format a13
SQL >
1 col module heading "Module|Program" format a25
SQL >
1 col w_time format 99999
SQL >
1 col node format a5
SQL >
1 col holder format a15
SQL >
1 col sql_text format a35
SQL >
1 col p1 heading "s.p1|s.p1Text" format a25
SQL >
1 col p2 heading "P2|P2Text" format a25
SQL >
1 col p3 heading "P3|P3Text" format a25
SQL >
1 col kill_script format a50
SQL >
1 col kill_script2 format a20
SQL >
1
SQL >
1 SELECT /*+ ordered use_nl( sw s p) use_hash( a b s2 ) */
2 S.SID || ',' || S.SERIAL# AS SID,
3 S.MODULE AS MODULE,
4 S.SECONDS_IN_WAIT W_TIME,
5 TO_CHAR(S.BLOCKING_INSTANCE) NODE,
6 TO_CHAR(S.BLOCKING_SESSION) HOLDER,
7 (SELECT SUBSTR(SQL_TEXT, 1, 65)
8 FROM V$SQL SQ
9 WHERE SQ.ADDRESS = S.SQL_ADDRESS
10 AND SQ.HASH_VALUE = S.SQL_HASH_VALUE
11 AND ROWNUM = 1) AS SQL_TEXT,
12 NVL(S.P1TEXT, 'Null') || CHR(10) || NVL(TO_CHAR(S.P1), 'Null') P1,
13 NVL(S.P2TEXT, 'Null') || CHR(10) || NVL(TO_CHAR(S.P2), 'Null') P2,
14 NVL(S.P3TEXT, 'Null') || CHR(10) || NVL(TO_CHAR(S.P3), 'Null') P3,
15 'alter system kill session ' || '''' || S.SID || ',' || S.SERIAL# || '''' ||
16 ' ; ' KILL_SCRIPT,
17 'kill -9 ' || P.SPID KILL_SCRIPT2
18 FROM V$SESSION S,
19 V$PROCESS P
20 WHERE S.PADDR = P.ADDR
21 AND S.EVENT = 'library cache lock'
22 ORDER BY S.SECONDS_IN_WAIT
23 /
-- V$SESSION 에서 BLOCKING_SESSION 값과 BLOCKING_INSTANCE 값 확인 가능
Sid,Ser# Module s.p1 P2 P3
Spid Program W_TIME NODE HOLDER SQL_TEXT s.p1Text P2Text P3Text KILL_SCRIPT KILL_SCRIPT2
------------- ---------- ------ ----- --------------- ----------------------------------- ------------------------- ------------------------- ------------------------- -------------------------------------------------- --------------------
9857,6 SQL*Plus 5 1 9906 UPDATE XSOFT_T2 SET CNT = 10000000 handle address lock address 100*mode+namespace alter system kill session '9857,6' ; kill -9 1228864
504403169284868160 504403169323693832 201
1 row selected.
|
DESC SYS.X$KGLPN
Name Null? Type
------------ -------- ---------
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
KGLPNADR RAW(8)
KGLPNUSE RAW(8)
KGLPNSES RAW(8)
KGLPNHDL RAW(8)
KGLPNLCK RAW(8)
KGLPNCNT NUMBER
KGLPNMOD NUMBER
KGLPNREQ NUMBER
KGLPNDMK NUMBER
KGLPNSPN NUMBER
;
SELECT KGLPNUSE,
KGLPNHDL
FROM SYS.X$KGLPN
WHERE ROWNUM <= 10
;
KGLPNUSE KGLPNHDL
---------------- ----------------
07000002C1C5A170 070000029EF91DB0
07000002C1C5A170 0700000298F48A30
07000002C1C5A170 070000029FD3C350
| KGLPNUSE | Library Cache Pin 을 획득하거나 대기하고 있는 세션의 주소. V$SESSION.SADDR값과 조인하면 세션 정보를 얻을 수 있음 |
| KGLPNHDL | Library Cache Lock 의 핸들 주소. V$SESSIONJ_WAIT.PlRAW 값과 조인하면 대기이벤트 정보를 얻을 수 있음 |
| 소스코드 | {code:SQL} – TEST_KGLLK 프로시저 생성 CREATE OR REPLACE PROCEDURE TEST_KGLLK (L_SLEEP IN BOOLEAN , L_COMPILE IN BOOLEAN) AS BEGIN IF (L_SLEEP ) THEN SYS.DBMS_LOCK.SLEEP(60); ELSIF (L_COMPILE ) THEN EXECUTE IMMEDIATE 'ALTER PROCEDURE TEST_KGLLK COMPILE'; END IF; END; / |
-- 수행
EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_1');
EXEC TEST_KGLLK ( TRUE, FALSE);
|
|수행내역|{code:SQL}
SQL >
1 CREATE OR REPLACE PROCEDURE TEST_KGLLK (L_SLEEP IN BOOLEAN , L_COMPILE IN BOOLEAN)
2 AS
3 BEGIN
4 IF (L_SLEEP ) THEN
5 SYS.DBMS_LOCK.SLEEP(60);
6 ELSIF (L_COMPILE ) THEN
7 EXECUTE IMMEDIATE 'ALTER PROCEDURE TEST_KGLLK COMPILE';
8 END IF;
9 END;
10 /
Procedure created.
SQL >
1 EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_1');
PL/SQL procedure successfully completed.
SQL >
1 EXEC TEST_KGLLK ( TRUE, FALSE);
PL/SQL procedure successfully completed.
|
| 소스코드 | {code:SQL} EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_2'); EXEC TEST_KGLLK ( FALSE, TRUE); {code} |
| 수행내역 | {code:SQL} SQL > 1 EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_2'); |
PL/SQL procedure successfully completed.
SQL >
1 EXEC TEST_KGLLK ( TRUE, FALSE);
|
h5. 2-3) Sesssion #3
* 세션_3에서 세션_2 모니터링
|소스코드|{code:SQL}
-- 1. Library Cache Pin 세션 모니터링
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. @LIBPIN 스크립트 사용
-- 이상한것은 책에서는 BLOCKING_SESSION을 찾을 수 있다고 했지만, 자기 SESSION_ID 밖에 못 찾음
set linesize 300
col sid heading "Sid,Ser#|Spid" format a13
col module heading "Module|Program" format a25
col w_time format 99999
col node format a5
col holder format a15
col sql_text format a35
col p1 heading "s.p1|s.p1Text" format a25
col p2 heading "P2|P2Text" format a25
col p3 heading "P3|P3Text" format a25
col kill_script format a50
col kill_script2 format a20
SELECT /*+ ordered use_nl( sw s p) use_hash( a b s2 ) */
S.SID || ',' || S.SERIAL# AS SID,
S.MODULE AS MODULE,
S.SECONDS_IN_WAIT W_TIME,
TO_CHAR(S.BLOCKING_INSTANCE) NODE,
TO_CHAR(S.BLOCKING_SESSION) HOLDER,
(SELECT SUBSTR(SQL_TEXT, 1, 65)
FROM V$SQL SQ
WHERE SQ.ADDRESS = S.SQL_ADDRESS
AND SQ.HASH_VALUE = S.SQL_HASH_VALUE
AND ROWNUM = 1) AS SQL_TEXT,
NVL(S.P1TEXT, 'Null') || CHR(10) || NVL(TO_CHAR(S.P1), 'Null') P1,
NVL(S.P2TEXT, 'Null') || CHR(10) || NVL(TO_CHAR(S.P2), 'Null') P2,
NVL(S.P3TEXT, 'Null') || CHR(10) || NVL(TO_CHAR(S.P3), 'Null') P3,
'alter system kill session ' || '''' || S.SID || ',' || S.SERIAL# || '''' ||
' ; ' KILL_SCRIPT,
'kill -9 ' || P.SPID KILL_SCRIPT2
FROM V$SESSION S,
V$PROCESS P
WHERE S.PADDR = P.ADDR
AND S.EVENT = 'library cache pin'
ORDER BY S.SECONDS_IN_WAIT
/
|
| 수행내역 | {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
1 row selected.
SQL >
1 BEGIN
2 PRINT_TABLE('SELECT *
3 FROM V$SESSION_WAIT
4 WHERE SID = &SID');
5 END;
6 /
SID : 9937
SEQ# : 81
EVENT : library cache pin
P1TEXT : handle address
P1 : 504403169386942624
P1RAW : 0700000296E3D0A0
P2TEXT : pin address
P2 : 504403169480899360
P2RAW : 070000029C7D7B20
P3TEXT : 100*mode+namespace
P3 : 301
P3RAW : 000000000000012D
WAIT_CLASS_ID : 3875070507
WAIT_CLASS# : 4
WAIT_CLASS : Concurrency
WAIT_TIME : 0
SECONDS_IN_WAIT : 6
STATE : WAITING
PL/SQL procedure successfully completed.
SQL >
1 set linesize 300
SQL >
1 col sid heading "Sid,Ser#|Spid" format a13
SQL >
1 col module heading "Module|Program" format a25
SQL >
1 col w_time format 99999
SQL >
1 col node format a5
SQL >
1 col holder format a15
SQL >
1 col sql_text format a35
SQL >
1 col p1 heading "s.p1|s.p1Text" format a25
SQL >
1 col p2 heading "P2|P2Text" format a25
SQL >
1 col p3 heading "P3|P3Text" format a25
SQL >
1 col kill_script format a50
SQL >
1 col kill_script2 format a20
SQL >
1
SQL >
1 SELECT /*+ ordered use_nl( sw s p) use_hash( a b s2 ) */
2 S.SID || ',' || S.SERIAL# AS SID,
3 S.MODULE AS MODULE,
4 S.SECONDS_IN_WAIT W_TIME,
5 TO_CHAR(S.BLOCKING_INSTANCE) NODE,
6 TO_CHAR(S.BLOCKING_SESSION) HOLDER,
7 (SELECT SUBSTR(SQL_TEXT, 1, 65)
8 FROM V$SQL SQ
9 WHERE SQ.ADDRESS = S.SQL_ADDRESS
10 AND SQ.HASH_VALUE = S.SQL_HASH_VALUE
11 AND ROWNUM = 1) AS SQL_TEXT,
12 NVL(S.P1TEXT, 'Null') || CHR(10) || NVL(TO_CHAR(S.P1), 'Null') P1,
13 NVL(S.P2TEXT, 'Null') || CHR(10) || NVL(TO_CHAR(S.P2), 'Null') P2,
14 NVL(S.P3TEXT, 'Null') || CHR(10) || NVL(TO_CHAR(S.P3), 'Null') P3,
15 'alter system kill session ' || '''' || S.SID || ',' || S.SERIAL# || '''' ||
16 ' ; ' KILL_SCRIPT,
17 'kill -9 ' || P.SPID KILL_SCRIPT2
18 FROM V$SESSION S,
19 V$PROCESS P
20 WHERE S.PADDR = P.ADDR
21 AND S.EVENT = 'library cache pin'
22 ORDER BY S.SECONDS_IN_WAIT
23 /
– BLOCKING_SESSION 값과 SID 값이 동일함 _;
Sid,Ser# Module s.p1 P2 P3
Spid Program W_TIME NODE HOLDER SQL_TEXT s.p1Text P2Text P3Text KILL_SCRIPT KILL_SCRIPT2
1 row selected.
|
h1. 5. Row Cache Lock 관련 대기 이벤트
h3. 1) Row Cache Lock 대기 이벤트 소개
* Row Cache Lock 은 딕셔너리 오브젝트(Dictionary Object)를 보호하는 시스템 락
* 딕셔너리 오브젝트란 딕셔너리 캐시 (Dictionary Cache) 영역에 저장되는 모든 종류의 공유 오브젝트를 의미함
* 유저, 테이블, 인텍스, 시권스, 컬럼, 함수, 프로시저 등 SQL을 실행하기 위해 펼요한 모든 딕셔너리 오브젝트들은 반드시 딕셔너리 캐시를 통해 액세스하게 됨
* Row Cache Lock 은 여러 세션이 동시에 동일한 딕셔너리 오브젝트를 변경하는 것을 방지하기 위해 사용되며, 획득하지 못한 경우 이 이벤트 대기함
* 이 이벤트는 V$SESSION에서 통합적인 정보 취득 가능하며, 상세 정보는 V$ROWCACHE_PARENT 뷰에서 확인 가능
{code:SQL}
DESC V$ROWCACHE_PARENT
Name Null? Type
------------------ -------- -----------------
INDX NUMBER
HASH NUMBER
ADDRESS RAW(8)
CACHE# NUMBER
CACHE_NAME VARCHAR2(64)
EXISTENT VARCHAR2(1)
LOCK_MODE NUMBER
LOCK_REQUEST NUMBER
TXN RAW(8)
SADDR RAW(8)
INST_LOCK_REQUEST NUMBER
INST_LOCK_RELEASE NUMBER
INST_LOCK_TYPE VARCHAR2(2)
INST_LOCK_ID1 RAW(4)
INST_LOCK_ID2 RAW(4)
KEY RAW(100)
;
SELECT CACHE#,
CACHE_NAME,
LOCK_MODE,
LOCK_REQUEST,
SADDR
FROM V$ROWCACHE_PARENT
WHERE ROWNUM <= 10
;
CACHE# CACHE_NAME LOCK_MODE LOCK_REQUEST SADDR
------ ------------------- ---------- ------------ ----------------
0 dc_tablespaces 0 0 00
0 dc_tablespaces 0 0 00
0 dc_tablespaces 0 0 00
0 dc_tablespaces 0 0 00
0 dc_tablespaces 0 0 00
0 dc_tablespaces 0 0 00
0 dc_tablespaces 0 0 00
0 dc_tablespaces 0 0 00
0 dc_tablespaces 0 0 00
0 dc_tablespaces 0 0 00
| CACHE# | 캐시 번호 |
| CACHE_NAME | 캐시 이름 예) DC SEQUENCE |
| LOCK_MODE | 해당 딕셔서리 오브젝트에 대해 Row Cache Lock 을 획득하고 있는 모드 |
| LOCK_REQUEST | 해당 딕셔너리 오브젝트에 대해 Row Cache Lock 을 획득하기 위해 대기하는 모드 |
| SADDR | Row Cache Lock 을 획득하거나 대기하고 있는 세션의 주소 V$SESSION.SADDR 값과 조인하면 세션 정보를 얻을 수 있음 |
| 소스코드 | {code:SQL} – 1. Sequence 생성 CREATE SEQUENCE SEQ1 NOCACHE ; |
– 2. 수행
EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_1');
BEGIN
FOR IDX IN 1..100000 LOOP
FOR R IN (SELECT SEQ1.NEXTVAL
FROM DUAL) LOOP
NULL;
END LOOP;
END LOOP;
END;
/
|
|수행내역|{code:SQL}
SQL >
1 CREATE SEQUENCE SEQ1 NOCACHE ;
Sequence created.
SQL >
1 EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_1');
PL/SQL procedure successfully completed.
SQL >
1 BEGIN
2 FOR IDX IN 1..100000 LOOP
3 FOR R IN (SELECT SEQ1.NEXTVAL
4 FROM DUAL) LOOP
5 NULL;
6 END LOOP;
7 END LOOP;
8 END;
9 /
PL/SQL procedure successfully completed.
|
| 소스코드 | {code:SQL} EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_2'); |
BEGIN
FOR IDX IN 1..100000 LOOP
FOR R IN (SELECT SEQ1.NEXTVAL
FROM DUAL) LOOP
NULL;
END LOOP;
END LOOP;
END;
/
|
|수행내역|{code:SQL}
SQL >
1 CREATE SEQUENCE SEQ1 NOCACHE ;
Sequence created.
SQL >
1 EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_2');
PL/SQL procedure successfully completed.
SQL >
1 BEGIN
2 FOR IDX IN 1..100000 LOOP
3 FOR R IN (SELECT SEQ1.NEXTVAL
4 FROM DUAL) LOOP
5 NULL;
6 END LOOP;
7 END LOOP;
8 END;
9 /
PL/SQL procedure successfully completed.
|
| 소스코드 | {code:SQL} – 1. Row Cache Lock 세션 모니터링 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. 상세 모니터링
col cache_name format a15
SELECT (SELECT SID
FROM V$SESSION
WHERE SADDR = H.SADDR) AS HOLDER_SID,
H.CACHE_NAME,
H.LOCK_MODE
FROM V$ROWCACHE_PARENT H,
V$ROWCACHE_PARENT W,
V$SESSION S
WHERE H.ADDRESS = W.ADDRESS
AND W.SADDR = S.SADDR
AND S.SID = &SID
AND H.LOCK_MODE > 0
;
|
|수행내역|{code:SQL}
SQL >
1 SELECT SID
2 FROM V$SESSION
3 WHERE CLIENT_INFO = 'SESSION_2';
SID
----------
9623
1 row selected.
SQL >
1 BEGIN
2 PRINT_TABLE('SELECT *
3 FROM V$SESSION_WAIT
4 WHERE SID = &SID');
5 END;
6 /
SID : 9623
SEQ# : 105
EVENT : row cache lock
P1TEXT : cache id
P1 : 13
P1RAW : 000000000000000D
P2TEXT : mode
P2 : 0
P2RAW : 00
P3TEXT : request
P3 : 5
P3RAW : 0000000000000005
WAIT_CLASS_ID : 3875070507
WAIT_CLASS# : 4
WAIT_CLASS : Concurrency
WAIT_TIME : 0
SECONDS_IN_WAIT : 19
STATE : WAITING
---------------------------------------------------------------------------
PL/SQL procedure successfully completed.
SQL >
1 col cache_name format a15
SQL >
1 SELECT (SELECT SID
2 FROM V$SESSION
3 WHERE SADDR = H.SADDR) AS HOLDER_SID,
4 H.CACHE_NAME,
5 H.LOCK_MODE
6 FROM V$ROWCACHE_PARENT H,
7 V$ROWCACHE_PARENT W,
8 V$SESSION S
9 WHERE H.ADDRESS = W.ADDRESS
10 AND W.SADDR = S.SADDR
11 AND S.SID = &SID
12 AND H.LOCK_MODE > 0
13 ;
HOLDER_SID CACHE_NAME LOCK_MODE
---------- --------------- ----------
9889 dc_sequences 5
1 row selected.
|
| 소스코드 | {code:SQL} EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_1'); |
DECLARE
v_cnt NUMBER;
BEGIN
FOR IDX IN 1..100 LOOP
FOR R IN (SELECT TABLE_NAME
FROM DBA_TABLES) LOOP
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*)
INTO V_CNT
FROM ' || R.TABLE_NAME || '
WHERE 1 = 0';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END LOOP;
END;
/
|
|수행내역|{code:SQL}
SQL >
1 EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_1');
PL/SQL procedure successfully completed.
SQL >
1 DECLARE
2 v_cnt NUMBER;
3 BEGIN
4 FOR IDX IN 1..100 LOOP
5 FOR R IN (SELECT TABLE_NAME
6 FROM DBA_TABLES) LOOP
7 BEGIN
8 EXECUTE IMMEDIATE 'SELECT COUNT(*)
9 INTO V_CNT
10 FROM ' || R.TABLE_NAME || '
11 WHERE 1 = 0';
12 EXCEPTION
13 WHEN OTHERS THEN
14 NULL;
15 END;
16 END LOOP;
17 END LOOP;
18 END;
19 /
|
| 소스코드 | {code:SQL} EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_2'); |
DECLARE
v_cnt NUMBER;
BEGIN
FOR IDX IN 1..100 LOOP
FOR R IN (SELECT TABLE_NAME
FROM DBA_TABLES) LOOP
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*)
INTO V_CNT
FROM ' || R.TABLE_NAME || '
WHERE 1 = 0';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END LOOP;
END;
/
|
|수행내역|{code:SQL}
SQL >
1 EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_1');
PL/SQL procedure successfully completed.
SQL >
1 DECLARE
2 v_cnt NUMBER;
3 BEGIN
4 FOR IDX IN 1..100 LOOP
5 FOR R IN (SELECT TABLE_NAME
6 FROM DBA_TABLES) LOOP
7 BEGIN
8 EXECUTE IMMEDIATE 'SELECT COUNT(*)
9 INTO V_CNT
10 FROM ' || R.TABLE_NAME || '
11 WHERE 1 = 0';
12 EXCEPTION
13 WHEN OTHERS THEN
14 NULL;
15 END;
16 END LOOP;
17 END LOOP;
18 END;
19 /
|
| 소스코드 | {code:SQL} – 1. latch: cache buffers chains 세션 모니터링 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. 상세 모니터링
– 2-1) V$SESSION.BLOCKING_SESSION 확인
-- 래치를 점유하는 시간이 매우 짧기 때문에 확인 안됨
SELECT BLOCKING_SESSION
FROM V$SESSION
WHERE SID = &SID
;
– 2-2) V$LATCHHOLDER 확인
-- 래치 점유시간이 워낙 짧기 때문에 이 뷰를 통해서도 블로킹 세션 찾기 어려움
SELECT H.PID,
H.SID,
H.LADDR,
H.NAME,
H.GETS
FROM V$LATCHHOLDER H,
V$SESSION_WAIT S
WHERE S.P1RAW = H.LADDR
AND S.SID = &SID
;
|
|수행내역|{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
----------
9889
1 row selected.
SQL >
1 BEGIN
2 PRINT_TABLE('SELECT *
3 FROM V$SESSION_WAIT
4 WHERE SID = &SID');
5 END;
6 /
SID : 9889
SEQ# : 5263
EVENT : latch: cache buffers chains
P1TEXT : address
P1 : 504403170016220752
P1RAW : 07000002BC65D650
P2TEXT : number
P2 : 122
P2RAW : 000000000000007A
P3TEXT : tries
P3 : 0
P3RAW : 00
WAIT_CLASS_ID : 3875070507
WAIT_CLASS# : 4
WAIT_CLASS : Concurrency
WAIT_TIME : -1
SECONDS_IN_WAIT : 0
STATE : WAITED SHORT TIME
---------------------------------------------------------------------------
PL/SQL procedure successfully completed.
SQL >
1 SELECT BLOCKING_SESSION
2 FROM V$SESSION
3 WHERE SID = &SID
4 ;
BLOCKING_SESSION
----------------
SQL >
1 SELECT H.PID,
2 H.SID,
3 H.LADDR,
4 H.NAME,
5 H.GETS
6 FROM V$LATCHHOLDER H,
7 V$SESSION_WAIT S
8 WHERE S.P1RAW = H.LADDR
9 AND S.SID = &SID
10 ;
no rows selected
|
| 소스코드 | {code:SQL} SELECT P1, P2RAW, COUNT(*) FROM V$SESSION WHERE EVENT = 'cursor: pin S wait on X' AND WAIT_TIME = 0 GROUP BY P1, P2RAW ; {code} |
| 수행내역 | {code:SQL} |
|
| P1 | 뮤텍스 아이디를 의미하며, V$MUTEX_SLEEP_HISTORY.MUTEXT_IDENTIFIER 컬럼과 동일함 |
| P2RAW | 뮤텍스 홀더의 세션 어드레스를 의미함. V$SESSION.SADDR 컬럼과 동일함 |
* 위의 두 값을 이용하면 어떤 세션이 어떤 뮤텍스를 점유함으로서 대기가 발생했는지 추적 가능함
h3. 3) V$MUTEX_SLEEP_HISTORY 뷰에서 정보 얻기
* 뮤텍스에 의해 대기 현상은 V$MUTEX_SLEEP, V$MUTEX_SLEEP_HISTORY 뷰를 통해 분석 가능함
{code:SQL}
DESC V$MUTEX_SLEEP
Name Null? Type
------------- ------- --------------
MUTEX_TYPE VARCHAR2(32)
LOCATION VARCHAR2(40)
SLEEPS NUMBER
WAIT_TIME NUMBER
;
SELECT *
FROM V$MUTEX_SLEEP
WHERE ROWNUM <= 10
;
MUTEX_TYPE LOCATION SLEEPS WAIT_TIME
--------------- -------------------------------- ------ ----------
Cursor Stat kksFindCursorStat [KKSSTALOC3] 1 6
Cursor Parent kkspsc0 [KKSPRTLOC26] 3 8
Cursor Parent kksfbc [KKSPRTLOC2] 1 6
Cursor Pin kkslce [KKSCHLPIN2] 844 8148033
Cursor Pin kksfbc [KKSCHLPIN1] 1 9787
;
DESC V$MUTEX_SLEEP_HISTORY
MUTEX_IDENTIFIER SLEEP_TIMESTAMP MUTEX_TYPE GETS SLEEPS REQUESTING_SESSION BLOCKING_SESSION LOCATION MUTEX_VALUE P1 P1RAW P2 P3 P4 P5
---------------- ------------------------------- ---------------- ---- ---------- ------------------ ---------------- --------------------- ---------------- ---------- ---------------- ---------- ---------- ---------- -----
1146918755 05-APR-12 06.10.30.393569 AM Cursor Parent 9 1 9902 9934 kksfbc [KKSPRTLOC2] 00 13 00 0 0 0
1299653039 05-APR-12 09.46.44.391569 AM Cursor Pin 1 1 9622 9889 kkslce [KKSCHLPIN2] 000026A100000000 0 00 0 0 0
4210584983 05-APR-12 09.46.38.339478 AM Cursor Pin 1 1 9889 9622 kkslce [KKSCHLPIN2] 0000259600000000 0 00 0 0 0
1194380816 05-APR-12 05.10.04.227956 AM Cursor Pin 12 14 9957 9901 kkslce [KKSCHLPIN2] 000026940000000C 0 00 0 0 0
2246118947 05-APR-12 09.46.42.303668 AM Cursor Pin 1 2 9622 9889 kkslce [KKSCHLPIN2] 000026A100000000 0 00 0 0 0
2920326977 05-APR-12 09.46.38.512431 AM Cursor Pin 1 1 9889 9622 kkslce [KKSCHLPIN2] 0000259600000000 0 00 0 0 0
27866834 05-APR-12 09.46.43.952397 AM Cursor Pin 1 2 9622 9889 kkslce [KKSCHLPIN2] 000026A100000000 0 00 0 0 0
643237928 05-APR-12 09.46.43.094252 AM Cursor Pin 1 1 9622 9889 kkslce [KKSCHLPIN2] 000026A100000000 0 00 0 0 0
2077966511 05-APR-12 09.46.38.734815 AM Cursor Pin 1 1 9889 9622 kkslce [KKSCHLPIN2] 0000259600000000 0 00 0 0 0
3832746397 05-APR-12 09.46.44.175636 AM Cursor Pin 1 1 9622 9889 kkslce [KKSCHLPIN2] 000026A100000000 0 00 0 0 0
;
SELECT *
FROM V$MUTEX_SLEEP_HISTORY
WHERE ROWNUM <= 10
;
Name Null? Type
--------------------- -------- --------------
MUTEX_IDENTIFIER NUMBER
SLEEP_TIMESTAMP TIMESTAMP(6)
MUTEX_TYPE VARCHAR2(32)
GETS NUMBER
SLEEPS NUMBER
REQUESTING_SESSION NUMBER
BLOCKING_SESSION NUMBER
LOCATION VARCHAR2(40)
MUTEX_VALUE RAW(8)
P1 NUMBER
P1RAW RAW(8)
P2 NUMBER
P3 NUMBER
P4 NUMBER
P5 VARCHAR2(64)
;
SELECT NAME
FROM V$EVENT_NAME
WHERE NAME LIKE '%mutex%' OR NAME LIKE 'cursor:%'
;
NAME
--------------------------
cursor: mutex X
cursor: mutex S
cursor: pin S wait on X
cursor: pin X
cursor: pin S
| 소스코드 | {code:SQL} – 1. @LONG_PARSE 파일 생성 SET heading OFF SET timing OFF SET feedback OFF |
SET serveroutput ON SIZE 100000
var v_sql CLOB;
BEGIN
:v_sql := 'EXPLAIN PLAN FOR SELECT COUNT(*) FROM ';
FOR R IN (SELECT T1.TABLE_NAME
FROM USER_TABLES T1,
USER_TABLES T2
WHERE ROWNUM <= 300
AND T1.TABLE_NAME NOT LIKE '%S%') LOOP
:v_sql := :v_sql || R.TABLE_NAME || ', ';
END LOOP;
:v_sql := :v_sql || ' DUAL WHERE 1 = 0;';
dbms_output.put_line(:v_sql);
END;
/
spool long_parse.sql
EXEC DBMS_OUTPUT.PUT_LINE(:v_sql);
spool OFF
SET heading ON
SET timing ON
SET feedback ON
SET serveroutput OFF
– 2. @LONG_PARSE 파일 수행
EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_1');
@long_parse
|
|수행내역|{code:SQL}
SQL >
1 SET heading OFF
SQL >
1 SET timing OFF
SQL >
1 SET feedback OFF
SQL >
1 SET serveroutput ON SIZE 100000
SQL >
1 var v_sql CLOB;
SQL >
1 BEGIN
2 :v_sql := 'SELECT COUNT(*) FROM ';
3
4 FOR R IN (SELECT T1.TABLE_NAME
5 FROM USER_TABLES T1,
6 USER_TABLES T2
7 WHERE ROWNUM <= 300
8 AND T1.TABLE_NAME NOT LIKE '%S%') LOOP
9
10 :v_sql := :v_sql || R.TABLE_NAME || ', ';
11
12 END LOOP;
13
14 :v_sql := :v_sql || ' DUAL WHERE 1 = 0;';
15
16 dbms_output.put_line(:v_sql);
17 END;
18 /
SQL >
1 spool OFF
SQL >
1 SET heading ON
SQL >
1 SET timing ON
SQL >
1 SET feedback ON
SQL >
1 SET serveroutput OFF
SQL >
1 EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_1');
PL/SQL procedure successfully completed.
SQL >
1 @long_parse
|
| 소스코드 | {code:SQL} EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_2'); |
@long_parse
|
|수행내역|{code:SQL}
SQL >
1 EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('SESSION_2');
PL/SQL procedure successfully completed.
SQL >
1 @long_parse
|
| 소스코드 | {code:SQL} – 1. 세션_2 모니터링 -- 세션_1에서 해당 SQL에 대해 LCO를 등록한 상태에서 실행계획을 만들고 있으므로, --- 세션_2는 파싱하는 동안 대기해야 하며, 이 때 cursor: pin S wait on X 이벤트 대기함 -- cursor: pin S wait on X 이벤트는, Curor Pin을 공유모드(Shared)로 획득하려고 하지만, 다른 세션이 (Exclusive)로 획득하고 있어서 대기함을 의미함 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. 세션_2 홀더 찾기
-- V$SESSION.BLOCKING_SESSION 컬럼으로 확인 가능
-- 하지만, 지속시간이 매우 짧은 다른 뮤텍스들은 래치 경합과 마찬가지로 홀더 찾기가 어려울 수 있음
SELECT BLOCKING_SESSION
FROM V$SESSION
WHERE SID = &SID
;
– 3. V$MUTEX_SLEEP_HISTORY 뷰에서 확인
-- MUTEX_IDENTIFIER 값을 통해 경합 대상이 되는 오브젝트 이름 얻을 수 있음
col obj_name format a10
col mutex_type format a10
col LOCATION format a10
SELECT *
FROM (SELECT BLOCKING_SESSION AS H_SID,
(SELECT KGLNAOBJ
FROM SYS.XM$KGLOB
WHERE KGLNAHSH = MUTEX_IDENTIFIER
AND ROWNUM = 1) AS OBJ_NAME,
MUTEX_TYPE,
LOCATION,
SLEEPS,
GETS,
TO_CHAR(SLEEP_TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS') AS SLEEP_TIMESTAMP
FROM V$MUTEX_SLEEP_HISTORY
WHERE REQUESTING_SESSION = &SID
ORDER BY SLEEP_TIMESTAMP DESC)
WHERE ROWNUM = 1
;
|
|수행내역|{code:SQL}
SQL >
1 col sid new_value sid
SQL >
1 SELECT SID
2 FROM V$SESSION
3 WHERE CLIENT_INFO = 'SESSION_2'
4 ;
SID
----------
9839
1 row selected.
SQL >
1 BEGIN
2 PRINT_TABLE('SELECT *
3 FROM V$SESSION_WAIT
4 WHERE SID = &SID');
5 END;
6 /
SID : 9839
SEQ# : 503
EVENT : cursor: pin S wait on X
P1TEXT : idn
P1 : 17636530
P1RAW : 00000000010D1CB2
P2TEXT : value
P2 : 41777146888192
P2RAW : 000025FF00000000
P3TEXT : where|sleeps
P3 : 21474836898
P3RAW : 00000005000001A2
WAIT_CLASS_ID : 3875070507
WAIT_CLASS# : 4
WAIT_CLASS : Concurrency
WAIT_TIME : 0
SECONDS_IN_WAIT : 0
STATE : WAITING
---------------------------------------------------------------------------
PL/SQL procedure successfully completed.
SQL >
1 SELECT BLOCKING_SESSION
2 FROM V$SESSION
3 WHERE SID = &SID
4 ;
BLOCKING_SESSION
----------------
1 row selected.
SQL >
1 col obj_name format a10
SQL >
1 col mutex_type format a10
SQL >
1 col LOCATION format a10
SQL >
1 SELECT *
2 FROM (SELECT BLOCKING_SESSION AS H_SID,
3 (SELECT KGLNAOBJ
4 FROM SYS.XM$KGLOB
5 WHERE KGLNAHSH = MUTEX_IDENTIFIER
6 AND ROWNUM = 1) AS OBJ_NAME,
7 MUTEX_TYPE,
8 LOCATION,
9 SLEEPS,
10 GETS,
11 TO_CHAR(SLEEP_TIMESTAMP, 'YYYY/MM/DD HH24:MI:SS') AS SLEEP_TIMESTAMP
12 FROM V$MUTEX_SLEEP_HISTORY
13 WHERE REQUESTING_SESSION = &SID
14 ORDER BY SLEEP_TIMESTAMP DESC)
15 WHERE ROWNUM = 1
16 ;
H_SID OBJ_NAME MUTEX_TYPE LOCATION SLEEPS GETS SLEEP_TIMESTAMP
---------- ---------- ---------- ---------- ---------- ---------- --------------------------------------
9727 EXPLAIN PL Cursor Pin 464 1 2012/04/05 15:19:37
AN FOR SEL
ECT COUNT(
*) FROM AD
X_PRE_AUTO
CONFIG, AM
W_OPINION_
LOG_MV, AM
W_OPINION_
MV, AP_605
0423_EMP_V
ENDOR_TEMP
, AP_69424
17_DATA, A
P_UNDO_EVE
NT_LOG, AQ
$_WF_BPEL_
QTAB_G, AQ
$_WF_BPEL_
QTAB_H, AQ
$_WF_BPEL_
QTAB_I, AQ
$_WF_BPEL_
QTAB_T, AR
CH_TRX_ID,
AR_REM_CR
_ID_GTT, A
W$ODPCODE,
AW$XWDEVK
IT, A_AR_C
MGT_BANK_R
EF_DATA, A
_AR_CMGT_G
UARANTOR_D
ATA, A_AR_
CMGT_OTHER
_DATA, A_A
R_CMGT_TRA
DE_REF_DAT
A, A_HZ_CO
NTACT_POIN
T_VAL_GT,
A_XXARF_CO
NTACT_PHON
E_BPM_ALL,
A_XXARF_K
EIC_CODE_D
H_SID OBJ_NAME MUTEX_TYPE LOCATION SLEEPS GETS SLEEP_TIMESTAMP
---------- ---------- ---------- ---------- ---------- ---------- --------------------------------------
ETAIL, A_X
XARF_KEIC_
CODE_DETAI
L_IF, A_XX
ARF_TT_CM_
ALL, A_XXI
MPM_EVAL_C
ONTRACT, A
_XXIMPM_EV
AL_REMITTA
NCE, A_XXI
MPM_PRE_BL
_B2BI_IF,
A_XXIMPM_V
MI_ITEM_MA
IL_GROUP,
BANK_ACCT_
TYPE, BANK
_ACCT_TYPE
_N, BIL_BI
_CAL_MV, B
IL_BI_GRP_
CNT_MV, BI
L_BI_OP11_
PG_MV, BIL
_BI_OP12_P
G_MV, BIL_
BI_OP21_G_
MV, BIL_BI
_OP22_G_MV
, BIL_BI_O
PDTL_MV, B
IL_BI_OPLD
C_GC_MV, B
IL_BI_OPLD
C_G_MV, BI
L_BI_OPLDC
_MV, BIL_B
I_OPLPC_GC
_MV, BIL_B
I_OPLPC_G_
MV, BIL_BI
_OPLPC_MV,
BIL_BI_OP
TY_G_MV, B
IL_BI_OPTY
_PG_MV, BI
H_SID OBJ_NAME MUTEX_TYPE LOCATION SLEEPS GETS SLEEP_TIMESTAMP
---------- ---------- ---------- ---------- ---------- ---------- --------------------------------------
L_BI_PIPEC
_G_MV, BIL
_BI_PIPEC_
WG_MV, BIL
_BI_PIPE_G
_MV, BIL_B
1 row selected.
|