Sort 부하 줄이기: 입력 행 수·Row 폭·연산 위치

핵심 요약

Sort 튜닝에서 가장 먼저 줄여야 하는 것은 Memory Parameter가 아니라 Sort에 들어가는 Data 양입니다.

Sort 작업량
≈ Sort 입력 A-Rows
 × Sort 단계에 전달되는 Row 폭
 + Sort Key 비교 비용
 + TEMP Spill 비용

따라서 기본 튜닝 순서는 다음과 같습니다.

Predicate·Partition Pruning으로 행 수 감소
→ 불필요한 Join·중복 Row 감소
→ 결과 의미가 같다면 선집계
→ Narrow Row로 정렬·Top-N
→ 선택된 행만 Wide Row 조회
→ Index Order·STOPKEY 가능성 비교
→ 남은 Workarea·PGA 검토

핵심 검증 질문입니다.

1. Sort 바로 아래 실제 A-Rows는 몇 행인가?
2. Sort Operation이 위쪽에 전달하는 Column과 Row 폭은 얼마인가?
3. Predicate가 Sort 이전 Row Source에 실제로 적용됐는가?
4. Join·Aggregation·Top-N 위치를 바꿔도 결과 Grain이 같은가?
5. Sort를 제거한 Index Plan이 ROWID Random Access를 폭증시키지 않는가?
6. Query Transformation 후 작성한 SQL 구조가 실제 Plan에도 유지됐는가?

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → 고급 SQL 튜닝 → 소트 튜닝 범위에서 입력 행 수·Row 폭·Predicate Pushdown·선집계·Top-N·Index Order와 Runtime 검증을 다룹니다.

학습 목표

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

• Sort 입력 A-Rows와 원본 Table NUM_ROWS를 구분한다.
• 전달 Row 폭과 Sort Key 폭이 Memory·TEMP에 미치는 영향을 설명한다.
• Predicate Pushing과 View Merging이 Sort 입력 감소에 미치는 영향을 설명한다.
• Join 결과 Grain과 Top-N 대상 Grain을 구분한다.
• 선집계가 결과를 보존하는 조건과 결과를 바꾸는 조건을 설명한다.
• Narrow Row Top-N 후 Wide Row 조회 원리를 설명한다.
• Sort Key 계산식과 출력 전용 비싼 표현식의 평가 위치를 구분한다.
• SORT ORDER BY STOPKEY가 있어도 전체 후보 Scan이 가능함을 설명한다.
• Index Range·Full Scan과 Index Fast Full Scan의 정렬 제공 차이를 설명한다.
• PROJECTION, MEMSTATS, V$SQL_WORKAREA로 실제 Sort Row·Byte·Spill을 검증한다.
• 동일 결과·순서·동점·NULL·Join Grain을 유지하며 전후 Plan을 비교한다.

1. Sort 작업량의 출발점

1.1 원본 Row 수와 Sort 입력 Row 수

ORDERS 원본 100,000,000행
→ 최근 완료 주문 300,000행
→ SORT ORDER BY 입력 300,000행

Sort 비용을 판단할 때는 원본 Table NUM_ROWS가 아니라 실행계획에서 **Sort Operation 바로 아래 Row Source의 실제 A-Rows**를 봅니다.

SORT ORDER BY
  FILTER·JOIN·TABLE ACCESS

하위 Operation이 300,000행을 생산했다면 Sort 입력도 그 방향입니다.

1.2 Row 폭

같은 행 수라도 전달되는 Byte가 다르면 Sort 부담이 달라집니다.

1,000,000행 × 40Byte
≈ 40MB + Sort 관리 Overhead

1,000,000행 × 800Byte
≈ 800MB + Sort 관리 Overhead

실제 Row 폭은 다음으로 확인합니다.

• 실행계획 Bytes
• +PROJECTION
• Select List와 Data Type
• 상위 Operation에 전달하는 Column
• 긴 문자열·LOB Locator·Expression

1.3 Sort Key 폭

ORDER BY customer_id

ORDER BY customer_name, address, description

긴 문자열과 여러 Sort Key는 비교 CPU와 Key 저장 Byte를 증가시킬 수 있습니다.

2. Predicate를 Sort 전에 적용한다

SELECT order_id,
       customer_id,
       order_date,
       amount
FROM   orders
WHERE  status='COMPLETED'
AND    order_date>=DATE '2026-01-01'
ORDER BY amount DESC,
         order_id DESC;

바람직한 흐름입니다.

Partition·Index·Table Access
→ status·date Predicate
→ 통과 Row만 Sort

2.1 Predicate Pushing

