[I. 오라클 아키텍처] DB Lock(Pin)

03. DB Lock(Pin)

 (01) DB Lock 개요

래치를 빨리 놓지 않으면 경합이 발생한다

DB 버퍼캐시에서 원하는 버퍼블럭을 찾았다면 가급적 빨리 쥐고 있는 래치를 해제해야 한다.

그렇지 않으면 하나의 cache buffers chains 래치에는 

여러 개의 해시 체인이 달려 있기 때문에 경합 발생 가능성이 증가하게 된다. 

그런데 그 버퍼에 먼저 접근한 선행 프로세스가 아직 버퍼를 사용중이라면 어떻게 해야 할까?

블럭에 대해 작업을 수행하려면 Lock을 얻어야 한다

아주 짧은 순간이라도 두 개 이상의 프로세스가 

동시에 버퍼내용을 읽고 쓴다면 데이터 정합성에 문제가 생길 수 있기 때문에,

캐시된 버퍼 블럭을 읽거나 변경하려는 프로세스는 먼저 버퍼헤더에서 버퍼 Lock을 획득해야 한다.

버퍼 Lock은 프로세스 작업 내용에 따라 각각 다른 모드로 얻는다.

• 읽기작업 : share 모드
• 쓰기작업 : Exclusive 모드

버퍼 Lock을 획득했다면 이제 안심하고 블럭을 읽고 쓸 수 있을 뿐만 아니라,

래치에 대한 경합 가능성을 줄이기 위해서라도 획득했던 래치를 곧바로 해제한다.

먼저 배타적 Lock을 얻은 프로세스가 있다면 대기한다

만약 래치를 획득하고 목표 버퍼를 찾았는데,

다른 프로세스가 버퍼 Lock을 Exclusive 모드로 점유한 채 내용을 갱신중이라면 마냥 래치를 쥔 채 대기할 수는 없다.

그럴 때는 버퍼헤더에 있는 버퍼 Lock 대기자 목록(Waiter list)에 자신을 등록하고 일단 래치를 해제한다.

버퍼 Lock 대기자 목록에 등록되어 있는 동안에는

buffer busy waits 대기 이벤트가 발생한다.

작업이 완료되어 Lock을 해제할 때도 래치가 필요하다

대기자 목록에서 기다리다가 비로소 Lock을 획득했다면 그제서야 원했던 작업을 진행할 수 있다.

목적한 읽기/쓰기 작업을 완료하면 버퍼 헤더에서 버퍼 Lock을 해제해야 하는데,

이 때도 버퍼 헤더를 액세스하려는 다른 프로세스와 충돌이 생길 수 있으므로,

해당 버퍼가 속한 체인 래치를 다시 한번 획득해야 한다.

버퍼 Lock을 해제하고 래치를 해제해야 비로소 하나의 블럭 읽기가 완료된다.

읽으려는 블럭이 버퍼 캐시에 없는 경우에는 디스크 I/O 까지 수반되므로,

하나의 블럭 읽기가 얼마나 고비용인지 실감할 수 있다.

한 번의 래치획득만 이루어지는 오퍼레이션도 있다

유명한 조나단 루이스(Jonathan Lewis)의 설명에 의하면,

버퍼 블럭을 읽을 때 대부분 두 번의 래치 획득을 요하지만(Pin할 때 한 번, 해제할 때 한 번)

몇몇오퍼레이션에서는 래치를 쥔 채 버퍼 블럭을 읽기 때문에 래치 획득이 한 번만 일어난다고 한다.

이것은 v$sysstat 뷰에서 consistent gets - examination 통계항목으로 측정된다.

 (02) 버퍼 핸들

Pin은 버퍼 헨들을 이용하여 설정한다

버퍼 Lock을 설정하는 것은 자신이 현재 그 버퍼를 사용중임을 표시해 두는 것으로서,

그 버퍼헤더에 Pin을 걸었다고 표현한다.

버퍼 헤더에 Pin을 설정하려고 사용하는 오브젝트를 버퍼핸들 이라고 부르며,

버퍼핸들을 얻어 버퍼 헤더에 있는 소유자 목록(Holder List)에 연결시키는 것으로 Lock을 설정한다.

한 블럭에 가능한 버퍼 Lock 개수는 작업 내용에 따라 다르다

읽기작업을 위해서라면 share모드로 Pin을 걸어두므로 

한 시점에서 여러 프로세스가 동시에 Pin할 수 있지만,

쓰기작업을 위해서라면 exlusive모드로 Pin을 걸어두므로 

한 시점에서 한 프로세스만 Pin을 설정할 수 있다.

오퍼레이션이 많다면 버퍼 핸들 래치가 경합지점이 된다

버퍼 핸들도 공유된 리소스이므로 버퍼 핸들을 얻으려면 cache buffer handles 래치 획득을 필요로 한다.

버퍼를 Pin 하는 오퍼레이션이 많을수록 작업 그 자체보다 핸들 래치가 경합지점이 되기 때문에,

