RAC환경 lob 튜닝

RAC 환경 LOB 튜닝

1. RAC 환경에서의 LOB 동시성 이슈 - 전문가 심화 분석
2. 문제의 본질: 왜 LOB이 RAC에서 특별히 까다로운가?

일반 블록 vs LOB 블록의 근본적 차이

• 일반 데이터 블록 (Cache Fusion 대상)

      → 상대적으로 작고 균일한 크기
      → 블록 단위 공유가 예측 가능

• LOB 청크 (CHUNK)

      → 크기가 크고(보통 8K~32K), 여러 블록으로 구성
      → 하나의 LOB 조작이 다수의 블록을 한번에 건드림
      → Cache Fusion 전송 비용이 일반 블록보다 훨씬 큼

• 핵심 문제: RAC의 Cache Fusion은 "블록 단위" 공유를 전제로 설계되었는데, LOB은 대용량 데이터 특성상 이 모델과 근본적으로 마찰이 있습니다.

LOB의 3단 구조가 만드는 3중 경합 지점

Instance 1                          Instance 2
    │                                    │
    ▼                                    ▼
[LOB Locator] ──────GES 관리──────► [LOB Locator]
    │                                    │
    ▼                                    ▼
[LOB Index] ◄──── GCS/GES 경합 ────► [LOB Index]   ← 경합 지점 #1
    │                                    │
    ▼                                    ▼
[LOB Segment] ◄─── GCS 경합 ───────► [LOB Segment] ← 경합 지점 #2
    │                                    │
    ▼                                    ▼
[HWM/Extent] ◄──── HW Enqueue ─────► [HWM/Extent]  ← 경합 지점 #3

---

경합 지점별 상세 분석

1. 경합 지점 #1: LOB Index Contention

   • 메커니즘 : LOB Index는 B-Tree 구조이므로, 여러 인스턴스에서 동시에 LOB INSERT가 발생하면 인덱스 리프 블록에 대한 경합 발생.

   -- 이 대기 이벤트가 자주 보이면 LOB Index 경합 의심
   SELECT event, count(*)
   FROM v$session
   WHERE event LIKE '%TX%' OR event LIKE '%index contention%'
   GROUP BY event;

2. 대기 이벤트 시그니처

   enq: TX - index contention          -- LOB Index 리프 블록 분할 경합
   gc buffer busy acquire               -- LOB Index 블록의 노드간 전송 대기
   gc cr block busy                     -- Consistent Read 블록 요청 지연

   • 실전 확인 쿼리

SELECT 
    a.inst_id, a.sid, a.event, a.p1, a.p2, a.p3,
    o.object_name, o.object_type
FROM gv$session_wait a, dba_objects o
WHERE a.event LIKE '%gc%'
  AND a.p1 = o.object_id  -- 근사치, 실제로는 block 매핑 필요
  AND o.object_type = 'LOB'
ORDER BY a.inst_id;

1. 경합 지점 #2: LOB Segment Cache Fusion 경합

   • 시나리오: Instance 1과 Instance 2가 동일 LOB 세그먼트의 인접한 CHUNK에 동시 쓰기 시도.

   Instance 1: INSERT INTO t (id, doc) VALUES (1, '대용량문서A')
                   → CHUNK #501 할당 요청
   
   Instance 2: INSERT INTO t (id, doc) VALUES (2, '대용량문서B')  
                   → CHUNK #502 할당 요청 (인접 블록)
                   
       → 같은 세그먼트 헤더/비트맵 블록 경합 발생
       → "gc buffer busy" 또는 "gc current block busy" 대기

   • 핵심 대기 이벤트

-- AWR에서 확인
SELECT event, total_waits, time_waited_micro/1000000 AS sec
FROM dba_hist_system_event
WHERE event IN (
    'gc buffer busy acquire',
    'gc buffer busy release', 
    'gc current block busy',
    'gc cr block busy',
    'gc current grant busy'
)
ORDER BY time_waited_micro DESC;

1. 경합 지점 #3: HW (High Water Mark) Enqueue - 가장 흔한 실전 문제

   *원리*: LOB Segment가 확장(extent 추가)될 때 HWM을 이동시켜야 하는데, 이 작업은 인스턴스 간 직렬화가 필요합니다.

   -- 실제 프로덕션에서 가장 흔하게 목격되는 패턴
   SELECT inst_id, event, count(*), avg(wait_time)
   FROM gv$session_wait_history
   WHERE event = 'enq: HW - contention'
   GROUP BY inst_id, event;

   • 시나리오 재현

