0. 소개

성능 문제를 분석하다보면, V$SESSION_WAIT, V$SESSION_EVENT, TRACE 등을 통해 수집한 기본적인 대기 이벤트 정보만으로 부족한 경우 발생.
대기 이벤트를 분석할 때, 해당 블록이 어떤 종류의 블록인지 파악해야 할 필요가 있고, 그 외에 추가적인 정보를 수집해야 하는데 이에 대해 소개함.

1. I/O 관련 대기 이벤트

I/O 관련 대기이벤트들의 공통적인 특징은 'P1, P2'가 '파일번호, 블록번호' 정보를 제공함.
P1이 'file#'이고, P2가 'block#'인 대기 이벤트 목록은 다음과 같음

소스코드	{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

위에서 얻은 file#, block# 값을 가지고 데이터 오브젝트 아이디 확인 가능

소스코드	{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 개의 행이 선택되었습니다.

|

책에서는 V$BH 뷰에서 STATUS값을 'xcur'(Exclusive)로 검색했지만, 테스트 시에는 'free'(Not currently in use) 상태여서 조건에서 제거함
위의 결과처럼 '파일번호, 블록번호'에 해당하는 블록이 버퍼 캐시에 존재한다면, V$BH 뷰에서 블록의 종류(CLASS#)와 데이터 오브젝트 아이디(OBJD) 값을 얻을 수 있음
OBJD 값을 얻었다면, 이를 이용해 ALL_OBJECTS 뷰에서 검색하여, OWNER, OBJECT_NAME, OBJECT_TYPE 확인이 가능
또한 블록의 종류(CLASS#)를 나타내는 4와 1은 각각 세그먼트 헤더(4), 데이터블록(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

--

--
1 data block
2 sort block
3 save undo block
4 segment header
5 save undo header
6 free list
7 extent map
8 1st level bmb
9 2nd level bmb
10 3rd level bmb
11 bitmap block
12 bitmap index block
13 file header block
14 unused
15 system undo header
16 system undo block
17 undo header
18 undo block

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

'PAGETABLE SEGMENT HEADER' 값은 블록 종류가 세그먼트 헤더 블록임을 알 수 있는 값이며 책에서는 확인이 됐지만
테스트 시에는 확인을 못함.

파일번호 1에 블록번호 86577인 블록도 확인 가능

소스코드	{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

여기서 'typ: 1 - DATA'는 블록 종류가 데이터 블록임을 알 수 있으며, 데이터 블록이란 테이블이나 인덱스에서 실제 데이터를 저장하고 있는 블록을 의미함.
'seg/obj: 0x12386'에서 세그먼트의 데이터 오브젝트 아이디가 0x12386(16진수)임을 알 수 있으며, 십진수로 변환하면 74630이어서 V$BH에서 얻은 값과 일치함

소스코드	{code:SQL} SELECT TO_DEC('12386') FROM DUAL; {code}
수행내역	{code:SQL} KENZO:xsoftdb:SYSTEM > 1 SELECT TO_DEC('12386') FROM DUAL;

TO_DEC('12386')

---
74630

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: PW - flush prewarm buffers
enq: RO - contention
enq: RO - fast object reuse
enq: KO - fast object checkpoint
enq: TM - contention
enq: ST - contention
enq: HW - contention
enq: SS - contention

...(중략)...

enq: JS - queue lock
enq: JS - sch locl enqs
enq: JS - q mem clnup lck
enq: JS - evtsub add
enq: JS - evtsub drop
enq: JS - wdw op
enq: JS - evt notify

209 rows selected.

Elapsed: 00:00:00.95

|

* 위의 결과를 보면 Enqueue 관련 대기 이벤트는 'enq:<Lock Type> - Reason'의 형태를 지니고 있음
** 'enq: TX - row lock contention' : 로우 레벨 락 경합 때문에 TX(Transaction) Enqueue를 획득하지 못해서 대기한다는 것을 의미함
** 'enq: TX - allocate ITL entry'  : ITL 엔트리를 할당받을 수 없기 때문에 TX(Transaction) Enqueue를 획득하지 못해서 대기한다는 것을 의미함

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 에 대한 상세한 설명

'TM Enqueue'의 정보를 V$LOCK_TYPE 뷰에서 검색하면 아래와 같음

소스코드	{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

4) V$SESSION

V$SESSION 뷰에서도 Enquene와 관련된 정보 확인 가능


DESC V$SESSION

Name                        Null?    Type
-------------------------- -------- ----------------
SADDR                                RAW(8)
SID                                  NUMBER

...(중략)...

ROW_WAIT_OBJ#                        NUMBER
ROW_WAIT_FILE#                       NUMBER
ROW_WAIT_BLOCK#                      NUMBER
ROW_WAIT_ROW#                        NUMBER

...(중략)...

BLOCKING_SESSION_STATUS              VARCHAR2(11)
BLOCKING_INSTANCE                    NUMBER
BLOCKING_SESSION                     NUMBER

...(중략)...
;

SELECT SADDR,                   
       SID,                     
       ROW_WAIT_OBJ#,           
       ROW_WAIT_FILE#,          
       ROW_WAIT_BLOCK#,         
       ROW_WAIT_ROW#,           
       BLOCKING_SESSION_STATUS, 
       BLOCKING_INSTANCE,       
       BLOCKING_SESSION  
FROM   V$SESSION
WHERE  ROWNUM <= 10
;

SADDR                   SID ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# BLOCKING_SESSION_STATU BLOCKING_INSTANCE BLOCKING_SESSION
---------------- ---------- ------------- -------------- --------------- ------------- ---------------------- ----------------- ----------------
07000002C1C0C830       9613            -1              0               0             0 NO HOLDER                                                
07000002C2C26C60       9618      23691487           1912         1721473             0 NO HOLDER                                                
07000002BFBFA388       9626        484182            361         2507607             0 NO HOLDER                                                
07000002C1C10640       9631      23691489            460         2087281             0 NO HOLDER                                                
07000002BFBFB838       9632            -1              0               0             0 NO HOLDER                                                
07000002BFBFCCE8       9638            -1              0               0             0 NO HOLDER                                                
07000002C0C222D0       9639            -1              0               0             0 NO HOLDER                                                
07000002C4C422A0       9640      23691494           2628         1797665             0 NO HOLDER                                                
07000002C1C12FA0       9643            -1              0               0             0 NO HOLDER                                                
07000002BFBFE198       9644      23691483           3193         1890049             0 NO HOLDER

ROW_WAIT_OBJ#	현재 세션이 로우 레벨 락(TX Enqueue)을 획득하기 위해 대기하는 경우，경합 대상이 되는 로우가 위치하는 오브젝트의 데이터 오브젝트 아이디
ROW_WAIT_FILE#	헌재 세션이 로우 레벨 락(TX Enqueue)을 획득하기 위해 대기하는 경우，경합 대상이 되는 로우가 위치하는 파일 번호
ROW_WAIT_BLOCK#	현재 세션이 로우 레벨 락(TX Enqueue)을 획득하기 위해 대기하는 경우，경합 대상이 되는 로우가 위치하는 블록 번호
ROW_WAIT_ROW#	현재 세션이 로우 레벨 락(TX Enqueue)을 획득하기 위해 대기하는 경우，경합 대상이 되는 로우가 위치하는 로우 번호
BLOCKING_SESSION_STATUS	현재 세션을 블로킹하고 있는 세선의 상태
BLOCKING_INSTANCE	현재 세션을 블로킹하고 있는 세션의 인스턴스 번호 RAC 환경에서 인스틴스 간에 Enqueue 경합이 발생하는 경우에 사용
BLOCKING_SESSION	현재 세션을 블로킹하고 있는 세선의 세션 아이디

ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#
- 이 4개 컬럼을 조합하면 ROWID 값이 되기 때문에, 이 값을 이용하여 어떤 로우에서 로우 레벨 락 경합이 발생하는지 알 수 있음
- 이 값들은 현재 세션이 로우 레벨 락 경합에 의해 TX Enqueue를 획득하기 위해 대기하고 있는 경우에만 의미가 있음
- 예를들어, 현재 세션이 'enq: TX - row lock contention' 이벤트를 대기하고 있다면, 이 값들에 대해 조회 가능함

BLOCKING_SESSION_STATUS, BLOCKING_INSTANCE, BLOCKING_SESSION
- 이 3개 컬럼은 V$SESSION 뷰를 조회하는 것만으로 블로커(Blocker)를 알 수 있다는데서 대단히 유용하게 사용할 수 있으며, 특히 RAC에서 대단히 유용함
- 단 Enqueue 환경에서만 완벽한 데이터를 제공하며, Enqueue가 아닌 다른 종류의 락(Library Cache Lock, Library Cache Pin, Row Cache Lock)과 같은 종류의 락들은 RAC에서 완전한 형태의 정보가 제공되지 않음

5) Enqueue 관련 대기 이벤트 테스트

5-1) SESSION #1

테이블과 데이터를 생성하고 UPDATE를 해서 LOCK을 걸음

소스코드	{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;

|

5-2) SESSION #2

세션 #2에서 동일한 로우 업데이트

소스코드	{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;

|

5-3) SESSION #3

세션 #3에서 동일한 로우 업데이트

소스코드	{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.

|

5-4) SESSION #4에서 SESSION #2 모니터링

세션 #1에서 UPDATE 한 로우를 세션 #2에서 동일한 로우를 UPDATE하기 때문에 락 경합이 발생되며, 이에 대해 모니터링함

소스코드	{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.

|

3. Library Cache Lock 관련 대기 이벤트

1) Library Cache Lock 대기 이벤트 소개

Library Cache Lock은 라이브러리 캐시 오브젝트(Library Cache Object. LCO)를 보호하는 시스템 락
LCO는 Shared Pool의 라이브러리 캐시 영역에 저장되는 모든 종류의 공유 오브젝트(테이블, 뷰, SQL 커서, 함수, 포르시저)를 의미함
Library Cache Lock은 여러 세션이 동시에 동일한 LCO를 변경하는 것을 방지하기 위해 사용되며, 획득하지 못한 경우 Library Cache Lock 대기 이벤트 발생
Library Cache Lock은 V$LOCK이 아닌 X$KGLLK 뷰에서 확인 가능


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

KGLLKUSE	Library Cache Lock 을 획득하거나 대기하고 있는 세션의 주소. V$SESSION, SADDR 값과 조인하면 세션 정보를 얻을 수 있음
KGLLKHDL	Library Cache lock의 핸들 주소. V$SESSION.WAIT.PlRAW 값과 조인하면 대기 이벤트 정보를 얻을 수 있음
KGLNAOBJ	LCO 이름

2) Library Cache Lock 테스트

책에서는 PROC1 프로시저를 10만번 생성하는 스크립트 수행하여 'Library Cache Lock' 경합을 유도하였지만
테스트 진행이 잘 안되서 'Table Modify' 및 UPDATE 방식으로 테스트 변경

2-1) SESSION #1

소스코드	{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));

