n-Tier 부분범위 처리 구현: Stateful Cursor·Stateless Pagination·Connection Pool

핵심 요약

부분범위 처리의 구현 방식은 요청 사이에 Database 실행 상태를 유지하는가에 따라 나뉜다.

Stateful Cursor
→ 한 번 연 Cursor에서 Fetch를 계속 수행
→ 같은 Database Session·Physical Connection·Cursor 실행 상태가 필요

Stateless Pagination
→ Page 요청마다 SQL을 새로 실행
→ OFFSET 또는 마지막 정렬 Key 이후 범위를 다시 탐색
→ 요청 사이에 Database Connection을 보존하지 않음

2-Tier Desktop처럼 Connection을 짧게 독점할 수 있는 환경에서는 Stateful Cursor가 자연스러울 수 있다. 반면 다수 사용자가 Physical Connection을 공유하는 n-Tier·Connection Pool 구조에서는 요청이 끝날 때 Logical Connection을 닫아 Pool에 반환하는 것이 일반적이므로, 이전 ResultSet의 위치를 다음 요청까지 유지하는 방식은 확장성과 장애 복구 측면에서 불리하다.

Statement Cache
→ 반복 Statement의 준비·Parse 관련 자원 재사용

ResultSet Fetch Position
→ 현재 실행의 행 위치와 Snapshot 상태
→ Statement Cache가 보존하는 Page 상태가 아님

따라서 n-Tier 조회는 결정적인 정렬, Keyset Token, Snapshot 정책, Resource·Transaction·Session State 정리, Timeout·Cancel·Backpressure를 하나의 설계로 묶어야 한다.

학습 목표

• Stateful Cursor와 Stateless Pagination의 상태 보존 범위를 구분한다.
• Logical Connection과 Physical Connection·Database Session의 관계를 설명한다.
• Statement Cache가 재사용하는 자원과 보존하지 않는 ResultSet 상태를 구분한다.
• OFFSET과 Keyset Pagination의 성능·기능 Trade-off를 판단한다.
• 결정적인 정렬과 안전한 Page Token을 설계한다.
• READ COMMITTED의 Page별 Snapshot과 Flashback Query의 운영 조건을 설명한다.
• Pool 반환 전 Resource·Transaction·Session State 정리 기준을 설명한다.
• UCP의 Statement Cache·Timeout·Connection Validation을 애플리케이션 책임과 구분한다.
• Query Timeout·Cancel·Network Timeout의 한계를 설명한다.
• Pool·Database·Application 지표로 부분범위 조회를 진단한다.

1. Stateful Cursor와 Stateless Pagination

1.1 Stateful Cursor

Cursor Open
→ 첫 Batch Fetch
→ 사용자 처리
→ 같은 Cursor에서 다음 Batch Fetch
→ End of Fetch 또는 Close

장점:

• 이미 처리한 앞쪽 행을 다시 탐색하지 않음
• Cursor가 현재 Fetch 위치를 유지
• 같은 SQL 실행의 Statement-Level Snapshot을 계속 사용
• OFFSET 재처리와 Page Token 해석이 불필요

비용:

• Connection·Database Session·Open Cursor를 사용자 대기시간 동안 점유
• Cursor Memory와 실행 상태 유지
• 장시간 Query SCN과 Consistent Read 유지
• 필요한 Undo가 재사용되면 ORA-01555: snapshot too old 가능
• Network 단절·Client 종료·Timeout 시 Resource 회수 절차 필요

Stateful Cursor는 통제된 Desktop, 짧은 Report Streaming, Server 내부 Batch처럼 Connection 보유시간과 동시 사용자 수가 제한된 환경에서 검토한다.

1.2 Stateless Pagination

Page 1 요청
→ Connection Borrow
→ SQL 실행·Page Fetch
→ Resource Close·Transaction 종료·Connection Return

Page 2 요청
→ Connection Borrow
→ SQL 재실행
→ Token 또는 OFFSET으로 다음 범위 탐색

장점:

• 요청 사이 Connection을 점유하지 않음
• Scale-Out Web·API에 적합
• 실패한 요청을 독립적으로 재시도하기 쉬움
• Pool의 Physical Connection을 여러 사용자에게 재사용 가능

비용:

• Page마다 Parse·Execute·Fetch가 발생
• OFFSET은 깊어질수록 앞쪽 범위 처리량이 누적될 수 있음
• 요청마다 Snapshot이 달라질 수 있음
• Keyset Token과 정렬·Filter Version 관리 필요

2. Logical Connection과 Physical Connection

UCP 같은 Connection Pool에서 애플리케이션이 받는 Connection은 일반적으로 Logical Connection Handle이다. 그 아래에는 실제 Database Session과 연결된 Physical Connection이 존재한다.

HTTP Request A
→ Logical Connection A
→ Physical Connection 3
→ Database Session 3

HTTP Request B
→ Logical Connection B
→ Physical Connection 7일 수 있음
→ Database Session 7

열린 Cursor, ResultSet 위치, Transaction, Session Parameter, Application Context는 원래 Database Session에 속한다. 다음 요청이 다른 Physical Connection을 받으면 이전 Cursor를 그대로 Fetch할 수 없다.

Connection.close()
→ Physical Socket을 매번 종료한다는 뜻이 아님
→ Logical Connection을 닫고 Physical Connection을 Pool에 반환

따라서 다음 HTTP 요청에서 이전 Cursor를 이어가려면 Connection Affinity나 장기 Checkout으로 같은 Physical Connection을 붙잡아야 한다. 이는 일반적인 Stateless Web 요청의 장점을 잃고 Pool 고갈·장애 회수 문제를 만든다.

3. Statement Cache와 Fetch 위치를 구분한다

Oracle JDBC·UCP의 Statement Cache는 Physical Connection별로 존재한다. UCP에서 maxStatements를 설정하지 않거나 0으로 설정하면 Statement Caching은 비활성화된다.

Statement Cache가 재사용하는 대표 기준:

• 동일한 SQL String
• 동일 Statement Type (PreparedStatement·CallableStatement)
• 동일 ResultSet Type (forward-only·scrollable)
• 같은 Physical Connection의 Cache

Implicit Statement Cache가 활성화된 Connection에서 Statement를 close()하면 재사용 가능한 Statement 객체와 Cursor 준비 자원이 Cache에 들어갈 수 있다.

Statement Cache가 보존하지 않는 것:

• 이전 ResultSet 객체
• 이전 ResultSet의 현재 Row 위치
• 이전 실행의 Bind 값과 업무 Page 상태
• 이전 실행의 Statement Snapshot
• 다른 Physical Connection의 Cursor 실행 위치

Statement Cache Hit
→ 새 요청에서 Statement 준비 비용 일부 절감
→ SQL은 다시 Execute됨
→ 새 ResultSet과 새 Snapshot이 생성됨

Connection 수 × Connection별 Cached Statement 수가 커지면 Database Open Cursor 한도와 Client Memory를 함께 소비할 수 있다. Cache 크기는 자주 반복하는 Distinct Statement 수와 OPEN_CURSORS, V$OPEN_CURSOR를 함께 보고 정한다.

4. OFFSET과 Keyset Pagination

4.1 OFFSET

SELECT order_id,
       order_date,
       amount
FROM   orders
WHERE  customer_id = :customer_id
ORDER  BY order_date DESC,
          order_id   DESC
OFFSET :offset ROWS
FETCH NEXT :page_size ROWS ONLY;

장점:

• Page 번호 기반 UI 구현이 단순
• 임의 Page 이동 가능
• Token에 마지막 Key를 저장하지 않아도 됨

비용:

• 깊은 Page에서 앞쪽 행을 반복 처리할 수 있음
• 중간에 Row가 삽입·삭제되면 위치가 이동해 중복·누락 가능
• 큰 OFFSET에서 CPU·Logical I/O·Elapsed Time이 증가할 수 있음