[Instance 1]                    [Instance 2]
LOB INSERT 대량 발생                LOB INSERT 대량 발생
    │                                   │
    ▼                                   ▼
Extent 소진 → HWM 이동 필요      Extent 소진 → HWM 이동 필요
    │                                   │
    └──────► HW Enqueue 대기 직렬화 ◄───┘
    
    결과: 두 인스턴스 모두 "enq: HW - contention" 대기

---

ASSM과 LOB의 상호작용 (RAC 필수 지식)

반드시 ASSM 사용해야 하는 이유

-- Tablespace 생성 시 반드시 확인
SELECT tablespace_name, segment_space_management
FROM dba_tablespaces
WHERE tablespace_name = 'YOUR_LOB_TS';
-- segment_space_management = 'AUTO' 여야 함

• MSSM(Manual) vs ASSM(Auto) 차이

| 구분 | MSSM (FREELIST) | ASSM (Bitmap) | |---|---|---| | RAC 확장성 | FREELIST GROUPS 수동 구성 필요 | 자동으로 인스턴스별 분산 | | HW 경합 | 매우 심함 | 상대적으로 완화 | | 권장 여부 | ❌ RAC에서 지양 | ✅ 필수 권장 |

RETENTION vs PCTVERSION - RAC에서의 실질적 차이

-- RAC 환경에서는 RETENTION 방식이 유리
CREATE TABLE lob_test (
    id NUMBER,
    doc CLOB
) LOB (doc) STORE AS SECUREFILE (
    TABLESPACE lob_ts   -- ASSM 필수
    RETENTION            -- PCTVERSION 대신 권장
    ...
);

• 이유 : PCTVERSION은 "공간 비율" 기반이라 다중 인스턴스의 동시 버전 생성 패턴을 예측하기 어려움. RETENTION(시간 기반)이 UNDO_RETENTION과 일관되게 동작하여 다중 인스턴스 환경에서 더 예측 가능.

---

SecureFile의 RAC 최적화 메커니즘 (핵심)

Write Gathering (쓰기 병합)

• SecureFile은 내부적으로 여러 개의 작은 쓰기를 병합하여 Cache Fusion 전송 횟수를 줄입니다.

-- SecureFile 내부 최적화 확인 (간접적)
SELECT name, value 
FROM v$sysstat
WHERE name LIKE '%securefile%' OR name LIKE '%lob%';

주요 통계:

securefile direct read bytes
securefile direct write bytes
securefile number of non-transformed reads
securefile bytes non-transformed via cache

1. 1. 1. 4.2 Space Reservation (공간 예약) - RAC HW 경합 완화 핵심 기법

```sql CREATE TABLE lob_test (

   id NUMBER,
   doc CLOB

) LOB (doc) STORE AS SECUREFILE (

   TABLESPACE lob_ts
   CHUNK 8192
   RETENTION
   STORAGE (
       INITIAL 10M
       NEXT 10M          -- 더 큰 단위로 미리 확장하여 HW 이동 빈도 감소
       MAXEXTENTS UNLIMITED
   )

); ```

• • 전략**: Extent 크기를 크게 잡아서(NEXT 값 상향) HW 이동 **빈도 자체**를 줄이는 것이 RAC에서 매우 효과적.

1. 1. 1. 4.3 SecureFile 필수 확인 - 인스턴스별 Segment 분산

```sql -- Oracle 11g R2 이후 SecureFile은 내부적으로 -- 인스턴스 어피니티(affinity)를 고려한 익스텐트 할당 로직 보유 SELECT

   inst_id, 
   segment_name,
   extent_id,
   bytes

FROM gv$... -- 실제로는 x$ 뷰 레벨 분석 필요 (kcbsw 등) ```

---

1. 1. 5. 실전 시나리오별 분석

1. 1. 1. 5.1 시나리오 A: 다중 노드 로그 적재 시스템

``` 문제 상황: - 5개 RAC 노드에서 동시에 CLOB 로그 INSERT - 초당 수천 건 발생 - "enq: HW - contention" 대기가 전체 대기의 40% 차지 ```

• • 해결 접근법**:

