새로쓴 대용량 데이터베이스솔루션 1 (2011년)
Hints for Parallel Execution
0
0
31,890
by 구루비스터디 Hint 힌트 PARALLEL NO_PARALLEL PARALLEL_INDEX NO_PARALLEL_INDEX PQ_DISTRIBUTE [2023.10.13]
Hints for Parallel Execution
PARALLEL
SELECT /*+ PARALLEL(T1 4) */ N4, COUNT(*) FROM T1 GROUP BY N4;
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 30 | 4 (25)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 10 | 30 | 4 (25)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 10 | 30 | 4 (25)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 10 | 30 | 4 (25)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 10 | 30 | 4 (25)| 00:00:01 | Q1,00 | P->P | HASH |
| 6 | HASH GROUP BY | | 10 | 30 | 4 (25)| 00:00:01 | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 10000 | 30000 | 3 (0)| 00:00:01 | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL| T1 | 10000 | 30000 | 3 (0)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------
-- 시스템 자원을 많이 사용하게 되므로 필요할 때 쓰자
-- PARALLEL DEGREE 를 지정하지 않으면 PARALLEL_THREADS_PER_CPU 값에 따라 자동 계산값 사용
-- PARALLEL 속성을 가진 테이블은 힌트가 없어도 병렬처리 될 수 있음
NO_PARALLEL
SELECT /*+ NO_PARALLEL(T1 4) */ N4, COUNT(*) FROM T1 GROUP BY N4;
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 30 | 5 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT| | 10 | 30 | 5 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | T1N4 | 10000 | 30000 | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------
PARALLEL_INDEX
SELECT /*+ INDEX_FFS(T1) PARALLEL_INDEX(T1 T1N4 4) */ N4, COUNT(*) FROM T1 GROUP BY N4;
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 30 | 12 (42)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 10 | 30 | 12 (42)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 10 | 30 | 12 (42)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 10 | 30 | 12 (42)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 10 | 30 | 12 (42)| 00:00:01 | Q1,00 | P->P | HASH |
| 6 | HASH GROUP BY | | 10 | 30 | 12 (42)| 00:00:01 | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 10000 | 30000 | 7 (0)| 00:00:01 | Q1,00 | PCWC | |
| 8 | INDEX FAST FULL SCAN| T1N4 | 10000 | 30000 | 7 (0)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------
-- TQ : Table Queue
-- QC : Query Coodinator
-- S->P, P->S, P->P : Inter-Operation Parallelism
-- PCWP, PCWC : Intra-Operation Parallelism
-- TQ10000 : 생산자(Q1,00), 소비자(Q1,01)
-- TQ10001 : 생산자(Q1,01), 소비자(QC)
NO_PARALLEL_INDEX
SELECT /*+ INDEX_FFS(T1) NO_PARALLEL_INDEX(T1 T1N4 4) */ N4, COUNT(*) FROM T1 GROUP BY N4;
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 30 | 9 (23)| 00:00:01 |
| 1 | HASH GROUP BY | | 10 | 30 | 9 (23)| 00:00:01 |
| 2 | INDEX FAST FULL SCAN| T1N4 | 10000 | 30000 | 7 (0)| 00:00:01 |
------------------------------------------------------------------------------
PQ_DISTRIBUTE
SELECT /*+ USE_HASH(PT1, PT2) ORDERED PARALLEL(PT2 4) */ * FROM PT1, PT2 WHERE PT1.N4 = PT2.N4;
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10M| 1083M| 51 (61)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 10M| 1083M| 51 (61)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 10M| 1083M| 51 (61)| 00:00:01 | | | Q1,01 | PCWP | |
| 4 | BUFFER SORT | | | | | | | | Q1,01 | PCWC | |
| 5 | PART JOIN FILTER CREATE | :BF0000 | 10639 | 540K| 18 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 6 | PX RECEIVE | | 10639 | 540K| 18 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 7 | PX SEND PARTITION (KEY) | :TQ10000 | 10639 | 540K| 18 (0)| 00:00:01 | | | | S->P | PART (KEY) |
| 8 | PARTITION LIST ALL | | 10639 | 540K| 18 (0)| 00:00:01 | 1 | 10 | | | |
| 9 | TABLE ACCESS FULL | PT1 | 10639 | 540K| 18 (0)| 00:00:01 | 1 | 10 | | | |
| 10 | PX PARTITION LIST JOIN-FILTER| | 10264 | 521K| 2 (0)| 00:00:01 |:BF0000|:BF0000| Q1,01 | PCWC | |
| 11 | TABLE ACCESS FULL | PT2 | 10264 | 521K| 2 (0)| 00:00:01 |:BF0000|:BF0000| Q1,01 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PT1"."N4"="PT2"."N4")
-- PT1, PT2 가 N4 컬럼으로 LIST 파티션 되어 있음
-- "PART JOIN FILTER CREATE, PX PARTITION LIST JOIN-FILTER" 발생 => PARTITION-WISE JOIN => PQ_DISTRIBUTE(PT2 NONE NONE) 와 같은 결과
SELECT /*+ USE_HASH(PT1, PT2) ORDERED PARALLEL(PT2 4) PQ_DISTRIBUTE(PT2 HASH HASH) */ * FROM PT1, PT2 WHERE PT1.N4 = PT2.N4;
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
me | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10M| 1083M| 51 (61)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 10M| 1083M| 51 (61)| 00:00:01 | | | Q1,02 | P->S | QC (RAND) |
|* 3 | HASH JOIN BUFFERED | | 10M| 1083M| 51 (61)| 00:00:01 | | | Q1,02 | PCWP | |
| 4 | BUFFER SORT | | | | | | | | Q1,02 | PCWC | |
| 5 | PART JOIN FILTER CREATE| :BF0000 | 10639 | 540K| 18 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 6 | PX RECEIVE | | 10639 | 540K| 18 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 7 | PX SEND HASH | :TQ10000 | 10639 | 540K| 18 (0)| 00:00:01 | | | | S->P | HASH |
| 8 | PARTITION LIST ALL | | 10639 | 540K| 18 (0)| 00:00:01 | 1 | 10 | | | |
| 9 | TABLE ACCESS FULL | PT1 | 10639 | 540K| 18 (0)| 00:00:01 | 1 | 10 | | | |
| 10 | PX RECEIVE | | 10264 | 521K| 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10001 | 10264 | 521K| 2 (0)| 00:00:01 | | | Q1,01 | P->P | HASH |
| 12 | PX BLOCK ITERATOR | | 10264 | 521K| 2 (0)| 00:00:01 |:BF0000|:BF0000| Q1,01 | PCWC | |
| 13 | TABLE ACCESS FULL | PT2 | 10264 | 521K| 2 (0)| 00:00:01 |:BF0000|:BF0000| Q1,01 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PT1"."N4"="PT2"."N4")
-- 위와 같은 쿼리에 억지로 HASH 분배 적용
SELECT /*+ USE_HASH(PT1, PT2) ORDERED PARALLEL(PT2 4) */ * FROM PT1, PT2 WHERE PT1.N2 = PT2.N2;
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 113K| 11M| 21 (5)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 113K| 11M| 21 (5)| 00:00:01 | | | Q1,02 | P->S | QC (RAND) |
|* 3 | HASH JOIN BUFFERED | | 113K| 11M| 21 (5)| 00:00:01 | | | Q1,02 | PCWP | |
| 4 | BUFFER SORT | | | | | | | | Q1,02 | PCWC | |
| 5 | PX RECEIVE | | 10639 | 540K| 18 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | 10639 | 540K| 18 (0)| 00:00:01 | | | | S->P | HASH |
| 7 | PARTITION LIST ALL| | 10639 | 540K| 18 (0)| 00:00:01 | 1 | 10 | | | |
| 8 | TABLE ACCESS FULL| PT1 | 10639 | 540K| 18 (0)| 00:00:01 | 1 | 10 | | | |
| 9 | PX RECEIVE | | 10264 | 521K| 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 10 | PX SEND HASH | :TQ10001 | 10264 | 521K| 2 (0)| 00:00:01 | | | Q1,01 | P->P | HASH |
| 11 | PX BLOCK ITERATOR | | 10264 | 521K| 2 (0)| 00:00:01 | 1 | 10 | Q1,01 | PCWC | |
| 12 | TABLE ACCESS FULL | PT2 | 10264 | 521K| 2 (0)| 00:00:01 | 1 | 10 | Q1,01 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PT1"."N2"="PT2"."N2")
-- 파티션 되지 않은 컬럼(N2) 로 조인 하면 HASH 분배 발생
SELECT /*+ USE_HASH(PT1, PT2) ORDERED PARALLEL(PT2 4) PQ_DISTRIBUTE(PT2 BROADCAST NONE) */ * FROM PT1, PT2 WHERE PT1.N2 = PT2.N2;
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 113K| 11M| 21 (5)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 113K| 11M| 21 (5)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 113K| 11M| 21 (5)| 00:00:01 | | | Q1,01 | PCWP | |
| 4 | BUFFER SORT | | | | | | | | Q1,01 | PCWC | |
| 5 | PX RECEIVE | | 10639 | 540K| 18 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 6 | PX SEND BROADCAST | :TQ10000 | 10639 | 540K| 18 (0)| 00:00:01 | | | | S->P | BROADCAST |
| 7 | PARTITION LIST ALL| | 10639 | 540K| 18 (0)| 00:00:01 | 1 | 10 | | | |
| 8 | TABLE ACCESS FULL| PT1 | 10639 | 540K| 18 (0)| 00:00:01 | 1 | 10 | | | |
| 9 | PX BLOCK ITERATOR | | 10264 | 521K| 2 (0)| 00:00:01 | 1 | 10 | Q1,01 | PCWC | |
| 10 | TABLE ACCESS FULL | PT2 | 10264 | 521K| 2 (0)| 00:00:01 | 1 | 10 | Q1,01 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PT1"."N2"="PT2"."N2")
-- 위와 같은 쿼리에 PT1을 억지로 BROADCAST 분배 적용 (비효율)
SELECT /*+ USE_HASH(PT1, PT2) ORDERED PARALLEL(PT2 4) */ * FROM PT1, PT2 WHERE PT1.N3 = PT2.N1 AND PT1.N1 = 0;
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 104 | 24 (5)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 104 | 24 (5)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 1 | 104 | 24 (5)| 00:00:01 | | | Q1,01 | PCWP | |
| 4 | BUFFER SORT | | | | | | | | Q1,01 | PCWC | |
| 5 | PX RECEIVE | | 1 | 52 | 18 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 6 | PX SEND BROADCAST | :TQ10000 | 1 | 52 | 18 (0)| 00:00:01 | | | | S->P | BROADCAST |
| 7 | PARTITION LIST ALL| | 1 | 52 | 18 (0)| 00:00:01 | 1 | 10 | | | |
|* 8 | TABLE ACCESS FULL| PT1 | 1 | 52 | 18 (0)| 00:00:01 | 1 | 10 | | | |
| 9 | PX BLOCK ITERATOR | | 10264 | 521K| 5 (0)| 00:00:01 | 1 | 10 | Q1,01 | PCWC | |
|* 10 | TABLE ACCESS FULL | PT2 | 10264 | 521K| 5 (0)| 00:00:01 | 1 | 10 | Q1,01 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PT1"."N3"="PT2"."N1")
8 - filter("PT1"."N1"=0)
10 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"PT2"."N1"))
-- 위와 같은 쿼리에 PT1 쪽이 작으므로 자연스럽게 PT1 의 BROADCAST 발생
SELECT /*+ USE_HASH(PT1, PT2) ORDERED PARALLEL(PT2 4) */ * FROM PT2, PT1 WHERE PT1.N3 = PT2.N1 AND PT1.N1 = 0;
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 104 | 24 (5)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 104 | 24 (5)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 1 | 104 | 24 (5)| 00:00:01 | | | Q1,01 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 10264 | 521K| 5 (0)| 00:00:01 | 1 | 10 | Q1,01 | PCWC | |
| 5 | TABLE ACCESS FULL | PT2 | 10264 | 521K| 5 (0)| 00:00:01 | 1 | 10 | Q1,01 | PCWP | |
| 6 | BUFFER SORT | | | | | | | | Q1,01 | PCWC | |
| 7 | PX RECEIVE | | 1 | 52 | 18 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 8 | PX SEND BROADCAST | :TQ10000 | 1 | 52 | 18 (0)| 00:00:01 | | | | S->P | BROADCAST |
| 9 | PARTITION LIST ALL| | 1 | 52 | 18 (0)| 00:00:01 | 1 | 10 | | | |
|* 10 | TABLE ACCESS FULL| PT1 | 1 | 52 | 18 (0)| 00:00:01 | 1 | 10 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PT1"."N3"="PT2"."N1")
10 - filter("PT1"."N1"=0)
-- 위와 같은 쿼리에 조인 순서를 바꾼결과 역시 PT1 쪽이 작으므로 자연스럽게 PT1 의 BROADCAST 발생
-- http://121.254.172.39:8080/pls/apex/f?p=101:11:0::::P11_QUESTION_ID:6418000346262286
-- http://download.oracle.com/docs/cd/E11882_01/server.112/e10837/parallel002.htm
-- 생산자(Producer) 와 소비자(Consumer) 프로세스 사이에서 조인할 테이블 로우를 주고 받는 할당 작업(Distribution) 방법 정의
-- 표현형식 : /*+ PQ_DISTRUBUTE(table, outer_distribution, inner_distribution) */
-- * HASH : 조인 키 컬럼에 대해 해쉬 함수 결과 값을 이용하여 분배
-- * BROADCAST : 외측 테이블의 전체 로우를 모든 Consumer 프로세스로 보냄
-- * PARTITION : 상대편 조인 대상 테이블이 조인 키 컬럼으로 파티션되어 있는 경우 파티션 키 값을 이용
-- * NONE : ...
-- 가능한 COMBINATION
-- HASH - HASH : OUTER, INNER 크기가 비슷할 때
-- BROADCAST - NONE : OUTER 테이블이 작을때
-- NONE - BROADCAST : INNER 테이블이 작을때
-- PARTITION - NONE : OUTER 테이블을 파티션 하여 분배
-- NONE - PARTITION : INNER 테이블을 파티션 하여 분배
-- NONE - NONE : 조인컬럼 기준으로 파티션 되어 있을때
"구루비 데이터베이스 스터디모임" 에서 2011년에 "새로쓴 대용량 데이터베이스 솔루션1" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4444
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.