Array Fetch 실측: Fetch Call·Rows per Fetch·Network·메모리

핵심 요약

Array Fetch는 SELECT 결과를 한 행씩 가져오지 않고 여러 행을 한 묶음으로 Client에 전달해 Fetch Call과 Oracle Net 왕복을 줄이는 방식이다.

Result Row 수 = R
Fetch Size = F

이론적 Data Batch 수
≈ CEIL(R / F)

예를 들어 10,050행을 Fetch Size 100으로 모두 소비하면 이론상 Data Batch는 101개다. 그러나 이 값은 결과 행을 나누는 계산값이지 실제 JDBC Method 호출 수, SQL Trace의 Fetch Count, Network Packet 수, Oracle Net Round Trip 수를 정확히 보장하는 공식은 아니다.

실제 관찰값에는 다음 요소가 개입한다.

• Execute 단계의 초기 Row Prefetch
• Driver의 Row Prefetch·Fetch Size 설정 계층
• 마지막 End-of-Fetch 확인
• 일부 행만 읽고 ResultSet을 닫는 Early Close
• LOB Locator와 LOB Data Prefetch
• Oracle JDBC 26ai Row Prefetch Auto Tuning
• Network Packet 분할·병합
• 동일 SQL의 서로 다른 Session·Fetch 설정 누적

따라서 Fetch Size의 목표는 가장 큰 값을 사용하는 것이 아니라 다음 균형을 찾는 것이다.

Round Trip·Fetch Call 감소
↔ Client Memory·전송 Byte·GC·첫 Batch 지연·불필요 Prefetch

Fetch Size, Page Size, JDBC DML Batch Size, PL/SQL FORALL, Commit Unit은 모두 묶음 크기처럼 보이지만 처리 방향과 책임이 서로 다르다.

학습 목표

• 이론적 Data Batch 수와 실제 Fetch·Round Trip을 구분한다.
• SQL*Plus ARRAYSIZE, JDBC Row Prefetch·Fetch Size, PL/SQL BULK COLLECT LIMIT의 계층을 구분한다.
• Connection·Statement·ResultSet의 Fetch 설정 우선순위와 적용 시점을 설명한다.
• Oracle JDBC 26ai Row Prefetch Auto Tuning 조건과 범위를 설명한다.
• 평균 Row 폭, Fetch Size, 동시 ResultSet 수로 Memory 위험을 추정한다.
• Row Prefetch와 LOB Prefetch의 곱셈 효과를 설명한다.
• V$SQLSTATS, V$SESSTAT, SQL Trace·TKPROF를 이용해 Fetch를 실측한다.
• 일부 Fetch·혼합 실행·누적 통계가 평균값을 왜곡하는 이유를 설명한다.
• Query 유형별 Fetch Size Test Matrix를 설계한다.
• Fetch Size, DML Batch Size, Commit Unit을 독립적으로 설계한다.

1. Row-by-Row Fetch의 비용

10,000행을 한 행씩 가져온다고 가정한다.

Fetch Size = 1
Result Row = 10,000
이론적 Data Batch ≈ 10,000

각 Fetch에는 다음 고정비가 반복될 수 있다.

• Client·Driver Method 호출
• Database Protocol 처리
• Oracle Net Message 교환
• Server Process와 Client 간 제어 전환
• ResultSet 상태 갱신
• Column 변환과 Buffer 복사

Network 왕복 지연이 1ms라고 단순 가정해도 10,000회의 왕복은 지연시간만으로 큰 비용이 된다. 실제 시스템에서는 Database 처리시간, Packet 처리, Client 변환, Thread Scheduling이 추가된다.

Array Fetch는 이 고정비를 여러 행에 분산한다.

Fetch Size 1
→ 행마다 통신·호출 고정비 반복

Fetch Size 100
→ 최대 100행에 한 번의 Data Fetch 묶음

단, SQL 자체가 느리거나 첫 행 전에 Sort·Hash Build가 필요한 경우 Fetch Size만으로 Database 실행비용이 해결되지는 않는다.

