<11/11> 기대했단 말야

카페북 정리 PPT 발표

buffer 대기 이벤트를 알아보기 전에 먼저 버퍼를 어떤 순서로 탐색하는지 살펴보자

오라클은 해시 체인과 LRU, LRUW 리스트를 사용하여 사용자가 요청한 블록을 버퍼 캐시로 위치시킨다

1. 사용자가 요청한 블록의 DBA에 대해 해시 함수를 통해 해시 값을 생성하고 해시 값에 해당하는 해시 버킷을 찾는다

2. 해시 버킷을 보호하는 cache buffers chains 래치를 획득하고 select 모드라면 shared 모드로 DML 작업이라면 Exclusive 모드로 래치를 획득한다 만일 이때 경합이 발생하면 latch: cache buffers chains 이벤트를 대기하게 된다 해시 버킷을 통해 체인을 탐색해서 원하는 블록의 버퍼 헤더를 찾는다 여기서 찾을 경우 해당 버퍼에 대한 buffer lock을 Shared 혹은 Exclusive 모드로 획득하고 사용자가 원하는 작업을 수행한다

이때 만약 사용하고자 하는 블록을 다른 세션에서 사용중일 경우 경합이 발생하여 buffer busy waits 이벤트를 대기하게 된다 그리고 DBWR에 의해 기록중인 버퍼에 대해 buffer lock을 획득하는 과정에서 경합이 발생할 경우 write complete waits 이벤트를 대기하게 된다 

cache buffers chains 래치를 획득한 후 체인 탐색으로 버퍼를 읽는 이 과정을 Logical Reads라 한다 만일 이 Logical Read 작업이 consistent read 작업이라면 consistent gets 통계 값이 증가하고 current read 작업이라면 db block gets 통계값이 증가한다 따라서 session logical reads 통계값은 consistent gets 값과 db block gets 값의 합에 일치한다

3. 버퍼 캐시에 블록이 존재하지 않을 경우 우선 Working Set을 관리하는 cache buffers lru chain 래치를 획득하고 이때 경합이 발생하면 latch: cache buffers lru chain 이벤트를 대기한다 래치를 획득 후 LRU 리스트의 보조 리스트에서 프리 버퍼를 찾고 보조 리스트가 비었다면 메인 리스트에서 가장 덜 사용된 순서로 프리 버퍼를 찾는다 여기서 더티 버퍼를 찾을 경우 LRUW 리스트로 이동시키고 프리 버퍼를 찾게 되면 해당 버퍼에 대해 buffer lock을 Exclusive하게 획득하고 데이터 파일로부터 블록을 해당 버퍼로 읽어 드린다

이 buffer lock을 획득하는 과정에서 경합이 발생하면 read by other session 이벤트를 대기하게된다 데이터 파일로부터 물리적으로 블록을 읽어 들이는 이 작업을 Physical reads라 하고 발생한 블록 수 만큼 Physical reads 통계 값이 증가한다 이 값은 direct path I/O 작업에서도 증가하기 때문에 버퍼 캐시를 경유한 정확한 Physical reads 값은 physical reads direct, physical reads direct(lob) 값을 빼면 된다.

4. LRU 리스트에서 프리 버퍼를 찾을 때 _DB_BLOCK_SCAN_MAX_PCT(default 40) 파라미터의 값 만큼 스캔을 하고도 프리 버퍼를 찾지 못하면 서버 프로세스는 LRU 리스트의 스캔을 멈추고 DBWR에게 더티 버퍼를 파일에 기록하고 프리버퍼를 확보할 것을 요청한다 이 DBWR에 의해 프리 버퍼가 확보될 때까지 서버 프로세스는 free buffer waits 이벤트를 대기한다 

