<오라클 성능 고도화 원리와 해법2> Ch07-04 PQ_DISTRIBUTE 힌트

오라클 성능 고도화 원리와 해법2 - Ch07-04 PQ_DISTRIBUTE 힌트

4가지 병렬 조인 방식의 동작 원리와 특징을 살펴보았고, 이제 이들을 제어하기 위해 사용되는 pq_distribute 힌트를 소개하려고 한다. 사용법을 설명하기에 앞서 이 힌트의 용도가 무엇인지를 먼저 살펴보자.

(1) pq_distribute 힌트의 용도

조인되는 양쪽 테이블의 파티션 구성, 데이터 크기 등에 따라 병렬 조인을 수행하는 옵티마이저의 선택이 달라질 수 있다. 대개 옵티마이저의 선택이 최적이라고 할 수 있지만 가끔 그렇지 못한 경우가 있다. 그럴 때 pq_distribute 힌트를 사용함으로써 옵티마이저의 선택을 무시하고 사용자가 직접 조인을 위한 데이터 분배 방식을 결정할 수 있다.

• 옵티마이저가 파티션된 테이블을 적절히 활용하지 못하고 동적 재분할을 시도할 때
• 기존 파티션 키를 무시하고 다른 키 값으로 동적 재분할하고 싶을 때
• 통계 정보가 부정확하거나 통계 정보를 제공하기 어려운 상황에서 실행 계획을 고정시키고자 할 때
• 기타 여러 가지 이유로 데이터 분배 방식을 변경하고자 할 때

병렬 방식으로 조인을 수행하기 위해서는 프로세스들이 서로 “독립적으로” 작업할 수 있도록 사전 준비 작업이 필요하다. 먼저 데이터를 적절히 배분하는 작업이 선행되어야 하는 것이며, 어떤 방식이 있는지는 앞 절에서 충분히 설명하였다.

병렬 쿼리는 ‘분할 & 정복(Divide & Conquer) 원리’에 기초한다. 그중에서도 병렬 조인을 위해서는 ‘분배 & 조인(Distribute & Join) 원리’가 작동함을 이해하는 것이 매우 중요하다. 이때, pq_distribute 힌트는 조인에 앞서 데이터를 분배(distribute)하는 과정에만 관여하는 힌트임을 반드시 기억할 필요가 있다.

(2) 구문 이해하기

pq_distribute 힌트의 사용법은 다음과 같다.

많은 개발자 또는 튜너들이 첫 번째 인자의 의미를 이해하는 데에 어려움을 느끼는 것 같은데, use_nl 힌트가 pq_distribute 힌트를 이해하는 데에 도움을 주므로 잠시 살펴보자. 2장 1절에서 use_nl 힌트 사용법을 아래와 같이 설명한 것을 상기하기 바란다.

select /*+ ordered use_nl(B) use_nl(C) use_hash(D) */ *
from A, B, C, D
where ...

이는 “A-B-C-D 순으로 조인하되 B, C와 조인할 때는 Nested Loop 방식으로 조인하고, D와 조인할 때는 해시 방식으로 조인하라”는 뜻이다.

(3) 분배 방식 지정

inner 테이블 지정하는 방법을 알았고, 이제 두 번째와 세 번째 인자를 통해 분배 방식을 어떻게 지정하는지 간단히 살펴보자. 병렬 조인의 수행 원리를 이미 이해하고 있으므로 분배 방식을 지정하는 것은 매우 쉽다고 느낄 것이다.

pq_distribute(inner, none, none)

Full-Partition Wise 조인으로 유도할 때 사용한다. 당연히, 양쪽 테이블 모두 조인 컬럼에 대해 같은 기준으로 파티셔닝(equi-partitioning)돼 있을 때만 작동한다.

pq_distribute(inner, partition, none)

Partial-Partition Wise 조인으로 유도할 때 사용하며, outer 테이블을 inner 테이블 파티션 기준에 따라 파티셔닝하라는 뜻이다. 당연히, inner 테이블이 조인 키 컬럼에 대해 파티셔닝돼 있을 때만 작동한다.

pq_distribute(inner, none, partition)

Partial-Partition Wise 조인으로 유도할 때 사용하며, inner 테이블을 outer 테이블 파티션 기준에 따라 파티셔닝하라는 뜻이다. 당연히, outer 테이블이 조인 키 컬럼에 대해 파티셔닝돼 있을 때만 작동한다.

pq_distribute(inner, hash, hash)

조인 키 컬럼을 해시 함수에 적용하고 거기서 반환된 값을 기준으로 양쪽 테이블을 동적으로 파티셔닝하라는 뜻이다.

pq_distribute(inner, broadcast, none)

outer 테이블을 Broadcast 하라는 뜻이다.

pq_distribute(inner, none, broadcast)

inner 테이블을 Broadcast 하라는 뜻이다.

(4) pq_distribute 힌트를 이용한 튜닝 사례

통계 정보가 없는 상태에서 병렬 조인하면 옵티마이저가 아주 큰 테이블을 Broadcast 하는 경우를 종종 보게 된다. 임시 테이블을 많이 사용하는 야간 배치나 데이터 이행(migration) 프로그램에서 그런 문제가 자주 발생하는 이유가 여기에 있다.

10g부터는 통계 정보가 없을 때 동적 샘플링이 일어나므로 그럴 가능성이 매우 낮아졌다. 하지만 테이블 간 조인을 여러 번 거치면 옵티마이저가 예상한 조인 카디널리티가 점점 부정확해지게 마련이다. 예를 들어, a-b-c-d-e 순으로 조인을 진행하는데, a, b, c, d를 조인하고 난 시점에도 결과 건수가 여전히 많을 수 있다. 그렇다면 해시/해시 분배 방식이 효과적인데 옵티마이저가 조인 카디널리티를 계산할 때는 그 시점의 결과 건수가 매우 적은 것으로 판단해 Broadcast 방식을 선택할 수도 있는 것이다.

데이터 분포가 고르지 않은 컬럼이 조건절에 많이 사용되거나, 다른 테이블과 조인되기 전인 라인뷰 내에서 많은 가공이 이루어져 정확한 카디널리티 계산이 어려울 때 이런 오류 발생 가능성은 더욱 커진다.