Bong's Log

오라클 데이터베이스를 운영하다 보면 가장 빈번하게 마주치는 대기 이벤트(Wait Event)가 바로 enq: TX - ...로 시작하는 트랜잭션 락(Transaction Enqueue) 경합입니다. 흔히 TX Lock이라고 하면 "두 세션이 같은 로우(Row)를 동시에 수정하려다 발생하는 락"으로만 단순하게 생각하기 쉽습니다.

하지만 오라클 아키텍처 내부를 들여다보면, 서로 전혀 다른 로우를 수정하거나 심지어 단순 INSERT만 수행하는 상황에서도 TX Lock 경합이 발생할 수 있습니다. 오라클 10g부터는 이러한 TX Lock의 발생 원인을 직관적으로 구분할 수 있도록 대기 이벤트를 4가지 세부 유형으로 세분화했습니다.

이번 포스팅에서는 데이터 아키텍트(DA)와 DBA가 반드시 숙지해야 할 TX Lock의 내부 동작 메커니즘과 4가지 주요 대기 이벤트(Row Lock Contention, ITL Shortage, Index Contention, Bitmap Index)의 발생 원리 및 실무 해결 방안을 완벽하게 정리합니다.

1. 개요: TX Lock(Transaction Enqueue)이란 무엇인가?

Enqueue(인큐)는 오라클이 공유 리소스에 대한 동시 접근을 직렬화하기 위해 사용하는 정교한 잠금(Lock) 메커니즘입니다. 래치(Latch)와 달리 FIFO(선입선출) 방식의 대기열(Queue)을 관리하므로, 먼저 요청한 세션이 순서대로 리소스를 획득할 수 있습니다.

그중에서도 TX Lock(Transaction Lock)은 트랜잭션의 원자성(Atomicity)과 일관성(Consistency)을 보장하기 위한 핵심 장치입니다.

