오라클 19c 일반 insert, insert all, union all insert 성능 테스트(컬럼 10개)

OS 환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

방법 : 오라클 19c 일반 insert, insert all, union all insert 성능 테스트2

컬럼 10개인 테이블에 대해 일반 insert, insert all, union all insert, bulk insert 방식을 사용해서 데이터 5000건을 insert 할때
어떤 방식으로 insert 하는게 빠른지 테스트해봄(기존에는 100만건으로 하려했지만 너무 느려서 갯수를 줄임)

 

 

구문 설명
일반 insert 구문 : 일반 insert는 가장 기본적인 형태로, 하나의 행을 삽입하거나 itas(insert into table select~)여러 행을 한번에 삽입할 수 있음
사용 예시 :

1 2	insert into table_name (column1, column2, column3)  values (value1, value2, value3);

 

 

insert all 구문 : 하나의 트랜잭션으로 여러 행을 동시에 삽입할 수 있는 구문으로, 여러 테이블에 걸쳐 삽입도 가능함
사용 예시 :

1 2 3 4 5

insert all   into table1 (column1, column2) values (value1, value2)   into table2 (column1, column2) values (value3, value4)   into table3 (column1, column2) values (value5, value6) select * from dual;

 

 

union all insert 구문 : 여러 개의 select 문을 사용하여 한번에 여러 행을 삽입함(위에서 설명한 itas방식)
사용 예시 :

1 2 3 4 5 6

insert into intest5 (col1) select 1 from dual union all select 2 from dual union all select 3 from dual union all ... select 5000 from dual;

 

 

테스트
1. 일반 insert 테스트(일반 sql)
2. 일반 insert 테스트(pl/sql)
3. insert all 테스트(일반 sql)
4. union all insert 테스트(일반 sql)
5. bulk insert 테스트
(다른 테스트는 5천건으로 테스트했지만 bulk는 빨라서 100배인 50만건을 insert 함)

 

 

테스트
샘플 테이블 생성

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SQL>  drop table intest1 purge; drop table intest2 purge; drop table intest3 purge; drop table intest4 purge; drop table intest5 purge; CREATE TABLE intest1  (col1 NUMBER, col2 NUMBER, col3 NUMBER, col4 NUMBER, col5 NUMBER, col6 NUMBER, col7 NUMBER, col8 NUMBER, col9 NUMBER, col10 NUMBER); CREATE TABLE intest2 (col1 NUMBER, col2 NUMBER, col3 NUMBER, col4 NUMBER, col5 NUMBER, col6 NUMBER, col7 NUMBER, col8 NUMBER, col9 NUMBER, col10 NUMBER); CREATE TABLE intest3  (col1 NUMBER, col2 NUMBER, col3 NUMBER, col4 NUMBER, col5 NUMBER, col6 NUMBER, col7 NUMBER, col8 NUMBER, col9 NUMBER, col10 NUMBER); CREATE TABLE intest4  (col1 NUMBER, col2 NUMBER, col3 NUMBER, col4 NUMBER, col5 NUMBER, col6 NUMBER, col7 NUMBER, col8 NUMBER, col9 NUMBER, col10 NUMBER); CREATE TABLE intest5  (col1 NUMBER, col2 NUMBER, col3 NUMBER, col4 NUMBER, col5 NUMBER, col6 NUMBER, col7 NUMBER, col8 NUMBER, col9 NUMBER, col10 NUMBER);   Table created.

 

 

1. 일반 insert 테스트(일반 sql)
insert 쿼리 생성

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SQL>  spool in10_1.sql set lines 200 pages 10000 select 'insert into intest1 (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10) values ('||level||','||level||','||level||','||level||','||level||','||level||','||level||','||level||','||level||','||level||');' insql  from dual  connect by level <= 5000;   INSQL ---------------------------------------------------------------------------- insert into intest1 (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10) values (1,1,1,1,1,1,1,1,1,1); .. insert into intest1 (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10) values (5000,5000,5000,5000,5000,5000,5000,5000,5000,5000);   5000 rows selected.

 

 

vi로 SQL 이외 문자 삭제 및 마지막에 commit; 추가

 

 

10046 트레이스 실행

1 2 3

SQL>  alter session set tracefile_identifier='INTEST1'; alter session set events '10046 trace name context forever, level 12';

 

 

insert 실행

1	SQL> @in10_1.sql

 

 

10046 트레이스 해제

1 2 3

SQL> alter session set events '10046 trace name context off';   Session altered.

 

 

트레이스 경로 확인

1 2 3 4 5 6 7 8 9

SQL>  set lines 200 pages 1000 col value for a100 select value from v$diag_info where name = 'Default Trace File';   VALUE ---------------------------------------------------------------------------------------------------- /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_17471_INTEST1.trc

 

 

tkprof 로 변환