```sql -- 1. 익스텐트 크기 대폭 상향 ALTER TABLE log_table MODIFY LOB (log_data) (

   STORAGE (NEXT 100M)

);

-- 2. 인스턴스별 파티셔닝 고려 (근본적 해결) CREATE TABLE log_table (

   id NUMBER,
   inst_id NUMBER,
   log_data CLOB

) PARTITION BY LIST (inst_id) (

   PARTITION p1 VALUES (1),
   PARTITION p2 VALUES (2),
   PARTITION p3 VALUES (3)
   -- 각 인스턴스가 자신의 파티션에만 쓰기 → HW 경합 원천 차단

); ```

1. 1. 1. 5.2 시나리오 B: LOB 인덱스 리프 블록 스플릿 경합

``` 문제 상황: - 순차적이지 않은 LOB ID로 동시 INSERT (예: GUID 기반) - 여러 노드가 인덱스의 서로 다른 위치에 리프 스플릿 유발 - "gc buffer busy acquire" 이벤트 급증 ```

• • 해결 접근법**:

```sql -- Reverse Key Index와 유사한 개념이 LOB Index에는 직접 적용 불가 -- 대신 애플리케이션 레벨 해시 분산 고려

-- 또는 LOB Index를 위한 별도 튜닝 (드문 케이스) -- Interested Transaction List(ITL) 관련 파라미터 검토 ALTER TABLE lob_test MODIFY LOB (doc) (

   STORE AS SECUREFILE (...)

) ; -- INITRANS 상향 검토 필요할 수 있음 ```

---

1. 1. 6. CACHE 옵션과 RAC Global Cache의 실질적 관계

1. 1. 1. 6.1 중요한 오해 정정

``` "CACHE로 설정하면 RAC에서 자동으로 전 노드에 캐싱된다" → 반은 맞고 반은 틀림 ```

• • 실제 동작**:

``` CACHE 옵션 = Buffer Cache 사용 여부 결정 (로컬 인스턴스 관점)

   │
   ▼

Buffer Cache에 있는 LOB 블록은 여전히 Cache Fusion 프로토콜 따름

   │
   ▼

즉, CACHE 설정 시 오히려 Cache Fusion 트래픽이 증가할 수 있음 (NOCACHE는 Direct Path I/O라서 애초에 Global Cache 경합이 적음) ```

1. 1. 1. 6.2 RAC에서의 실전 권장사항

| 워크로드 패턴 | 권장 옵션 | 이유 | |---|---|---| | 대용량, 1회성, 노드 분산 접근 | **NOCACHE** | Direct I/O로 Cache Fusion 우회 → 경합 최소화 | | 소용량, 특정 노드 반복 접근 | CACHE | 로컬 캐싱 이득 > Cache Fusion 비용 | | 다중 노드 랜덤 접근, 중간 크기 | CACHE READS | 쓰기는 우회, 읽기만 캐싱 |

```sql -- RAC 환경 대용량 첨부파일 시스템 권장 설정 LOB (doc) STORE AS SECUREFILE (

   NOCACHE           -- Global Cache 경합 원천 차단
   NOLOGGING         -- 가능한 경우 (Standby 고려 필요)
   CHUNK 32768        -- 큰 청크로 I/O 효율 극대화

) ```

---

1. 1. 7. Parallel DML + LOB + RAC 삼중 복잡도

1. 1. 1. 7.1 병렬 INSERT 시 발생하는 문제

ALTER SESSION ENABLE PARALLEL DML;

INSERT /*+ PARALLEL(4) */ INTO lob_test
SELECT id, large_doc FROM source_table;

• • 내부 동작**:

``` Parallel Slave 1 (Node A) ──┐ Parallel Slave 2 (Node A) ──┼──► 동일 LOB Segment에 동시 쓰기 Parallel Slave 3 (Node B) ──┤ → HW enqueue 경합 극대화 Parallel Slave 4 (Node B) ──┘ ```

• • 완화 전략**:

```sql -- PDML + LOB 사용 시 필수 고려사항 -- 1) 충분히 큰 익스텐트 사이즈 사전 확보 -- 2) 가능하면 병렬도를 노드 수의 배수로 제한하지 않고 -- 단일 노드 집중 실행 고려 (역설적이지만 효과적일 때 있음) ALTER SESSION SET INSTANCE_GROUPS = 'single_node_group'; ```

---

1. 1. 8. 실전 모니터링 스크립트

1. 1. 1. 8.1 종합 LOB RAC 경합 진단 쿼리

-- LOB 관련 대기 이벤트 종합 (AWR 기반)
SELECT 
    dhse.instance_number,
    dhse.event_name,
    dhse.total_waits,
    ROUND(dhse.time_waited_micro/1000000, 2) AS wait_sec