2. 이론적 Batch 계산과 실제 관찰값

2.1 기본 계산

R = 10,000
F = 100

CEIL(10,000 / 100) = 100

R = 10,050
F = 100

CEIL(10,050 / 100) = 101

마지막 Batch에는 50행만 포함된다.

2.2 공식이 의미하는 범위

CEIL(R/F)는 다음 조건의 이론적 Data 묶음 수다.

• 결과를 끝까지 소비함
• Fetch Size가 실행 중 일정함
• 단순 Row Fetch를 가정함
• LOB·Stream의 별도 전송을 제외함
• Driver 내부 Prefetch와 EOF Protocol을 단순화함

실제 SQL Trace의 Fetch Count와 Oracle Net Round Trip은 다음 이유로 달라질 수 있다.

• 첫 Row Batch가 Execute와 결합될 수 있음
• 끝을 확인하는 Fetch가 추가될 수 있음
• Application이 일부 행만 소비하고 Close할 수 있음
• ResultSet에서 Fetch Size를 변경할 수 있음
• Driver Auto Tuning으로 실제 Prefetch 크기가 변할 수 있음
• LOB Data를 읽을 때 추가 왕복이 발생할 수 있음
• 여러 Message가 하나의 Packet에 실리거나 한 Message가 여러 Packet으로 분할될 수 있음

따라서 공식은 Test의 예상 규모를 정하는 출발점이고 최종 판단은 Runtime Delta로 한다.

3. 설정 계층을 구분한다

3.1 SQL*Plus ARRAYSIZE

SET ARRAYSIZE 100

SQLPlus가 Database에서 한 번에 Fetch하는 행 수를 조정한다. Oracle SQLPlus 26ai 문서의 유효 범위는 1~5,000이다. 다만 최근 SQL*Plus·Database에서는 Network Packet 충전 방식에 따라 ARRAYSIZE 변화 효과가 작을 수도 있으므로 실측이 필요하다.

3.2 Oracle JDBC Connection Row Prefetch

OracleConnection conn = ...;
conn.setDefaultRowPrefetch(100);

Connection Default Row Prefetch는 이후 해당 Connection에서 생성되는 Statement의 기본값에 영향을 준다. 이미 생성된 Statement·ResultSet을 소급 변경한다고 가정하지 않는다.

3.3 JDBC Statement Fetch Size

PreparedStatement ps = conn.prepareStatement(sql);
ps.setFetchSize(100);
ResultSet rs = ps.executeQuery();

Statement의 Fetch Size는 그 Statement로 이후 실행하는 Query에 적용되고 생성된 ResultSet의 초기 Fetch Size로 전달된다.

3.4 JDBC ResultSet Fetch Size

ResultSet rs = ps.executeQuery();
rs.setFetchSize(50);

ResultSet에서 설정한 값은 이미 생성된 ResultSet의 이후 Database Trip에 영향을 줄 수 있다. 반대로 ResultSet을 만든 뒤 Statement의 Fetch Size를 바꿔도 기존 ResultSet에는 적용되지 않는다.

3.5 PL/SQL BULK COLLECT LIMIT

FETCH c_orders
BULK COLLECT INTO l_orders
LIMIT 1000;

SQL Engine 결과를 PL/SQL Collection으로 옮기는 묶음 크기다. Java Client의 JDBC Fetch Size와 적용 경계가 다르다.

3.6 묶음 크기 비교

4. Oracle JDBC 26ai Row Prefetch Auto Tuning

Oracle JDBC Thin 26ai는 기본 Row Prefetch를 변경하지 않은 경우 자동 조정 기능을 사용할 수 있다.

기본 Row Prefetch = 10
첫 3회의 Prefetch
→ 평균 Row 크기 관찰

4번째 Fetch 이후
→ 계산된 Row Prefetch 적용 가능

공식 문서의 핵심 조건은 다음과 같다.

