엑기스 | Oracle 12c Partition Online Operations

 

 

개요

오라클 파티셔닝은 테이블이나 인덱스를 물리적으로 분할하는 것을 의미한다. 오라클의 파티셔닝은 8i 처음 소개 되었고, 이후에 DB운영자는 테이블의 성격에 따라 적절한 파티셔닝을 통해 데이터를 효율적으로 관리함은 물론이고, DB의 성능까지도 향상시킬 수 있었다.

오라클은 새로운 버전이 출시할 때마다 파티셔닝 기능도 개선되었는데, 12c에서는 partition의 online move 기능이 추가됐다. 이는 자주 사용하는 새로운 데이터는 고성능 디스크에 저장한 후에 데이터 효용 주기가 지나면, 값싼 디스크로 이동시키는 작업을 매우 편리하게 지원한다.

이 글에서는 오라클 12c 이전 partition operation과 12c의 partition online operation 차이점에 관해 확인하고, 실무에서의 적용 가능성에 대해 확인해 보고자 한다.

 

 

Partition move의 동작원리

partition move의 작업은 partition에 대해 TM lock을 획득 후 진행되기 때문에, TM Lock의 Lock mode 호환성에 따라 다른 세션의 접근 가능 여부가 정해진다. 테스트를 통해 partition move의 동작 과정을 좀 더 자세히 확인해보자.

 

partition move의 동작 과정

 

 

Partition move 테스트

먼저 partition move 동작 과정 및 Lock을 획득하는 과정을 확인하기 위해 hr 유저에서 아래의 스크립트를 수행해보자.

 

테스트 테이블 생성

-- TEST의 편의를 위해 hr 계정에 DBA 권한 부여 후 진행 grant dba to hr ; <<Table이 저장 될 Tablespace 생성 >> CREATE TABLESPACE PT_TS1 DATAFILE '/app/oracle/oradata/pt_ts_01.dbf' size 100M; CREATE TABLESPACE PT_TS2 DATAFILE '/app/oracle/oradata/pt_ts_02.dbf' size 100M; <<Partition_Test 테이블 생성>> -- 태어난 날을 기준으로 Range Partition 생성 -- global index, local index 생성 Conn hr/hr CREATE TABLE PT1 ( ID NUMBER(10), BON_DATE DATE, GENDER VARCHAR2(10) ) PARTITION BY RANGE(BON_DATE) ( PARTITION P1 VALUES LESS THAN(TO_DATE('2000/01/01','YYYY/MM/DD')), PARTITION P2 VALUES LESS THAN (MAXVALUE) TABLESPACE PT_TS1 ); -- ID 칼럼으로 GLOBAL INDX 생성 -- DATE(태어난 날날) 칼럼으로 LOCAL INDEX 생성 CREATE INDEX PT1_GLOBAL ON PT1(ID) CREATE INDEX PT1_LOCAL ON PT1(BON_DATE) LOCAL -- ID 컬럼에 들어갈 Sequence 생성 CREATE SEQUENCE pt1_num START WITH 1 INCREMENT BY 1; INSERT INTO pt1 SELECT pt1_num.nextval ,       TO_DATE( 19800101 , 'YYYY-DD-MM' ) ,       decode( ROUND( dbms_random.value( 1 , 2 ) , 0 ) , 1 , 'female' , 2 , 'male') FROM dual CONNECT BY LEVEL <= 1000000 INSERT INTO pt1 SELECT pt1_num.nextval ,       TO_DATE( 19900101 , 'YYYY-DD-MM' ),       decode( ROUND( dbms_random.value( 1 , 2 ) , 0 ) , 1 , 'female' , 2 , 'male') FROM dual CONNECT BY LEVEL <= 1000000 INSERT INTO pt1 SELECT pt1_num.nextval ,       TO_DATE( 20100101 , 'YYYY-DD-MM' ),       decode( ROUND( dbms_random.value( 1 , 2 ) , 0 ) , 1 , 'female' , 2 , 'male') FROM dual CONNECT BY LEVEL <= 1000000 COMMIT;

 

테이블의 Lock 상태를 확인하는 스크립트

