옵티마이저 목표 모드: ALL_ROWS와 FIRST_ROWS(n)

핵심 요약

Optimizer Goal은 옵티마이저가 후보 실행계획의 자원 사용량을 비교할 때 무엇을 우선할지 정하는 기준입니다.

ALL_ROWS
  → Statement 전체를 완료하는 처리량과 최소 총 자원 사용을 중시

FIRST_ROWS(n)
  → 처음 n행을 반환하는 초기 응답시간을 중시

Oracle의 기본 OPTIMIZER_MODE는 ALL_ROWS입니다. FIRST_ROWS(n)도 비용 기반 방식이지만, 전체 결과 완료보다 앞쪽 n행의 반환을 더 중요하게 평가합니다.

두 가지를 반드시 구분해야 합니다.

Optimizer Goal
  → 실행계획의 비용 비교 목표를 바꾼다.

Top-N 또는 Row Limiting
  → SQL이 반환해야 하는 결과 행 수를 제한한다.

따라서 FIRST_ROWS(20) Hint를 사용해도 SQL에 결과 제한이 없고 Client가 끝까지 Fetch하면 전체 행이 반환됩니다. 최신 20건만 필요한 업무라면 ORDER BY와 FETCH FIRST 20 ROWS ONLY를 SQL에 별도로 표현해야 합니다.

또한 FIRST_ROWS(n) Hint가 항상 초기 응답 계획을 만드는 것은 아닙니다. SELECT Statement Block에 전체 입력을 소비해야 하는 Blocking Operation이 포함되면 Hint가 무시되고 처리량 중심으로 최적화될 수 있습니다.

학습 목표

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

• Optimizer Goal의 의미를 설명한다.
• ALL_ROWS와 FIRST_ROWS(n)의 목표 차이를 설명한다.
• Parameter의 FIRST_ROWS_10과 Hint의 FIRST_ROWS(10) 문법과 적용 범위를 구분한다.
• Parameter에서 사용할 수 있는 n과 Hint에 직접 지정하는 n의 차이를 설명한다.
• FIRST_ROWS와 FIRST_ROWS(n)을 구분한다.
• FIRST_ROWS(n)이 결과 행 수를 제한하지 않는 이유를 설명한다.
• 부분범위 처리와 Blocking Operation의 차이를 설명한다.
• FIRST_ROWS Hint가 무시될 수 있는 조건을 설명한다.
• 초기 n행 응답시간과 전체 Fetch 완료시간을 별도로 측정한다.
• 화면 조회와 Batch에서 적합한 목표가 달라질 수 있는 이유를 설명한다.

1. 왜 Optimizer Goal이 필요한가

같은 SQL이라도 업무 목적에 따라 좋은 실행계획의 기준이 달라질 수 있습니다.

1.1 온라인 화면

사용자는 검색 버튼을 누른 뒤 첫 화면의 20건을 빠르게 확인하려고 합니다.

중요한 지표
  → 첫 행 또는 첫 20행을 받을 때까지의 시간

1.2 Batch·Report

업무는 전체 수백만 행을 읽어 집계 파일이나 보고서를 완성해야 합니다.

중요한 지표
  → 전체 결과를 끝까지 처리하는 시간
  → 전체 CPU·I/O·메모리 사용량

첫 행을 빨리 반환하는 계획과 전체 결과를 효율적으로 완료하는 계획은 서로 다를 수 있습니다. Optimizer Goal은 이 차이를 후보 계획의 Cost 평가에 반영합니다.

2. ALL_ROWS: 전체 처리량 최적화

ALL_ROWS는 Statement가 접근해야 하는 전체 행을 처리하고 작업을 완료하는 데 필요한 총 자원을 줄이는 방향으로 계획을 비교합니다.

목표
  → 전체 Statement 완료 비용 최소화
  → 전체 처리량 향상

다음과 같은 업무에 주로 적합합니다.

• 대량 Batch
• 전체 집계 Report
• ETL
• 대량 데이터 검증
• 결과를 끝까지 모두 Fetch하는 업무

전체 데이터 중 많은 행을 처리한다면 다음 후보가 유리할 수 있습니다.

• Table Full Scan
• Hash Join
• 대량 Sort·Hash Aggregate
• 병렬 처리

