부분범위 처리 기초: 첫 행 응답·Fetch 중단·전체 처리량

핵심 요약

부분범위 처리는 전체 결과를 모두 만든 뒤 일부를 버리는 기능이 아니다. 필요한 앞쪽 행을 먼저 생산할 수 있고 Client가 더 이상 Fetch하지 않을 때 남은 Row Source 작업을 끝내지 않아도 되는 실행 특성이다.

업무가 필요한 앞쪽 범위를 정의
→ SQL이 정렬과 최대 반환 행을 명확히 표현
→ 실행계획이 앞 행을 이른 시점에 생산
→ Driver가 결과 Batch를 전달
→ Client가 필요한 행만 소비
→ ResultSet·Statement Close
→ 남은 작업 생략 가능

부분범위 처리의 효과는 다음 세 계층이 모두 맞아야 한다.

SQL 의미
→ 결정적인 ORDER BY와 필요한 Row Limit

실행계획
→ 앞 행을 빠르게 생산하고 불필요한 Blocking·Random Access를 줄임

Application
→ 전체 Materialize 없이 필요한 행까지만 Fetch하고 즉시 Close

FIRST_ROWS(n)은 Optimizer의 응답시간 목표이고, FETCH FIRST n ROWS ONLY는 Query의 최대 반환 행을 정의한다. Client가 일부만 읽고 닫는 동작은 또 다른 계층이다. 세 개를 같은 기능으로 이해하면 안 된다.

학습 목표

• 부분범위 처리와 전체범위 처리의 차이를 설명한다.
• Parse·Execute·Fetch·Close와 Client-visible First Batch를 구분한다.
• Time to First Row·First Batch·Last Row를 업무 목표에 맞게 선택한다.
• Pipelined Operation과 Blocking Operation의 초기 응답 차이를 설명한다.
• SORT ORDER BY STOPKEY와 정렬 지원 Index의 입력 처리량 차이를 분석한다.
• FIRST_ROWS(n)과 Row Limiting Clause의 역할·한계를 구분한다.
• JDBC Fetch Size·Row Prefetch와 Page Size를 구분한다.
• V$SQLSTATS.END_OF_FETCH_COUNT를 누적값·Delta 관점에서 해석한다.
• 부분 Fetch의 실행계획 Runtime 통계와 운영 Trade-off를 진단한다.

1. 부분범위 처리의 의미

사용자가 검색 결과의 첫 20행만 확인한다고 가정한다.

조건을 만족할 가능성이 있는 행 = 1,000,000
화면이 필요한 행             = 20

다음 조건이 충족되면 Database가 전체 후보의 모든 후속 작업을 끝내기 전에 첫 20행을 반환하고, Client가 Cursor를 닫은 시점에 남은 작업을 중단할 수 있다.

부분범위 처리
→ 실제 소비 범위까지만 Row Source 작업 수행 가능

전체범위 처리
→ 마지막 행·전체 집계·전체 Export가 필요하므로 끝까지 수행

대표 업무

부분범위 처리는 결과 건수가 작다는 뜻이 아니다. 전체 후보가 매우 커도 앞쪽 일부만 필요하면 적용할 수 있고, 반대로 결과가 20행이어도 첫 행 전에 전체 집계가 필요하면 부분범위 효과가 작을 수 있다.

2. Parse·Execute·Fetch·Close를 구분한다

SELECT의 Client 처리 흐름은 다음처럼 본다.

Parse
→ Execute
→ Fetch Batch
→ Fetch Batch
→ ...
→ End of Fetch 또는 Early Close

• Parse: SQL 문법·의미를 확인하고 실행 가능한 Cursor를 준비한다.
• Execute: Query 실행을 시작하고 Row Source를 구동할 준비를 한다.
• Fetch: Result Set에서 다음 행 또는 다음 Batch를 Client로 전달한다.
• Close: ResultSet·Statement와 관련 자원을 정리한다.

Execute가 항상 작업을 거의 하지 않는다고 단정하면 안 된다. Blocking Operation, Parallel Query 초기화, Remote Access 등은 첫 행 전에 상당한 작업을 수행할 수 있다. 반대로 많은 Row Source는 부모 Operation이 행을 요청할 때 필요한 만큼 생산하므로 Fetch가 진행되는 동안 Scan·Join·Table Access가 계속될 수 있다.

실행 A
→ 20행을 소비하고 즉시 Close

