Oracle 데이터베이스 성능 튜닝: Buffer Busy Waits 이해 및 해결 방안

Buffer Busy Waits 이벤트의 기술적 개요

오라클 데이터베이스에서 buffer busy waits는 세션이 메모리 내의 특정 블록에 접근할 때 발생하며, 주로 다른 세션이 해당 블록을 독점적으로 점유하고 있거나 수정 중일 때 대기 시간이 길어지는 현상을 의미합니다. 이는 논리적 읽기 (Logical Read) 과정에서 캐시된 버퍼 헤더를 획득하려는 시도와 직접적인 연관이 있습니다.

버퍼 액세스 메커니즘과 뮤텍스 경쟁

세션이 데이터를 가져오거나 수정할 때 시스템은 다음과 같은 단계를 거칩니다:

대상 파일 번호와 블록 번호를 기반으로 해시 알고리즘을 적용하여 위치를 계산합니다.
계산된 해시값에 대응하는 버킷 (Bucket) 을 탐색합니다.
해당 버킷 내에 연결된 체인 (Chain) 을 순회하며 목표 버퍼 헤더 (BH) 를 식별합니다. 이 과정에서는 CBC (Cache Buffers Chains) Latch가 필수적입니다.
목표 BH 를 찾았으면 버퍼 주소를 추출하고, 데이터 일관성을 위해 Buffer Pin을 획득해야 합니다.
필요한 경우 Latch 를 해제한 후 실제 데이터 읽기 또는 쓰기 작업을 수행합니다.
작업 완료 후 다시 Latch 를 획득하여 Buffer Pin 을 해제하고 상태를 갱신합니다.

여기서 buffer busy waits는 세션이 BH 를 찾았으나 즉시 Buffer Pin 을 할 수 없는 상황에서 발생합니다. 하나의 블록에 대해 여러 세션이 동시에 쓰기 권한 (Exclusive Mode) 을 요청하거나, 읽기 세션이 수정 중인 블록을 읽으려 할 때 충돌이 일어나 대기하게 됩니다. 이는 단순한 락 (Lock) 대기보다 더 저수준的竞争 상태로, CPU 스핀보다 효율적이지만 과도하면 성능 저하를 초래합니다.

대기 원인 분석 및 진단 쿼리

성능 병목 지점을 파악하기 위해서는 어떤 객체나 블록 타입이 대기를 유발하는지 확인해야 합니다. 기존 방법론을 보완하여 더 명확한 정보를 얻는 쿼리를 구성할 수 있습니다.

대기 중인 객체 식별

V$SESSION 와 DBA_OBJECTS 뷰를 조인하여 대기 중인 물리적 파일과 로직적인 테이블 이름을 매핑합니다.

SELECT s.sid,
       sw.event,
       sw.p1 AS file_id,
       sw.p2 AS block_id,
       obj.owner,
       obj.object_name,
       obj.object_type
  FROM v$session s
  JOIN v$session_wait sw ON s.sid = sw.sid
  LEFT JOIN dba_objects obj 
      ON s.row_wait_obj# = obj.data_object_id
 WHERE sw.event = 'buffer busy waits'
   AND s.status = 'ACTIVE';

블록 유형별 최적화 전략

P3 파라미터 값과 대기되는 블록의 클래스 ID 에 따라 문제의 근원이 다르므로 각각의 상황에 맞는 조치를 취해야 합니다.

데이터 블록 (Data Block) 의 핫 스팟

동일한 데이터 블록이 여러 트랜잭션에 의해 빈번히 수정되는 경우입니다. 예를 들어 시퀀스 키나 타임스탬프 필드가 많은 행을 담고 있을 때 발생할 수 있습니다.

패러렐 도미네이션 조정: 너무 높은 Degree 의 Parallel Query 가 동일한 덩어리를 공격하지 않도록 제한합니다.
인덱스 구조 변경: 연속된 값을 생성하는 열에 대해 역순 인덱스 (Reverse Key Index) 를 사용하여 블록 분산을 유도합니다.
블록 크기 최적화: 기록 당 바이트 수를 줄이기 위해 작은 블록 사이즈의 테이블Spaces 를 활용하거나, PCTFREE 설정을 높여 페이지 내부에 남는 공간을 늘립니다.

