SQL 작업량의 기초: Logical I/O·Physical I/O와 읽기 방식

핵심 요약

Oracle의 I/O 성능은 단순히 “디스크를 몇 번 읽었는가”만으로 판단하지 않습니다. Database는 Block 단위로 데이터를 처리하며, SQL이 수행한 논리적 Block 접근량과 Storage 계층에 요청한 물리 I/O를 서로 다른 통계로 관찰합니다.

일반 Buffer Cache 경로
  Block 요청
    → Buffer Cache 또는 관리되는 Block Image 접근
       ├─ 필요한 Block Image 존재
       │    → Logical I/O
       └─ 필요한 Block Image 부재
            → Storage Read
            → Cache에 Block 적재
            → Logical I/O로 Block 사용

Direct Path 경로
  Storage Read
    → 일반 Buffer Cache 경로를 우회
    → Process Private Memory(PGA 등)로 전달 가능

핵심 구분은 다음과 같습니다.

Physical Read Block 수
  ≠ Physical I/O Request 수

Logical I/O가 작음
  ≠ Physical I/O가 반드시 작음

Cache Hit Ratio가 높음
  ≠ SQL이 효율적임

SQL 튜닝에서는 다음 질문을 우선합니다.

1. 한 번 실행할 때 몇 Block을 방문하는가?
2. 결과 한 행을 만들기 위해 몇 Block을 방문하는가?
3. 몇 개의 후보 행을 읽고 몇 행을 버리는가?
4. Block 수와 I/O Request 수는 각각 얼마인가?
5. Single Block·Multiblock·Prefetch·Direct Path 중 어떤 경로가 사용됐는가?
6. CPU와 I/O Wait가 전체 응답시간에서 차지하는 비중은 얼마인가?

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → SQL 수행 구조 → 데이터베이스 I/O 메커니즘 범위에서 Logical·Physical I/O와 대표 읽기 경로를 다룹니다. SQL Trace·TKPROF의 Call별 통계, Wait Event 정밀 분석, 각 인덱스·조인 방식의 상세 튜닝은 후속 이론에서 다룹니다.

학습 목표

이 이론을 학습한 뒤에는 다음 내용을 설명할 수 있어야 합니다.

• Oracle이 Row가 아니라 Database Block 단위로 I/O를 관리하는 이유를 설명한다.
• consistent gets, db block gets, session logical reads의 관계를 설명한다.
• Logical Read가 Buffer Cache뿐 아니라 Process Private Memory의 Block 접근을 포함할 수 있음을 설명한다.
• physical reads, physical reads cache, physical reads direct를 구분한다.
• Physical Read Block 수·I/O Request 수·Byte 수를 구분한다.
• Application I/O 통계와 Instance 전체 I/O 통계의 범위를 구분한다.
• Single Block Read와 Multiblock Read의 대표 Access Path를 설명한다.
• db file sequential read와 db file scattered read의 이름을 올바르게 해석한다.
• Cache Prefetch와 TABLE ACCESS BY INDEX ROWID BATCHED를 구분한다.
• Direct Path Read의 Buffer Cache 우회 특성을 설명한다.
• 공식 Buffer Cache Hit Ratio 계산에 Cache 전용 통계를 사용하는 이유를 설명한다.
• SQL별 실행당·행당 Buffer Gets와 Disk Reads를 계산한다.
• I/O Block 수·Request 수·Wait Time을 함께 분석한다.

1. Oracle의 기본 I/O 단위는 Block이다

테이블에는 여러 Row가 저장되지만 Oracle이 Buffer Cache와 Data File 사이에서 주로 읽고 관리하는 기본 단위는 Database Block입니다.

예를 들어 한 Block에 주문 Row 80개가 저장되어 있다면 주문 한 건만 필요해도 해당 Block Image를 읽어야 합니다.

SQL A
  결과: 10행
  Logical Block 방문: 20
  → 행당 2 Block

SQL B
  결과: 10행
  Logical Block 방문: 20,000
  → 행당 2,000 Block

두 SQL은 같은 결과 Row 수를 반환하지만 내부 작업량은 크게 다릅니다. 따라서 SQL 성능은 반환 행 수뿐 아니라 다음 값과 함께 봅니다.