<<lock 관련 view join을 통한 Table lock 확인 Queary>> SELECT session_id ,       object_name ,       subobject_name ,       object_type ,       s.blocking_session ,       decode( s.command , 1 , 'Create table' , 2 , 'Insert' , 3 , 'Select' , 6 , 'Update' , 7 , 'Delete' , 9 , 'Create index' , 10 , 'Drop index' , 11 , 'Alter index' , 12 , 'Drop table' , 13 , 'Create seq' , 14 , 'Alter sequence' , 15 , 'Alter table' , 16 , 'Drop sequ.' , 17 , 'Grant' , 19 , 'Create syn.' , 20 , 'Drop synonym' , 21 , 'Create view' , 22 , 'Drop view' , 23 , 'Validate index' , 24 , 'Create procedure' , 25 , 'Alter procedure' , 26 , 'Lock table' , 42 , 'Alter session' , 44 , 'Commit' , 45 , 'Rollback' , 46 , 'Savepoint' , 47 , 'PL/SQL Exec' , 48 , 'Set Transaction' , 60 , 'Alter trigger' , 62 , 'Analyze Table' , 63 , 'Analyze index' , 71 , 'Create Snapshot Log' , 72 , 'Alter Snapshot Log' , 73 , 'Drop Snapshot Log' , 74 , 'Create Snapshot' , 75 , 'Alter Snapshot' , 76 , 'drop Snapshot' , 85 , 'Truncate table' , 0 , 'No command' , '? : ' ||s.command ) nocommand ,       s.blocking_session ,       decode( l.block , 0 , 'Not Blocking' , 1 , 'Blocking' , 2 , 'Global' ) status ,       decode( v.locked_mode , 0 , 'None' , 1 , 'Null' , 2 , 'Row-S (SS)' , 3 , 'Row-X (SX)' , 4 , 'Share' , 5 , 'S/Row-X (SSX)' , 6 , 'Exclusive' , TO_CHAR( lmode ) ) mode_held FROM gv$locked_object v ,       dba_objects d ,       gv$lock l ,       gv$session s WHERE v.object_id = d.object_id AND ( v.object_id = l.id1 ) AND v.session_id = s.sid AND owner = USER AND object_name = 'PT1' ORDER BY username ,       session_id ,       object_name ,       subobject_name ;

 

 

Partition move online option 미사용

12c부터 partition move시 online 옵션 지정이 가능해졌으며, 옵션에 따라 동작 방법은 달라진다. 먼저 online 옵션 제외 시 partition move의 동작 방법에 대해 확인해보자.

 

테스트 1 스크립트

-- 총 3개의 Session에서 명령어 실행을 통해 TEST 진행한다. <<Sesssion1 에서의 partition move >> ALTER TABLE PT1 MOVE PARTITION P1 TABLESPACE PT_TS2; -- partition move 작업이 진행 중인 상태에서 Session2에서 DML 작업을 실행한다. <<Sesssion2 에서의 DML 실행>> UPDATE pt1 SET GENDER= ‘male’ WHERE ID =1; <<Sesseion3 에서 Table Lock 확인>> -- Table Lock을 확인 하는 Script 실행 <<Sesssion2 Update 취소>> Rollback;

 

[그림 1] Session3에서의 Table Lock 상태 확인 및 스크립트 수행 결과

 

[그림 1]은 당시의 Table Lock mode 획득 상태를 조회한 결과다. 237번 세션에서 partition move를 수행하면, 대상 파티션 P1에 대해 Exclusive Lock을 획득하게 된다. 이때 23번 세션에서 파티션 P1에 대해 DML 작업을 수행하면, 해당 파티션에 대해 Sub-Exclusive Lock을 획득하려고 시도한다. 그러나 237번 세션이 획득한 Exclusive Lock과 Sub-Exclusive Lock은 서로 호환이 되지 않는다. 따라서 partition move 작업이 끝날 때까지 23번 세션은 대기해야 한다.

 

[그림 2] Lock mode 호환성

 

Index 상태 확인 스크립트

<<Sesssion1 에서에서Index 상태 확인 >> SELECT ui.index_name ,       uip.partition_name ,       NVL( uip.status , ui.status ) status FROM user_indexes ui ,       user_ind_partitions uip WHERE ui.table_name = 'PT1' AND ui.index_name = uip.index_name( + ) ORDER BY uip.partition_name ,       ui.index_name ;

 

[그림 3] partition move 후 INDEX 상태

 

