조회 왕복 최소화: Fetch Size·Row Limiting·OFFSET·Keyset Pagination

핵심 요약

조회 왕복 최소화는 Fetch Size 하나를 키우는 작업이 아니다. 먼저 다음 세 값을 분리해야 한다.

전체 후보 행 수
→ Filter·Join·Sort·Access Path가 실제로 처리하는 입력량

Row Limit·Page Size
→ Database가 반환하고 화면·API가 소비해야 할 최대 행 수

Fetch Size·Row Prefetch
→ 한 번의 Database 왕복에서 Client로 전달할 행 수

일반적인 우선순위는 다음과 같다.

불필요한 후보 처리 제거
→ Server-Side Row Limiting
→ 결정적인 ORDER BY
→ 깊이에 맞는 Pagination 방식
→ Filter·Order를 지원하는 Index
→ Fetch Size 실측 조정

Fetch Size를 키우면 같은 결과를 전달하는 Fetch Call과 Oracle Net 왕복을 줄일 수 있다. 그러나 SQL이 100만 행을 읽고 정렬한 뒤 Client가 20행만 사용한다면, Fetch Size보다 SQL에서 반환 행과 하위 Row Source 처리량을 줄이는 것이 먼저다.

학습 목표

• Parse·Execute·Fetch·Close의 역할을 구분한다.
• 전체 후보 행 수, Row Limit, Page Size, Fetch Size를 구분한다.
• Oracle JDBC의 Connection·Statement·ResultSet Fetch Size 적용 관계를 설명한다.
• Oracle JDBC Thin 26ai의 Row Prefetch Auto Tuning 조건을 설명한다.
• FETCH FIRST, FETCH NEXT, WITH TIES, OFFSET의 의미와 제한을 설명한다.
• 결정적인 ORDER BY, Tie-Breaker, NULL 순서를 설계한다.
• OFFSET Pagination과 Keyset Pagination의 비용·기능 차이를 설명한다.
• READ COMMITTED의 Page 간 Snapshot 차이와 Flashback SCN 정책을 설명한다.
• V$SQLSTATS, V$SESSTAT, 실행계획 Runtime 통계로 Fetch와 처리량을 진단한다.

1. SELECT는 Execute와 Fetch로 나뉜다

SELECT 결과는 Execute 한 번에 모두 Client로 전송되는 것이 아니다.

Parse
→ Execute
→ Fetch
→ Fetch
→ ...
→ End of Fetch 또는 조기 Close

Execute는 Cursor 실행을 시작하고 Row Source를 준비한다. Client는 Fetch를 반복하면서 결과를 소비한다. Row Source가 실제로 언제 얼마만큼의 행을 생산하는지는 실행계획 Operation, Blocking Operation, Driver Prefetch, Client 소비 방식에 따라 달라질 수 있다.

따라서 조회 시간은 최소한 다음처럼 나누어 측정해야 한다.

검색 화면은 첫 Page 응답이 중요하지만, Export·Report는 마지막 행까지 소비하는 비용이 중요하다. 같은 SQL이라도 전체 Fetch 여부가 다르면 END_OF_FETCH_COUNT와 처리시간을 다르게 해석해야 한다.

2. 네 가지 크기를 혼동하지 않는다

2.1 전체 후보 행 수

Server가 Predicate를 평가하고 Join·Sort·Aggregation을 수행하기 위해 읽는 행 수다. 최종 반환이 20행이어도 하위 Row Source가 수십만 행을 읽을 수 있다.

2.2 Row Limit

SQL이 Database에서 반환할 최대 행 수다.

FETCH FIRST 20 ROWS ONLY

2.3 Page Size

화면·API Contract가 한 Page에 제공할 행 수다. Row Limit과 같게 두는 경우가 많지만, WITH TIES처럼 최종 반환 수가 Page Size보다 커질 수 있는 기능도 있다.

2.4 Fetch Size

Driver가 한 번의 Fetch 왕복에서 가져오려는 행 수다. Fetch Size는 전달 단위이지 SQL의 Filter·Join·Sort 입력량 제한이 아니다.

