OS 환경 : Oracle Linux 8.4 (64bit)
DB 환경 : Oracle Database 23.4.0.24.05 ai Free
방법 : 오라클 23ai append_values 힌트를 사용한 벌크 인서트 성능 비교
오라클 23ai 부터 Direct Load(Direct Path Insert) 에 대한 트랜잭션 제한 해제됨
direct path insert 후 해당 테이블을 직렬 또는 병렬로 조회하거나, dml 이나 병렬 dml을 수행할 수 있음
참고 : 오라클 23ai Direct Load(Direct Path Insert) 에 대한 트랜잭션 제한 해제 ( https://positivemh.tistory.com/1127 )
아래 링크처럼 벌크 인서트를 사용할때
INSERT INTO sample_t VALUES w_ins(i); 이방식으로 반복적으로 insert를 하기 때문에
이전버전에서는 Direct Load 제약에 걸려 append_values 힌트를 사용할 수 없었음
하지만 23ai 부터는 append_values 도 중간에 commit 하지 않고 계속 실행 가능하기 때문에
벌크 인서트에서 성능이 잘나올것 같아 한번 테스트를 시도해봄
참고 : 오라클 19c 대량 샘플데이터 생성용 쿼리 벌크 인서트 ( https://positivemh.tistory.com/808 )
본문에서는 벌크인서트시 append_values 힌트 사용 유무에 따라 성능이 얼마나 차이나는지 확인해봄
사용 요건
- 대상 테이블이 힙 구조 테이블이어야함(기본값)
- 대상 테이블이 AUTO SEGMENT SPACE MANAGEMENT(ASSM) 와 AUTOALLOCATE 옵션을 사용하여 생성된 테이블스페이스에 존재해야함
- COMPATIBLE 파라미터가 23.0 이상으로 설정되어 있어야함
테스트
1. append_values 힌트 미사용 벌크인서트 xplan 확인
2. append_values 힌트 사용 벌크인서트 xplan 확인
3. append_values 힌트 미사용 벌크인서트 10046 trace 확인
4. append_values 힌트 사용 벌크인서트 10046 trace 확인
5. 10046 트레이스 비교 분석
테스트
1. append_values 힌트 미사용 벌크인서트 xplan 확인
샘플 테이블 생성
|
1
2
3
4
5
6
7
8
9
|
SQL>
drop table sample_t purge;
create table sample_t
(id1 number, id2 number, name varchar2(10),
date1 varchar2(8), date2 varchar2(8), date3 date,
phone varchar2(13), price number, qty number,
test1 number, test2 varchar2(5), test3 varchar2(4)
)
nologging;
|
append_values 힌트 없이 데이터 삽입(50만건)
|
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
|
SQL>
set timing on
DECLARE
TYPE tbl_ins IS TABLE OF sample_t%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..500000 LOOP
w_ins(i).id1 := i;
w_ins(i).id2 := i||ceil(dbms_random.value(1, 10000000));
w_ins(i).name := dbms_random.string('x',5);
w_ins(i).date1 := round(dbms_random.value(2010,2021))||to_char(round(dbms_random.value(1,12)), 'FM09')||to_char( round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date2 := '2021'||to_char(round(dbms_random.value(1,12)) , 'FM09')||to_char(round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date3 := to_date(round(dbms_random.value(2010,2021))||'-'||round(dbms_random.value(1,12))||'-'||round(dbms_random.value(1,28))||' '||round(dbms_random.value(1, 23))||':'||round(dbms_random.value(0, 59))||':'||round(dbms_random.value(0, 59)), 'YYYY-MM-DD HH24:MI:SS');
w_ins(i).phone := '010-'||ceil(dbms_random.value(1000, 9999))||'-'||ceil(dbms_random.value(1000, 9999));
w_ins(i).price := ceil(dbms_random.value(1, 10))*1000;
w_ins(i).qty := ceil(dbms_random.value(1, 10));
w_ins(i).test1 := 1234;
w_ins(i).test2 := 'SQLP';
w_ins(i).test3 := 'A'||ceil(dbms_random.value(100, 999));
END LOOP;
FORALL i in 1..500000 INSERT /*+ gather_plan_statistics test1 */ INTO sample_t VALUES w_ins(i);
COMMIT;
END;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:09.22
|
append_values 힌트를 사용하지 않았을때 9.22초 소요됨
sql_id 확인
|
1
2
3
4
5
6
7
8
|
SQL> select sql_id , sql_text from v$sql where sql_text like '%test1%';
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5yc9k6s7c398g
INSERT /*+ gather_plan_statistics test1 */ INTO SAMPLE_T VALUES (:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 )
|
xplan 확인
|
1
2
3
4
5
6
7
8
|
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR('5yc9k6s7c398g', 0, 'ADVANCED ALLSTATS LAST');
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 1 | 0 |00:00:00.51 | 54579 | 1 |
| 1 | LOAD TABLE CONVENTIONAL | SAMPLE_T | 1 | | 0 |00:00:00.51 | 54579 | 1 |
-------------------------------------------------------------------------------------------------------
|
append_values 힌트를 사용하지 않았기 때문에 conventional insert 로 동작함
개별 insert 구문 동작 시간이 0.51초 소요됨
용량 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
set lines 200 pages 1000
col segment_name for a20
select segment_name, blocks, bytes/1024/1024 mb
from dba_segments
where segment_name = 'SAMPLE_T';
SEGMENT_NAME BLOCKS MB
-------------------- ---------- ----------
SAMPLE_T 6016 47
|
6016 블록을 사용했고, 47mb임
2. append_values 힌트 사용 벌크인서트 xplan 확인
샘플 테이블 재생성
|
1
2
3
4
5
6
7
8
9
|
SQL>
drop table sample_t purge;
create table sample_t
(id1 number, id2 number, name varchar2(10),
date1 varchar2(8), date2 varchar2(8), date3 date,
phone varchar2(13), price number, qty number,
test1 number, test2 varchar2(5), test3 varchar2(4)
)
nologging;
|
direct path write wait 조회
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
set lines 200 pages 1000
col event for a30
select event, total_waits, time_waited
from v$system_event
where event like 'direct path write%';
EVENT TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
direct path write 5096 86
direct path write temp 22317 485
|
direct path write 의 total_waits 이 5096임
append_values 힌트와 함께 데이터 삽입(50만건)
|
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
|
SQL>
set timing on
DECLARE
TYPE tbl_ins IS TABLE OF sample_t%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..500000 LOOP
w_ins(i).id1 := i;
w_ins(i).id2 := i||ceil(dbms_random.value(1, 10000000));
w_ins(i).name := dbms_random.string('x',5);
w_ins(i).date1 := round(dbms_random.value(2010,2021))||to_char(round(dbms_random.value(1,12)), 'FM09')||to_char( round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date2 := '2021'||to_char(round(dbms_random.value(1,12)) , 'FM09')||to_char(round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date3 := to_date(round(dbms_random.value(2010,2021))||'-'||round(dbms_random.value(1,12))||'-'||round(dbms_random.value(1,28))||' '||round(dbms_random.value(1, 23))||':'||round(dbms_random.value(0, 59))||':'||round(dbms_random.value(0, 59)), 'YYYY-MM-DD HH24:MI:SS');
w_ins(i).phone := '010-'||ceil(dbms_random.value(1000, 9999))||'-'||ceil(dbms_random.value(1000, 9999));
w_ins(i).price := ceil(dbms_random.value(1, 10))*1000;
w_ins(i).qty := ceil(dbms_random.value(1, 10));
w_ins(i).test1 := 1234;
w_ins(i).test2 := 'SQLP';
w_ins(i).test3 := 'A'||ceil(dbms_random.value(100, 999));
END LOOP;
FORALL i in 1..500000 INSERT /*+ gather_plan_statistics test2 append_values */ INTO sample_t VALUES w_ins(i);
COMMIT;
END;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:09.50
|
append_values 힌트를 사용했을때 9.5초 소요됨
direct path write wait 재조회
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
set lines 200 pages 1000
col event for a30
select event, total_waits, time_waited
from v$system_event
where event like 'direct path write%';
EVENT TOTAL_WAITS TIME_WAITED
------------------------------ ----------- -----------
direct path write 5151 90
direct path write temp 22317 485
|
direct path write 의 total_waits 이 5096 에서 5151로 증가함
용량 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
set lines 200 pages 1000
col segment_name for a20
select segment_name, blocks, bytes/1024/1024 mb
from dba_segments
where segment_name = 'SAMPLE_T';
SEGMENT_NAME BLOCKS MB
-------------------- ---------- ----------
SAMPLE_T 6016 47
|
동일하게 6016 블록을 사용했고, 47mb임
sql_id 확인
|
1
2
3
4
5
6
7
8
|
SQL> select sql_id , sql_text from v$sql where sql_text like '%test2%';
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
6q57uw5b75wcn
INSERT /*+ gather_plan_statistics test2 append_values */ INTO SAMPLE_T VALUES (:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 )
|
xplan 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR('6q57uw5b75wcn', 0, 'ADVANCED ALLSTATS LAST');
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 1 | 0 |00:00:00.81 | 8806 | 26 | 5868 | | | |
| 1 | LOAD AS SELECT | SAMPLE_T | 1 | | 0 |00:00:00.81 | 8806 | 26 | 5868 | 2071K| 2071K| 2071K (0)|
| 2 | OPTIMIZER STATISTICS GATHERING | | 1 | | 500K|00:00:00.57 | 829 | 26 | 0 | 256K| 256K| |
| 3 | BULK BINDS GET | | 1 | | 500K|00:00:00.27 | 0 | 0 | 0 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------
|
append_values 힌트를 사용했기 때문에 LOAD AS SELECT(direct path insert)로 동작함
개별 insert 구문 동작 시간이 0.81초 소요됨
3. append_values 힌트 미사용 벌크인서트 10046 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
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
|
$ cat FREE_ora_473512.txt
SQL ID: 67r40amwtths2 Plan Hash: 0
DECLARE
TYPE tbl_ins IS TABLE OF sample_t%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..500000 LOOP
w_ins(i).id1 := i;
w_ins(i).id2 := i||ceil(dbms_random.value(1, 10000000));
w_ins(i).name := dbms_random.string('x',5);
w_ins(i).date1 := round(dbms_random.value(2010,2021))||to_char(round(dbms_random.value(1,12)), 'FM09')||to_char( round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date2 := '2021'||to_char(round(dbms_random.value(1,12)) , 'FM09')||to_char(round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date3 := to_date(round(dbms_random.value(2010,2021))||'-'||round(dbms_random.value(1,12))||'-'||round(dbms_random.value(1,28))||' '||round(dbms_random.value(1, 23))||':'||round(dbms_random.value(0, 59))||':'||round(dbms_random.value(0, 59)), 'YYYY-MM-DD HH24:MI:SS');
w_ins(i).phone := '010-'||ceil(dbms_random.value(1000, 9999))||'-'||ceil(dbms_random.value(1000, 9999));
w_ins(i).price := ceil(dbms_random.value(1, 10))*1000;
w_ins(i).qty := ceil(dbms_random.value(1, 10));
w_ins(i).test1 := 1234;
w_ins(i).test2 := 'SQLP';
w_ins(i).test3 := 'A'||ceil(dbms_random.value(100, 999));
END LOOP;
FORALL i in 1..500000 INSERT /*+ gather_plan_statistics test1 */ INTO sample_t VALUES w_ins(i);
COMMIT;
END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 199 76 0
Execute 1 8.59 8.63 0 5222 49643 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 8.61 8.65 0 5421 49719 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 139
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Allocate UGA memory from OS 4 0.00 0.00
Allocate CGA memory from OS 7 0.00 0.00
Allocate PGA memory from OS 339 0.00 0.00
Free private memory to OS 13 0.00 0.01
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
SQL ID: 5yc9k6s7c398g Plan Hash: 0
INSERT /*+ gather_plan_statistics test1 */ INTO SAMPLE_T
VALUES
(:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.52 0.53 0 5178 49630 500000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.52 0.53 0 5178 49630 500000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 139 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL SAMPLE_T (cr=5184 pr=2 pw=0 time=529924 us starts=1 direct read=0 direct write=0)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 2 0.00 0.00
Allocate UGA memory from OS 69 0.00 0.00
log file switch (private strand flush incomplete)
2 0.00 0.00
********************************************************************************
|
4. append_values 힌트 사용 벌크인서트 10046 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
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
|
$ cat FREE_ora_473571.txt
SQL ID: cn1k8319zzptr Plan Hash: 0
DECLARE
TYPE tbl_ins IS TABLE OF sample_t%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..500000 LOOP
w_ins(i).id1 := i;
w_ins(i).id2 := i||ceil(dbms_random.value(1, 10000000));
w_ins(i).name := dbms_random.string('x',5);
w_ins(i).date1 := round(dbms_random.value(2010,2021))||to_char(round(dbms_random.value(1,12)), 'FM09')||to_char( round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date2 := '2021'||to_char(round(dbms_random.value(1,12)) , 'FM09')||to_char(round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date3 := to_date(round(dbms_random.value(2010,2021))||'-'||round(dbms_random.value(1,12))||'-'||round(dbms_random.value(1,28))||' '||round(dbms_random.value(1, 23))||':'||round(dbms_random.value(0, 59))||':'||round(dbms_random.value(0, 59)), 'YYYY-MM-DD HH24:MI:SS');
w_ins(i).phone := '010-'||ceil(dbms_random.value(1000, 9999))||'-'||ceil(dbms_random.value(1000, 9999));
w_ins(i).price := ceil(dbms_random.value(1, 10))*1000;
w_ins(i).qty := ceil(dbms_random.value(1, 10));
w_ins(i).test1 := 1234;
w_ins(i).test2 := 'SQLP';
w_ins(i).test3 := 'A'||ceil(dbms_random.value(100, 999));
END LOOP;
FORALL i in 1..500000 INSERT /*+ gather_plan_statistics test2 append_values */ INTO sample_t VALUES w_ins(i);
COMMIT;
END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 51 76 0
Execute 1 8.66 8.70 0 1526 8111 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 8.67 8.71 0 1577 8187 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 139
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Allocate CGA memory from OS 17 0.00 0.00
Allocate UGA memory from OS 4 0.00 0.00
Allocate PGA memory from OS 342 0.00 0.00
Free private memory to OS 23 0.00 0.01
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
SQL ID: 6q57uw5b75wcn Plan Hash: 3581094869
INSERT /*+ gather_plan_statistics test2 append_values */ INTO SAMPLE_T
VALUES
(:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.74 0.74 0 435 7650 500000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.74 0.74 0 435 7650 500000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 139 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD AS SELECT SAMPLE_T (cr=751 pr=10 pw=5868 time=836806 us starts=1 direct read=0 direct write=0)
500000 500000 500000 OPTIMIZER STATISTICS GATHERING (cr=404 pr=10 pw=0 time=585785 us starts=1 direct read=0 direct write=0)
500000 500000 500000 BULK BINDS GET (cr=0 pr=0 pw=0 time=281930 us starts=1 direct read=0 direct write=0)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 3 0.00 0.00
Allocate CGA memory from OS 2 0.00 0.00
direct path write 55 0.00 0.01
direct path sync 1 0.00 0.00
********************************************************************************
|
5. 10046 트레이스 비교 분석
insert call 통계 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
append_values 미사용시
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.52 0.53 0 5178 49630 500000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.52 0.53 0 5178 49630 500000
append_values 사용시
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.74 0.74 0 435 7650 500000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.74 0.74 0 435 7650 500000
|
10046 트레이스의 insert call 통계 부분을 보면 query와 current 이 차이가 남
append_values 를 사용했을때가 훨씬 낮음, 그리고 insert 시에는 속도차이가 별로 나지않지만 append_values 를 사용 했을때가 미세하게 더 오래걸림(0.2초 정도)
plsql call 통계 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
append_values 미사용시
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 199 76 0
Execute 1 8.59 8.63 0 5222 49643 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 8.61 8.65 0 5421 49719 1
append_values 사용시
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 51 76 0
Execute 1 8.66 8.70 0 1526 8111 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 8.67 8.71 0 1577 8187 1
|
10046 트레이스의 plsql call 통계 부분을 보면 query와 current 이 차이가 남(insert 값도 같이 들어가있는듯함)
이 부분도 속도차이는 미미함
wait 이벤트 확인(insert 부분)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
append_values 미사용시
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 2 0.00 0.00
Allocate UGA memory from OS 69 0.00 0.00
log file switch (private strand flush incomplete)
2 0.00 0.00
********************************************************************************
append_values 사용시
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 3 0.00 0.00
Allocate CGA memory from OS 2 0.00 0.00
direct path write 55 0.00 0.01
direct path sync 1 0.00 0.00
********************************************************************************
|
append_values 미사용시에는 'Allocate UGA memory from OS'가 가장 높고
append_values 사용시에는 'direct path write'가 가장 높음
wait 이벤트 확인(plsql 부분)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
append_values 미사용시
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Allocate UGA memory from OS 4 0.00 0.00
Allocate CGA memory from OS 7 0.00 0.00
Allocate PGA memory from OS 339 0.00 0.00
Free private memory to OS 13 0.00 0.01
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
append_values 사용시
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Allocate CGA memory from OS 17 0.00 0.00
Allocate UGA memory from OS 4 0.00 0.00
Allocate PGA memory from OS 342 0.00 0.00
Free private memory to OS 23 0.00 0.01
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
|
두가지 모두 'Allocate PGA memory from OS'이 가장 높음, 두가지 방식 모두 pga를 사용해 속도차이가 안나는것인지는 모르겠음
결론 :
벌크인서트시 append_values 힌트를 사용하지 않았을때와 사용 했을때 속도차이가 크게 나지 않음
플랜을 보면 append_values 힌트를 사용했을때 direct path write 로 동작하긴 했음
그리고 두 방식 모두 쌓이는 용량과 블록은 동일했음
xplan 에서는 특이점을 찾지 못했지만, 10046 트레이스에서는 아래와 같은 정보들이 확인됨
append_values 힌트를 사용하지 않은 경우 insert 통계 및 이벤트
- query : 5178
- current : 49630
- event : 'Allocate UGA memory from OS'가 가장 높음
append_values 힌트를 사용한 경우 insert 통계 및 이벤트
- query : 435
- current : 7650
- event : 'direct path write'가 가장 높음
append_values 힌트를 사용하지 않은 경우 plsql 통계 및 이벤트
- query : 5421
- current : 49719
- event : 'Allocate PGA memory from OS'가 가장 높음
append_values 힌트를 사용한 경우 plsql 통계 및 이벤트
- query : 1577
- current : 8187
- event : 'Allocate PGA memory from OS'가 가장 높음
이 결과들로 봤을때 direct path가 항상 빠른건 아니라서 상황에 맞게 사용하는게 중요할듯함
참조 :
https://apex.oracle.com/pls/apex/features/r/dbfeatures/features?feature_id=1808
https://docs.oracle.com/en/database/oracle/oracle-database/23/nfcoa/oltp_dbarch.html#GUID-89256-1
https://docs.oracle.com/en/database/oracle/oracle-database/23/vldbg/using-parallel.html#GUID-6626C70C-876C-47A4-8C01-9B66574062D8
https://docs.oracle.com/en/database/oracle/oracle-database/23/vldbg/using-parallel.html#GUID-53B78D2C-FA4C-4CA1-AF28-91D5AE142859
https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/types-parallelism.html#GUID-53B78D2C-FA4C-4CA1-AF28-91D5AE142859
https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/types-parallelism.html#GUID-6626C70C-876C-47A4-8C01-9B66574062D8
https://tuna.tistory.com/102
https://oracle-randolf.blogspot.com/2023/08/oracle-23c-free-unrestricted-direct.html
https://positivemh.tistory.com/808
https://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_ID:228657900346252297
https://positivemh.tistory.com/1127
https://positivemh.tistory.com/1128
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| 오라클 데이터베이스 무료 분석 툴 SQLdb360 사용법 (0) | 2024.12.17 |
|---|---|
| 오라클 19c 테이블 ctas 생성 및 append 삽입시, 인덱스 생성시 자동 통계정보 수집 (0) | 2024.10.24 |
| 오라클 19c hash 조인 과다 실행시 부하 테스트 (0) | 2024.05.28 |
| 오라클 19c ordered_predicates 힌트 사용 확인(xplan, 10053 비교) (0) | 2024.05.24 |
| 오라클 19c insert 시 commit 주기에 따른 성능 테스트 (0) | 2024.05.23 |