DBMS_PARALLEL_EXECUTE 패키지(병렬로 DML을 진행하는 방법 )

 

DBMS_PARALLEL_EXECUTE?

대용량 테이블에 대한 DML을 하나의 단위(Chunk)로 쪼개어 병렬로 처리할 수 있도록 지원하는 기능입니다.

Oracle 11gR2부터 도입되었으며, parallel옵션을 추가한 PDML과의 차이점으로는 하나의 트랜잭션이 아니라 각각의 DML이 각각의 트랜잭션으로 수행되게 되며 락(Lock)이나 Undo관련 문제(ORA-01555)경합을 최소화 할수 있습니다.

 

핵심 특징?

작업의 세분화 (Chunking): 수천만 건의 데이터를 RowID, 숫자 컬럼, 또는 사용자 정의 SQL을 기준으로 잘게 분할합니다.
독립적인 커밋 (Commit): 각 청크 단위로 트랜잭션이 커밋되므로 Undo 테이블스페이스의 부담이 대폭 줄어듭니다.
재시작 기능 (Resumability): 작업 중간에 오류가 발생하거나 중단되더라도, 실패한 청크부터 다시 시작할 수 있습니다.
유연한 병렬도 제어: 백그라운드 Job을 통해 실행되며, 시스템 리소스 상황에 맞게 병렬 프로세스(Parallel Degree) 개수를 지정할 수 있습니다.


주요 프로시저 및 옵션 설명?
대용량 데이터 처리는 보통 작업 생성 ➔ 청크 분할 ➔ 작업 실행 ➔ 상태 확인 ➔ 작업 삭제의 라이프사이클을 가집니다.

1. CREATE_TASK
병렬 처리 작업을 위한 논리적인 작업(Task)를 생성합니다.

task_name: 생성할 작업의 이름 (고유해야 함)

2. 청크 생성 옵션 (CREATE_CHUNKS_BY_*)
테이블을 어떤 기준으로 분할할지 결정합니다. 데이터의 분포도에 따라 적절한 방식을 선택해야 합니다.

CREATE_CHUNKS_BY_ROWID: 가장 일반적으로 사용되며, 오라클의 물리적 주소인 RowID를 기준으로 블록을 나눕니다. chunk_size 파라미터로 청크당 행(Row)의 수를 대략적으로 지정합니다.

CREATE_CHUNKS_BY_NUMBER_COL: 숫자형 Primary Key나 인덱스 컬럼을 기준으로 시작값과 끝값을 계산하여 나눕니다.

CREATE_CHUNKS_BY_SQL: 사용자가 직접 작성한 SELECT 쿼리의 결과(start_id, end_id 형태)를 바탕으로 청크를 구성합니다. 데이터 쏠림이 심할 때 유용합니다.

3. RUN_TASK
분할된 청크들을 대상으로 실제 실행할 PL/SQL 익명 블록을 백그라운드 Job으로 병렬 실행합니다.

sql_stmt: 각 청크에 대해 실행할 쿼리 (반드시 :start_id와 :end_id 바인드 변수를 포함해야 함).

language_flag: DBMS_SQL.NATIVE 지정.

parallel_level: 동시에 실행할 Job의 개수 (병렬도).

4. DROP_TASK
모든 작업이 정상적으로 완료되었거나 취소할 때 관련된 메타데이터를 삭제합니다.


해당 패키지를 이용한 테스트

 

시나리오. 

테스트 테이블을 생성하여

각 청크를 생성하는 옵션별 명령어를 통한 문으로 수행 테스트

 

###TASK 삭제 명령어

exec DBMS_PARALLEL_EXECUTE.DROP_TASK('TASK명');

 

### CREATE_CHUNKS_BY_SQL

DECLARE
l_task_name  varchar2(100);
l_chunks_sql varchar2(4000);
BEGIN

l_task_name := 'TEST_NONPART';
DBMS_PARALLEL_EXECUTE.CREATE_TASK(l_task_name);

l_chunk_sql := '
WITH src_data AS (
SELECT NVL(MIN(id),1) as min_val,
   NVL(MAX(id),1) as max_val
FROM YSBAE.TEST_NONPART_SOURCE),
chunk_info AS (
SELECT min_val,
   max_val,
   4 as chunk_cnt, /* 원하는 chunk 개수 */
   CEIL((max_val - min_val +1) / 4) AS chunk_size
FROM src_data
)
SELECT min_val + (level -1) * chunk_size as start_id,
CASE WHEN level = chunk_cnt THEN max_val
ELSE min_val + (level * chunk_size) -1 END as end_id
FROM chunk_info
CONNECT BY level <= chunk_cnt';

DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL(
task_name => l_task_name,
sql_statement => l_chunks_sql,
by_rowid => FALSE); ---TRUE시 rowid를 where절에 사용, FALSE시 number column을 사용

DBMS_PARALLEL_EXECUTE.RUN_TASK(
task_name => l_task_name,
sql_stmt => 'UPDATE YSBAE.TEST_NONPART_TARGET_CTAS_3 SET VAL2 = 'YSBAE TEST UPDATE '||id  WHERE id BETWEEN :start_id AND :end_id',
language_flag => DBMS_SQL.NATIVE,
parallel_level => 4
);
END;
/

 

### CREATE_CHUNKS_BY_ROWID

DECLARE
l_task_name  varchar2(100);

BEGIN

l_task_name := 'TEST_BY_ROWID';
DBMS_PARALLEL_EXECUTE.CREATE_TASK(l_task_name);


DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID(
task_name => l_task_name,
table_owner => 'YSBAE',
table_name => 'TEST_NONPART_TARGET_CTAS_3',
by_row => TRUE, ---TRUE시 row건수 기준, FALASE : 블록개수기준
chunk_size => 1000000
);
DBMS_PARALLEL_EXECUTE.RUN_TASK(
task_name => l_task_name,
sql_stmt => 'UPDATE YSBAE.TEST_NONPART_TARGET_CTAS_3 SET VAL2 = 'YSBAE TEST UPDATE '||id  WHERE rowid BETWEEN :start_id AND :end_id',
language_flag => DBMS_SQL.NATIVE,
parallel_level => 4
);
END;
/

 

### CREATE_CHUNKS_BY_NUMBER_COL

DECLARE
l_task_name  varchar2(100);

BEGIN

l_task_name := 'TEST_BY_NUMBER_COL';
DBMS_PARALLEL_EXECUTE.CREATE_TASK(l_task_name);


DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID(
task_name => l_task_name,
table_owner => 'YSBAE',
table_name => 'TEST_NONPART_TARGET_CTAS_3',
table_column => 'ID' ---반드시 number column
chunk_size => 250000
);
DBMS_PARALLEL_EXECUTE.RUN_TASK(
task_name => l_task_name,
sql_stmt => 'UPDATE YSBAE.TEST_NONPART_TARGET_CTAS_3 SET VAL2 = 'YSBAE TEST UPDATE '||id  WHERE rowid BETWEEN :start_id AND :end_id',
language_flag => DBMS_SQL.NATIVE,
parallel_level => 4
);
END;
/

 

 

각각 수행하면 백그라운드로 스케줄러로 병렬도에 맞게 갯수대로 생성되어 수행되게 됩니다.

user_parallel_execute_chunks 딕셔너리뷰에서 진행상황 확인가능합니다.

 

시나리오2. 파티션테이블과 일반테이블 해당패키지사용시 처리되는 부분의 차이가 있는지?

 

해당 테스트는 굼금하신분들은 접은글을 펼처서 확인해보시길 바랍니다.

결론은 테이블에 따라 달라지는것은 없습니다.

 

###일반 테이블
DECLARE
l_task_name  varchar2(100);
l_chunks_sql varchar2(4000);
BEGIN

l_task_name := 'TEST_NONPART';
DBMS_PARALLEL_EXECUTE.CREATE_TASK(l_task_name);

l_chunk_sql := '
WITH src_data AS (
SELECT NVL(MIN(id),1) as min_val,
   NVL(MAX(id),1) as max_val
FROM YSBAE.TEST_NONPART_SOURCE),
chunk_info AS (
SELECT min_val,
   max_val,
   4 as chunk_cnt, /* 원하는 chunk 개수 */
   CEIL((max_val - min_val +1) / 4) AS chunk_size
FROM src_data
)
SELECT min_val + (level -1) * chunk_size as start_id,
CASE WHEN level = chunk_cnt THEN max_val
ELSE min_val + (level * chunk_size) -1 END as end_id
FROM chunk_info
CONNECT BY level <= chunk_cnt';

DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL(

task_name => l_task_name,

sql_statement => l_chunks_sql,

by_rowid => FALSE); ---TRUE시 rowid를 where절에 사용, FALSE시 number column을 사용

DBMS_PARALLEL_EXECUTE.RUN_TASK(
task_name => l_task_name,
sql_stmt => 'INSERT /*+ APPEND */ INTO YSBAE.TEST_NONPART_TARGET_CTAS_2 SELECT * FROM YSBAE.TEST_NONPART_SOURCE@LINK_YSBAE WHERE ID BETWEEN :start_id AND :end_id',
language_flag => DBMS_SQL.NATIVE,
parallel_level => 4
);
END;
/

