Hash Join 고급 튜닝: Data Skew·Bloom Filter·Build Input 제어

핵심 요약

Hash Join이 선택됐다는 사실만으로 좋은 실행계획이라고 판단할 수 없습니다.

고급 진단에서는 다음 네 구간을 나누어 봅니다.

Build Row Source 생성
→ Hash Table 생성
→ Probe·Bucket 후보 비교
→ Spill·Parallel 분배·후속 처리

대표적인 성능 위험입니다.

Build Input 과대
→ Hash Table Memory 증가
→ TEMP Spill 가능성 증가

Duplicate Key
→ 정상 Match 조합 증가
→ Join 결과와 후속 처리 증가

Data Skew
→ Hot Bucket·큰 Hash Partition
→ CPU·TEMP·PX 불균형 증가

Bloom Filter 효과 부족
→ Probe Row가 거의 줄지 않음
→ 생성·전파 비용 대비 이점 제한

핵심 진단 질문입니다.

1. 실제 Build Row Source는 무엇인가?
2. Build A-Rows와 저장 Row 폭은 얼마인가?
3. Join Key 중복과 Hot Key가 결과 행 수를 얼마나 늘리는가?
4. Hash Workarea는 OPTIMAL·ONE PASS·MULTI-PASS 중 무엇인가?
5. Bloom Filter가 Probe Scan·PX 전송 Row를 실제로 줄였는가?
6. Join Order나 Input Swap 후 전체 작업량이 줄었는가?

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → 조인 순서와 조인 방식 → 해시 조인 고급 튜닝 범위에서 Data Skew, Bloom Filter, Build Input, Input Swap, Workarea·Parallel Runtime 검증을 다룹니다.

학습 목표

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

• Hash Collision·Duplicate Key·Data Skew를 구분한다.
• Duplicate Key의 Join 결과 Cardinality를 계산한다.
• Hot Key가 Bucket·Partition·Parallel Worker에 미치는 영향을 설명한다.
• Bloom Filter의 생성·검사·False Positive 특성을 설명한다.
• JOIN FILTER CREATE, JOIN FILTER USE, SYS_OP_BLOOM_FILTER를 읽는다.
• V$SQL_JOIN_FILTER의 적용 범위와 주요 Column을 설명한다.
• Build Input을 A-Rows와 Row 폭으로 비교한다.
• LEADING, USE_HASH, SWAP_JOIN_INPUTS, NO_SWAP_JOIN_INPUTS의 역할을 구분한다.
• 세 개 이상 Table의 각 Hash Join별 Build·Probe·결과를 분리해 기록한다.
• Predicate Pushdown·Projection 축소·선집계·Join Key 보완·통계정보로 입력을 줄인다.
• ALLSTATS LAST·MEMSTATS·Workarea·PX 통계로 개선 효과를 검증한다.

본문

Hash Join 고급 진단 구조

Hash Join 성능은 다음 네 구간으로 분해합니다.

다음 식으로 이해합니다.

Build Memory
≈ Build A-Rows
 × Hash Table에 보관할 Row 폭
 + Bucket·Pointer·관리 Overhead

Join 결과
≈ Key별 Build 중복 수
 × Key별 Probe 중복 수
의 합

HASH JOIN Operation의 A-Time이 크더라도 하위 Build·Probe Row Source 생성 비용과 상위 결과 처리 비용을 함께 봐야 합니다.

Collision·Duplicate·Skew 구분

Hash Collision

Hash 함수는 서로 다른 Key를 같은 Bucket에 배치할 수 있습니다.

hash(10) → Bucket 4
hash(30) → Bucket 4

이는 Hash Collision입니다.

Probe Row가 Bucket을 찾은 뒤 Oracle은 실제 Join Key를 다시 비교합니다.

같은 Hash 값
→ 후보 Bucket 탐색

실제 Join Key 비교
→ 진짜 Match만 반환

Collision은 후보 비교 CPU를 늘릴 수 있지만 SQL 결과의 중복 의미를 바꾸지 않습니다.

Duplicate Key

같은 Join Key를 가진 Row가 여러 건 존재하는 상태입니다.

Build Key A: 1,000행
Probe Key A: 20,000행

결과
= 1,000 × 20,000
= 20,000,000행

Duplicate는 업무 관계가 N:M이면 정상 결과일 수 있습니다.

반대로 원래 1:N 또는 1:1이어야 한다면 확인합니다.

