Sort Workarea 진단: Optimal·One-Pass·Multi-Pass·TEMP

핵심 요약

Sort Workarea는 ORDER BY, GROUP BY, DISTINCT, 분석 함수, Sort Merge Join 등 Memory-Intensive Operation이 사용하는 PGA 내부 작업 영역입니다.

Server Process PGA
├─ Session·Cursor State
├─ PL/SQL·Java 등 기타 PGA
└─ SQL Workarea
   ├─ SORT
   ├─ HASH JOIN
   ├─ GROUP BY
   ├─ BUFFER
   └─ BITMAP 관련 작업

Workarea 실행 상태는 다음 세 가지로 구분합니다.

OPTIMAL
  → Memory 안에서 완료
  → Workarea TEMP Spill 없음

ONE PASS
  → 일부 Run·Partition을 TEMP에 기록
  → 한 번의 추가 처리로 완료

MULTI-PASS
  → One-Pass에도 Memory 부족
  → TEMP Run·Partition을 여러 단계로 재처리

일반적인 Workarea I/O 비용 경향입니다.

OPTIMAL < ONE PASS < MULTI-PASS

그러나 다음도 함께 기억해야 합니다.

OPTIMAL
  ≠ SQL 전체가 최적
  ≠ 입력 Table I/O가 없음
  ≠ CPU·Blocking 비용이 작음

TEMP 사용
  ≠ 무조건 실패

고정된 Workarea 크기를 SQL이 독점하는 구조로 이해하면 안 됩니다.

WORKAREA_SIZE_POLICY=AUTO
→ PGA 목표와 현재 사용량
→ 동시 활성 Workarea 수
→ 각 Operation의 Memory 요구량
→ 개별 Workarea 크기 동적 조정

따라서 단독 실행에서는 OPTIMAL이었던 SQL이 운영 동시 부하에서는 ONE PASS로 바뀔 수 있습니다.

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → 소트 튜닝 → Workarea·PGA·TEMP 범위에서 OPTIMAL·ONE PASS·MULTI-PASS, Dynamic Performance View, SQL 구조 개선과 PGA 정책 검증을 다룹니다.

학습 목표

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

• PGA와 SQL Workarea의 관계를 설명한다.
• OPTIMAL·ONE PASS·MULTI-PASS의 차이를 설명한다.
• Sort 입력 A-Rows·전달 Row 폭·Sort Key 폭을 구분한다.
• External Sort의 Run 생성과 Merge 흐름을 설명한다.
• WORKAREA_SIZE_POLICY=AUTO의 동적 배정 원리를 설명한다.
• PGA_AGGREGATE_TARGET과 PGA_AGGREGATE_LIMIT의 역할을 구분한다.
• global memory bound가 동시 Workarea 증가 시 변할 수 있음을 설명한다.
• OMem·1Mem·Used-Mem·Used-Tmp를 해석한다.
• V$SQL_WORKAREA, V$SQL_WORKAREA_ACTIVE, V$SQL_WORKAREA_HISTOGRAM의 역할을 구분한다.
• V$PGA_TARGET_ADVICE 계열을 PGA 정책 검토에 활용한다.
• Multi-Pass가 발생했을 때 SQL 구조·추정·동시성 원인을 순서대로 진단한다.
• 단독 실행과 대표 동시 부하에서 변경 효과를 검증한다.

본문

Workarea와 PGA

PGA는 Server Process가 사용하는 Process 전용 Memory입니다. SQL Workarea는 그 일부입니다.

PGA 전체
  ≠ Workarea 전체

PGA에는
  Session Memory
  Cursor State
  PL/SQL·Java Memory
  Workarea
등이 함께 존재

하나의 SQL에도 여러 Workarea가 있을 수 있습니다.

WINDOW SORT
→ HASH GROUP BY
→ SORT ORDER BY

각 Operation은 별도 Memory 요구량과 실행 상태를 가질 수 있습니다.

또한 시스템에서는 다음이 동시에 발생합니다.

Session A: SORT ORDER BY
Session B: HASH JOIN
Session C: WINDOW SORT
Session D: GROUP BY

따라서 구분합니다.

OPTIMAL 실행

Sort 입력이 배정된 Workarea에 들어가면 Memory에서 정렬을 완료할 수 있습니다.

입력 Row 수집
→ Memory Sort
→ 정렬 결과 반환

OPTIMAL의 정확한 의미입니다.

마지막 Workarea 실행이 중간 Data를 TEMP Segment로 Spill하지 않고 Memory 안에서 완료된 상태

OPTIMAL이어도 다음 비용은 남습니다.

• 하위 Row Source의 Buffers·Reads
• Sort Key 비교
• Row·Key Copy
• CPU 사용
• Blocking 시간
• 동시 PGA 압력
• 결과 전송