[그림 3]은 partition move 후 Index 상태에 대해 조회한 결과다. partition move를 수행한 파티션 P1의 Local Index와 Global Index는 모두 Unusable 상태가 되었다. 만약 인덱스를 다시 사용하려면 Index Rebuild 작업을 수행해 주어야 한다.

 

 

Partition move online option 사용

이번에는 12c에서 추가된 online 옵션을 사용해 partition move 진행 후 동작 과정에 대해 확인해보자. 

 

테스트 2 스크립트

-- p2 partition move를 통해 p1 partition move와의 비교 <<Sesssion1 에서의 partition move >> ALTER TABLE PT1 MOVE PARTITION P2 TABLESPACE PT_TS2 online; -- partition move 작업이 진행 중인 상태에서 Session2에서 DML 작업을 실행한다. <<Sesssion2 에서의 DML 실행>> -- partition p2에 속한 data update 실행 UPDATE pt1 SET GENDER= ‘male’ WHERE ID = 2000001; <<Sesseion3 에서 Table Lock 확인>> -- Table Lock을 확인 하는 Script 실행 <<Sesssion2 Update 완료>> Commit;

 

[그림 4] Session3에서의 Table Lock 상태 확인 및 스크립트 수행 결과

 

[그림 4]는 당시의 Table Lock mode 획득 상태를 조회한 결과다. 세션 237에서 partition move를 수행하면, 대상 파티션인 P2에 대해 Sub-Exclusive Lock을 하게 된다. 이때 세션 23에서 P2 파티션에 Update 수행하면 Sub-Exclusive mode를 획득하려 한다. Sub-Exclusive 간의 Lock mode는 호환이 가능하다.

 

즉, 12c online 옵션의 추가로 move 대상 파티션의 Lock mode가 변경되었고, 이로 인해 move 작업 중 다른 세션의 DML 작업이 가능해졌다.

 

Index 상태 확인 스크립트

<<Sesssion1 에서에서Index 상태 확인 >> SELECT ui.index_name ,       uip.partition_name ,       NVL( uip.status , ui.status ) status FROM user_indexes ui ,       user_ind_partitions uip WHERE ui.table_name = 'PT1' AND ui.index_name = uip.index_name( + ) ORDER BY uip.partition_name ,       ui.index_name ;

 

[그림 5] online partition move 후 INDEX 상태

 

[그림 5]는 online partition move 이후 Index 상태를 조회한 결과다. 첫 번째 테스트에서는 online 옵션을 적용하지 않았기 때문에 Global Index와 PT1의 Local Index가 Unusable 상태가 되었다. 반면, online 옵션을 적용해 partition move를 진행한 P2 파티션의 Local Index는 Usable 상태로 바로 사용할 수 있다.

 

 

Partition move online의 장단점

지금까지 테스트를 통해 partition move시 online option 사용에 따라 동작 방식이 어떻게 달라지는지 알아보았다. 테스트를 통해 필자는 online operation이 가지는 장점 두 가지를 알 수 있었다.

 

첫째는 Lock mode가 Exclusive에서 Sub-Exclusive로 바뀌면서 move 작업 중인 파티션에도 DML 작업이 가능하다는 점이다. 둘째는 partition move 후에도 Index의 상태가 usable이라는 점이다. online option이 없었을 때는 partition move 작업 후에 Index들이 Unusable 상태가 됨으로써 Index Rebuild 해야만 했다. 이는 상당히 귀찮고 시간이 오래 걸리는 작업으로써, partition move를 online으로 할 수 없었던 가장 큰 이유였다.

 

이쯤 되면 partition move online option이 가지는 장점이 너무 커서 '무조건 online option을 사용해야 하는구나!' 생각하는 분들도 있을 것이다. 하지만 안타깝게도 테스트를 진행하는 동안 2가지 단점도 확인할 수 있었다.

 

가장 큰 문제는 다른 세션의 작업이 완료되지 않으면, partition move 작업이 끝나지 않는다는 것이다. 원인 확인을 위해서는 partition move online 작업 진행 과정에 대해 이해해야 한다. 오라클은 partition move online의 과정에서 3가지 temporary table이 생성된다.

 

1. SYS_JOURNAL

2. SYS_RMTAB_M
3. SYS_RMTAB_I

 