세그먼트 헤더 (Segment Header) 경쟁

주로 Free List 관리를 위해 헤더 블록에 접근할 때 발생합니다. 빈도가 높은 영역 확장 (HWM 증가) 이나 자유 공간 관리가 원인일 수 있습니다.

Extent Size 증대: 지역 관리 모드 (Locally Managed Tablespace) 를 사용하여 프리리스트 컨텐션을 방지합니다.
PCTUSED/PCTFREE 간격 조정: 데이터 블록이 자주 프리리스트에서 오가는 것을 막기 위해 사용 임계치 사이의 격차를 충분히 확보합니다.

언도 (Undo) 관련 대기

Undo Segment 헤더 또는 블록 자체에서 대기가 발생하는 경우입니다.

Segment Header (#17):_undo_ 섹터가 부족하여 헤더 잠금 경쟁이 심화된 것입니다. 자동 관리 UNDO 모드를 사용하거나 rollback segment 수를 늘립니다.
Undo Block (#18): 장기 실행 쿼리가 활성 트랜잭션의 과거 버전 읽기에 필요한 언도 블록을 필요로 할 때 발생합니다. 작업 스케줄을 분리하여读写 혼합 부하를 줄이는 것이 효과적입니다.

현상 재현을 위한 테스트 시나리오

실제 환경에서 문제가 발생하는지 확인하거나 교육 목적으로 contention 을 유도하는 방법을 설계할 수 있습니다. 두 개의 독립 세션에서 동일한 블록 속 서로 다른 레코드를 빠르게 업데이트하도록 구성된 프로시저 예시입니다.

세션 A 스크립트 (고부하 업데이트)

DECLARE
    l_iter_cnt NUMBER := 30000000;
BEGIN
    FOR idx IN 1 .. l_iter_cnt LOOP
        UPDATE target_table 
        SET modification_col = modification_col + 1 
        WHERE record_identifier = 'AAAR4jAAMAAAAh7AAA';
        
        IF MOD(idx, 5000000) = 0 THEN
            NULL; -- 주기적인 체크포인트 또는 로그 생략
        END IF;
    END LOOP;
END;
/

세션 B 스크립트 (경쟁자)

DECLARE
    l_limit CONSTANT NUMBER := 30000000;
BEGIN
    FOR i IN 1 .. l_limit LOOP
        UPDATE target_table 
        SET modification_col = modification_col - 1 
        WHERE record_identifier = 'AAAR4jAAMAAAAh7AAB';
    END LOOP;
    COMMIT;
END;
/

위와 같이 실행되면, 동일한 파일 블록 내의 두 행이 수정되면서 CBC Latch 와 Buffer Pin 간의 경쟁이 발생하게 되며, v$session_wait_history 를 통해 이를 모니터링할 수 있습니다.

SELECT sid, 
       event, 
       p1text, p1 AS val_p1,
       p2text, p2 AS val_p2,
       p3text, p3 AS val_p3,
       time_waited
  FROM v$session_wait
 WHERE event = 'buffer busy waits'
   AND sid IN (SELECT sid FROM v$mystat WHERE rownum = 1);

공식 문서 참조 사항

해당 웨이트 이벤트에 대한 정확한 정의 및 권장 사항은 Oracle 공식 서버 제품 문서에서 확인 가능합니다. 특히 인스턴스 튜닝 가이드에는 다중 프로세스 접속 시의 버퍼 캐시 동작 원리에 대한 상세 설명이 포함되어 있으며, 이를 기반으로 P3 코드 (Reason Code) 를 해석하여 구체적인 조치 방안을 수립해야 합니다.

태그: Oracle database-performance sql-tuning buffer-cache waiting-events

5월 29일 00:23에 게시됨

괴물 클럽