• Executions
• Rows Processed
• Buffer Gets
• Disk Reads
• CPU Time
• Elapsed Time과 I/O Wait Time

2. Logical I/O

2.1 consistent gets

consistent gets는 Query 시점의 Read Consistency에 맞는 Consistent Read Block Image를 요청한 횟수입니다.

대표적으로 다음 상황에서 발생합니다.

• 일반 SELECT
• DML이 조건을 찾기 위해 수행하는 Consistent Read
• Undo를 적용해 과거 시점의 Block Image를 구성하는 조회

Consistent Read가 항상 Undo Block을 읽는다는 뜻은 아닙니다. 현재 Block Image가 Query SCN에 적합하면 그대로 사용할 수 있고, 필요할 때 Undo를 적용해 CR Image를 구성합니다.

2.2 db block gets

db block gets는 최신 상태의 Current Mode Block을 요청한 횟수입니다.

대표적으로 다음 상황에서 증가할 수 있습니다.

• INSERT·UPDATE·DELETE의 Block 변경
• Segment·Index 관리
• 일부 내부 처리

2.3 session logical reads

Oracle의 session logical reads는 다음 통계의 합계입니다.

session logical reads
  = db block gets
  + consistent gets

Oracle 공식 정의에서는 Buffer Cache뿐 아니라 Process Private Memory에 있는 Database Block 접근도 이 통계에 포함될 수 있습니다. 따라서 Logical I/O를 “Buffer Cache Hit 횟수”로만 한정하면 Direct Path·Private Buffer 경로를 충분히 설명하지 못할 수 있습니다.

SQL Trace·TKPROF에서는 일반적으로 다음 이름으로 표시됩니다.

query   = consistent gets
current = db block gets

Logical I/O
  = query + current

실행계획 Runtime Statistics의 Buffers도 CR·Current Buffer Get을 합친 논리 작업량으로 해석합니다. 상위 Operation 통계에 하위 작업이 반영될 수 있으므로 Plan의 모든 Buffers를 더하지 않습니다.

3. Physical I/O 통계의 단위와 범위

Storage 계층에서 Block을 읽는 작업은 여러 통계로 관찰합니다.

3.1 Block 수와 I/O Request 수

한 번의 Multiblock Read Request로 16 Block을 읽었다고 가정합니다.

Physical Read I/O Requests = 1
Physical Reads             = 16 Blocks

따라서 physical reads=16을 “OS Read 호출 16회”로 해석하지 않습니다.

평균 Application Read Request 크기는 다음처럼 계산할 수 있습니다.

평균 Block / Request
  = physical reads / physical read IO requests

단, 통계 수집 구간과 대상 활동의 범위를 동일하게 맞춰야 합니다.

3.2 Physical Read의 Storage 의미

Oracle의 physical reads는 공식적으로 Disk에서 읽은 Block 수로 정의됩니다. 다만 실제 응답은 OS File Cache, Storage Controller Cache, Flash Cache, Exadata Smart Flash Cache 등 하위 계층에서 제공될 수 있습니다.

따라서 Oracle 통계의 Physical Read를 반드시 회전식 Disk Head가 움직인 횟수로 해석하지 않습니다. Oracle Process가 Buffer Cache 밖의 Storage I/O 경로를 요청했다는 관점에서 봅니다.

4. 일반 Cache Read와 Direct Path Read

4.1 Buffer Cache Read

일반적인 Buffer Cache Read에서는 Cache Miss가 발생하면 Storage에서 Block을 읽어 Buffer Cache에 적재한 뒤 사용합니다.

Block 요청
  → Cache 검색
  → Miss
  → physical reads cache 증가 가능
  → Buffer Cache에 적재
  → Consistent 또는 Current Get

4.2 Direct Path Read

Direct Path Read는 일반 Buffer Cache를 우회하여 읽은 Block을 Process Memory로 전달할 수 있습니다.

대표 사례는 다음과 같습니다.

• Parallel Query
• 큰 Segment의 일부 Serial Full Scan
• Temporary Tablespace의 Sort·Hash Spill
• 일부 LOB·대량 읽기 경로

