Oracle 읽기 일관성: SCN·CR Block·Current Get·SQL Trace

핵심 요약

Oracle AI Database는 일반 조회가 실행되는 동안 다른 트랜잭션의 변경이 발생하더라도, 조회가 기준으로 삼는 시점에 맞는 커밋된 데이터를 일관되게 반환합니다. 기본 READ COMMITTED에서는 각 SQL 문장이 열릴 때 Statement-Level Snapshot이 정해지고, SERIALIZABLE 또는 READ ONLY 트랜잭션에서는 트랜잭션 시작 시점의 Snapshot을 여러 문장이 공유합니다.

조회 기준 SCN 결정
→ 현재 블록의 트랜잭션 상태 확인
→ 기준 SCN보다 새로운 변경이면 Undo 적용
→ CR(Consistent Read) Clone 생성 또는 재사용
→ 동일한 기준 시점의 결과 반환

SQL Trace와 세션 통계에서는 다음 축을 분리해서 읽습니다.

query   ↔ consistent gets ↔ Consistent Mode Buffer Get
current ↔ db block gets   ↔ Current Mode Buffer Get
disk    ↔ physical reads  ↔ 물리 블록 읽기

query + current는 대략적인 Logical I/O 방문 횟수이며, 고유 블록 개수나 변경된 블록 개수와 같지 않습니다.

학습 목표

• 격리 수준별 Snapshot 기준 시점을 구분한다.
• SCN, ITL, Undo가 CR Clone 생성에 어떻게 연결되는지 설명한다.
• Current Block과 CR Block의 용도를 구분한다.
• 일반 SELECT, DML, SELECT FOR UPDATE의 동시성 차이를 설명한다.
• SQL Trace의 query, current, disk를 세션 통계와 연결한다.
• consistent changes, CR blocks created, V$UNDOSTAT로 읽기 일관성 비용과 ORA-01555를 진단한다.

1. 읽기 일관성이 필요한 이유

다음 집계가 수행되는 동안 주문 데이터가 계속 변경된다고 가정합니다.

SELECT SUM(amount)
FROM   orders
WHERE  order_date >= DATE '2026-07-01'
AND    order_date <  DATE '2026-08-01';

앞쪽 블록은 변경 전 값으로, 뒤쪽 블록은 변경 후 값으로 읽는다면 실제 어느 시점에도 존재하지 않았던 합계가 만들어질 수 있습니다. Oracle은 SQL 문장이 일관성을 유지할 기준 시점을 정하고, 그 시점에 커밋된 데이터 버전만 조합해 반환합니다. 이를 읽기 일관성(Read Consistency)이라고 합니다.

읽기 일관성이 보장하는 핵심은 다음 두 가지입니다.

1. 다른 트랜잭션의 미커밋 변경을 일반 SELECT에 노출하지 않는다.
2. 하나의 Snapshot 안에서 서로 다른 시점의 데이터 버전을 섞지 않는다.

2. SCN과 Snapshot 기준

SCN(System Change Number)은 데이터베이스 변경 순서를 표현하는 내부 논리 번호입니다. 벽시계 시각 그 자체가 아니라, 트랜잭션의 커밋 순서와 데이터 버전의 가시성을 판단하는 기준입니다.

2.1 READ COMMITTED

Oracle의 기본 격리 수준입니다. 각 SQL 문장이 열릴 때 새로운 기준 SCN을 사용합니다.

첫 번째 SELECT 기준 SCN = 1000
다른 트랜잭션 COMMIT SCN = 1010
두 번째 SELECT 기준 SCN = 1020

첫 번째 SELECT에는 SCN 1010의 변경이 보이지 않지만, 두 번째 SELECT에는 보일 수 있습니다. 따라서 같은 트랜잭션 안에서도 두 SELECT의 결과가 달라질 수 있습니다.

2.2 SERIALIZABLE과 READ ONLY

SERIALIZABLE 또는 READ ONLY 트랜잭션은 트랜잭션 시작 시점에 맞춘 Transaction-Level Read Consistency를 사용합니다. 여러 SELECT가 같은 트랜잭션 Snapshot을 공유하므로, 트랜잭션 시작 이후 다른 세션이 커밋한 변경은 해당 트랜잭션의 일반 조회에 보이지 않습니다.

자신의 트랜잭션이 아직 커밋하지 않은 변경은 같은 세션에서 볼 수 있습니다. 이는 다른 세션에 Dirty Read를 허용하는 것이 아니라 Read Your Own Writes입니다.

3. Current Block과 CR Clone

3.1 Current Block

Current Block은 현재 상태를 기준으로 접근하는 블록입니다. 실제 행을 변경하거나 행 잠금을 획득해야 하는 DML과 SELECT FOR UPDATE는 Current Mode 접근이 필요합니다. SQL Trace의 current, 인스턴스·세션 통계의 db block gets가 이 접근 횟수와 연결됩니다.

3.2 CR Block 또는 CR Clone