• 복합 Join Key 일부가 누락됐는가?
• Dimension Key가 실제로 Unique한가?
• Version·유효기간 조건이 빠졌는가?
• Header와 Detail Grain을 잘못 연결했는가?
• 중복 Data가 적재됐는가?

DISTINCT는 원인을 숨기고 대형 Sort를 만들 수 있으므로 먼저 Join 관계를 확인합니다.

Data Skew

소수 Key가 전체 Row의 큰 비율을 차지하는 분포입니다.

NORMAL Key
→ 각 1,000행

UNKNOWN Key
→ 50,000,000행

Skew는 다음 문제를 만들 수 있습니다.

• 특정 Bucket의 후보 비교 증가
• 특정 Hash Partition 과대
• ONE PASS가 MULTI-PASS로 악화
• PX Server 한쪽으로 Row 집중
• 평균 NDV 기반 Cardinality 오차
• Duplicate 결과 조합 증가

세 개념을 구분합니다.

실제 Filter 범위에서 Skew 확인

전체 Table 분포만 확인하면 안 됩니다.

SELECT join_key,
       COUNT(*) AS row_count
FROM   target_table
WHERE  <실제 SQL과 같은 Filter>
GROUP BY join_key
ORDER BY row_count DESC
FETCH FIRST 20 ROWS ONLY;

다음을 구분합니다.

전체 Table은 균등
하지만 최근 1일·특정 지역에서 Hot Key 발생

전체 Table은 Skew
하지만 현재 Partition은 균등

Column Statistics를 확인합니다.

SELECT column_name,
       num_distinct,
       num_nulls,
       density,
       histogram,
       num_buckets,
       last_analyzed
FROM   user_tab_col_statistics
WHERE  table_name  = UPPER(:table_name)
AND    column_name = UPPER(:join_column);

상황별 우선 확인입니다.

Histogram은 Optimizer의 Cardinality 추정을 돕습니다. 실행 시 Skew 자체를 제거하지는 않습니다.

Build Input을 판단하는 기준

Build Input은 원본 Row 수가 작은 Table이 아니라 실제 Hash Table Memory Footprint가 작은 Row Source가 유리합니다.

Input A
100만 행 × 20Byte
≈ 20MB + Overhead

Input B
30만 행 × 500Byte
≈ 150MB + Overhead

Input B는 Row 수가 적지만 더 큰 Build가 될 수 있습니다.

Build Input은 다음일 수 있습니다.

• Base Table Filter 결과
• Partition Pruning 결과
• Inline View·CTE
• 선집계 결과
• 이전 Join 결과
• Transformation 이후 Row Source

Build에 필요한 정보입니다.

• Join Key
• 최종 결과에 필요한 Build Column
• 상위 Operation에 전달할 식별자·표현식
• Null·Outer Join 처리를 위한 정보

따라서 불필요한 Column 전달은 Build Memory와 TEMP Write Byte를 증가시킬 수 있습니다.

Workarea와 Skewed Partition

Hash Table이 PGA Workarea에 들어가지 않으면 Build와 Probe를 같은 Hash Partition 규칙으로 나눕니다.

Build
→ B0, B1, B2, B3

Probe
→ P0, P1, P2, P3

대응 Partition끼리 처리합니다.

B0 ↔ P0
B1 ↔ P1
B2 ↔ P2
B3 ↔ P3

Skew가 있으면 다음처럼 될 수 있습니다.

P0: 2GB
P1: 2GB
P2: 2GB
P3: 40GB

평균 Partition은 작아도 P3가 Workarea에 들어가지 않으면 재분할과 반복 TEMP I/O가 발생할 수 있습니다.

OPTIMAL

Hash Table과 처리 구조가 Memory 안에서 완료
→ Workarea TEMP Spill 없음

입력 Table·Index I/O와 Hash CPU는 여전히 존재합니다.

ONE PASS

일부 Build·Probe Partition을 TEMP에 기록
→ Partition별 한 번 추가 처리

MULTI-PASS

Disk Partition도 Memory에 들어가지 않음
→ 재분할
→ TEMP Write·Read 반복

MULTI-PASS의 원인을 PGA 부족 하나로 단정하지 않습니다.

• Build A-Rows 과소 추정
• 넓은 Build Row
• Predicate 적용 지연
• 잘못된 Join Order
• Hot Key Skew
• 동시 Workarea 증가

Bloom Filter 기본 원리

Bloom Filter는 적은 Memory로 값이 집합에 존재할 가능성을 검사하는 확률적 구조입니다.

