조인 튜닝

NL 조인

인덱스를 이용한 조인이기 때문에, 인덱스 원리를 이해하고 있다면 쉽다고 한다.
과연 정말 쉬운지 한 번 읽으면서 정리해보자…
(사실 이미 머리에서 1~3장의 내용이 흐릿하다…)

기본 메커니즘

첫 번째 조건을 만족하는 데이터들을 찾은 후, 각 데이터마다 다른 테이블을 한 번씩 살펴보며 다음 조건과 일치하는 것을 찾는 것을 반복하는 방식이다.

일반적으로, Outer(Driving)와 Inner(Driven) 양쪽 테이블 모두 인덱스를 이용하고, Outer 테이블이 크지 않다면 인덱스를 이용하지 않고 Table Full Scna을 사용할 수도 있다. 하지만, Inner 테이블은 인덱스를 사용해야만 한다.

이유는 Outer 루프는 한 번만 스캔이 일어나지만, Inner 루프는 Outer 루프 결과의 수만큼 반복해야 하기 때문에 인덱스를 이용하지 않고 매번 테이블 전체를 스캔한다면 엄청난 I/O가 발생하기 때문이다.

실행 순서는 아래와 같다.

1. Outer 루프 조건에 해당하는 건을 인덱스에서 찾는다.
2. 인덱스로 테이블의 레코드를 찾아간다.
3. 테이블에서 가져온 데이터로 Inner 루프 조건에 해당하는 건을 인덱스에서 찾는다.
4. 인덱스로 테이블의 레코드를 찾아간다.
5. Inner 루프를 조건과 불일치할 때까지 반복한다.
6. Outer 조인을 반복한다.

소트 머지 조인과 해시 조인도 Sort Area나 Hash Area에 가공해 둔 데이터를 사용한다는 점만 다르고, 기본적인 조인 프로세싱은 다르지 않다.

NL 조인 실행계획 제어

매커니즘에서 배웠던 것처럼 실행계획을 살펴보면 위쪽 테이블 기준으로 아래쪽 테이블과 NL 조인을 하고, 테이블 액세스 시 인덱스를 이용하는 것을 확인할 수 있다.

use_nl(A) 힌트를 사용해 A와 조인할 때는 NL 조인을 사용하라고 지정할 수 있다.
ordered 힌트를 사용해 from 절에 명시한 순서대로 조인을 수행하게 할 수 있다.
leading(C, A, D, B) 힌트를 사용해 from 절과 상관없이 조인 순서를 정할 수 있다.
ordered나 leading 힌트를 사용하지 않는 경우에는 옵티마이저 스스로 순서를 정한다.

NL 조인 수행 과정 분석

NL 조인 시 조건절은 적힌 순서대로 실행되는 것이 아니라, Outer 테이블의 조건부터 처리한 후에 Inner 테이블의 조건을 처리하게 되고, 인덱스는 각 테이블의 첫 조건에 해당하는 인덱스를 사용한다.

NL 조인 튜닝 포인트

Outer 테이블의 인덱스를 읽고 나서 테이블에 액세스 할 때, 많은 양의 랜덤 액세스가 발생했고, 후행 조건으로 필터링을 많이 할 수 있어 랜덤 액세스를 줄일 수 있다면 해당 컬럼을 인덱스에 추가하는 방안을 고려할 수도 있다.

Ineer 인덱스를 탐색하는 횟수는 조인 액세스 횟수와 비례해서 증가하고, 당연히 탐색 횟수가 많을수록 성능이 느려진다. 조인 액세스 횟수는 결국 Outer의 조건에서 결정되는 것이기 때문에 적절한 조건을 통해 필터링을 해줘서 줄여야 한다.

올바른 조인 메소드 선택

OLTP 환경에서 튜닝할 때는 우선적으로 NL 조인을 고려하는 것이 일반적인 순서인데, 성능이 느린 쿼리가 있다면, 각 단계의 수행 일량을 분석에 과도한 랜덤 액세스가 발생하는 지점을 파악한 후, 조인 순서 변경 혹은 더 효율적인 인덱스를 통해 랜덤 액세스 발생량을 줄일 방법을 생각해야 한다.