CR(Consistent Read) Block은 조회의 기준 SCN에 맞도록 재구성한 읽기 버전입니다. 현재 블록이 기준 SCN보다 새로운 변경을 포함하면 Oracle은 현재 블록을 복제한 뒤 Undo를 적용해 과거 버전을 재구성합니다. 공식 문서에서는 이를 CR Clone이라고 설명합니다.

Current Block 확보
→ 블록 헤더의 ITL 확인
→ Undo Segment의 Transaction Table에서 상태·Commit SCN 확인
→ 필요한 Undo Record를 역방향 적용
→ 기준 SCN에 맞는 CR Clone 생성

CR Clone은 데이터 파일에 영구 저장한 별도 과거 복사본이 아닙니다. Buffer Cache에서 읽기 일관성을 위해 생성하거나 재사용하는 블록 버전입니다.

4. ITL과 Undo가 연결되는 방식

세그먼트 블록 헤더의 ITL(Interested Transaction List)은 해당 블록을 변경한 트랜잭션과 잠금 행에 관한 정보를 가집니다. ITL은 Undo Segment의 Transaction Table을 가리키며, Oracle은 이를 통해 다음을 판단합니다.

• 조회가 시작될 때 변경 트랜잭션이 미커밋 상태였는가?
• 변경이 조회 기준 SCN 이전에 커밋되었는가?
• 기준 SCN으로 되돌리기 위해 어떤 Undo Record가 필요한가?

Undo는 Rollback에만 쓰이는 것이 아닙니다. 이미 커밋된 변경이라도 더 오래된 Snapshot을 재구성해야 한다면, 해당 변경의 Undo가 읽기 일관성에 필요합니다.

5. Reader와 Writer의 동시 처리

Session A가 행을 변경한 뒤 아직 커밋하지 않았다고 가정합니다.

-- Session A
UPDATE account
SET    balance = balance - 100
WHERE  account_id = 10;

Session B의 일반 SELECT는 A의 미커밋 값을 읽지 않습니다. 일반적인 경우 Session B는 Undo를 이용해 자신의 기준 SCN에 맞는 커밋 버전을 읽습니다.

-- Session B
SELECT balance
FROM   account
WHERE  account_id = 10;

일반 SELECT가 언제나 어떤 이유로도 대기하지 않는다는 뜻은 아닙니다. 이론의 핵심은 다른 트랜잭션의 미커밋 행 값을 보기 위해 커밋을 기다리지 않고, Undo 기반의 일관 버전을 읽는다는 것입니다.

6. SQL Trace의 query·current·disk

TKPROF Call 통계의 핵심 열은 다음과 같습니다.

call      count   cpu  elapsed  disk  query  current  rows
Parse         1   ...      ...     0      0        0     0
Execute       1   ...      ...     0      2        1     1
Fetch         2   ...      ...     3     15        0    10

위 예시의 전체 Buffer 방문은 대략 query 17 + current 1 = 18회입니다. 같은 블록을 여러 번 방문하면 매번 Get이 증가하므로, 18개의 서로 다른 블록을 처리했다는 의미는 아닙니다.

SQL_TRACE 초기화 파라미터는 하위 호환성을 위해 남아 있지만 현재 문서에서는 Deprecated로 분류됩니다. 특정 세션이나 모듈을 추적할 때는 DBMS_MONITOR, 필요에 따라 DBMS_SESSION을 우선 검토합니다. 전체 인스턴스 Trace는 성능과 파일 공간에 큰 영향을 줄 수 있으므로 피해야 합니다.

7. query와 current를 잘못 해석하지 않는 법

7.1 query

query는 SQL 문장 수가 아니라 Consistent Mode Buffer Get 횟수입니다. 조회와 Subquery 처리는 주로 Query Mode를 사용합니다.

7.2 current

current는 Dirty Block 수, 변경 행 수, 미커밋 트랜잭션 수가 아닙니다. Current Mode Block 요청 횟수입니다. DML의 대상 블록, 세그먼트 헤더, 행 잠금이 필요한 블록 등이 Current Mode로 접근될 수 있습니다.

일반 SELECT에서 current > 0이 관찰되었다는 사실만으로 사용자 DML이 수행됐다고 단정하지 않습니다. 내부 관리 작업이나 블록 정리와 관련된 Current 접근 가능성을 실행 흐름과 함께 확인해야 합니다.

7.3 disk

disk = 0은 필요한 블록이 Buffer Cache에 있었음을 뜻할 수 있습니다. Logical I/O가 많다면 Physical Read 없이도 CPU와 Latch·Mutex 등 메모리 접근 비용이 커질 수 있습니다.

Logical I/O의 큰 흐름 ≈ query + current

이 식은 작업량을 빠르게 파악하기 위한 근사입니다. Direct Path Read, Exadata Offload, In-Memory 등 환경별 세부 통계는 별도 확인해야 합니다.

8. CR 재구성 비용을 보는 통계

다음 통계는 읽기 일관성 비용을 더 구체적으로 설명합니다.