Hash Join에서는 Build Key로 Filter를 만들고 Probe Scan에 적용할 수 있습니다.

Build Key 집합
→ Bloom Filter 생성

Probe Row 검사
├─ 집합에 없음이 확실
│  → 조기 제거
└─ 집합에 있을 가능성
   → 실제 Hash Join으로 전달

판단 특성입니다.

따라서 Bloom Filter는 최종 Join을 대체하지 않습니다.

Bloom 통과
→ 실제 Hash Bucket 탐색
→ 실제 Join Key 비교

Oracle은 Bloom Filter 사용 여부를 Cost에 따라 자동 판단할 수 있습니다.

Bloom Filter가 유리한 조건

대표적으로 작은 Dimension Filter와 큰 Fact Scan이 있습니다.

Dimension Filter
100만 행 → 500 Key

Fact Scan
10억 행

Fact Key 대부분이 500 Key에 없음
→ Bloom Filter로 Scan·PX 전송 전에 대량 제거 가능

효과가 큰 조건입니다.

• Build Key 집합이 비교적 작음
• Probe 입력이 매우 큼
• Probe Row 대부분이 Build Key와 불일치
• Parallel Query의 Data Transfer가 큼
• Partition Pruning이나 Storage Filtering과 결합 가능

효과가 제한적인 조건입니다.

• Probe Row 대부분이 실제 Match
• Build Key 집합이 매우 큼
• Filter가 너무 포화되어 False Positive 증가
• Probe 입력 자체가 이미 작음
• Filter 생성·전파 비용에 비해 제거 Row가 적음

실행계획에서 Bloom Filter 읽기

대표 Plan입니다.

HASH JOIN
  JOIN FILTER CREATE :BF0000
    SMALL BUILD ROW SOURCE
  JOIN FILTER USE :BF0000
    LARGE PROBE ROW SOURCE

Predicate Information에는 다음 형태가 나타날 수 있습니다.

SYS_OP_BLOOM_FILTER(:BF0000, probe_join_key)

확인합니다.

• JOIN FILTER CREATE
• JOIN FILTER USE
• 같은 :BF0000 식별자
• SYS_OP_BLOOM_FILTER
• CREATE 자식 A-Rows
• USE 아래 Scan A-Rows
• PX SEND 전후 Row 수
• 최종 Hash Join A-Rows

Plan에 Bloom Filter가 존재하는 것과 효과가 큰 것은 다릅니다.

V$SQL_JOIN_FILTER 해석

V$SQL_JOIN_FILTER는 병렬 Cursor에서 사용된 Join Filter의 특성을 보여 줍니다.

대표 Column입니다.

주의합니다.

Oracle Reference의 FILTERED와 PROBED 정의는 이름만으로 제거 Row 수를 단정하기 어렵습니다. 따라서 다음을 교차 검증합니다.

• Database Version의 Reference 정의
• JOIN FILTER USE 아래 A-Rows
• SYS_OP_BLOOM_FILTER 적용 전후 Row
• SQL Monitor의 Operation Row
• V$PQ_TQSTAT의 PX 전송 Row
• 최종 Hash Join 입력 A-Rows

단순히 FILTERED/PROBED 하나만으로 효과를 확정하지 않습니다.

Parallel 환경과 Bloom Filter

Bloom Filter는 Parallel Query에서 불필요한 Data Transfer를 줄이는 데 특히 유용합니다.

Probe Table Scan
→ Bloom 검사
→ PX SEND
→ Hash Join

Filter가 Scan 쪽에 적용되면 불일치 Row를 PX Network로 보내지 않을 수 있습니다.

확인합니다.

• JOIN FILTER USE 위치
• PX BLOCK ITERATOR
• PX SEND 전후 A-Rows
• V$PQ_TQSTAT의 Producer·Consumer Row
• SQL Monitor의 PX Server별 Row·Time
• 특정 Worker에 Hot Key 집중 여부

Bloom Filter가 많은 Row를 제거해도 통과한 Row가 특정 Hot Key에 몰리면 PX 불균형은 남을 수 있습니다.

Join Order·Method·Input Swap

LEADING

/*+ LEADING(d f) */

Row Source를 연결하는 순서를 유도합니다.

USE_HASH

/*+ LEADING(d f) USE_HASH(f) */

f를 다른 Row Source와 Hash 방식으로 연결하도록 유도합니다.

중요합니다.

USE_HASH
→ Join Method 유도

LEADING·ORDERED
→ Join Order 유도

