SELECT, UPDATE 도중 파티션 exchange 변경 시 발생하는 현상 확인

OS환경 : Oracle Linux6.8(64bit)

DB 환경 : Oracle Database 11.2.0.4

쿼리 : 테이블 SELECT, UPDATE 도중 파티션 exchange 변경 시 발생하는 현상 확인

ALTER TABLE EXCHANGE PARTITION은 파티션 테이블을 일반 테이블로,

또는 파티션 되어있지 않은 테이블을 파티션 테이블로 변경시킬 때 사용함

테스트 시나리오

일반 테이블 생성

SQL> 
DROP TABLE TEST PURGE;
CREATE TABLE TEST(ID NUMBER, COL1 VARCHAR2(10), COL2 VARCHAR2(10));

파티션 테이블 생성

SQL> 
DROP TABLE PART_TEST PURGE;
CREATE TABLE PART_TEST
(ID NUMBER, COL1 VARCHAR2(10), COL2 VARCHAR2(10))
PARTITION BY RANGE(ID) 
  (PARTITION TEST_P1 VALUES LESS THAN (1000000),
   PARTITION TEST_P2 VALUES LESS THAN (2000000),
   PARTITION TEST_P3 VALUES LESS THAN (3000000)
);

 

첫번째 파티션에 들어갈 데이터 생성(999999건)

SQL> 
DECLARE
TYPE tbl_ins IS TABLE OF TEST%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..999999 LOOP 
   w_ins(i).ID   :=i;
   w_ins(i).COL1 :=300000;
   w_ins(i).COL2 :=99;
END LOOP;
   FORALL i in 1..999999 INSERT INTO TEST VALUES w_ins(i);
   COMMIT;
END;
/

데이터 확인

SQL> SELECT MAX(ID) FROM TEST;
 
   MAX(ID)
----------
   999999

통계정보 수집

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','TEST'); 
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT','PART_TEST'); 

SELECT 시 EXCHANGE 테스트

세션1

select.sh 파일 생성

$ vi select.sh
sqlplus "scott/tiger"<< EOF 
select * from TEST;
union
select * from TEST;
union
select * from TEST;
union
select * from TEST;
union
select * from TEST;
union
select * from TEST;
union
select * from TEST;
union
select * from TEST;
union
select * from TEST;
union
select * from TEST;
exit
EOF

실행 권한 부여

$ chmod +x select.sh

아래 while 문 실행과 동시에 세션 2, 3구문 실행

select 반복 쉘 실행

$ while true; do sleep 1; nohup ./select.sh; done

세션2

EXCHANGE 실행

SQL> 
ALTER TABLE PART_TEST EXCHANGE PARTITION TEST_P1
WITH TABLE TEST;

정상수행됨.

세션3

lock 모니터링

lock 없음

세션4

alert log

이상 없음

다음 테스트를 위해 테이블 초기화

EXCHANGE 실행

SQL> 
ALTER TABLE PART_TEST EXCHANGE PARTITION TEST_P1
WITH TABLE TEST;

UPDATE 시 EXCHANGE 테스트

세션1

update 구문 실행

SQL> update test set COL2 = 300000;

세션2

EXCHANGE 실행

SQL> ALTER TABLE PART_TEST EXCHANGE PARTITION TEST_P1
WITH TABLE TEST;
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

에러 발생함

세션3

lock 모니터링

set pages 1000
set lines 200
col object_name for a10 
col machine for a10
col TERMINAL for a20
col PROGRAM for a30
select distinct x.session_id,  a.serial#,d.object_name,a.machine,a.terminal,a.program, a.logon_time
from gv$locked_object x, gv$session a, dba_objects d
where x.session_id = a.sid
and x.object_id = d.object_id
order by logon_time;
 
SESSION_ID    SERIAL# OBJECT_NAM MACHINE    TERMINAL         PROGRAM            LOGON_TIM
---------- ---------- ---------- ---------- -------------------- ------------------------------ ---------
    55      151 TEST     orcldb   pts/2         sqlplus@orcldb (TNS V1-V3)    08-JAN-19

세션 하나 확인됨

세션4

alert log 확인

Tue Jan 08 16:22:23 2019
Thread 2 advanced to log sequence 220 (LGWR switch)
  Current log# 4 seq# 220 mem# 0: /dev/raw/raw15
Tue Jan 08 16:22:25 2019
Archived Log entry 487 added for thread 2 sequence 219 ID 0x2712c937 dest 1:
Tue Jan 08 16:22:41 2019
Thread 2 advanced to log sequence 221 (LGWR switch)
  Current log# 5 seq# 221 mem# 0: /dev/raw/raw16
Tue Jan 08 16:22:43 2019
Archived Log entry 488 added for thread 2 sequence 220 ID 0x2712c937 dest 1:

log switch 발생하는것 외에는 특이사항 없음

세션1에서 commit 후 바로 세션2 재실행 시 exchange됨

결론 : select 도중 exchange 시 특이사항 없음, update 도중 exchange 시 ORA-00054 발생

참조 : http://kosate.tistory.com/83

https://12bme.tistory.com/290