여러 방안을 검토해봐도 NL 조인으로 성능을 낼 수 없다면, 그때 가서 소트 머지 조인이나 해시 조인을 검토해야 한다.

NL 조인 특징 요약

1. 랜덤 액세스 위주의 조인 방식이기 때문에 대량 데이터 조인 시 NL 조인은 불리할 수 밖에 없다.
2. 조인을 한 레코드씩 순차적으로 진행해 아무리 큰 테이블과 조인해도 매우 빠른 응답 속도를 낼 수 있다. 즉, 한 줄씩 순차적이라 부분 범위 처리가 가능해, 필요한 데이터만 빠르게 검색이 가능하다.
3. 인덱스 구성 전략에 따라 조인 효율이 크게 달라진다.

그래서 소량 데이터를 처리하거나 부분범위 처리가 가능한 OLTP 시스템에 적합한 조인이다.

NL 조인 튜닝 실습

튜닝 시 Outer 조건에서 불필요한 테이블 액세스를 줄이는 것도 중요하지만, 불필요한 액세스를 줄인 후에도 블록 요청 횟수도 살펴봐서 인덱스에서 필요한 레코드를 얻기 위해 읽은 총 레코드 수를 줄이는 것도 중요하다.

테이블 액세스와 읽는 레코드 수까지 모두 줄였다면, Outer가 Inner 보다 작은지 확인한 후 그렇지 않다면 조인 순서를 변경하는 것을 고려해볼 수도 있다. 조인 순서를 변경해도 별 차이가 없다면 소트 머지 조인과 해시 조인을 검토해봐야 한다.

NL 조인 확장 메커니즘

테이블 Prefetch
인덱스를 이용해 테이블을 액세스하다 디스크 I/O가 필요하면, 곧 읽게 될 블록까지 미리 읽어서 버퍼캐시에 적재하는 기능이다.

배치 I/O
디스크 I/O Call을 쌓아두고, 일정량 쌓이면 한 번에 처리하는 기능이다.

NL 조인 시 Inner 테이블을 미리 액세스 해서 읽은 후 버퍼 캐시에 적재하거나, Inner 테이블 액세스 시 배치 I/O를 사용해 콜 횟수를 줄이는 식으로 사용된다.

Inner 테이블을 모두 버퍼캐시에서 읽게 된다면 성능에 차이가 없고, 데이터 출력 순서도 같지만, 일부를 디스크에서 읽게 된다면 성능 차이가 나타날 수 있다. 또한, 배치 I/O는 정렬 순서를 보장하지 않기 때문에 주의해야 한다.

배치 I/O를 사용해도 Outer 선에서 정렬을 다룬다면 정렬은 유지된다. Inner에 대해서만 정렬이 되지 않을 뿐이다.

NL 조인 자가 진단

-- 인덱스 PRA_HST_STC_N1 : SALE_ORG_ID + STRD_GRP_ID + STRD_ID + STC_DT
-- Outer : a
-- Inner : b
select *
	FROM PRA_HST_STC a, ODM_TRMS b
WHERE 
	a.SALE_ORG_ID = :sale_org_id
	AND b.STRD_GR_ID = a.STRD_GRP_ID
	AND b.STRD_ID = a.STRD_ID
ORDER BY a.STC_DT desc

Outer 테이블인 PRA_HST_STC에서 조건에 사용되는 컬럼은 SALE_ORG_ID뿐이고 정렬에 STC_DT 컬럼이 사용된다. 따라서 SALE_ORG_ID + STC_DT 로 인덱스를 구성해줘야 불필요한 컬럼을 인덱스에서 제외하고, 정렬 연산을 생략할 수 있다.

소트 머지 조인

조인 컬럼에 인덱스가 없는 경우, 대량 데이터 조인으로 인해 인덱스가 효율적이지 않은 경우에 옵티마이저는 NL 조인이 아닌 소트 머지 조인 혹은 해시 조인을 선택한다.

SGA vs. PGA

SGA에 캐시된 데이터는 여러 프로세스가 공유하지만, 락으로 인해 동시에 액세스할 수는 없다. 마찬가지로 DB 버퍼 캐시에서도 블록을 읽으려면 버퍼 락을 얻어야 한다.