FROM dba_hist_system_event dhse
WHERE dhse.event_name IN (
    'enq: HW - contention',
    'enq: TX - index contention',
    'gc buffer busy acquire',
    'gc buffer busy release',
    'gc current block busy',
    'gc cr block busy',
    'direct path write',
    'direct path read'
)
AND dhse.snap_id = (SELECT MAX(snap_id) FROM dba_hist_snapshot)
ORDER BY wait_sec DESC;

1. 1. 1. 8.2 실시간 세션 레벨 진단

SELECT 
    s.inst_id, s.sid, s.serial#, s.event,
    s.seconds_in_wait,
    s.program,
    sql.sql_text
FROM gv$session s, gv$sql sql
WHERE s.sql_id = sql.sql_id(+)
  AND (s.event LIKE '%gc%' OR s.event LIKE '%HW%')
  AND s.wait_class != 'Idle'
ORDER BY s.seconds_in_wait DESC;

1. 1. 1. 8.3 LOB 세그먼트별 익스텐트 분산 확인

```sql -- 익스텐트가 특정 노드/인스턴스에 편중되어 있는지 확인 SELECT

   owner, segment_name, 
   extent_id, bytes, blocks,
   relative_fno

FROM dba_extents WHERE segment_name = (

   SELECT segment_name FROM dba_lobs 
   WHERE table_name = 'YOUR_TABLE'

) ORDER BY extent_id; ```

---

1. 1. 9. 종합 Best Practice 체크리스트 (RAC + LOB)

```sql -- 최종 권장 템플릿 CREATE TABLE rac_optimized_lob_table (

   id NUMBER,
   partition_key NUMBER,  -- 인스턴스/노드 분산용 컬럼 고려
   doc CLOB

) PARTITION BY HASH (partition_key) PARTITIONS 8 -- 경합 원천 분산 LOB (doc) STORE AS SECUREFILE (

   TABLESPACE lob_ts        -- ASSM 필수 확인
   ENABLE STORAGE IN ROW
   CHUNK 16384                -- 워크로드에 맞게 조정
   RETENTION                  -- PCTVERSION 대신
   NOCACHE                    -- RAC 경합 최소화 (워크로드별 조정)
   COMPRESS MEDIUM            -- I/O 자체를 줄여 경합 시간 단축
   STORAGE (
       INITIAL 50M
       NEXT 50M              -- 큰 익스텐트로 HW 이동 빈도 감소
       MAXEXTENTS UNLIMITED
   )

); ```

1. 1. 1. 체크리스트

| 항목 | 확인 사항 | |---|---| | ☑ Tablespace | ASSM (AUTO Segment Space Management) 여부 | | ☑ LOB Type | SecureFile 사용 여부 (BasicFile 지양) | | ☑ Extent 전략 | 큰 NEXT 값으로 HW 이동 빈도 최소화 | | ☑ 파티셔닝 | 가능하면 노드/워크로드 특성 따라 분산 | | ☑ CACHE 옵션 | 워크로드 패턴에 맞게 선택 (기본 NOCACHE 권장) | | ☑ RETENTION | PCTVERSION 대신 RETENTION 사용 검토 | | ☑ 모니터링 | HW/gc buffer busy 이벤트 정기 추적 |

---

1. 1. 핵심 요약

1. **HW Enqueue**가 RAC + LOB 환경에서 가장 흔하고 치명적인 경합 → **큰 익스텐트 크기**로 완화 2. **ASSM은 선택이 아닌 필수** - MSSM은 RAC에서 사실상 사용 불가 수준 3. **SecureFile의 Write Gathering**이 Cache Fusion 트래픽을 내부적으로 최적화 4. **NOCACHE가 RAC에서는 오히려 유리한 경우가 많음** (Direct I/O로 Global Cache 우회) 5. **파티셔닝을 통한 물리적 분산**이 근본적 해결책 (경합 자체를 원천 차단) 6. **PCTVERSION보다 RETENTION**이 다중 인스턴스 환경에서 예측 가능성이 높음

더 깊이 다뤄볼 만한 주제로 **Exadata Smart Scan과 LOB의 상호작용**, **GoldenGate/Data Guard 환경에서 SecureFile LOB 복제 시 이슈**, 또는 **AWR/ASH 리포트를 활용한 실전 LOB 경합 튜닝 케이스 스터디** 등이 있는데, 관심 있으신 영역 있으신가요?