그러나 이것은 고정 규칙이 아닙니다. ALL_ROWS에서도 통계와 데이터량을 기준으로 전체 Cost가 낮다면 Index Scan과 Nested Loops Join이 선택될 수 있습니다.

2.1 설정 예시

ALTER SESSION SET optimizer_mode = ALL_ROWS;

개별 Statement Block에서는 다음 Hint를 사용할 수 있습니다.

SELECT /*+ ALL_ROWS */
       order_id,
       customer_id,
       order_date
FROM orders
WHERE status = 'READY';

3. FIRST_ROWS(n): 처음 n행 응답 최적화

FIRST_ROWS(n)은 처음 n개 행을 반환하는 응답시간을 중시하는 비용 기반 목표입니다.

목표
  → 처음 n행을 가능한 한 빠르게 반환

다음 업무에서 검토할 수 있습니다.

• 검색 화면의 첫 페이지
• 대화형 조회
• 최신 데이터 일부 확인
• 사용자가 전체 결과를 보지 않고 앞부분만 확인하는 업무

초기 행을 빠르게 반환할 수 있다면 다음 후보의 Cost가 상대적으로 유리해질 수 있습니다.

• 정렬 순서와 맞는 Index Scan
• 소량의 선행 행을 빠르게 만드는 Nested Loops Join
• 전체 입력을 소비하기 전에 행을 전달할 수 있는 Non-Blocking Row Source
• 필요한 행 수에 도달하면 중단할 수 있는 Stopkey 기반 Top-N 처리

이 역시 고정 규칙은 아닙니다. 적합한 인덱스가 없거나 반환 대상이 매우 많거나 Blocking Operation이 필요하면 Full Scan, Hash Join 또는 처리량 중심 계획이 선택될 수 있습니다.

4. Parameter와 Hint 문법·적용 범위

4.1 OPTIMIZER_MODE Parameter

OPTIMIZER_MODE Parameter에서 사용할 수 있는 비용 기반 초기 응답 목표는 다음 네 가지입니다.

ALTER SESSION SET optimizer_mode = FIRST_ROWS_1;
ALTER SESSION SET optimizer_mode = FIRST_ROWS_10;
ALTER SESSION SET optimizer_mode = FIRST_ROWS_100;
ALTER SESSION SET optimizer_mode = FIRST_ROWS_1000;

Parameter의 n은 1, 10, 100, 1000 중 하나를 사용하며 밑줄로 표기합니다.

FIRST_ROWS_1
FIRST_ROWS_10
FIRST_ROWS_100
FIRST_ROWS_1000

Parameter는 System 또는 Session 범위에서 설정할 수 있습니다.

4.2 FIRST_ROWS(n) Hint

개별 SQL Statement Block에는 괄호 형식의 Hint를 사용합니다.

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

Parameter
  → FIRST_ROWS_10

Hint
  → FIRST_ROWS(10)
  → FIRST_ROWS(20)처럼 목표 행 수를 직접 지정 가능

Hint는 해당 Hint가 작성된 Statement Block의 최적화에 영향을 줍니다. 복합 SQL이나 Set Operator의 각 구성 Statement Block에 서로 다른 Optimizer Goal Hint가 지정될 수도 있습니다.

Hint는 System·Session Parameter보다 우선할 수 있지만, 문법·적용 가능성·Blocking Operation 등의 이유로 항상 사용되는 것은 아닙니다.

5. FIRST_ROWS와 FIRST_ROWS(n)의 구분

n이 없는 FIRST_ROWS는 비용과 휴리스틱을 혼합하여 처음 몇 행을 빠르게 전달하려는 과거 호환 방식입니다.

FIRST_ROWS
  → 비용과 휴리스틱을 혼합
  → 하위 호환성과 Plan 안정성을 위해 유지
  → Oracle은 FIRST_ROWS_n 사용을 권장

FIRST_ROWS(n)
  → 명시한 n행의 초기 응답을 목표로 하는 비용 기반 방식

SQLP 문제에서 두 용어가 함께 등장하면 다음을 구분해야 합니다.

• n의 유무
• 순수 비용 기반 목표인지
• Parameter인지 Hint인지
• 실제 결과 제한이 존재하는지

6. 목표 모드는 결과 행 수를 제한하지 않는다

다음 SQL에 FIRST_ROWS(20) Hint가 있어도 결과를 20행으로 제한하는 조건은 없습니다.

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

Client가 전체 결과를 끝까지 Fetch하면 모든 행이 반환됩니다.

