OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.10.0.0
방법 : 오라클 19c 파티션 테이블 append insert 시 lock 확인
만약 파티션 테이블에 일반 테이블의 데이터를 통으로 밀어 넣어야할 때
일반 insert 로 데이터를 넣게 되면 속도가 느림
이때 append 힌트를 사용해 insert 를 하면 빠른데 일반테이블에 append 힌트를 사용하여 insert를 하게되면
해당 테이블에 lock이 걸려 다른 트랜젝션에서 dml을 할수 없음
파티션 테이블의 경우 해당 파티션에만 lock이 걸려 다른 파티션의 dml에 영향을 주지 않고
해당 파티션만 사용하지 못하는 상태에서 append 힌트를 이용해 빠른 insert가 가능함
테스트를 통해 확인해봄
세션 정보 확인
|
1
2
3
4
5
|
SQL> select sid from v$mystat where rownum<=1;
SID
----------
300
|
파티션 테이블 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
SQL>
drop table ptable purge;
create table ptable
(
col1 varchar2(8),
col2 number
)
tablespace users
partition by range (col1)
(
partition p202101 values less than ('202102'),
partition p202102 values less than ('202103'),
partition p202103 values less than ('202104'),
partition p202104 values less than ('202105'),
partition p202105 values less than ('202106'),
partition p202106 values less than ('202107'),
partition p202107 values less than ('202108'),
partition p202108 values less than ('202109'),
partition p202109 values less than ('202110'),
partition p202110 values less than ('202111'),
partition p202111 values less than ('202112'),
partition p202112 values less than ('202113'),
partition p_max values less than (maxvalue)
);
|
파티션 테이블 샘플 데이터 삽입
|
1
2
3
4
5
6
7
8
|
SQL>
insert into PTABLE values ('202101',1);
insert into PTABLE values ('202102',1);
insert into PTABLE values ('202102',1);
insert into PTABLE values ('202103',1);
insert into PTABLE values ('202104',1);
insert into PTABLE values ('202105',1);
commit;
|
동일구조 일반 샘플 테이블 생성
|
1
2
3
4
5
6
7
8
|
SQL>
drop table ntable purge;
create table ntable
(
col1 varchar2(8),
col2 number
)
tablespace users;
|
샘플 데이터 삽입(NTABLE 에는 모두 202101 데이터만 들어있음)
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
insert into NTABLE values ('202101',1);
insert into NTABLE values ('202101',2);
insert into NTABLE values ('202101',3);
insert into NTABLE values ('202101',4);
insert into NTABLE values ('202101',5);
insert into NTABLE values ('202101',6);
insert into NTABLE values ('202101',7);
insert into NTABLE values ('202101',8);
insert into NTABLE values ('202101',9);
insert into NTABLE values ('202101',10);
commit;
|
세션레벨 파라미터 변경
|
1
2
3
4
5
6
7
8
9
|
SQL>
alter session enable parallel dml;
--선택사항--
alter session set workarea_size_policy = manual;
alter session set sort_area_size = 2000000000;
show parameter workarea_size_policy
show parameter sort_area_size
--선택사항--
|
append parallel 힌트 사용하여 특정 파티션으로 insert
|
1
2
3
|
SQL>
insert /*+ append parallel(b 2) */ into ptable partition (p202101) b
select /*+ full(a) parallel(a 2) */ * from ntable a;
|
lock 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL>
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and sid = 300
--and TYPE in ('TX','TM')
order by sid
;
SID TY ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- -- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
300 AE 134 0 4 0 0 0 134 0
300 TM 23784 0 3 0 0 0 23784 0
300 TM 23785 0 6 0 0 0 23785 0
300 TX 393229 530 6 0 0 6 13 530
|
id1 object id 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
col object_name for a15
col subobject_name for a15
select object_id, object_name, subobject_name
from dba_objects
where object_id in (23784, 23785, 393229);
OBJECT_ID OBJECT_NAME SUBOBJECT_NAME
---------- --------------- ---------------
23784 PTABLE
23785 PTABLE P202101
|
23784 => PTABLE : TM Lock mode 3(row-X SX)
23785 => PTABLE P202101 파티션 : TM Lock mode 6(exclusive X)
해당 파티션에만 lock 이 걸림
다른 세션 dml확인
먼저 다른 파티션에 insert
|
1
2
3
4
|
SQL>
insert into PTABLE values ('202105',11);
1 row created.
|
다른 파티션에 update
|
1
2
3
4
5
|
SQL>
update PTABLE set col2 = 12
where col1 = '202105' and col2 = 11;
1 row updated.
|
다른 파티션에 delete
|
1
2
3
4
5
|
SQL>
delete PTABLE
where col1 = '202105' and col2 = 12;
1 row deleted.
|
다른 파티션에 대해서 dml들이 모두 동작함
append 중인 파티션에 insert
|
1
2
3
|
SQL>
insert into PTABLE values ('202101',11);
(lock 대기)
|
append 중인 파티션에 insert 시 lock로 대기함
append 세션에서 commit 이후 이 세션에서 insert 성공함
DDL 테스트
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
DDL 테스트
drop table => ORA-00054 발생
SQL> drop table ptable;
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
truncate partition => ORA-00054 발생
SQL> alter table ptable truncate partition p202101;
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
split partition => 가능
SQL> alter table ptable split partition p_max at ('202305') into (partition p202206, partition p_max);
Table altered.
|
결론 :
append 힌트를 이용해 특정 파티션에 데이터 insert 시
해당 파티션에만 lock이 걸려 다른 파티션의 dml에 영향을 주지 않고
(해당 파티션만 사용하지 못하는 상태에서) 빠른 insert가 가능함
참조 :
https://positivemh.tistory.com/842
오라클 11gR2 파티션 변경(split, drop, add) 시 커서 상태 변화 확인
OS환경 : Oracle Linux 6.8 (64bit) DB 환경 : Oracle Database 11.2.0.4 방법 : 오라클 11gR2 파티션 변경(split, drop, add) 시 실행계획 변화 확인(실행계획 변경) 오라클 11gR2에서 파티션 테이블에 변경(split, drop, add)
positivemh.tistory.com
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c ASM usable_file_mb -(음수) 됬을때 조치방법 (0) | 2023.05.23 |
|---|---|
| 오라클 19c 파티션 Exchange 방법 (0) | 2023.05.03 |
| 오라클 19c ORA-04036 에러를 발생시키는 방법 (0) | 2023.04.30 |
| 오라클 19c 파티션 테이블 멀티 truncate 테스트 (0) | 2023.03.06 |
| 오라클 19c Memoptimize pool 테스트(memoptimize for read) (0) | 2023.02.14 |