View가 Merge되지 않더라도 Optimizer는 바깥 Query Block의 관련 Predicate를 안쪽 View에 밀어 넣을 수 있습니다.

Outer Query Predicate
→ Unmerged View 안으로 Push
→ Index Access·Filter에 사용
→ View 결과와 Sort 입력 감소 가능

실제 적용은 Predicate Information과 각 Operation의 A-Rows로 확인합니다.

2.2 View Merging

Optimizer는 View Query Block을 Outer Query Block과 Merge해 전체 Join Order·Predicate·Access Path를 함께 최적화할 수 있습니다.

작성한 Inline View 경계
  ≠ 실제 실행 경계 보장

따라서 “Inline View 안에서 Column을 줄였으니 반드시 그 폭으로 Sort된다”거나 “함수가 반드시 Top-N 이후 실행된다”고 SQL Text만 보고 단정하지 않습니다.

확인 항목입니다.

• Query Block·Alias
• +PROJECTION
• Table Access 위치
• Function이 포함된 Projection Operation
• Sort 자식 A-Rows·Bytes
• View Operation 존재 여부

2.3 Pushdown이 제한되는 경우

다음 의미 때문에 Predicate를 무조건 아래로 이동할 수 없습니다.

• Outer Join의 보존 Row
• Analytic Function 계산 집합
• Set Operation
• Aggregate 전후 조건
• Non-deterministic·Side-Effect Function
• Top-N의 대상 집합
• Query Block 경계와 Transformation 제약

결과 의미가 같은 경우에만 이동합니다.

3. Join으로 행이 증가한 뒤 Sort되는 구조

SELECT c.customer_name,
       o.order_id,
       o.order_date,
       o.amount
FROM   customers c
JOIN   orders o
  ON   o.customer_id=c.customer_id
ORDER BY o.amount DESC,
         o.order_id DESC;

고객 한 명에 주문이 여러 건이면 결과 Grain은 주문입니다.

CUSTOMERS 100,000행
ORDERS 10,000,000행
Join 결과 약 10,000,000행
→ Sort 입력 약 10,000,000행

Join 전에 행을 줄이려면 업무가 어떤 Grain을 요구하는지 먼저 확인합니다.

4. Top-N 위치와 결과 Grain

4.1 Join 후 Top-N

SELECT ...
FROM orders o
JOIN order_items i
  ON i.order_id=o.order_id
ORDER BY o.order_date DESC,
         o.order_id DESC,
         i.item_id
FETCH FIRST 20 ROWS ONLY;

의미입니다.

주문·상품 Join Row 중 상위 20행

4.2 주문 Top-N 후 Detail Join

WITH recent_orders AS (
  SELECT order_id,
         order_date
  FROM   orders
  ORDER BY order_date DESC,
           order_id DESC
  FETCH FIRST 20 ROWS ONLY
)
SELECT ...
FROM recent_orders r
JOIN order_items i
  ON i.order_id=r.order_id
ORDER BY r.order_date DESC,
         r.order_id DESC,
         i.item_id;

의미입니다.

최근 주문 20건
→ 선택된 주문의 모든 상품
→ 최종 Row는 20행보다 많을 수 있음

두 SQL은 1:N 관계에서 결과가 다릅니다.

4.3 고객 조건이 있는 Top-N

다음 요구는 “VIP 고객 주문 중 상위 20건”입니다.

FROM orders o
JOIN customers c
  ON c.customer_id=o.customer_id
WHERE c.grade='VIP'
ORDER BY o.amount DESC
FETCH FIRST 20 ROWS ONLY

고객 Filter를 적용하기 전에 주문 20건을 고르면 VIP가 아닌 주문이 포함되어 결과가 달라질 수 있습니다.

Top-N 이동 가능 여부
= Join·Filter가 Top-N 포함 집합을 바꾸는가?

5. 선집계로 Row 수 줄이기

고객별 주문합계가 목적입니다.

5.1 Join 후 집계

SELECT c.customer_id,
       c.customer_name,
       SUM(o.amount) total_amount
FROM   customers c
JOIN   orders o
  ON   o.customer_id=c.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY total_amount DESC,
         c.customer_id;

5.2 Detail 선집계

WITH order_sum AS (
  SELECT customer_id,
         SUM(amount) total_amount
  FROM   orders
  GROUP BY customer_id
)
SELECT c.customer_id,
       c.customer_name,
       s.total_amount
FROM   customers c
JOIN   order_sum s
  ON   s.customer_id=c.customer_id
ORDER BY s.total_amount DESC,
         c.customer_id;