4.2 Keyset

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,
          order_id   DESC
FETCH FIRST :page_size ROWS ONLY;

장점:

• 마지막 Key 이후부터 탐색
• 적절한 Index가 있으면 Page 깊이에 따른 작업량이 비교적 안정적
• 무한 스크롤·Feed·최근 이력 조회에 적합

제약:

• 임의 Page 번호 Jump가 어려움
• 모든 Sort Key와 고유 Tie-Breaker를 Token에 보존해야 함
• 정렬 Column 값이 변경되거나 Row가 삭제되면 현재 결과 기준 위치가 달라질 수 있음
• 혼합 ASC·DESC·NULL 순서까지 Predicate와 일치해야 함

5. 결정적인 정렬과 안전한 Page Token

5.1 결정적인 정렬

ORDER BY order_date DESC,
         order_id   DESC

order_date가 같은 행이 여러 개라면 order_id처럼 고유한 Tie-Breaker가 필요하다. 정렬이 결정적이지 않으면 동일 데이터에서도 Page 경계의 행이 실행마다 바뀔 수 있다.

5.2 Token 구성

{
  "token_version": 2,
  "query_version": "orders-v4",
  "sort_id": "order_date_desc_order_id_desc",
  "last_order_date": "2026-08-02T15:10:20.123456+09:00",
  "last_order_id": 987654321,
  "filter_hash": "sha256:...",
  "snapshot_scn": 1234567890123,
  "page_size": 50,
  "expires_at": "2026-08-02T16:10:20+09:00"
}

필수 검토 항목:

• ORDER BY의 모든 Key와 방향
• 고유 Tie-Breaker
• Query·Token Format Version
• Filter를 식별하는 Hash
• 허용 Page Size
• 필요 시 Snapshot SCN
• 만료시간
• 위변조 방지 HMAC 또는 전자서명

Token의 Sort Column 이름이나 방향을 Client가 전달한 문자열로 SQL에 직접 연결하지 않는다. 서버의 Allowlist에서 sort_id를 검증한 SQL Template로 매핑하고, Key 값은 Bind Variable로 전달한다.

filter_hash는 Token 발급 당시 검색조건과 현재 요청이 같은지 확인한다. 서명은 Client가 마지막 Key·SCN·만료시간·Page Size를 임의로 변경하는 것을 막는다.

6. Page 사이 Snapshot 정책

Oracle READ COMMITTED에서는 각 SQL Statement가 열린 시점의 Snapshot을 사용한다.

Page 1 SQL → SCN 100 Snapshot
다른 Transaction Commit
Page 2 SQL → SCN 130 Snapshot

Keyset은 OFFSET보다 위치 이동을 줄일 수 있지만 별도 Statement가 같은 Snapshot을 자동 공유하는 것은 아니다. 업무 요구에 따라 두 정책 중 하나를 명시한다.

6.1 최신성 우선

• Page마다 현재 Commit Data 조회
• 새 행·수정·삭제의 영향을 허용
• Feed·Monitoring·검색 화면에 적합
• Token 만료가 짧고 재검색이 자연스러운 업무

6.2 결과 집합 일관성 우선

첫 Page에서 SCN을 확보하고 다음 Page에서 같은 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,
         order_id   DESC
FETCH FIRST :page_size ROWS ONLY;

Flashback Query는 지정 SCN의 Commit된 데이터를 반환한다. 필요한 과거 Block Image는 Undo에 의존하므로 다음을 함께 정한다.

• Token 최대 유효시간
• Undo Tablespace 크기와 실제 부하
• TUNED_UNDORETENTION과 장기 Query 길이
• 오래된 Token 만료·재검색 정책
• ORA-01555 처리
• Flashback 권한과 대상 Object·DDL 제약

UNDO_RETENTION은 항상 보장되는 SLA가 아니라 공간과 활동량의 영향을 받는 Best-Effort 기준이다. 긴 Token 수명만 늘리고 Undo 용량과 실패 정책을 설계하지 않으면 일관된 Pagination을 보장할 수 없다.

