INSERT PARALLEL,APPEND 테스트

출처 : https://bae9086.tistory.com/364

 

빠른 결론 : 대용량 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