Detail 10,000,000행이 고객 100,000 Group으로 줄면 다음 작업이 감소할 수 있습니다.

• Join 입력·결과 Row
• Hash Table·Sort 입력
• Parallel 전송
• Client 결과 전 처리량

5.3 선집계의 정합성 조건

반드시 확인합니다.

• 최종 결과 Grain과 Aggregate Grain이 같은가?
• Dimension Join이 Detail 포함·제외를 바꾸는가?
• Dimension Key가 Unique한가?
• Outer Join의 미Match Dimension을 보존해야 하는가?
• Join 후 Predicate가 Aggregate 대상 Row를 바꾸는가?
• Dimension 중복이 집계값을 복제하지 않는가?
• Detail Column이 최종 결과에 필요한가?

선집계는 결과 의미가 보존될 때만 적용합니다.

6. Narrow Row로 Top-N 후 Wide Row 조회

SELECT *
FROM product
ORDER BY sales_amount DESC,
         product_id
FETCH FIRST 100 ROWS ONLY;

SELECT *에 긴 설명·JSON·LOB Column이 포함되면 Sort Row가 넓어질 수 있습니다.

WITH top_product AS (
  SELECT product_id,
         sales_amount
  FROM   product
  ORDER BY sales_amount DESC,
           product_id
  FETCH FIRST 100 ROWS ONLY
)
SELECT p.*
FROM   top_product t
JOIN   product p
  ON   p.product_id=t.product_id
ORDER BY t.sales_amount DESC,
         t.product_id;

개념입니다.

Narrow Row로 Sort·Top-N
→ 선택된 100개 Key
→ Wide Row Table Access

이 방식은 Late Materialization 또는 Deferred Table Access 관점으로 이해할 수 있습니다.

6.1 Trade-off

• 선택된 소수 Row만 Wide Column 조회
• Sort Memory·TEMP 감소 가능
• Table을 다시 방문하는 비용 발생
• View Merging으로 기대한 경계가 사라질 수 있음
• LOB Locator와 실제 LOB Access 방식은 별도 확인
• 결정적 ORDER BY가 필요

실제 PROJECTION, Table Access Starts·A-Rows, Sort Bytes를 확인합니다.

7. 계산식과 함수의 실행 위치

7.1 Sort Key 표현식

ORDER BY amount * exchange_rate DESC

정렬 기준 자체가 표현식이므로 모든 Sort 후보에 대해 계산해야 합니다.

7.2 출력 전용 비싼 함수

SELECT order_id,
       expensive_format_function(order_id),
       amount
FROM   orders
ORDER BY amount DESC
FETCH FIRST 100 ROWS ONLY;

업무상 가능하면 Top-N 후보를 먼저 정한 뒤 비싼 출력 함수를 실행하는 구조를 검토할 수 있습니다.

WITH top_order AS (
  SELECT order_id,
         amount
  FROM   orders
  ORDER BY amount DESC,
           order_id DESC
  FETCH FIRST 100 ROWS ONLY
)
SELECT order_id,
       expensive_format_function(order_id),
       amount
FROM   top_order
ORDER BY amount DESC,
         order_id DESC;

그러나 실제 함수 호출 위치는 다음 영향을 받습니다.

• View Merging
• Projection Pruning
• Scalar Expression 이동
• Function Deterministic·Side Effect
• SQL·PL/SQL Context Switching

실행계획 Projection과 Trace·SQL Monitor 등으로 호출 횟수를 검증합니다.

8. Top-N과 STOPKEY

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

row_limiting_clause는 반환 Row 수를 제한하며, 일관된 결과를 위해 결정적 ORDER BY를 지정해야 합니다.

가능한 Plan입니다.

SORT ORDER BY STOPKEY
  TABLE ACCESS FULL ORDERS

8.1 STOPKEY가 있어도 전체 Scan이 가능한 이유

입력이 정렬되지 않았다면 상위 20개를 확정하려고 모든 후보의 Sort Key를 확인해야 할 수 있습니다.

STOPKEY
→ Memory에 유지하는 상위 후보 수 감소 가능

하지만
→ 하위 Table Scan A-Rows는 전체 후보일 수 있음

따라서 다음을 확인합니다.

• Table·Index Access A-Rows
• Sort Operation A-Rows
• Buffers·Reads
• Used-Mem·Used-Tmp
• First Row·End-of-Fetch

9. Index Order와 Sort 생략

CREATE INDEX orders_status_date_ix
ON orders(status, order_date DESC, order_id DESC);

SELECT order_id,
       order_date,
       amount
FROM   orders
WHERE  status='COMPLETED'
ORDER BY order_date DESC,
         order_id DESC;

