MULTI-TABLE INSERT 문은 APPEND 힌트와 PARALLEL 힌트의 사용에 따라 동작 방식이 달라질 수 있습니다.
테스트를 위해 아래와 같이 테이블을 생성하고, ALTER SESSION 문으로 병렬 DML을 활성화하겠습니다.
-- 1-1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
DROP TABLE t3 PURGE;
CREATE TABLE t1 AS SELECT ROWNUM AS c1 FROM XMLTABLE ('1 to 10000');
CREATE TABLE t2 (c1 NUMBER);
CREATE TABLE t3 (c1 NUMBER);
-- 1-2
ALTER SESSION ENABLE PARALLEL DML;
아래 INSERT 문은 t1 테이블을 읽어 t2, t3 테이블에 데이터를 입력합니다. 참고로 12.2 이하 버전은 INTO 오퍼레이션이 INSERT 오퍼레이션 상단에 표시됩니다.
-- 2
INSERT /*+ MONITOR */
ALL
INTO t2
INTO t3
SELECT *
FROM t1;
SQL Plan Monitoring Details (Plan Hash Value=1248537433)
============================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes |
============================================================================================================
| 0 | INSERT STATEMENT | | | | 1 | +0 | 1 | 1 | | |
| 1 | MULTI-TABLE INSERT | | | | 1 | +0 | 1 | 1 | | |
| 2 | TABLE ACCESS FULL | T1 | 10000 | 7 | 1 | +0 | 1 | 10000 | 3 | 128KB |
| 3 | INTO | T2 | | | | | 10000 | | | |
| 4 | INTO | T3 | | | | | 10000 | | | |
============================================================================================================
아래 INSERT 문은 APPEND 힌트를 사용합니다. INSERT 문이 Direct Load 방식으로 동작합니다.
-- 3
INSERT /*+ MONITOR APPEND */
ALL
INTO t2
INTO t3
SELECT *
FROM t1;
SQL Plan Monitoring Details (Plan Hash Value=1248537433)
=============================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Write | Write |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes |
=============================================================================================================
| 0 | INSERT STATEMENT | | | | 1 | +0 | 1 | 1 | | |
| 1 | MULTI-TABLE INSERT | | | | 1 | +0 | 1 | 1 | 2 | 16384 |
| 2 | TABLE ACCESS FULL | T1 | 10000 | 7 | 1 | +0 | 1 | 10000 | | |
| 3 | DIRECT LOAD INTO | T2 | | | 1 | +0 | 10000 | 1 | 2 | 120KB |
| 4 | DIRECT LOAD INTO | T3 | | | 1 | +0 | 10000 | 1 | 2 | 120KB |
=============================================================================================================
아래 INSERT 문은 SELECT 절에 PARALLEL 힌트를 사용합니다. p000, p001 PX 서버가 t1 테이블을 조회하고, QC가 데이터를 입력합니다.
-- 4
INSERT /*+ MONITOR APPEND */
ALL
INTO t2
INTO t3
SELECT /*+ PARALLEL(T1 2) */
*
FROM t1;
Parallel Execution Details (DOP=2 , Servers Allocated=2)
=============================================================================================
| Name | Type | Server# | Elapsed | IO | Other | Buffer | Write | Write |
| | | | Time(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes |
=============================================================================================
| PX Coordinator | QC | | 0.11 | 0.00 | 0.11 | 168 | 6 | 256KB | -- !
| p000 | Set 1 | 1 | 0.01 | | 0.01 | 24 | | . |
| p001 | Set 1 | 2 | 0.01 | | 0.01 | 24 | | . |
=============================================================================================
SQL Plan Monitoring Details (Plan Hash Value=872670919)
====================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Write | Write |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes |
====================================================================================================================
| 0 | INSERT STATEMENT | | | | 1 | +0 | 1 | 1 | | |
| 1 | MULTI-TABLE INSERT | | | | 1 | +0 | 1 | 1 | 2 | 32768 |
| 2 | PX COORDINATOR | | | | 1 | +0 | 3 | 10000 | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 10000 | 4 | 1 | +0 | 2 | 10000 | | |
| 4 | PX BLOCK ITERATOR | | 10000 | 4 | 1 | +0 | 2 | 10000 | | |
| 5 | TABLE ACCESS FULL | T1 | 10000 | 4 | 1 | +0 | 16 | 10000 | | |
| 6 | DIRECT LOAD INTO | T2 | | | 1 | +0 | 10000 | 1 | 2 | 112KB |
| 7 | DIRECT LOAD INTO | T3 | | | 1 | +0 | 10000 | 1 | 2 | 112KB |
====================================================================================================================
아래 INSERT 문은 INSERT 절에 PARALLEL 힌트를 사용합니다. p002 PX 서버가 t1 테이블을 조회하고, p000, p001 병렬 서버가 데이터를 입력합니다. p003 PX 서버는 아무런 작업을 수행하지 않습니다. 참고로 PX SELECTOR 오퍼레이션은 12.1 버전에 추가되었습니다. 12.2 이전 버전은 2개의 PX 서버가 할당되고 QC가 t1 테이블을 조회합니다.
-- 5
INSERT /*+ MONITOR APPEND PARALLEL(T2 2) */
ALL
INTO t2
INTO t3
SELECT *
FROM t1;
Parallel Execution Details (DOP=2 , Servers Allocated=4)
=====================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Concurrency | Other | Buffer | Write | Write |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes |
=====================================================================================================================
| PX Coordinator | QC | | 0.03 | 0.01 | | | 0.01 | 20 | | . |
| p000 | Set 1 | 1 | 0.04 | 0.02 | 0.02 | 0.00 | | 138 | 2 | 128KB |
| p001 | Set 1 | 2 | 0.03 | | 0.01 | 0.02 | 0.01 | 52 | 2 | 128KB |
| p002 | Set 2 | 1 | 0.00 | | | | 0.00 | 18 | | . | -- !
| p003 | Set 2 | 2 | 0.00 | | | | 0.00 | | | . |
=====================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=4014791189)
====================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Write | Write |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes |
====================================================================================================================================
| 0 | INSERT STATEMENT | | | | 1 | +0 | 5 | 6 | | |
| 1 | PX COORDINATOR | | | | 1 | +0 | 5 | 6 | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | | | 1 | +0 | 2 | 6 | | |
| 3 | MULTI-TABLE INSERT | | | | 1 | +0 | 2 | 2 | 4 | 256KB |
| 4 | PX RECEIVE | | | | 1 | +0 | 2 | 10000 | | |
| 5 | PX SEND ROUND-ROBIN | :TQ10000 | | | 1 | +0 | 2 | 10000 | | |
| 6 | PX SELECTOR | | | | 1 | +0 | 2 | 10000 | | | -- !
| 7 | TABLE ACCESS FULL | T1 | 10000 | 7 | 1 | +0 | 1 | 10000 | | |
| 8 | DIRECT LOAD INTO (HYBRID TSM/HWMB) | T2 | | | 1 | +0 | 10000 | 2 | | |
| 9 | DIRECT LOAD INTO (HYBRID TSM/HWMB) | T3 | | | 1 | +0 | 10000 | 2 | | |
====================================================================================================================================
아래 쿼리는 INSERT 절, SELECT 절 모두에 PARALLEL 힌트를 사용합니다. 효율적으로 p002, p003 PX 서버가 t1 테이블을 조회하고, p000, p001 PX 서버가 데이터를 입력합니다.
-- 6
INSERT /*+ MONITOR APPEND PARALLEL(T2 2) */
ALL
INTO t2
INTO t3
SELECT /*+ PARALLEL(T1 2) */
*
FROM t1;
Parallel Execution Details (DOP=2 , Servers Allocated=4)
===========================================================================================================
| Name | Type | Server# | Elapsed | IO | Concurrency | Other | Buffer | Write | Write |
| | | | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes |
===========================================================================================================
| PX Coordinator | QC | | 0.02 | | | 0.02 | 25 | | . |
| p000 | Set 1 | 1 | 0.01 | 0.00 | 0.00 | 0.01 | 113 | 2 | 128KB |
| p001 | Set 1 | 2 | 0.01 | 0.00 | 0.00 | 0.01 | 96 | 2 | 128KB |
| p002 | Set 2 | 1 | 0.00 | | | 0.00 | 21 | | . | -- !
| p003 | Set 2 | 2 | 0.00 | | | 0.00 | 27 | | . | -- !
===========================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=581855196)
====================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Write | Write |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes |
====================================================================================================================================
| 0 | INSERT STATEMENT | | | | 1 | +0 | 5 | 6 | | |
| 1 | PX COORDINATOR | | | | 1 | +0 | 5 | 6 | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | | | 1 | +0 | 2 | 6 | | |
| 3 | MULTI-TABLE INSERT | | | | 1 | +0 | 2 | 2 | 4 | 256KB |
| 4 | PX RECEIVE | | | | 1 | +0 | 2 | 10000 | | |
| 5 | PX SEND ROUND-ROBIN | :TQ10000 | | | 1 | +0 | 2 | 10000 | | |
| 6 | PX BLOCK ITERATOR | | 10000 | 4 | 1 | +0 | 2 | 10000 | | | -- !
| 7 | TABLE ACCESS FULL | T1 | 10000 | 4 | 1 | +0 | 16 | 10000 | | |
| 8 | DIRECT LOAD INTO (HYBRID TSM/HWMB) | T2 | | | 1 | +0 | 10000 | 2 | | |
| 9 | DIRECT LOAD INTO (HYBRID TSM/HWMB) | T3 | | | 1 | +0 | 10000 | 2 | | |
====================================================================================================================================