기준 데이터를 저장한 비파티션 임시 테이블과 실제 데이터를 저장한 파티션 테이블을 병렬 조인하는 경우 임시 테이블의 기준 데이터에 따라 PPWJ(Partial-Partition Wise Join)의 성능이 저하될 수 있습니다. 이 글에서 분배 방식을 변경하여 PPWJ 성능 저하를 개선한 사례를 살펴보겠습니다.
테스트를 위해 아래와 같이 테이블을 생성하겠습니다. t1 테이블은 기준 데이터를 저장하는 비파티션 임시 테이블, t2 테이블은 실제 데이터를 저장하는 파티션 테이블입니다.
-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
CREATE GLOBAL TEMPORARY TABLE t1 (c1 NUMBER);
CREATE TABLE t2 (c1)
PARTITION BY RANGE (c1) (
PARTITION p1 VALUES LESS THAN (10001)
, PARTITION p2 VALUES LESS THAN (20001)
, PARTITION p3 VALUES LESS THAN (30001)
, PARTITION p4 VALUES LESS THAN (40001)
, PARTITION p5 VALUES LESS THAN (50001)
, PARTITION p6 VALUES LESS THAN (60001)
, PARTITION p7 VALUES LESS THAN (70001)
, PARTITION p8 VALUES LESS THAN (80001)
, PARTITION p9 VALUES LESS THAN (90001)
, PARTITION pm VALUES LESS THAN (MAXVALUE))
AS
SELECT ROWNUM FROM XMLTABLE ('1 to 100000');
-- 1-2
ALTER SESSION SET "_serial_direct_read"= ALWAYS;
ALTER SESSION SET "_small_table_threshold" = 0;
t1 테이블에 t2 테이블의 p1, p2 파티션에 해당하는 데이터를 입력하고 PPWJ 방식으로 조인을 수행하면 파티션 단위로 조인이 수행되므로 p002, p003 PX 서버가 조인을 수행하지 않습니다. 성능 저하 없이 PPWJ 방식을 사용하려면 임시 테이블에 DOP 배수만큼의 파티션 데이터가 입력해야 하며 파티션 또한 SKEW가 없어야 합니다.
-- 2-1
INSERT INTO t1 SELECT ROWNUM FROM XMLTABLE ('1 to 20000');
-- 2-2
SELECT /*+ PARALLEL(4) LEADING(A) USE_HASH(B) PQ_DISTRIBUTE(B PARTITION NONE) */
*
FROM t1 a
, t2 b
WHERE b.c1 = a.c1;
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | | |
| 1 | PX COORDINATOR | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | | | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | | | Q1,01 | PCWP | |
| 4 | PART JOIN FILTER CREATE | :BF0000 | | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | | | Q1,01 | PCWP | |
| 6 | PX SEND PARTITION (KEY) | :TQ10000 | | | Q1,00 | P->P | PART (KEY) |
| 7 | PX BLOCK ITERATOR | | | | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL | T1 | | | Q1,00 | PCWP | |
| 9 | PX PARTITION RANGE JOIN-FILTER| |:BF0000|:BF0000| Q1,01 | PCWC | |
| 10 | TABLE ACCESS FULL | T2 |:BF0000|:BF0000| Q1,01 | PCWP | |
---------------------------------------------------------------------------------------------------
Global Stats
===================================
| Elapsed | Buffer | Read | Read |
| Time(s) | Gets | Reqs | Bytes |
===================================
| 0.07 | 166 | 33 | 504KB |
===================================
Parallel Execution Details (DOP=4 , Servers Allocated=8)
============================================================
| Name | Type | Server# | Buffer | Read | Read |
| | | | Gets | Reqs | Bytes |
============================================================
| PX Coordinator | QC | | 35 | | . |
| p000 | Set 1 | 1 | 18 | 1 | 128KB |
| p001 | Set 1 | 2 | 18 | 1 | 128KB |
| p002 | Set 1 | 3 | 1 | | . | -- !
| p003 | Set 1 | 4 | 1 | | . | -- !
| p004 | Set 2 | 1 | 21 | 7 | 57344 |
| p005 | Set 2 | 2 | 24 | 8 | 65536 |
| p006 | Set 2 | 3 | 24 | 8 | 65536 |
| p007 | Set 2 | 4 | 24 | 8 | 65536 |
============================================================
SQL Plan Monitoring Details (Plan Hash Value=786901403)
======================================================================================================================
| Id | Operation | Name | Time | Start | Execs | Rows | Read | Read | Mem |
| | | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) |
======================================================================================================================
| 0 | SELECT STATEMENT | | 7 | +0 | 9 | 20000 | | | . |
| 1 | PX COORDINATOR | | 7 | +0 | 9 | 20000 | | | . |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 4 | +2 | 4 | 20000 | | | . |
| 3 | HASH JOIN | | 4 | +2 | 4 | 20000 | | | 5MB |
| 4 | PART JOIN FILTER CREATE | :BF0000 | 1 | +2 | 4 | 20000 | | | . |
| 5 | PX RECEIVE | | 1 | +2 | 4 | 20000 | | | . |
| 6 | PX SEND PARTITION (KEY) | :TQ10000 | 1 | +0 | 4 | 20000 | | | . |
| 7 | PX BLOCK ITERATOR | | 1 | +0 | 4 | 20000 | | | . |
| 8 | TABLE ACCESS FULL | T1 | 1 | +0 | 31 | 20000 | 31 | 248KB | . |
| 9 | PX PARTITION RANGE JOIN-FILTER | | 4 | +2 | 2 | 20000 | | | . |
| 10 | TABLE ACCESS FULL | T2 | 4 | +2 | 6 | 20000 | 2 | 256KB | . |
======================================================================================================================
아래 예제처럼 HASH 분배 방식을 사용하면 p004 ~ p007 PX 서버가 모두 조인을 수행하므로 t1 테이블에 입력되는 데이터와 파티션 SKEW 여부에 관계없이 일정한 성능을 보장할 수 있습니다. 다만 HASH 분배 방식으로 인해 조인 결과가 버퍼링(HASH JOIN BUFFERED)될 수 있다는 점을 주의해야 합니다.
-- 3
SELECT /*+ PARALLEL(4) LEADING(A) USE_HASH(B) PQ_DISTRIBUTE(B HASH HASH) */
*
FROM t1 a
, t2 b
WHERE b.c1 = a.c1;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------
| 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 | |
| 4 | PART JOIN FILTER CREATE | :BF0000 | | | Q1,02 | PCWP | |
| 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 ADAPTIVE| |:BF0000|:BF0000| Q1,01 | PCWC | |
| 12 | TABLE ACCESS FULL | T2 |:BF0000|:BF0000| Q1,01 | PCWP | |
-------------------------------------------------------------------------------------------------
Global Stats
===================================
| Elapsed | Buffer | Read | Read |
| Time(s) | Gets | Reqs | Bytes |
===================================
| 0.07 | 198 | 63 | 504KB |
===================================
Parallel Execution Details (DOP=4 , Servers Allocated=8)
============================================================
| Name | Type | Server# | Buffer | Read | Read |
| | | | Gets | Reqs | Bytes |
============================================================
| PX Coordinator | QC | | 41 | | . |
| p000 | Set 1 | 1 | | | . |
| p001 | Set 1 | 2 | | | . |
| p002 | Set 1 | 3 | | | . |
| p003 | Set 1 | 4 | | | . |
| p004 | Set 2 | 1 | 37 | 15 | 120KB |
| p005 | Set 2 | 2 | 40 | 16 | 128KB |
| p006 | Set 2 | 3 | 40 | 16 | 128KB |
| p007 | Set 2 | 4 | 40 | 16 | 128KB |
============================================================
SQL Plan Monitoring Details (Plan Hash Value=2220810352)
====================================================================================================================
| Id | Operation | Name | Time | Start | Execs | Rows | Read | Read | Mem |
| | | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) |
====================================================================================================================
| 0 | SELECT STATEMENT | | 6 | +0 | 9 | 20000 | | | . |
| 1 | PX COORDINATOR | | 6 | +0 | 9 | 20000 | | | . |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 5 | +1 | 4 | 20000 | | | . |
| 3 | HASH JOIN BUFFERED | | 5 | +1 | 4 | 20000 | | | 19MB | -- !
| 4 | PART JOIN FILTER CREATE | :BF0000 | 4 | +1 | 4 | 20000 | | | . |
| 5 | PX RECEIVE | | 4 | +1 | 4 | 20000 | | | . |
| 6 | PX SEND HASH | :TQ10000 | 1 | +0 | 4 | 20000 | | | . |
| 7 | PX BLOCK ITERATOR | | 1 | +0 | 4 | 20000 | | | . |
| 8 | TABLE ACCESS FULL | T1 | 1 | +0 | 31 | 20000 | 31 | 248KB | . |
| 9 | PX RECEIVE | | 4 | +1 | 4 | 20000 | | | . |
| 10 | PX SEND HASH | :TQ10001 | 1 | +0 | 4 | 20000 | | | . |
| 11 | PX BLOCK ITERATOR ADAPTIVE | | 1 | +0 | 4 | 20000 | | | . |
| 12 | TABLE ACCESS FULL | T2 | 1 | +0 | 32 | 20000 | 32 | 256KB | . |
====================================================================================================================