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
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
35 | 7. 인덱스 스캔 효율 | 휘휘 | 2011.03.06 | 8451 |
34 | 7. OR-Expansion | AskZZang | 2011.06.01 | 8334 |
33 | 1. 소트 수행 원리 | balto | 2011.06.12 | 8025 |
32 | 2. 소트 머지 조인 | 오예스 | 2011.03.21 | 7913 |
31 | 4. 테이블 Random 액세스 부하 | 휘휘 | 2011.02.26 | 6947 |
30 | 1장. 인덱스 원리와 활용 | 휘휘 | 2011.02.21 | 6799 |
29 | 3. 옵티마이저의 한계 | 휘휘 | 2011.04.19 | 6700 |
28 | 3. 해시 조인 | 휘휘 | 2011.03.21 | 6497 |
27 | 8. 고급 조인 테크닉-1 | 휘휘 | 2011.04.05 | 6463 |
26 | 1. 쿼리 변환이란? | 운영자 | 2011.05.16 | 6258 |
25 | 2. 옵티마이저 행동에 영향을 미치는 요소 | balto | 2011.04.18 | 6163 |
24 | 3. 뷰 Merging | 오라클잭 | 2011.05.17 | 6082 |
23 | 1. 옵티마이저 | 휘휘 | 2011.04.18 | 6079 |
22 | 7. Sort Area 크기 조정 | 오예스 | 2011.06.16 | 5987 |
21 | 8. 통계정보 Ⅱ | AskZZang | 2011.05.04 | 5913 |
20 | 8. 인덱스 설계 | AskZZang | 2011.03.09 | 5727 |
19 | 5. 조건절 이행 | balto | 2011.05.30 | 5465 |
18 | 6. 조인 제거 | AskZZang | 2011.06.01 | 5441 |
17 | 4. 조인 순서의 중요성 | AskZZang | 2011.03.30 | 5329 |
16 | 9. 비트맵 인덱스 | 휘휘 | 2011.03.07 | 5015 |