오라클은 각 프로세스마다 _db_handles_cached(기본값 5) 만큼의 버퍼 핸들을 미리 할당해준다.

각 세션은 이를 캐싱하고 있다가 버퍼를 Pin 할 때 마다 사용하며,

그 이상의 버퍼 핸들이 필요할 때만 핸들 래치를 얻고 추가로 버퍼 핸들을 할당받는다.

시스템 전체적으로 사용할 수 있는 총 버퍼 핸들 개수는 _db_handles 피라미터에 의해 결정되며,

이것은 process 피라미터와 _db_handles_cached피라미터를 곱한 값으로 결정된다.

 (03) 버퍼 Lock의 필요성

오라클에는 로우단위 Lock도 있다

값을 변경하는 작업 이전에 갱신대상 로우에 다시 로우단위 Lock을 걸고 변경작업을 진행한다.

이러한 로우단위 Lock은 레코드의 속성(Lock flag)를 수정함으로써 설정된다.

로우단위 Lock이 있음에도 버퍼 Lock을 사용하는 것은 I/O단위가 블럭이기 때문이다

특정 레코드를 수정해야 할 때 로우단위 Lock이라는 시스템이 이미 존재함에도 불구하고,

그 레코드가 담긴 전체블럭에 Lock을 걸어야 하는 이유는

오라클은 하나의 레코드만 갱신해야 하더라도 블럭 단위로 I/O를 수행해야 하기 때문이다.

때문에 같은 블럭내의 각기다른 레코드를, 서로 다른 프로세스가 갱신작업을 수행한다면.

데이터의 정합성이 깨질 위험이 있을 뿐만 아니라,

위에서 설명했다시피 로우단위 Lock도 레코드의 속성변경으로 이루어지는 작업이므로

대상 로우가 서로 다르더라도 같은 블럭 내 레코드에 대해 로우단위 Lock을 시도하여도 문제가 된다.

거기에 블럭헤더의 내용을 변경하려는 작업도 동시에 일어날 수 있기 때문에,

블럭 자체로의 진입을 직렬화 해야하는 것 이다.

Pin된 버퍼블럭은 밀려나지 않는다

참고로 Lock된 버퍼블럭은 버퍼 캐시 전체를 비우기 위하여

아래 시스템 명령어를 날리더라도 밀려나지 않는다.

   SQL> alter system flush buffer_cache;

 (04) 버퍼 Pinning 기능

버퍼 Pinning이란 재방문 가능성이 높은 블럭의 Pin을 계속해서 쥐고 있는 것이다

버퍼 Pinning은 버퍼를 읽고 나서 버퍼 Pin을 즉각 해제하지 않고,

하나의 데이터베이스 call(parse call, execute call, fetch call)이 진행되는 동안 유지하는 기능을 말한다.

같은 블럭을 재방문할 가능성이 큰 블럭을 Pinning 한다면,

래치획득 과정이 생략되므로 논리적인 블럭 읽기(Logical Reads) 횟수를 획기적으로 줄일 수 있다.

클러스터링 팩터가 좋다면 버퍼 Pinning 효과가 극대화된다

인덱스를 통해 얼마 안되는 테이블을 읽어도 성능이 매우 안 좋은 경우가 있는 반면에,

대량의 테이블을 읽어도 매우 빠를 때가 있는데.

이러한 현상은 인덱스 클러스터링 팩터와 관련이 있고,

그 이면에는 버퍼 Pinning을 이용한 블럭 I/O 감소효과와 관련이 있다.

클러스터링 팩터가 좋다면, (테이블에 저장된 실제 테이블 정렬순서가 인덱스 키 값 정렬순서와 거의 일치한다면,)

이 인덱스를 사용했을 경우 같은 테이블 블럭을 반복 액세스할 가능성이 매우 높아지기 때문에

버퍼 Pinning으로 인한 Logical Reads 횟수가 감소했기 때문이다.

재방문 가능성이 높은 블럭

따라서 오라클은 재방문 가능성이 높은 다음 블럭들을 Pinning하기 시작했으며,

버전이 높아질수록 그 범위를 점차 확대하고 있다.

• 인덱스 사용 시, 인덱스의 리프블럭
• 인덱스로부터 액세스되는 하나의 데이터 블럭 (8i 부터)
• NL 조인시 룩업 테이블에 접근하려고 사용하는 인덱스의 인덱스 루트 블럭 (9i 부터)
• Index Skip Scan에서 리프블럭 바로 직전의 브랜치 블럭 (9i 부터)
• NL 조인시 룩업 테이블에 접근하려고 사용하는 인덱스의 모든 블럭 (11g 부터)
• DML 수행시 Undo 레코드를 기록하는 Undo 블럭 (11g 부터)

버퍼 Pinning 기능을 이용하여 튜닝할 수 있다

도저히 튜닝 방안을 찾을 수 없다면 버퍼 Pinning 효과를 노려

자주 사용하는 인덱스의 키 값 정렬 순서대로 테이블 레코드를 재정렬한다면 

성능을 획기적으로 개선할 수 있다.