요청받은 DBWR은 DBWR make free request 통계 값을 증가시키고 cache buffers lru chain 래치를 획득 후 LRUW 리스트를 cold area부터 탐색한다 디스크에 기록할 버퍼를 찾게 되면 buffer lock을 획득한 후 버퍼를 디스크로 기록한다 디스크에 기록된 버퍼는 프리 버퍼로 변경되고 LRU 리스트로 옮겨진다 DBWR이 LRUW 리스트를 탐색할 때마다 DBWR lru scans 통계값과 DBWR buffers scanned 통계 값이 증가한다

# 오라클은 과다한 체인 탐색에 의한 래치 경합을 줄이기 위해 Buffer Pining 기법을 사용한다 특정 세션의 동일 SQL 콜(Call 내에서 2번 이상 스캔된 버퍼는 Pinned 상태로 변환) Pinned 상태란 래치를 획득하지 않고도 곧바로 버퍼 영역으로 갈 수 있도록 버퍼의 위치를 저장하고 있다는 의미 Pinned 상태의 버퍼는 버퍼 캐시에서 밀려나지 않기 때문에 지정된 위치를 이용해 래치 획득 없이 곧바로 찾아갈 수 있다 Pinned 상태의 버퍼라 하더라도 메모리 I/O 는 발생하며 대신 래치 획득과 해시 체인 탐새과 같은 부가적인 작업이 줄어든다

Select / Select 에 의한 read by other session

보통 SGA에 적재된 블록을 읽을 때는 Shared 모드로 buffer lock을 획득하기 때문에 읽기 작업에 의한 buffer lock 경합이 발생하지 않는다 하지만 물리적 I/O가 발생하여 블록을 새롭게 SGA에 올리는 것은 버퍼를 새로 생성하는 작업을 요구하므로 최초로 버퍼를 생성하는 프로세스는 buffer lock을 Exclusive하게 획득하게 된다 따라서 해당 블록을 읽기 위해 buffer lock을 shared 모드로 획득하려는 다른 세션들은 Exclusive한 buffer lock이 해제될 때까지 기다려야 한다 이로 인해서 발생하는 이벤트가 read by other session다 이것은 하드 파싱이 발생하는 경우에 해당 SQL Cursor에 대해 library cache pin을 Exclusive하게 획득하는 것과 유사한 과정이다

Select / Select 에 의한 read by other session 대기를 줄이는 방법은 다음과 같다

 - SQL 최적화를 통한 최소한의 I/O 만으로 원하는 결과를 가져오도록 해야 한다

 - SGA 사이즈가 시스템 전반적인 I/O량에 비해 작다면 SGA 물리적 크기를 늘려야 한다

Select / Update에 의한 Buffer busy waits / read by other session

오라클의 select는 기본적으로 consistent read에 기반하므로 실제 읽어야 할 데이터 블록이 변경되었다면 해당 블록의 과거 이미지를 가지고 있는 CR 블록을 읽어야 한다 이때 CR 블록이 현재 버퍼 캐시에 없을 경우에 디스크의 언두 블록에서 읽어 들어야 한다 만일 여러 세션이 동시에 언두 블록에 대해 읽기를 시도할 경우 언두 블록은 메모리에 올리는 과정에서 buffer lock 경합이 발생하게 된다 select /update에 의한 buffer lock 경합은 다음과 같은 상황에서 발생한다

 - 특정 프로세스가 특정 테이블을 변경한다 데이터의 이전 이미지가 언두 블록에 기록된다

 - 동시에 많은 프로세스들이 변경된 데이터에 대해 읽기를 시도한다

일반적으로 Select 와 Update는 서로 간에 경합을 일으키지 않는다고 알려져있지만 언두 블록을 읽는 과정에서 select / select의 경우와 동일한 상황의 buffer lock 경합이 발생하고 이로 인해 read by other session 이벤트 대기가 발생한다

Update / Update 에 의한 buffer busy waits

