OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.10.0.0
방법 : 오라클 19c insert append, append_values 속도 비교 테스트
oracle 19c 환경에서 append 힌트와 append_values 힌트 사용시 속도가 얼마나 차이나는지를 테스트함
append 힌트는 ctas(create table as select ~)나 itas(insert into select ~) 구문에서 사용가능한 힌트임
append 힌트 사용시 위와 같은 구문에서 direct path i/o 기능을 사용할수 있고
insert 시 freelist 를 조회하지 않고 hwm 뒷쪽에 insert를하기 때문에 일반 insert 에 비해 속도가 빠름
append 힌트 사용시 장점
1. Insert 할 때 Freelist 조회 안 함
2. Insert 할 블록을 찾기 위한 버퍼캐시 탐색 없음
3. 버퍼캐시에 적재하지 않고 데이터파일에 직접 Write
4. Undo 발생량 최소화
5. nologging 모드에서 사용한다면, Redo 로그 최소화
(https://cafe.naver.com/dbian/109)
append 힌트 사용시 단점
1. Exclusive 모드 TM Lock --> 동시 DML 불가
2. conventional insert가 없는 테이블일 경우, HWM 아래 쪽에 빈공간 양산
3. nologging 모드로 insert 한 경우, 복구 불가
하지만 append 힌트는 일반 insert 문(insert into ~ values ~)에서는 사용할수 없음(힌트가 무시됨)
11gR2에서 새로운 힌트가 나왔는데 append_values 라는 힌트임
이 힌트를 사용하면 insert values 절에서도 append 힌트와 같이 direct path i/o 기능을 사용할수 있음
테스트는 4가지 경우를 확인해봄
1. insert select 절에 append 미사용시
2. insert select 절에 append 사용시
3. 일반 insert values 절에 append_values 미사용시
4. 일반 insert values 절에 append_values 사용시
0. 테스트 전 대량 데이터용 테이블 생성
|
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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
대량 데이터용 테이블 생성
SQL>
create table intest1_dat (cola varchar2(20), colb number, colc number,
cold varchar2(30), cole varchar2(30), colf varchar2(30),
colg number, colh varchar2(30), coli varchar2(30), colj varchar2(30));
데이터 삽입
SQL>
set serveroutput on;
DECLARE
TYPE tbl_ins IS TABLE OF INTEST1_DAT%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..1000000 LOOP
w_ins(i).COLA :=i;
w_ins(i).COLB :=300000;
w_ins(i).COLC :=999;
w_ins(i).COLD :='DDDDDDDDDDDDDDDD';
w_ins(i).COLE :='EEEEEEEEEEEEEEEE';
w_ins(i).COLF :='FFFFFFFFFFFFFFFF';
w_ins(i).COLG :=9999999;
w_ins(i).COLH :='HHHHHHHHHHHHHHHHHHHHHHHHHH';
w_ins(i).COLI :='IIIIIIIIIIIIIIIllllIIIIIIIIIII';
w_ins(i).COLJ :='AAAAAAAAAAAAAAAAAAAAAAAAAA';
END LOOP;
FORALL i in 1..1000000 INSERT INTO INTEST1_DAT VALUES w_ins(i);
COMMIT;
END;
/
count 확인
SQL> select count (*) from intest1_dat;
COUNT(*)
----------
1000000
용량 확인
SQL>
set lines 200
col owner for a10
col segment_name for a20
select segment_name, sum(bytes)/1024/1024 as MB
from dba_segments
where segment_name='INTEST1_DAT'
GROUP BY segment_name;
SEGMENT_NAME MB
-------------------- ----------
INTEST1_DAT 176
|
1. insert select 절에 append 미사용시
샘플 테이블 생성 및 trace 설정
|
1
2
3
4
|
SQL>
create table intest1 as select * from intest1_dat where 1=2;
alter session set tracefile_identifier='itastest1';
alter session set events '10046 trace name context forever, level 8';
|
데이터 삽입 및 commit, trace 종료
|
1
2
3
4
|
SQL>
insert into intest1 select * from intest1_dat;
commit;
alter session set events '10046 trace name context off';
|
bdump trace 경로로 가서 tkprof 후 trace 확인
(trace 파일 만드는 법 및 tkprof 생략)
|
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
27
28
29
30
31
32
33
34
|
$ vi itastest1.trc
SQL ID: dy58sxj9m0qgc Plan Hash: 1601287119
insert into intest1 select * from intest1_dat
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 2.59 3.21 22340 36621 173600 1000000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 2.59 3.21 22340 36622 173600 1000000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL INTEST1 (cr=36622 pr=22339 pw=0 time=3207167 us starts=1)
1000000 1000000 1000000 TABLE ACCESS FULL INTEST1_DAT (cr=22341 pr=22337 pw=0 time=703569 us starts=1 cost=6077 size=148908015 card=973255)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 3 0.00 0.00
direct path read 709 0.00 0.15
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
|
=> 총 3.21초가 걸렸고 direct path read만 일어나고 direct path write는 일어나지 않음
2. insert select 절에 append 사용시
샘플 테이블 생성 및 trace 설정
|
1
2
3
4
|
SQL>
create table intest2 as select * from intest1_dat where 1=2;
alter session set tracefile_identifier='itastest2';
alter session set events '10046 trace name context forever, level 8';
|
데이터 삽입 및 commit, trace 종료
|
1
2
3
4
|
SQL>
insert /*+ append */ into intest2 select * from intest1_dat;
commit;
alter session set events '10046 trace name context off';
|
bdump trace 경로로 가서 tkprof 후 trace 확인
(trace 파일 만드는 법 및 tkprof 생략)
|
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
27
28
29
30
31
32
33
34
35
36
37
38
39
|
$ vi itastest2.trc
SQL ID: fk1qjvqqpwz 1k Plan Hash: 2351433377
insert /*+ append */ into intest2 select * from intest1_dat
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 10 0 0
Execute 1 3.25 3.79 22338 22630 25579 1000000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 3.26 3.80 22338 22640 25579 1000000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD AS SELECT INTEST2 (cr=23053 pr=22373 pw=22677 time=3849407 us starts=1)
1000000 1000000 1000000 OPTIMIZER STATISTICS GATHERING (cr=22787 pr=22373 pw=0 time=2190351 us starts=1 cost=6077 size=148908015 card=973255)
1000000 1000000 1000000 TABLE ACCESS FULL INTEST1_DAT (cr=22341 pr=22337 pw=0 time=1016963 us starts=1 cost=6077 size=148908015 card=973255)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 1 0.00 0.00
PGA memory operation 13 0.00 0.00
direct path read 709 0.00 0.14
direct path write 187 0.00 0.17
direct path sync 2 0.39 0.39
log file sync 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
|
=> 총 3.80초가 걸렸고 direct path read와 direct path write가 일어남
하지만 오히려 append 힌트를 사용하지 않았을때가 빠름
이는 19c 기능인 자동 통계정보 수집 기능 때문에 그런것
자동 통계정보 수집 기능을 끄고 재확인(또는 11gR2 버전에서 확인)
샘플 테이블 생성 및 trace 설정
|
1
2
3
4
|
SQL>
create table intest3 as select * from intest1_dat where 1=2;
alter session set tracefile_identifier='itastest3';
alter session set events '10046 trace name context forever, level 8';
|
데이터 삽입 및 commit, trace 종료(no_gather_optimizer_statistics 힌트 사용)
|
1
2
3
4
|
SQL>
insert /*+ append no_gather_optimizer_statistics */ into intest3 select * from intest1_dat;
commit;
alter session set events '10046 trace name context off';
|
bdump trace 경로로 가서 tkprof 후 trace 확인
(trace 파일 만드는 법 및 tkprof 생략)
|
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
27
28
29
30
31
32
33
34
35
36
37
38
39
|
$ vi itastest3.trc
SQL ID: 26079rwzf4d9k Plan Hash: 2351433377
insert /*+ append no_gather_optimizer_statistics */ into intest3 select *
from intest1_dat
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 2.07 2.68 22339 22606 25717 1000000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 2.07 2.69 22339 22607 25717 1000000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD AS SELECT INTEST3 (cr=22607 pr=22337 pw=22677 time=2636237 us starts=1)
1000000 1000000 1000000 TABLE ACCESS FULL INTEST1_DAT (cr=22341 pr=22337 pw=0 time=1047836 us starts=1 cost=6077 size=148908015 card=973255)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 2 0.00 0.00
local write wait 128 0.00 0.03
direct path read 709 0.00 0.13
PGA memory operation 4 0.00 0.00
direct path write 187 0.00 0.18
direct path sync 2 0.48 0.48
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
|
=> 총 2.69초가 걸렸고 direct path read와 direct path write가 일어나고
append 힌트를 사용하지 않았을때보다 빠름
아래는 11gR2에서 테스트한 결과(trace 만 첨부)
|
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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
|
append 미사용시(11gR2)
SQL ID: dy58sxj9m0qgc Plan Hash: 1601287119
insert into intest1 select * from intest1_dat
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 109 0 0
Execute 1 2.30 3.46 0 62769 196893 1000000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 2.31 3.47 0 62878 196893 1000000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 53
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL (cr=62877 pr=0 pw=0 time=3454574 us)
1000000 1000000 1000000 TABLE ACCESS FULL INTEST1_DAT (cr=22339 pr=0 pw=0 time=2181433 us cost=6070 size=161113590 card=1053030)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
********************************************************************************
append 사용시(11gR2)
SQL ID: fk1qjvqqpwz1k Plan Hash: 2351433377
insert /*+ append */ into intest2 select * from intest1_dat
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 109 0 0
Execute 1 0.49 0.67 0 22497 25574 1000000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.50 0.68 0 22606 25574 1000000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 53
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD AS SELECT (cr=22605 pr=0 pw=22677 time=674558 us)
1000000 1000000 1000000 TABLE ACCESS FULL INTEST1_DAT (cr=22339 pr=0 pw=0 time=426995 us cost=6070 size=161113590 card=1053030)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
direct path write 222 0.01 0.15x
********************************************************************************
|
=> 11gR2에서는 append 미사용시 3.47초 사용시 0.68의 속도로 19c에서 테스트했을때보다 더 빠르게 실행됨
3. 일반 insert values 절에 append_values 미사용시
일반 insert values 절 단건은 속도테스트가 어려울것 같아 forall 구문으로 여러건 삽입하는 방식으로 테스트함
*참고로 append_values 절로 1건 insert 후 commit을 하지 않으면 다음 1건을 삽입하려 할 때
ORA-12838: cannot read/modify an object after modifying it in parallel 에러가 발생함
그렇기 때문에 아래와 같은 방식으로 테스트 불가함
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
begin
for i in 1 .. 10000 loop
insert /*+ append_values no_gather_optimizer_statistics */ into INVTEST2 values (i);
end loop;
commit;
end;
/
begin
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
ORA-06512: at line 5
|
그렇다고 아래와 같이 건별로 commit을 하면 오히려 성능에 좋지 않음
|
1
2
3
4
5
6
7
8
|
SQL>
begin
for i in 1 .. 10000 loop
insert /*+ append_values no_gather_optimizer_statistics */ into INVTEST2 values (i);
commit;
end loop;
end;
/
|
그렇기 때문에 아래와 같이 forall 구문으로 여러건 삽입하는 방식으로 테스트함
샘플 테이블 생성 및 trace 설정
|
1
2
3
4
5
|
SQL>
set timing on
create table INVTEST1 (col1 number);
alter session set tracefile_identifier='invtest1';
alter session set events '10046 trace name context forever, level 8';
|
데이터 삽입 및 commit, trace 종료
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL>
DECLARE
TYPE NumList IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
testlist NumList;
BEGIN
FOR j IN 1..10000 LOOP
testlist(j) := j;
END LOOP;
FOR k IN 1..1000 LOOP
FORALL i IN 1..10000
INSERT
INTO INVTEST1
VALUES (testlist(i));
commit;
END LOOP;
END;
/
Elapsed: 00:00:03.69
alter session set events '10046 trace name context off';
|
sqlplus timing 결과값상 3.69초 소요됨
bdump trace 경로로 가서 tkprof 후 trace 확인
(trace 파일 만드는 법 및 tkprof 생략)
|
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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
|
$ vi invtest1.trc
DECLARE
TYPE NumList IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
testlist NumList;
BEGIN
FOR j IN 1..10000 LOOP
testlist(j) := j;
END LOOP;
FOR k IN 1..1000 LOOP
FORALL i IN 1..10000
INSERT
INTO INVTEST1
VALUES (testlist(i));
commit;
END LOOP;
END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.07 0.07 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.07 0.07 0 0 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 80
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PGA memory operation 10 0.00 0.00
log file sync 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 13.80 13.80
********************************************************************************
SQL ID: b1ry4b6v5t9sz Plan Hash: 0
INSERT INTO INVTEST1
VALUES
(:B1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1000 2.92 3.57 0 14773 160082 10000000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1001 2.92 3.57 0 14773 160082 10000000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 80 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL INVTEST1 (cr=109 pr=0 pw=0 time=3440 us starts=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 1 0.00 0.00
PGA memory operation 2 0.00 0.00
log buffer space 13 0.09 0.63
********************************************************************************
SQL ID: 8ggw94h7mvxd7 Plan Hash: 0
COMMIT
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1000 0.03 0.03 0 0 1000 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1001 0.03 0.03 0 0 1000 0
Misses in library cache during parse: 0
Parsing user id: 80 (recursive depth: 1)
********************************************************************************
|
=> insert만 봤을 때 총 3.57초가 걸림
4. 일반 insert values 절에 append_values 사용시
샘플 테이블 생성 및 trace 설정
|
1
2
3
4
5
|
SQL>
set timing on
create table INVTEST2 (col1 number);
alter session set tracefile_identifier='invtest2';
alter session set events '10046 trace name context forever, level 8';
|
데이터 삽입 및 commit, trace 종료
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL>
DECLARE
TYPE NumList IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
testlist NumList;
BEGIN
FOR j IN 1..10000 LOOP
testlist(j) := j;
END LOOP;
FOR k IN 1..1000 LOOP
FORALL i IN 1..10000
INSERT /*+ append_values no_gather_optimizer_statistics */
INTO INVTEST2
VALUES (testlist(i));
commit;
END LOOP;
END;
/
Elapsed: 00:00:04.57
alter session set events '10046 trace name context off';
|
sqlplus timing 결과값상 4.57초 소요됨
bdump trace 경로로 가서 tkprof 후 trace 확인
(trace 파일 만드는 법 및 tkprof 생략)
|
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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
|
$ vi invtest2.trc
DECLARE
TYPE NumList IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
testlist NumList;
BEGIN
FOR j IN 1..10000 LOOP
testlist(j) := j;
END LOOP;
FOR k IN 1..1000 LOOP
FORALL i IN 1..10000
INSERT /*+ append_values no_gather_optimizer_statistics */
INTO INVTEST2
VALUES (testlist(i));
commit;
END LOOP;
END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.07 0.07 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.07 0.07 0 0 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 80
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PGA memory operation 12 0.00 0.00
log file sync 1 0.08 0.08
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
SQL ID: 2n68xw8rwwxut Plan Hash: 3581094869
INSERT /*+ append_values no_gather_optimizer_statistics */ INTO INVTEST2
VALUES
(:B1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1000 3.25 4.18 0 2172 26560 10000000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1001 3.25 4.18 0 2172 26560 10000000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 80 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD AS SELECT INVTEST2 (cr=8 pr=0 pw=16 time=4654 us starts=1)
10000 10000 10000 BULK BINDS GET (cr=0 pr=0 pw=0 time=1012 us starts=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PGA memory operation 2 0.00 0.00
Disk file operations I/O 1 0.00 0.00
direct path write 1126 0.00 0.04
direct path sync 1000 0.02 0.23
log buffer space 6 0.31 0.71
********************************************************************************
SQL ID: 8ggw94h7mvxd7 Plan Hash: 0
COMMIT
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1000 0.08 0.21 0 154 21117 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1001 0.08 0.21 0 154 21117 0
Misses in library cache during parse: 0
Parsing user id: 80 (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
log buffer space 1 0.13 0.13
********************************************************************************
|
=> insert 만 봤을 때 총 4.18초가 걸렸고 direct path sync와 direct path write가 일어나고
append_values 힌트를 사용하지 않았을때보다 느림
wait time이 direct path sync와 direct path write에 많이 걸림
아래는 11gR2에서 테스트한 결과 첨부예정(trace 만 첨부)
결론 :
insert select 절 사용시에는 append 사용시 더 빠르다.
일반 insert values 절 사용시에는 append_values 사용시 더 느리다.(19c)
(어떤 이유로 느린것인지 조금더 연구가 필요해보인다. )
참조 :
https://positivemh.tistory.com/850
오라클 19c insert append, append_values 힌트 Direct path, 실행계획 테스트
OS환경 : Oracle Linux 7.6 (64bit) DB 환경 : Oracle Database 19.10.0.0 방법 : 오라클 19c insert append, append_values 힌트 Direct path, 실행계획 테스트 oracle 19c 환경에서 append 힌트와 append_values 힌트 사용시 direct path 방
positivemh.tistory.com
https://positivemh.tistory.com/851
오라클 19c insert append, append_values 힌트 속도 비교 테스트
OS환경 : Oracle Linux 7.6 (64bit) DB 환경 : Oracle Database 19.10.0.0 방법 : 오라클 19c insert append, append_values 속도 비교 테스트 oracle 19c 환경에서 append 힌트와 append_values 힌트 사용시 속도가 얼마나 차이나는
positivemh.tistory.com
https://positivemh.tistory.com/852
https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Comments.html#GUID-DD069661-D431-40F5-9303-DB8C1153D87D
https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements006.htm#SQLRF50901
https://docs.oracle.com/database/121/TGSQL/tgsql_statscon.htm#TGSQL344
https://blogs.oracle.com/optimizer/post/space-management-and-oracle-direct-path-load
https://oracle-base.com/articles/11g/append-values-hint-11gr2
https://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:1211797200346279484
http://www.dba-oracle.com/t_append_oracle.htm
https://hoing.io/archives/292
https://hrjeong.tistory.com/223
https://cafe.naver.com/dbian/109
https://cafe.naver.com/dbian/234
https://cafe.naver.com/dbian/232
https://cafe.naver.com/dbian/5804
https://www.dbmaestro.com/blog/database-devops/boost-insert-statements
https://www.bobbydurrettdba.com/2012/10/19/append_values-hint/
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| 오라클 19c 옵티마이저에 영향을 주는 파라미터 목록 (0) | 2023.01.30 |
|---|---|
| 오라클 19c insert append, append_values 힌트 extent 테스트 (0) | 2022.11.06 |
| 오라클 19c insert append, append_values 힌트 Direct path, 실행계획 테스트 (0) | 2022.11.04 |
| 오라클 19c 하드파싱 쿼리 플랜 변경 의심시 조치방법 (0) | 2022.10.09 |
| 오라클 19c cursor_sharing 테스트 (0) | 2022.08.08 |