예시입니다.

Sort A
  OPTIMAL
  입력 50,000행
  Elapsed 0.2초

Sort B
  OPTIMAL
  입력 50,000,000행
  Elapsed 25초

둘 다 OPTIMAL이지만 SQL 비용은 크게 다를 수 있습니다.

External Sort와 TEMP Run

Workarea에 전체 Sort Data를 유지할 수 없으면 Memory에서 처리 가능한 단위로 Run을 만듭니다.

입력 일부 수집
→ Memory에서 Sort
→ 정렬 Run을 TEMP에 Write

다음 입력 일부 수집
→ Memory에서 Sort
→ 다음 Run을 TEMP에 Write

Run들을 Merge
→ 최종 정렬 결과

추가 비용입니다.

• TEMP Segment 할당
• TEMP Write
• TEMP Read
• Run Merge CPU
• I/O Wait
• TEMP Tablespace 경쟁
• Storage Bandwidth 경쟁

Run 수와 Merge 단계는 입력량, Row 폭, Workarea 크기에 따라 달라질 수 있습니다.

OPTIMAL·ONE PASS·MULTI-PASS

OPTIMAL

Memory 안에서 완료
→ Workarea TEMP Spill 없음

ONE PASS

Memory 안에 모두 유지 불가
→ 일부 Run을 TEMP에 Write
→ 한 번의 추가 Merge로 완료

ONE PASS는 Disk를 사용하지만 반복적인 다단계 Merge까지는 필요하지 않은 상태입니다.

MULTI-PASS

한 번에 Merge할 Run 수도 Memory를 초과
→ 중간 Merge Run 생성
→ TEMP에 다시 Write
→ 여러 단계에서 Read·Merge 반복

MULTI-PASS는 같은 Data를 여러 번 읽고 쓰므로 일반적으로 큰 성능 저하의 신호입니다.

그러나 원인을 PGA가 작다 하나로 단정하지 않습니다.

• Sort 입력 A-Rows 과다
• Row·Key 폭 과다
• Predicate 적용 지연
• 불필요한 Join Row 증가
• 전체 Sort 후 일부만 사용
• Cardinality 과소 추정
• 동시 Workarea 증가
• 부적합한 Access Path·Join Method

Memory 요구량을 결정하는 요소

Sort 입력 A-Rows

Sort 바로 아래 자식 A-Rows
= 실제 Sort 입력 Row 수

원본 Table NUM_ROWS만으로 판단하지 않습니다.

원본 100,000,000행
→ WHERE 후 300,000행
→ Sort 입력 300,000행

전달 Row 폭

SELECT *
FROM orders
ORDER BY order_date;

SELECT order_id, order_date
FROM orders
ORDER BY order_date;

같은 행 수라도 첫 SQL은 Sort Operation과 상위 Operation 사이에 전달되는 Payload가 더 클 수 있습니다.

Sort Data Byte
≈ A-Rows × 전달 Row 폭

Sort Key 폭

ORDER BY customer_name,
         address,
         description

긴 문자열과 다수 Key는 다음 비용을 증가시킬 수 있습니다.

• Key 저장 Byte
• 비교 CPU
• Copy·Move
• TEMP Run Byte

Distinct Group 수

SORT UNIQUE, Hash Group By·Hash Unique에서는 입력 Row 수뿐 아니라 Distinct Key 수와 저장 Payload도 중요합니다.

동시 활성 Workarea

같은 SQL의 동일 Operation도 다음처럼 달라질 수 있습니다.

단독 실행
→ Workarea 200MB
→ OPTIMAL

동시 Workarea 100개
→ 개별 배정 감소
→ ONE PASS

AUTO Workarea Memory 관리

WORKAREA_SIZE_POLICY=AUTO에서는 Oracle이 Workarea를 자동으로 조정합니다.

공식적인 주요 입력입니다.

• 시스템의 현재 PGA 사용량
• PGA_AGGREGATE_TARGET
• 개별 Operation의 Memory 요구량
• 동시에 활성화된 Workarea 부하

AUTO 정책
→ Instance 전체 목표 안에서
→ 활성 Workarea에 Memory를 동적으로 배정

PGA_AGGREGATE_TARGET을 0보다 크게 설정하면 일반적으로 WORKAREA_SIZE_POLICY가 AUTO로 설정됩니다.

Target은 개별 SQL의 고정 할당량이 아니다

PGA_AGGREGATE_TARGET=8GB
  ≠ 한 Sort가 8GB 사용
  ≠ 모든 활성 Workarea가 8GB 사용

Target은 Instance PGA 관리 목표이며 Workarea 외 PGA 소비도 존재합니다.

