해시 조인 튜닝

해시 조인 튜닝

개발자를 위한 오라클 SQL 튜닝 내용에서 참고한 내용입니다.

Intro

해시 조인

• 두 개의 테이블을 조인한다고 가정할 때 작은 집합을 빠르게 읽어 해시 테이블(Hash Table)을 생성하여 해시 영역(Hash Area)에 저장

• 큰 테이블을 순차적으로 읽으면서 해시 함수(Hash Function)에 입력 값을 주어 해시 영역에 있는 해시 테이블 내에 해당 값이 존재하면 조인 집합에 저장

• 원하는 조인 결과를 도출하는 방식

• 일반적으로 대용량 테이블의 조인 연산의 효율성 비교

  • 해시 조인 방식 > 중첩 루프 조인 or 소트 머지 조인

• 작은 집합과 큰 집합이 있는 상황에서 오라클의 해시 조인은 극적인 성능 향상을 이루어 낸다.

• 하지만 해시 조인은 대용량 테이블 조인 시에 메모리가 많이 필요하기때문에 메모리 관리도 신경써야 한다.

해시 조인의 특성

• 해시 조인의 특성

  • 두 개의 테이블 중 한 테이블이 작은 집합이어야 성능 극대화가 가능하다.

  • 조인 조건이 반드시 equijoin('=') 방식이어야 한다.

• 일반적인 업무에서 두 개의 테이블이 1:M 관계에 놓였을 경우 1쪽의 집합이 훨씬 작은 용량의 테이블인 경우가 많다.

• 이러한 상황에서 1쪽 집합을 Build Input으로 하여 해시 조인을 수행한다면 극적인 상황을 이룰 수 있다.

Build Input과 Probe Input

• Build Input은 해시 조인 시 해시 영역에 저장하는 집합을 뜻한다.

• 중첩 루프 조인 기준으로 Outer 테이블이라고 이해하면 된다.

• Build Input은 반드시 작은 집합이어야 해시 영역에 메모리 공간을 초과하지 않고 들어갈 수 있다.

• Build Input이 지나치게 큰 테이블이 된다면 오히려 메모리 영역과 디스크 영역 사이에 페이징이 발생하게 되어 성능이 떨어질 위험이 있다.

• Probe Input은 해시 조인 시 해시 영역에 저장된 Build Input의 데이터가 해시 방식 접근으로 조인을 수행하는 집합을 뜻한다.

• 해시 영역에 생성된 해시 테이블이 구성되면 Probe Input을 순차적으로 스캔하면서 해시 함수를 통한 해시 테이블 검색을 하게 된다.

• 이때 Probe Input은 큰 용량의 테이블을 지정해야 한다.

해시 조인을 위한 메모리 관리

• 오라클의 PGA(Private Global Area) 영역은 해시 조인 시 사용하게 되는 메모리 영역이다.

• 해시 조인 시 Build Input이 PGA 영역에 모두 담길 정도로 작다면 최적 연산이 일어나면서 극적인 성능을 발휘한다.

• 하지만 Build Input이 너무 커서 PGA 영역에 모두 담지 못하게 되면 임시 공간 영역을 이용하게 되면서 디스크 I/O가 발생하게 된다.

  • 즉, 멀티 패스(Multi Pass)연산이 일어나게 된다.

  • -이러한 경우 PGA_AGGREGATE_TARGET 파라미터의 메모리 크기를 확장하여 성능 향상을 꾀할 수 있다.

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=6G;

해시 조인 튜닝

• 해시 조인 튜닝은 기존의 해시 조인 방식이 아닌 중첩 루프 조인이나

  소트 머지 조인 방식으로 되어 있는 SQL을 해시 조인 연산 조건에 부합되는지 분석한 후 해시 조인 방식으로 바꾸는 일련의 모든 활동을 뜻한다.

해시 조인 튜닝 관련 힌트

USE_HASH

• USE_HASH 힌트는 옵티마이저에 해시 조인을 유도한다.

• 사용법

  • 두 집합 중 작은 집합을 Build Input으로 지정하여 큰 집합인 Probe Input과 해시 조인을 수행

SELECT /*+ USE_HASH(테이블 테이블) */