USE_HASH만으로 Build Input을 직접 확정한다고 단정하지 않습니다.

SWAP_JOIN_INPUTS

Outline Data에는 다음 Hint가 나타날 수 있습니다.

SWAP_JOIN_INPUTS(row_source)
NO_SWAP_JOIN_INPUTS(row_source)

이들은 Hash Join의 입력 배치를 변경하거나 유지하는 고급 Hint입니다.

우선순위는 다음과 같습니다.

Cardinality·Statistics
→ Predicate 적용
→ Join Order
→ Build Row 폭
→ Workarea·Skew
→ 제한적인 Input Swap 실험

Hint 전후 확인합니다.

• 실제 Build Child
• Build A-Rows·Bytes
• O/1/M·Used-Tmp
• Bloom Filter CREATE 위치
• Probe Scan·PX 전송 Row
• 전체 Elapsed·CPU
• 다른 Bind·동시성 회귀

세 개 이상 Table의 Hash Join

각 HASH JOIN Operation마다 Build·Probe·결과가 별도로 존재합니다.

HASH JOIN 2
  DIM_B
  HASH JOIN 1
    DIM_A
    FACT

다음처럼 기록합니다.

Hash Join 1
Build A-Rows·Bytes
Probe A-Rows
Result A-Rows
Workarea·TEMP

Hash Join 2
Build A-Rows·Bytes
Probe A-Rows
Result A-Rows
Workarea·TEMP

큰 Fact를 한 번 흘려보내며 여러 작은 Hash Table을 Probe할 수도 있습니다.

반대로 이전 Join의 큰 결과가 다음 Hash Join Build가 되면 Memory와 TEMP가 급증할 수 있습니다.

Plan의 위아래만 보고 모든 Hash Join의 Build를 하나로 묶지 않습니다.

입력을 줄이는 튜닝 방법

Predicate를 일찍 적용

50,000,000행
→ 선택적 Filter
→ 200,000행 Build

Predicate Information과 자식 A-Rows로 실제 Pushdown 여부를 확인합니다.

Projection 축소

100만 행 × 20Byte
vs
100만 행 × 500Byte

필요한 Join Key와 결과 Column만 전달하면 Hash Memory와 TEMP Byte를 줄일 수 있습니다.

의미가 보존되는 선집계

WITH order_sum AS (
  SELECT customer_id,
         SUM(order_amount) AS total_amount
  FROM   orders
  WHERE  order_date >= :from_date
  GROUP BY customer_id
)
SELECT c.customer_id,
       s.total_amount
FROM   customers c
JOIN   order_sum s
  ON   s.customer_id=c.customer_id;

확인합니다.

• 집계 전후 Grain
• Detail Column 필요 여부
• Dimension Key Unique
• Outer Join 미Match 보존
• NULL·중복 의미

빠진 Join Key 복원

ON d.order_id   = h.order_id
AND d.version_no = h.version_no

업무 관계가 복합 Key인데 일부 조건이 누락되면 Duplicate·결과 조합과 CPU가 폭증할 수 있습니다.

통계정보 보완

• 최신 Table·Column·Index Statistics
• Hot Key Histogram
• 상관 조건 Column Group
• Partition·Global Statistics
• Bind별 Child Cursor
• 실제 Filter 범위의 Key 빈도

Runtime 검증 절차

실행합니다.

SELECT /*+ GATHER_PLAN_STATISTICS */
       ...
FROM   ...;

실제 Cursor를 확인합니다.

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

검증 순서입니다.

1. SQL_ID·Child·Bind·Fetch·Parallel을 고정한다.
2. 각 HASH JOIN의 두 Child A-Rows·Bytes를 기록한다.
3. 실제 Build 방향과 Outline을 확인한다.
4. E-Rows·A-Rows가 처음 크게 갈라지는 지점을 찾는다.
5. Duplicate·Hot Key와 Hash Join 결과 A-Rows를 확인한다.
6. OMem·1Mem·O/1/M·Used-Tmp를 확인한다.
7. V$SQL_WORKAREA·ACTIVE로 Memory·Pass·TEMP를 확인한다.
8. JOIN FILTER CREATE·USE와 Bloom Predicate를 확인한다.
9. V$SQL_JOIN_FILTER·PX Row로 Bloom 효과를 교차 검증한다.
10. Hint 전후 Elapsed·CPU·Buffers·TEMP와 다른 Bind·동시 실행을 비교한다.

자주 혼동하는 판단