병렬 MERGE 문은 아래의 두 가지 데이터 분배 방식을 사용합니다. 12.0.1.2 이상 버전은 타깃 테이블이 비파티션 테이블인 경우 PX SEND (ROWID RANDOM) 분배 방식을 사용하고, 타깃 테이블이 파티션 테이블인 경우 PX SEND HYBRID (ROWID PKEY) 분배 방식을 사용합니다. 타깃 테이블이 파티션 테이블인 경우 삽입되는 데이터에 따라 데이터가 불균형하게 분배될 수 있습니다.
- PX SEND (ROWID RANDOM) 분배 방식: UPDATE는 ROWID 범위, INSERT는 RANDOM 방식으로 분배
- PX SEND HYBRID (ROWID PKEY) 분배 방식: UPDATE는 ROWID 범위, INSERT는 PK 범위로 분배
테스트를 위해 아래와 같이 t1, t2, t3 테이블을 생성하겠습니다. t1이 소스 테이블, t2, t3가 타깃 테이블입니다. 아울러 병렬 DML 기능을 활성화하고, 온라인 통계 수집 기능을 비활성화합니다.
-- 1-1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
DROP TABLE t3 PURGE;
CREATE TABLE t1 (c1, c2) AS
SELECT DATE '2049-12-31' + CEIL (ROWNUM / 1000)
, NVL (NULLIF (MOD (ROWNUM, 1000), 0), 1000)
FROM XMLTABLE ('1 to 31000');
CREATE TABLE t2 (c1 DATE, c2 NUMBER);
CREATE TABLE t3 (c1 DATE, c2 NUMBER)
PARTITION BY RANGE (c1) (
PARTITION p205001 VALUES LESS THAN (DATE '2050-02-01')
, PARTITION p205002 VALUES LESS THAN (DATE '2050-03-01')
, PARTITION p205003 VALUES LESS THAN (DATE '2050-04-01')
, PARTITION p205004 VALUES LESS THAN (DATE '2050-05-01')
);
-- 1-2
ALTER SESSION ENABLE PARALLEL DML;
ALTER SESSION SET "_optimizer_gather_stats_on_load" = FALSE;
아래 MERGE 문은 PX SEND (ROWID RANDOM) 분배 방식을 사용합니다. Parallel Execution Details 항목에서 PX 서버가 데이터를 균등하게 삽입한 것을 확인할 수 있습니다.
-- 2
MERGE /*+ PARALLEL(4) */
INTO t2 t
USING t1 s
ON ( t.c1 = s.c1
AND t.c2 = s.c2)
WHEN NOT MATCHED THEN
INSERT (t.c1, t.c2)
VALUES (s.c1, s.c2);
-----------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | Q1,01 | P->S | QC (RAND) |
| 3 | MERGE | T2 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | Q1,01 | PCWP | |
| 5 | PX SEND (ROWID RANDOM) | :TQ10000 | Q1,00 | P->P | (ROWID RAND| -- !
| 6 | VIEW | | Q1,00 | PCWP | |
|* 7 | HASH JOIN RIGHT OUTER| | Q1,00 | PCWP | |
| 8 | TABLE ACCESS FULL | T2 | Q1,00 | PCWP | |
| 9 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
|* 10 | TABLE ACCESS FULL | T1 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------
Parallel Execution Details (DOP=4 , Servers Allocated=8)
============================================
| Name | Type | Server# | Write |
| | | | Bytes |
============================================
| PX Coordinator | QC | | . |
| p000 | Set 1 | 1 | 152KB |
| p001 | Set 1 | 2 | 152KB |
| p002 | Set 1 | 3 | 152KB |
| p003 | Set 1 | 4 | 152KB |
| p004 | Set 2 | 1 | . |
| p005 | Set 2 | 2 | . |
| p006 | Set 2 | 3 | . |
| p007 | Set 2 | 4 | . |
============================================
PX SEND (ROWID RANDOM) 분배 방식은 버그 16405740과 관련이 있습니다. 12.1.0.2 버전부터 MERGE 문의 INSERT 절은 단일 세그먼트에 대해 RANDOM 분배 방식을 사용합니다.
-- 3-1
SELECT bugno, value, sql_feature, description, optimizer_feature_enable
FROM v$system_fix_control
WHERE bugno = 16405740;
BUGNO VALUE SQL_FEATURE DESCRIPTION OPTIMIZER_FEATURE_ENABLE
-------- ----- ------------------ ------------------------------------------------------ ------------------------
16405740 1 QKSFM_DML_16405740 Use random distribution for insert into single segment 12.1.0.2
1개의 행이 선택되었습니다.
-- 3-2
ROLLBACK;
앞선 MERGE 문에 OPTIMIZER_FEATURES_ENABLE('12.1.0.1') 힌트를 추가하면 MERGE 문이 PX SEND (ROWID RANDOM) 분배 방식을 사용합니다. Parallel Execution Details 항목에서 PX 서버가 데이터를 균등하게 삽입한 것을 확인할 수 있습니다.
-- 4
MERGE /*+ PARALLEL(4) OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
NO_GATHER_OPTIMIZER_STATISTICS */
INTO t2 t
USING t1 s
ON ( t.c1 = s.c1
AND t.c2 = s.c2)
WHEN NOT MATCHED THEN
INSERT (t.c1, t.c2)
VALUES (s.c1, s.c2);
---------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | Q1,01 | P->S | QC (RAND) |
| 3 | MERGE | T2 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | Q1,01 | PCWP | |
| 5 | PX SEND HYBRID (ROWID PKEY)| :TQ10000 | Q1,00 | P->P | HYBRID (ROW| -- !
| 6 | VIEW | | Q1,00 | PCWP | |
|* 7 | HASH JOIN RIGHT OUTER | | Q1,00 | PCWP | |
| 8 | TABLE ACCESS FULL | T2 | Q1,00 | PCWP | |
| 9 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
|* 10 | TABLE ACCESS FULL | T1 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------
Parallel Execution Details (DOP=4 , Servers Allocated=8)
============================================
| Name | Type | Server# | Write |
| | | | Bytes |
============================================
| PX Coordinator | QC | | . |
| p000 | Set 1 | 1 | 152KB |
| p001 | Set 1 | 2 | 152KB |
| p002 | Set 1 | 3 | 152KB |
| p003 | Set 1 | 4 | 152KB |
| p004 | Set 2 | 1 | . |
| p005 | Set 2 | 2 | . |
| p006 | Set 2 | 3 | . |
| p007 | Set 2 | 4 | . |
============================================
아래 MERGE 문은 파티션 테이블에 대한 PX SEND HYBRID (ROWID PKEY) 분배 방식에 인해 데이터 분배가 불균등합니다. Parallel Execution Details 항목에서 1개의 PX 서버만 데이터를 삽입한 것을 확인할 수 있습니다.
-- 5
MERGE /*+ PARALLEL(4) */
INTO t3 t
USING t1 s
ON ( t.c1 = s.c1
AND t.c2 = s.c2)
WHEN NOT MATCHED THEN
INSERT (t.c1, t.c2)
VALUES (s.c1, s.c2);
---------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | Q1,01 | P->S | QC (RAND) |
| 3 | MERGE | T3 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | Q1,01 | PCWP | |
| 5 | PX SEND HYBRID (ROWID PKEY)| :TQ10000 | Q1,00 | P->P | HYBRID (ROW| -- !
| 6 | VIEW | | Q1,00 | PCWP | |
|* 7 | HASH JOIN RIGHT OUTER | | Q1,00 | PCWP | |
| 8 | PARTITION RANGE ALL | | Q1,00 | PCWC | |
| 9 | TABLE ACCESS FULL | T3 | Q1,00 | PCWP | |
| 10 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
|* 11 | TABLE ACCESS FULL | T1 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------
Parallel Execution Details (DOP=4 , Servers Allocated=8)
============================================
| Name | Type | Server# | Write |
| | | | Bytes |
============================================
| PX Coordinator | QC | | . |
| p000 | Set 1 | 1 | . |
| p001 | Set 1 | 2 | . |
| p002 | Set 1 | 3 | . |
| p003 | Set 1 | 4 | 584KB | -- !
| p004 | Set 2 | 1 | . |
| p005 | Set 2 | 2 | . |
| p006 | Set 2 | 3 | . |
| p007 | Set 2 | 4 | . |
============================================
소스 데이터가 단일 파티션에 해당하는 경우 파티션을 지정하여 데이터 분배 방식을 변경할 수 있습니다. 아래 MERGE 문은 앞선 MERGE 문에 PARTITION 절을 추가하여 PX SEND (ROWID RANDOM) 분배 방식을 사용합니다. Parallel Execution Details 항목에서 PX 서버가 데이터를 균등하게 삽입한 것을 확인할 수 있습니다.
-- 6
MERGE /*+ PARALLEL(4) */
INTO t3 PARTITION (p205001) t
USING t1 s
ON ( t.c1 = s.c1
AND t.c2 = s.c2)
WHEN NOT MATCHED THEN
INSERT (t.c1, t.c2)
VALUES (s.c1, s.c2);
-------------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | Q1,01 | P->S | QC (RAND) |
| 3 | MERGE | T3 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | Q1,01 | PCWP | |
| 5 | PX SEND (ROWID RANDOM) | :TQ10000 | Q1,00 | P->P | (ROWID RAND| -- !
| 6 | VIEW | | Q1,00 | PCWP | |
|* 7 | HASH JOIN RIGHT OUTER | | Q1,00 | PCWP | |
| 8 | PARTITION RANGE SINGLE| | Q1,00 | PCWC | |
| 9 | TABLE ACCESS FULL | T3 | Q1,00 | PCWP | |
| 10 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
|* 11 | TABLE ACCESS FULL | T1 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------
Parallel Execution Details (DOP=4 , Servers Allocated=8)
============================================
| Name | Type | Server# | Write |
| | | | Bytes |
============================================
| PX Coordinator | QC | | . |
| p000 | Set 1 | 1 | 152KB |
| p001 | Set 1 | 2 | 152KB |
| p002 | Set 1 | 3 | 152KB |
| p003 | Set 1 | 4 | 152KB |
| p004 | Set 2 | 1 | . |
| p005 | Set 2 | 2 | . |
| p006 | Set 2 | 3 | . |
| p007 | Set 2 | 4 | . |
============================================
[2023-05-29]
파티션 테이블에 대한 PX SEND HYBRID (ROWID PKEY) 분배 방식의 성능 저하는 23.2 버전까지 해결되지 않고 있습니다.
관련 링크