global memory bound

V$PGASTAT의 global memory bound는 AUTO Mode에서 개별 Workarea가 사용할 수 있는 최대 크기 방향을 보여 주며 현재 Workarea 부하에 따라 동적으로 변합니다.

활성 Workarea 증가
→ global memory bound 감소 가능
→ 같은 SQL의 Spill 가능성 증가

개별 SQL 진단만으로 설명되지 않는 운영 시간대 변동을 분석할 때 유용합니다.

PGA_AGGREGATE_TARGET과 LIMIT

PGA_AGGREGATE_TARGET

• Instance PGA의 목표값
• AUTO Workarea Memory 관리의 기준
• 증가하면 더 많은 Workarea가 OPTIMAL 또는 ONE PASS로 실행될 가능성
• 절대 상한은 아님

Target
→ Oracle이 PGA 사용량을 관리하려는 목표
→ 순간적으로 초과 가능

PGA_AGGREGATE_LIMIT

• Instance PGA 사용의 Hard Limit 역할
• Target과 역할이 다름
• Limit에 도달하면 과도한 PGA를 사용하는 Call·Session 종료가 발생할 수 있음

Target
  → 조정 목표

Limit
  → 절대 상한 관리

PGA를 늘릴 때는 Operating System Memory, SGA, 병렬 실행, PL/SQL·Java·Session PGA와 함께 검토합니다.

DBMS_XPLAN Memory 통계

실제 Row Source 통계를 수집합니다.

SELECT /*+ GATHER_PLAN_STATISTICS */
       customer_id,
       SUM(amount)
FROM   orders
GROUP BY customer_id
ORDER BY SUM(amount) DESC;

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

ALLSTATS는 IOSTATS와 MEMSTATS의 Short Cut이며, LAST는 마지막 Cursor 실행 통계를 표시합니다.

환경과 Operation에 따라 다음 Column이 보일 수 있습니다.

예시입니다.

| Operation     | A-Rows | OMem | 1Mem | Used-Mem | Used-Tmp |
| SORT ORDER BY | 500000 | 120M | 10M  | 8M (1)   | 900M     |

확인합니다.

• Sort 자식 A-Rows
• OMem 대비 사용 Memory
• Pass 표시
• TEMP Byte
• E-Rows와 A-Rows 오차
• 상위 Operation의 누적 통계

Operation별 실제 실행 상태는 V$SQL_WORKAREA와 교차 검증합니다.

V$SQL_WORKAREA

완료된 Cursor의 Workarea별 통계를 제공합니다.

SELECT sql_id,
       child_number,
       operation_id,
       operation_type,
       policy,
       estimated_optimal_size,
       estimated_onepass_size,
       last_memory_used,
       last_execution,
       last_tempseg_size,
       max_tempseg_size,
       optimal_executions,
       onepass_executions,
       multipasses_executions
FROM   v$sql_workarea
WHERE  sql_id=:sql_id
AND    child_number=:child_number
ORDER BY operation_id;

주요 Column입니다.

LAST_TEMPSEG_SIZE가 NULL이면 마지막 Workarea 실행이 Disk로 Spill하지 않았음을 나타낼 수 있습니다.

주의합니다.

• 같은 SQL_ID에 여러 Child Cursor 가능
• Child별 Plan·Bind 특성이 다를 수 있음
• OPERATION_ID를 V$SQL_PLAN과 연결
• 마지막 실행과 누적 실행 횟수를 구분

V$SQL_WORKAREA_ACTIVE

현재 실행 중인 Workarea를 순간적으로 확인합니다.

SELECT sql_id,
       sql_exec_id,
       operation_id,
       operation_type,
       expected_size,
       actual_mem_used,
       max_mem_used,
       number_passes,
       tempseg_size
FROM   v$sql_workarea_active
WHERE  sql_id=:sql_id;

대표 해석입니다.

NUMBER_PASSES=0
→ 현재 OPTIMAL Mode 방향

NUMBER_PASSES>0
→ 추가 Pass 발생

TEMPSEG_SIZE
→ 현재 Workarea가 사용하는 TEMP Segment

장시간 Batch SQL의 Memory·TEMP가 실행 중 어떻게 변하는지 확인할 수 있습니다.

V$SQL_WORKAREA_HISTOGRAM

Instance Startup 이후 Workarea 크기 구간별 실행 분포를 보여 줍니다.

SELECT low_optimal_size,
       high_optimal_size,
       optimal_executions,
       onepass_executions,
       multipasses_executions
FROM   v$sql_workarea_histogram
ORDER BY low_optimal_size;

활용 목적입니다.