7. 요청 종료와 Connection 반환

JDBC Driver는 Finalizer에 의존하지 않으므로 ResultSet과 Statement를 명시적으로 닫아야 한다. ResultSet만 닫고 Statement를 닫지 않으면 Database Cursor가 해제되지 않을 수 있다.

권장 구조:

try-with-resources
→ ResultSet Close
→ Statement Close 또는 Statement Cache로 반환
→ 성공 시 Commit / 실패 시 Rollback
→ 변경한 Session State 복구
→ Logical Connection Close
→ Physical Connection Pool 반환

Framework가 Transaction 종료 순서를 관리하더라도 요청 종료 시 다음 상태가 다음 사용자에게 노출되지 않아야 한다.

• 미완료 Local Transaction
• 열린 ResultSet·Statement
• 변경된 Auto-Commit·Isolation
• ALTER SESSION으로 바꾼 NLS·Optimizer 설정
• Current Schema
• CLIENT_IDENTIFIER, Module, Action
• Local Application Context
• Global Temporary Table 데이터 정책
• PL/SQL Package State

Oracle 26ai의 Request Boundary 기반 Reset Database Session State 기능은 Application이 남긴 Session State를 정리하는 데 도움을 줄 수 있다. 그러나 애플리케이션의 정상·예외 경로에서 Close·Commit·Rollback을 생략하는 대체재로 사용해서는 안 된다.

UCP의 Abandoned Connection Timeout도 누수 방지의 마지막 안전장치다. UCP는 회수 전 Pending Local Transaction을 Cancel하거나 Rollback할 수 있지만, 정상 업무가 ACT에 의해 회수되도록 설계하면 예측 불가능한 실패와 부분 처리 위험이 생긴다.

8. Timeout·Cancel·Connection 상태

서로 다른 Timeout을 구분한다.

Statement.setQueryTimeout()은 내부적으로 Cancel에 의존한다. Statement.cancel()은 Database에 Interrupt를 보내고 Database의 응답을 기다리므로 Network가 끊겼거나 Server가 Hang 상태이면 호출 Thread가 즉시 풀리지 않을 수 있다. Timeout 시간도 정확한 Deadline보다 늦게 발생할 수 있다.

Timeout·Cancel 후 점검:

1. Database Statement가 실제 종료됐는가?
2. ResultSet·Statement가 닫혔는가?
3. Transaction Rollback이 필요한가?
4. Connection Protocol 상태가 정상인가?
5. Validation을 통과하는가?
6. 불확실하면 해당 Physical Connection을 Pool에서 제거할 것인가?

Query Timeout과 Network Timeout을 동일한 의미로 취급하지 않는다. Query Timeout은 SQL 실행 취소를 시도하고, Network Read Timeout은 응답을 읽는 Socket 대기를 제한한다.

9. Backpressure와 Page 크기

느린 Client가 큰 ResultSet을 천천히 소비하면 Database Cursor와 Physical Connection의 Hold Time이 길어진다.

Database Row 생산
→ Driver Prefetch
→ Network 전송
→ Application 처리 지연
→ Connection 점유시간 증가
→ Pool Borrow Wait 증가

적용할 제어:

• 최대 Page Size와 최대 응답 Byte
• Driver Fetch Size와 Page Size의 조정
• Bounded Queue·Buffer
• 동시 조회 수 제한
• 느린 Client Disconnect
• Export를 비동기 Batch로 분리
• Backpressure를 지원하는 Streaming Framework
• page_size + 1 조회로 다음 Page 존재만 판단

Fetch Size를 무조건 크게 하면 Round Trip은 줄 수 있지만, Page Size보다 많은 행을 Client로 Prefetch해 소비하지 않는 Row와 Memory를 늘릴 수 있다.

10. Count Query와 Page Query를 분리한다

COUNT(*)
→ 전체 조건의 모든 Row를 계산할 수 있음

Page Query
→ 정렬된 앞쪽 N행에서 종료 가능