consistent gets가 많아도 consistent changes가 작다면 많은 블록을 일관 모드로 읽었지만 Undo 적용은 상대적으로 적었을 수 있습니다. 반대로 consistent changes가 크면 과거 버전 재구성에 더 많은 작업이 발생한 것입니다.

세션 단위 확인 예시는 다음과 같습니다.

SELECT n.name,
       s.value
FROM   v$sesstat s
JOIN   v$statname n
  ON   n.statistic# = s.statistic#
WHERE  s.sid = :sid
AND    n.name IN (
         'consistent gets',
         'consistent changes',
         'CR blocks created',
         'data blocks consistent reads - undo records applied',
         'db block gets',
         'physical reads'
       )
ORDER BY n.name;

누적 통계이므로 측정 전후 Delta를 구해야 특정 SQL의 증가량에 가깝게 해석할 수 있습니다. 가능하면 SQL Trace, DBMS_XPLAN.DISPLAY_CURSOR의 실행 통계와 함께 비교합니다.

9. ORA-01555와 Undo 보존

오래 실행되는 Query는 Cursor Open부터 마지막 Fetch까지 같은 기준 시점의 데이터가 필요할 수 있습니다. 그 사이 필요한 커밋 Undo가 재사용되면 과거 블록을 재구성할 수 없어 ORA-01555: snapshot too old가 발생할 수 있습니다.

UNDO_RETENTION은 Undo를 유지하려는 최소 기준이지만, 일반 설정에서 Undo Tablespace 공간이 부족하면 Unexpired Undo도 재사용될 수 있습니다. 즉, 파라미터 값만으로 보존이 절대 보장되는 것은 아닙니다. RETENTION GUARANTEE는 보존을 우선하지만, 공간 부족 시 DML 실패 위험을 높이므로 업무 영향까지 검토해야 합니다.

9.1 V$UNDOSTAT 핵심 열

SELECT begin_time,
       end_time,
       maxquerylen,
       maxqueryid,
       undoblks,
       tuned_undoretention,
       unxpblkreucnt,
       ssolderrcnt,
       nospaceerrcnt
FROM   v$undostat
ORDER BY begin_time DESC;

V$UNDOSTAT은 일반적으로 10분 단위 통계를 최근 4일 범위로 제공합니다. 더 오래된 이력은 라이선스와 환경을 확인한 뒤 AWR의 DBA_HIST_UNDOSTAT 등을 검토합니다.

10. 실전 진단 절차

1. 격리 수준과 Snapshot 기준 확인
2. 실패 SQL의 Cursor Open~마지막 Fetch 시간 확인
3. 실행계획의 A-Rows·Buffers와 Trace의 query·current·disk 확인
4. consistent changes와 Undo Record 적용량 확인
5. 같은 시간대 V$UNDOSTAT의 MAXQUERYLEN·UNDOBLKS·TUNED_UNDORETENTION 확인
6. UNXPBLKREUCNT·SSOLDERRCNT·NOSPACEERRCNT로 공간 압박 확인
7. SQL 접근 범위 축소, Fetch 방식 개선, Undo 공간·스케줄 조정을 순서대로 검토

SQL이 비효율적으로 많은 블록을 방문하면 실행시간이 길어지고, 그만큼 더 오래된 Undo가 필요해집니다. 따라서 ORA-01555를 Undo 용량 문제로만 보지 말고 실행계획과 Fetch 패턴을 함께 개선해야 합니다.

11. 튜닝 전후 비교

같은 SQL을 비교할 때는 결과 행 수와 실행 조건을 맞춘 뒤 다음을 함께 봅니다.

A-Rows와 결과 건수
→ query와 current
→ disk
→ consistent changes와 Undo 적용량
→ CPU·Elapsed
→ 실행계획 Operation별 Buffers

인덱스를 추가한 뒤 disk만 줄고 query가 그대로 크다면 Cache 또는 물리 읽기는 개선됐지만 Logical Access 범위는 줄지 않았을 수 있습니다. 반대로 disk = 0인 단일 측정만으로 튜닝 성공을 판단하면 Warm Cache 효과를 SQL 개선 효과로 오인할 수 있습니다.

12. 혼동하기 쉬운 판단

핵심 판단 순서

Snapshot 기준은 Statement인가 Transaction인가?
→ 현재 블록을 그대로 사용할 수 있는가?
→ Undo를 얼마나 적용해 CR Clone을 만드는가?
→ query·current·disk 중 비용이 어디에 나타나는가?
→ 장기 Fetch와 Undo 재사용이 겹쳤는가?
→ SQL 접근량, Fetch 방식, Undo 공간 중 무엇을 먼저 개선할 것인가?

검증 기준 문서

• 한국데이터산업진흥원 SQL 전문가 시험주요내용
• Oracle AI Database Concepts 26ai: Data Concurrency and Consistency
• Oracle AI Database Performance Tuning Guide 26ai: Performing Application Tracing
• Oracle AI Database Reference 26ai: Statistics Descriptions, V$UNDOSTAT, SQL_TRACE, UNDO_RETENTION
• Oracle AI Database Administrator's Guide 26ai: Managing Undo