3._버퍼_Lock

조회 수 5973 추천 수 0 2012.03.06 21:55:21
박영창 *.193.94.20

            1.jpg


03. 버퍼 Lock
(1)  버퍼 Lock 이란?

* 버퍼 Lock
DB 버퍼 캐시내 버퍼 블록을 찾은 이후 데이터 정합성을 위해서 프로세스가 버퍼 헤더에서 획득하는 Lock
# 버퍼 lock 을 획득하는 과정에는 cache buffer latch 를 이용한 사전 작업이 이루어짐을 알 수 있음

* 버퍼 Lock 의 두가지 모드
Share 모드 : 버퍼 내용을 읽기만 할 경우 (multi process -read only- at a time)
Exclusive 모드 : 버퍼 내용을 변경할 경우 (one process -read and write- at a time)
※ select 문일지라도 buffer cleanout 이 필요할 경우 데이터 변경이므로 exclusive lock 획득
# select 문의 경우에도 버퍼캐시에 없는 데이터를 조회할 경우 물리 디스크 상의 데이터를 버퍼 캐시로
 올리는 과정에서 버퍼 캐시의 내용을 변경해야하는 경우가 발생함. 이 과정중에 버퍼 캐시의 사이즈가
 없을 경우 공간 확보를 위해서 기존에 할당된 버퍼를 디스크 상으로 내리는 과정이 진행됨.

* 버퍼 Lock 대기모드
찾아낸 버퍼 캐시상의 대상 버퍼가 타 프로세스에 의해 exclusive lock 이 획득된 상태
-> 버퍼헤더에 있는 버퍼 Lock 대기자 목록에 등록 후 대기
# 해당 요청을 했던 프로세스에 할당된 cache buffer chain latch 는 이 시점에 해지
 이렇게 동작해야만 또 다른 프로세스가 해당 버퍼 캐시에 요청을 했을때 latch wait 이 발생하지 않고
 버퍼lock 대기자 목록에 자신의 정보를 기록할 수 있음.
-> buffer busy waits 대기 이벤트 발생
-> lock 해지 이후 해당 버퍼의 버퍼 Lock 획득 과정에서 버퍼 체인 latch 를 재획득 이후 작업 진행

버퍼 블록을 읽을때 대부분 두번의 래치 획득요구. (버퍼 헤더 검색 후 Pin / Un-pin)
일부 작업의 경우 래치를 획득한 상태로 버퍼 블록을 읽기 때문에 래치 획득이 한번만 일어남.
(consistent gets - examination 항목)

15:14:25 SQL> select version, status from v$instance;

VERSION                            STATUS
---------------------------------- ------------------------
11.2.0.3.0                         OPEN

Elapsed: 00:00:00.01
15:08:51 SQL> select * from v$sysstat where name like 'consistent%'
15:09:07   2  /

STATISTIC# NAME                                CLASS      VALUE    STAT_ID
---------- ------------------------------ ---------- ---------- ----------
       72 consistent gets                         8    4734798 4162191256
       73 consistent gets from cache              8    4734798 2839918855
       74 consistent gets from cache (fa          8          0 2173751438
          stpath)

       75 consistent gets - examination           8    1362299 1701530557
       76 consistent gets direct                  8          0  420374750
       85 consistent changes                      8      36888 3876379665

6 rows selected.

Elapsed: 00:00:00.00
15:09:09 SQL>


(2) 버퍼 핸들

* 버퍼 Lock
버퍼Lock (~버퍼Pin) : 변경시에는 하나의 프로세스만 Pin 설정 가능. 읽기 작업에는 여러개 프로세스가 동시에 Pin 설정

* 버퍼 핸들
버퍼 헤더에 Pin 설정을 위해서 사용하는 오브젝트.
# 윈도우 핸들처럼 오라클 자체적으로 각각의 버퍼의 구분 및 관리 목적으로 사용하는 값을 의미하는 것으로 추측.

버퍼 핸들 획득 -> 버퍼 헤더에 있는 소유자 목록에 연결 -> Pin을 설정.
버퍼 핸들 공유 자원 -> 핸들 획득 과정중 cache buffer handles latch 이용 -> 접근을 직렬화
버퍼 Pin 오퍼레이션 증가 -> 버퍼 핸들 부분이 경합 지점. (bottle neck)

각 프로세스마다 _db_handles_cached 개수 만큼의 버퍼 핸들을 미리 할당. (기본 값은 5)
-> 각 프로세스는 해당 핸들을 캐싱하여 가지고 있다가 우선 사용
# 프로세스의 작업 처리에 필요한 버퍼를 다시 캐시상에서 검색하여 찾는 과정을 생략하고 direct 접근하기 위해
 캐싱하는 것으로 추측
-> 더 필요할때 cache buffer handles 래치를 획득후 추가 버퍼 핸들을 획득

시스템 전체 버퍼 핸들 개수 (_db_handles) = processes * _db_handles_cached

12:34:36 SQL> select version, status from v$instance;