#########################################################################
select * From user_parallel_execute_chunks;

CHUNK_ID TASK_NAME STATUS START_ROWID END_ROWID START_ID END_ID JOB_NAME START_TS END_TS ERROR_CODE ERROR_MESSAGE
--------------------------------------------------------------------------------------------------------------------------------------------------------
4 TEST_NONPART PROCESSED 1 250000 TASK$_464432_1 2026/08/27 16:33:44.410962  2026/08/27 16:33:44.795466
5 TEST_NONPART PROCESSED 250001 500000 TASK$_464432_1 2026/08/27 16:33:44.799739  2026/08/27 16:33:45.172564
6 TEST_NONPART PROCESSED 500001 750000 TASK$_464432_3 2026/08/27 16:33:45.079343  2026/08/27 16:33:45.563755
7 TEST_NONPART PROCESSED 750001 1000000 TASK$_464432_1 2026/08/27 16:33:45.175910  2026/08/27 16:33:45.932508


INST_ID  SID     SERIAL#  PROGRAM                                  STATE               EVENT                                         STATUS  SECONDS_IN_WAIT
-------  ------  -------  ---------------------------------------- ------------------- --------------------------------------------- ------- ---------------
    1     1106    17514  sqlplus@dbarac1 (TNS V1-V3)                WAITING             PL/SQL lock timer  ACTIVE                0


  
#########################################################################


##파티션테이블
DECLARE
l_task_name  varchar2(100);
l_chunks_sql varchar2(4000);
BEGIN
BEGIN
DBMS_PARALLEL_EXECUTE.DROP_TASK(l_task_name);
EXCEPTION WHEN OTHERS THEN NULL;
END;
 ---1.파티션테이블 테스트용 task
l_task_name := 'TEST_PART';
DBMS_PARALLEL_EXECUTE.CREATE_TASK(l_task_name);


l_chunk_sql := '
WITH src_data AS (
SELECT NVL(MIN(id),1) as min_val,
   NVL(MAX(id),1) as max_val
FROM YSBAE.TEST_NONPART_SOURCE),
chunk_info AS (
SELECT min_val,
   max_val,
   4 as chunk_cnt, /* 원하는 chunk 개수 */
   CEIL((max_val - min_val +1) / 4) AS chunk_size
FROM src_data
)
SELECT min_val + (level -1) * chunk_size as start_id,
CASE WHEN level = chunk_cnt THEN max_val
ELSE min_val + (level * chunk_size) -1 END as end_id
FROM chunk_info
CONNECT BY level <= chunk_cnt';

DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL(l_task_name,l_chunks_sql,FALSE);

DBMS_PARALLEL_EXECUTE.RUN_TASK(
task_name => l_task_name,
sql_stmt => 'INSERT /*+ APPEND */ INTO YSBAE.TEST_PART_TARGET_CTAS_2 SELECT * FROM YSBAE.TEST_NONPART_SOURCE@LINK_YSBAE WHERE ID BETWEEN :start_id AND :end_id',
language_flag => DBMS_SQL.NATIVE,
parallel_level => 4
);
END;
/

#########################################################################


select * From user_parallel_execute_chunks;

CHUNK_ID TASK_NAME STATUS START_ROWID END_ROWID START_ID END_ID JOB_NAME START_TS END_TS ERROR_CODE ERROR_MESSAGE
--------------------------------------------------------------------------------------------------------------------------------------------------------
18 TEST_PART PROCESSED 1 250000 TASK$_464438_1 2026/08/27 16:44:03.044976  2026/08/27 16:44:03.772818
19 TEST_PART PROCESSED 250001 500000 TASK$_464438_1 2026/08/27 16:44:03.145348  2026/08/27 16:44:04.139756
20 TEST_PART PROCESSED 500001 750000 TASK$_464438_3 2026/08/27 16:44:03.793058  2026/08/27 16:44:04.497978
21 TEST_PART PROCESSED 750001 1000000 TASK$_464438_1 2026/08/27 16:44:04.144534  2026/08/27 16:44:04.845840


INST_ID  SID     SERIAL#  PROGRAM                                  STATE               EVENT                                         STATUS  SECONDS_IN_WAIT
-------  ------  -------  ---------------------------------------- ------------------- --------------------------------------------- ------- ---------------
    1     1106    17514  sqlplus@dbarac1 (TNS V1-V3)                WAITING             PL/SQL lock timer  ACTIVE                0

패키지 수행시 내부적으로 dba_scheduler_jobs에 각각의 job_name으로 스케줄을 생성하여 1회성으로 수행하고 종료시 삭제됩니다.

출처 : https://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/DBMS_PARALLEL_EXECUTE.html