OS 환경 : Oracle Linux 8.4 (64bit)
DB 환경 : Oracle Database 23.4.0.24.05 ai Free
방법 : 오라클 23ai 신기능 Direct Load(Direct Path Insert) 에 대한 트랜잭션 제약 해제
오라클 23ai 버전부터 Direct Load 후 dml 및 병렬 dml에 대한 제약이 일부 해제됨
direct path insert 후 해당 테이블을 직렬 또는 병렬로 조회하거나, dml 이나 병렬 dml을 수행할 수 있음
이 기능은 _online_direct_load 파라미터로 제어됨
사용 요건
- 대상 테이블이 힙 구조 테이블이어야함(기본값)
- 대상 테이블이 AUTO SEGMENT SPACE MANAGEMENT(ASSM) 와 AUTOALLOCATE 옵션을 사용하여 생성된 테이블스페이스에 존재해야함
- COMPATIBLE 파라미터가 23.0 이상으로 설정되어 있어야함
공식 문서상으로는 병렬쿼리도 가능한 기능이지만 free 버전에서는 병렬쿼리 테스트가 불가해서
본문에서는 19c 버전과 23ai 버전에서 append 힌트와 append_values 힌트에 대해서만 테스트를 진행함
테스트
1. append 힌트 사용
2. append_values 힌트 사용
3. 실행계획 확인
4. 벌크 인서트시 사용
5. 파라미터
테스트
1. append 힌트 사용
샘플 테이블 생성(19c, 23ai 모두)
|
1
2
3
|
SQL>
drop table emp2;
create table emp2 as select * from emp;
|
append 모드로 insert(19c)
|
1
2
3
|
SQL> insert /*+ append */ into emp2 select * from emp2;
14 rows created.
|
정상적으로 수행됨
append 모드로 insert(23ai)
|
1
2
3
|
SQL> insert /*+ append */ into emp2 select * from emp2;
14 rows created.
|
정상적으로 수행됨
insert 중인 테이블 조회(19c)
|
1
2
3
4
5
|
SQL> select count(*) from emp2;
select * from emp2
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
|
처음 append insert 후 commit 하지 않고 테이블 조회시 에러가 발생함
insert 중인 테이블 조회(23ai)
|
1
2
3
4
5
|
SQL> select count(*) from emp2;
COUNT(*)
----------
28
|
정상적으로 조회됨
append 모드로 insert 재수행(19c)
|
1
2
3
4
5
|
SQL> insert /*+ append */ into emp2 select * from emp2;
insert /*+ append */ into emp2 select * from emp2
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
|
처음 append insert 후 commit 하지 않고 append insert 재수행시 에러가 발생함
append 모드로 insert 재수행(23ai)
|
1
2
3
|
SQL> insert /*+ append */ into emp2 select * from emp2;
28 rows created.
|
정상적으로 insert 됨
append 힌트 없이 insert 재수행(19c)
|
1
2
3
4
5
|
SQL> insert into emp2 select * from emp2;
insert into emp2 select * from emp2
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
|
처음 append insert 후 commit 하지 않고 insert 재수행시 에러가 발생함
append 힌트 없이 insert 재수행(23ai)
|
1
2
3
|
SQL> insert into emp2 select * from emp2;
56 rows created.
|
정상적으로 insert 됨
lock 조회(19c, 23ai 모두)
|
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>
set lines 200 pages 1000
col sid for 999999
col usn for a20
col object_name for a20
col subobject_name for a20
select sid, type, o.object_name, o.subobject_name, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
from v$lock v, dba_objects o
where 1=1
and TYPE in ('TX','TM')
and v.id1 = o.object_id(+)
order by sid, type;
19c
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
779 TM EMP2 63620 0 6 0 0 0 63620
779 TX 12845085 27825 6 0 0 196 29
23ai
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
19 TM EMP2 93719 0 6 0 0 1 28183
19 TX 589857 6239 6 0 0 9 33
|
두 버전 모두 lock은 동일하게 tm lock lmode 6와 tx lock lmode 6으로 잡힘
2. append_values 힌트 사용
샘플 테이블 생성(19c, 23ai 모두)
|
1
2
3
|
SQL>
drop table t1;
create table t1 (col1 number);
|
append_values 힌트 사용하여 insert(19c)
|
1
2
3
|
SQL> insert /*+ append_values */ into t1 values (1);
1 row created.
|
정상적으로 수행됨
append_values 힌트 사용하여 insert(23ai)
|
1
2
3
|
SQL> insert /*+ append_values */ into t1 values (1);
14 rows created.
|
정상적으로 수행됨
insert 중인 테이블 조회(19c)
|
1
2
3
4
5
|
SQL> select count(*) from t1;
select count(*) from t1
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
|
처음 append insert 후 commit 하지 않고 테이블 조회시 에러가 발생함
insert 중인 테이블 조회(23ai)
|
1
2
3
4
5
|
SQL> select count(*) from t1;
COUNT(*)
----------
1
|
정상적으로 조회됨
append_values 힌트 사용하여 insert 재수행(19c)
|
1
2
3
4
5
|
SQL> insert /*+ append_values */ into t1 values (1);
insert /*+ append_values */ into t1 values (1)
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
|
처음 append insert 후 commit 하지 않고 append insert 재수행시 에러가 발생함
append_values 힌트 사용하여 insert 재수행(23ai)
|
1
2
3
|
SQL> insert /*+ append_values */ into t1 values (1);
1 row created.
|
정상적으로 insert 됨
append_values 힌트 없이 insert 재수행(19c)
|
1
2
3
4
5
|
SQL> insert into t1 values (1);
insert into t1 values (1)
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
|
처음 append_values insert 후 commit 하지 않고 insert 재수행시 에러가 발생함
append_values 힌트 없이 insert 재수행(23ai)
|
1
2
3
|
SQL> insert into t1 values (1);
1 rows created.
|
정상적으로 insert 됨
lock 조회(19c, 23ai 모두)
|
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>
set lines 200 pages 1000
col sid for 999999
col usn for a20
col object_name for a20
col subobject_name for a20
select sid, type, o.object_name, o.subobject_name, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
from v$lock v, dba_objects o
where 1=1
and TYPE in ('TX','TM')
and v.id1 = o.object_id(+)
order by sid, type;
19c
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
779 TM T1 63621 0 6 0 0 0 63621
779 TX 13303826 27871 6 0 0 203 18
23ai
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
19 TM T1 93720 0 6 0 0 1 28184
19 TX 589840 6246 6 0 0 9 16
|
두 버전 모두 lock은 동일하게 tm lock lmode 6와 tx lock lmode 6으로 잡힘
3. 실행계획 확인
append 모드로 insert 재수행(23ai)
|
1
2
3
|
SQL> insert /*+ gather_plan_statistics 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 | 57 | 7 | 1 | | | |
| 1 | LOAD AS SELECT | EMP2 | 1 | | | | | 0 |00:00:00.01 | 57 | 7 | 1 | 1043K| 1043K| 1043K (0)|
| 2 | OPTIMIZER STATISTICS GATHERING | | 1 | 14 | 532 | 3 (0)| 00:00:01 | 56 |00:00:00.01 | 21 | 7 | 0 | 256K| 256K| |
| 3 | TABLE ACCESS FULL | EMP2 | 1 | 14 | 532 | 3 (0)| 00:00:01 | 56 |00:00:00.01 | 20 | 7 | 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.
|
Outline Data 에 LOAD_METHOD 와 LOAD_TYPE 이라는 새로운 힌트가 보임
참고 : 오라클 23ai load_method load_type 힌트 ( https://positivemh.tistory.com/1128 )
append_values 힌트 사용하여 insert 재수행(23ai)
|
1
2
3
|
SQL> insert /*+ gather_plan_statistics append_values */ into t1 values (1);
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
|
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4yp86d95hnb08, child number 0
-------------------------------------
insert /*+ gather_plan_statistics append_values */ into t1 values (1)
Plan hash value: 3581094869
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Cost | A-Rows | A-Time | Buffers | Writes | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 1 | 0 |00:00:00.01 | 33 | 1 | | | |
| 1 | LOAD AS SELECT | T1 | 1 | | 0 |00:00:00.01 | 33 | 1 | 1043K| 1043K| 1043K (0)|
| 2 | OPTIMIZER STATISTICS GATHERING | | 1 | | 1 |00:00:00.01 | 1 | 0 | 256K| 256K| |
| 3 | BULK BINDS GET | | 1 | | 1 |00:00:00.01 | 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 - [1, MIN(1), MAX(1)] 1[2]
Note
-----
- cpu costing is off (consider enabling it)
Query Block Registry:
---------------------
INS$1 (PARSER) [FINAL]
36 rows selected.
|
19c에 비해 크게 변경된점은 보이지 않음
4. 벌크 인서트시 사용
글이 길어져서 다른 게시글로 작성함
참고 : 오라클 23ai append_values 벌크 인서트 성능 차이 확인 ( https://positivemh.tistory.com/1129 )
5. 파라미터
이 기능은 _online_direct_load 히든 파라미터로 제어됨
이 파라미터를 0으로 변경하면 기능이 이전 버전처럼 동작함(append insert 후 재 append insert 안됨)
파라미터 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
SQL>
set lines 200 pages 1000
col desc for a70
col name for a30
col current_value for a15
col default_value for a15
col default_t_f for a15
col session for a10
col instance for a10
col sess_modi for a10
col sys_modi for a10
select
a.ksppinm "name",
decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "session",
c.ksppstvl "instance",
b.ksppstdfl "default_value",
b.ksppstdf "default_t_f",
decode(p.isses_modifiable,'FALSE','F','TRUE','T') "sess_modi",
decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "sys_modi",
a.ksppdesc "desc"
from x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
where 1=1
and a.indx=b.indx
and a.indx=c.indx
AND p.name(+) = a.ksppinm
AND SUBSTR(a.KSPPINM, 1, 1) = '_'
and a.ksppinm like '%_online_direct_load%'
order by 1;
name session instance default_value default_t_f sess_modi sys_modi desc
------------------------------ ---------- ---------- --------------- --------------- ---------- ---------- ----------------------------------------------------------------------
_online_direct_load 2 2 TRUE Online Direct Load
|
기본값이 2임
_online_direct_load 파라미터 값 0으로 변경
|
1
2
3
|
SQL> alter system set "_online_direct_load" = 0;
System altered.
|
파라미터 재확인
|
1
2
3
4
5
|
SQL> @hidden
name session instance default_value default_t_f sess_modi sys_modi desc
------------------------------ ---------- ---------- --------------- --------------- ---------- ---------- ----------------------------------------------------------------------
_online_direct_load 0 0 2 TRUE T I Online Direct Load
|
append 힌트 사용 테스트 재수행
샘플 테이블 생성(23ai)
|
1
2
3
|
SQL>
drop table emp2;
create table emp2 as select * from emp;
|
append 모드로 insert(23ai)
|
1
2
3
|
SQL> insert /*+ append */ into emp2 select * from emp2;
14 rows created.
|
정상적으로 수행됨
append 모드로 insert 재수행(23ai)
|
1
2
3
4
5
6
|
SQL> insert /*+ append */ into emp2 select * from emp2;
insert /*+ append */ into emp2 select * from emp2
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
Help: https://docs.oracle.com/error-help/db/ora-12838/
|
파라미터를 0으로 변경하자 에러 발생함
참고로 이 파라미터 값을 3 이나 99 등으로 올려보았지만 별다른 특이사항은 발견하지 못함, 2 일때와 동일하게 동작함
결론 :
오라클 23ai 버전부터 append 나 append_values 힌트를 사용한 insert 구문 실행 후
트랜잭션을 마무리(commit, rollback)하지 않은 상태로 해당 세션에서 다시한번 insert 를 수행해도 에러가 발생하지 않음
해당 세션에서 조회도 정상적으로 됨
19c 와 lock 을 비교했을때 두 버전 모두 lock은 동일하게 tm lock lmode 6와 tx lock lmode 6으로 잡힘
lock lmode 를 조정해서 이 기능을 구현한게 아닌 또 다른 메커니즘이 있는듯함
이 신기능으로 인해 append 를 사용하는 작업시 조금더 유연하게 작업이 가능할듯함
참조 :
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
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 12cR2 asm 환경 v$asm_disk 의 path 컬럼 값 (0) | 2024.07.17 |
|---|---|
| 오라클 23ai 신기능 load_method, load_type 힌트 (0) | 2024.07.05 |
| 오라클 23ai 신기능 xplan 의 Query Block Registry 개선 (0) | 2024.07.01 |
| 오라클 23ai 신기능 SQL Transpiler (0) | 2024.06.29 |
| 오라클 23ai 신기능 세미나 발표자료(202406) (0) | 2024.06.29 |