OS 환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c 일반 insert, insert all, union all insert 성능 테스트
컬럼 1개인 테이블에 대해 일반 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;
|
Bulk insert 구문 : 테이블 레코드 형식의 배열에 값을 저장하고 한번에 insert 구문을 실행함
사용 예시 : 대량 데이터 삽입 insert 빠르게 실행 벌크 insert ( https://positivemh.tistory.com/183 )
테스트
1. 일반 insert 테스트(일반 sql)
2. 일반 insert 테스트(pl/sql)
3. insert all 테스트(일반 sql)
4. insert all 테스트(pl/sql)
5. union all insert 테스트(일반 sql)
6. union all insert 테스트(pl/sql)
7. bulk insert 테스트
(다른 테스트는 5천건으로 테스트했지만 bulk는 빨라서 100배인 50만건을 insert 함)
테스트
샘플 테이블 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL>
drop table intest1 purge;
drop table intest2 purge;
drop table intest3 purge;
drop table intest4 purge;
drop table intest5 purge;
drop table intest6 purge;
drop table intest7 purge;
create table intest1 (col1 number);
create table intest2 (col1 number);
create table intest3 (col1 number);
create table intest4 (col1 number);
create table intest5 (col1 number);
create table intest6 (col1 number);
create table intest7 (col1 number);
Table created.
|
1. 일반 insert 테스트(일반 sql)
insert 쿼리 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
spool in1.sql
set lines 200 pages 10000
select 'insert into intest1 (col1) values ('||level||');' insql
from dual
connect by level <= 5000;
INSQL
----------------------------------------------------------------------------
insert into intest1 (col1) values (1);
..
insert into intest1 (col1) values (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> @in1.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_14903_INTEST1.trc
|
tkprof 로 변환
|
1
2
|
$ cd /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/
$ tkprof oracle19_ora_14903_INTEST1.trc oracle19_ora_14903_INTEST1.txt sys=no
|
trace 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
$ vi oracle19_ora_14903_INTEST1.txt
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5002 1.20 1.67 0 0 0 0
Execute 5002 0.12 0.18 0 63 10207 5000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10004 1.32 1.86 0 63 10207 5000
Misses in library cache during parse: 5000
Misses in library cache during execute: 1
|
1.86초 소요됨
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
|
SQL>
begin
for i in 1..5000 loop
insert into intest2 (col1) values (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_17428_INTEST2.trc
|
tkprof 로 변환
|
1
2
|
$ cd /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/
$ tkprof oracle19_ora_17428_INTEST2.trc oracle19_ora_17428_INTEST2.txt sys=no
|
trace 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
$ vi oracle19_ora_17428_INTEST2.txt
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 5000 0.12 0.25 0 63 5224 5000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5001 0.12 0.25 0 63 5224 5000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
|
0.25초 소요됨
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 in3.sql
set lines 200 pages 10000
select 'insert all' from dual
union all
select 'into intest3 (col1) values ('||level||')' insql
from dual
connect by level <= 5000
union all
select 'select * from dual;' from dual;
INSERTALL
----------------------------------------------------------------------------
insert all
into intest3 (col1) values (1)
..
into intest3 (col1) values (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> @in3.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_25569_INTEST3.trc
|
tkprof 로 변환
|
1
2
|
$ cd /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/
$ tkprof oracle19_ora_25569_INTEST3.trc oracle19_ora_25569_INTEST3.txt sys=no
|
trace 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
$ vi oracle19_ora_25569_INTEST3.txt
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 3.69 7.43 2 10022 0 0
Execute 1 3.25 7.65 128 633 11123 5000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 6.94 15.09 130 10655 11123 5000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
|
15.09초 소요됨
4. insert all 테스트(pl/sql)
세션 재접속 후 10046 트레이스 실행
|
1
2
3
|
SQL>
alter session set tracefile_identifier='INTEST4';
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
|
SQL>
declare
v_sql clob := empty_clob();
v_chunk varchar2(32767);
begin
dbms_lob.createtemporary(v_sql, true);
v_chunk := 'insert all ';
dbms_lob.writeappend(v_sql, length(v_chunk), v_chunk);
for i in 1..5000 loop
v_chunk := 'into intest4 (col1) values (' || i || ') ';
dbms_lob.writeappend(v_sql, length(v_chunk), v_chunk);
end loop;
v_chunk := 'select * from dual';
dbms_lob.writeappend(v_sql, length(v_chunk), v_chunk);
execute immediate v_sql;
dbms_lob.freetemporary(v_sql);
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_39127_INTEST4.trc
|
tkprof 로 변환
|
1
2
|
$ cd /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/
$ tkprof oracle19_ora_39127_INTEST4.trc oracle19_ora_39127_INTEST4.txt sys=no
|
trace 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
$ vi oracle19_ora_39127_INTEST4.txt
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 3.91 7.86 0 0 0 0
Execute 1 3.67 7.40 0 63 10212 5000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 7.59 15.27 0 63 10212 5000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
|
15.27초 소요됨
이 plsql 트레이스의 SQL을 보면 3번과 동일한 방식으로 수행되어 속도차이가 없는걸로 보임
5. 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 in5.sql
set lines 200 pages 10000
select 'insert into intest5 (col1)' insql from dual
union all
select 'select '||level||' from dual union all'
from dual
connect by level <= 5000
union all
select ';' from dual;
INSQL
-------------------------------------------------------------------
insert into intest5 (col1)
select 1 from dual union all
..
select 5000 from dual union all
;
5002 rows selected.
|
vi로 SQL 이외 문자 삭제 및 마지막에 commit; 추가
10046 트레이스 실행
|
1
2
3
|
SQL>
alter session set tracefile_identifier='INTEST5';
alter session set events '10046 trace name context forever, level 12';
|
insert 실행
|
1
|
SQL> @in5.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_40828_INTEST5.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_40828_INTEST5.trc oracle19_ora_40828_INTEST5.txt sys=no
trace 확인
$ vi oracle19_ora_40828_INTEST5.txt
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 5.57 11.22 0 10 0 0
Execute 1 5.48 11.07 0 51 156 5000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 11.06 22.29 0 61 156 5000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
|
22.29초 소요됨
6. union all insert 테스트(pl/sql)
세션 재접속 후 10046 트레이스 실행
|
1
2
3
|
SQL>
alter session set tracefile_identifier='INTEST6';
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
v_sql clob := empty_clob();
v_chunk varchar2(32767);
begin
dbms_lob.createtemporary(v_sql, true);
v_chunk := 'insert into intest6 (col1) ';
dbms_lob.writeappend(v_sql, length(v_chunk), v_chunk);
for i in 1..5000 loop
if i = 5000 then
v_chunk := 'select ' || i || ' from dual';
else
v_chunk := 'select ' || i || ' from dual union all ';
end if;
dbms_lob.writeappend(v_sql, length(v_chunk), v_chunk);
end loop;
execute immediate v_sql;
dbms_lob.freetemporary(v_sql);
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_48893_INTEST6.trc
|
tkprof 로 변환
|
1
2
|
$ cd /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/
$ tkprof oracle19_ora_48893_INTEST6.trc oracle19_ora_48893_INTEST6.txt sys=no
|
trace 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
$ vi oracle19_ora_48893_INTEST6.txt
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 5.64 11.43 0 0 0 0
Execute 1 5.59 11.28 0 61 155 5000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 11.24 22.72 0 61 155 5000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
|
22.72초 소요됨
이 plsql 트레이스의 SQL을 보면 5번과 동일한 방식으로 수행되어 속도차이가 없는걸로 보임
7. bulk insert 테스트
(다른 테스트는 5천건으로 테스트했지만 bulk는 빨라서 100배인 50만건을 insert 함)
세션 재접속 후 10046 트레이스 실행
|
1
2
3
|
SQL>
alter session set tracefile_identifier='INTEST7';
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
|
SQL>
declare
type tbl_ins is table of intest7%rowtype index by binary_integer;
w_ins tbl_ins;
begin
for i in 1..500000 loop
w_ins(i).col1 :=i;
end loop;
forall i in 1..500000 insert into intest7 values w_ins(i);
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_24879_INTEST7.trc
|
tkprof 로 변환
|
1
2
|
$ cd /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/
$ tkprof oracle19_ora_24879_INTEST7.trc oracle19_ora_24879_INTEST7.txt sys=no
|
trace 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
$ vi oracle19_ora_24879_INTEST7.txt
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.17 0.35 0 986 8841 500000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.17 0.35 0 986 8841 500000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
|
0.35초 소요됨
결론 :
1. 일반 insert(일반 sql) : 1.86초(parse call 5000번 수행)
2. 일반 insert(pl/sql) : 0.25초(어플리케이션 커서 캐싱 효과 parse call 1번만 수행)
3. insert all(일반 sql) : 15.09초
4. insert all(pl/sql) : 15.27초
5. union all insert(일반 sql) : 22.29초
6. union all insert(pl/sql) : 22.72초
7. bulk insert : 0.35초(50만건)
전체적으로 보면 벌크 insert가 가장 빨랐고,
컬럼 1개인 테이블에 대해 5천건 insert를 기준으로 일반 insert(pl/sql포함)이 가장 빠르고 그다음 insert all이 빨랐고 union all을 이용한 insert 이 가장 느렸음
참고로 컬럼 10개인 테이블에 대해 5천건 insert를 기준으로
일반 insert(pl/sql포함)이 가장 빠르고 그다음 union all을 이용한 insert 이 빨랐고 insert all이 가장 느렸음
참고 : 오라클 19c 일반 insert, insert all, union all insert 성능 테스트2 ( https://positivemh.tistory.com/1109 )
참조 :
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/INSERT.html
https://positivemh.tistory.com/183
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| 오라클 19c insert 시 commit 주기에 따른 성능 테스트 (0) | 2024.05.23 |
|---|---|
| 오라클 19c 일반 insert, insert all, union all insert 성능 테스트(컬럼 10개) (0) | 2024.05.22 |
| 오라클 19c Parallel DML 시 Direct Path Write 이벤트 관련 (0) | 2024.04.19 |
| 오라클 19c 다른 세션 트레이스 설정 (0) | 2024.04.06 |
| 오라클 19c latch: cache buffers chains 이벤트 발생시 핫블록 찾기 (0) | 2024.03.28 |