|

2-2) SESSION #2

XSOFT_T2.CNT 컬럼 UPDATE해서 'Library Cache Lock' 유도

소스코드	{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;

|

2-3) SESSION #3에서 SESSION #2 모니터링

Library Cache Lock 모니터링

소스코드	{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.

|

4. Library Cache Pin 관련 대기 이벤트

1) Library Cache Pin 대기 이벤트 소개

Library Cache Pin은 LCO를 실행하는 과정을 보호하는 시스템 락.
예를들어, 특정 세션이 프로시저 Pl을 실행 하는 도중에, 다른 세션이 해당 프로시저를 컴파일해서는 안되며, 거꾸로 특정 세션이 프로시저 Pl 을 컴파일하고 있는 도중에 다른 세션이 해당 프로시저를 실행해서도 안됨.
이처럼 특정 LCO의 실행 과정을 보호하기 위해 사용되는 것이 Library Cache Pin이며, 획득하지 못 할 경우 이 이벤트를 대기함
Library Cache Pin 은 V$LOCK 뷰가 아닌 X$KGLPN 뷰에서 정보를 얻을 수 있음


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 값과 조인하면 대기이벤트 정보를 얻을 수 있음

2) Library Cache Pin 테스트

테스트 시나리오는, 세션_1에서 SLEEP하는 프로시저를 수행하고, 세션_2에서 동일 프로시저를 수행하면서 'Library Cache Pin' 경합을 유도함
- reference_site : http://orainternals.wordpress.com/2009/06/02/library-cache-lock-and-library-cache-pin-waits/

