4. PQ_DISTRIBUTE 힌트
2011.06.27 02:20
- 병렬 조인 방식을 제어하기 위해 사용
(1) pq_distribute 힌트의 용도
- 병렬 조인시 옵티마이저의 선택
- 조인되는 양쪽 테이블의 파티션 구성
- 데이터 크기 에 따라 달라짐
- 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 경우
select /*+ parallel(a,32) parallel(b,32) parallel(c,32) parallel(d,32) */ ....
from 상품기본이력임시 a, 상품 b, 코드상세 c, 상품상세 d
where a.상품번호=b.상품번호
and …
- 실제 천만건에 이르는 상품이력임시 테이블에 통계정보가 없어 옵티마이저가 5,248 건으로 판단하여
- 32개 병렬 서버에 Broadcast하는 동한 과도한 프로세스간 통신이발생하여
TEMP테이블스페이스를 모두 소진하고 멈춤
select /*+ parallel(a,32) parallel(b,32) parallel(c,32) parallel(d,32)
pq_distribute( b, none, partition)
pq_distribute(c,none,brocase)
pq_distribute(d,hash,hash) */ ....
where a.상품번호=b.상품번호
and ...
- ps_distribute 힌트를 사용해 분배방식을 조정한결과 1시간 40분동안 미완성되던 쿼리가 2분 29초 만에 완료
- 10g부터는 동적 샘플링이 일어나 가능성이 낮지만 조인이 여러번 일어나면 옵티마이저의
예상 카디널리티가 부정확해질수 있음
- 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
- 작성자: 남송휘 (tofriend)
- 최초작성일: 2011년 6월 26일
- 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
- 문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^
댓글 0
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
55 | Front Page | 운영자 | 2011.02.16 | 114630 |
54 | 1. 테이블 파티셔닝 | 오라클잭 | 2011.06.21 | 28274 |
53 | 5. 병렬 처리에 관한 기타 상식 [1] | balto | 2011.06.26 | 28162 |
52 | 1. Nested Loops 조인 | 오라클잭 | 2011.03.23 | 23031 |
51 | 6. 스칼라 서브쿼리를 이용한 조인 | balto | 2011.03.27 | 18959 |
50 | 2. 인덱스 기본 원리 | balto | 2011.02.18 | 18848 |
49 | 5. Outer 조인 | 휘휘 | 2011.03.28 | 17670 |
48 | 4. 조건절 Pushing | 오예스 | 2011.05.31 | 17496 |
47 | 6. 히스토그램 | 오예스 | 2011.04.25 | 17418 |
46 | 4. 통계정보 Ⅰ | 토시리 | 2011.04.25 | 16017 |
45 | 1. 인덱스 구조 | 운영자 | 2011.03.30 | 15954 |
» | 4. PQ_DISTRIBUTE 힌트 | 휘휘 | 2011.06.27 | 15620 |
43 | 6. IOT, 클러스터 테이블 활용 | 오예스 | 2011.02.26 | 15604 |
42 | 5. 카디널리티 | 오라클잭 | 2011.04.27 | 12922 |
41 | 8. 고급 조인 테크닉-2 | 오라클잭 | 2011.04.05 | 12564 |
40 | 2. 파티션 Pruning | 휘휘 | 2011.06.19 | 11868 |
39 | 2. 병렬 Order By와 Group By | 휘휘 | 2011.06.30 | 10127 |
38 | 7. 조인을 내포한 DML 튜닝 | 오예스 | 2011.04.04 | 9955 |
37 | 10. 실체화 뷰 쿼리로 재작성 | 오라클잭 | 2011.06.08 | 9953 |
36 | 5. 테이블 Random 액세스 최소화 튜닝 | 휘휘 | 2011.02.26 | 9507 |