FIRST_ROWS(20)
  → 비용 계산 목표를 처음 20행 응답에 맞춘다.
  → SQL의 논리적 결과 집합을 20행으로 바꾸지 않는다.

업무 요구가 최신 20건이라면 결과 제한을 SQL에 표현합니다.

SELECT /*+ FIRST_ROWS(20) */
       order_id,
       order_date,
       total_amount
FROM orders
WHERE customer_id = :customer_id
ORDER BY order_date DESC, order_id DESC
FETCH FIRST 20 ROWS ONLY;

ORDER BY 없이 행 수만 제한하면 어떤 20행을 선택해야 하는지 업무적으로 불명확할 수 있습니다. 또한 같은 order_date 값을 가진 행의 순서까지 일관되게 유지하려면 order_id처럼 고유한 Tie-breaker를 함께 사용합니다.

FETCH FIRST 20 ROWS ONLY 자체가 업무 결과 범위를 명확히 표현하므로, FIRST_ROWS(20) Hint를 추가해야만 Top-N 처리가 가능한 것은 아닙니다. 최종 계획과 실제 작업량을 확인해 Hint의 필요성을 판단합니다.

7. 부분범위 처리와 Blocking Operation

7.1 부분범위 처리

부분범위 처리는 전체 결과를 모두 완성한 뒤 한 번에 반환하는 것이 아니라, 앞쪽 결과를 생산하는 대로 Client가 Fetch할 수 있게 전달하는 처리 특성입니다.

효과가 기대되는 조건은 다음과 같습니다.

• Client가 실제로 n행 정도만 Fetch하고 Cursor를 닫음
• SQL이 FETCH FIRST n ROWS ONLY로 결과를 제한함
• 정렬 기준을 만족하는 인덱스가 있어 별도 전체 Sort를 피할 수 있음
• 선행 Row Source가 적은 행을 빠르게 생산함
• 필요한 행 수에 도달하면 Stopkey로 조기 중단할 수 있음

7.2 Blocking Operation

Blocking Operation은 첫 결과를 반환하기 전에 입력 집합의 전부 또는 상당 부분을 먼저 소비해야 하는 Operation입니다.

대표적인 예는 다음과 같습니다.

• 전체 Sort가 필요한 ORDER BY
• GROUP BY와 Aggregate
• DISTINCT
• 일부 Set Operation
• 전체 입력을 요구하는 처리 단계

Oracle은 FIRST_ROWS(n) Hint를 DELETE·UPDATE Statement Block에서 무시합니다. 또한 SELECT Statement Block에 Sort나 Grouping과 같은 Blocking Operation이 포함되어 처음 행을 반환하기 전에 전체 입력을 읽어야 한다면 Hint를 무시하고 처리량 중심으로 최적화할 수 있습니다.

중요한 구분은 다음과 같습니다.

ORDER BY가 존재함
  ≠ 반드시 Blocking Sort가 발생함

정렬 순서와 맞는 Index가 사용됨
  → 별도 Sort 없이 순서대로 행을 생산할 수 있음

전체 Sort가 실제로 필요함
  → FIRST_ROWS(n)의 초기 응답 이점이 줄거나 Hint가 무시될 수 있음

8. FIRST_ROWS(n)의 효과가 줄어드는 조건

다음 상황에서는 초기 응답 목표의 실질적인 효과가 작아질 수 있습니다.

• Client가 결국 전체 결과를 모두 Fetch함
• 전체 결과를 읽어야 하는 Sort·Aggregate·DISTINCT가 있음
• 정렬 기준에 맞는 Access Path가 없음
• Index Range Scan 후 Table Access by ROWID가 지나치게 반복됨
• 목표 n보다 실제 사용하는 행 수가 훨씬 많음
• 인기 Bind 값에서 반환 행 수가 크게 증가함
• Hint가 Blocking Operation 등의 이유로 무시됨

따라서 Optimizer Goal은 Hint 문자열만 확인하는 것이 아니라 실제 Fetch 행 수와 실행계획을 함께 확인해야 합니다.

9. 같은 SQL에서 Goal에 따라 달라질 수 있는 계획

고객 주문을 최신순으로 조회하는 SQL을 가정합니다.

SELECT order_id,
       order_date,
       total_amount
FROM orders
WHERE customer_id = :customer_id
ORDER BY order_date DESC, order_id DESC;