실행 B
→ 1,000,000행을 마지막까지 Fetch

같은 SQL_ID와 Plan이라도 두 실행의 A-Rows, Buffers, Fetches, Rows Processed, Elapsed Time은 달라질 수 있다.

3. 첫 행·첫 Batch·마지막 행 시간을 구분한다

3.1 Time to First Row

Database Row Source가 첫 행을 만들 수 있는 시점이다. 실행계획의 초기 선행 작업량을 평가할 때 유용하다.

3.2 Time to First Batch

Driver의 Row Prefetch·Fetch Size만큼 결과가 Client에 도착해 Application이 첫 행을 읽기 시작할 수 있는 시점이다. 사용자가 체감하는 API 초기 응답은 첫 행보다 첫 Batch와 Serialization 시점에 더 가까울 수 있다.

3.3 Time to Last Row

전체 결과를 끝까지 Fetch하고 마지막 행을 소비한 시점이다. Export·Report·Batch에서는 이 값과 총 CPU·I/O·Network 사용량이 중요하다.

Plan A
→ First Batch 60ms
→ Last Row 28s

Plan B
→ First Batch 1.5s
→ Last Row 9s

검색 첫 Page에는 Plan A가 유리할 수 있고 전체 Download에는 Plan B가 유리할 수 있다. 성능 목표를 정하지 않은 채 평균 Elapsed Time 하나만 비교하면 잘못된 Plan을 선택할 수 있다.

4. Pipelined Operation과 Blocking Operation

4.1 앞 행을 비교적 일찍 전달할 수 있는 Operation

INDEX RANGE SCAN
TABLE ACCESS BY INDEX ROWID
NESTED LOOPS
FILTER
UNION ALL의 현재 Branch

선택적인 Index Range Scan과 Nested Loops는 앞쪽 Driving Row를 빠르게 찾을 때 초기 응답에 유리할 수 있다. 다만 Inner Table Random Access가 매우 많거나 첫 일치 행을 찾기 어려우면 NL Join도 느릴 수 있다.

4.2 첫 출력 전에 선행 작업이 필요한 Operation

전체 SORT ORDER BY
HASH GROUP BY
SORT GROUP BY
SORT UNIQUE·HASH UNIQUE
WINDOW SORT
HASH JOIN의 Build 단계
SORT MERGE JOIN의 입력 Sort
TEMP TABLE TRANSFORMATION의 적재 단계

Blocking은 항상 나쁘다는 뜻이 아니다. 전체 결과를 끝까지 처리하는 업무에서는 Hash Join·Hash Aggregate가 더 효율적일 수 있다. 부분범위 처리에서는 첫 출력 전 입력량과 전체 완료 비용을 따로 비교한다.

5. 정렬·STOPKEY·Index Order

다음 Query는 급여 상위 20명을 요청한다.

SELECT empno,
       ename,
       sal
FROM   emp
ORDER  BY sal   DESC,
          empno ASC
FETCH FIRST 20 ROWS ONLY;

5.1 정렬을 지원하는 Index가 없는 경우

TABLE ACCESS FULL
→ SORT ORDER BY STOPKEY
→ 상위 20행 반환

SORT ORDER BY STOPKEY는 Top-N만 유지하도록 Sort Workarea를 줄일 수 있다. 그러나 어떤 행이 최상위 20개인지 확정하려면 조건을 만족하는 많은 하위 후보를 읽고 비교할 수 있다.

최종 출력 A-Rows = 20
하위 Scan A-Rows  = 800,000 가능

따라서 Plan에 STOPKEY가 보인다는 사실만으로 Database가 20행만 읽었다고 판단하지 않는다.

5.2 Filter와 정렬을 함께 지원하는 Index가 있는 경우

예를 들어 업무 조건과 정렬 방향에 맞는 Index가 있다면 다음처럼 앞쪽 Key에서 중단할 가능성이 높아진다.

INDEX RANGE SCAN DESCENDING
→ 필요한 Key 20개 확보
→ 선택적 TABLE ACCESS
→ STOPKEY

하지만 Index에 없는 반환 Column을 읽기 위한 Table Random Access, Clustering Factor, Filter 후 탈락 행을 함께 확인해야 한다. Sort 제거만 보고 Index Plan이 항상 우수하다고 결론 내리지 않는다.

6. FIRST_ROWS(n)과 Row Limiting Clause

