[2025-09-04] 특정 서브 파티션에 대해서 parallel dml 시 다른 서브 파티션까지 TM락이 걸리는지 확인 (12cR2에서 테스트)
[실습 환경]
OS :
Oracle Linux Server 7.9 (Linux 5.4.17-2102.201.3.el7uek.x86_64)
DB : Oracle
Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit
Production
(Database Jan 2022 Release Update :
12.2.0.1.220118 (33587128))
RAC1 : rdb01p(192.168.0.11) : PRDB1
RAC2 :
rdb02p(192.168.0.12) : PRDB2
PDML + 파티션
프루닝이 걸리면, 해당되는 서브파티션에만 TM 락이 걸린다.
다른 서브파티션은 락 영향이 없어서, 동시에 직렬 DML 수행이
가능하다.
즉, 병렬 DML이라도 프루닝 범위 안의 서브파티션만 잠그고, 나머지는 정상 처리된다.
[실습 진행]
1. 실습 테이블
생성
CREATE TABLE tb_ord_dtl_test
(
ord_dt VARCHAR2(8) NOT NULL,
item_cd
VARCHAR2(1) NOT NULL,
qty
NUMBER,
amt
NUMBER
)
PARTITION BY RANGE (ord_dt)
SUBPARTITION BY LIST
(item_cd)
SUBPARTITION TEMPLATE
(
SUBPARTITION
sp_item_a VALUES ('A'),
SUBPARTITION sp_item_b VALUES
('B')
)
(
PARTITION p2020 VALUES LESS THAN
('20210101'),
PARTITION p2021 VALUES LESS THAN
('20220101'),
PARTITION p2022 VALUES LESS THAN
('20230101'),
PARTITION p2023 VALUES LESS THAN
('20240101'),
PARTITION pmax VALUES LESS THAN
(MAXVALUE)
);
ALTER
SESSION ENABLE PARALLEL DML;
ALTER TABLE tb_ord_dtl_test
NOLOGGING;
INSERT /*+
APPEND PARALLEL(t,4) */ INTO tb_ord_dtl_test t
SELECT
TO_CHAR(DATE
'2020-01-01' + MOD(lv-1, 366*5), 'YYYYMMDD') AS ord_dt,
CASE WHEN
MOD(lv,2)=0 THEN 'A' ELSE 'B' END AS
item_cd,
MOD(lv*lvv, 100) + 1 AS qty,
MOD(lv*lvv, 10000) + 100 AS
amt
FROM
(SELECT LEVEL AS lv FROM dual CONNECT BY LEVEL <=
1000000)
, (SELECT LEVEL AS lvv FROM dual CONNECT BY LEVEL <=
5);
--rollback;
COMMIT;
;
2. 세션 하나 열어서
explain
plan SET STATEMENT_ID = 'UPD_A_2020' for
update /*+ enable_parallel_dml
parallel(t 2) */ tb_ord_dtl_test t
set amt = 9999999
where ord_dt like
'2020%'
and item_cd = 'A';
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY(
'PLAN_TABLE', 'UPD_A_2020',
'BASIC +PREDICATE +PARTITION +PARALLEL +NOTE
+ALIAS'
));
PLAN_TABLE_OUTPUT
Plan hash value:
1763998619
----------------------------------------------------------------------------------------------
|
Id |
Operation
| Name |
Pstart| Pstop | TQ |IN-OUT| PQ Distrib
|
----------------------------------------------------------------------------------------------
|
0 | UPDATE STATEMENT
|
| |
| |
|
|
| 1 | PX COORDINATOR
|
| |
| |
|
|
| 2 | PX SEND QC (RANDOM) |
:TQ10000
| |
| Q1,00 | P->S | QC (RAND) |
| 3
|
UPDATE |
TB_ORD_DTL_TEST |
| | Q1,00 | PCWP
|
|
| 4 | PX BLOCK ITERATOR
|
| 1 | 1 | Q1,00 | PCWC
|
|
|* 5 | TABLE ACCESS FULL|
TB_ORD_DTL_TEST | 1 | 1 |
Q1,00 | PCWP |
|
----------------------------------------------------------------------------------------------
Query
Block Name / Object Alias (identified by operation
id):
-------------------------------------------------------------
1 - UPD$1
5 - UPD$1 / T@UPD$1
Predicate Information (identified by operation
id):
---------------------------------------------------
5 - filter("ORD_DT" LIKE '2020%')
Note
-----
-
Degree of Parallelism is 2 because of table property
update /*+
enable_parallel_dml parallel(t 2) */ tb_ord_dtl_test t
set amt = 9999999
where ord_dt like '2020%'
and item_cd = 'A';
--> 2020년의 A 상품 서브 파티션에 대해서 parallel update dml을
실행
--> 돌리기만 하고 커밋이나 롤백을 하지 않음
--서브 파티션 락이 걸리는걸 확인
SELECT 1
, LO.INST_ID
, LO.SESSION_ID
,
LO.ORACLE_USERNAME
,
LO.OS_USER_NAME
,
LO.PROCESS
, DECODE(LO.LOCKED_MODE,0, 'NONE',1,
'NULL',2, 'ROW SHARE (SS)', 3, 'ROW EXCL (SX)',4, 'SHARE',5, 'SHARE ROW EXCL
(SSX)',6, 'EXCLUSIVE', TO_CHAR(LO.LOCKED_MODE))
MODE_HELD
, DO.OWNER
, DO.OBJECT_TYPE
,
DO.OBJECT_NAME
, DO.OBJECT_ID
,
DO.STATUS
FROM GV$LOCKED_OBJECT LO, DBA_OBJECTS DO
WHERE LO.OBJECT_ID =
DO.OBJECT_ID
ORDER BY LO.INST_ID,
MODE_HELD
;
INST_ID SESSION_ID
ORACLE_USERNAME OS_USER_NAME PROCESS
MODE_HELD OWNER
OBJECT_TYPE
OBJECT_NAME OBJECT_ID
STATUS
------- ---------- ---------------
------------ ---------- -------------- -----
----------------- ---------------- ---------
------
1
736
TUNER
leeko 33248:6704
EXCLUSIVE TUNER TABLE SUBPARTITION
TB_ORD_DTL_TEST 246815
VALID
1
740
TUNER
leeko
5403
NULL
TUNER TABLE SUBPARTITION TB_ORD_DTL_TEST
246815
VALID
1
1100
TUNER
leeko
5405
NULL
TUNER TABLE SUBPARTITION TB_ORD_DTL_TEST
246815
VALID
1
1100
TUNER
leeko
5405 ROW EXCL (SX)
TUNER
TABLE
TB_ORD_DTL_TEST 246809
VALID
1
736
TUNER
leeko 33248:6704 ROW EXCL
(SX) TUNER
TABLE
TB_ORD_DTL_TEST 246809
VALID
1
740
TUNER
leeko
5403 ROW EXCL (SX)
TUNER
TABLE
TB_ORD_DTL_TEST 246809
VALID
1
1100
TUNER
leeko
5405
SHARE TUNER
TABLE SUBPARTITION TB_ORD_DTL_TEST 246815
VALID
1
740
TUNER
leeko
5403
SHARE TUNER
TABLE SUBPARTITION TB_ORD_DTL_TEST 246815
VALID
3. 다른
세션하나 더 열어서
explain
plan SET STATEMENT_ID = 'UPD_B_2020' for
update tb_ord_dtl_test t
set amt
= 9999999
where ord_dt like '2020%'
and item_cd = 'B';
SELECT *
FROM
TABLE(DBMS_XPLAN.DISPLAY(
'PLAN_TABLE', 'UPD_B_2020',
'BASIC +PREDICATE +PARTITION +PARALLEL +NOTE
+ALIAS'
));
Plan hash value:
808400091
-------------------------------------------------------------------
|
Id |
Operation
| Name |
Pstart| Pstop
|
-------------------------------------------------------------------
|
0 | UPDATE STATEMENT
|
| |
|
| 1 |
UPDATE
| TB_ORD_DTL_TEST |
| |
| 2 |
PARTITION RANGE
SINGLE|
| 1 | 1 |
| 3
| PARTITION LIST
SINGLE|
| 2 | 2 |
|* 4
| TABLE ACCESS FULL | TB_ORD_DTL_TEST
| 2 | 2
|
-------------------------------------------------------------------
Query
Block Name / Object Alias (identified by operation
id):
-------------------------------------------------------------
1 - UPD$1
4 - UPD$1 / T@UPD$1
Predicate Information
(identified by operation
id):
---------------------------------------------------
4 - filter("ORD_DT" LIKE '2020%')
update
tb_ord_dtl_test t
set amt = 9999999
where ord_dt like '2020%'
and
item_cd = 'B';
500505 rows Updated.
SQL Execution Time
> 00:00:00.438
-->잘됨
4. 2번에서 연 세션에서 다시 가서
update /*+ enable_parallel_dml parallel(t 2)
*/ tb_ord_dtl_test t
set amt = 9999999
where ord_dt like '2020%'
and
item_cd = 'A';
--> 이거 rollback; 시킴
rollback;
5.
결론
parallel
dml시 파티션 푸루닝이 제대로 된다면 해당 파티션만 TM락을 잡게되고
다른 파티션은 정상적으로 dml이 이루어짐