PGA는 각 오라클 서버 프로세스에 할당된 고유 메모리 영역으로, 프로세스에 종속적인 고유 데이터를 저장한다. 다른 프로세스와 공유하지 않기 때문에 래치 메커니즘이 불필요해 SGA의 버퍼캐시보다 빠르게 데이터를 읽을 수 있다. 메모리 공간이 부족하다면 Temp 테이블 스페이스를 사용한다.

기본 메커니즘

이름 그대로 소트와 머지 두 단계로 진행된다.

1. 소트 단계 : 양쪽 집합을 조인 컬럼 기준으로 정렬
2. 머지 단계 : 정렬한 양쪽 집합을 병합

NL 조인 기준으로 순서를 정리해보자

1. Outer 조건에 해당하는 데이터를 읽는다.
2. 읽은 결과집합을 조인 컬럼 기준으로 정렬한다.
3. 정렬한 결과집합을 PGA의 Sort Area에 저장한다.
4. PGA에 담을 수 없는 크기라면 Temp 테이블스페이스에 저장한다.
5. Inner 조건에 해당하는 데이터를 읽는다.
6. 읽은 결과집합을 조인 컬럼 기준으로 정렬한다.
7. 정렬한 결과집합을 PGA의 Sort Area에 저장한다.
8. PGA에 담을 수 없는 크기라면 Temp 테이블스페이스에 저장한다.
9. PGA 혹은 Temp 테이블스페이스에 저장한 Outer 데이터를 스캔하면서 Inner 데이터와 조인한다.

주목할 점은 Outer를 기준으로 매번 Inner를 전체 스캔하지 않는 것인데, 이는 Inner 데이터가 정렬되어 있기 때문에 조인 대상 레코드의 시작점과 종료점을 쉽게 찾을 수 있기 때문이다.

즉, Sort Area에 저장한 데이터 자체가 인덱스 역할을 수행하기 때문에 조인 컬럼에 인덱스가 없어도 사용할 수 있는 조인 방식이다. 조인 컬럼에 인덱스가 있어도 대량 데이터 조인 시에는 NL 조인보다 소트 머지 조인이 유리하다.

소트 머지 조인이 빠른 이유

NL 조인은 인덱스를 이용한 조인이기 때문에 랜덤 액세스로 인해 대량 데이터 조인시 피할 수 없는 성능 저하가 발생한다. 반면에 소트 머지 조인은 양쪽 테이블로부터 조인 대상 집합을 일괄적으로 읽어 PGA에 저장한 후에 조인한다. 또한, PGA는 프로세스별로 독립적인 공간이기에 데이터를 읽을 때 래치 획득 과정이 없기에 더욱 빠르다.

뮬론, 소트 머지 조인도 처음 양쪽 테이블로부터 조인 대상 집합을 읽을 때는 DB 버퍼캐시를 경유해 NL 조인에서 발생하는 버퍼캐시 탐색 비용과 랜덤 액세스 부하는 피할 수 없다.

소트 머지 조인의 주용도

이후에 등장한 해시 조인이 대부분의 경우 더 빠르기 때문에 잘 사용되지는 않지만, 해시 조인은 조건이 등치 조건이 아니면 사용할 수 없다는 단점이 있다. 그래서 소트 머지 조인은 주로 조건이 등치 조건이 아닌 대량 데이터 조인이나, 조인 조건식이 아예 없는 크로스 조인에 사용된다.

소트 머지 조인 특징 요약

조인을 위해 PGA 영역에 정렬을 수행 한 결과집합을 저장하고, 이를 사용해 조인을 한다. 따라서 초기 결과집합 조회를 위한 액세스를 제외하면, 조인 시 발생하는 액세스는 모두 PGA를 경유해 NL 조인보다 빠르다.

또한, 조인 컬럼의 인덱스 유무에 영향을 받지 않고, 양쪽 집합을 개별적으로 읽고 조인을 시작한다. 그래서 조인 컬럼에 인덱스가 없고, 두 테이블을 각각 읽어 조인 대상 집합을 줄일 수 있을 때 효율적이다.