6.1 FIRST_ROWS(n)

SELECT /*+ FIRST_ROWS(20) */
       order_id,
       order_date,
       amount
FROM   orders
WHERE  customer_id = :customer_id
ORDER  BY order_date DESC,
          order_id   DESC;

FIRST_ROWS(n)은 앞쪽 n행을 빠르게 반환하는 Plan을 선택하도록 Optimizer 목표를 제시한다.

• 결과 행 수를 n으로 제한하지 않는다.
• Client가 전체 Fetch하면 전체 Result Set을 처리한다.
• Sort·Grouping처럼 Query Block이 첫 행 전에 전체 입력을 요구하면 Hint가 무시될 수 있다.
• Hint는 오류 없이 무시될 수 있으므로 실제 실행계획과 Hint Report를 확인한다.
• 통계와 데이터 분포가 바뀌면 Hint의 장기 효과도 다시 검증해야 한다.

6.2 FETCH FIRST n ROWS ONLY

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

Row Limiting Clause는 Query의 최대 반환 행을 정의한다. Top-N이 업무 의미라면 Client에서 20행 후 중단하는 것보다 SQL에 제한을 표현하는 편이 다음 측면에서 유리하다.

• Database가 Top-N·STOPKEY 변환을 검토할 수 있음
• Client 구현이 바뀌어도 최대 반환 행이 유지됨
• Network·Serialization·Memory 상한이 명확해짐
• 테스트와 운영 통계의 의미가 분명해짐

일관된 Top-N을 위해 고유 Tie-Breaker를 포함한 결정적인 ORDER BY를 사용한다.

FIRST_ROWS(20)
→ Plan 선택 목표

FETCH FIRST 20 ROWS ONLY
→ SQL 결과 의미와 최대 행 수

20행 Fetch 후 Close
→ Application 소비·자원 정리 방식

7. Fetch Size와 Page Size는 다르다

Page Size
→ 화면·API가 사용할 업무 행 수

Fetch Size·Row Prefetch
→ 한 번의 Database 왕복에서 Driver가 가져오려는 행 수

예를 들어 Page Size가 20인데 Fetch Size가 100이면, Application이 20행만 사용해도 Driver가 첫 왕복에서 최대 100행을 미리 가져올 수 있다. 이 경우 Client가 소비한 행 수와 Database가 생산·전송한 행 수가 같지 않을 수 있다.

try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setFetchSize(20);

    try (ResultSet rs = ps.executeQuery()) {
        int count = 0;
        while (count < 20 && rs.next()) {
            consume(rs);
            count++;
        }
    }
}

Fetch Size를 지나치게 키우면 Network Round Trip은 줄 수 있지만 다음 비용이 커질 수 있다.

• 사용하지 않을 행의 Prefetch
• 넓은 Row·LOB에 대한 Client Memory
• 첫 Batch Byte 증가
• 동시 ResultSet의 Heap·Direct Memory 증가

부분범위 화면에서는 Page Size, Row 폭, Network RTT, 실제 소비 비율을 함께 측정한다.

8. Client가 실제로 Fetch를 멈추고 닫아야 한다

Database가 앞쪽 행을 빠르게 만들어도 Framework가 전체 ResultSet을 List·DataFrame·JSON Tree로 Materialize한 뒤 20행만 노출하면 부분범위 효과가 사라진다.

확인할 Application 동작

• ResultSet.next()를 실제로 몇 회 호출하는가?
• ORM이 SQL Row Limit을 생성하는가, 아니면 Client에서 Slice하는가?
• Serialization 전에 전체 결과를 읽는가?
• Timeout·Cancel·예외에서 ResultSet과 Statement가 즉시 닫히는가?
• Connection이 ResultSet 소비 중 얼마나 오래 점유되는가?
• Fetch Size가 Page Size보다 지나치게 크지 않은가?

Oracle JDBC에서는 ResultSet과 Statement를 명시적으로 닫아야 한다. ResultSet만 닫고 Statement를 장기간 유지하면 Database Cursor 자원 해제가 기대와 다를 수 있으므로 try-with-resources로 두 객체의 생명주기를 함께 관리한다.

9. END_OF_FETCH_COUNT의 정확한 해석

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

• 일부 행만 Fetch하고 Close
• 실행 중 오류
• Cursor를 끝까지 읽기 전에 재실행
• Client Cancel·Timeout·Connection 장애 등으로 중단