• oracle.jdbc.fetchSizeTuning 기본값은 8
• 0으로 설정하면 Auto Tuning 비활성화
• 자동 계산 범위는 최소 4행, 최대 250행
• 애플리케이션이 기본 Prefetch 값을 변경하면 Auto Tuning 비활성화
• Statement.setFetchSize()는 해당 Statement의 Auto Tuning보다 우선하며 Auto Tuning을 비활성화
• oracle.jdbc.defaultRowPrefetch는 Connection에서 생성되는 Statement에 영향을 줌

기본값 유지
→ Driver가 Row 폭을 관찰해 조정 가능

Statement.setFetchSize(N)
→ 해당 Statement는 N을 사용
→ 해당 Statement Auto Tuning 비활성화

Auto Tuning이 항상 최적이라는 뜻도, 명시적 Fetch Size가 항상 더 빠르다는 뜻도 아니다. 운영 Driver 버전, Query별 Row 폭, Page 소비 방식, Network 지연, Heap·GC를 함께 Test한다.

5. Row 폭과 Fetch Buffer 추정

Fetch Size는 행 수만으로 결정하지 않는다.

순수 Row Data 추정치
≈ 평균 Row Byte × Fetch Size

평균 Row 500Byte, Fetch Size 1,000이면 다음과 같다.

500 × 1,000
= 500,000Byte
≈ 488KiB

이는 순수 Row Data의 단순 추정치다. 실제 Client Memory에는 다음이 추가된다.

• Driver Buffer와 내부 Array
• Character Set 변환
• Column Metadata와 NULL 표시
• Java Object·Wrapper·String
• ORM Entity와 Persistence Context
• JSON·XML 직렬화 Buffer
• Application Collection
• LOB Locator와 LOB Prefetch Data

동시에 열린 ResultSet 수를 고려하면 대략적인 위험이 커진다.

동시 Fetch Data 추정
≈ 평균 Row Byte × Fetch Size × 동시 활성 ResultSet 수

예를 들어 순수 데이터 488KiB 수준의 Fetch Buffer가 100개 동시에 활성화되면 순수 Row Data만 약 47.7MiB다. 실제 Heap은 Object·Driver·Serialization Overhead로 더 커질 수 있다.

6. 좁은 Row·넓은 Row·목록·상세를 구분한다

6.1 좁은 Row

SELECT order_id,
       status
FROM   orders;

한 행이 작고 결과를 끝까지 소비하는 Report라면 비교적 큰 Fetch Size의 Round Trip 감소 효과를 기대할 수 있다.

6.2 넓은 Row

SELECT *
FROM   customer_document;

다음 Column이 포함되면 Row 폭이 급격히 커질 수 있다.

• 긴 VARCHAR2
• JSON·XML
• 다수의 Number·Date·Timestamp
• Inline Binary Data
• LOB Locator·Prefetch Data

같은 Fetch Size라도 Heap, Network Byte, 첫 Batch 준비시간, Connection Hold Time이 훨씬 커질 수 있다.

6.3 설계 원칙

• SELECT * 대신 필요한 Column만 선택
• 목록과 상세 Query 분리
• 목록에서 LOB·긴 본문 제외
• Page Size와 응답 Byte 상한 설정
• Row 폭 Profile별 Fetch Size 운영
• 단건·목록·Report·LOB Query를 같은 Global 값으로 강제하지 않음

7. LOB Prefetch와 메모리 곱셈 효과

Oracle JDBC는 LOB Locator를 Fetch할 때 LOB 길이·Chunk 정보와 LOB Data 일부를 함께 Prefetch해 추가 Round Trip을 줄일 수 있다. Oracle JDBC 26ai의 기본 LOB Prefetch Size는 32,768이다.

일반 Row Fetch
→ Row와 LOB Locator 수신
→ 설정에 따라 LOB Metadata·앞부분 Data Prefetch
→ 나머지 LOB는 필요 시 추가 Read

LOB Prefetch는 작은 LOB에서 효과가 크고 매우 큰 LOB에서는 상대 이득이 줄 수 있다.