Page Query가 Index Order와 STOPKEY로 빨라도 정확한 전체 Count는 조건 전체를 끝까지 읽을 수 있다.

page_size + 1행을 조회해 추가 한 행이 있으면 has_next=true로 판단하고, 사용자에게는 앞의 page_size행만 반환한다.

11. 운영 관찰 지표

11.1 Connection Pool

• Available·Borrowed·Total Physical Connections
• Pending Borrow Requests
• Average·Maximum Connection Wait Time
• Connection Hold Time
• Abandoned·TTL Reclaim 수
• Validation 실패와 제거된 Connection 수
• Physical Connection당 Statement Cache 크기

11.2 Database

• V$SESSION: SQL_ID, Status, Module, Action, Client Identifier
• V$OPEN_CURSOR: Session별 Open·Cached Cursor와 Cursor Type
• opened cursors current
• user calls
• SQL*Net roundtrips to/from client
• V$SQLSTATS: EXECUTIONS, FETCHES, ROWS_PROCESSED, END_OF_FETCH_COUNT
• Long-Running Query, ORA-01555, Undo 통계

END_OF_FETCH_COUNT는 Cursor가 끝까지 실행된 횟수다. 첫 Page만 읽고 닫은 실행, 오류, Cancel, 재실행은 증가하지 않을 수 있으므로 낮은 값만으로 성공적인 Pagination이라고 단정하지 않는다.

11.3 Application

• First Page·Next Page 응답시간
• Page별 반환 Row·Byte
• Token 오류·만료·서명 실패
• OFFSET 깊이와 Keyset 사용 비율
• ResultSet 소비시간과 Connection Hold Time
• Query Timeout·Network Timeout·사용자 Cancel
• 동일 사용자·Tenant의 동시 조회 수

12. 구현·진단 절차

1. 2-Tier Stateful Cursor가 가능한 환경인지 확인한다.
2. n-Tier이면 요청 사이 Connection을 보존하지 않는 전제를 세운다.
3. OFFSET 또는 Keyset을 업무 기능과 Page 깊이로 선택한다.
4. ORDER BY의 모든 Key·방향·NULL 순서와 Tie-Breaker를 확정한다.
5. Token에 Key·Query Version·Filter Hash·만료·서명을 포함한다.
6. 최신성 또는 동일 Snapshot 정책을 결정한다.
7. Flashback 사용 시 Undo·권한·Token 수명을 함께 검증한다.
8. try-with-resources와 명시적 Commit·Rollback 경로를 작성한다.
9. 변경한 Session State를 Request 종료 시 복구한다.
10. Query·Network·HTTP·Pool Timeout을 계층별로 설정한다.
11. Cancel 후 Connection Validation·제거 정책을 정의한다.
12. Pool Wait·Connection Hold·Fetch·Open Cursor를 부하 테스트로 측정한다.

13. 혼동하기 쉬운 판단

14. 최종 체크리스트

• 요청 사이 같은 Physical Connection이 필요하지 않은 구조인가?
• Statement Cache와 ResultSet Fetch 위치를 구분했는가?
• ORDER BY가 결정적이고 고유 Tie-Breaker가 있는가?
• Keyset Predicate가 ASC·DESC·NULL 의미와 일치하는가?
• Token에 Filter Hash·Version·만료·서명이 있는가?
• 최신성·동일 Snapshot 요구가 명시됐는가?
• Flashback Token 수명과 Undo 보존을 함께 검증했는가?
• ResultSet과 Statement를 모두 닫는가?
• 성공·오류·Timeout 경로에서 Commit·Rollback이 명확한가?
• Session State Reset 또는 명시적 복구 정책이 있는가?
• Query·Network·HTTP·Pool Timeout을 구분했는가?
• Cancel 후 Connection Validation·폐기 기준이 있는가?
• Page Size·Fetch Size·응답 Byte 상한이 있는가?
• Count Query가 정말 필요한지 확인했는가?
• Pool Hold Time·Open Cursor·Fetch 통계를 실측했는가?