| 오라클에서 병령처리(PARALLEL) 시 사용되는 조인 방식 제어 힌트 -- 힌트 문법 /*+ pq_distribute(inner_table, outer_distribution, inner_distribution) */ |
-- 두 테이블이 조인될 때 리딩하는 테이블이 Driving Table 또는 Outer Table 조인되어지는 테이블은 Inner Table -- 힌트 옵션별 작동 방식 PQ_DISTRIBUTE( Inner, none, none ) : Full-Partition Wise Join 유도할 때 사용한다 : 양쪽 테이블 모두 조인 컬럼에 대해 같은 기준으로 파티셔닝 되어 있어야 함 PQ_DISTRIBUTE( Inner, partition, none ) : Partial-Partition Wise 조인으로 유도할 때 사용 : outer 테이블을 inner 테이블 파티션기준에 따라 파티셔닝 : inner 테이블이 조인 키 컬럼에 대해 파티셔닝 되어 있어야 함 PQ_DISTRIBUTE( Inner, none, partition ) : Partial-Partition Wise 조인으로 유도할 때 사용 : inner 테이블을 outer 테이블 파티션기준에 따라 파티셔닝 : Outer 테이블은 Join Key 컬럼으로 파티션되어 있어야 함(Outer 테이블이 파티션되어 있지 않은 경우에는 사용 못함) PQ_DISTRIBUTE( Inner, hash, hash ) : 조인 키 컬럼을 해시 함수에 적용하고 거기서 반환된 값을 기준으로 양쪽 테이블을 동적으로 파티셔닝 : 병렬조인에서 hash join 또는 sort merge join 을 하면서 조인하는 테이블들이 꽤 큰 경우에 이 방식을 사용하는게 좋음 PQ_DISTRIBUTE( inner, broadcast, none ) : outer 테이블을 Boardcast : outer 테이블이 작은 경우 사용 PQ_DISTRIBUTE( inner, none, broadcast ) : inner 테이블을 Broadcast : inner 테이블이 작은 경우 사용 |
| 병렬처리(Parallel Processing) 에서는 Producer, Consumer 가 있음 조인하지 않고 한 테이블에서 데이터를 읽어서 처리하는 경우에도 병렬처리시에는 2쌍의 병렬프로세서들이 일을 나누어서 함 테이블에서 데이터를 읽는 역할을 하는 Producer 병렬서버 읽은 데이터를 Sort, DML 등의 작업을 하는 Consumer 병렬서버 |
반응형
테스트 테이블
SELECT count(*) FROM sk.tab1 WHERE owner = 'SK' ;
COUNT(*)|
--------+
50|
SELECT count(*) FROM sk.tab2 WHERE owner = 'SK' ;
COUNT(*)|
--------+
51|
tab1 테이블 serial, tab2 테이블 parallel 2, tab1 테이블 broadcast 힌트 사용 테스트 sql monitor 결과
- TAB1에 PX SELECTOR가 SERIAL로 수행됨을 의미한다
SQL Monitoring Report
SQL Text
------------------------------
SELECT /*+ monitor NOPARALLEL(A) PARALLEL(B 2) PQ_DISTRIBUTE(B BROADCAST NONE ) */
*
FROM sk.tab1 a, sk.tab2 b
WHERE a.object_name = b.object_name
AND a.owner = 'SK'
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : SYS (27:45372)
SQL ID : 2xabsn1u3fvj1
SQL Execution ID : 16777217
Execution Started : 09/21/2023 20:18:10
First Refresh Time : 09/21/2023 20:18:10
Last Refresh Time : 09/21/2023 20:18:10
Duration : .016133s
Module/Action : DBeaver 23?1?1 ? SQLEditor ?Script?36?sql?/-
Service : ora19c
Program : DBeaver 23?1?1 ? SQLEditor ?Script?36?sql?
Fetch Calls : 33
Global Stats
===========================================================================
| Elapsed | Cpu | IO | Other | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
===========================================================================
| 0.03 | 0.02 | 0.00 | 0.00 | 33 | 2892 | 64 | 11MB |
===========================================================================
Parallel Execution Details (DOP=2 , Servers Allocated=4)
====================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Other | Buffer | Read | Read | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | (sample #) |
====================================================================================================================
| PX Coordinator | QC | | 0.00 | 0.00 | | 0.00 | 5 | | . | |
| p000 | Set 1 | 1 | 0.00 | | 0.00 | 0.00 | 540 | 21 | 4MB | |
| p001 | Set 1 | 2 | 0.01 | 0.01 | 0.00 | | 925 | 43 | 7MB | |
| p002 | Set 2 | 1 | 0.00 | | | 0.00 | 1422 | | . | |
| p003 | Set 2 | 2 | 0.02 | 0.02 | | | | | . | |
====================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=1505479081)
===========================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
===========================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 5 | 322 | | | . | | |
| 1 | PX COORDINATOR | | | | 1 | +0 | 5 | 322 | | | . | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 3240 | 614 | 1 | +0 | 2 | 322 | | | . | | |
| 3 | HASH JOIN | | 3240 | 614 | 1 | +0 | 2 | 322 | | | 2MB | | |
| 4 | JOIN FILTER CREATE | :BF0000 | 2707 | 395 | 1 | +0 | 2 | 100 | | | . | | |
| 5 | PX RECEIVE | | 2707 | 395 | 1 | +0 | 2 | 100 | | | . | | |
| 6 | PX SEND BROADCAST | :TQ10000 | 2707 | 395 | 1 | +0 | 2 | 100 | | | . | | |
| 7 | PX SELECTOR | | | | 1 | +0 | 2 | 50 | | | . | | |
| 8 | TABLE ACCESS FULL | TAB1 | 2707 | 395 | 1 | +0 | 1 | 50 | | | . | | |
| 9 | JOIN FILTER USE | :BF0000 | 73077 | 220 | 1 | +0 | 2 | 57 | | | . | | |
| 10 | PX BLOCK ITERATOR | | 73077 | 220 | 1 | +0 | 2 | 57 | | | . | | |
| 11 | TABLE ACCESS FULL | TAB2 | 73077 | 220 | 1 | +0 | 45 | 57 | 64 | 11MB | . | | |
===========================================================================================================================================================
참고용으로 PARALLEL(B 4) 수행 결과
- TQ10000 부분이 (PARALLE 2) 때와 달라진 것이 보인다
SELECT /*+ monitor NOPARALLEL(A) PARALLEL(B 4) PQ_DISTRIBUTE(B BROADCAST NONE ) */ * FROM sk.tab1 a, sk.tab2 b WHERE a.object_name = b.object_name AND a.owner = 'SK'
Global Stats
===========================================================================
| Elapsed | Cpu | IO | Other | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
===========================================================================
| 0.04 | 0.01 | 0.00 | 0.03 | 23 | 2900 | 69 | 11MB |
===========================================================================
Parallel Execution Details (DOP=4 , Servers Allocated=8)
===================================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Other | Buffer | Read | Read | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | (sample #) |
===================================================================================================================================
| PX Coordinator | QC | | 0.03 | 0.00 | | 0.02 | 5 | | . | PX Deq: Signal ACK EXT (1) |
| p000 | Set 1 | 1 | 0.00 | | 0.00 | 0.00 | 252 | 13 | 2MB | |
| p001 | Set 1 | 2 | 0.00 | | 0.00 | 0.00 | 291 | 14 | 2MB | |
| p002 | Set 1 | 3 | 0.00 | 0.00 | 0.00 | | 494 | 20 | 4MB | |
| p003 | Set 1 | 4 | 0.01 | 0.01 | 0.00 | | 436 | 22 | 3MB | |
| p004 | Set 2 | 1 | 0.00 | 0.00 | | 0.00 | 1422 | | . | |
| p005 | Set 2 | 2 | 0.00 | 0.00 | | | | | . | |
| p006 | Set 2 | 3 | 0.00 | 0.00 | | | | | . | |
| p007 | Set 2 | 4 | 0.00 | 0.00 | | | | | . | |
===================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=1505479081)
======================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
======================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 9 | 322 | | | . | | |
| 1 | PX COORDINATOR | | | | 1 | +0 | 9 | 322 | | | . | 100.00 | PX Deq: Signal ACK EXT (1) |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 3240 | 505 | 1 | +0 | 4 | 322 | | | . | | |
| 3 | HASH JOIN | | 3240 | 505 | 1 | +0 | 4 | 322 | | | 5MB | | |
| 4 | JOIN FILTER CREATE | :BF0000 | 2707 | 395 | 1 | +0 | 4 | 200 | | | . | | |
| 5 | PX RECEIVE | | 2707 | 395 | 1 | +0 | 4 | 200 | | | . | | |
| 6 | PX SEND BROADCAST | :TQ10000 | 2707 | 395 | 1 | +0 | 4 | 200 | | | . | | |
| 7 | PX SELECTOR | | | | 1 | +0 | 4 | 50 | | | . | | |
| 8 | TABLE ACCESS FULL | TAB1 | 2707 | 395 | 1 | +0 | 1 | 50 | | | . | | |
| 9 | JOIN FILTER USE | :BF0000 | 73077 | 110 | 1 | +0 | 4 | 57 | | | . | | |
| 10 | PX BLOCK ITERATOR | | 73077 | 110 | 1 | +0 | 4 | 57 | | | . | | |
| 11 | TABLE ACCESS FULL | TAB2 | 73077 | 110 | 1 | +0 | 51 | 57 | 69 | 11MB | . | | |
======================================================================================================================================================================
tab1 테이블, tab2 테이블 모두 parallel 2, tab1 테이블 broadcast 힌트 사용 테스트 sql monitor 결과
SQL Monitoring Report
SQL Text
------------------------------
SELECT /*+ monitor PARALLEL(2) PQ_DISTRIBUTE(B BROADCAST NONE ) */
*
FROM sk.tab1 a, sk.tab2 b
WHERE a.object_name = b.object_name
AND a.owner = 'SK'
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : SYS (27:45372)
SQL ID : aqguyzm080k6h
SQL Execution ID : 16777217
Execution Started : 09/21/2023 20:21:47
First Refresh Time : 09/21/2023 20:21:47
Last Refresh Time : 09/21/2023 20:21:47
Duration : .039277s
Module/Action : DBeaver 23?1?1 ? SQLEditor ?Script?36?sql?/-
Service : ora19c
Program : DBeaver 23?1?1 ? SQLEditor ?Script?36?sql?
Fetch Calls : 33
Global Stats
===========================================================================
| Elapsed | Cpu | IO | Other | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
===========================================================================
| 0.05 | 0.05 | 0.00 | 0.01 | 33 | 2938 | 127 | 22MB |
===========================================================================
Parallel Execution Details (DOP=2 , Servers Allocated=4)
====================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Other | Buffer | Read | Read | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | (sample #) |
====================================================================================================================
| PX Coordinator | QC | | 0.00 | 0.00 | | 0.00 | 10 | | . | |
| p000 | Set 1 | 1 | 0.01 | 0.01 | 0.00 | | 705 | 30 | 5MB | |
| p001 | Set 1 | 2 | 0.00 | | 0.00 | 0.00 | 760 | 34 | 6MB | |
| p002 | Set 2 | 1 | 0.04 | 0.04 | 0.00 | | 1056 | 43 | 8MB | |
| p003 | Set 2 | 2 | 0.00 | | 0.00 | 0.00 | 407 | 20 | 3MB | |
====================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2438948461)
===========================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
===========================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 5 | 322 | | | . | | |
| 1 | PX COORDINATOR | | | | 1 | +0 | 5 | 322 | | | . | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 3240 | 439 | 1 | +0 | 2 | 322 | | | . | | |
| 3 | HASH JOIN | | 3240 | 439 | 1 | +0 | 2 | 322 | | | 2MB | | |
| 4 | JOIN FILTER CREATE | :BF0000 | 2707 | 219 | 1 | +0 | 2 | 100 | | | . | | |
| 5 | PX RECEIVE | | 2707 | 219 | 1 | +0 | 2 | 100 | | | . | | |
| 6 | PX SEND BROADCAST | :TQ10000 | 2707 | 219 | 1 | +0 | 2 | 100 | | | . | | |
| 7 | PX BLOCK ITERATOR | | 2707 | 219 | 1 | +0 | 2 | 50 | | | . | | |
| 8 | TABLE ACCESS FULL | TAB1 | 2707 | 219 | 1 | +0 | 45 | 50 | 63 | 11MB | . | | |
| 9 | JOIN FILTER USE | :BF0000 | 73077 | 220 | 1 | +0 | 2 | 57 | | | . | | |
| 10 | PX BLOCK ITERATOR | | 73077 | 220 | 1 | +0 | 2 | 57 | | | . | | |
| 11 | TABLE ACCESS FULL | TAB2 | 73077 | 220 | 1 | +0 | 45 | 57 | 64 | 11MB | . | | |
===========================================================================================================================================================
tab1 테이블, tab2 테이블 모두 parallel 2, tab2 테이블 broadcast 힌트 사용 테스트 sql monitor 결과
SQL Monitoring Report
SQL Text
------------------------------
SELECT /*+ monitor PARALLEL(2) PQ_DISTRIBUTE(B NONE BROADCAST ) */
*
FROM sk.tab1 a, sk.tab2 b
WHERE a.object_name = b.object_name
AND a.owner = 'SK'
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : SYS (27:45372)
SQL ID : gzr2n4yr2y8qr
SQL Execution ID : 16777217
Execution Started : 09/21/2023 20:25:37
First Refresh Time : 09/21/2023 20:25:37
Last Refresh Time : 09/21/2023 20:25:37
Duration : .050873s
Module/Action : DBeaver 23?1?1 ? SQLEditor ?Script?36?sql?/-
Service : ora19c
Program : DBeaver 23?1?1 ? SQLEditor ?Script?36?sql?
Fetch Calls : 33
Global Stats
===========================================================================
| Elapsed | Cpu | IO | Other | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
===========================================================================
| 0.09 | 0.08 | 0.00 | 0.01 | 33 | 2936 | 127 | 22MB |
===========================================================================
Parallel Execution Details (DOP=2 , Servers Allocated=4)
====================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Other | Buffer | Read | Read | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | (sample #) |
====================================================================================================================
| PX Coordinator | QC | | 0.00 | 0.00 | | 0.00 | 10 | | . | |
| p000 | Set 1 | 1 | 0.01 | | 0.00 | 0.01 | 825 | 38 | 6MB | |
| p001 | Set 1 | 2 | 0.01 | 0.01 | 0.00 | | 638 | 25 | 5MB | |
| p002 | Set 2 | 1 | 0.05 | 0.05 | 0.00 | | 737 | 32 | 6MB | |
| p003 | Set 2 | 2 | 0.01 | 0.01 | 0.00 | | 726 | 32 | 6MB | |
====================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=744072433)
==========================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
==========================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 5 | 322 | | | . | | |
| 1 | PX COORDINATOR | | | | 1 | +0 | 5 | 322 | | | . | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 3240 | 439 | 1 | +0 | 2 | 322 | | | . | | |
| 3 | HASH JOIN BUFFERED | | 3240 | 439 | 1 | +0 | 2 | 322 | | | 5MB | | |
| 4 | PX BLOCK ITERATOR | | 2707 | 219 | 1 | +0 | 2 | 50 | | | . | | |
| 5 | TABLE ACCESS FULL | TAB1 | 2707 | 219 | 1 | +0 | 45 | 50 | 63 | 11MB | . | | |
| 6 | PX RECEIVE | | 73077 | 220 | 1 | +0 | 2 | 73077 | | | . | | |
| 7 | PX SEND BROADCAST | :TQ10000 | 73077 | 220 | 1 | +0 | 2 | 146K | | | . | | |
| 8 | PX BLOCK ITERATOR | | 73077 | 220 | 1 | +0 | 2 | 73077 | | | . | | |
| 9 | TABLE ACCESS FULL | TAB2 | 73077 | 220 | 1 | +0 | 45 | 73077 | 64 | 11MB | . | | |
==========================================================================================================================================================
참조 :
https://jack-of-all-trades.tistory.com/197
http://wiki.gurubee.net/pages/viewpage.action?pageId=6260556
'DBMS > SQL튜닝' 카테고리의 다른 글
| 윈도우함수 사용하여 누적 값 구하기 (0) | 2023.08.20 |
|---|---|
| PARALLEL SQL DBMS_XPLAN 실행통계 보기 (0) | 2023.08.04 |
| INSERT..SELECT / INSERT..VALUES 성능 테스트 (0) | 2023.08.02 |
| rollup, grouping sets, cube, grouping 함수 (0) | 2023.07.30 |
| oracle ABS 함수, 절대값을 구하는 함수 (0) | 2023.07.18 |
댓글