대표 통계와 Wait Event는 다음과 같습니다.

physical reads direct
physical reads direct temporary tablespace

direct path read
direct path read temp

Direct Path는 Buffer Cache Pollution을 줄이고 대량 전송을 효율화할 수 있으므로 그 자체가 오류는 아닙니다. 업무상 필요한 데이터량보다 과도한 Block·Byte를 읽거나 I/O Wait가 응답시간의 큰 비중을 차지할 때 개선 대상으로 봅니다.

5. Single Block Read

Single Block Read는 한 I/O 요청에서 일반적으로 한 Database Block을 읽는 방식입니다.

대표 Access Pattern은 다음과 같습니다.

• B-Tree Index Root·Branch·Leaf 탐색
• Index에서 얻은 ROWID를 이용한 Table Block 접근
• 특정 Block을 선택적으로 읽는 작업

대표 Wait Event는 db file sequential read입니다. 이 Event의 blocks Parameter는 일반적으로 1입니다.

db file sequential read
  → 일상적 의미의 “순차 스캔”이 아님
  → 일반적으로 Single Block Read 문맥

다음 Plan에서 자주 나타날 수 있습니다.

TABLE ACCESS BY INDEX ROWID
  INDEX RANGE SCAN

인덱스를 사용했더라도 후보 ROWID가 많고 Table Clustering이 나쁘면 Single Block Logical·Physical I/O가 크게 증가할 수 있습니다.

6. Multiblock Read

Multiblock Read는 한 I/O 요청으로 여러 Database Block을 읽는 방식입니다.

대표 Access Pattern은 다음과 같습니다.

• Table Full Scan
• Index Fast Full Scan
• 대량 Segment Scan

Buffer Cache 경로에서 대표적으로 db file scattered read가 관찰될 수 있습니다. Oracle 공식 설명에서는 db file sequential read와 유사하지만 여러 Block을 읽는 Event로 정의합니다.

db file scattered read
  → 여러 Block을 한 Request로 읽음
  → Buffer Cache의 여러 Buffer에 배치될 수 있음

scattered를 “Data File의 무작위 Block만 읽는다”는 뜻으로 해석하지 않습니다.

6.1 DB_FILE_MULTIBLOCK_READ_COUNT

DB_FILE_MULTIBLOCK_READ_COUNT는 Sequential Scan에서 한 I/O Operation으로 읽을 수 있는 최대 Block 수를 지정합니다.

실제 Read Block 수는 다음 이유로 더 작을 수 있습니다.

• Segment·Extent 경계
• 요청 범위의 남은 Block 수
• 일부 Block의 Cache 존재 여부
• Platform·Storage의 효율적인 I/O 크기
• Optimizer와 실행 엔진의 선택

따라서 Parameter 값과 매 I/O Request의 실제 Block 수가 항상 같다고 단정하지 않습니다. 기본값은 Platform이 효율적으로 수행할 수 있는 최대 I/O 크기에 따라 결정될 수 있습니다.

7. Prefetch와 ROWID Batching

7.1 Cache Prefetch

Oracle은 앞으로 필요할 Block을 미리 읽을 수 있습니다. 관련 Instance 통계로 physical reads cache prefetch를 확인할 수 있습니다.

Prefetch는 연속 또는 비연속 Block을 사전에 읽어 Cache에 준비함으로써 Foreground Process의 개별 Wait를 줄이는 데 도움을 줄 수 있습니다.

7.2 TABLE ACCESS BY INDEX ROWID BATCHED

다음 Plan Operation은 Index에서 여러 ROWID를 모은 뒤 Table Block 순서에 가깝게 접근하여 같은 Block의 반복 방문을 줄이려는 최적화입니다.

TABLE ACCESS BY INDEX ROWID BATCHED
  INDEX RANGE SCAN

Index에서 ROWID Batch 수집
  → ROWID의 Table Block 위치 고려
  → Block 순서에 가깝게 Table Row 접근
  → Clustering 개선과 반복 Block 접근 감소 시도

BATCHED가 표시됐다고 물리 I/O가 반드시 하나의 Multiblock Request로 수행된다고 단정하지 않습니다. ROWID Reordering·Cache Prefetch·Async I/O·Cache Hit가 결합될 수 있으므로 다음 정보를 함께 봅니다.