큰 Row Prefetch, 큰 LOB Prefetch, 여러 LOB Column이 결합되면 Memory가 크게 증가한다.

위험 요인
≈ Row Prefetch 수
 × LOB Column 수
 × LOB Prefetch Size
 × 동시 활성 ResultSet 수

정확한 Allocation 공식은 아니지만 Capacity Risk를 찾는 유용한 관점이다.

개선 기준

• 목록 Query에서 LOB Column 제외
• 실제로 읽는 LOB만 상세 Query로 분리
• 작은 LOB 전체 소비율이 높을 때 Prefetch 검토
• 큰 LOB·부분 읽기에서는 LOB Prefetch를 별도 Test
• Row Fetch Size와 LOB Prefetch Size를 동시에 최대화하지 않음
• Heap·GC·Network Byte·Connection Hold Time 측정

8. Page Size와 Fetch Size

Page Size는 사용자에게 반환할 행 수이고 Fetch Size는 Driver가 Database에서 가져오는 묶음이다.

Page Size = 20
Fetch Size = 100

Application이 20행만 읽고 Close
→ 최대 80행 수준이 불필요하게 Prefetch됐을 가능성

실제 초과 Prefetch 수는 Driver·초기 Prefetch·Protocol에 따라 달라질 수 있으므로 정확히 80행이라고 단정하지 않는다. 그러나 Page Size보다 훨씬 큰 Fetch Size는 다음 비용을 만들 수 있다.

• 사용하지 않을 Row 전송
• 불필요한 Client Buffer와 Object 생성
• LOB Prefetch 낭비
• 첫 Batch 수신량 증가
• GC와 Connection 점유 증가

반대로 Fetch Size가 Page Size보다 작으면 한 Page를 구성하기 위해 여러 Database Trip이 필요할 수 있다.

따라서 Page Query는 Page Size와 비슷한 수준에서 시작해 Row 폭·Network 지연·Driver Auto Tuning을 반영해 조정하고, 전체 Report는 별도 Profile을 사용한다.

9. V$SQLSTATS로 Fetch 실측

SELECT sql_id,
       executions,
       fetches,
       rows_processed,
       end_of_fetch_count,
       ROUND(fetches / NULLIF(executions, 0), 2)
         AS fetches_per_execution,
       ROUND(rows_processed / NULLIF(fetches, 0), 2)
         AS observed_rows_per_fetch,
       ROUND(rows_processed / NULLIF(executions, 0), 2)
         AS rows_per_execution
FROM   v$sqlstats
WHERE  sql_id = :sql_id;

9.1 Column 의미

• EXECUTIONS: Cursor 실행 횟수
• FETCHES: SQL과 연관된 Fetch 횟수
• ROWS_PROCESSED: SELECT가 반환한 누적 행 수
• END_OF_FETCH_COUNT: Cursor를 끝까지 실행한 횟수

9.2 평균값 해석

EXECUTIONS = 100
FETCHES = 10,000
ROWS_PROCESSED = 1,000,000

Fetches per Execution = 100
Observed Rows per Fetch = 100
Rows per Execution = 10,000

ROWS_PROCESSED / FETCHES는 관찰 구간의 평균이지 설정한 Fetch Size를 그대로 보여 주는 값이 아니다.

왜곡 요인:

• Fetch Size가 다른 Session·실행 혼합
• 마지막 Batch가 작음
• 일부 Fetch 후 Close
• 실행 오류·Cancel·재실행
• Cursor가 Reload되면서 통계 범위 변경
• 동일 SQL_ID에 다양한 Bind 결과량 혼합
• Driver Auto Tuning으로 실행 중 크기 변화

가능하면 AWR Snapshot 사이 Delta 또는 Test 전후 Session·SQL Delta를 사용한다.

DELTA_FETCH_COUNT
DELTA_ROWS_PROCESSED
DELTA_EXECUTION_COUNT
DELTA_END_OF_FETCH_COUNT
DELTA_ELAPSED_TIME
DELTA_CPU_TIME

10. END_OF_FETCH_COUNT로 전체 소비를 구분한다

