HASH JOIN BUFFERED 오퍼레이션은 병렬 해시 조인시 조인 결과를 버퍼링한다. 병렬 서버는 데이터를 수신(consumer)하는 동안 데이터를 송신(producer)할 수 없기 때문에 수신한 데이터로 병렬 해시 조인을 수행하는 경우 조인 결과를 버퍼링하고, 조인이 완료된 후 조인 결과를 송신한다. 조인 결과가 큰 경우 버퍼링으로 인한 성능 저하가 발생할 수 있다. 관련 내용을 살펴보자.
테스트를 위해 아래와 같이 테이블을 생성하자.
-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
DROP TABLE t3 PURGE;
DROP TABLE t4 PURGE;
DROP TABLE t5 PURGE;
CREATE TABLE t1 AS SELECT ROWNUM AS c1, ROWNUM AS c2 FROM XMLTABLE ('1 to 10000');
CREATE TABLE t2 AS SELECT * FROM t1;
CREATE TABLE t3 AS SELECT * FROM t1;
CREATE TABLE t4 AS SELECT * FROM t1;
CREATE TABLE t5 AS SELECT * FROM t1 WHERE 0 = 1;
아래 쿼리는 t1, t2 테이블을 병렬 해시 조인한다. 실행 계획 3번에서 HASH JOIN BUFFERED 오퍼레이션이 발생한 것을 확인할 수 있다. 동작 순서를 살펴보자. 4단계에서 수신한 데이터로 조인을 수행하기 때문에 조인 결과가 버퍼링되었다.
- 세트1(Q1,00)이 t1 테이블을 읽어 세트2(Q1,02)로 데이터를 송신 (7 -> 6 -> 5)
- 세트2(Q1,02)가 데이터를 수신하여 해시 맵을 생성 (4 -> 3)
- 세트1(Q1,01)이 t2 테이블을 읽어 세트2(Q1,02)로 데이터를 송신 (11 -> 10 -> 9)
- 세트2(Q1,02)가 데이터를 수신하여 조인을 수행하고 조인 결과를 버퍼링 (8 -> 3)
- 조인이 완료되면 세트2(Q1,02)가 QC로 조인 결과를 송신 (2 -> 1)
-- 2
SELECT /*+ ORDERED PARALLEL(A 2) PARALLEL(B 2) PQ_DISTRIBUTE(B HASH HASH) */
*
FROM t1 a, t2 b
WHERE b.c1 = a.c1;
------------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib | Used-Mem |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | Q1,02 | P->S | QC (RAND) | |
|* 3 | HASH JOIN BUFFERED | | Q1,02 | PCWP | | 5404K (0)| -- !
| 4 | PX RECEIVE | | Q1,02 | PCWP | | |
| 5 | PX SEND HASH | :TQ10000 | Q1,00 | P->P | HASH | |
| 6 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | |
|* 7 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | | |
| 8 | PX RECEIVE | | Q1,02 | PCWP | | |
| 9 | PX SEND HASH | :TQ10001 | Q1,01 | P->P | HASH | |
| 10 | PX BLOCK ITERATOR | | Q1,01 | PCWC | | |
|* 11 | TABLE ACCESS FULL| T2 | Q1,01 | PCWP | | |
------------------------------------------------------------------------------------
아래 쿼리는 t1 테이블을 BROADCAST했다. HASH JOIN BUFFERED 오퍼레이션이 발생하지 않은 것을 확인할 수 있다. 동작 순서를 살펴보자. 2단계에서 각각의 병렬 서버가 전체 테이블로 해시 맵을 생성했기 때문에 3단계에서 동일한 병렬 서버가 조인을 수행할 수 있다.
- 세트1(Q1,00)이 t1 테이블을 읽어 세트2(Q1,01)로 데이터를 송신 (7 -> 6 -> 5)
- 세트2(Q1,01)가 데이터를 수신하여 해시 맵을 생성 (4 -> 3)
- 세트2(Q1,01)가 t2 테이블을 읽어 조인을 수행하고 QC로 조인 결과를 송신 (9 -> 8 -> 3 -> 2 -> 1)
-- 3
SELECT /*+ ORDERED
PARALLEL(A 2)
PARALLEL(B 2)
PQ_DISTRIBUTE(B BROADCAST NONE)
NO_PQ_REPLICATE(B) */
*
FROM t1 a, t2 b
WHERE b.c1 = a.c1;
------------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib | Used-Mem |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | Q1,01 | P->S | QC (RAND) | |
|* 3 | HASH JOIN | | Q1,01 | PCWP | | 2161K (0)|
| 4 | PX RECEIVE | | Q1,01 | PCWP | | |
| 5 | PX SEND BROADCAST | :TQ10000 | Q1,00 | P->P | BROADCAST | |
| 6 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | |
|* 7 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | | |
| 8 | PX BLOCK ITERATOR | | Q1,01 | PCWC | | |
|* 9 | TABLE ACCESS FULL | T2 | Q1,01 | PCWP | | |
------------------------------------------------------------------------------------
아래 쿼리는 t2 테이블을 PQ_REPLICATE했다.1 HASH JOIN BUFFERED 오퍼레이션이 발생하지 않았다. 동작 순서를 살펴보자. 2단계에서 각각의 병렬 서버가 전체 테이블을 읽으므로 동일한 병렬 서버가 조인을 수행할 수 있다.
- 세트1(Q1,00)이 t1 테이블을 읽어 해시 맵을 생성 (5 -> 4 -> 3)
- 세트1(Q1,00)이 t2 테이블을 읽어 조인을 수행하고 QC로 조인 결과를 송신 (6 -> 3 -> 2 -> 1)
-- 4
SELECT /*+ ORDERED
PARALLEL(A 2)
PARALLEL(B 2)
PQ_DISTRIBUTE(B NONE BROADCAST)
PQ_REPLICATE(B) */
*
FROM t1 a, t2 b
WHERE b.c1 = a.c1;
----------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib | Used-Mem |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) | |
|* 3 | HASH JOIN | | Q1,00 | PCWP | | 1669K (0)|
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | |
|* 5 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | | |
| 6 | TABLE ACCESS FULL | T2 | Q1,00 | PCWP | | |
----------------------------------------------------------------------------------
아래 쿼리는 GROUP BY 절을 사용했다. HASH JOIN BUFFERED 오퍼레이션이 발생하지 않는다. 실행 계획 3번 HASH GROUP BY 오퍼레이션에서 집계 결과를 저장했기 때문이다.
-- 5
SELECT /*+ ORDERED PARALLEL(A 2) PARALLEL(B 2) PQ_DISTRIBUTE(B HASH HASH) */
a.c1, COUNT (*)
FROM t1 a, t2 b
WHERE b.c1 = a.c1
GROUP BY a.c1;
-------------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib | Used-Mem |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | Q1,02 | P->S | QC (RAND) | |
| 3 | HASH GROUP BY | | Q1,02 | PCWP | | 1378K (0)|
|* 4 | HASH JOIN | | Q1,02 | PCWP | | 1660K (0)|
| 5 | PX RECEIVE | | Q1,02 | PCWP | | |
| 6 | PX SEND HASH | :TQ10000 | Q1,00 | P->P | HASH | |
| 7 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | |
|* 8 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | | |
| 9 | PX RECEIVE | | Q1,02 | PCWP | | |
| 10 | PX SEND HASH | :TQ10001 | Q1,01 | P->P | HASH | |
| 11 | PX BLOCK ITERATOR | | Q1,01 | PCWC | | |
|* 12 | TABLE ACCESS FULL| T2 | Q1,01 | PCWP | | |
-------------------------------------------------------------------------------------
-- 6
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ PARALLEL(T 2) NO_GATHER_OPTIMIZER_STATISTICS */
INTO t5 t
SELECT /*+ ORDERED PARALLEL(A 2) PARALLEL(B 2) PQ_DISTRIBUTE(B HASH HASH) */
a.*
FROM t1 a, t2 b
WHERE b.c1 = a.c1;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib | Used-Mem |
-----------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | | |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | Q1,02 | P->S | QC (RAND) | |
| 3 | LOAD AS SELECT (HYBRID TSM/HWMB)| T9 | Q1,02 | PCWP | | 1040K (0)|
|* 4 | HASH JOIN | | Q1,02 | PCWP | | 1690K (0)|
| 5 | PX RECEIVE | | Q1,02 | PCWP | | |
| 6 | PX SEND HASH | :TQ10000 | Q1,00 | P->P | HASH | |
| 7 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | |
|* 8 | TABLE ACCESS FULL | T1 | Q1,00 | PCWP | | |
| 9 | PX RECEIVE | | Q1,02 | PCWP | | |
| 10 | PX SEND HASH | :TQ10001 | Q1,01 | P->P | HASH | |
| 11 | PX BLOCK ITERATOR | | Q1,01 | PCWC | | |
|* 12 | TABLE ACCESS FULL | T2 | Q1,01 | PCWP | | |
-----------------------------------------------------------------------------------------------
PARALLEL DML을 비활성화해보자. HASH JOIN BUFFERED 오퍼레이션이 발생했다. 조인 결과를 QC에 전송해야 하므로 조인 결과를 버퍼링해야 한다.
-- 7
ALTER SESSION DISABLE PARALLEL DML;
INSERT /*+ PARALLEL(T 2) NO_GATHER_OPTIMIZER_STATISTICS */
INTO t5 t
SELECT /*+ ORDERED PARALLEL(A 2) PARALLEL(B 2) PQ_DISTRIBUTE(B HASH HASH) */
a.*
FROM t1 a, t2 b
WHERE b.c1 = a.c1;
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | TQ |IN-OUT| PQ Distrib | Used-Mem |
----------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | | | |
| 1 | LOAD TABLE CONVENTIONAL | T5 | 1 | | | | |
| 2 | PX COORDINATOR | | 1 | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | 0 | Q1,02 | P->S | QC (RAND) | |
|* 4 | HASH JOIN BUFFERED | | 0 | Q1,02 | PCWP | | 4862K (0)| -- !
| 5 | PX RECEIVE | | 0 | Q1,02 | PCWP | | |
| 6 | PX SEND HASH | :TQ10000 | 0 | Q1,00 | P->P | HASH | |
| 7 | PX BLOCK ITERATOR | | 0 | Q1,00 | PCWC | | |
|* 8 | TABLE ACCESS FULL| T1 | 0 | Q1,00 | PCWP | | |
| 9 | PX RECEIVE | | 0 | Q1,02 | PCWP | | |
| 10 | PX SEND HASH | :TQ10001 | 0 | Q1,01 | P->P | HASH | |
| 11 | PX BLOCK ITERATOR | | 0 | Q1,01 | PCWC | | |
|* 12 | TABLE ACCESS FULL| T2 | 0 | Q1,01 | PCWP | | |
----------------------------------------------------------------------------------------------
지금까지 살펴본 내용을 정리하면 아래의 경우에 HASH JOIN BUFFERED 오퍼레이션이 발생하지 않았다.
- Build Input을 BROADCAST
- Prove Input을 PQ_REPLICATE
- 조인 결과을 집계
- 조인 결과를 삽입
조금 더 복잡한 상황을 살펴보자. 아래 쿼리는 SELECT 문에서 3개의 테이블을 조인했다. 실행 계획 3번에서 HASH JOIN BUFFERED 오퍼레이션이 발생한다. 동작 순서를 살펴보자. 조인 키가 c1 열로 동일하기 때문에 4단계에서 조인 결과로 해시 맵을 생성했다.
- 세트1(Q1,00)이 t1 테이블을 읽어 세트2(Q1,03)로 데이터를 송신 (8 -> 7 -> 6)
- 세트2(Q1,03)가 데이터를 수신하여 해시 맵을 생성 (5 -> 4)
- 세트1(Q1,01)이 t2 테이블을 읽어 세트2(Q1,03)로 데이터를 송신 (12 -> 11 -> 10)
- 세트2(Q1,03)가 데이터를 수신하여 조인을 수행하고 조인 결과로 해시 맵을 생성 (9 -> 4)
- 세트1(Q1,02)이 t3 테이블을 읽어 세트2(Q1,03)로 데이터를 송신 (16 -> 15 -> 14)
- 세트2(Q1,03)가 데이터를 수신하여 조인을 수행하고 조인 결과를 버퍼링 (13 -> 3)
- 조인 완료되면 세트2(Q1,03)가 QC로 조인 결과를 송신 (2 -> 1)
-- 8
SELECT /*+ ORDERED
PARALLEL(A 2)
PARALLEL(B 2)
PARALLEL(C 2)
PQ_DISTRIBUTE(B HASH HASH)
PQ_DISTRIBUTE(C HASH HASH)
NO_SWAP_JOIN_INPUTS(B)
NO_SWAP_JOIN_INPUTS(C) */
a.*
FROM t1 a, t2 b, t3 c
WHERE b.c1 = a.c1
AND c.c1 = b.c1;
-------------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib | Used-Mem |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10003 | Q1,03 | P->S | QC (RAND) | |
|* 3 | HASH JOIN BUFFERED | | Q1,03 | PCWP | | 4907K (0)| -- !
|* 4 | HASH JOIN | | Q1,03 | PCWP | | 1651K (0)|
| 5 | PX RECEIVE | | Q1,03 | PCWP | | |
| 6 | PX SEND HASH | :TQ10000 | Q1,00 | P->P | HASH | |
| 7 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | |
|* 8 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | | |
| 9 | PX RECEIVE | | Q1,03 | PCWP | | |
| 10 | PX SEND HASH | :TQ10001 | Q1,01 | P->P | HASH | |
| 11 | PX BLOCK ITERATOR | | Q1,01 | PCWC | | |
|* 12 | TABLE ACCESS FULL| T2 | Q1,01 | PCWP | | |
| 13 | PX RECEIVE | | Q1,03 | PCWP | | |
| 14 | PX SEND HASH | :TQ10002 | Q1,02 | P->P | HASH | |
| 15 | PX BLOCK ITERATOR | | Q1,02 | PCWC | | |
|* 16 | TABLE ACCESS FULL | T3 | Q1,02 | PCWP | | |
-------------------------------------------------------------------------------------
아래 쿼리는 t2 테이블의 c1 열과 t3 테이블의 c2 열을 조인 키로 사용했다. 실행 계획 6번에서도 HASH JOIN BUFFERED 오퍼레이션이 발생한다. 동작 순서를 살펴보자. t3 테이블과 조인하기 위해 c2 열로 데이터를 송신해야 하므로 4단계에서 조인 결과를 버퍼링했다.
- 세트1(Q1,00)이 t1 테이블을 읽어 세트2(Q1,02)로 데이터를 송신 (10 -> 9 -> 8)
- 세트2(Q1,02)가 데이터를 수신하여 해시 맵을 생성 (7 -> 6)
- 세트1(Q1,01)이 t2 테이블을 읽어 세트2(Q1,02)로 데이터를 송신 (14 -> 13 -> 12)
- 세트2(Q1,02)가 데이터를 수신하여 조인을 수행하고 조인 결과를 버퍼링 (11 -> 6)
- 조인이 완료되면 세트2(Q1,02)가 조인 결과를 세트1(Q1,04)으로 전송 (5)
- 세트1(Q1,04)이 데이터를 수신하여 해시 맵을 생성 (4 -> 3)
- 세트2(Q1,03)가 t3 테이블을 읽어 세트1(Q1,04)으로 데이터를 송신 (18 -> 17 -> 16)
- 세트1(Q1,04)이 데이터를 수신하여 조인을 수행하고 조인 결과를 버퍼링 (15 -> 3)
- 조인이 완료되면 세트1(Q1,04)이 QC로 조인 결과를 송신 (2 -> 1)
-- 9
SELECT /*+ ORDERED
PARALLEL(A 2)
PARALLEL(B 2)
PARALLEL(C 2)
PQ_DISTRIBUTE(B HASH HASH)
PQ_DISTRIBUTE(C HASH HASH)
NO_SWAP_JOIN_INPUTS(B)
NO_SWAP_JOIN_INPUTS(C) */
a.*
FROM t1 a, t2 b, t3 c
WHERE b.c1 = a.c1
AND c.c1 = b.c2;
---------------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib | Used-Mem |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10004 | Q1,04 | P->S | QC (RAND) | |
|* 3 | HASH JOIN BUFFERED | | Q1,04 | PCWP | | 4896K (0)| -- !
| 4 | PX RECEIVE | | Q1,04 | PCWP | | |
| 5 | PX SEND HASH | :TQ10002 | Q1,02 | P->P | HASH | |
|* 6 | HASH JOIN BUFFERED | | Q1,02 | PCWP | | 4885K (0)| -- !
| 7 | PX RECEIVE | | Q1,02 | PCWP | | |
| 8 | PX SEND HASH | :TQ10000 | Q1,00 | P->P | HASH | |
| 9 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | |
|* 10 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | | |
| 11 | PX RECEIVE | | Q1,02 | PCWP | | |
| 12 | PX SEND HASH | :TQ10001 | Q1,01 | P->P | HASH | |
| 13 | PX BLOCK ITERATOR | | Q1,01 | PCWC | | |
|* 14 | TABLE ACCESS FULL| T2 | Q1,01 | PCWP | | |
| 15 | PX RECEIVE | | Q1,04 | PCWP | | |
| 16 | PX SEND HASH | :TQ10003 | Q1,03 | P->P | HASH | |
| 17 | PX BLOCK ITERATOR | | Q1,03 | PCWC | | |
|* 18 | TABLE ACCESS FULL | T3 | Q1,03 | PCWP | | |
---------------------------------------------------------------------------------------
아래 쿼리는 t3 테이블을 PQ_REPLICATE했다. HASH JOIN BUFFERED 오퍼레이션이 발생하지 않는다. 동작 순서를 살펴보자. 5단계에서 각각의 병렬 서버가 전체 테이블을 읽으므로 동일한 병렬 서버가 조인을 수행했다.
- 세트1(Q1,00)이 t1 테이블을 읽어 세트2(Q1,02)로 데이터를 송신 (8 -> 7 -> 6)
- 세트2(Q1,02)가 데이터를 수신하여 해시 맵을 생성 (5 -> 4)
- 세트1(Q1,01)이 t2 테이블을 읽어 세트2(Q1,02)로 데이터를 송신 (12 -> 11 -> 10)
- 세트2(Q1,02)가 데이터를 수신하여 조인을 수행하고 조인 결과로 해시맵을 생성 (9 -> 4 -> 3)
- 세트2(Q1,02)가 t3 테이블을 읽어 조인을 수행하고 QC로 조인 결과를 송신 (13 -> 3 -> 2 -> 1)
-- 10
SELECT /*+ ORDERED
PARALLEL(A 2)
PARALLEL(B 2)
PARALLEL(C 2)
PQ_DISTRIBUTE(B HASH HASH)
PQ_DISTRIBUTE(C NONE BROADCAST)
NO_SWAP_JOIN_INPUTS(B)
NO_SWAP_JOIN_INPUTS(C)
PQ_REPLICATE(C) */
a.*
FROM t1 a, t2 b, t3 c
WHERE b.c1 = a.c1
AND c.c1 = b.c2;
-------------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib | Used-Mem |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | Q1,02 | P->S | QC (RAND) | |
|* 3 | HASH JOIN | | Q1,02 | PCWP | | 1690K (0)|
|* 4 | HASH JOIN | | Q1,02 | PCWP | | 1666K (0)|
| 5 | PX RECEIVE | | Q1,02 | PCWP | | |
| 6 | PX SEND HASH | :TQ10000 | Q1,00 | P->P | HASH | |
| 7 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | |
|* 8 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | | |
| 9 | PX RECEIVE | | Q1,02 | PCWP | | |
| 10 | PX SEND HASH | :TQ10001 | Q1,01 | P->P | HASH | |
| 11 | PX BLOCK ITERATOR | | Q1,01 | PCWC | | |
|* 12 | TABLE ACCESS FULL| T2 | Q1,01 | PCWP | | |
| 13 | TABLE ACCESS FULL | T3 | Q1,02 | PCWP | | |
-------------------------------------------------------------------------------------
아래 쿼리는 4개의 테이블을 조인한다. t1 ~ t3 테이블의 조인 결과(10 번 쿼리와 동일)를 BROADCAST했다. 조인 결과를 테이블로 가정하면 3번 쿼리와 동일한 패턴이다.
-- 11
SELECT /*+ ORDERED
PARALLEL(A 2)
PARALLEL(B 2)
PARALLEL(C 2)
PARALLEL(D 2)
PQ_DISTRIBUTE(B HASH HASH)
PQ_DISTRIBUTE(C NONE BROADCAST)
PQ_DISTRIBUTE(D BROADCAST NONE)
NO_SWAP_JOIN_INPUTS(B)
NO_SWAP_JOIN_INPUTS(C)
NO_SWAP_JOIN_INPUTS(D)
PQ_REPLICATE(C) */
a.*
FROM t1 a, t2 b, t3 c, t4 d
WHERE b.c1 = a.c1
AND c.c1 = b.c2
AND d.c1 = c.c2;
----------------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib | Used-Mem |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10003 | Q1,03 | P->S | QC (RAND) | |
|* 3 | HASH JOIN | | Q1,03 | PCWP | | 2224K (0)|
| 4 | PX RECEIVE | | Q1,03 | PCWP | | |
| 5 | PX SEND BROADCAST | :TQ10002 | Q1,02 | P->P | BROADCAST | |
|* 6 | HASH JOIN | | Q1,02 | PCWP | | 1643K (0)|
|* 7 | HASH JOIN | | Q1,02 | PCWP | | 1666K (0)|
| 8 | PX RECEIVE | | Q1,02 | PCWP | | |
| 9 | PX SEND HASH | :TQ10000 | Q1,00 | P->P | HASH | |
| 10 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | |
|* 11 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | | |
| 12 | PX RECEIVE | | Q1,02 | PCWP | | |
| 13 | PX SEND HASH | :TQ10001 | Q1,01 | P->P | HASH | |
| 14 | PX BLOCK ITERATOR | | Q1,01 | PCWC | | |
|* 15 | TABLE ACCESS FULL| T2 | Q1,01 | PCWP | | |
| 16 | TABLE ACCESS FULL | T3 | Q1,02 | PCWP | | |
| 17 | PX BLOCK ITERATOR | | Q1,03 | PCWC | | |
|* 18 | TABLE ACCESS FULL | T4 | Q1,03 | PCWP | | |
----------------------------------------------------------------------------------------
아래 쿼리는 t4 테이블을 PQ_REPLICATE했다. 조인 결과를 테이블로 가정하면 4번 쿼리와 동일한 패턴이다.
-- 12
SELECT /*+ ORDERED
PARALLEL(A 2)
PARALLEL(B 2)
PARALLEL(C 2)
PARALLEL(D 2)
PQ_DISTRIBUTE(B HASH HASH)
PQ_DISTRIBUTE(C NONE BROADCAST)
PQ_DISTRIBUTE(D NONE BROADCAST)
NO_SWAP_JOIN_INPUTS(B)
NO_SWAP_JOIN_INPUTS(C)
NO_SWAP_JOIN_INPUTS(D)
PQ_REPLICATE(C)
PQ_REPLICATE(D) */
a.*
FROM t1 a, t2 b, t3 c, t4 d
WHERE b.c1 = a.c1
AND c.c1 = b.c2
AND d.c1 = c.c2;
--------------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib | Used-Mem |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | Q1,02 | P->S | QC (RAND) | |
|* 3 | HASH JOIN | | Q1,02 | PCWP | | 1642K (0)|
|* 4 | HASH JOIN | | Q1,02 | PCWP | | 1651K (0)|
|* 5 | HASH JOIN | | Q1,02 | PCWP | | 1666K (0)|
| 6 | PX RECEIVE | | Q1,02 | PCWP | | |
| 7 | PX SEND HASH | :TQ10000 | Q1,00 | P->P | HASH | |
| 8 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | |
|* 9 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | | |
| 10 | PX RECEIVE | | Q1,02 | PCWP | | |
| 11 | PX SEND HASH | :TQ10001 | Q1,01 | P->P | HASH | |
| 12 | PX BLOCK ITERATOR | | Q1,01 | PCWC | | |
|* 13 | TABLE ACCESS FULL| T2 | Q1,01 | PCWP | | |
| 14 | TABLE ACCESS FULL | T3 | Q1,02 | PCWP | | |
| 15 | TABLE ACCESS FULL | T4 | Q1,02 | PCWP | | |
--------------------------------------------------------------------------------------
아래 쿼리는 5개의 테이블을 조인한다. t5 테이블을 PQ_REPLICATE했다. t1 ~ t4 테이블의 조인 결과까지는 11번 쿼리와 동일하며, 조인 결과를 하나의 테이블로 가정하면 4번 쿼리와 동일한 패턴이다.
-- 13
SELECT /*+ ORDERED
PARALLEL(A 2)
PARALLEL(B 2)
PARALLEL(C 2)
PARALLEL(D 2)
PARALLEL(E 2)
PQ_DISTRIBUTE(B HASH HASH)
PQ_DISTRIBUTE(C NONE BROADCAST)
PQ_DISTRIBUTE(D BROADCAST NONE)
PQ_DISTRIBUTE(E NONE BROADCAST)
NO_SWAP_JOIN_INPUTS(B)
NO_SWAP_JOIN_INPUTS(C)
NO_SWAP_JOIN_INPUTS(D)
NO_SWAP_JOIN_INPUTS(E)
PQ_REPLICATE(C)
NO_PQ_REPLICATE(D)
PQ_REPLICATE(E) */
a.*
FROM t1 a, t2 b, t3 c, t4 d, t5 e
WHERE b.c1 = a.c1
AND c.c1 = b.c2
AND d.c1 = c.c2
AND e.c1 = d.c2;
-----------------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib | Used-Mem |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10003 | Q1,03 | P->S | QC (RAND) | |
|* 3 | HASH JOIN | | Q1,03 | PCWP | | 1523K (0)|
|* 4 | HASH JOIN | | Q1,03 | PCWP | | 2161K (0)|
| 5 | PX RECEIVE | | Q1,03 | PCWP | | |
| 6 | PX SEND BROADCAST | :TQ10002 | Q1,02 | P->P | BROADCAST | |
|* 7 | HASH JOIN | | Q1,02 | PCWP | | 1696K (0)|
|* 8 | HASH JOIN | | Q1,02 | PCWP | | 1697K (0)|
| 9 | PX RECEIVE | | Q1,02 | PCWP | | |
| 10 | PX SEND HASH | :TQ10000 | Q1,00 | P->P | HASH | |
| 11 | PX BLOCK ITERATOR | | Q1,00 | PCWC | | |
|* 12 | TABLE ACCESS FULL| T1 | Q1,00 | PCWP | | |
| 13 | PX RECEIVE | | Q1,02 | PCWP | | |
| 14 | PX SEND HASH | :TQ10001 | Q1,01 | P->P | HASH | |
| 15 | PX BLOCK ITERATOR | | Q1,01 | PCWC | | |
|* 16 | TABLE ACCESS FULL| T2 | Q1,01 | PCWP | | |
| 17 | TABLE ACCESS FULL | T3 | Q1,02 | PCWP | | |
| 18 | PX BLOCK ITERATOR | | Q1,03 | PCWC | | |
|* 19 | TABLE ACCESS FULL | T4 | Q1,03 | PCWP | | |
| 20 | TABLE ACCESS FULL | T5 | Q1,03 | PCWP | | |
-----------------------------------------------------------------------------------------
지금까지 살펴본데로 BROADCAST와 PQ_REPLICATE를 적절히 조정해야 HASH JOIN BUFFERED 오퍼레이션을 회피할 수 있다. 테이블과 조인 결과의 크기, 병렬도 등을 고려해야 하기 때문에 적용이 어려울 수도 있지만 대량 배치나 데이터 이행에 필요한 성능 개선 요소이므로 동작 원리를 이해할 필요가 있다.
- PQ_REPLICATE는 12.1 버전에서 도입된 기능이다. 각각의 병렬 서버가 전체 테이블을 읽기 때문에 데이터 분배가 발생하지 않는다.(http://hrjeong.tistory.com/190 참조) [본문으로]
'Oracle > Tuning' 카테고리의 다른 글
| 일별 배치 튜닝 사례 (0) | 2018.06.21 |
|---|---|
| TABLE ACCESS BY INDEX ROWID BATCHED 오퍼레이션 #2 (0) | 2018.06.21 |
| NVL 함수와 스칼라 서브 쿼리 (0) | 2018.06.15 |
| Band Join 기능 (0) | 2018.03.13 |
| TABLE ACCESS BY INDEX ROWID BATCHED 오퍼레이션 #1 (0) | 2018.03.09 |