• 개별 SQL 원인 분석이 아니라 Instance 전체 경향 확인
• 어느 Workarea 크기 구간에서 ONE PASS·MULTI-PASS가 많이 발생하는지 파악
• PGA 정책 변경 전후 분포 비교
• 대표 Workload에서 Multi-Pass 감소 여부 확인

V$SQL_WORKAREA
→ 특정 Cursor·Operation

V$SQL_WORKAREA_ACTIVE
→ 현재 실행 중 Operation

V$SQL_WORKAREA_HISTOGRAM
→ Instance 전체 실행 분포

PGA Advice와 Instance 통계

V$PGASTAT

주요 항목입니다.

• aggregate PGA target parameter
• aggregate PGA auto target
• global memory bound
• total PGA allocated
• total PGA used for auto workareas
• over allocation count

해석 예시입니다.

aggregate PGA auto target가 매우 작음
→ Workarea 외 PGA 소비 또는 높은 부하 확인

global memory bound가 매우 작음
→ 동시 활성 Workarea 과다 가능

over allocation count 증가
→ PGA Target이 최소 요구를 충족하지 못한 이력 가능

V$PGA_TARGET_ADVICE

PGA_AGGREGATE_TARGET 변경 시 Cache Hit Percentage와 Over Allocation 변화를 예측하는 데 사용합니다.

V$PGA_TARGET_ADVICE_HISTOGRAM

Target 후보별로 예상 OPTIMAL·ONE PASS·MULTI-PASS 실행 분포를 세부적으로 확인할 수 있습니다.

주의합니다.

Advice
→ 과거·대표 Workload 기반 예상

실제 적용
→ 대표 동시 Workload로 재검증 필요

Multi-Pass 발견 시 진단 순서

정확한 Operation 식별

SQL_ID + Child Number + Operation Id

SORT ORDER BY, WINDOW SORT, HASH JOIN, GROUP BY 중 어떤 Operation인지 확인합니다.

입력 행 수 확인

Operation 자식 A-Rows
E-Rows vs A-Rows
Predicate 위치
Join 후 Row 증가

Row 폭 확인

• SELECT *
• LOB·긴 문자열
• 상위에서만 필요한 Column
• 불필요한 Expression
• Plan Bytes·Projection

Sort 필요성 확인

• 전체 Sort가 정말 필요한가?
• Top-N으로 제한 가능한가?
• Index Order 활용 가능한가?
• 분석 함수 사양 공유 가능한가?
• 선집계·Predicate Pushdown이 의미를 유지하는가?

통계와 Plan 확인

• Stale Statistics
• Histogram
• Column Group
• Bind Peeking·Child Cursor
• Dynamic Statistics
• Partition Statistics

동시성 확인

• 같은 SQL 동시 실행 수
• 병렬도
• 다른 Workarea 부하
• global memory bound
• TEMP Tablespace·Storage 부하

PGA 정책 검토

SQL 구조와 Cardinality를 개선해도 필수 대규모 Workarea가 남고 Instance Memory가 충분할 때 Target 조정을 검토합니다.

TEMP 사용량만으로 판단하지 않는다

다음 SQL을 비교합니다.

Plan A
TEMP 0
Buffers 10,000,000
CPU 60초
Elapsed 70초

Plan B
TEMP 2GB
Buffers 500,000
CPU 12초
Elapsed 18초

Plan B가 업무상 더 나을 수 있습니다.

반대 상황도 가능합니다.

TEMP 500GB
Multi-Pass
Elapsed 4시간
동시 Batch TEMP 경쟁

최종 판단 지표입니다.

• Elapsed Time
• CPU Time
• Buffers·Reads
• TEMP Read·Write
• Workarea Mode
• 첫 Row·전체 Fetch
• 반환 Row 수
• 동시 PGA·TEMP 영향
• 업무 SLA

목표는 TEMP 0이 아니라 정확한 결과를 허용 가능한 시간과 시스템 자원으로 처리하는 것입니다.

변경 전후 검증 절차

1. 동일 SQL 결과·Bind·Data Type을 사용한다.
2. SQL_ID·Child·Operation Id를 고정해 비교한다.
3. Sort 입력 A-Rows·Row 폭·E/A 오차를 기록한다.
4. OMem·1Mem·Used-Mem·Used-Tmp를 기록한다.
5. LAST_EXECUTION·LAST_TEMPSEG_SIZE를 확인한다.
6. First Row·End-of-Fetch를 같은 기준으로 측정한다.
7. 단독 실행과 대표 동시 부하를 모두 시험한다.
8. V$PGASTAT·Histogram으로 Instance 영향도 확인한다.
9. PGA Target 변경 시 Advice와 OS·SGA 여유를 검토한다.
10. 다른 SQL·Bind·병렬 실행의 회귀를 확인한다.

자주 혼동하는 판단