Page Size = 20
SQL Row Limit = 20
Fetch Size = 20 또는 합리적인 배수

위 구성은 일반적인 시작점일 뿐 고정 정답은 아니다. Row 폭, LOB, Network RTT, 동시 ResultSet 수, 사용자가 전체 결과를 소비하는지에 따라 실측한다.

3. Oracle JDBC Fetch Size 적용 계층

Oracle JDBC는 Connection의 Row Prefetch, Statement의 Fetch Size, ResultSet의 Fetch Size를 제공한다.

Connection 기본 Row Prefetch
→ 이후 생성되는 Statement의 기본값

Statement.setFetchSize(N)
→ 해당 Statement로 이후 실행되는 Query에 적용

Query 실행
→ Statement Fetch Size가 ResultSet 기본값으로 전달

ResultSet.setFetchSize(M)
→ 해당 ResultSet의 이후 Fetch 왕복에 적용

Statement가 ResultSet을 만든 뒤 Statement의 Fetch Size를 변경해도 이미 생성된 ResultSet에는 반영되지 않는다. 반면 ResultSet에서 Fetch Size를 변경하면 그 ResultSet의 이후 Database 왕복에 영향을 줄 수 있다.

Oracle JDBC Connection의 기본 Row Prefetch는 10이다. 애플리케이션이 JDBC 표준 Fetch Size를 별도로 설정하지 않으면 Connection Row Prefetch 값이 기본으로 사용될 수 있다.

String sql = """
    SELECT order_id, order_date, amount
    FROM   orders
    WHERE  customer_id = ?
    ORDER  BY order_date DESC NULLS LAST,
              order_id   DESC
    FETCH FIRST 20 ROWS ONLY
    """;

try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setLong(1, customerId);
    ps.setFetchSize(20);

    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            // 최대 20행 소비
        }
    }
}

주의

• Fetch Size는 Hint가 아니라 Driver의 결과 전달 설정이다.
• Fetch Size와 실제 Network Packet 수는 1:1이 아니다.
• SDU, TLS, Driver 내부 Buffer, Row 폭, LOB Prefetch가 실제 Packet과 Byte 수에 영향을 준다.
• Connection Pool에서는 동시에 열린 Physical Connection·ResultSet의 수까지 Memory 계산에 포함한다.

4. Oracle JDBC Thin 26ai Row Prefetch Auto Tuning

Oracle JDBC Thin 26ai는 기본 Prefetch를 애플리케이션이 변경하지 않았을 때 Row Prefetch를 자동 조정할 수 있다.

기본 Row Prefetch = 10
첫 세 번의 Prefetch에서 평균 Row 크기 관찰
네 번째 Fetch 이후 자동 조정 활성화
자동 조정 범위 = 4 ~ 250 Rows

oracle.jdbc.fetchSizeTuning Connection Property의 기본값은 8이며, 0으로 설정하면 Auto Tuning이 비활성화된다. Statement.setFetchSize()를 명시하면 해당 Statement에서는 명시값이 우선하며 Auto Tuning이 비활성화된다.

따라서 두 전략을 구분해 시험한다.

짧은 1~2 Page 조회는 네 번째 Fetch 전에 끝날 수 있으므로 Auto Tuning 효과가 거의 나타나지 않을 수 있다. 명시값을 설정했다고 해서 항상 더 빠른 것도 아니다.

5. Fetch Size가 작거나 클 때

너무 작을 때

결과 행 수 = 10,000
Fetch Size = 10
이론적 Batch 수 ≈ 1,000

• Fetch Call 증가
• Oracle Net 왕복 증가
• 높은 Network RTT에서 응답시간 증가
• Driver·Server 전환과 Protocol 처리 증가

너무 클 때

• Client·Driver Memory 증가
• 넓은 Row의 한 번 전송 Byte 증가
• 일부 행만 사용할 때 불필요한 Prefetch 가능
• 첫 Batch가 커져 First Row 응답이 늦어질 수 있음
• 동시 ResultSet이 많을 때 Heap·Direct Buffer 압박
• LOB Row Prefetch와 LOB Data Prefetch가 결합되면 Memory 사용 증가