1 2	$ cd /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/ $ tkprof oracle19_ora_17471_INTEST1.trc oracle19_ora_17471_INTEST1.txt sys=no

 

 

trace 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

$ vi oracle19_ora_17471_INTEST1.txt ********************************************************************************   OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS   call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse     5002      0.90       1.28          0          0          0           0 Execute   5002      0.09       0.32        130        212      11401        5000 Fetch        0      0.00       0.00          0          0          0           0 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total    10004      0.99       1.61        130        212      11401        5000   Misses in library cache during parse: 5000 Misses in library cache during execute: 1

1.61초 소요됨

 

 

2. 일반 insert 테스트(pl/sql)
세션 재접속 후 10046 트레이스 실행

1 2 3

SQL>  alter session set tracefile_identifier='INTEST2'; alter session set events '10046 trace name context forever, level 12';

 

 

insert 실행

1 2 3 4 5 6 7 8 9 10

SQL> begin     for i in 1..5000 loop         insert into intest2 (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10)          values (i, i, i, i, i, i, i, i, i, i);     end loop; end; /   PL/SQL procedure successfully completed.

 

 

10046 트레이스 해제

1 2 3

SQL> alter session set events '10046 trace name context off';   Session altered.

 

 

트레이스 경로 확인

1 2 3 4 5 6 7 8 9

SQL>  set lines 200 pages 1000 col value for a100 select value from v$diag_info where name = 'Default Trace File';   VALUE ---------------------------------------------------------------------------------------------------- /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_16117_INTEST2.trc

 

 

tkprof 로 변환

1 2	$ cd /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/ $ tkprof oracle19_ora_16117_INTEST2.trc oracle19_ora_16117_INTEST2.txt sys=no

 

 

trace 확인

1 2 3 4 5 6 7 8 9 10 11

$ vi oracle19_ora_16117_INTEST2.txt call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          0          0          0           0 Execute   5000      0.53       0.53          0        212       5520        5000 Fetch        0      0.00       0.00          0          0          0           0 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total     5001      0.53       0.53          0        212       5520        5000   Misses in library cache during parse: 1 Misses in library cache during execute: 1

0.53초 소요됨

 

 

3. insert all 테스트(일반 sql)
세션 재접속 후 진행
insert 쿼리 생성

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

SQL>  spool in10_3.sql set lines 200 pages 10000 select 'insert all' from dual  union all select 'into intest3 (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10) values ('||level||','||level||','||level||','||level||','||level||','||level||','||level||','||level||','||level||','||level||')' insql  from dual  connect by level <= 5000 union all select 'select * from dual;' from dual;   INSERTALL ---------------------------------------------------------------------------- insert all into intest3 (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10) values (1,1,1,1,1,1,1,1,1,1) .. into intest3 (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10) values (5000,5000,5000,5000,5000,5000,5000,5000,5000,5000) select * from dual;   5002 rows selected.

 

 

vi로 SQL 이외 문자 삭제 및 마지막에 commit; 추가

 

 

10046 트레이스 실행

1 2 3

SQL>  alter session set tracefile_identifier='INTEST3'; alter session set events '10046 trace name context forever, level 12';

 

 

insert 실행

1	SQL> @in10_3.sql

 

 

10046 트레이스 해제

1 2 3

SQL> alter session set events '10046 trace name context off';   Session altered.

 

 

트레이스 경로 확인

1 2 3 4 5 6 7 8 9

SQL>  set lines 200 pages 1000 col value for a100 select value from v$diag_info where name = 'Default Trace File';   VALUE ---------------------------------------------------------------------------------------------------- /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_20672_INTEST3.trc

 

 

tkprof 로 변환

1 2	$ cd /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/ $ tkprof oracle19_ora_20672_INTEST3.trc oracle19_ora_20672_INTEST3.txt sys=no

 

 

trace 확인

1 2 3 4 5 6 7 8 9 10 11

$ vi oracle19_ora_20672_INTEST3.txt call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1     41.28      41.42          0          0          0           0 Execute      1     41.90      42.06          0        212      10569        5000 Fetch        0      0.00       0.00          0          0          0           0 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        2     83.18      83.48          0        212      10569        5000   Misses in library cache during parse: 1 Misses in library cache during execute: 1

83.48초 소요됨

 

 

4. union all insert 테스트(일반 sql)
세션 재접속 후 진행
insert 쿼리 생성

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

SQL> spool in10_4.sql set lines 200 pages 10000 select 'insert into intest4 (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10)' insql from dual  union all select 'select '||level||','||level||','||level||','||level||','||level||','||level||','||level||','||level||','||level||','||level||' from dual union all'  from dual  connect by level <= 5000 union all select ';' from dual;   INSQL ------------------------------------------------------------------- insert into intest4 (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10) select 1,1,1,1,1,1,1,1,1,1 from dual union all .. select 5000,5000,5000,5000,5000,5000,5000,5000,5000,5000 from dual union all ;   5002 rows selected.

 

 