VERSION                            STATUS
---------------------------------- ------------------------
11.2.0.3.0                         OPEN

Elapsed: 00:00:00.01
12:34:46 SQL> select a.ksppinm, b.ksppstvl from x$ksppi a, x$ksppsv b where a.indx=b.indx and a.ksppinm like '%_db_handles%';

KSPPINM                        KSPPSTVL
------------------------------ ------------------------------
_db_handles_cached             8
_db_handles                    1200

Elapsed: 00:00:00.01
12:34:52 SQL> show parameter processes

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
aq_tm_processes                      integer                1
db_writer_processes                  integer                1
gcs_server_processes                 integer                0
global_txn_processes                 integer                1
job_queue_processes                  integer                1000
log_archive_max_processes            integer                4
processes                            integer                150


(3) 버퍼 Lock 의 필요성
* 단일 레코드 갱신시의 버퍼 lock 필요성
단일 레코드를 갱신
-> 사용자 데이터 변경시 DML Lock 을 통해서 정합성을 유지.
# DML lock 만으로는 정합성 유지에는 불충분
-> Oracle I/O는 블록 단위로 발생
-> 블록내의 저장된 10개의 레코드를 읽는 짧은 순간 동안 다른 프로세스에 의해 변경 발생 가능
-> 버퍼 Lock 필요.


// Consistent 모드 읽기 : 쿼리 SCN과 블록 SCN을 확인하여 읽어도 되는 블록인지 확인하며 읽는 모드.
  Read 가능 버전이라고 확인후 읽어도 다른 프로세스가 변경 가능하기 때문에 Buffer Lock 이 필요.
  Undo 에서 읽어도 된다고 생각해서는 안됨.

* row-level lock 에서의 버퍼 lock 필요성
row-level lock 설정 자체도 레코드의 속성을 변경
-> 두 프로세스가 동시에 row-level lock 설정할 경우 문제 발생
-> 블록 SCN 변경 / ITL 슬록 변경도 동일
-> 블록 진입 자체를 직렬화 해야함

* Pinned 블록의 flush
pinned block 의 경우 아래의 명령어를 이용해도 캐시에서 비워지지 않음.
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM SET EVENTS ‘immediate trace name flush_cache’;
      (9i의 경우 2번 명령어를 이용해서 강제 flush 가능)


(4) 버퍼 Pinning
* 정의
버퍼 읽은 뒤 Pin 을 즉시 해제하지 않고, database call 이 진행되는 동안 유지하는 기능.
> 동일 블록을 반복적으로 읽을때 버퍼 Pinning을 통해 래치 획득 과정을 생략한다면 논리적인 블록 읽기 횟수를 획기적으로 줄일 수 있음.

session logical reads : latch 획득을 통해 블록 액세스할 경우
buffer is pinned count : latch 획득 없이 블록 액세스할 경우
13:04:24 SQL> select * from v$sysstat where name like '%session logical reads%' or name like '%buffer is pinned count%'
13:04:33   2  /

STATISTIC# NAME                                                    CLASS      VALUE    STAT_ID
---------- -------------------------------------------------- ---------- ---------- ----------
       12 session logical reads                                       1  378869371 3143187968
       13 session logical reads in local numa group                   1          0 1564026751
       14 session logical reads in remote numa group                  1          0 3011877906
      573 buffer is pinned count                                     72     446070 1300470380

Elapsed: 00:00:00.01



* 버퍼 pinning 의 생명주기
하나의 데이터베이스 call (parse call, execute call, fetch call) 에서만 유효함.

* 버퍼 pinning 의 적용 범위 (전통적 예시)
인덱스를 스캔하면서 테이블을 액세스할 때의 인덱스 리프 블록.
index range scan 하면서 인덱스와 테이블 블록을 교차 방문할 때의 블록 I/O를 체크하면 테이블 블록 I/O만 계속 증가하는 이유.
-> clustering factor 가 좋은 인덱스의 경우 효과는 극대화 됨.
# 인덱스 키 값의 순서와 인덱스가 가르키는 테이블 블록 rowid 의 정렬 순서가 거의 동일할 경우
-> 8i 부터는 인덱스로부터 액세스되는 하나의 테이블 블록을 pinning 하기 시작.

* 버퍼 pinning 적용 범위의 확대
9i Nested Loop Join 에서 inner 테이블 lookup 을 위해서 사용되는 인덱스 루트 블록을 pinning
  Index Skip Scan 에서 브랜치 블록을 거쳐 리프 블록을 액세스 하는 동안에도 브랜치 블록을 pinning
11g Nested Loop Join 인덱스 루트 블록 이외의 블록들도 pinning
   DML 수행시 undo 레코드를 기록하는 undo 블록도 pinning 적용

* 정리
버퍼 lock 은 내부 매커니즘이며 이해하기 어렵고 몰라도 무관한 내용이지만 튜닝 관점에서는 중요한 내용임. 버퍼 pinning 을 통한 블록 I/O 감소효과는 튜닝에서 간과할 수 없는 요소.