END_OF_FETCH_COUNT는 Cursor가 끝까지 실행된 횟수다. 다음 경우에는 증가하지 않을 수 있다.

• 첫 Page·일부 행만 Fetch하고 Close
• 실행 중 오류 발생
• Statement Cancel
• 같은 Cursor를 끝까지 읽기 전에 재실행

EXECUTIONS = 1,000
END_OF_FETCH_COUNT = 100

이 값만 보고 900회가 모두 성공적인 부분범위 처리였다고 결론 내리면 안 된다. 오류·Timeout·Cancel·재실행도 함께 확인한다.

부분범위 처리 Test에서는 다음을 분리한다.

• Page Size만 소비하고 Close
• 결과 전체 소비
• Timeout·Cancel
• 빈 결과
• 단건 결과

11. Session Network 통계

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 (
         'SQL*Net roundtrips to/from client',
         'bytes sent via SQL*Net to client',
         'bytes received via SQL*Net from client',
         'user calls'
       )
ORDER BY n.name;

해석

• SQL*Net roundtrips to/from client: Client와 교환한 Oracle Net Message 수
• bytes sent via SQL*Net to client: Database Foreground가 Client로 보낸 누적 Byte
• bytes received via SQL*Net from client: Client에서 받은 누적 Byte
• user calls: Client가 Database에 요청한 User Call 누적

Fetch Size 전후에 다음을 Delta로 비교한다.

Round Trips
Fetches
전송 Byte
Time to First Row·First Page
Time to Last Row
Client Heap·GC
Connection Hold Time
CPU·Elapsed

Round Trip은 감소했지만 전송 Byte와 Heap·GC가 급증하면 Fetch Size가 과도할 수 있다.

SQL*Net message from client 대기시간은 Server가 Client의 다음 Message를 기다린 시간이다. 사용자 Think Time·Client CPU·Middle Tier 처리도 포함될 수 있으므로 Network Latency 하나로 단정하지 않는다.

12. SQL Trace·TKPROF로 Fetch 단계 확인

SQL Trace는 Parse·Execute·Fetch Count, CPU·Elapsed, Logical·Physical Read, 처리 Row를 분리한다.

call     count   cpu   elapsed   disk   query   current   rows
Parse
Execute
Fetch

SELECT에서는 다음을 확인한다.

• Fetch count
• Fetch 단계의 rows
• Fetch CPU·Elapsed
• query·current
• Fetch 단계 Physical Read
• Cursor가 끝까지 Close됐는지
• Trace 구간과 Client Test 구간이 일치하는지

TKPROF의 Fetch Count는 Database Fetch Call 통계다. Network Packet 수나 Application의 ResultSet.next() 호출 수와 동일하다고 단정하지 않는다.

선택적 Trace는 DBMS_MONITOR·DBMS_SESSION으로 Test Session·Client Identifier·Module에 한정해 수집한다. 전체 Instance Trace는 Overhead와 Trace File 증가 위험이 있다.

13. SQL*Plus ARRAYSIZE 실험

SET ARRAYSIZE 10
SELECT ...;

SET ARRAYSIZE 100
SELECT ...;

SET ARRAYSIZE 1000
SELECT ...;

SQL*Plus ARRAYSIZE의 유효 범위는 1~5,000이다. 그러나 값이 10배 커졌다고 Round Trip이 정확히 10분의 1이 된다고 보장할 수 없다.

• Network Packet 크기와 채움 효율
• Row 폭
• Terminal 출력·Spooling 비용
• SQL*Plus Formatting
• Database 실행비용
• Client Host 성능

을 함께 통제한다.

화면 출력 Test는 Terminal Rendering이 병목이 될 수 있으므로 SPOOL, 출력 억제, 동일한 Formatting 조건을 사용해 Database Fetch 효과와 화면 표시 비용을 분리한다.

14. Fetch Size Test Matrix

Test 조건 통제