스캔 위주의 액세스 방식을 사용하지만, 모든 처리가 스캔 방식을 사용하지는 않고, 조인 대상 레코드를 찾을 때 인덱스를 이용할 수 있고, 그때는 랜덤 액세스가 발생하기도 한다.

해시 조인

해시 조인은 소트 머지 조인과 다르게 양쪽 테이블을 정렬할 필요가 없다. 하지만 모든 조인을 처리할 수 없기에 상황에 맞게 조인 방식을 선택할 줄 알아야 한다.

기본 메커니즘

Build + Probe 두 단계로 진행된다.

1. Build : 작은 쪽(Build Input, Outer) 테이블을 읽어 해시 테이블을 생성
2. Probe : 큰 쪽(Probe Input, Inner) 테이블을 읽어 해시 테이블을 탐색하며 조인

마찬가지로, NL 조인 기준으로 순서를 정리해보자

1. Outer 조건을 만족하는 데이터를 읽어 해시 테이블을 생성한다.
2. 조인컬럼을 해시 테이블 키 값으로 사용한다.
3. PGA의 Hash Area에 해시 테이블이 저장된다. (혹은 Temp 테이블스페이스)
4. Inner 조건을 만족하는 데이터를 하나씩 읽어 해시 테이블을 탐색한다.
5. 조인컬럼을 해시 함수에 입력한다.
6. 반환된 값으로 해시 체인을 찾는다.
7. 해시 체인을 스캔해 값이 같은 데이터를 찾는다.
8. 찾으면 조인 성공, 못찾으면 실패다.

해시 조인이 빠른 이유

해시 조인도 소트 머지 조인과 마찬가지로 PGA에 해시 테이블을 할당하기 때문에 래치 획득 과정 없이 탐색 및 조인이 가능해 빠르다.

해시 테이블에는 조인 키값만 저장하는 것은 아니고, SQL에 사용한 컬럼을 모두 저장한다. 키값만 저장한다면 나머지 데이터를 가져오기 위해 테이블에 액세스 해야해서 NL 조인과 다를게 없다.

해시 조인이 소트 머지 조인보다 빠른 이유는 조인 오퍼레이션 실행 전 양쪽 집합을 모두 정렬해 PGA 담는 것이 아닌, 양쪽 중 더 작은 집합으로 해시 맵을 만들어 한쪽만 넣기 때문에 공간이 부족해 Temp 테이블스페이스, 즉 디스크에 쓰는 작업이 발생할 일이 없기 때문이다.

Build Input이 너무 커서 Hash Area 크기를 초과하더라도 대량 데이터 조인 시에는 일반적으로 해시 조인이 가장 빠르기 때문에 다른 조인 메서드를 선택할 필요는 없다.

대용량 Build Input 처리

조인 대상이 모두 대용량 테이블이라 인메모리 해시 조인이 불가능한 경우 DBMS는 분할 정복 방식으로 해시 조인을 처리한다.

1. 파티션 단계
   1. 양쪽 집합을 읽어 조인 컬럼에 해시 함수를 적용한다.
   2. 해시 값에 따라 동적으로 파티셔닝한다.
   3. 즉, 같은 해시값끼리 같은 집합과 페어로 파티셔닝한다.
2. 조인 단계
   1. 페어에 대해 하나씩 조인을 수행한다.
   2. 파티션 전 테이블 크기와 상관없이 페어별로 작은 쪽은 Build Input으로 선택한다.
   3. 해시 테이블을 생성한다.
   4. 반대쪽 파티션 로우를 하나씩 읽으면서 모든 페어에 대한 처리를 마칠 때까지 반복한다.

해시 조인 실행계획 제어

use_hash 힌트를 사용해 해시 조인 실행계획을 제어할 수 있고, 해당 힌트만 사용하면 옵티마이저가 둘 중 카디널리티가 작은 테이블을 선택한다. 직접 선택하고 싶은 경우에는 마찬가지로 leading 이나 ordered 힌트를 사용하면 되고, swap_join_inputs 힌트로 명시적으로 선택할 수도 있다.

세 개 이상 테이블 해시 조인

중간 테이블을 어디로 보는지에 따라 경로가 여러 개가 되지만 결국은 같은 경로가 된다.