선두 status가 Equality로 고정되고 Scan 방향이 호환되면 Index가 필요한 순서를 제공할 수 있습니다.

9.1 정렬 순서를 제공하는 Scan

• INDEX RANGE SCAN
• INDEX RANGE SCAN DESCENDING
• INDEX FULL SCAN
• INDEX FULL SCAN DESCENDING

Plan과 Predicate에 따라 순서 제공 가능성을 확인합니다.

9.2 Index Fast Full Scan

INDEX FAST FULL SCAN은 Multiblock I/O로 Index Block을 정렬되지 않은 순서로 읽으므로 Sort를 제거하는 순서 제공 경로로 사용할 수 없습니다.

INDEX FULL SCAN
→ Index Key 순서 제공 가능

INDEX FAST FULL SCAN
→ 순서 보장 없음

9.3 Sort 제거 Trade-off

Index Order Plan
→ Sort 제거
→ Index Leaf Scan
→ amount 조회를 위한 Table by ROWID

반환 Row가 많고 Clustering Factor가 불리하면 다음 Plan이 더 저렴할 수 있습니다.

Full Table Scan
→ Filter
→ Sort

비교합니다.

• Buffers·Reads
• Table by ROWID
• Clustering Factor
• TEMP
• CPU·Elapsed
• First Row·Full Fetch
• Index 유지·DML 비용

10. 불필요하거나 중복된 Sort 찾기

다음 Plan은 각각 다른 목적일 수 있습니다.

WINDOW SORT
→ HASH GROUP BY
→ SORT ORDER BY

무조건 중복이라고 판단하지 않습니다.

확인합니다.

• Window Partition·Order와 최종 ORDER BY가 호환되는가?
• GROUP BY가 중간 순서를 깨뜨리는가?
• Inline View의 ORDER BY가 Top-N·Analytic 의미에 필요한가?
• UNION 중복 제거 후 바깥 DISTINCT가 중복되는가?
• Pagination과 ROW_NUMBER가 같은 Rank를 두 번 계산하는가?
• Query Block별 ORDER BY가 외부 결과에 실제 의미가 있는가?

Optimizer는 SQL이 비절차적이므로 View를 Merge하고 Operation을 재배치하거나 불필요한 정렬을 제거할 수 있습니다.

11. 실행계획과 Workarea 검증

SELECT /*+ GATHER_PLAN_STATISTICS */
       order_id,
       customer_id,
       amount
FROM   orders
WHERE  status='COMPLETED'
ORDER BY amount DESC,
         order_id DESC
FETCH FIRST 100 ROWS ONLY;

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    :sql_id,
    :child_no,
    'ALLSTATS LAST +MEMSTATS +PREDICATE +PROJECTION +ALIAS +NOTE'
  )
);

11.1 핵심 지표

ALLSTATS는 I/O와 Memory 통계를 포함할 수 있고 LAST는 마지막 Cursor 실행 통계를 보여 줍니다.

11.2 V$SQL_WORKAREA

SELECT sql_id,
       child_number,
       operation_id,
       operation_type,
       estimated_optimal_size,
       estimated_onepass_size,
       last_memory_used,
       last_execution,
       last_tempseg_size
FROM   v$sql_workarea
WHERE  sql_id=:sql_id
AND    child_number=:child_no
ORDER BY operation_id;

OPERATION_ID로 실행계획의 해당 Sort와 연결합니다.

12. 변경 전후 결과 검증

Sort 튜닝은 결과 의미를 바꾸기 쉽습니다.

결정적 순서 예시입니다.

ORDER BY amount DESC,
         order_id DESC

동점 처리 Column이 없으면 Top-N·Pagination 결과가 재실행에서 달라질 수 있습니다.

13. 실전 튜닝 절차

1. Sort Operation의 목적을 확인한다.
2. Sort 바로 아래 A-Rows를 기록한다.
3. Projection·Bytes로 Row 폭을 확인한다.
4. Predicate·Partition Pruning을 Sort 전에 적용할 수 있는지 본다.
5. Join으로 불필요한 Row 증가가 있는지 확인한다.
6. 의미가 같다면 선집계·Semi Join을 검토한다.
7. Top-N 대상 Grain을 확인하고 Row Limit를 정확한 Query Block에 둔다.
8. Narrow Row Top-N 후 Wide Row 조회를 검토한다.
9. Index Order와 Full Scan+Sort의 전체 비용을 비교한다.
10. MEMSTATS·V$SQL_WORKAREA와 결과 정합성·동시 부하 회귀를 검증한다.

자주 혼동하는 판단