• 실행계획 Operation
• Buffers, Reads
• Physical Read Block 수와 Request 수
• Wait Event와 Wait Time
• Table Clustering Factor와 실제 Row 수

8. Wait Event를 읽는 기본 기준

Wait Event 이름과 Count만으로 SQL의 전체 I/O를 확정하지 않습니다.

현대 Oracle은 Async I/O, Prefetch와 Cache Hit를 사용할 수 있으므로 다음 통계를 함께 봅니다.

• Wait Count와 Time Waited
• Physical Read Block 수
• Physical Read Request 수
• Logical I/O
• SQL CPU·Elapsed Time
• Access Path와 반환 행 수

예를 들어 Single Block Read Request가 Cache Hit라면 db file sequential read Wait는 발생하지 않지만 Logical I/O는 발생할 수 있습니다.

9. Buffer Cache Hit Ratio

9.1 공식 Cache 전용 근사식

Oracle 공식 Tuning Guide는 Buffer Cache Hit Ratio를 다음 Cache 전용 통계로 계산합니다.

BCHR
  = 1 - physical reads cache
        / (consistent gets from cache + db block gets from cache)

누적 Instance 값 자체가 아니라 동일한 두 시점의 Delta를 사용합니다.

구간 Hit Ratio
  = 1 - Δphysical reads cache
        / (Δconsistent gets from cache + Δdb block gets from cache)

전체 physical reads에는 Direct Path와 Process Private Buffer Read가 포함될 수 있으므로 Buffer Cache Hit Ratio 계산에는 Cache 전용 통계를 사용하는 것이 더 정확합니다.

9.2 Ratio는 진단 신호일 뿐이다

Oracle 공식 성능 가이드는 Hit Ratio를 병목의 확정 지표가 아니라 Indicator로 사용하도록 안내합니다.

Logical Reads       = 10,000,000
Physical Reads Cache=     10,000
BCHR                ≈ 99.9%

Ratio는 높지만 SQL이 같은 Block을 불필요하게 반복 방문하면 CPU와 Buffer 관리 비용이 매우 클 수 있습니다.

반대로 한 번만 읽는 대규모 Batch Scan이나 Direct Path Read는 Cache 재사용률이 낮아도 합리적일 수 있습니다.

따라서 다음 SQL별 지표를 먼저 봅니다.

• Buffer Gets / Execution
• Buffer Gets / Row
• Disk Reads / Execution
• Physical I/O Requests / Execution
• CPU·Elapsed·I/O Wait / Execution

10. SQL별 작업량 계산

다음 SQL 통계를 가정합니다.

Executions     = 100
Buffer Gets    = 500,000
Disk Reads     = 20,000
Rows Processed = 10,000

Buffer Gets / Execution
  = 500,000 ÷ 100
  = 5,000 Blocks

Buffer Gets / Row
  = 500,000 ÷ 10,000
  = 50 Blocks

Disk Reads / Execution
  = 20,000 ÷ 100
  = 200 Blocks

여기에 I/O Request 통계가 2,500회라면 다음 계산도 가능합니다.

평균 Block / Physical Read Request
  = 20,000 ÷ 2,500
  = 8 Blocks / Request

단, Disk Reads와 I/O Request가 같은 SQL·Session·측정 구간을 나타내는지 확인해야 합니다.

10.1 Rows Processed 주의

SELECT 통계는 Client가 실제로 Fetch한 범위의 영향을 받을 수 있습니다. DML은 영향받은 Row 수의 성격을 가집니다. 변경 전후 비교에서는 Fetch Row 수와 실행 조건을 통일합니다.

11. Access Path와 I/O를 연결하는 사례

고객의 최근 주문 20건을 조회한다고 가정합니다.

SELECT /*+ GATHER_PLAN_STATISTICS */
       order_id,
       order_date,
       amount
FROM   orders
WHERE  customer_id = :customer_id
ORDER BY order_date DESC
FETCH FIRST 20 ROWS ONLY;

가상의 Runtime Statistics입니다.

