• 병렬 조인 방식을 제어하기 위해 사용

• 병렬 조인시 옵티마이저의 선택
• 조인되는 양쪽 테이블의 파티션 구성
• 데이터 크기 에 따라 달라짐

• pq_distribute 힌트 사용
• 옵티마이져가 파티션된 테이블을 적절히 활용하지 못하고 동적 재분할을 시도할경우
• 기본 파티션 키를 무시하고 다른 키 값으로 동적 재분할 하고 싶을때
• 통계 정보가 부정확하거나 통계정보를 제공하기 어려울때 실행계획을 고정시키고자 할때
• 기타 이유로 데이터 분배 방식을 변경할때

• 병렬쿼리
• ‘분할&정복(Divide & Conquer) 원리' 에 기초
• 병렬조인
• ‘분할&조인(Distribute & Join) 원리'가 작동

• pq_distribute 힌트는 조인에 앞서 데이터를 분배(distribute)과정에 관여

select /*+ ordered use_merge(e) parallel (d 4) parallel (e 4)
   pq_distribute(e hash hash) */ *
from scott.dept d, scott.emp e
where e.deptno=d.deptno;


Execution Plan
----------------------------------------------------------
Plan hash value: 3605570675

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |    14 |   812 |     5  (20)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR          |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10002 |    14 |   812 |     5  (20)| 00:00:01 |  Q1,02 | P->S | QC (RAND)  |
|   3 |    MERGE JOIN            |          |    14 |   812 |     5  (20)| 00:00:01 |  Q1,02 | PCWP |            |
|   4 |     SORT JOIN            |          |     4 |    80 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|   5 |      PX RECEIVE          |          |     4 |    80 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|   6 |       PX SEND HASH       | :TQ10000 |     4 |    80 |     2   (0)| 00:00:01 |  Q1,00 | P->P | HASH       |
|   7 |        PX BLOCK ITERATOR |          |     4 |    80 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| DEPT     |     4 |    80 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|*  9 |     SORT JOIN            |          |    14 |   532 |     3  (34)| 00:00:01 |  Q1,02 | PCWP |            |
|  10 |      PX RECEIVE          |          |    14 |   532 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|  11 |       PX SEND HASH       | :TQ10001 |    14 |   532 |     2   (0)| 00:00:01 |  Q1,01 | P->P | HASH       |
|  12 |        PX BLOCK ITERATOR |          |    14 |   532 |     2   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|  13 |         TABLE ACCESS FULL| EMP      |    14 |   532 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  9 - access("E"."DEPTNO"="D"."DEPTNO")
      filter("E"."DEPTNO"="D"."DEPTNO")

• 테이블은 양쪽 모두 hash 방식으로 분배, 조인은 소트 머지 방식으로 수행

(2) 구문 이해하기

pq_distribute 힌트

/*+ pq_distribute (table, outer_distribution, inner_distribution) */

table: inner 테이블 명 또는 alias
outer_distribution : outer 테이블의 distribution 방식
inner_distribution : inner 테이블의 distribution 방식

select /*+ ordered
    use_hash (b) use_nl(c) use_merge(d)
    full(a) full(b) full(c) full(d)
    parallel(a,16) parallel(b,16) parallel(c,16) parallel(d,16)
    pq_distribute(b,none,partition)
    pq_distribute(c,none,broadcase)
    pq_distrivute(d,hash,hash) */ ….
from 상품기본이력임시 a,상품 b, 코드상세 c, 상품상세 d
where a.상품번호=b.상폼번호
and …


1. from 절에 나열된 순서대로 조인하라
2.b(상품) 테이블과 해시조인, c(코드상세)테이블과 NL,d(상품상세) 테이블과 소트머지
3.a,b,c,d 네 테이블을 full scan
4.a,b,c,d 네 테이블을 병렬로 처리
5. 상품(b) 테이블과 조인할때 , inner 테이블(=상품)을 outer테이블(=상품기본이력임시) 에 맞춰 파티셔닝
6.코드상세(c)테이블과 조인할때, inner 테이블(=코드상세)을 Broadcast
7.상품상세(d)테이블과 조인할때, 양쪽 모두를 Hash방식으로 동적 파티셔닝

(3) 분배방식 지정

• pq_distribute(inner,none,none)
• - Full-Partition Wise 조인으로 유도 , 양쪽 테이블 모두 같은 기준으로 조인컬럼에 파티셔닝돼있을경우

• 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,broadcase,none)
• outer 테이블을 Broadcast

• pq_distribute(inner,none,broadcase)
• inner 테이블을 Broadcast

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

• 통계 정보가 없는 상태에서 병렬 조인하면 옵티마이저가
  아주 큰 테이블을 Broadcast하는 경우가 있으며 그경우 발생한 사례

• 9i 경우

• 실제 천만건에 이르는 상품이력임시 테이블에 통계정보가 없어 옵티마이저가 5,248 건으로 판단하여
• 32개 병렬 서버에 Broadcast하는 동한 과도한 프로세스간 통신이발생하여
  TEMP테이블스페이스를 모두 소진하고 멈춤

pq_distribute( b, none, partition)

pq_distribute(c,none,brocase)

pq_distribute(d,hash,hash) */ ....

• ps_distribute 힌트를 사용해 분배방식을 조정한결과 1시간 40분동안 미완성되던 쿼리가 2분 29초 만에 완료

• 10g부터는 동적 샘플링이 일어나 가능성이 낮지만 조인이 여러번 일어나면 옵티마이저의
  예상 카디널리티가 부정확해질수 있음

• 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
• 작성자: 남송휘 (tofriend)
• 최초작성일: 2011년 6월 26일
• 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
• 문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^