[Oracle 공식입장] DB 형상 변경과 업무의 상관관계

DB 형상 변경과 관련한 업무 영향도.

※ 형상 변경이란 DDL(Create,Alter,Drop)로서 DB Object(테이블,파티션,인덱스,시퀀스,Procedure...)에 대한 구조 변경 SQL을 의미

 

본 내용에서는 테이블/인덱스에 대한 변경이 업무에 미치는 영향에 한정하여 설명합니다.

 

오라클은 데이타의 정합성, 일관성과 Structure 보호를 위해 내부적으로 "Lock"이라는 기술을 사용하며, Lock은 크게 3가지로 나뉘닙다.

1. DML Locks : 데이타 보호                   => row locks

               ex) INSERT,UPDATE,DELETE,MERGE,SELECT ... FOR UPDATE,LOCK TABLE

2. DDL Locks : 유저 Objects의 Structure 보호 => table, view, sql area

               ex) CREATE ..., DROP ..., ALTER ...

3. System Locks : Internal DB Structure 보호 => latch, mutex

 

이중에서 DDL Lock이 업무에 미치는 영향은 다음과 같습니다.

첫째, DDL 명령 실행 단계로 명령문 수행이 실패하는 경우입니다.

      대부분의 DDL 문장은 Lock 획득을 위해서 관련된 Object에 대해서 배타적인 Lock을 요구합니다.

      이 얘기는 트랜잭션으로 인한 DML Lock이 없어야 되며, 트랜잭션이 지속적인 상황에서는

      DDL 문장이 수행되지 못하고 아래와 같은 오류가 발생합니다.

      ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

      이를 위한 대응 방안으로 오라클은 "ddl_lock_timeout"이라는 파라미터로 일정시간(0~1,000,000초)동안

      내부적으로 계속 DDL 수행을 시도할 수 있습니다.

      ex) alter session set DDL_LOCK_TIMEOUT=30;

      따라서 빈번한 트랜잭션이 발생하는 DB에서는 Lock 획득 실패 가능성이 있으며, 이럴경우에는 

      DDL 문장 수행을 위해서 일시적인 업무 중지등 별도의 Downtime(PM)이 필요합니다.

 

둘째, DDL 명령이 수행되는 동안의 Lock 발생입니다.

      통상적인 경우에는 DDL 문장이 짧지만 일부 상황에서는 장시간 소요될 수 있습니다.

      이런경우 명령이 완료될때까지 관련된 Object를 참조하는 모든 트랜잭션이 Lock될 수 있습니다. ==> Hang

      ex) alter table T (add col_x varchar2(100) default 'a');   ==> default 값 지정

          create index IND1 on tab1 (col1, col2, col3);          ==> index NON online

          alter table T drop partition T1 update global indexes; ==> update index

      따라서 장애 예방을 위해서는 동일한 Case를 개발 시스템에서 테스트하여 소요 시간 및 

      영향도를 파악하여 PM 수립후 진행할 필요가 있습니다.

 

셋째, DDL 명령이 완료된 이후의 트랜잭션 Wait 현상입니다.

      DDL 명령 수행시 관련 Object를 참조하는 SQL Area의 LCO(Library Cache Object)에 대해서

      invalid 상태로 변경되고, 모든 SQL 수행은 Hard Parsing을 필요로 할 수 있습니다.

      SQL 수행시 파싱 단계에서 해당 SQL에 대한 LCO 획득이 필요하며, 빈번하게 사용되는 SQL의 경우

      Library Cache와 관련된 대기 Event를 장시간 대기할 수 있으며, 심각한 경우에는 DB Restart가 필요할 수 있습니다.

      ex) library cache pin, library cache lock, library cache load lock

      따라서 트랜잭션이 많은 경우에는 Parsing을 유발할 수 있는 DDL 사용 자제를 권고합니다.

 

결론적으로

모든 DDL 문장은 다양한 위험 요소를 내포하고 있으므로, 유사한 개발/테스트 환경에서 충분한 검증을 실시하고,

가급적이면 PM 시간을 확보하여 수행하시기를 권고합니다.

만약 PM이 불가능한 상황이라면 트랜잭션이 최소인 시간을 선택하여 수행하시되 위에 언급된 상황에 대비한

Contingency Plan이 필요합니다.

From. 오라클 공식입장 박기종 부장