OS 환경 : Oracle Linux 8.4 (64bit)
DB 환경 : Oracle Database 23.5.0.24.07 ai for Oracle Cloud and Engineered Systems
방법 : 오라클 23ai 신기능 Parallel DML 에 대한 트랜잭션 제한 해제
오라클 23ai 버전부터 Parallel dml 후 dml 및 직렬, 병렬 dml에 대한 제약이 일부 해제됨
Parallel dml 후 해당 테이블을 직렬 또는 병렬로 조회하거나, 직렬 dml 이나 병렬 dml을 수행할 수 있음
이 기능은 append 힌트 제약 해제 신기능과 같이 _online_direct_load 파라미터로 제어됨
Parallel 과 Direct Load 제약이 같이 해제됨
참고 : 오라클 23ai 신기능 Direct Load(Direct Path Insert) 에 대한 제약 해제 ( https://positivemh.tistory.com/1127 )
사용 요건
- 대상 테이블이 힙 구조 테이블이어야함(기본값)
- 대상 테이블이 AUTO SEGMENT SPACE MANAGEMENT(ASSM) 와 AUTOALLOCATE 옵션을 사용하여 생성된 테이블스페이스에 존재해야함
- COMPATIBLE 파라미터가 23.0 이상으로 설정되어 있어야함
공식 문서상으로는 병렬쿼리도 가능한 기능이지만 free 버전에서는 병렬쿼리 테스트가 불가함
나의 경우 exa 버전을 설치하여 테스트가 가능한 상태임(2024년 9월기준)
본문에서는 19c 버전과 23ai 버전에서 parallel dml 에 대해서 테스트를 진행함
테스트
1. parallel 힌트 사용
2. parallel 힌트 사용 후 append 힌트 사용
3. 실행계획 확인
4. 파라미터
테스트
1. parallel 힌트 사용
샘플 테이블 생성(19c, 23ai 모두)
1
2
3
|
SQL>
drop table emp2;
create table emp2 as select * from emp;
|
parallel dml 기능 활성화
1
2
3
|
SQL> alter session enable parallel dml;
Session altered.
|
parallel 모드로 insert(19c)
1
2
3
|
SQL> insert /*+ parallel(emp2 2) */ into emp2 select * from emp2;
14 rows created.
|
정상적으로 수행됨
parallel 모드로 insert(23ai)
1
2
3
|
SQL> insert /*+ parallel(emp2 2) */ into emp2 select * from emp2;
14 rows created.
|
정상적으로 수행됨
insert 중인 테이블 조회(19c)
1
2
3
4
5
|
SQL> select count(*) from emp2;
select count(*) from emp2
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
|
처음 parallel insert 후 commit 하지 않고 테이블 조회시 에러가 발생함
insert 중인 테이블 조회(23ai)
1
2
3
4
5
|
SQL> select count(*) from emp2;
COUNT(*)
----------
28
|
정상적으로 조회됨
parallel 모드로 insert 재수행(19c)
1
2
3
4
5
|
SQL> insert /*+ parallel(emp2 2) */ into emp2 select * from emp2;
insert /*+ parallel(emp2 2) */ into emp2 select * from emp2
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
|
처음 parallel insert 후 commit 하지 않고 parallel insert 재수행시 에러가 발생함
parallel 모드로 insert 재수행(23ai)
1
2
3
|
SQL> insert /*+ parallel(emp2 2) */ into emp2 select * from emp2;
28 rows created.
|
정상적으로 insert 됨
parallel 힌트 없이 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
|
처음 parallel insert 후 commit 하지 않고 insert 재수행시 에러가 발생함
parallel 힌트 없이 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
27
28
29
30
31
32
|
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
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
784 TX 12910624 31493 6 0 0 197 32
1163 TM EMP2 69920 0 6 0 0 1 4384
1163 TX 12976141 31872 6 0 0 198 13
1165 TX 13369370 31789 6 0 0 204 26
23ai
SID TY OBJECT_NAME SUBOBJECT_NAME ID1 ID2 LMODE REQUEST BLOCK USN SLOT
------- -- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
30 TX 458777 779 6 0 0 7 25
142 TX 262170 801 6 0 0 4 26
264 TX 524307 801 6 0 0 8 19
394 TM EMP2 29721 0 6 0 0 0 29721
394 TX 196628 780 6 0 0 3 20
404 TX 131094 792 6 0 0 2 22
|
두 버전 모두 lock은 동일하게 tm lock lmode 6와 tx lock lmode 6으로 잡힘
v$px_session 확인(19c, 23ai 모두)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL> select * from v$px_session;
19c
SADDR SID SERIAL# QCSID QCSERIAL# QCINST_ID SERVER_GROUP SERVER_SET SERVER# DEGREE REQ_DEGREE CON_ID
---------------- ------- ---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ----------
00000000804B8660 784 63818 1163 29383 1 1 1 1 2 2 0
000000008086F690 1165 7753 1163 29383 1 1 1 2 2 2 0
00000000819A84D0 20 19313 1163 29383 1 1 2 1 2 2 0
00000000800FEE48 402 25725 1163 29383 1 1 2 2 2 2 0
000000008086A6C0 1163 29383 1163 0
23ai
SADDR SID SERIAL# QCSID QCSERIAL# QCINST_ID SERVER_GROUP SERVER_SET SERVER# DEGREE REQ_DEGREE CON_ID
---------------- ------- ---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ----------
0000000081CDBDA0 404 47080 394 4615 1 1 1 1 2 2 3
00000000818B95F0 30 16579 394 4615 1 1 1 2 2 2 3
00000000819F6600 142 15971 394 4615 1 2 1 1 2 2 3
0000000081B4FA90 264 18775 394 4615 1 2 1 2 2 2 3
0000000081CBF920 394 4615 394 3
|
px_session 에서 다른점은 19c 는 server_group 가 모두 1이고 server_set 이 1,1,2,2 인 반면 23ai는 server_group 이 1,1,2,2 이고 server_set 이 모두 1임
다음 테스트를 위해 모두 롤백
1
2
3
|
SQL> rollback;
Rollback complete.
|
update 확인
1
2
3
|
SQL> update /*+ parallel(emp2 2) */ emp2 set empno=9999;
14 rows updated.
|
19c, 23ai 모두 첫번쨰는 잘 수행됨
update 한번 더 수행
19c
1
2
3
4
5
|
SQL> update /*+ parallel(emp2 2) */ emp2 set empno=9999;
update /*+ parallel(emp2 2) */ emp2 set empno=9999
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
|
에러가 발생함
23ai
1
2
3
|
SQL> update /*+ parallel(emp2 2) */ emp2 set empno=9999;
14 rows updated.
|
에러 없이 정상적으로 수행됨
다음 테스트를 위해 모두 롤백
1
2
3
|
SQL> rollback;
Rollback complete.
|
delete 확인
emp2 테이블의 절반을 제거
1
2
3
4
|
SQL>
delete /*+ parallel(emp2 2) */ from emp2 where rownum <= (select count(*) * 0.5 from emp2);
7 rows deleted.
|
19c, 23ai 모두 첫번쨰는 잘 수행됨
delete 한번 더 수행
19c
1
2
3
4
5
|
SQL> delete /*+ parallel(emp2 2) */ from emp2 where rownum <= (select count(*) * 0.5 from emp2);
delete /*+ parallel(emp2 2) */ from emp2 where rownum <= (select count(*) * 0.5 from emp2)
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
|
에러가 발생함
23ai
1
2
3
|
SQL> delete /*+ parallel(emp2 2) */ from emp2 where rownum <= (select count(*) * 0.5 from emp2);
3 rows deleted.
|
에러 없이 정상적으로 수행됨
다음 테스트를 위해 모두 롤백
1
2
3
|
SQL> rollback;
Rollback complete.
|
2. parallel 힌트 사용 후 append 힌트 사용
parallel insert 진행
1
2
3
|
SQL> insert /*+ parallel(emp2 2) */ into emp2 select * from emp2;
14 rows created.
|
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
|
에러가 발생함
23ai
1
2
3
|
SQL> insert /*+ append */ into emp2 select * from emp2;
28 rows created.
|
에러 없이 정상적으로 수행됨
다음 테스트를 위해 모두 롤백
1
2
3
|
SQL> rollback;
Rollback complete.
|
3. 실행계획 확인
parallel insert 2번 수행(23ai)
1
2
3
4
5
6
|
SQL> insert /*+ gather_plan_statistics parallel(emp2 2) */ into emp2 select * from emp2;
14 rows created.
SQL> insert /*+ gather_plan_statistics parallel(emp2 2) */ 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
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
|
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID avhkwnkz6q5gf, child number 0
-------------------------------------
insert /*+ gather_plan_statistics parallel(emp2 2) */ into emp2 select
* from emp2
Plan hash value: 1443397865
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | TQ |IN-OUT| PQ Distrib | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | | 4 (100)| | | | | 2 |00:00:00.02 | 25 | | | |
| 1 | PX COORDINATOR | | 1 | | | | | | | | 2 |00:00:00.02 | 25 | 73728 | 73728 | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 0 | 14 | 532 | 4 (0)| 00:00:01 | Q1,01 | P->S | QC (RAND) | 0 |00:00:00.01 | 0 | | | |
| 3 | LOAD AS SELECT (HYBRID TSM/HWMB)| EMP2 | 0 | | | | | Q1,01 | PCWP | | 0 |00:00:00.01 | 0 | 1043K| 1043K| 1043K (0)|
| 4 | OPTIMIZER STATISTICS GATHERING | | 0 | 14 | 532 | 4 (0)| 00:00:01 | Q1,01 | PCWP | | 0 |00:00:00.01 | 0 | 256K| 256K| 1024K (0)|
| 5 | PX RECEIVE | | 0 | 14 | 532 | 4 (0)| 00:00:01 | Q1,01 | PCWP | | 0 |00:00:00.01 | 0 | | | |
| 6 | PX SEND ROUND-ROBIN | :TQ10000 | 0 | 14 | 532 | 4 (0)| 00:00:01 | Q1,00 | S->P | RND-ROBIN | 0 |00:00:00.01 | 0 | | | |
| 7 | PX SELECTOR | | 0 | | | | | Q1,00 | SCWC | | 0 |00:00:00.01 | 0 | | | |
| 8 | TABLE ACCESS FULL | EMP2 | 0 | 14 | 532 | 4 (0)| 00:00:01 | Q1,00 | SCWP | | 0 |00:00:00.01 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
8 - 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')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"INS$1")
PQ_DISTRIBUTE(@"INS$1" "EMP2"@"INS$1" RANDOM)
LOAD_METHOD(@"INS$1" "EMP2"@"INS$1" HYBRID_TSM_HWMB)
LOAD_TYPE(@"INS$1" "EMP2"@"INS$1" PARALLEL)
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 - (#keys=0) SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[120], SYSDEF[32720]
3 - SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[120], SYSDEF[32720]
4 - ["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]
5 - "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]
6 - (#keys=0) "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]
7 - "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]
8 - "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"
- parallel(emp2 2)
Note
-----
- Degree of Parallelism is 2 because of table property
Query Block Registry:
---------------------
INS$1 (PARSER) [FINAL]
SEL$1 (PARSER) [FINAL]
83 rows selected.
|
2번째 insert 시에도 parallel 이 잘 동작하고 있음
Outline Data 에 LOAD_METHOD 와 LOAD_TYPE 이라는 새로운 힌트가 보임
참고 : 오라클 23ai load_method load_type 힌트 ( https://positivemh.tistory.com/1128 )
4. 파라미터
이 기능은 _online_direct_load 히든 파라미터로 제어됨
이 파라미터를 0으로 변경하면 기능이 이전 버전처럼 동작함(parallel insert 후 재 parallel 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
|
parallel 힌트 사용 테스트 재수행
샘플 테이블 생성(23ai)
1
2
3
4
|
SQL>
conn imsi/imsi
drop table emp2;
create table emp2 as select * from emp;
|
parallel dml 기능 활성화
1
2
3
|
SQL> alter session enable parallel dml;
Session altered.
|
parallel insert 수행(23ai)
1
2
3
|
SQL> insert /*+ parallel(emp2 2) */ into emp2 select * from emp2;
14 rows created.
|
정상적으로 수행됨
parallel insert 재수행(23ai)
1
2
3
4
5
6
|
SQL> insert /*+ parallel(emp2 2) */ into emp2 select * from emp2;
insert /*+ parallel(emp2 2) */ 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/
|
파라미터 변경시 에러 발생함
결론 :
오라클 23ai 버전부터 parallel 힌트를 사용한 dml 구문 실행 후
트랜잭션을 마무리(commit, rollback)를 하지 않은 상태로 해당 세션에서 다시한번 일반 dml, 병렬 dml, 조회, append insert 를 사용해도 에러가 발생하지 않음
19c 와 lock 을 비교했을때 두 버전 모두 lock은 동일하게 tm lock lmode 6와 tx lock lmode 6으로 잡힘
lock lmode 를 조정해서 이 기능을 구현한게 아닌 또 다른 메커니즘이 있는듯함
이 신기능으로 인해 parallel 및 append 를 사용하는 작업시 조금더 유연하게 작업이 가능할듯함
참조 :
https://positivemh.tistory.com/1163
https://apex.oracle.com/pls/apex/features/r/dbfeatures/features?feature_id=1749
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://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_ID:228657900346252297
https://tuna.tistory.com/102
https://oracle-randolf.blogspot.com/2023/08/oracle-23c-free-unrestricted-direct.html
https://positivemh.tistory.com/808
https://positivemh.tistory.com/881
https://positivemh.tistory.com/1127
https://positivemh.tistory.com/1128
https://positivemh.tistory.com/1129
'ORACLE > Admin' 카테고리의 다른 글
오라클 19c sql 취소 기능(alter system cancel sql) (0) | 2024.11.14 |
---|---|
오라클 23ai 신기능 스테이징 테이블(Staging) (0) | 2024.11.08 |
오라클 23ai 신기능 스키마 레벨 권한 부여 (0) | 2024.11.05 |
오라클 23ai 신기능 sql 이력 확인뷰 v$sql_history (0) | 2024.10.31 |
오라클 23ai 신기능 bigfile 테이블스페이스 shrink (0) | 2024.10.28 |