동시에 여러 개의 세션이 같은 로우를 업데이트 하는 것은 기본적으로 TX락에 의해 동기화가 이루어진다 하지만 동시에 여러 개의 세션이 서로 다른 로우를 업데이트하는 경우에도 만일 해당 로우들이 같은 블록 안에 있다고 하면 buffer lock에 의한 동기화가 필요하다 이 과정에서 경합이 발생하면 buffer busy waits 이벤트를 대기한다 

이 경우에 발생하는 buffer lock에 의한 경합은 TX 락에 의한 경합과는 전혀 다른 성격을 가지고 있다 TX락 경합이 발생하는 경우에는 락을 획득한 락 홀더(Lock Holder)의 트랜잭션을 종료(commit,rollback,kill)하는 것만이 유일한 해결책이다 하지만 동일 블록을 변경하는 과정에서 발생하는 buffer lock 경합은 그 해결책이 전혀 다르다

일단 동시에 여러 프로세스가 동일 블록을 변경하는 과정에서의 모습은 동일한 블록에 대해 buffer lock을 exclusive하게 획득하기 위해 대기하게 된다 이는 프로세스들이 각각 서로 다른 로우를 업데이트하지만 같은 블록을 변경하는 것만으로 매우 심각한 성능 문제를 야기하게 된다 인덱스의 경우에도 buffer lock 경합이 발생할 수 있다 인덱스 세그먼트의 동일 leaf block에 대해 블록 변경 요청이 동시 다발적으로 발생하면 buffer busy waits 대기에 의한 성능 저하가 생긴다

그리고 언두 헤더 블록에 대한 경합도 많이 나타난다 이것은 동시에 여러 프로세스가 Update에 의한 언두 데이터를 생성하면서 언두 헤더 블록을 변경하는 과정에서 발생한다 Update / Update에 의한 buffer lock 경합이 발생하는 경우에는 다양한 해결책들이 제시되는데 해결책이 많다는 것은 거꾸로 Update / Update에 의한 경합이 매우 보편적이라는 것을 반증하며 Buffer lock 경합이 발생하는 원인이 서로 다른 로우가 같은 블록에 있다는데서 기인하므로 서로 다른 로우를 다른 블록에 흩어지게끔 분산시키는 방법이 가장 보편적으로 사용된다 로우 분산시키는 방법으로는 여러가지가 있는데

 - PCTFREE 값을 높게 준다 로우를 분산시키는 가장 확실한 해결책이지만 공간의 낭비를 초래하고 그로 인해  Full table scan이나 Index Full Scan Index Range Scan 성능에 영향을 주게 된다 뿐만 아니라 동일한 데이터를 처리하기 위해 생성해야할 블록의 수가 늘어나므로 SGA내의 버퍼 캐시에 대한 낭비를 초래하고 이로 인해 cache buffers chains 래치 경합과 관련된 성능 문제를 야기할 수 있다

 - 파티셔닝(Partitioning) 기법을 사용하여 각 로우들을 물리적으로 다른 블록으로 흩어지게끔 한다 이 경우 PCTFREE를 높게 주는 방법에 비해 공간을 낭비하는 문제는 없지만 업무 로직 변경에 의해 Update 하는 방식이 바뀌면 같은 문제가 반복될 가능성이 있다

 - 작은 블록 크기를 사용한다 이것은 블록 사이즈가 작으면 자연스럽게 한 블록 안에 들어가는 로우수가 줄어든다는 것을 이용하는 것이며 오라클 9i 부터는 블록 크기가 서로 다른 테이블 스페이스를 생성할 수 있다 작은 블록 크기를 사용하는 테이블스페이스를 생성한 다음 문제가 발생한 테이블이나 인덱스를 MOVE 함으로써 문제를 해결할 수 있다 하지만 이경우에도 Full table scan이나 Index Full Scan Index Range Scan 성능에 부정적인 영향을 준다 또한 블록의 수가 늘어나므로 cache buffer chains 래치와 관련된 성능 문제를 야기할 수 있다.