--------------------------------------------------------------------------------
| Id | Operation                          | Starts | A-Rows | Buffers | Reads |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                   |      1 |     20 |   8,200 |   600 |
|  1 |  SORT ORDER BY STOPKEY             |      1 |     20 |   8,200 |   600 |
|  2 |   TABLE ACCESS BY INDEX ROWID       |      1 |  5,000 |   8,200 |   600 |
|  3 |    INDEX RANGE SCAN                 |      1 |  5,000 |     350 |    10 |
--------------------------------------------------------------------------------

해석합니다.

1. Index가 customer_id 조건으로 5,000개 후보 ROWID를 생산했습니다.
2. Table Access가 5,000 Row를 읽었습니다.
3. Sort Stopkey가 정렬 후 20 Row만 반환했습니다.
4. 최종 20 Row를 위해 8,200 Logical Block 요청이 발생했습니다.
5. Physical Read가 600 Block이지만 Cache가 Warm해져도 8,200 Buffers의 구조적 작업량은 남을 수 있습니다.
6. Index 단계보다 Table Access 단계에 Logical I/O가 집중되었습니다.

후속 확인
  → customer_id + order_date 정렬 순서를 지원하는 Index 가능성
  → Table Clustering과 Covering 여부
  → 한 고객의 실제 주문 분포
  → Top-N Stop을 더 이른 단계에서 수행할 수 있는지

핵심은 Physical Read 600만 보는 것이 아니라 20행을 위해 5,000행과 8,200 Block을 처리한 구조를 보는 것입니다.

12. 진단 패턴

이 표는 원인을 확정하는 규칙이 아니라 조사 순서를 정하는 출발점입니다.

13. 실전 진단 순서

1. SQL_ID·Child·Bind·Fetch 범위를 확정한다.
2. Executions·Rows Processed를 확인한다.
3. Buffer Gets·Disk Reads를 실행당·행당으로 계산한다.
4. Physical Read Block·Request·Byte 통계의 범위를 맞춘다.
5. Plan에서 후보 행이 급증하는 최초 Operation을 찾는다.
6. Single·Multiblock·Prefetch·Direct Path 경로를 확인한다.
7. Wait Event의 Count보다 Time Waited와 DB Time 비중을 본다.
8. 같은 Bind·데이터량·Fetch·Cache 조건으로 변경 전후를 비교한다.
9. Hit Ratio보다 읽는 행·Block·Request와 응답시간 감소를 검증한다.

좋은 분석 문장은 다음처럼 작성합니다.

이 SQL은 실행당 평균 5,000 Buffer Gets와 200 Disk Reads를 사용하며 결과 한 행당 50 Buffer Gets가 발생한다. 실행계획에서 Index가 5,000개 후보 ROWID를 만들고 Table Access가 모두 방문한 뒤 Sort Stopkey가 20행만 반환하므로, Physical Read보다 후보 행과 Table Logical I/O를 줄이는 것이 우선이다.

14. 자주 혼동하는 판단

15. 핵심 정리

Logical I/O
  = consistent gets + db block gets
  = CR·Current Block의 논리 접근

Physical I/O
  Block 수    → physical reads
  Request 수  → physical read IO requests
  Byte 수     → physical read bytes

Cache Read
  → physical reads cache

Direct Read
  → physical reads direct
  → Buffer Cache 우회 가능

Single Block
  → db file sequential read

Multiblock
  → db file scattered read
  → DB_FILE_MULTIBLOCK_READ_COUNT는 최대값

Prefetch·ROWID Batching
  → 앞으로 필요한 Block·ROWID 접근 최적화
  → 물리 Request 구조는 통계로 검증

BCHR
  → Cache 전용 Delta 통계 사용
  → 병목 확정값이 아닌 참고 Indicator

SQL 튜닝
  → Buffer Gets/Execution
  → Buffer Gets/Row
  → Blocks/Request
  → CPU·I/O Wait와 응답시간

I/O 튜닝의 목표는 Cache Hit Ratio를 특정 숫자로 만드는 것이 아닙니다. 업무 결과를 만들기 위해 읽는 후보 Row, Logical Block, Physical Block, I/O Request와 대기시간을 필요한 수준까지 줄이는 것입니다.