SELECT sql_id,
       executions,
       fetches,
       rows_processed,
       end_of_fetch_count,
       delta_execution_count,
       delta_fetch_count,
       delta_rows_processed,
       delta_end_of_fetch_count
FROM   v$sqlstats
WHERE  sql_id = :sql_id;

해석 원칙

1. END_OF_FETCH_COUNT <= EXECUTIONS다.
2. 낮은 비율은 의도적 부분 Fetch의 증거가 아니라 부분 실행·실패·재실행 가능성을 뜻한다.
3. 값은 SQL_ID 단위 누적 통계이므로 여러 Module·업무가 같은 SQL을 공유하면 원인을 분리하기 어렵다.
4. Cursor가 Shared Pool에서 사라져도 V$SQLSTATS에 통계가 더 오래 남을 수 있다.
5. 원시 누적값보다 부하 전후 Delta 또는 AWR Snapshot Delta를 사용한다.

EXECUTIONS Delta          = 1,000
END_OF_FETCH_COUNT Delta  = 120

잘못된 결론
→ 880회가 성공적인 부분범위 처리

정확한 후속 확인
→ Module·Action, 오류율, Timeout, Cancel, 재실행, 실제 소비 행 수를 함께 확인

ROWS_PROCESSED는 SELECT가 반환한 행의 누적값이다. 하위 Row Source가 읽은 후보 행 수는 실행계획 Runtime A-Rows, Buffers, Reads로 별도 확인한다.

10. Runtime 실행계획으로 실제 중단 지점을 확인한다

테스트 Session에서 Runtime Row Source 통계를 수집한다.

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

SELECT *
FROM   TABLE(
         DBMS_XPLAN.DISPLAY_CURSOR(
           NULL,
           NULL,
           'ALLSTATS LAST +PREDICATE +HINT_REPORT'
         )
       );

확인 항목은 다음과 같다.

일부 Fetch 후 Cursor를 닫은 실행의 Runtime 통계는 중단 시점까지 수행한 작업을 보여 준다. 전체 Fetch 시 필요한 잠재 비용과 같다고 보지 말고 동일 Bind·동일 환경에서 부분 Fetch와 전체 Fetch를 별도 실행해 비교한다.

11. 부분 Fetch의 운영 Trade-off

부분 Fetch는 남은 작업을 피할 수 있지만 Cursor를 오래 열어 두는 방식은 다음 위험을 만든다.

• Connection 장시간 점유
• Open Cursor 증가
• 사용자 Think Time 동안 Server·Client 상태 유지
• 긴 Query Snapshot과 Undo 재구성 부담
• 필요한 Undo가 재사용될 경우 ORA-01555 가능성
• Timeout·Network 단절 시 정리 지연
• Transaction 경계와 Connection Pool 반환 지연

대화형 Web·API에서는 한 Page를 SQL로 제한해 조회하고 즉시 Cursor를 닫는 Stateless 방식이 일반적으로 운영하기 쉽다. Cursor를 여러 화면 요청 사이에 유지하는 방식은 Session Affinity, 장애 복구, Pooling, Undo 보존을 모두 통제할 수 있을 때만 검토한다.

12. 진단·적용 절차

1. 업무 목표가 First Page인지 전체 결과인지 확정한다.
2. 반환 Column·Page Size·최대 Row를 정한다.
3. 고유 Tie-Breaker를 포함한 ORDER BY를 작성한다.
4. Top-N이면 SQL에 FETCH FIRST·FETCH NEXT를 명시한다.
5. FIRST_ROWS(n)은 필요성과 Hint 적용 여부를 Plan에서 검증한다.
6. Filter·Order를 지원하는 Index와 Table Random Access를 함께 비교한다.
7. Blocking Operation 이전 입력량과 Runtime A-Rows·Buffers를 확인한다.
8. Client의 실제 next() 횟수, Materialize 여부, Close 시점을 추적한다.
9. Page Size와 Fetch Size를 분리해 RTT·Memory·Prefetch 낭비를 측정한다.
10. V$SQLSTATS 누적값이 아니라 부하 구간 Delta를 비교한다.
11. 부분 Fetch와 전체 Fetch를 동일 Bind로 각각 측정한다.
12. Cursor·Connection 보유시간, Timeout, Undo 위험을 운영 기준에 반영한다.

혼동하기 쉬운 판단