• 어떤 세션이 첫 번째 DML(INSERT, UPDATE, DELETE, MERGE, SELECT ... FOR UPDATE)을 수행하여 트랜잭션을 시작하면, 오라클은 해당 트랜잭션에 고유한 XID(Transaction ID: USN + Slot + Sq#)를 부여하고 동시에 TX Lock을 배타적 모드(Exclusive Mode, Mode 6)로 획득합니다.
• 이 TX Lock은 트랜잭션이 COMMIT 또는 ROLLBACK을 수행하여 완전히 종료될 때까지 유지됩니다.

다른 세션이 현재 진행 중인 트랜잭션과 충돌하는 작업을 시도할 때, 요청하는 작업의 성격에 따라 Mode 6(Exclusive) 또는 Mode 4(Share)로 TX Lock을 요청하며 대기열에 들어가게 됩니다. 이때 왜 대기하느냐에 따라 아래 4가지 대기 이벤트로 나뉘게 됩니다.

2. 내부 메커니즘: TX Lock은 오라클 내부에서 어떻게 동작하는가?

오라클은 타 DBMS와 달리 메모리 상에 별도의 거대한 'Lock List'를 두고 로우 레벨 락을 관리하지 않습니다. 대신 데이터 블록 자체에 락 정보를 기록하는 독특한 아키텍처를 가집니다.

2.1. 로우 레벨 락과 ITL(Interested Transaction List)의 연결 고리

1. Row Header의 Lock Byte: 테이블의 각 로우 헤더에는 현재 이 로우를 변경 중인 트랜잭션이 블록 헤더의 몇 번 ITL 슬롯을 사용하고 있는지를 가리키는 1바이트 크기의 포인터(Lock Byte)가 존재합니다.
2. Block Header의 ITL: 데이터 블록 헤더의 ITL 슬롯에는 해당 블록을 변경 중인 트랜잭션의 XID(Undo 세그먼트 번호 + 트랜잭션 테이블 슬롯 번호 + 시퀀스 번호)와 UBA(Undo Block Address), 그리고 커밋 상태 플래그가 기록됩니다.
3. Undo Segment Header의 Transaction Table: ITL에 적힌 XID를 따라가면 UNDO 세그먼트 헤더의 트랜잭션 테이블 슬롯이 나오며, 이곳에 실제 트랜잭션의 최종 상태(Active 또는 Committed)가 기록되어 있습니다.

2.2. TX Lock 대기 발생 과정

세션 B가 특정 블록의 로우를 수정하려고 도달했을 때의 내부 처리 과정입니다.

1. 수정하려는 로우의 Lock Byte를 확인합니다. 만약 0이 아니라면 누군가 변경 중이라는 뜻이므로, 가리키는 블록 헤더의 ITL 슬롯을 확인합니다.
2. ITL 슬롯에 기록된 XID를 읽고, 해당 UNDO 세그먼트 헤더의 트랜잭션 테이블을 찾아가 트랜잭션(세션 A)이 아직 Active(진행 중) 상태인지 확인합니다.
3. 만약 세션 A가 아직 커밋하지 않은 Active 상태라면, 세션 B는 세션 A가 쥐고 있는 XID를 리소스 식별자로 삼아 TX Enqueue 대기열에 줄을 섭니다.
4. 이때 무엇 때문에 막혔는지에 따라 요청하는 Lock Mode(Mode 6 vs Mode 4)와 대기 이벤트 명이 결정됩니다.

3. 4가지 주요 TX 대기 이벤트 상세 원인 분석

실무에서 발생하는 4가지 TX Lock 대기 이벤트의 정확한 발생 원리와 요청 모드(Mode)의 차이를 살펴보겠습니다.

3.1. enq: TX - row lock contention (Mode 6 또는 Mode 4)

가장 흔하고 직관적인 락 경합입니다. 앞서 세션 A가 특정 로우를 수정(UPDATE/DELETE)하고 아직 커밋하지 않은 상태에서, 세션 B가 정확히 동일한 로우를 수정하려고 할 때 발생합니다. 이때 세션 B는 Mode 6(Exclusive)으로 TX Lock을 요청하며 대기합니다.

💡 주의: PK/Unique Index 중복 INSERT 시의 Row Lock Contention (Mode 4) 물리적으로 존재하는 같은 로우를 수정할 때만 이 이벤트가 발생하는 것은 아닙니다. 테이블에 PK나 Unique 인덱스가 걸려 있을 때, 세션 A가 'A001'이라는 키 값을 INSERT(미커밋)한 상태에서 세션 B가 똑같은 'A001' 값을 INSERT하면, 세션 B는 *Mode 4(Share)*로 enq: TX - row lock contention 대기에 빠집니다. 세션 A가 커밋하면 세션 B는 ORA-00001(Unique constraint violated) 에러를 맞고, 롤백하면 세션 B의 INSERT가 정상 수행됩니다.

3.2. enq: TX - allocate ITL entry (Mode 4,Share)

서로 전혀 다른 로우를 수정하거나 INSERT하는데도 락 대기가 걸리는 대표적인 케이스입니다.

• 데이터 블록의 데이터를 변경하려면 반드시 블록 헤더의 ITL(Interested Transaction List) 슬롯을 하나씩 할당받아야 합니다.
• 기본 할당된 ITL 슬롯(INITRANS, 기본값 2)이 모두 사용 중이면, 오라클은 블록 내의 여유 공간(PCTFREE 영역)을 사용해 최대 MAXTRANS(10g부터 255 고정)까지 ITL 슬롯을 동적으로 늘립니다(슬롯당 24바이트 소요).
• 하지만 블록 내에 여유 공간이 전혀 없어서 더 이상 새로운 ITL 슬롯을 생성할 수 없을 때, 해당 블록의 다른 로우를 변경하려는 새로운 트랜잭션은 기존에 ITL 슬롯을 점유하고 있는 트랜잭션 중 하나가 끝나기를 기다리며 Mode 4(Share)로 대기하게 됩니다.

3.3. enq: TX - index contention (Mode 4, Share)

대규모 동시 트랜잭션이 몰리는 OLTP 환경에서 인덱스 구조가 변경될 때 발생하는 내부 경합입니다.

• 세션들이 테이블에 데이터를 INSERT하면 인덱스 리프 블록(Leaf Block)에도 정렬된 순서대로 키 값을 넣어야 합니다.
• 만약 특정 인덱스 리프 블록이 가득 차서 더 이상 공간이 없으면, 오라클은 새로운 블록을 할당받아 기존 데이터를 나누어 담는 Index Block Split(인덱스 블록 분할, 50-50 또는 90-10 Split) 작업을 수행합니다.
• 세션 A가 인덱스 블록을 분할하는 도중(자율 트랜잭션 형태로 수행됨), 세션 B가 분할 중인 바로 그 인덱스 블록에 새로운 키 값을 INSERT하려고 진입하면, 블록 분할이 완료될 때까지 Mode 4(Share)로 enq: TX - index contention 대기를 겪게 됩니다. 특히 시퀀스 기반의 우편향(Right-Hand) 인덱스에서 극심하게 발생합니다.

3.4. enq: TX - bitmap index contention (Mode 4, Share) (특수 케이스)

이름 그대로는 별도 이벤트가 아닌, enq: TX - row lock contention (Mode 4)로 나타나지만 원인이 비트맵 인덱스(Bitmap Index)에 있는 치명적인 경합입니다.

• B-Tree 인덱스는 인덱스 엔트리 하나가 하나의 ROWID만 가리키지만, Bitmap Index는 하나의 인덱스 엔트리가 특정 키 값(예: 성별 = 'M')을 가진 수천~수만 개의 ROWID 범위(Start ROWID ~ End ROWID)를 압축된 비트맵(0과 1) 형태로 묶어서 관리합니다.
• 따라서 세션 A가 단 1건의 로우를 수정하거나 INSERT하더라도, 해당 로우가 포함된 비트맵 인덱스 청크(Chunk) 전체에 락이 걸립니다.
• 이때 세션 B가 전혀 다른 로우를 수정하더라도, 그 로우가 같은 비트맵 범위 내에 속해 있다면 Mode 4(Share)로 TX Lock 대기에 빠집니다. OLTP 환경에서 비트맵 인덱스를 절대 사용하면 안 되는 결정적인 이유입니다.

4. 트러블슈팅 및 해결 방안

실무에서 TX Lock 대기가 발생했을 때, 어떤 유형(Mode 6 vs Mode 4)인지 즉시 판별하고 홀더(Blocker)와 대상 오브젝트를 찾아 해결하는 단계별 가이드입니다.

4.1. 1단계: 실시간 TX Lock 대기 세션 및 Lock Mode(6 vs 4) 진단 쿼리

가장 먼저 현재 대기 중인 세션이 요청하는 락 모드(REQUEST)가 6(Exclusive)인지 4(Share)인지 확인하여 원인을 좁혀야 합니다.

-- 실시간 TX Lock Blocker(홀더)와 Waiter(대기자) 및 요청 모드 확인 쿼리
SELECT DECODE(l.REQUEST, 0, '🔴 Holder: ', '⚪ Waiter: ') || l.SID AS SESS_ROLE,
       s.SERIAL#,
       s.USERNAME,
       s.PROGRAM,
       s.SQL_ID,
       s.EVENT,
       l.ID1 AS USN_SLOT,
       l.ID2 AS SEQ,
       l.LMODE AS HELD_MODE,     -- 6: Exclusive
       l.REQUEST AS REQ_MODE,    -- 6: 일반 Row Lock, 4: PK중복/ITL/Index/Bitmap 등
       l.CTIME AS WAIT_TIME_SEC,
       s.ROW_WAIT_OBJ# AS TARGET_OBJ_ID,
       s.ROW_WAIT_FILE# AS FILE_ID,
       s.ROW_WAIT_BLOCK# AS BLOCK_ID,
       s.ROW_WAIT_ROW# AS ROW_ID
  FROM V$LOCK l,
       V$SESSION s
 WHERE l.SID = s.SID
   AND l.TYPE = 'TX'
   AND (l.ID1, l.ID2) IN (SELECT ID1, ID2 FROM V$LOCK WHERE TYPE = 'TX' AND REQUEST > 0)
 ORDER BY l.ID1, l.REQUEST;

위 쿼리에서 추출된 TARGET_OBJ_ID(ROW_WAIT_OBJ#)를 아래 쿼리에 넣으면 정확히 어떤 테이블이나 인덱스에서 경합이 났는지, 심지어 어떤 로우(ROWID)가 문제인지까지 추적할 수 있습니다. (단, Mode 4 대기의 경우 ROW_WAIT_OBJ#가 -1로 나오는 경우가 많으므로 이때는 ASH의 CURRENT_OBJ#를 함께 확인해야 합니다.)

-- 경합 대상 오브젝트 및 실제 충돌 ROWID 추출 (Mode 6 Row Lock 시 유효)
SELECT o.OWNER,
       o.OBJECT_NAME,
       o.OBJECT_TYPE,
       DBMS_ROWID.ROWID_CREATE(1, o.DATA_OBJECT_ID, &FILE_ID, &BLOCK_ID, &ROW_ID) AS TARGET_ROWID
  FROM DBA_OBJECTS o
 WHERE o.OBJECT_ID = &TARGET_OBJ_ID;

4.2. 2단계: 4가지 유형별 맞춤 해결 전략

① enq: TX - row lock contention 해결 방안

• Mode 6 (일반 로우 충돌): 전형적인 애플리케이션 설계 및 트랜잭션 관리 문제입니다.
  1. 트랜잭션 내에서 외부 API 호출(결제, 메일 발송 등)이나 무거운 조회 작업을 수행하느라 COMMIT이 지연되는 구간을 찾아 트랜잭션 단위를 짧게 쪼개야 합니다.
  2. 채번 테이블(UPDATE TB_SEQ SET SEQ = SEQ + 1)을 사용하고 있다면 즉시 오라클 시퀀스(Sequence)로 전환해야 합니다.
  3. 데드락(ORA-00060)이나 장기 락을 유발하는 미커밋 세션은 현업 확인 후 ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;로 강제 종료합니다.
• Mode 4 (PK 중복 또는 FK 인덱스 누락):
  • 외래키(Foreign Key) 인덱스 생성 점검: 부모 테이블의 PK를 수정하거나 삭제할 때 자식 테이블의 FK 컬럼에 인덱스가 없으면, 자식 테이블 전체에 Table Lock이나 불필요한 경합이 발생합니다. 모든 FK 컬럼에는 반드시 인덱스를 생성해야 합니다.

② enq: TX - allocate ITL entry 해결 방안

특정 블록에 동시 DML이 집중되는데 블록 여유 공간이 부족해 ITL 슬롯을 못 늘리는 상황이므로, 초기 보장 ITL 슬롯 개수(INITRANS)와 블록 여유 공간 비율(PCTFREE)을 상향 조정해야 합니다.

-- ITL 경합이 발생한 세그먼트 확인 (V$SEGSTAT 활용)
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, VALUE AS ITL_WAITS
  FROM V$SEGSTAT
 WHERE STATISTIC_NAME = 'ITL waits'
   AND VALUE > 0
 ORDER BY VALUE DESC;

-- 해결: 테이블 및 인덱스의 INITRANS와 PCTFREE 상향 변경
ALTER TABLE TB_ORDER_DTL INITRANS 10 PCTFREE 20;
ALTER INDEX IDX_ORDER_DTL_01 INITRANS 20;

💡 DBA 실무 주의사항: 단순히 ALTER TABLE ... INITRANS 10; 명령만 수행하면, 이후에 새로 할당되는 블록부터만 변경된 설정이 적용됩니다. 이미 데이터가 들어있는 기존 블록들의 ITL 슬롯까지 즉시 늘려주려면 반드시 테이블 재구성(ALTER TABLE ... MOVE)과 인덱스 리빌드(ALTER INDEX ... REBUILD) 작업을 병행해야 효과가 나타납니다.

③ enq: TX - index contention 해결 방안

인덱스 블록 분할(Block Split)이 집중되는 것을 막고, 분할 작업 자체가 빠르게 끝나도록 유도해야 합니다.

1. Global Hash Partitioned Index 적용 (가장 확실한 해결책): 순차 증가하는 시퀀스 기반 인덱스의 가장 오른쪽 리프 블록(Rightmost Leaf Block)에 집중되는 90-10 Split을 막기 위해, 인덱스를 해시 파티션(예: 16개 또는 32개)으로 쪼개어 INSERT 부하를 물리적으로 분산시킵니다.
2. 시퀀스 CACHE 사이즈 대폭 상향: 인덱스 키 값으로 사용되는 시퀀스의 CACHE 크기가 기본값(20)으로 되어 있다면, 동시 INSERT 시 enq: SQ - contention과 함께 인덱스 경합이 심화됩니다. 고부하 환경에서는 시퀀스 캐시를 최소 1,000 ~ 10,000 이상으로 넉넉히 부여해야 합니다.
3. 불필요한 인덱스 삭제 및 리빌드: 거의 사용되지 않으면서 DML 시 Split 부하만 주는 과도한 인덱스를 정리합니다. 또한 대량 DELETE 이후 빈 공간(Deleted Leaf Row)이 방치된 왜곡된 인덱스는 ALTER INDEX ... REBUILD ONLINE 또는 COALESCE를 통해 정리해 줍니다.

④ enq: TX - bitmap index contention 해결 방안

• OLTP 환경에서 Bitmap Index 즉시 제거: 동시 DML이 발생하는 온라인 트랜잭션 테이블에 생성된 비트맵 인덱스는 아키텍처상의 설계 오류입니다. 즉시 일반 B-Tree Index로 전환해야 합니다.
• DW/배치 환경에서의 대량 적재 수칙: 데이터 웨어하우스(DW) 환경에서 불가피하게 비트맵 인덱스를 사용한다면, 대량 데이터를 적재(ETL)할 때는 반드시 비트맵 인덱스를 UNUSABLE 상태로 변경(또는 Drop)한 뒤, 적재가 완료된 후 일괄 REBUILD하는 방식으로 파이프라인을 구성해야 합니다.

5. 요약

1. TX Lock(Transaction Enqueue)은 트랜잭션의 무결성을 지키기 위해 첫 DML 시 획득하여 COMMIT/ROLLBACK 시까지 유지하는 락이며, 대기 원인에 따라 요청 모드(Mode 6 vs Mode 4)와 세부 대기 이벤트가 달라진다.
2. enq: TX - row lock contention은 동일한 로우를 동시에 수정하려 할 때(Mode 6), 또는 PK/Unique 키 값 중복 INSERT 시(Mode 4) 발생하며, 트랜잭션 길이를 단축하고 채번 테이블을 시퀀스로 교체하여 해결한다.
3. enq: TX - allocate ITL entry (Mode 4)는 블록 내 여유 공간 부족으로 트랜잭션 슬롯(ITL)을 할당받지 못해 발생하며, INITRANS와 PCTFREE를 높인 후 세그먼트를 MOVE/REBUILD하여 해결한다.
4. enq: TX - index contention (Mode 4)은 동시 INSERT 중 인덱스 리프 블록 분할(Block Split) 과정에서 발생하며, 우편향 인덱스를 Global Hash Partitioned Index로 분산 설계하여 해결한다.
5. Bitmap Index 경합 (Mode 4)은 하나의 비트맵 엔트리가 수천 개의 로우를 묶어 관리하는 구조적 특성 때문에 발생하므로, 동시 DML이 발생하는 OLTP 환경에서는 절대 사용해서는 안 된다.

대규모 동시 접속이 발생하는 OLTP 시스템이나 고부하 배치 작업 중, 디스크 I/O에는 전혀 문제가 없는데도 CPU 사용률이 치솟고 전체 시스템 응답 속도가 급격히 느려지는 현상을 마주할 때가 있습니다. 이때 AWR이나 ASH 리포트를 분석해 보면 어김없이 최상단에 등장하는 단골 대기 이벤트가 바로 latch: cache buffers chains와 buffer busy waits입니다.

많은 개발자와 초급 DBA가 이 두 가지 대기 이벤트를 단순히 "특정 테이블이나 블록에 접근이 몰려서 발생하는 현상"으로 묶어서 이해하곤 합니다. 하지만 데이터 아키텍트(DA)와 전문 DBA 관점에서 두 이벤트는 버퍼 캐시(Database Buffer Cache)를 탐색하고 블록에 접근하는 단계(Phase) 자체가 완전히 다릅니다.

이 글에서는 오라클 버퍼 캐시의 해시 체인(Hash Chain) 아키텍처와 버퍼 핀(Buffer Pinning) 메커니즘을 통해 두 대기 이벤트의 정확한 발생 위치를 파헤치고, 실무에서 즉시 활용 가능한 핫 블록(Hot Block) 추적 쿼리와 근본적인 해결 방안을 정리합니다.

1. 개요: CBC Latch와 Buffer Busy Waits란 무엇인가?

오라클은 디스크 I/O를 최소화하기 위해 모든 데이터 블록의 읽기(SELECT)와 쓰기(DML) 작업을 SGA 내부의 데이터 버퍼 캐시(Database Buffer Cache)를 통해 수행합니다. 수많은 서버 프로세스가 동시에 공유 메모리인 버퍼 캐시의 특정 블록을 찾아 읽고 수정하기 때문에, 메모리 구조가 깨지는 것을 막기 위한 정교한 직렬화(Serialization) 장치가 필요합니다.

이 직렬화 과정에서 발생하는 대표적인 두 가지 병목 지점이 바로 CBC Latch와 Buffer Busy Waits입니다.

• latch: cache buffers chains (CBC Latch): 원하는 데이터 블록이 버퍼 캐시 어디에 있는지 찾기 위해 해시 체인(Hash Chain)의 주소록(Buffer Header)을 탐색하는 과정에서 발생하는 가벼운 메모리 잠금(Latch) 경합입니다. 즉, "블록을 찾아가는 길목"에서 발생하는 병목입니다.
• buffer busy waits (또는 read by other session): CBC Latch를 획득하여 원하는 버퍼 헤더를 찾는 데는 성공했으나, 실제 데이터 블록(Buffer Block) 자체를 누군가 배타적으로 수정 중이거나 디스크에서 읽어오는 중이어서 블록 내부로 진입하지 못하고 대기하는 상태입니다. 즉, "목적지(블록) 문 앞"에서 발생하는 병목입니다.

2. 내부 메커니즘: 버퍼 캐시 탐색과 블록 접근의 단계별 원리

두 대기 이벤트의 차이를 명확히 이해하려면, 오라클이 버퍼 캐시 내부에서 어떻게 블록을 관리하고 탐색하는지 그 아키텍처를 들여다봐야 합니다.

2.1. 버퍼 캐시의 핵심 구조: Hash Bucket과 Hash Chain

오라클은 수백만 개에 달하는 버퍼 블록을 순차적으로 검색하지 않고, 해시 알고리즘(Hash Algorithm)을 사용하여 초고속으로 탐색합니다.

1. Hash Bucket (해시 버킷): 데이터 블록의 물리적 주소인 DBA(Data Block Address: 파일 번호 + 블록 번호)를 해시 함수에 통과시키면 특정 해시 버킷에 매핑됩니다.
2. Hash Chain (해시 체인): 각 해시 버킷에는 이중 연결 리스트(Doubly Linked List) 형태로 여러 개의 버퍼 헤더(Buffer Header)가 줄줄이 연결되어 있습니다.
3. Buffer Header (버퍼 헤더): 실제 데이터가 담긴 버퍼 블록의 메타데이터(DBA, 블록 상태, 현재 사용자 리스트, Lock 모드 등)를 가지고 있으며, 실제 메모리 상의 데이터 블록(Buffer)을 가리키는 포인터 역할을 합니다.
4. CBC Latch: 바로 이 해시 체인(여러 개의 해시 버킷 그룹)의 연결 구조가 탐색 도중 변경되지 않도록 보호하는 수문장이 Cache Buffers Chains Latch입니다.

2.2. 서버 프로세스가 블록을 읽는 5단계 과정 (경합 발생 포인트)

세션 A가 특정 로우(Row)를 조회하거나 수정하기 위해 블록을 찾아가는 과정을 순서대로 따라가 보겠습니다.

1. DBA 해싱: 읽고자 하는 블록의 DBA(예: File 4, Block 1052)를 해시 함수에 넣어 찾아갈 해시 버킷을 결정합니다.
2. CBC Latch 획득 시도 [🔥 경합 포인트 1]: 해당 해시 버킷에 연결된 해시 체인을 탐색하기 위해 그 체인을 담당하는 CBC Latch를 요청합니다.
   • 과거(8i 이전)에는 단순 조회 시에도 배타적(Exclusive) 모드로 래치를 잡았으나, 최신 오라클에서는 단순 읽기 시 Shared 모드(S)로 래치를 공유할 수 있어 경합이 많이 줄었습니다.
   • 하지만 체인 구조를 변경(블록을 새로 적재하거나 교체)하거나 특정 작업을 할 때는 여전히 배타적 모드가 필요하며, 극단적으로 많은 세션이 동시에 같은 체인을 스캔하면 스핀(Spin) 후 슬립(Sleep)에 빠지며 latch: cache buffers chains 대기가 발생합니다.
3. Hash Chain 스캔 및 Buffer Pin 시도 [🔥 경합 포인트 2]: 래치를 획득한 후 해시 체인을 따라가며 원하는 버퍼 헤더(File 4, Block 1052)를 찾습니다. 헤더를 찾았다면 이제 실제 데이터 블록의 내용이 내가 읽는 도중 변하지 않도록 버퍼 헤더에 Buffer Pin (또는 Buffer Lock)을 걸어야 합니다.
   • 읽기(SELECT) 작업: Shared 모드(S)로 Pin을 요청합니다.
   • 쓰기(DML) 작업: Exclusive 모드(X)로 Pin을 요청합니다.
   • 만약 다른 세션 B가 이미 해당 블록을 수정하기 위해 Exclusive 모드로 Pin을 잡고 작업 중이라면, 세션 A는 Pin을 획득할 수 없습니다. 이때 세션 A는 보유하고 있던 CBC Latch를 즉시 해제(Release)한 뒤, 버퍼 헤더의 대기자 명단(Waiter List)에 이름을 올리고 대기 상태에 들어갑니다. 이것이 바로 buffer busy waits입니다.
4. 블록 액세스 및 CBC Latch 해제: 정상적으로 Buffer Pin을 획득했다면, 더 이상 해시 체인을 보호할 필요가 없으므로 즉시 CBC Latch를 해제합니다. (Pin을 잡는 순간에만 잠깐 Latch를 쥐고 있습니다.) 이후 안전하게 실제 데이터 블록을 읽거나 수정합니다.
5. Buffer Unpin: 블록 작업이 끝나면 다시 아주 잠깐 CBC Latch를 획득하여 버퍼 헤더에서 자신의 Pin을 제거(Unpin)하고 모든 작업을 마칩니다.

💡 핵심 아키텍처 인사이트 (Read by other session): 오라클 10g 이전에는 다른 세션이 디스크에서 버퍼 캐시로 블록을 읽어 들이는(Physical I/O) 도중에 내가 같은 블록을 요청하며 기다리는 현상도 모두 buffer busy waits로 분류했습니다. 10g부터는 이 케이스가 **read by other session**이라는 별도의 대기 이벤트로 분리되었습니다. 따라서 최신 버전에서 발생하는 순수한 buffer busy waits는 대부분 메모리 상에 이미 존재하는 블록에 대한 동시 읽기/쓰기(DML) 충돌을 의미합니다.

3. 주요 원인 분석: 실무에서 왜 발생하는가?

내부 메커니즘을 이해했다면, 실무 환경에서 어떤 쿼리와 설계가 이 두 가지 경합을 유발하는지 명확히 짚어낼 수 있습니다.

3.1. latch: cache buffers chains 주요 발생 원인

1. 비효율적인 SQL로 인한 과도한 논리적 읽기 (Logical I/O 폭증): 가장 흔한 원인(90% 이상)입니다. 잘못된 인덱스 스캔이나 비효율적인 Nested Loops 조인으로 인해, 작은 테이블이나 특정 인덱스 블록을 수백만 번 반복해서 방문(Logical Read)하는 쿼리가 여러 세션에서 동시에 돌아갈 때 발생합니다.
2. 인덱스 루트/브랜치 블록 핫스팟 (Hot Block): 조건절이 자주 들어오는 핵심 인덱스의 Root 블록이나 Branch 블록은 모든 인덱스 탐색 시 반드시 첫 번째로 거쳐야 하는 관문입니다. 수천 개의 동시 세션이 특정 인덱스를 태울 때 루트 블록이 속한 해시 체인에 엄청난 CBC Latch 요청이 집중됩니다.
3. 긴 해시 체인 (Long Hash Chain) 또는 과도한 CR 블록 생성: 특정 블록에 DML이 빈번하게 발생하는 동시에 조회가 몰리면, 오라클은 읽기 일관성을 위해 버퍼 캐시에 수많은 복사본(CR 블록)을 생성합니다. 이 CR 블록들은 모두 동일한 DBA를 가지므로 같은 해시 체인에 줄줄이 매달리게 됩니다. 체인이 비정상적으로 길어지면 체인을 스캔하는 시간 자체가 길어져 Latch 보유 시간이 늘어나고 경합이 증폭됩니다.

3.2. buffer busy waits 주요 발생 원인

1. Data Block 경합 - 우편향 인덱스 (Right-Hand Index) INSERT: 주문 번호, 로그 일련번호처럼 순차적으로 증가하는 시퀀스(Sequence)나 날짜(SYSDATE) 컬럼으로 생성된 B-Tree 인덱스에 수백 개의 세션이 동시에 INSERT를 수행하는 경우입니다. 모든 새로운 값들이 인덱스의 가장 오른쪽 끝 리프 블록(Rightmost Leaf Block) 단 하나에만 집중적으로 들어가려다 보니, 해당 블록을 수정(Exclusive Pin)하기 위한 극심한 경합과 90-10 Block Split이 발생합니다.
2. Data Block 경합 - 높은 밀집도(High Row Density) 블록에 대한 동시 DML: 하나의 데이터 블록(예: 8KB) 안에 수백 개의 작은 로우(Row)가 빽빽하게 들어차 있는 상태에서, 여러 세션이 서로 다른 로우를 UPDATE 하더라도 물리적으로 같은 블록에 속해 있다면 동시에 블록 헤더(ITL)와 데이터를 수정하는 순간 짧은 buffer busy waits가 다발합니다. (참고: 로우 레벨 락은 enq: TX - row lock contention이지만, 블록 자체의 메타데이터를 건드리는 찰나의 순간에는 버퍼 Pin 경합이 발생합니다.)
3. Segment Header 경합 - 공간 할당 병목: 과거 수동 세그먼트 관리(MSSM, Freelist 방식) 환경에서 동시 INSERT 시 빈 블록 목록을 관리하는 세그먼트 헤더 블록을 서로 차지하려다 발생했습니다. 최신 ASSM(Automatic Segment Space Management) 환경에서는 비트맵 블록(BMB)으로 분산되어 많이 해소되었으나, 여전히 극단적인 대량 동시 INSERT 시 공간 확장(Extent Allocation) 과정에서 간헐적으로 발생할 수 있습니다.

4. 트러블슈팅 및 해결 방안

문제가 발생했을 때 정확히 어떤 테이블, 어떤 인덱스의 몇 번 블록이 문제인지(Hot Block) 찾아내는 진단 쿼리와 아키텍처적 해결 전략입니다.

4.1. 1단계: CBC Latch 경합의 주범 (Hot Latch & Hot Block) 추출

latch: cache buffers chains 대기가 높을 때는 가장 스립(Sleep)이 많이 발생한 자식 래치(Child Latch)의 주소(ADDR)를 찾은 뒤, X$BH(버퍼 헤더 내부 뷰, SYS 권한 필요)와 조인하여 열기(Touch Count)가 가장 높은 핫 블록의 정체를 밝혀냅니다.

-- 1. 가장 경합이 심한 상위 5개 CBC 자식 래치 주소(ADDR) 확인
SELECT *
  FROM (SELECT ADDR,
               CHILD#,
               GETS,
               MISSES,
               SLEEPS,
               ROUND((GETS - MISSES) / DECODE(GETS, 0, 1, GETS) * 100, 2) AS HIT_RATIO
          FROM V$LATCH_CHILDREN
         WHERE NAME = 'cache buffers chains'
         ORDER BY SLEEPS DESC)
 WHERE ROWNUM <= 5;

-- 2. 위에서 찾은 HLADDR(Latch Address)에 매달린 핫 블록 및 대상 오브젝트 추적 (SYS 계정 수행)
SELECT b.HLADDR,
       b.FILE#,
       b.DBARFIL,
       b.DBABLK,
       b.TCH AS TOUCH_COUNT, -- 블록 접근 횟수 (높을수록 Hot Block)
       o.OWNER,
       o.OBJECT_NAME,
       o.OBJECT_TYPE
  FROM X$BH b,
       DBA_OBJECTS o
 WHERE b.OBJ = o.DATA_OBJECT_ID
   AND b.HLADDR = '000000045A8B1230' -- 1번 쿼리에서 추출한 최상위 ADDR 값 입력
   AND b.TCH > 10
 ORDER BY b.TCH DESC;

4.2. 2단계: Buffer Busy Waits 경합 블록 및 원인(Block Class) 진단

buffer busy waits 발생 시에는 대기 이벤트의 파라미터인 P1(파일 번호), P2(블록 번호), P3(Class ID 또는 Reason Code)를 확인하는 것이 핵심입니다. V$WAITSTAT을 통해 어떤 종류의 블록에서 경합이 나는지 먼저 파악합니다.

-- 1. 블록 유형(Class)별 buffer busy waits 누적 발생 현황 확인
SELECT CLASS,
       COUNT,
       TIME
  FROM V$WAITSTAT
 WHERE COUNT > 0
 ORDER BY TIME DESC;

• data block이 높다면: 테이블이나 인덱스 리프 블록에 대한 동시 DML 경합입니다.
• segment header 또는 1st level bmb가 높다면: 동시 INSERT로 인한 공간 할당 경합입니다.
• undo header 또는 undo block이 높다면: 트랜잭션에 비해 UNDO 세그먼트가 부족하거나 특정 UNDO 블록에 경합이 몰린 것입니다.

실시간(또는 ASH)에서 정확히 어떤 오브젝트가 buffer busy waits를 유발하는지 아래 쿼리로 즉시 뽑아냅니다.

-- 2. 최근 10분간 ASH(Active Session History) 기반 Buffer Busy Waits 유발 오브젝트 및 SQL 추출
SELECT o.OWNER,
       o.OBJECT_NAME,
       o.OBJECT_TYPE,
       h.SQL_ID,
       h.CURRENT_FILE# AS FILE_ID,
       h.CURRENT_BLOCK# AS BLOCK_ID,
       COUNT(*) AS WAIT_COUNT
  FROM V$ACTIVE_SESSION_HISTORY h,
       DBA_OBJECTS o
 WHERE h.CURRENT_OBJ# = o.OBJECT_ID
   AND h.EVENT = 'buffer busy waits'
   AND h.SAMPLE_TIME >= SYSDATE - (10 / 1440) -- 최근 10분
 GROUP BY o.OWNER, o.OBJECT_NAME, o.OBJECT_TYPE, h.SQL_ID, h.CURRENT_FILE#, h.CURRENT_BLOCK#
 ORDER BY WAIT_COUNT DESC;

4.3. 3단계: 근본적인 해결 전략 및 아키텍처 튜닝

원인이 되는 SQL과 오브젝트를 특정했다면 상황에 맞는 처방을 내립니다.

① CBC Latch 해결책: SQL 튜닝을 통한 논리적 I/O(Buffer Gets) 절감

CBC Latch 경합을 파라미터 조정 등으로 해결하려는 시도는 무의미합니다. 유일하고 가장 강력한 해결책은 SQL 튜닝입니다.

• 추출된 악성 SQL의 실행 계획을 분석하여, 수백만 건의 버퍼 블록을 반복 스캔하는 Nested Loops 조인을 Hash Join으로 변경합니다. (Hash Join은 PGA 메모리에서 해시 테이블을 만들어 조인하므로 SGA 버퍼 캐시 래치 경합을 획기적으로 줄여줍니다.)
• 변별력이 떨어지는 인덱스를 경유하여 테이블을 랜덤 액세스하는 대신, 적절한 결합 인덱스를 구성하거나 차라리 파티션 프루닝(Partition Pruning)을 동반한 Full Scan으로 유도하여 블록 방문 횟수(Touch Count) 자체를 줄입니다.

② 우편향 인덱스(Right-Hand Index) INSERT 경합 해결책

시퀀스 기반의 PK 인덱스에 동시 INSERT가 몰려 buffer busy waits가 발생한다면 물리적 설계를 변경해야 합니다.

Global Hash Partitioned Index 구성 (가장 권장): 인덱스를 해시 파티셔닝(예: 8개, 16개, 32개 파티션)으로 쪼개어 구성합니다. 이렇게 하면 연속된 시퀀스 값이 들어오더라도 해시 함수에 의해 서로 다른 파티션의 리프 블록으로 골고루 분산되어 저장되므로 경합이 완벽히 사라집니다.

-- 시퀀스 기반 PK 인덱스를 16개 해시 파티션으로 분산 생성하는 예시
CREATE UNIQUE INDEX IDX_ORD_PK ON TB_ORDER (ORDER_NO)
GLOBAL PARTITION BY HASH (ORDER_NO)
PARTITIONS 16;

• Reverse Key Index 고려 (주의 필요): 바이트 순서를 뒤집어 저장(1234 -> 4321)하여 리프 블록을 분산시키는 기법입니다. 단, 값의 순서가 깨지므로 BETWEEN이나 >, < 같은 범위 스캔(Index Range Scan)이 불가능해진다는 치명적인 단점이 있어 반드시 = 조건으로만 조회되는 인덱스에 한해 제한적으로 사용해야 합니다.
• Oracle 18c 이상: Scalable Sequences 활용: 시퀀스 생성 시 SCALE EXTEND 옵션을 부여하면, 시퀀스 앞자리에 인스턴스 ID와 세션 ID 조합의 오프셋이 자동으로 붙어 생성되므로 인덱스 리프 블록이 자연스럽게 분산됩니다.

③ 테이블 데이터 블록의 높은 밀집도(Hot Block) 완화

특정 소수 블록에 자주 업데이트되는 로우들이 뭉쳐 있어서 경합이 발생한다면, 블록 당 저장되는 로우의 수를 줄여 물리적으로 여러 블록에 흩어놓아야 합니다.

• PCTFREE 상향 조정 또는 MINIMIZE RECORDS_PER_BLOCK 활용: 테이블의 PCTFREE 값을 높게(예: 40~50) 설정하여 재구성(Move)하면, 하나의 블록에 들어가는 로우 수가 줄어들어 동시 DML 시 같은 블록에서 부딪힐 확률이 낮아집니다.

5. 요약

1. CBC Latch (latch: cache buffers chains)는 버퍼 캐시에서 원하는 블록의 위치를 찾기 위해 해시 체인(주소록)을 탐색하는 과정에서 발생하는 메모리 잠금 경합이며, 주원인은 비효율적인 SQL로 인한 과도한 논리적 읽기(Logical I/O)다.
2. Buffer Busy Waits는 해시 체인에서 버퍼 헤더를 찾은 후, 실제 데이터 블록 내부로 진입(Buffer Pin 획득)하려 할 때 다른 세션이 이미 블록을 수정 중이어서 대기하는 현상이며, 주원인은 우편향 인덱스에 대한 동시 INSERT나 특정 블록에 대한 동시 DML 충돌이다.
3. CBC Latch 경합을 잡기 위해서는 X$BH의 Touch Count가 높은 핫 블록과 SQL을 찾아 Nested Loops를 Hash Join으로 바꾸는 등 블록 스캔 횟수 자체를 줄이는 SQL 튜닝이 필수적이다.
4. Buffer Busy Waits 경합을 해소하기 위해서는 V$WAITSTAT과 ASH를 통해 대상 세그먼트를 특정하고, 시퀀스 인덱스의 경우 Global Hash Partitioned Index로 분산 설계하여 물리적인 블록 집중 현상을 깨뜨려야 한다.

데이터베이스 아키텍트(DA)나 DBA로서 SQL 튜닝을 수행하고 장애를 트러블슈팅할 때 가장 든든한 무기가 되는 것은 오라클 서버의 내부 아키텍처(Architecture)에 대한 깊은 이해입니다. 특정 쿼리가 왜 느린지, 왜 특정 대기 이벤트(Wait Event)가 발생하는지 정확히 진단하려면 데이터가 디스크에서 메모리로 올라와 사용자에게 전달되고 다시 디스크에 기록되기까지의 전 과정을 머릿속에 그릴 수 있어야 합니다.

이번 포스팅에서는 오라클 인스턴스(Instance)를 구성하는 양대 산맥인 메모리 구조(SGA, PGA)와 백그라운드 프로세스(Background Process)의 내부 메커니즘을 파헤치고, 실무에서 마주치는 주요 경합 원인과 모니터링 및 튜닝 가이드를 정리합니다.

1. 개요: 오라클 서버 아키텍처의 정의와 배경

흔히 '오라클 데이터베이스'라고 부르는 시스템은 엄밀히 말해 인스턴스(Instance)와 데이터베이스(Database) 두 가지 영역의 결합체입니다.

• 인스턴스 (Instance): 물리적인 디스크 파일에 접근하여 데이터를 읽고 쓰기 위해 서버 메모리에 할당된 공유 메모리 영역(SGA)과, 이를 관리하고 작업을 수행하는 백그라운드 프로세스(Background Process)들의 집합입니다. 인스턴스는 메모리에만 존재하므로 서버가 내려가면 사라지는 휘발성 구조입니다.
• 데이터베이스 (Database): 디스크(Storage) 상에 물리적으로 존재하는 파일들의 집합입니다. 실제 데이터가 저장된 Datafile, 변경 이력을 기록하는 Redo Log File, 데이터베이스의 물리적 구조와 동기화(SCN) 정보를 담고 있는 Control File 등이 여기에 속합니다.

사용자 애플리케이션이 DB에 접속하면 서버 프로세스(Server Process)가 생성되며, 이 서버 프로세스가 고유 메모리 영역인 PGA를 할당받아 인스턴스(SGA) 및 디스크 파일과 상호작용하며 쿼리를 처리합니다.

2. 내부 메커니즘: 메모리와 프로세스는 어떻게 상호작용하는가?

오라클 내부에서 하나의 SQL(SELECT 및 UPDATE)이 수행될 때 각 컴포넌트가 어떻게 맞물려 돌아가는지 핵심 구조별로 살펴보겠습니다.

2.1. 공유 메모리 영역: SGA (System Global Area)

SGA는 모든 서버 프로세스가 공유하는 메모리 영역으로, 동시성 제어(Latch/Lock)가 가장 치열하게 일어나는 곳입니다.

1. Database Buffer Cache (데이터 버퍼 캐시): 디스크의 데이터 파일로부터 읽어 들인 데이터 블록의 복사본을 보관하는 영역입니다. 오라클은 디스크 I/O를 최소화하기 위해 모든 블록 읽기/쓰기 작업을 버퍼 캐시를 거쳐 수행합니다. 내부적으로 해시 체인(Hash Chain) 구조로 관리되며, 체인을 탐색할 때 Cache Buffers Chains (CBC) Latch를 획득해야 합니다.
   • Pinned Buffer: 현재 다른 프로세스가 사용(읽기/쓰기) 중인 버퍼입니다.
   • Dirty Buffer: 메모리 상에서 데이터가 변경(DML)되었으나, 아직 디스크의 데이터 파일에 기록되지 않아 디스크 블록과 불일치하는 버퍼입니다.
   • Free/Clean Buffer: 비어있거나 디스크와 동기화가 완료되어 언제든 새로운 블록으로 덮어쓸 수 있는 버퍼입니다.
2. Shared Pool (공유 풀): SQL 문장 자체의 텍스트, 파싱된 트리, 실행 계획(Execution Plan)을 저장하는 Library Cache와 데이터 딕셔너리(테이블, 컬럼, 권한 정보 등) 메타데이터를 캐싱하는 Data Dictionary Cache(Row Cache)로 구성됩니다. SQL 수행 시 하드 파싱(Hard Parsing)을 피하고 실행 계획을 재사용(Soft Parsing)하기 위한 핵심 공간입니다.
3. Redo Log Buffer (리두 로그 버퍼): 데이터베이스에서 발생하는 모든 변경 사항(DML, DDL 등)을 순서대로 기록하는 순환형(Circular) 버퍼입니다. 장애 복구(Instance Recovery)를 위한 핵심 장치로, 버퍼 캐시의 블록이 변경되기 전(또는 변경과 동시에) 리두 레코드가 이곳에 먼저 기록됩니다.

2.2. 전용 메모리 영역: PGA (Program Global Area)

PGA는 각 서버 프로세스마다 독립적으로 할당되는 비공유(Private) 메모리 영역입니다. 다른 프로세스와 공유하지 않으므로 래치(Latch) 경합이 발생하지 않아 매우 빠른 접근이 가능합니다.

• SQL Work Area: ORDER BY, GROUP BY 등을 위한 정렬 공간(Sort Area)과 해시 조인(Hash Join)을 수행하기 위한 Hash Area가 여기에 속합니다. 만약 작업해야 할 데이터량이 할당된 PGA 크기를 초과하면, 디스크의 Temporary Tablespace를 사용하게 되어(Temp Spill / Multipass Execution) 성능이 급격히 저하됩니다.
• Session Memory & Private SQL Area: 세션 변수, 로그인 정보 및 바인드 변수 값, 커서(Cursor)의 실행 상태 정보를 보관합니다.

2.3. 핵심 백그라운드 프로세스 (Background Processes)

메모리와 디스크 사이의 동기화 및 인스턴스 유지를 담당하는 일꾼들입니다.

1. DBWn (Database Writer): 버퍼 캐시의 Dirty Buffer들을 모아 디스크의 데이터 파일에 비동기적으로 내려쓰는(Write) 역할을 합니다. 주의할 점은 커밋(Commit) 시점에 DBWn이 작동하는 것이 아니라, 가용한 Free Buffer가 부족하거나 체크포인트(Checkpoint)가 발생할 때 작동한다는 것입니다.
2. LGWR (Log Writer): Redo Log Buffer의 내용을 디스크의 Online Redo Log File에 내려씁니다. 사용자가 COMMIT을 날리면 LGWR이 즉시 작동하여 해당 트랜잭션의 리두 레코드를 디스크에 기록하고 완료 신호를 보냅니다(Write-Ahead Logging 및 Fast Commit 메커니즘).
3. CKPT (Checkpoint): 특정 시점의 SCN(System Change Number)을 기준으로 버퍼 캐시의 변경 사항을 데이터 파일에 동기화하도록 DBWn에게 신호를 보내고, 완료된 체크포인트 정보를 Control File과 Datafile Header에 기록하여 추후 장애 복구 시간(MTTR)을 단축합니다.
4. SMON (System Monitor) & PMON (Process Monitor): SMON은 비정상 종료 후 재기동 시 Redo Log를 읽어 Instance Recovery(Roll Forward & Roll Back)를 수행하고 임시 세그먼트를 정리합니다. PMON은 비정상 종료된 사용자 세션을 감지하여 해당 세션이 잡고 있던 Lock과 리소스를 해제하고 트랜잭션을 롤백합니다.

3. 주요 원인 분석: 아키텍처 관점의 실무 병목 현상

각 메모리 영역과 프로세스의 설계 한계를 초과하는 워크로드가 유입될 때 다양한 대기 이벤트(Wait Event)와 성능 저하가 발생합니다.

3.1. Shared Pool 경합: 과도한 하드 파싱 (Hard Parsing)

애플리케이션에서 바인드 변수(Bind Variable)를 사용하지 않고 리터럴(Literal) SQL을 남발하면, 오라클은 매번 새로운 SQL로 인식하여 옵티마이저가 실행 계획을 새로 생성하는 하드 파싱을 수행합니다.

• 결과: Shared Pool 내부의 여유 공간을 찾고 할당받는 과정에서 library cache: mutex X 또는 shared pool latch 대기가 폭증하며 CPU 사용률이 100%에 육박합니다. 심한 경우 메모리 단편화(Fragmentation)로 인해 ORA-04031: unable to allocate bytes of shared memory 에러가 발생합니다.

3.2. Buffer Cache 및 DBWn 병목: Free Buffer Waits

대규모 배치 작업이나 비효율적인 인덱스 스캔으로 디스크에서 엄청난 양의 블록을 버퍼 캐시로 읽어 들일 때, DBWn이 Dirty Buffer를 디스크로 내려쓰는 속도보다 서버 프로세스가 Free Buffer를 요구하는 속도가 더 빠르면 발생합니다.

• 결과: 서버 프로세스는 빈 공간을 찾지 못해 free buffer waits 대기 이벤트에 빠지며, 전체 시스템의 I/O 응답 속도가 마비됩니다.

3.3. Redo Log 및 LGWR 병목: Log File Sync

OLTP 환경에서 루프(Loop) 내 건건이 커밋을 수행하는 등 초당 트랜잭션 커밋 횟수가 비정상적으로 높거나, Online Redo Log가 위치한 디스크의 I/O 성능이 느릴 때 발생합니다.

• 결과: LGWR이 리두 버퍼의 내용을 디스크에 다 기록할 때까지 세션들이 대기하는 log file sync 이벤트가 최상위 대기로 올라오며 트랜잭션 처리량(TPS)이 급감합니다.

3.4. PGA 부족: Direct Path Read / Write Temp

대용량 테이블 조인(Hash Join)이나 정렬 작업 시 할당된 PGA(Work Area) 메모리가 부족하면, 오라클은 중간 결과 집합을 디스크의 Temp 영역에 쓰고 읽는 작업을 반복합니다.

• 결과: 메모리 연산이 디스크 I/O 연산으로 바뀌면서 direct path write temp 및 direct path read temp 대기가 대량 발생하고 배치 작업 시간이 수배 이상 늘어납니다.

4. 트러블슈팅 및 해결 방안

아키텍처 병목을 진단하고 최적화하기 위한 DBA 실무 점검 쿼리와 튜닝 가이드입니다.

4.1. 1단계: 메모리 할당 현황 및 동적 리사이징 점검

오라클의 자동 메모리 관리 기능(AMM: MEMORY_TARGET 또는 ASMM: SGA_TARGET)을 사용 중이라면, 현재 각 컴포넌트에 메모리가 어떻게 분배되어 있는지, 그리고 빈번한 리사이징으로 인한 오버헤드는 없는지 확인해야 합니다.

-- 현재 SGA 및 PGA 주요 컴포넌트 메모리 할당 크기(MB) 확인
SELECT COMPONENT,
       ROUND(CURRENT_SIZE / 1024 / 1024, 2) AS CURRENT_MB,
       ROUND(MIN_SIZE / 1024 / 1024, 2) AS MIN_MB,
       ROUND(MAX_SIZE / 1024 / 1024, 2) AS MAX_MB,
       LAST_OPER_TYPE,
       TO_CHAR(LAST_OPER_TIME, 'YYYY-MM-DD HH24:MI:SS') AS LAST_OPER_TIME
  FROM V$MEMORY_DYNAMIC_COMPONENTS
 WHERE CURRENT_SIZE > 0
 ORDER BY CURRENT_MB DESC;

💡 아키텍트 실무 팁 (ASMM 환경의 하한선 설정): SGA_TARGET을 통한 자동 관리에만 전적으로 의존할 경우, 갑작스러운 리터럴 SQL 폭증 시 Buffer Cache 메모리를 과도하게 뺏어 Shared Pool로 넘기거나, 반대의 상황이 발생하며 시스템이 출렁일 수 있습니다. 실무에서는 db_cache_size와 shared_pool_size 파라미터에 최소 보장 크기(Minimum Value)를 명시적으로 설정해 두는 것이 안정적인 운영의 정석입니다.

4.2. 2단계: PGA 작업 영역 효율성(Optimal vs Multipass) 진단

PGA 크기가 적정한지 확인하려면 메모리 내에서 작업이 100% 끝난 비율(Optimal)과 디스크 Temp 영역으로 넘어간 비율(Onepass / Multipass)을 조회해야 합니다.

-- PGA Workarea 수행 통계 확인 (Optimal 비율이 95% 이상이어야 이상적)
SELECT NAME,
       VALUE,
       ROUND(RATIO_TO_REPORT(VALUE) OVER () * 100, 2) AS PCT
  FROM V$SYSSTAT
 WHERE NAME LIKE 'workarea executions - %';

-- 현재 PGA_AGGREGATE_TARGET 대비 어드바이저 추천 값 확인
SELECT ROUND(PGA_TARGET_FOR_ESTIMATE / 1024 / 1024) AS TARGET_MB,
        PGA_TARGET_FACTOR,
       ESTD_EXTRA_BYTES_RW,
       ESTD_PGA_CACHE_HIT_PERCENTAGE,
       ESTD_OVERALLOC_COUNT
  FROM V$PGA_TARGET_ADVICE
 ORDER BY PGA_TARGET_FACTOR;

점검 결과 Multipass가 지속적으로 발생하거나 ESTD_OVERALLOC_COUNT가 0보다 크다면, PGA_AGGREGATE_TARGET을 증설하거나 특정 대용량 배치 세션에 한해 수동으로 Sort/Hash 영역을 넓혀주어야 합니다.

-- 시스템 전체 PGA 목표치 증설 예시 (8GB)
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 8G SCOPE=BOTH;

-- 특정 대용량 배치 세션에서만 수동으로 Workarea 크기 강제 할당
ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;
ALTER SESSION SET HASH_AREA_SIZE = 1048576000; -- 약 1GB
ALTER SESSION SET SORT_AREA_SIZE = 524288000;  -- 약 500MB

4.3. 3단계: Shared Pool 및 하드 파싱 튜닝

하드 파싱으로 인한 ORA-04031이나 Mutex 경합을 막기 위해서는 바인드 변수 사용을 표준화하는 것이 최우선입니다. 애플리케이션을 즉시 수정할 수 없는 긴급 상황이라면 아래 조치를 고려합니다.

• CURSOR_SHARING 파라미터 검토: 리터럴 값을 오라클이 강제로 바인드 변수로 치환하도록 설정합니다. 단, 히스토그램을 활용해야 하는 컬럼에서 실행 계획이 틀어지는 부작용(Bind Peeking 이슈 등)이 있을 수 있으므로 세션 레벨이나 테스트 검증 후 적용해야 합니다.
• 대용량 패키지/프로시저 고정: 자주 사용되는 핵심 PL/SQL 객체는 DBMS_SHARED_POOL.KEEP 프로시저를 사용해 Shared Pool 메모리에 상주(Pinning)시켜 단편화를 예방합니다.

4.4. 4단계: DBWn 및 LGWR I/O 병목 해소

• free buffer waits 다발 시: 1차적으로 불필요한 대량 블록을 읽는 악성 Full Scan 쿼리를 튜닝하여 논리적/물리적 I/O 요구량 자체를 줄여야 합니다. 하드웨어적으로는 비동기 I/O(Async I/O)가 활성화되어 있는지 점검하고, 필요시 DB_WRITER_PROCESSES 파라미터를 조정하여 DBWn 프로세스 개수를 늘려 쓰기 처리량을 높입니다.
• log file sync 다발 시: 애플리케이션의 불필요한 잦은 커밋(Fetch Across Commit 등)을 배치 커밋으로 변경합니다. 인프라 관점에서는 Online Redo Log 파일들을 가장 I/O 성능이 뛰어난 디스크(SSD/NVMe)에 격리 배치하고, 로그 스위치가 너무 자주(예: 1~2분 간격) 발생하지 않도록 리두 로그 그룹 및 파일 크기를 넉넉하게 증설합니다.

5. 요약

1. 오라클 서버는 공유 메모리(SGA)와 백그라운드 프로세스로 이루어진 인스턴스(Instance), 그리고 물리적 디스크 파일의 집합인 데이터베이스(Database)로 구성된다.
2. SGA의 Buffer Cache는 데이터 블록의 I/O 캐싱을, Shared Pool은 SQL 파싱 및 실행 계획 공유를 담당하며, 프로세스 전용 영역인 PGA는 래치 경합 없이 정렬(Sort) 및 해시 조인 연산을 수행한다.
3. LGWR은 커밋 시점에 리두 로그를 즉시 디스크에 기록(Fast Commit)하여 영속성을 보장하고, DBWn은 체크포인트나 공간 부족 시점에 Dirty Buffer를 비동기적으로 데이터 파일에 내려쓴다.
4. 아키텍처 경합을 예방하려면 바인드 변수 사용(Shared Pool 보호), SQL 튜닝을 통한 블록 I/O 감소(Buffer Cache 보호), 적정 PGA 할당을 통한 Temp I/O 방지, 그리고 적절한 커밋 주기와 빠른 디스크 구성(LGWR 최적화)이 유기적으로 이루어져야 한다.