OS 환경 : Oracle Linux 8.4 (64bit)
DB 환경 : Oracle Database 23.4.0.24.05 ai Free
방법 : 오라클 23ai 신기능 load_method, load_type 힌트
23ai 버전에서 테스트를 하다 xplan outline data 에서 load_method, load_type 이라는 힌트 두가지를 발견함
현재 이 힌트들은 오라클 공식 홈페이지에 문서화되어 있지 않음
먼저 이 load_type 힌트는 직렬 sql과 병렬 sql을 구분하는 힌트임
예를들어 load_type(테이블명 직렬/병렬(serial/parallel) 이렇게 사용하면 직렬로 수행할지 병렬로 수행할지를 정할 수 있음
parallel 힌트나 no_parallel 힌트와 동일 또는 비슷한 기능을 하는 힌트같음
그리고 load_method 힌트는 insert 시 conventional insert 를 할지 Direct Path Insert 를 할지를 정하는 힌트임
예를들어 load_method(테이블명 conventional/high_water_mark) 이렇게 사용하면 conventional insert 나 Direct Path Insert 를 정할수 있음
append 힌트나 noappend 힌트와 동일 또는 비슷한 기능을 하는 힌트같음
본문에서는 이 힌트들을 사용해보고 어떻게 동작하는지 확인해봄
테스트
1. append 미사용시 플랜 확인
2. append 사용시 플랜 확인
3. load_method 힌트 conventional 테스트
4. load_method 힌트 high_water_mark 테스트
5. load_type 힌트 serial 테스트
6. load_type 힌트 parallel 테스트
테스트
힌트 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
set lines 200 pages 1000
col name for a40
col class for a30
col inverse for a30
select name, substr(sql_feature,7) class, inverse, version, version_outline, target_level,
decode(bitand(target_level, 1), 0, 'no', 'yes') statement_level,
decode(bitand(target_level, 2), 0, 'no', 'yes') query_block_level,
decode(bitand(target_level, 4), 0, 'no', 'yes') object_level,
decode(bitand(target_level, 8), 0, 'no', 'yes') join_level
from v$sql_hint h
where name like '%LOAD_%'
order by sql_feature, name;
NAME CLASS INVERSE VERSION VERSION_OUTLINE TARGET_LEVEL STA QUE OBJ JOI
---------------------------------------- ------------------------------ ------------------------------ ------------------------- ------------------------- ------------ --- --- --- ---
LOAD_METHOD EXECUTION 23.1.0 23.1.0 4 no no yes no
LOAD_TYPE EXECUTION 23.1.0 23.1.0 4 no no yes no
|
23ai에서 2가지 힌트가 추가됨
1. append 미사용시 플랜 확인
샘플 테이블 생성
|
1
2
3
|
SQL>
drop table emp2;
create table emp2 as select * from emp;
|
statistics_level 파라미터 all로 변경
|
1
2
3
|
SQL> alter session set statistics_level = all;
Session altered.
|
일반 모드로 insert
|
1
2
3
|
SQL> insert into emp2 select * from emp2;
14 rows created.
|
xplan 확인
|
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
|
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4q8ccgwx1pvy0, child number 0
-------------------------------------
insert into emp2 select * from emp2
Plan hash value: 2941272003
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | | 3 (100)| | 0 |00:00:00.01 | 46 | 1 |
| 1 | LOAD TABLE CONVENTIONAL | EMP2 | 1 | | | | | 0 |00:00:00.01 | 46 | 1 |
| 2 | TABLE ACCESS FULL | EMP2 | 1 | 14 | 532 | 3 (0)| 00:00:01 | 28 |00:00:00.01 | 17 | 1 |
------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / "EMP2"@"SEL$1"
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('23.1.0')
DB_VERSION('23.1.0')
OPT_PARAM('optimizer_dynamic_sampling' 0)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"INS$1")
LOAD_METHOD(@"INS$1" "EMP2"@"INS$1" CONVENTIONAL)
LOAD_TYPE(@"INS$1" "EMP2"@"INS$1" SERIAL)
FULL(@"INS$1" "EMP2"@"INS$1")
FULL(@"SEL$1" "EMP2"@"SEL$1")
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
2 - "EMP2"."EMPNO"[NUMBER,22], "EMP2"."ENAME"[VARCHAR2,10], "EMP2"."JOB"[VARCHAR2,9], "EMP2"."MGR"[NUMBER,22],
"EMP2"."HIREDATE"[DATE,7], "EMP2"."SAL"[NUMBER,22], "EMP2"."COMM"[NUMBER,22], "EMP2"."DEPTNO"[NUMBER,22]
Query Block Registry:
---------------------
INS$1 (PARSER) [FINAL]
SEL$1 (PARSER) [FINAL]
52 rows selected.
|
append 힌트를 사용하지 않았을때 Outline Data 에 이렇게 두가지 힌트가 보임
LOAD_METHOD(@"INS$1" "EMP2"@"INS$1" CONVENTIONAL)
LOAD_TYPE(@"INS$1" "EMP2"@"INS$1" SERIAL)
2. append 사용시 플랜 확인
이전 dml 롤백
|
1
2
3
|
SQL> rollback;
Rollback complete.
|
append 모드로 insert
|
1
2
3
|
SQL> insert /*+ append */ into emp2 select * from emp2;
14 rows created.
|
xplan 확인
|
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
|
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID aj3w54auqd6nk, child number 0
-------------------------------------
insert /*+ gather_plan_statistics append */ into emp2 select * from emp2
Plan hash value: 1283500778
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | | 3 (100)| | 0 |00:00:00.01 | 39 | 1 | 1 | | | |
| 1 | LOAD AS SELECT | EMP2 | 1 | | | | | 0 |00:00:00.01 | 39 | 1 | 1 | 1043K| 1043K| 1043K (0)|
| 2 | OPTIMIZER STATISTICS GATHERING | | 1 | 14 | 532 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 3 | 1 | 0 | 256K| 256K| |
| 3 | TABLE ACCESS FULL | EMP2 | 1 | 14 | 532 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 2 | 1 | 0 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / "EMP2"@"SEL$1"
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('23.1.0')
DB_VERSION('23.1.0')
OPT_PARAM('optimizer_dynamic_sampling' 0)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"INS$1")
LOAD_METHOD(@"INS$1" "EMP2"@"INS$1" HIGH_WATER_MARK)
LOAD_TYPE(@"INS$1" "EMP2"@"INS$1" SERIAL)
FULL(@"INS$1" "EMP2"@"INS$1")
FULL(@"SEL$1" "EMP2"@"SEL$1")
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[120], SYSDEF[32720]
2 - ["EMP2"."COMM", "EMP2"."DEPTNO", "EMP2"."EMPNO", "EMP2"."ENAME", "EMP2"."HIREDATE", "EMP2"."JOB", "EMP2"."MGR", "EMP2"."SAL", MIN("EMP2"."COMM"),
MAX("EMP2"."COMM"), MIN("EMP2"."DEPTNO"), MAX("EMP2"."DEPTNO"), MIN("EMP2"."EMPNO"), MAX("EMP2"."EMPNO"), MIN("EMP2"."ENAME"), MAX("EMP2"."ENAME"),
MIN("EMP2"."HIREDATE"), MAX("EMP2"."HIREDATE"), MIN("EMP2"."JOB"), MAX("EMP2"."JOB"), MIN("EMP2"."MGR"), MAX("EMP2"."MGR"), MIN("EMP2"."SAL"), MAX("EMP2"."SAL")]
"EMP2"."EMPNO"[NUMBER,22], "EMP2"."ENAME"[VARCHAR2,10], "EMP2"."JOB"[VARCHAR2,9], "EMP2"."MGR"[NUMBER,22], "EMP2"."HIREDATE"[DATE,7], "EMP2"."SAL"[NUMBER,22],
"EMP2"."COMM"[NUMBER,22], "EMP2"."DEPTNO"[NUMBER,22]
3 - "EMP2"."EMPNO"[NUMBER,22], "EMP2"."ENAME"[VARCHAR2,10], "EMP2"."JOB"[VARCHAR2,9], "EMP2"."MGR"[NUMBER,22], "EMP2"."HIREDATE"[DATE,7], "EMP2"."SAL"[NUMBER,22],
"EMP2"."COMM"[NUMBER,22], "EMP2"."DEPTNO"[NUMBER,22]
Query Block Registry:
---------------------
INS$1 (PARSER) [FINAL]
SEL$1 (PARSER) [FINAL]
59 rows selected.
|
append 힌트를 사용 했을때 Outline Data 에 이렇게 두가지 힌트가 보임
LOAD_METHOD(@"INS$1" "EMP2"@"INS$1" HIGH_WATER_MARK)
LOAD_TYPE(@"INS$1" "EMP2"@"INS$1" SERIAL)
3. load_method 힌트 conventional 테스트
이전 dml 롤백
|
1
2
3
|
SQL> rollback;
Rollback complete.
|
load_method 힌트 conventional 모드로 insert
|
1
2
3
|
SQL> insert /*+ load_method(emp2 conventional) */ into emp2 select * from emp2;
14 rows created.
|
xplan 확인
|
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
|
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 463whjbbtvc4n, child number 0
-------------------------------------
insert /*+ load_method(emp2 conventional) */ into emp2 select * from
emp2
Plan hash value: 2941272003
---------------------------------------------------------------------------------------------------------------------------
| 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 | 32 |
| 1 | LOAD TABLE CONVENTIONAL | EMP2 | 1 | | | | | 0 |00:00:00.01 | 32 |
| 2 | TABLE ACCESS FULL | EMP2 | 1 | 14 | 532 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 2 |
---------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / "EMP2"@"SEL$1"
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('23.1.0')
DB_VERSION('23.1.0')
OPT_PARAM('optimizer_dynamic_sampling' 0)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"INS$1")
LOAD_METHOD(@"INS$1" "EMP2"@"INS$1" CONVENTIONAL)
LOAD_TYPE(@"INS$1" "EMP2"@"INS$1" SERIAL)
FULL(@"INS$1" "EMP2"@"INS$1")
FULL(@"SEL$1" "EMP2"@"SEL$1")
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
2 - "EMP2"."EMPNO"[NUMBER,22], "EMP2"."ENAME"[VARCHAR2,10], "EMP2"."JOB"[VARCHAR2,9], "EMP2"."MGR"[NUMBER,22],
"EMP2"."HIREDATE"[DATE,7], "EMP2"."SAL"[NUMBER,22], "EMP2"."COMM"[NUMBER,22], "EMP2"."DEPTNO"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
0 - INS$1 / "EMP2"@"INS$1"
- load_method(emp2 conventional)
Query Block Registry:
---------------------
INS$1 (PARSER) [FINAL]
SEL$1 (PARSER) [FINAL]
60 rows selected.
|
load_method conventional insert 시 Outline Data 에 이렇게 두가지 힌트가 보임
LOAD_METHOD(@"INS$1" "EMP2"@"INS$1" CONVENTIONAL)
LOAD_TYPE(@"INS$1" "EMP2"@"INS$1" SERIAL)
Hint Report 에서도 load_method 힌트가 정상적으로 사용된것으로 표시됨(E나 U가 발생하지 않음)
참고 : 오라클 19c dbms_xplan.display_cursor Hint Report 포맷 ( https://positivemh.tistory.com/775 )
4. load_method 힌트 high_water_mark 테스트
이전 dml 롤백
|
1
2
3
|
SQL> rollback;
Rollback complete.
|
load_method 힌트 high_water_mark 모드로 insert
|
1
2
3
4
5
6
7
|
SQL> insert /*+ load_method(emp2 high_water_mark) */ into emp2 select * from emp2;
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 470705
Session ID: 41 Serial number: 21924
Help: https://docs.oracle.com/error-help/db/ora-03113/
|
이전 dml 들을 rollback 하고 load_method(emp2 high_water_mark) 힌트를 사용한 insert 를 실행하면 에러가 발생하면서 세션이 종료됨(버그인듯함)
참고 : https://forums.oracle.com/ords/apexds/post/bug-occurs-when-using-the-load-method-hint-9005
하지만 일반 insert 를 한번 실행한 뒤 load_method(emp2 high_water_mark) 힌트를 사용한 insert 실행시 에러가 발생하지 않음
|
1
2
3
4
5
6
|
SQL> insert into emp2 select * from emp2;
14 rows created.
SQL> insert /*+ load_method(emp2 high_water_mark) */ into emp2 select * from emp2;
28 rows created.
|
이 경우에는 에러가 발생하지 않음
xplan 확인
|
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
|
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 64ccd8wuwm8u5, child number 1
-------------------------------------
insert /*+ load_method(emp2 high_water_mark) */ into emp2 select * from
emp2
Plan hash value: 1283500778
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | 38 | 1 | | | |
| 1 | LOAD AS SELECT | EMP2 | 1 | | | | | 0 |00:00:00.01 | 38 | 1 | 1043K| 1043K| 1043K (0)|
| 2 | OPTIMIZER STATISTICS GATHERING | | 1 | 14 | 532 | 3 (0)| 00:00:01 | 28 |00:00:00.01 | 7 | 0 | 256K| 256K| |
| 3 | TABLE ACCESS FULL | EMP2 | 1 | 14 | 532 | 3 (0)| 00:00:01 | 28 |00:00:00.01 | 6 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / "EMP2"@"SEL$1"
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('23.1.0')
DB_VERSION('23.1.0')
OPT_PARAM('optimizer_dynamic_sampling' 0)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"INS$1")
LOAD_METHOD(@"INS$1" "EMP2"@"INS$1" HIGH_WATER_MARK)
LOAD_TYPE(@"INS$1" "EMP2"@"INS$1" SERIAL)
FULL(@"INS$1" "EMP2"@"INS$1")
FULL(@"SEL$1" "EMP2"@"SEL$1")
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[120], SYSDEF[32720]
2 - ["EMP2"."COMM", "EMP2"."DEPTNO", "EMP2"."EMPNO", "EMP2"."ENAME", "EMP2"."HIREDATE", "EMP2"."JOB", "EMP2"."MGR", "EMP2"."SAL", MIN("EMP2"."COMM"),
MAX("EMP2"."COMM"), MIN("EMP2"."DEPTNO"), MAX("EMP2"."DEPTNO"), MIN("EMP2"."EMPNO"), MAX("EMP2"."EMPNO"), MIN("EMP2"."ENAME"), MAX("EMP2"."ENAME"),
MIN("EMP2"."HIREDATE"), MAX("EMP2"."HIREDATE"), MIN("EMP2"."JOB"), MAX("EMP2"."JOB"), MIN("EMP2"."MGR"), MAX("EMP2"."MGR"), MIN("EMP2"."SAL"),
MAX("EMP2"."SAL")] "EMP2"."EMPNO"[NUMBER,22], "EMP2"."ENAME"[VARCHAR2,10], "EMP2"."JOB"[VARCHAR2,9], "EMP2"."MGR"[NUMBER,22], "EMP2"."HIREDATE"[DATE,7],
"EMP2"."SAL"[NUMBER,22], "EMP2"."COMM"[NUMBER,22], "EMP2"."DEPTNO"[NUMBER,22]
3 - "EMP2"."EMPNO"[NUMBER,22], "EMP2"."ENAME"[VARCHAR2,10], "EMP2"."JOB"[VARCHAR2,9], "EMP2"."MGR"[NUMBER,22], "EMP2"."HIREDATE"[DATE,7],
"EMP2"."SAL"[NUMBER,22], "EMP2"."COMM"[NUMBER,22], "EMP2"."DEPTNO"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
0 - INS$1 / "EMP2"@"INS$1"
- load_method(emp2 high_water_mark)
Query Block Registry:
---------------------
INS$1 (PARSER) [FINAL]
SEL$1 (PARSER) [FINAL]
67 rows selected.
|
load_method high_water_mark insert 시 Outline Data 에 이렇게 두가지 힌트가 보임
LOAD_METHOD(@"INS$1" "EMP2"@"INS$1" HIGH_WATER_MARK)
LOAD_TYPE(@"INS$1" "EMP2"@"INS$1" SERIAL)
Hint Report 에서도 load_method 힌트가 정상적으로 사용된것으로 표시됨(E나 U가 발생하지 않음)
5. load_type 힌트 serial 테스트
이전 dml 롤백
|
1
2
3
|
SQL> rollback;
Rollback complete.
|
load_type 힌트 serial 모드로 insert
|
1
2
3
|
SQL> insert /*+ load_type(emp2 serial) */ into emp2 select * from emp2;
14 rows created.
|
xplan 확인
|
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
|
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0d90q5th9n2fy, child number 0
-------------------------------------
insert /*+ load_type(emp2 serial) */ into emp2 select * from emp2
Plan hash value: 2941272003
---------------------------------------------------------------------------------------------------------------------------
| 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 | 11 |
| 1 | LOAD TABLE CONVENTIONAL | EMP2 | 1 | | | | | 0 |00:00:00.01 | 11 |
| 2 | TABLE ACCESS FULL | EMP2 | 1 | 14 | 532 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 6 |
---------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / "EMP2"@"SEL$1"
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('23.1.0')
DB_VERSION('23.1.0')
OPT_PARAM('optimizer_dynamic_sampling' 0)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"INS$1")
LOAD_METHOD(@"INS$1" "EMP2"@"INS$1" CONVENTIONAL)
LOAD_TYPE(@"INS$1" "EMP2"@"INS$1" SERIAL)
FULL(@"INS$1" "EMP2"@"INS$1")
FULL(@"SEL$1" "EMP2"@"SEL$1")
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
2 - "EMP2"."EMPNO"[NUMBER,22], "EMP2"."ENAME"[VARCHAR2,10], "EMP2"."JOB"[VARCHAR2,9], "EMP2"."MGR"[NUMBER,22],
"EMP2"."HIREDATE"[DATE,7], "EMP2"."SAL"[NUMBER,22], "EMP2"."COMM"[NUMBER,22], "EMP2"."DEPTNO"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
0 - INS$1 / "EMP2"@"INS$1"
- load_type(emp2 serial)
Query Block Registry:
---------------------
INS$1 (PARSER) [FINAL]
SEL$1 (PARSER) [FINAL]
59 rows selected.
|
load_type serial insert 시 Outline Data 에 이렇게 두가지 힌트가 보임
LOAD_METHOD(@"INS$1" "EMP2"@"INS$1" CONVENTIONAL)
LOAD_TYPE(@"INS$1" "EMP2"@"INS$1" SERIAL)
Hint Report 에서도 load_type 힌트가 정상적으로 사용된것으로 표시됨(E나 U가 발생하지 않음)
6. load_type 힌트 parallel 테스트
이전 dml 롤백
|
1
2
3
|
SQL> rollback;
Rollback complete.
|
parallel dml 활성화
|
1
2
3
|
SQL> alter session enable parallel dml;
Session altered.
|
load_type 힌트 parallel 모드로 insert
|
1
2
3
|
SQL> insert /*+ load_type(emp2 parallel) */ into emp2 select * from emp2;
14 rows created.
|
xplan 확인
|
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
|
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 01mq0c7t9155h, child number 0
-------------------------------------
insert /*+ load_type(emp2 parallel) */ into emp2 select * from emp2
Plan hash value: 2941272003
---------------------------------------------------------------------------------------------------------------------------
| 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 | 11 |
| 1 | LOAD TABLE CONVENTIONAL | EMP2 | 1 | | | | | 0 |00:00:00.01 | 11 |
| 2 | TABLE ACCESS FULL | EMP2 | 1 | 14 | 532 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 6 |
---------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / "EMP2"@"SEL$1"
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('23.1.0')
DB_VERSION('23.1.0')
OPT_PARAM('optimizer_dynamic_sampling' 0)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"INS$1")
LOAD_METHOD(@"INS$1" "EMP2"@"INS$1" CONVENTIONAL)
LOAD_TYPE(@"INS$1" "EMP2"@"INS$1" SERIAL)
FULL(@"INS$1" "EMP2"@"INS$1")
FULL(@"SEL$1" "EMP2"@"SEL$1")
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
2 - "EMP2"."EMPNO"[NUMBER,22], "EMP2"."ENAME"[VARCHAR2,10], "EMP2"."JOB"[VARCHAR2,9], "EMP2"."MGR"[NUMBER,22],
"EMP2"."HIREDATE"[DATE,7], "EMP2"."SAL"[NUMBER,22], "EMP2"."COMM"[NUMBER,22], "EMP2"."DEPTNO"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
0 - INS$1 / "EMP2"@"INS$1"
- load_type(emp2 parallel)
Query Block Registry:
---------------------
INS$1 (PARSER) [FINAL]
SEL$1 (PARSER) [FINAL]
59 rows selected.
|
load_type parallel insert 시 Outline Data 에 이렇게 두가지 힌트가 보임
LOAD_METHOD(@"INS$1" "EMP2"@"INS$1" CONVENTIONAL)
LOAD_TYPE(@"INS$1" "EMP2"@"INS$1" SERIAL)
Hint Report 에서는 load_type 힌트가 정상적으로 사용된것으로 표시되지만(E나 U가 발생하지 않음)
실제 Outline Data 에는 load_type 이 serial 이 사용된것으로 표시됨
아마 free 버전에서 parallel 을 사용할 수 없어서 이렇게 나오는듯함
결론 :
23ai 버전 이전에는 append 힌트를 사용해도 Outline Data 는 이 쿼리가 append 를 사용한건지 하지 않은건지 알수없었음
오직 플랜에 나오는 LOAD AS SELECT 나 direct path write 이벤트가 증가하는지만을 가지고 판단할수 있었음
하지만 23ai 버전부터는 Outline Data 의 load_method 힌트에 어떤 옵션이 사용됬는지만 보면 append 힌트가 제대로 사용됬는지 알수 있게됨
insert 시 병렬쿼리 사용유무도 Outline Data 의 load_type만 보고 알수 있게됨
현재 load_type 의 parallel 은 제대로 동작하지 않지만 이건 free 버전의 문제일수 있음
정식버전이 나오면 다시 테스트해봐야 할듯함
사용 가능한 힌트
serial insert : load_type(emp2 serial)
parallel insert : load_type(emp2 parallel)(에러는 안나지만 동작X(free 버전의 문제일수도 있음))
conventional insert : load_method(emp2 conventional)
append insert : load_method(emp2 high_water_mark)
참조 :
https://apex.oracle.com/pls/apex/features/r/dbfeatures/features?feature_id=1808
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://positivemh.tistory.com/775
https://forums.oracle.com/ords/apexds/post/bug-occurs-when-using-the-load-method-hint-9005
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c varchar2 컬럼에서 clob 으로 변경 가이드 (0) | 2024.07.31 |
|---|---|
| 오라클 12cR2 asm 환경 v$asm_disk 의 path 컬럼 값 (0) | 2024.07.17 |
| 오라클 23ai 신기능 Direct Load(Direct Path Insert) 에 대한 제약 해제 (0) | 2024.07.02 |
| 오라클 23ai 신기능 xplan 의 Query Block Registry 개선 (0) | 2024.07.01 |
| 오라클 23ai 신기능 SQL Transpiler (0) | 2024.06.29 |