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
'ORACLE > Performance Tuning ' 카테고리의 다른 글
오라클 19c ordered_predicates 힌트 사용 확인(xplan, 10053 비교) (0) | 2024.05.24 |
---|---|
오라클 19c insert 시 commit 주기에 따른 성능 테스트 (0) | 2024.05.23 |
오라클 19c 일반 insert, insert all, union all insert 성능 테스트(컬럼 1개) (0) | 2024.05.19 |
오라클 19c Parallel DML 시 Direct Path Write 이벤트 관련 (0) | 2024.04.19 |
오라클 19c 다른 세션 트레이스 설정 (0) | 2024.04.06 |