대략적인 Memory 위험은 다음처럼 본다.

평균 Row Byte
× Row Fetch Size
× 동시 열린 ResultSet 수
+ LOB Prefetch Byte

Oracle JDBC 26ai의 LOB Data Prefetch 기본값은 32768이며, 큰 Row Prefetch·큰 LOB Prefetch·여러 LOB Column을 함께 사용하면 Memory가 크게 증가할 수 있다. LOB가 포함된 조회는 일반 Row 조회와 별도로 시험한다.

6. Server-Side Row Limiting

화면에 20행만 필요하면 SQL에서 최대 반환 행을 명시한다.

SELECT order_id,
       order_date,
       amount
FROM   orders
WHERE  customer_id = :customer_id
ORDER  BY order_date DESC NULLS LAST,
          order_id   DESC
FETCH FIRST 20 ROWS ONLY;

6.1 결정적인 정렬

Pagination과 Top-N은 모든 행의 순서를 하나로 결정해야 한다.

-- 불안정할 수 있음: 동점 행의 순서가 미결정
ORDER BY order_date DESC

-- 고유 Tie-Breaker 추가
ORDER BY order_date DESC NULLS LAST,
         order_id   DESC

ORDER_DATE가 같아도 ORDER_ID가 고유하면 정렬 순서가 결정된다. Tie-Breaker는 Token에도 포함해야 한다.

6.2 NULL 순서

Oracle의 기본 NULL 정렬은 다음과 같다.

ASC  → NULLS LAST가 기본
DESC → NULLS FIRST가 기본

Pagination Token과 비교 Predicate를 안정적으로 만들려면 NULLS FIRST 또는 NULLS LAST를 SQL에 명시하고, NULL이 가능한 Key는 다음 중 하나로 설계한다.

• NULL을 허용하지 않는 정렬 Key 사용
• NULL 영역과 Non-NULL 영역을 별도 단계로 처리
• 업무적으로 안전한 변환 표현식을 정렬·Predicate·Index에 일관되게 사용

6.3 WITH TIES

FETCH FIRST 20 ROWS WITH TIES

마지막 Row와 같은 Sort Key를 가진 행을 추가 반환하므로 20행보다 많을 수 있다. WITH TIES에는 ORDER BY가 필요하며, 고유 Tie-Breaker까지 포함하면 Tie가 사실상 사라져 ONLY와 같은 개수로 동작할 수 있다.

6.4 OFFSET 값의 규칙

• 음수 Offset은 0으로 처리된다.
• Fractional Offset의 소수 부분은 버려진다.
• Offset이 NULL이거나 결과 행 수 이상이면 0행을 반환한다.
• row_limiting_clause는 FOR UPDATE와 함께 사용할 수 없다.

7. 실행계획에서 Row Limit을 검증한다

실행계획에 STOPKEY 계열 Operation이 나타날 수 있지만 Operation 이름만으로 충분하지 않다. Runtime 통계에서 상위와 하위 Row Source를 비교한다.

최종 A-Rows = 20
하위 Index·Table A-Rows = 20에 근접
→ 조기 종료가 효과적으로 작동할 가능성

최종 A-Rows = 20
하위 Join·Sort A-Rows = 1,000,000
→ 반환은 20행이지만 입력 처리량은 큼

확인 항목:

• Predicate가 Index Access Predicate로 사용되는지
• Index Column 순서가 Filter와 ORDER BY를 함께 지원하는지
• Sort를 생략하거나 소량만 유지하는지
• Table Random Access가 Offset 위치까지 누적되는지
• ALLSTATS LAST의 A-Rows, Buffers, Reads, Starts가 예상과 맞는지

Fetch Size는 하위 Row Source의 Scan·Join·Sort를 직접 줄이지 않는다.

8. OFFSET Pagination

SELECT order_id,
       order_date,
       amount
