04 PQ_DISTRIBUTE 힌트

• 4가지 병렬 조인방식의 동작 원리와 특징을 살펴보았고, 이제 이들을 제어하기 위해 사용되는 PQ_DISTRIBUTE 힌트를 소개하려고 한다.

PQ_DISTRIBUTE 힌트의 용도

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

{CODE:SQL}
SQL> SELECT /*+ gather_plan_statistics LEADING( D ) USE_MERGE( E ) FULL( D ) FULL ( E ) PARALLEL(D 4) PARALLEL(E 4) PQ_DISTRIBUTE(E ,HASH, HASH ) /COUNT()
2 FROM DEPT_NOPARTITION D, EMP_NOPARTITION E
3 WHERE E.DEPTNO = D.DEPTNO;

COUNT(*)

SQL> @XPLAN

Predicate Information (identified by operation id):

10 - access(:Z>=:Z AND :Z<=:Z)
11 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
15 - access(:Z>=:Z AND :Z<=:Z)

Note

• dynamic sampling used for this statement

39 개의 행이 선택되었습니다.
{CODE

• 병렬 조인을 수행하기 위해서는 프로세스들이 서로 "독립적을" 작업업 할 수 있도록 사전 준비작업이 필요하다. 먼저 테이터를 적절히 배분하는 작업이 선행
  되어야 하는 것이며, 어떤 방식이 있는지는 앞 절에서 충분히 설명하였다.
• 병렬 쿼리는 '분할 & 정복 원리'에 기초한다. 그 중에서도 병렬 조인을 위해서는 '분배 & 조인 원리'가 작동함을 이해하는 것이 매우 중요하다.
• 이때 PQ_DISTRIBUTE 힌트는 조인에 앞서 데이터를 분배 하는 과정만 관여하는 힌트임을 반드시 기억할 필요가 있다.

구문 이해하기

SQL> SELECT /*+ gather_plan_statistics
2 ORDERED
3 USE_HASH( D ) USE_HASH( E ) USE_HASH( PE )
4 FULL( D ) FULL( PE ) FULL( E )
5 PARALLEL(D 8) PARALLEL(E 8) PARALLEL(PE 8)
6 PQ_DISTRIBUTE(E, BROADCAST, NONE) PQ_DISTRIBUTE(PE, PARTITION, NONE ) */
7 COUNT(*)
8 FROM DEPT_NOPARTITION D, EMP_NOPARTITION E, EMP_PARTITION3 PE
9 WHERE D.DEPTNO = E.DEPTNO
10 AND E.DEPTNO = PE.DEPTNO
11 AND ROWNUM <= 40000000;

COUNT(*)

SQL> @xplan

Predicate Information (identified by operation id):

2 - filter(ROWNUM<=40000000)
5 - filter(ROWNUM<=40000000)
6 - access("E"."DEPTNO"="PE"."DEPTNO")
11 - access("D"."DEPTNO"="E"."DEPTNO")
15 - access(:Z>=:Z AND :Z<=:Z)
17 - access(:Z>=:Z AND :Z<=:Z)

분배방식 지정

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 테이블 파티션기준에 따라 파티셔닝 하라는 뜻이다.

PQ_DISTRIBUTE( Inner, hash, hash )

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

PQ_DISTRIBUTE( innert, broadcast, none )

• outer 테이블을 Boardcast 하라는 뜻이다.

PQ_DISTRIBUTE( inner, none, broadcast )

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

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

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

SQL> SELECT /*+ gather_plan_statistics
2 ORDERED
3 USE_HASH( D ) USE_HASH( E ) USE_HASH( PE )
4 FULL( D ) FULL( PE ) FULL( E )
5 PARALLEL(D 8) PARALLEL(E 8) PARALLEL(PE 8)
6 PQ_DISTRIBUTE(E, BROADCAST, NONE) PQ_DISTRIBUTE(PE, PARTITION, NONE ) */
7 COUNT(*)
8 FROM DEPT_NOPARTITION D, EMP_NOPARTITION E, EMP_PARTITION3 PE
9 WHERE D.DEPTNO = E.DEPTNO
10 AND E.DEPTNO = PE.DEPTNO
11 AND ROWNUM <= 40000000;

COUNT(*)

SQL> @xplan

Predicate Information (identified by operation id):

2 - filter(ROWNUM<=40000000)
5 - filter(ROWNUM<=40000000)
6 - access("E"."DEPTNO"="PE"."DEPTNO")
11 - access("D"."DEPTNO"="E"."DEPTNO")
15 - access(:Z>=:Z AND :Z<=:Z)
17 - access(:Z>=:Z AND :Z<=:Z)