• 같은 SQL Text·Plan
• 같은 Bind 값과 결과 행 수
• 전체 Fetch 또는 일부 Fetch 조건 고정
• 같은 Driver·JDK·Connection Property
• 같은 Network 구간
• 같은 Page Size·Serialization 방식
• Warm·Cold Cache 구분
• 같은 동시 사용자 수
• 같은 LOB Column·LOB Prefetch 설정
• Client Heap·GC·CPU 수집
• Test 전후 Session·SQL Delta 수집

평가 기준

좋은 변경
→ Round Trip·Elapsed 감소
→ Heap·GC·전송 Byte 증가가 허용 범위
→ 첫 Page·마지막 Row 모두 SLA 충족

나쁜 변경
→ Round Trip은 줄지만
→ Heap·GC·LOB 전송·Connection Hold Time 급증

단건 PK 조회는 Fetch Size를 키워도 이득이 거의 없다. 수십만 행을 끝까지 소비하는 좁은 Row Report는 큰 Fetch Size의 이득이 클 수 있다. LOB·Wide Row·짧은 Page는 작은 값이 더 안전할 수 있다.

15. Fetch Size·DML Batch·Commit Unit 분리

SELECT Fetch Size
→ Database에서 Client로 Row를 받는 묶음

JDBC DML Batch Size
→ Client에서 Database로 여러 Bind를 보내는 묶음

Commit Unit
→ 하나의 Transaction으로 확정하는 업무 범위

ETL 예시:

SELECT Fetch Size = 1,000
DML Batch Size    = 500
Commit Unit       = 10,000

각 값은 서로 독립적으로 결정한다.

• Fetch Size: 조회 Round Trip·Client Memory
• DML Batch Size: Bind 전송·Execute Call·오류 처리
• Commit Unit: 원자성·Undo·Redo·Lock·재처리 범위

Fetch Size를 1,000으로 설정했다고 1,000행마다 Commit해야 하는 것은 아니다.

16. 진단·적용 절차

1. 결과를 전부 소비하는가, 일부 Page만 소비하는가?
2. 평균·P95·최대 Row 폭은 얼마인가?
3. LOB·JSON·긴 문자열이 포함되는가?
4. Page Size와 응답 Byte 상한은 무엇인가?
5. Driver 26ai Auto Tuning이 활성화돼 있는가?
6. Connection·Statement·ResultSet 중 어디에서 값을 설정했는가?
7. FETCHES·ROWS_PROCESSED·END_OF_FETCH_COUNT Delta는 얼마인가?
8. SQL*Net Round Trip과 전송 Byte Delta는 얼마인가?
9. Client Heap·GC·Connection Hold Time은 어떻게 변했는가?
10. 단건·Page·Report·LOB Query를 별도 Profile로 나눴는가?
11. 일부 Fetch와 전체 Fetch를 분리해 Test했는가?
12. DML Batch·Commit Unit과 혼동하지 않았는가?

17. 혼동하기 쉬운 판단

18. 최종 체크리스트

• 이론적 Batch 수와 실제 Fetch·Round Trip을 구분했는가?
• Fetch Size 설정 계층과 적용 시점을 확인했는가?
• Oracle JDBC 26ai Auto Tuning 활성 여부를 확인했는가?
• 평균·최대 Row 폭을 측정했는가?
• LOB Prefetch Size와 LOB Column 수를 확인했는가?
• 동시 활성 ResultSet 수와 Pool 크기를 고려했는가?
• Page Size보다 과도한 Prefetch가 없는가?
• 일부 Fetch와 전체 Fetch를 분리했는가?
• V$SQLSTATS 누적값이 아니라 Delta를 비교했는가?
• V$SESSTAT의 Round Trip·Byte Delta를 측정했는가?
• SQL Trace Fetch Count를 Packet 수로 오해하지 않았는가?
• Time to First Page와 Time to Last Row를 모두 측정했는가?
• Client Heap·GC·Connection Hold Time을 기록했는가?
• 단건·목록·Report·LOB Query별 Profile이 있는가?
• Fetch Size·DML Batch Size·Commit Unit을 분리했는가?