FROM   orders
WHERE  customer_id = :customer_id
ORDER  BY order_date DESC NULLS LAST,
          order_id   DESC
OFFSET 100000 ROWS
FETCH NEXT 20 ROWS ONLY;

OFFSET은 앞쪽 행을 건너뛴 뒤 Page를 반환한다. 적절한 Index가 있어 Sort를 피하더라도 깊은 위치까지 Index Entry와 필요한 Table Row를 지나갈 수 있다.

Page 1      → Offset 0
Page 101    → Offset 2,000
Page 5,001  → Offset 100,000

OFFSET이 적합한 경우

• 임의 Page 번호 이동이 필수
• 전체 결과가 작음
• 깊은 Page 접근이 드묾
• Stateless URL이 중요함

OFFSET의 주의점

• 깊이에 따라 처리량이 누적될 수 있음
• Page 사이 DML로 중복·누락 가능
• OFFSET 값 자체를 사용자에게 무제한 허용하면 과도한 부하 유발 가능
• 최대 Offset·검색 기간·조건을 API 정책으로 제한할 필요가 있음

9. Keyset Pagination

이전 Page의 마지막 정렬 Key 이후부터 다음 Page를 찾는다.

SELECT order_id,
       order_date,
       amount
FROM   orders
WHERE  customer_id = :customer_id
AND   (
          order_date < :last_order_date
       OR (order_date = :last_order_date AND order_id < :last_order_id)
      )
ORDER  BY order_date DESC NULLS LAST,
          order_id   DESC
FETCH FIRST 20 ROWS ONLY;

두 Column이 모두 내림차순이므로 다음 Page는 마지막 Key보다 작은 방향으로 이동한다.

ORDER BY A DESC, B DESC

A < :last_a
OR (A = :last_a AND B < :last_b)

오름차순과 내림차순이 섞이면 각 Column 방향에 맞춰 사전식 비교를 설계해야 한다. 예를 들어 A DESC, B ASC이면 A가 작아지는 영역 또는 A가 같고 B가 커지는 영역으로 진행한다.

Keyset 장점

• 깊은 Page에서도 시작 Key를 Index로 찾을 수 있음
• 앞쪽 행을 매번 건너뛰는 비용 감소
• Feed·무한 스크롤에 적합
• Page 진행 비용이 비교적 일정

Keyset 제약

• 임의 Page 번호 이동이 어려움
• Token에 모든 Sort Key와 Tie-Breaker가 필요
• Sort 방향, NULL 정책, Filter 조건이 바뀌면 기존 Token을 무효화해야 함
• Token은 서명·암호화하거나 Server State와 연결해 변조를 방지
• 정렬 Key가 변경 가능한 Column이면 행 이동에 따른 중복·누락 정책 필요

Token에는 단순히 마지막 ID만 넣는 것이 아니라 다음을 포함할 수 있다.

정렬 Key 전체
+ Tie-Breaker
+ Sort Version
+ 주요 Filter Signature
+ 선택적 Snapshot SCN

10. Page 사이 데이터 변경과 Snapshot

Oracle의 READ COMMITTED에서는 각 Statement가 열린 시점의 일관된 Snapshot을 사용한다. Page 1과 Page 2가 별도 Statement면 서로 다른 SCN을 사용할 수 있다.

Page 1 조회
→ 다른 Transaction의 INSERT·UPDATE·DELETE Commit
→ Page 2 조회

발생 가능한 현상:

• 새 행이 앞쪽에 삽입돼 OFFSET Page가 밀림
• 정렬 Key가 변경된 행이 다음 Page에 중복되거나 누락
• 삭제로 Page 크기 감소
• Filter 상태 변경으로 결과 집합 자체가 변함

정책 1: 최신 상태 우선

Keyset Pagination을 사용하고 변화 가능성을 업무 규칙으로 수용한다. 실시간 Feed에 적합하다.

정책 2: 고정 Snapshot

첫 요청에서 SCN을 얻어 Token에 저장하고 각 Page에 동일한 AS OF SCN을 사용한다.

