Louis.Kim 의 제 3막

분류

래치(Latch)

락(Lock)

목적

하나의 목적을 수행함 : 메모리 구조에 대한 배타적인 접근을 위함 ( 오라클 9i 부터cache buffers chains latch 들은 읽기 전용시에 공유가 가능함

두 가지 목적을 수행함: 락 모드가 호환가능하면 다수의 프로세스가 동일한 리소스를 공유하는 것을 허용하며, 락 모드가 호환 가능하지 않으면 리소스에 대한 배타적인 접근만 허용함

사용범위

SGA 내부의 데이터 구조에만 적용. 메모리 오브젝트를 임시적으로 보호함. 단일 오퍼레이션으로 메모리 구조에 대한 접근 제어. 트랜잭션 단위가 아님

테이블, 데이터 블록 및 state object와 같은 오브젝트를 보호함.

데이터베이스의 데이터 또는 베타데이터 접근 제어. 트랜잭션 단위

획득방식

두 가지 모드로 요청이 가능 : willing-to-wait 또는 no-wait

6가지 모드로 요청이 가능 : null, row share, row exclusive, share, share row exclusive 또는 exclusive

범위

SGA내부에 정보가 존재하며, 로컬 인스턴스에서만 볼 수 있음 – 래치는 인스턴스 레벨로 작동

데이터베이스 내부에 정보가 존재하며, 모든 인스턴스에서 볼 수 있음 – 락은 데이터베이스 레벨에서 작동

복잡도

단순한 명령어를 사용하여 구현됨. 일반적으로 test-and-set, compare-and-swap 또는 단순한 CPU명령어. 구현이 쉬움

문맥 교환(context switch)를 포함한 일련의 명령어들을 사용하여 구현됨, 구현이 복잡함

지속기간

아주 짧은 순간만 지속됨 (microsecond 단위)

일정 시간 동안 지속됨 (트랜잭션 동안)

큐

프로세스가 래치 획득을 실패한 후 슬립(sleep) 상태로 들어갈 때, 해당 요청은 큐(queueu)로 관리되지 않으며, 요청한 순서대로 서비스되지 않음(latch wait list를 이용하여 큐 방식으로 사용되는 래치들은 예외임)

프로세스가 락 획득을 실패한 후, 해당 요청은 큐(queue)로 관리되며, 요청한 순서대로 서비스됨(no-wait 모드는 예외임)

데드락

래치는 데드락(deadlock)이 발생되지 않도록 구현됨

락은 큐(queue)방식을 사용하며, 데드락이 발생될 가능성이 높다. 데드락이 발생 될 때마다 트레이스 파일이 생김

-       SQL문을 실행하고자 하는 프로세스는 SQL를 shared pool 의 library cache 영역에 올려야하는데, 이 때 heap 메모리 영역을 할당받기 위해 반드시 shared pool 래치를 획득 필요

-       하나의 프로세스만이 힙으로부터 청크(chunk)를 할당받을 수 있음

-       select문의 경우 특정 데이터블록을 읽기 위해 데이터블록의 DBA와 Class에 해당하는 Hash chain에 접근하고자 하는 모든 프로세스는 반드시 Hash chain을 관장하는 cache buffers chains Latch를 획득해야 함

-       DML를 실행하기 위해서는 PGA영역에 Redo 데이터를 생성 / Redo data를 Redo buffer로 복하기 위해 redo copy latch 가 필요

컬럼

설명

GETS

Willing-to-wait 모드에서 슬립하기 전의 래치 “요청” 회수

MISSES

Willing-to-wait 모드에서의 슬립하기 전의 래치 획득 실패 회수

SPIN_GETS

Willing-to-wait 모드에서 슬립하기 전의 스핀단계에서의 래치 획득 성공 회수

SLEEPS

Willing-to-wait 모드에서의 슬립 회수

IMMEDIATE_GETS

No-wait 모드에서 래치 획득 성공 회수

IMMEDIATE_MISSES

No-wait 모드에서 래치 획득 실패 회수

SLEEP1 ~ SLEEP4

1~3 회의 슬립회수와 4회 이상의 슬립 회수. 오라클 10gR2부터는 사용되지 않으며 V$EVENT_HISTOGRAM 뷰로 대체

WAITERS_WOKEN

Latch wait posting 을 사용할 경우 세션이 깨어난 회수. 오라클 10gR2 부터는 사용되지 않는다. Latch wait list 래치가 없어진 것과 같은 맥락으로 latch wait posting 알고리즘이 완전히 바뀌었음을 의미

WAIT_TIME

래치를 획득하기 위해 대기한 시간(microsecond) 단위

Mode

설명

0

None

1

Null(N)

2

Sub-Shared(SS) 또는 Row-Shared(RS)

3

Sub-Exclusive(SX) 또는 Row-Exclusive(RX)

4

Shared(S)

5

Shared-Sub-Exclusive(SSX) 또는 Shared-ROW-Exclusive(SRX)

6

Exclusive(X)

N

SS

SX

S

SSX

X

N

O

O

O

O

O

O

SS

O

O

O

O

O

X

SX

O

O

O

X

X

X

S

O

O

X

O

X

X

SSX

O

O

X

X

X

X

X

O

X

X

X

X

SID

Lock 을 보유중이거나 요청 중인 Session ID

LMODE > 0 이면 Lock을 보유중인 Session 이며, Request > 0 이면 Lock 요청중인 session

Type

Enqueue Lock Resoure Type (ex – TX, TM, UL, US, CI, TC, …etc)

ID1

Resource ID 1

ID2

Resource ID 2 <TYPE-ID1-ID2>가 Lock이 보호하는 Resource 구분자

LMODE

Lock 을 보유하고 있는 경우의 Mode (1~6 -> Lock 모드 참조)

REQUEST

Lock 을 요청중인 경우의 Mode (1~6 -> Lock 모드 참조)

CTIME

현재의 lock mode가 허용된 시간(second) Lock을 보유하거나 요청한 이후부터의 시간

Block

현재의 Lock이 다른 Lock을 블로킹하고 있는지의 여부.
1: 다른 Lock을 블로킹 중
0 : 다른 Lock을 블로킹 안하고 있음

1.     System Trace 를 이용한 분석

$sqlplus '/ as sysdba'

oradebug setmypid

oradebug unlimit

oradebug dump systemstate 266

행 걸린 SID 를 이용하여 PID 검색

select pid from v$process where addr=

(select paddr from v$session where sid= sid번호;

TRACE 파일에서 아래 PID로 검색하여 wait event 확인('waiting for' 를 검색)

PROCESS 위에서 확인한 PID

2.     X$KGLLK 테이블을 조사

select sid,saddr from v$session where event= 'library cache lock';

select kgllkhdl Handle,kgllkreq Request, kglnaobj Object

from x$kgllk where kgllkses = '위에서 찾은 saddr정보'

and kgllkreq > 0

--직접 만든 join 문

select a.kgllkhdl Handle,a.kgllkreq Request, a.kglnaobj Object

from x$kgllk a, v$session b

where b.event='library cache lock'

and b.saddr=a.kgllkses

and a.kgllkreq > 0;

또는

select sid,saddr from v$session where event= 'library cache lock';

select kgllkses saddr,kgllkhdl handle,kgllkmod mod,kglnaobj object

from x$kgllk lock_a

where kgllkmod > 0

and exists (select lock_b.kgllkhdl from x$kgllk lock_b

where kgllkses = '위에서 찾은 saddr' /* blocked session */

and lock_a.kgllkhdl = lock_b.kgllkhdl

and kgllkreq > 0);

공통 상황들 

• 테이블이 진행중인 변경(ALTER TABLE)에 의해 사용중이라면 이 테이블에 DML작업은 행이 걸리게 됩니다. 테이블의 크기나 변경이 어떤 작업(예를 들어 (수천 건의 레코드를 가지고 있는 테이블에 ALTER TABLE x MODIFY (col1 CHAR(200))) )이냐에 따라 아주 오래 락이 걸리기도 합니다.
  
  이런 경우, v$lock은 이 테이블 오브젝트에 DML 인큐 락(ID1이 테이블의 OBJECT_ID로 LMODE=6, TYPE=TM)을 가지고 'ALTER TABLE' 을 수행하고 있는 세션을 보여줄 것입니다. 이 기다리고 있는 세션은 v$lock에서 보이지 않는데 동시 접속 세션들이 많은 환경에서는 V$LOCK 정보를 이용하여 작업을 방해하고 있는 문제의 세션을 확인하기에 부족할 것입니다.

• 어떤 사용자가 패키지에서 정의하고 있는  프로시져나 펑션을 수행중이라면 이 패키지 컴파일은 라이브러리 캐시 락 과 라이브러리 캐시 핀을 기다리게 될 것이다.

방법 1: Systemstate 분석

Systemstate 이벤트는 모든 오라클 프로세스들의 상세한 정보를 담고 있는 트레이스 파일을 생성해 줍니다. 이 정보에는 특정 프로세스에 의해 사용중이거나 요청중인 모든 자원들을 포함하고 있습니다.

어떤 작업이 행 상태에 있는 동안 새로운 세션을 열고 다음의 명령을 발행합니다:
Oracle 9.2.0.1 또는 그 이후 :

$sqlplus '/ as sysdba'
oradebug setmypid
oradebug unlimit
oradebug dump systemstate 266

더 오래 전 버젼은 상위 버젼에서도 수행 가능한 다음의 구문을 이용할 수 있습니다. 이벤트 level 266은 9.2.0.6 이전에서는 사용할 수 없습니다.

alter session set max_dump_file_size=unlimited;
alter session set events 'immediate trace name systemstate level 10'

오라클은 USER_DUMP_DEST 디렉토리에 systemstate 트레이스 파일을 생성합니다.

행 상태에 있는 세션의 PID를 얻기 위해 :

select pid from v$process where addr=
(select paddr from v$session where sid= <sid_of_hanging_session> );

이 Systemstate 덤프는 각각의 프로세스별로 구분되는 정보를 담고 있습니다.
트레이스 화일을 열고 "PROCESS <위에서 확인한 PID>"를 찾습니다.
이 프로세스 부분에서 'waitinf for' 를 검색하여 지연 이벤트가 무엇인지 확인합니다.

 PROCESS 20: 
---------------------------------------- 
SO: 0x7d2bd820, type: 2, owner: (nil), flag: INIT/-/-/0x00 
(process) Oracle pid=20, calls cur/top: 0x7d3d62d0/0x7d3d85dc, flag: (0) - 
int error: 0, call error: 0, sess error: 0, txn error 0 
(post info) last post received: 109 0 4 
last post received-location: kslpsr 
last process to post me: 7d2b8d94 1 6 
last post sent: 0 0 24 
last post sent-location: ksasnd 
last process posted by me: 7d2b8d94 1 6 
(latch info) wait_event=0 bits=0 
Process Group: DEFAULT, pseudo proc: 0x7d2ed5dc 
O/S info: user: oracle, term: pts/7, ospid: 19759 
OSD pid info: Unix process pid: 19759, image: goblin.forgotten.realms (TNS V1-V3) 

<cut> 

(session) sid: 141 trans: (nil), creator: 0x7d2bd820, flag: (41) USR/- BSY/-/-/-/-/- 
DID: 0001-0014-00000668, short-term DID: 0000-0000-00000000 
txn branch: (nil) 
oct: 6, prv: 0, sql: 0x62d01c34, psql: 0x7c20f24c, user: 542/SCOTT 
service name: SYS$USERS 
O/S info: user: oracle, term: pts/7, ospid: 19758, machine: goblin.forgotten.realms 
program: sqlplus@goblin.forgotten.realms (TNS V1-V3) 
application name: SQL*Plus, hash value=3669949024 
waiting for 'library cache lock' blocking sess=0x(nil) seq=36 wait_time=0 seconds since wait started=11 
handle address=62d064dc, lock address=79f88a68, 100*mode+namespace=c9 

• U락이 잡힌 오브젝트의 정보를 찾기 위해 핸들 주소를 사용합니다:

SO: 0x79f88a68, type: 53, owner: 0x7d3d62d0, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=79f88a68 handle=62d064dc request=S
call pin=(nil) session pin=(nil) hpc=0000 hlc=0000
htl=0x79f88ab4[0x79e71e60,0x79e71e60] htb=0x79e71e60 ssga=0x79e716fc
user=7d3a13b8 session=7d3a13b8 count=0 flags=[0000] savepoint=0xce
LIBRARY OBJECT HANDLE: handle=62d064dc mtx=0x62d06590(0) cdp=0
name=SCOTT.EMPLOYEES

라이브러리 오브젝트 락이 공유 모드(request=S)에서 요청되었으며 그 이름이 SCOTT.EMPLOYEE임을 알 수 있습니다.

• 같은 트레이스 파일에서 이 'handle address' 주소로 검색하여 자원에 락을 잡고 있는 프로세스가 무엇인지 찾습니다. 

PROCESS 18: 
---------------------------------------- 
SO: 0x7d2bcca8, type: 2, owner: (nil), flag: INIT/-/-/0x00 
(process) Oracle pid=18, calls cur/top: 0x79f3ab84/0x7d3d5fc8, flag: (0) - 
int error: 0, call error: 0, sess error: 0, txn error 0 
(post info) last post received: 109 0 4 
last post received-location: kslpsr 
last process to post me: 7d2b8d94 1 6 
last post sent: 0 0 24 
last post sent-location: ksasnd 
last process posted by me: 7d2b8d94 1 6 

<cut> 

SO: 0x75fe8f7c, type: 53, owner: 0x7b751914, flag: INIT/-/-/0x00 
LIBRARY OBJECT LOCK: lock=75fe8f7c handle=62d064dc mode=X 
call pin=(nil) session pin=(nil) hpc=0000 hlc=0000 
htl=0x75fe8fc8[0x79f81790,0x79fc3ef8] htb=0x79f81790 ssga=0x79f8102c 
user=7d3988d0 session=7d3988d0 count=1 flags=[0000] savepoint=0x146e 
LIBRARY OBJECT HANDLE: handle=62d064dc mtx=0x62d06590(0) cdp=0 
name=SCOTT.EMPLOYEES

위 결과에서 우리는 Process 18(pid)가 우리가 사용하고자 하는 오브젝트에 배타 락(mode=X)을 걸고 있는 것을 볼 수 있습니다. V$PROCESS 와 V$SESSION을 이용하여 이 프로세스의 sid, 사용자, 터미널, 프로그램, 등을 조회해 볼 수 있습니다.

이 세션이 실행했던 실제 문장도 이 트레이스 화일에서 확인 할 수 있습니다(문장들과 다른 라이브러리 오브젝트들은 "name=" 다음에 위치합니다).

방법 2: X$KGLLK 테이블에서 조사

X$KGLLK 테이블(SYS/INTERNAL로 조회 가능)은 모든 세션들이 잡고 있거나 요청중인 모든 라이브러리 케시 오브젝트들 담고 있으며 비록 의미를 알기 어려운 열이름들이 사용되지만 V$LOCK 뷰보다 더욱 완벽한 정보를 제공합니다.

여러분은 V$SESSION에 있는 세션 주소(SADDR) 을 찾아 보고 아래의 조회를 해 봄으로써 대기중인 세션에 의해 요청된 락들을 조사할 수 있습니다:

select sid,saddr from v$session where event= 'library cache lock';

SID SADDR
---------- --------
16 572ed244


select kgllkhdl Handle,kgllkreq Request, kglnaobj Object
from x$kgllk where kgllkses = '572ed244'
and kgllkreq > 0;

HANDLE   REQUEST   OBJECT
-------- ---------- ------------------------------------------------------------
62d064dc          2 EMPLOYEES

이 결과는 라이브러리 캐시 락을 요청하고 있는 세션(KGLLKREQ > 0) 과 함께 락을 잡고자 하는 오브젝트의 이름중 첫 80자리를 KGLNAOBJ로 보여주고 있습니다. KGLLKHDL 의 값은 앞에서 봤던 방법 1의 Systemstate 분석절차에서의 오브젝트에 대한 '핸들 주소' 에 해당됩니다.

만일 이 KGLLKHDL 이 X$KGLLK 의 다른 세션의 핸들과 일치한다면 블로킹 세션의 주소를 알수가 있게 됩니다. 이 락을 작고 있는 세션은 락을 잡고 있기 때문에 KGLLKMOD > 0 상태를 가지고 있을 것입니다.

select kgllkses saddr,kgllkhdl handle,kgllkmod mod,kglnaobj object
from x$kgllk lock_a
where kgllkmod > 0
and exists (select lock_b.kgllkhdl from x$kgllk lock_b
where kgllkses = '572ed244' /* blocked session */
and lock_a.kgllkhdl = lock_b.kgllkhdl
and kgllkreq > 0);

SADDR     HANDLE   MOD
--------  -------- ----------
OBJECT
------------------------------------------------------------
572eac94  62d064dc          3
EMPLOYEES

조금 더 살펴보면, v$session 에서 KGLLKSES 과 일치하는 SADDR 를 찾아 블러킹 세션에 대한 추가적인 정보를 확인할 수 있습니다:

select sid,username,terminal,program from v$session where saddr = '572eac94'

SID        USERNAME                  TERMINAL
---------- ------------------------------ ------------------------------
PROGRAM
------------------------------------------------
12          SCOTT                          pts/20
sqlplus@goblin.forgotten.realms (TNS V1-V3)

같은 방법으로, 모든 대기 세션들을 찾을 수 있습니다: 

select sid,username,terminal,program from v$session
where saddr in 
(select kgllkses from x$kgllk lock_a 
 where kgllkreq > 0
 and exists (select lock_b.kgllkhdl from x$kgllk lock_b
             where kgllkses = '572eac94' /* blocking session */
             and lock_a.kgllkhdl = lock_b.kgllkhdl
             and kgllkreq = 0)
);

SID        USERNAME                       TERMINAL
---------- ------------------------------ ------------------------------
PROGRAM
------------------------------------------------
13         SCOTT                           pts/22
sqlplus@goblin.forgotten.realms (TNS V1-V3)

16         SCOTT                           pts/7
sqlplus@goblin.forgotten.realms (TNS V1-V3)

Row cache lock

Row cache lock에 의한 경합

Buffer busy waits, read by other session

Buffer lock에 의하 경합

Library cache lock

Library cache lock에 의한 경합

Library cache pin

Library cache pin 에 의한 경합

DFS Lock handle

Global lock인 경우에 관찰