9.1 ALL_ROWS 관점

고객 주문 전체를 끝까지 읽는 비용을 중시
  → 결과가 많으면 Full Scan·Hash Join·대량 Sort 등
     전체 처리량에 유리한 후보를 검토할 수 있음

9.2 FIRST_ROWS(20) 관점

최신 20건을 먼저 반환하는 비용을 중시
  → CUSTOMER_ID와 정렬 컬럼을 지원하는 Index 후보의 가치 증가
  → 적은 선행 행을 빠르게 만드는 Nested Loops 후보 검토
  → 조건을 만족하면 Stopkey로 조기 중단 가능

한 고객의 주문이 수백만 건이고 Client가 결국 모든 행을 Fetch한다면, 첫 20행에 빠른 Index Plan이 전체 완료시간에서는 많은 Random Access로 불리할 수 있습니다.

반대로 적절한 인덱스가 정렬 순서를 제공하고 Client가 20행만 사용한다면, 전체 Sort와 대량 처리를 피하는 계획이 초기 응답에 유리할 수 있습니다.

10. 화면 조회와 Batch 비교

Optimizer Goal은 업무 명칭만 보고 기계적으로 선택하지 않습니다. 실제 반환량, SQL의 결과 제한, 정렬 구조, 대표 Bind 값과 측정 목표를 확인해야 합니다.

11. 적용 범위와 우선순위

Statement Hint는 일반적으로 System·Session의 기본 Goal보다 우선합니다. 다만 Hint가 문법 오류, 충돌, 지원되지 않는 Statement 유형, Blocking Operation 등의 이유로 무시될 수 있으므로 실제 계획에서 확인해야 합니다.

전체 System이나 Session에 FIRST_ROWS_n을 일괄 적용하면 Batch와 대량 조회에도 초기 응답 목표가 적용될 수 있습니다. 특정 화면 SQL만 앞쪽 응답 최적화가 필요하다면 다음 순서를 우선 검토합니다.

1. 필요한 행 수와 정렬을 SQL에 정확히 표현한다.
2. 실제 사용 가능한 인덱스와 데이터 분포를 확인한다.
3. Statement 단위 Goal이 필요한지 검토한다.
4. 첫 n행과 전체 Fetch 성능을 모두 측정한다.

12. 검증해야 할 지표

Optimizer Goal을 비교할 때는 한 가지 시간만 측정하지 않습니다.

초기 응답
  → 첫 행 또는 첫 n행을 받을 때까지의 시간

전체 처리
  → 마지막 행까지 Fetch한 전체 경과시간

함께 확인할 항목은 다음과 같습니다.

• 첫 n행까지의 응답시간
• 전체 Fetch 완료시간
• Client가 실제 Fetch한 행 수
• Fetch Call 수와 Fetch Size
• Logical I/O와 Physical I/O
• Index Table Access 반복 횟수
• Sort·TEMP 사용량
• Stopkey와 Blocking Operation 존재 여부
• 실제 선택된 Access Path와 Join Method
• 대표 Bind 값·인기값·희소값의 차이

FIRST_ROWS(n) 계획은 첫 n행에서는 유리하고 전체 Fetch에서는 불리할 수 있으므로 두 경우를 분리하여 측정합니다.

13. 자주 혼동하는 판단과 정확한 기준

14. 핵심 정리

ALL_ROWS
  → 전체 Statement 완료 처리량과 최소 총 자원 사용을 중시한다.

FIRST_ROWS_n Parameter
  → n은 1, 10, 100, 1000 중 하나다.
  → System 또는 Session 기본 Goal을 설정한다.

FIRST_ROWS(n) Hint
  → 개별 Statement Block에서 처음 n행 응답을 중시한다.
  → 결과 행 수를 제한하지 않는다.
  → Blocking Operation 등의 조건에서는 무시될 수 있다.

Top-N SQL
  → ORDER BY와 FETCH FIRST n ROWS ONLY로
     업무가 필요한 결과 범위를 직접 표현한다.

Optimizer Goal은 특정 실행계획을 강제로 지정하는 기능이 아니라, 무엇을 빠르게 만들 것인지 Cost 비교 목표를 정하는 기능입니다. 올바른 적용 여부는 SQL의 결과 제한, 실제 Fetch 범위, 실행계획과 측정 결과를 함께 검증해야 합니다.