2-1) Sesssion #1

세션_1에서 TEST_KGLLK 프로시저 컴파일 하고 수행

소스코드	{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.

|

2-2) Sesssion #2

세션_2에서 TEST_KGLLK 컴파일 수행(Library Cache Pin 대기)

소스코드	{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

--
9937

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

-

---

--

-

---

-

--

9937,41 SQL*Plus 6 1 9937 ALTER PROCEDURE TEST_KGLLK COMPILE handle address pin address 100*mode+namespace alter system kill session '9937,41' ; kill -9 3560198
504403169386942624 504403169480899360 301

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 값과 조인하면 세션 정보를 얻을 수 있음

2) Row Cache Lock 테스트

테스트 시나리오는 다음과 같다.
- NOCACHE 속성의 시권스에 대해 NEXTVAL 을 호출할 때는 Row Cache Lock 을 획득해야 하므로, 동시에 여러 세션이 시퀀스를 사용하여 이벤트 유도함

2-1) Session #1

총 100,000번 LOOPING 돌면서 SEQ1.NEXTVAL 호출함

소스코드	{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}

|

2-2) Session #2

총 100,000번 LOOPING 돌면서 SEQ1.NEXTVAL 호출함

소스코드	{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}

|

2-3) Row Cache Lock 모니터링