SELECT order_id,
       order_date,
       amount
FROM   orders AS OF SCN :snapshot_scn
WHERE  customer_id = :customer_id
AND   (
          order_date < :last_order_date
       OR (order_date = :last_order_date AND order_id < :last_order_id)
      )
ORDER  BY order_date DESC NULLS LAST,
          order_id   DESC
FETCH FIRST 20 ROWS ONLY;

현재 SCN은 DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER로 얻을 수 있다. Flashback Query는 해당 SCN에서 Commit된 데이터를 반환한다.

Flashback 주의점

• 필요한 Undo가 남아 있어야 한다.
• UNDO_RETENTION은 Best-Effort이므로 설정값만으로 보존을 보장하지 않는다.
• 유지시간이 길면 Undo 크기와 부하를 함께 검토한다.
• Table 구조를 변경하는 일부 DDL은 과거 Undo 기반 조회를 무효화할 수 있다.
• Flashback 권한과 보안 정책을 확인한다.

완전 고정 결과가 장시간 필요하면 결과 ID·작업 Table·검색 Snapshot Materialization 같은 별도 구조도 검토한다.

11. Fetch와 왕복을 측정한다

11.1 SQL별 누적 통계

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

해석:

• FETCHES: SQL과 연관된 Fetch 횟수
• END_OF_FETCH_COUNT: 마지막 행까지 완전 Fetch된 실행 횟수
• ROWS_PROCESSED: SQL을 위해 처리된 누적 행 수
• END_OF_FETCH_COUNT < EXECUTIONS: 일부 실행이 조기 Close·실패·재실행됐을 가능성

주의:

• 누적값에는 여러 Session과 실행이 섞일 수 있다.
• 마지막 Fetch는 Fetch Size보다 적은 행을 반환할 수 있다.
• ROWS_PROCESSED / FETCHES는 평균이지 설정된 Fetch Size 자체가 아니다.
• Cursor가 Shared Pool에서 Aging되거나 Reload되면 관찰 구간이 달라질 수 있다.
• 전후 Delta 또는 AWR의 Delta 통계를 사용해 변경 효과를 비교한다.

11.2 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',
         'user calls'
       );

SQL*Net roundtrips to/from client는 Client와 주고받은 Oracle Net Message 수를 나타낸다. Fetch 수와 정확히 같다고 단정하지 말고 같은 부하 구간의 Delta를 비교한다.

11.3 함께 수집할 지표

• API 응답시간과 ResultSet 소비시간
• 반환 Row 수와 전송 Byte
• SQL BUFFER_GETS, DISK_READS, CPU_TIME, ELAPSED_TIME
• 실행계획 A-Rows, Buffers, Reads
• Client Heap·Direct Memory와 GC
• Network RTT와 Throughput
• 완전 Fetch 비율과 조기 Close 비율

12. 진단·적용 절차

1. 화면·API가 실제로 필요한 최대 행 수를 확정한다.
2. SQL에 Row Limit을 넣고 불필요한 Column·LOB를 제거한다.
3. 고유 Tie-Breaker와 명시적 NULL 순서를 포함한 ORDER BY를 작성한다.
4. 임의 Page 이동과 깊이를 기준으로 OFFSET·Keyset을 선택한다.
5. Filter와 Order를 함께 지원하는 Index를 검토한다.
6. Runtime 실행계획에서 하위 A-Rows·Buffers를 확인한다.
7. JDBC Auto Tuning 또는 명시적 Fetch Size 중 하나를 선택한다.
8. Row 폭·LOB·동시 ResultSet을 포함해 Memory를 계산한다.
9. 전체 Fetch와 조기 Close 시나리오를 나눠 시험한다.
10. SQL Fetch 통계와 Session Network Delta를 함께 비교한다.
11. Page 사이 변경을 허용할지 Snapshot SCN을 유지할지 정한다.
12. Token Version·서명·만료와 최대 Offset 정책을 적용한다.

혼동하기 쉬운 판단