vi로 SQL 이외 문자 삭제 및 마지막 union all 제거 및 commit; 추가

 

 

10046 트레이스 실행

1 2 3

SQL>  alter session set tracefile_identifier='INTEST4'; alter session set events '10046 trace name context forever, level 12';

 

 

insert 실행

1	SQL> @in10_4.sql

 

 

10046 트레이스 해제

1 2 3

SQL> alter session set events '10046 trace name context off';   Session altered.

 

 

트레이스 경로 확인

1 2 3 4 5 6 7 8 9

SQL>  set lines 200 pages 1000 col value for a100 select value from v$diag_info where name = 'Default Trace File';   VALUE ---------------------------------------------------------------------------------------------------- /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_24022_INTEST4.trc

 

 

tkprof 로 변환

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

$ cd /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/ $ tkprof oracle19_ora_24022_INTEST4.trc oracle19_ora_24022_INTEST4.txt sys=no   trace 확인 $ vi oracle19_ora_24022_INTEST4.txt call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1     11.27      11.31          0          0          0           0 Execute      1     11.21      11.25          0        207        442        5000 Fetch        0      0.00       0.00          0          0          0           0 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        2     22.48      22.57          0        207        442        5000   Misses in library cache during parse: 1 Misses in library cache during execute: 1

22.57초 소요됨

 

 

5. bulk insert 테스트

(다른 테스트는 5천건으로 테스트했지만 bulk는 빨라서 100배인 50만건을 insert 함)
세션 재접속 후 10046 트레이스 실행

1 2 3

SQL>  alter session set tracefile_identifier='INTEST5'; alter session set events '10046 trace name context forever, level 12';

 

 

insert 실행

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

SQL> declare     type tbl_ins is table of intest5%rowtype index by binary_integer;     w_ins tbl_ins; begin     for i in 1..500000 loop          w_ins(i).col1 := i;         w_ins(i).col2 := i;         w_ins(i).col3 := i;         w_ins(i).col4 := i;         w_ins(i).col5 := i;         w_ins(i).col6 := i;         w_ins(i).col7 := i;         w_ins(i).col8 := i;         w_ins(i).col9 := i;         w_ins(i).col10 := i;     end loop;          forall i in 1..500000          insert into intest5 (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10)          values (w_ins(i).col1, w_ins(i).col2, w_ins(i).col3, w_ins(i).col4, w_ins(i).col5, w_ins(i).col6, w_ins(i).col7, w_ins(i).col8, w_ins(i).col9, w_ins(i).col10);     commit; end; /   PL/SQL procedure successfully completed.

 

 

10046 트레이스 해제

1 2 3

SQL> alter session set events '10046 trace name context off';   Session altered.

 

 

트레이스 경로 확인

1 2 3 4 5 6 7 8 9

SQL>  set lines 200 pages 1000 col value for a100 select value from v$diag_info where name = 'Default Trace File';   VALUE ---------------------------------------------------------------------------------------------------- /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_25287_INTEST5.trc

 

 

tkprof 로 변환

1 2	$ cd /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/ $ tkprof oracle19_ora_25287_INTEST5.trc oracle19_ora_25287_INTEST5.txt sys=no

 

 

trace 확인

1 2 3 4 5 6 7 8 9 10 11

$ vi oracle19_ora_25287_INTEST5.txt call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          0          0          0           0 Execute      1      0.52       0.52          0       1554      31364      500000 Fetch        0      0.00       0.00          0          0          0           0 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        2      0.52       0.52          0       1554      31364      500000   Misses in library cache during parse: 1 Misses in library cache during execute: 1

0.52초 소요됨

 

 

결론 :
1. 일반 insert(일반 sql) : 1.61초(parse call 5000번 수행)
2. 일반 insert(pl/sql) : 0.53초(어플리케이션 커서 캐싱 효과 parse call 1번만 수행)
3. insert all(일반 sql) : 83.48초
4. union all insert(일반 sql) : 22.57초
5. bulk insert : 0.52초(50만건)

 

전체적으로 보면 벌크 insert가 가장 빨랐고,
컬럼 10개인 테이블에 대해 5천건 insert를 기준으로
일반 insert(pl/sql포함)이 가장 빠르고 그다음 union all을 이용한 insert 이 빨랐고 insert all이 가장 느렸음

 

참고로 컬럼 1개인 테이블에 대해 5천건 insert를 기준으로는
일반 insert(pl/sql포함)이 가장 빠르고 그다음 insert all이 빨랐고 union all을 이용한 insert 이 가장 느렸음
참고 : 오라클 19c 일반 insert, insert all, union all insert 성능 테스트 ( https://positivemh.tistory.com/1108 )

 

 

참조 : 

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/INSERT.html
https://positivemh.tistory.com/183
https://positivemh.tistory.com/1108