online move 작업 시 SYS_JOURNAL, SYS_RMTAB_M, SYS_RMTAB_I 순서로 Table이 생성된다. SYS_JOURNAL은 online Index rebuild 작업을, SYS_RMTAB_M은 파티션 데이터에 row_id를 삽입하는 작업을, 마지막으로 SYS_RMTAB_I는파티션데이터와 기존 데이터의 merge 작업을 담당하고 있다. 이 3가지 temporary table은 partition move 작업이 모두 완료되어야 drop 되게 된다. 그러나 작업 중 타 세션이 DML 작업 후 트랜잭션을 완료하지 않는다면 SYS_JOURNAL, SYS_RMTAB_M, SYS_RMTAB_I 테이블은 drop 되지 않게 되고 partition move 작업 역시 완료되지 않는다.

 

[그림 6] Session3에서의 Table Lock 상태 조회

 

[그림 6]은 partition move시 생성되는 Table의 Lock 상태를 조회한 결과다. 완료되지 않는 세션 23번에 의해 temporary table과 Table이 Blocking되 작업이 끝나지 않는다. 세션 23 트랜잭션 완료 시 Blocking이 해제되면서 partition move 작업이 완료되게 된다.

 

또한 SYS owner Table의 partition move online은 불가하다. 물론 DB 운영상 SYS 계정에서 Table 생성이나 운영은 권장하지 않는 사항이다. 특이하게도 SYS 계정에서 online option을 미사용하면 정상적으로 수행된다. partition move online 기능이 모든 사용자에게 적용되지 않는 것을 알 수 있다.

 

 

결론

지금까지 12c NF partition move operation에 대해 알아보았다. 오라클이 partition 기능에 online operation을 추가한 이유는 작업 시 불편한 점을 개선한 것으로 보인다. DBA들은 대량의 데이터 이동 및 압축 작업들이 있을 때, 서비스가 중단되는 PM이나 새벽에 서비스를 중단한 후 작업이 가능했다. 이는 Lock 발생 및 성능저하로 인해 서비스가 동작하지 않을 것을 우려해서다.

 

서비스를 일시 중단하는 것은 고객과의 협의, 타 부서와의 협의 등을 동반하므로 상당히 까다로운 업무다. 하지만 이러한 작업을 DB 리소스 점유가 적은 운영시간에 진행한다면, 관리자들의 고생을 줄일 수 있을 것이다. 그러나 기능 개선에 따른 Side effect 또한 확인할 수 있었다. 만약 트랜잭션이 많은 시간에 partition move를 online으로 수행한다면, 이 작업은 매우 오래 걸릴 것이란 점이다.

 

필자가 이번 테스트를 통해 느낀 것은 partition move online에 대한 동작 원리 및 절차에 대한 이해가 반드시 선행되어야 한다는 것이다. online 기능이 된다는 점에만 매료되어 가장 바쁜 시간대에 작업해도 된다 생각하는 우(愚)를 범하는 일이 없었으면 한다. NF 기능은 제대로 알고 잘 활용하면 약이 되지만, 제대로 이해하지 못하고 사용하는 경우에는 오히려 치명적인 문제가 당신을 기다리고 있을 수도 있다는 점을 기억하길 바란다.

 

 

 

 

 

 

 

참고자료

클라우드 데이터베이스 Oracle 12c 가이드

Oracle Database 12c: What's New in Partitioning?

Oracle Communities, What is SYS.RMTAB$ table?

 

 

 

 

 

기고 | 컨설팅본부 김규민

편집 | 사업기획팀 박예영

 

 

 

[Semtong 158회] 다른 이야기도 궁금하시다면?
🌱 엑셈 뉴스룸 | 엑셈, 내일채움공제 도입   ❗️ 엑셈 인사이트 | '관찰 가능성(Observability)'을 확보하는 싸이옵스의 IT 운영 전략  💡 혁신스토리 | 실리콘 밸리의 탄생  🔎 엑기스 | Oracle 12c Partition Online Operations  📁 IT뉴스 | 3월 테크뉴스 모음.zip  📺 경기도에서 화성(Mars)까지, 최근 주목받는 진화의 사례들  💻 엑쓸신잡 | 검색왕이 되기 위한 구글링 꿀팁  📰 엑셈 ‘클라우드모아’, 하이브리드·컨테이너 환경 가시성 확보에 최적  💪 이벤트 | 엑셈을 응원해봄!