반응형
INSERT PARALLEL,APPEND 테스트
참고 : https://jack-of-all-trades.tistory.com/187
오라클 Parallel DML 처리 방법 및 주의 사항 - enable parallel dml
Oracle DB 에서 병렬처리 작업시 /*+ parallel(...) */ 힌트를 사용합니다. 조회 Query SQL 이나 Insert/Update/Delete/Merge 같은 DML SQL 이나 Parallel 힌트 주는 것은 똑같습니다. 근데, Parallel DML 의 경우..
jack-of-all-trades.tistory.com
참고 : https://m.blog.naver.com/inhim/220187506059
INSERT .. SELECT .. 문에서 PARALLEL Operation 정리
INSERT .. SELECT .. 문에서 PARALLEL Operation 정리 1. PDML Mode 와 Insert...
blog.naver.com
빠른 결론 : 대용량 INSERT 빠르게 수행하려면 PARALLEL + APPEND 옵션을 사용하면 된다.
시나리오
테이블은 약 6G이고, 37100209 건 존재함.
0.테스트 테이블 생성
create table ord_item_par as select * from ord_item where 1=2;
1.parallel dml + nologging 명령어 주고 /*+ append parallel(ord_item_par 8) */ 힌트
alter session enable parallel dml;
alter table ord_item_par nologging;
- ------------------------[Start Time: 2022/02/23 09:32:47]-------------------------
SQL> select * from v$pq_sysstat
where STATISTIC like '%Busy%';
STATISTIC VALUE CON_ID
---
Servers Busy 0 0
1 rows selected.
수행
INSERT INTO /*+ append parallel(ord_item_par 8) */ ord_item_par
select * from ord_item;
- ------------------------[Start Time: 2022/02/23 09:32:47]-------------------------
SQL> select * from v$pq_sysstat
where STATISTIC like '%Busy%';
STATISTIC VALUE CON_ID
---
Servers Busy 0 0
1 rows selected.
결과 : parallel이 먹히지 않음.
2. parallel(8) 만 사용
alter session enable parallel dml;
insert /*+ parallel(8) */ into ord_item_par
select * from ord_item;
- ------------------------[Start Time: 2022/02/23 09:35:26]-------------------------
SQL> select * from v$pq_sysstat
where STATISTIC like '%Busy%';
STATISTIC VALUE CON_ID
---
Servers Busy 8 0
1 rows selected.
결과 : parallel이 먹힘
3.enable_parallel_dml parallel(8) 사용
insert /*+ enable_parallel_dml parallel(8) */ into ord_item_par
select * from ord_item;
- ------------------------[Start Time: 2022/02/23 09:50:59]-------------------------
SQL> select * from v$pq_sysstat
where STATISTIC like '%Busy%';
STATISTIC VALUE CON_ID
---
Servers Busy 8 0
1 rows selected.
세션 확인
select username,status,schemaname,program,event from v$session
where username='DEV'
DEV ACTIVE DEV TOrangeV7.1U.exe SQL*Net message to client
DEV ACTIVE DEV oracle@dbarac1 (P002) log buffer space
DEV ACTIVE DEV oracle@dbarac1 (P003) log buffer space
DEV ACTIVE DEV oracle@dbarac1 (P005) log buffer space
DEV ACTIVE DEV oracle@dbarac1 (P006) log buffer space
DEV ACTIVE DEV oracle@dbarac1 (P007) log buffer space
DEV ACTIVE DEV OrangeMain.exe PX Deq: Execute Reply
DEV INACTIVE DEV TOrangeV7.1U.exe SQL*Net message from client
DEV ACTIVE DEV oracle@dbarac1 (P004) log buffer space
DEV ACTIVE DEV oracle@dbarac1 (P000) log buffer space
DEV ACTIVE DEV oracle@dbarac1 (P001) log buffer space
결과 : parallel 가능
테스트 결과 parallel(숫자) 형식으로 사용해야 parallel이 동작한다.
append 옵션을 이용하여 실제로 redo 발생량이 주는 것을 확인해본다.
4-0. trace on
set autot traceonly statistic
4.append 안썼을 때 redo 발생량
insert into ord_item_par
select * from ord_item;
Statistics
----------------------------------------------------------
361 recursive calls
6312141 db block gets
1465644 consistent gets
824822 physical reads
6698397556 redo size
856 bytes sent via SQL*Net to client
968 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
37100209 rows processed
redo size 6698397556 사용
5.append만 썻을때
insert /*+ append */ into ord_item_par
select * from ord_item;
Statistics
----------------------------------------------------------
774 recursive calls
834941 db block gets
826017 consistent gets
824828 physical reads
6791018008 redo size
850 bytes sent via SQL*Net to client
982 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
37100209 rows processed
redo size 6791018008 사용
6.nologging + append 사용
SQL> alter table ord_item_par nologging;
Table altered.
SQL> insert /*+ append */ into ord_item_par
select * from ord_item;
Statistics
----------------------------------------------------------
766 recursive calls
834940 db block gets
825958 consistent gets
824822 physical reads
1159692 redo size
854 bytes sent via SQL*Net to client
982 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
37100209 rows processed
SQL> alter table ord_item_par logging;
Table altered.
redo size 1159692 사용
결론 : INSERT 를 빠르게 하고 싶다면 append + parallel 옵션을 주자
alter session enable parallel dml;
alter table ord_item_par nologging;
insert /*+ append parallel(8) */ into ord_item_par
select * from ord_item;
SQL> select * from v$pq_sysstat
where STATISTIC like '%Busy%';
STATISTIC VALUE CON_ID
------------------------------------------------------------------------------------------------------------------------ ---------- ------------
Servers Busy 8 0
1 rows selected.
Statistics
----------------------------------------------------------
116 recursive calls
11 db block gets
202 consistent gets
0 physical reads
344 redo size
853 bytes sent via SQL*Net to client
994 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
37100209 rows processed반응형
'Oracle > 이관' 카테고리의 다른 글
| [DATAPUMP] SCHEMAS 옵션 사용시 public synonym도 이관여부? (0) | 2023.02.15 |
|---|---|
| SQL Loader의 direct path load와 Index unusable (0) | 2022.09.14 |
| [EXPDP] QUERY 옵션 사용하여 원하는 데이터만 이관 (0) | 2022.01.20 |
| IMPDP remap_table 옵션 사용하여 이관시 인덱스에 대한 테스트 (0) | 2022.01.11 |
| EXP/IMP 데이터 이관 테스트 (case 캐릭터셋이 서로 다른 DB) (0) | 2021.11.26 |
댓글