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 방식을 사용하는지 여부를 테스트함
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 기능을 사용할수 있음
테스트는 6가지 경우를 확인해봄
1. 일반 insert values 절에 append 미사용시
2. 일반 insert values 절에 append 사용시
3. insert select 절에 append 미사용시
4. insert select 절에 append 사용시
5. 일반 insert values 절에 append_values 미사용시
6. 일반 insert values 절에 append_values 사용시
1. 일반 insert values 절에 append 미사용시
샘플 테이블 생성 및 trace 설정
|
1
2
3
4
|
SQL>
create table test20221106 (col1 number);
alter session set tracefile_identifier='test1';
alter session set events '10046 trace name context forever, level 8';
|
데이터 삽입 및 commit, trace 종료
|
1
2
3
4
|
SQL>
insert /*+ gather_plan_statistics test1 */ into test20221106 values (1);
commit;
alter session set events '10046 trace name context off';
|
v$sql 에서 해당 sql의 sql_id 확인
|
1
2
3
4
|
SQL>
select sql_id, plan_hash_value, last_load_time, sql_text
from v$sql
where sql_text like '%insert /*+ gather_plan_statistics test1 */%';
|
xplan display_cursor로 커서에 올라간 실제 플랜 조회
|
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> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('6036tn843ymfh', NULL, 'ADVANCED ALLSTATS LAST'));
SQL_ID 6036tn843ymfh, child number 0
-------------------------------------
insert /*+ gather_plan_statistics test1 */ into test20221106 values (1)
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 1 | 0 |00:00:00.01 | 34 |
| 1 | LOAD TABLE CONVENTIONAL | TEST20221106 | 1 | | 0 |00:00:00.01 | 34 |
--------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - INS$1
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - INS$1
E - test1
Note
-----
- cpu costing is off (consider enabling it)
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[INS$1]]></n><f><h><t><![CDATA[TEST20221106]]></t><s><![CDATA[IN
S$1]]></s></h></f></q>
|
bdump trace 경로로 가서 tkprof 후 trace 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
(trace 파일 만드는 법 및 tkprof 생략)
$ vi test1.trc
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 8 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 8 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL TEST20221106 (cr=1 pr=0 pw=0 time=62 us starts=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 2.42 2.42
********************************************************************************
|
direct path i/o 미작동
2. 일반 insert values 절에 append 사용시
trace 설정
|
1
2
3
|
SQL>
alter session set tracefile_identifier='test2';
alter session set events '10046 trace name context forever, level 8';
|
데이터 삽입 및 commit, trace 종료
|
1
2
3
4
|
SQL>
insert /*+ APPEND gather_plan_statistics test2 */ into test20221106 values (2);
commit;
alter session set events '10046 trace name context off';
|
xplan display_cursor로 커서에 올라간 실제 플랜 조회
|
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> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('1ap82k2nnxr04', NULL, 'ADVANCED ALLSTATS LAST'));
SQL_ID 1ap82k2nnxr04, child number 0
-------------------------------------
insert /*+ APPEND gather_plan_statistics test2 */ into test20221106
values (2)
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 1 | 0 |00:00:00.01 | 4 |
| 1 | LOAD TABLE CONVENTIONAL | TEST20221106 | 1 | | 0 |00:00:00.01 | 4 |
--------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - INS$1
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - INS$1
E - test2
Note
-----
- cpu costing is off (consider enabling it)
- Direct Load disabled because insert values with no append values hint used
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[INS$1]]></n><f><h><t><![CDATA[TEST20221106]]></t><s><![CDATA[IN
S$1]]></s></h></f></q>
|
bdump trace 경로로 가서 tkprof 후 trace 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
(trace 파일 만드는 법 및 tkprof 생략)
$ vi test2.trc
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 3 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL TEST20221106 (cr=1 pr=0 pw=0 time=34 us starts=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 1.47 1.47
********************************************************************************
|
direct path i/o 미작동
3. insert select 절에 append 미사용시
샘플 테이블 생성 및 trace 설정
|
1
2
3
4
|
SQL>
create table test20221106_2 as select * from test20221106 where 1=2;
alter session set tracefile_identifier='test3';
alter session set events '10046 trace name context forever, level 8';
|
데이터 삽입 및 commit, trace 종료
|
1
2
3
4
|
SQL>
insert /*+ gather_plan_statistics test3*/ into test20221106_2 select * from test20221106;
commit;
alter session set events '10046 trace name context off';
|
xplan display_cursor로 커서에 올라간 실제 플랜 조회
|
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
|
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dprdz9cz22mw3', NULL, 'ADVANCED ALLSTATS LAST'));
SQL_ID dprdz9cz22mw3, child number 0
-------------------------------------
insert /*+ gather_plan_statistics test3*/ into test20221106_2 select *
from test20221106
Plan hash value: 2588427966
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | | 3 (100)| | 0 |00:00:00.01 | 41 |
| 1 | LOAD TABLE CONVENTIONAL | TEST20221106_2 | 1 | | | | | 0 |00:00:00.01 | 41 |
| 2 | TABLE ACCESS FULL | TEST20221106 | 1 | 3 | 39 | 3 (0)| 00:00:01 | 3 |00:00:00.01 | 7 |
-------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / TEST20221106@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"INS$1")
FULL(@"INS$1" "TEST20221106_2"@"INS$1")
FULL(@"SEL$1" "TEST20221106"@"SEL$1")
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
2 - "TEST20221106"."COL1"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
0 - INS$1
E - test3
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[INS$1]]></n><f><h><t><![CDATA[TEST20221106_2]]></t><s><![CDATA[INS$1]]></s></h></f></q>
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[TEST20221106]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
|
bdump trace 경로로 가서 tkprof 후 trace 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
(trace 파일 만드는 법 및 tkprof 생략)
$ vi test3.trc
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 8 0 0
Execute 1 0.00 0.00 0 4 8 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.01 0 12 8 2
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL TEST20221106_2 (cr=4 pr=0 pw=0 time=75 us starts=1)
2 2 2 TABLE ACCESS FULL TEST20221106 (cr=3 pr=0 pw=0 time=15 us starts=1 cost=2 size=26 card=2)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 8.36 8.36
********************************************************************************
|
direct path i/o 미작동
4. insert select 절에 append 사용시
trace 설정
|
1
2
3
|
SQL>
alter session set tracefile_identifier='test4';
alter session set events '10046 trace name context forever, level 8';
|
데이터 삽입 및 commit, trace 종료
|
1
2
3
4
|
SQL>
insert /*+ gather_plan_statistics test4 APPEND */ into test20221106_2 select * from test20221106;
commit;
alter session set events '10046 trace name context off';
|
xplan display_cursor로 커서에 올라간 실제 플랜 조회
|
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> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('9msgtnmyjd3y3', NULL, 'ADVANCED ALLSTATS LAST'));
SQL_ID 9msgtnmyjd3y3, child number 0
-------------------------------------
insert /*+ gather_plan_statistics test4 APPEND */ into test20221106_2
select * from test20221106
Plan hash value: 1370867083
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Writes | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | | 3 (100)| | 0 |00:00:00.01 | 42 | 1 | | | |
| 1 | LOAD AS SELECT | TEST20221106_2 | 1 | | | | | 0 |00:00:00.01 | 42 | 1 | 1042K| 1042K| 1042K (0)|
| 2 | OPTIMIZER STATISTICS GATHERING | | 1 | 3 | 39 | 3 (0)| 00:00:01 | 3 |00:00:00.01 | 8 | 0 | 256K| 256K| |
| 3 | TABLE ACCESS FULL | TEST20221106 | 1 | 3 | 39 | 3 (0)| 00:00:01 | 3 |00:00:00.01 | 7 | 0 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / TEST20221106@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"INS$1")
FULL(@"INS$1" "TEST20221106_2"@"INS$1")
FULL(@"SEL$1" "TEST20221106"@"SEL$1")
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[120], SYSDEF[32720]
2 - "TEST20221106"."COL1"[NUMBER,22]
3 - "TEST20221106"."COL1"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
0 - INS$1
E - test4
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[INS$1]]></n><f><h><t><![CDATA[TEST20221106_2]]></t><s><![CDATA[INS$1]]></s></h></f></q>
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[TEST20221106]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
|
bdump trace 경로로 가서 tkprof 후 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
|
(trace 파일 만드는 법 및 tkprof 생략)
$ vi test4.trc
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 4 0 0
Execute 1 0.00 0.00 0 3 3 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 7 3 2
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD AS SELECT TEST20221106_2 (cr=3 pr=0 pw=1 time=467 us starts=1)
2 2 2 TABLE ACCESS FULL TEST20221106 (cr=3 pr=0 pw=0 time=17 us starts=1 cost=2 size=26 card=2)
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 1 0.00 0.00
direct path sync 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 1.58 1.58
********************************************************************************
|
direct path i/o 작동
5. 일반 insert values 절에 append_values 미사용시
샘플 테이블 생성 및 trace 설정
|
1
2
3
4
|
SQL>
create table test20221106_3 (col1 number);
alter session set tracefile_identifier='test5';
alter session set events '10046 trace name context forever, level 8';
|
데이터 삽입 및 commit, trace 종료
|
1
2
3
4
|
SQL>
insert /*+ gather_plan_statistics testnew1 */ into test20221106_3 values (1);
commit;
alter session set events '10046 trace name context off';
|
xplan display_cursor로 커서에 올라간 실제 플랜 조회
|
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
|
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('5j9qnx0mg44ny', NULL, 'ADVANCED ALLSTATS LAST'));
SQL_ID 5j9qnx0mg44ny, child number 0
-------------------------------------
insert /*+ gather_plan_statistics testnew1 */ into test20221106_3 values
(1)
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 1 | 0 |00:00:00.01 | 33 |
| 1 | LOAD TABLE CONVENTIONAL | test20221106_3 | 1 | | 0 |00:00:00.01 | 33 |
--------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - INS$1
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - INS$1
E - testnew1
Note
-----
- cpu costing is off (consider enabling it)
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[INS$1]]></n><f><h><t><![CDATA[test20221106_3]]></t><s><![CDATA[IN
S$1]]></s></h></f></q>
|
bdump trace 경로로 가서 tkprof 후 trace 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
(trace 파일 만드는 법 및 tkprof 생략)
$ vi test5.trc
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 1 8 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 2 8 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL TEST20221106_3 (cr=1 pr=0 pw=0 time=79 us starts=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 3.39 3.39
********************************************************************************
|
direct path i/o 미작동
6. 일반 insert values 절에 append_values 사용시
trace 설정
|
1
2
3
|
SQL>
alter session set tracefile_identifier='test6';
alter session set events '10046 trace name context forever, level 8';
|
데이터 삽입 및 commit, trace 종료
|
1
2
3
4
|
SQL>
insert /*+ gather_plan_statistics testnew2 APPEND_VALUES */ into test20221106_3 values (2);
commit;
alter session set events '10046 trace name context off';
|
xplan display_cursor로 커서에 올라간 실제 플랜 조회
|
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
|
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('8x5z3dfqzj2b9', NULL, 'ADVANCED ALLSTATS LAST'));
SQL_ID 8x5z3dfqzj2b9, child number 0
-------------------------------------
insert /*+ gather_plan_statistics testnew2 APPEND_VALUES */ into
test20221106_3 values (2)
Plan hash value: 3581094869
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 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.01 | 38 | 1 | 1 | | | |
| 1 | LOAD AS SELECT | test20221106_3 | 1 | | 0 |00:00:00.01 | 38 | 1 | 1 | 1042K| 1042K| 1042K (0)|
| 2 | OPTIMIZER STATISTICS GATHERING | | 1 | | 1 |00:00:00.01 | 1 | 0 | 0 | 256K| 256K| |
| 3 | BULK BINDS GET | | 1 | | 1 |00:00:00.01 | 0 | 0 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - INS$1
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[120], SYSDEF[32720]
2 - 2[2]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - INS$1
E - testnew2
Note
-----
- cpu costing is off (consider enabling it)
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[INS$1]]></n><f><h><t><![CDATA[test20221106_3]]></t><s><![CDATA[INS$1]]></s></h></f></q>
|
bdump trace 경로로 가서 tkprof 후 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
|
(trace 파일 만드는 법 및 tkprof 생략)
$ vi test6.trc
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 3 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD AS SELECT TEST20221106_3 (cr=0 pr=0 pw=1 time=559 us starts=1)
1 1 1 BULK BINDS GET (cr=0 pr=0 pw=0 time=1 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 1 0.00 0.00
direct path sync 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 2.83 2.83
********************************************************************************
|
direct path i/o 작동
결론
1. 일반 insert values 절에 append 미사용시 => direct path i/o 미작동
2. 일반 insert values 절에 append 사용시 => direct path i/o 미작동
3. insert select 절에 append 미사용시 => direct path i/o 미작동
4. insert select 절에 append 사용시 => direct path i/o 작동
5. 일반 insert values 절에 append_values 미사용시 => direct path i/o 미작동
6. 일반 insert values 절에 append_values 사용시 => direct path i/o 작동
insert select 절에는 append 힌트를 써야 하고 일반 insert values 문에는 insert_values 힌트를 써야
direct path i/o가 정상적으로 작동해 성능상 이점이 있다.
참조 :
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 insert append, append_values 힌트 extent 테스트 (0) | 2022.11.06 |
|---|---|
| 오라클 19c insert append, append_values 힌트 속도 비교 테스트 (0) | 2022.11.06 |
| 오라클 19c 하드파싱 쿼리 플랜 변경 의심시 조치방법 (0) | 2022.10.09 |
| 오라클 19c cursor_sharing 테스트 (0) | 2022.08.08 |
| 오라클 19c 실행계획 변경 시 spm 적용 방법 (0) | 2022.08.08 |