소스코드	{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. 상세 모니터링
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

|
|수행내역|{code:SQL}

|

6. 래치 관련 대기 이벤트

7. 뮤텍스 관련 대기 이벤트

문서에 대하여

최초작성자 : ~xsoft
최초작성일 : 2012년 04월 07일
이 문서는 오라클클럽 오라클 성능 트러블슈팅의 기초 스터디 모임에서 작성하였습니다.
{*}이 문서의 내용은 엑셈(EXEM) 에서 출간한 '오라클 성능 트러블슈팅의 기초'를 참고하였습니다.*

대기 이벤트별 추가적인 데이터 수집_1 (by xsoft) [2012.04.05]

0. 소개

1. I/O 관련 대기 이벤트

4) V$SESSION

5) Enqueue 관련 대기 이벤트 테스트

5-1) SESSION #1

5-2) SESSION #2

5-3) SESSION #3

5-4) SESSION #4에서 SESSION #2 모니터링

3. Library Cache Lock 관련 대기 이벤트

1) Library Cache Lock 대기 이벤트 소개

2) Library Cache Lock 테스트

2-1) SESSION #1

2-2) SESSION #2

2-3) SESSION #3에서 SESSION #2 모니터링

4. Library Cache Pin 관련 대기 이벤트

1) Library Cache Pin 대기 이벤트 소개

2) Library Cache Pin 테스트

2-1) Sesssion #1

2-2) Sesssion #2

2) Row Cache Lock 테스트

2-1) Session #1

2-2) Session #2

2-3) Row Cache Lock 모니터링

6. 래치 관련 대기 이벤트

7. 뮤텍스 관련 대기 이벤트

문서에 대하여