토요일 오후 영업 중에 돌리겠다는 변경 작업 계획서 다섯 줄을 검토해 달라고 한다

실전DDL유통544 / 700

상황

본사 레거시 유통 전산 담당자가 다음 주 토요일 14:00에 돌릴 변경 작업 계획서를 보내왔다. 매장 여섯 곳은 그 시간에 영업 중이고, 토요일 오후는 판매가 가장 많은 때다. 새벽 작업은 외주 DBA 일정이 맞지 않아 두 달 뒤에나 된다고 한다. 담당자의 메모는 이렇다. "다섯 줄 다 개발 DB에서 1초 안에 끝났습니다. 영업 중이어도 괜찮겠죠?"

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO) · SHP_NO는 지금 VARCHAR2(5) · POS가 판매마다 INSERT한다
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO)(리프 7,850블록) · POS가 판매마다 1 ~ 3행 INSERT하고, 반품이면 UPDATE한다
CF15 입출고상품: 51,497행 · 349블록 · PK CF15_PK (INOUT_NO, ITM_NO) · 물류센터 화면이 입출고마다 INSERT한다 · 지난달 2년 전 입출고 3만 행을 지워 블록 절반이 비었다
CF11에는 SLE_NO가 CF10에 없는 행이 하나도 없다(지난주 확인)
토요일 오후 판매는 매장 여섯 곳을 합쳐 분당 40건 안팎이다 · 운영 DB는 19c EE · DDL_LOCK_TIMEOUT 기본값 0

원본 SQL

sql

-- 변경 작업 계획서 · 토요일 14:00 · 위에서부터 차례로
-- ① 매장 번호 체계 확장 대비 — 칸 넓히기
ALTER TABLE CF10 MODIFY (SHP_NO VARCHAR2(10));
-- ② MD 「매장별 일 매출」 화면용 인덱스
CREATE INDEX CF10_X01 ON CF10 (SHP_NO, SLE_DT);
-- ③ 판매상품 → 판매 FK (감사 지적)
ALTER TABLE CF11 ADD CONSTRAINT CF11_FK_SLE FOREIGN KEY (SLE_NO) REFERENCES CF10 (SLE_NO);
-- ④ 지난달 지운 입출고 공간 회수
ALTER TABLE CF15 MOVE;
-- ⑤ 정기 인덱스 리빌드 (분기마다)
ALTER INDEX CF11_PK REBUILD;

실행계획 학습용 예시 · ONLINE 없는 CREATE INDEX의 TM 4는 교재 12화 실측(실습/05 [D4])

xplan

[개발 DB 리허설 — 운영의 1/50 크기 · 아무도 쓰지 않는 DB]
  ①  0.02초    ②  0.14초    ③  0.31초    ④  0.02초    ⑤  0.19초

[운영 크기로 만든 사본에서 혼자 다시 잼 (학습용 예시)]
줄   걸린 시간   그동안 쥐는 락                         그동안 같은 테이블의 DML              끝난 뒤
①      0.02초   CF10 TM 6 (잠깐)                       잠깐 기다림                           CF10 을 쓰는 커서가 모두 무효
②      6.8초    CF10 TM 4                              POS 판매 INSERT 기다림                —
③     14.2초    CF11 에 DML 을 막는 락 (검증 내내)      POS 판매상품 INSERT · 반품 기다림     —
④      0.6초    CF15 TM 6                              입출고 INSERT 기다림                  CF15_PK UNUSABLE → 입출고 INSERT ORA-01502
⑤      9.5초    CF11 TM 4                              POS 판매상품 INSERT 기다림            CF11_PK 리프 7,850 → 7,790 (0.8% 줄었다)

[운영 · 지난주 토요일 14:00 ~ 15:00 에 1분마다 찍은 V$LOCKED_OBJECT]
  CF10 · CF11 에 열린 트랜잭션은 늘 2 ~ 5개 · 하나가 1 ~ 3초 (카드 승인을 기다린다)

문제

다섯 줄마다 영업 중에 돌리면 무엇을 잠그고 매장이 무엇을 겪는지, 개발 DB 리허설이 그것을 왜 보여 주지 못했는지 설명하시오.
토요일 14:00에 돌려도 되도록 계획서를 다시 쓰시오. 뺄 줄은 빼고 이유를 쓰시오.
고친 계획서에도 남는 위험과 작업 중에 지켜볼 것을 쓰시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

-- 토요일 14:00 · 모든 DDL 은 3초까지만 기다린다 (못 잡으면 ORA-00054 → 1분 뒤 다시, 세 번까지)
ALTER SESSION SET DDL_LOCK_TIMEOUT = 3;

-- ① 그대로 — 칸 넓히기는 딕셔너리만 고친다 (TM 6 은 순간) · 끝나면 CF10 커서가 한 번씩 다시 파싱된다
ALTER TABLE CF10 MODIFY (SHP_NO VARCHAR2(10));

-- ② ONLINE — 만드는 동안 POS 의 INSERT 를 막지 않는다 (시작과 끝에 열린 트랜잭션을 기다린다)
CREATE INDEX CF10_X01 ON CF10 (SHP_NO, SLE_DT) ONLINE;

-- ③ 켜기와 검증을 나눈다 — 켜는 순간부터 새 행은 검사 · 옛 행 검증은 DML 을 막지 않고 따로
ALTER TABLE CF11 ADD CONSTRAINT CF11_FK_SLE FOREIGN KEY (SLE_NO) REFERENCES CF10 (SLE_NO) ENABLE NOVALIDATE;
ALTER TABLE CF11 MODIFY CONSTRAINT CF11_FK_SLE VALIDATE;

-- ④ 뺀다 — 349 블록의 절반을 돌려받으려고 입출고를 멈출 일이 아니다 (꼭 하려면 ALTER TABLE CF15 MOVE ONLINE)
-- ⑤ 뺀다 — 리프 0.8% 를 줄이려고 POS 를 9.5초 세울 이유가 없다

개선 후 실행계획 학습용 예시 · ONLINE 없는 CREATE INDEX의 TM 4는 교재 12화 실측(실습/05 [D4])

xplan

[고친 계획서 · 운영 크기 사본에 POS 부하를 걸고 다시 (학습용 예시)]
줄   걸린 시간              POS 와 물류센터가 겪은 것
①      0.03초               최대 0.03초 기다림 · 직후 CF10 SQL 이 한 번씩 하드 파싱
②     11.4초               기다림 없음 (만드는 동안의 변경은 저널에) · 판매 INSERT 0.02 → 0.03초
③      0.04초 + 검증 15.8초  기다림 없음
④ ⑤    빠짐

모범 답안 서술

개발 DB 리허설은 운영의 1/50 크기에 아무도 쓰지 않는 DB였다. 그래서 몇 초 걸리는지만 보였고, 그동안 무엇을 쥐고 누가 기다리는지는 보이지 않았다. 영업 중 DDL의 비용은 걸린 시간이 아니라, 쥐는 락과 그 시간 동안 들어오는 DML로 정해진다. ① 칸 넓히기는 딕셔너리만 고치므로 0.02초다. 다만 TM 6을 한 번 얻어야 하므로 열린 트랜잭션이 있으면 그 뒤에 서고, 그동안 뒤의 판매가 줄을 선다. 끝나면 CF10을 쓰는 커서가 모두 무효가 되어 한 번씩 다시 파싱된다. 짧은 DDL_LOCK_TIMEOUT을 주면 영업 중에도 할 만하다. ② ONLINE 없는 CREATE INDEX는 만드는 내내 CF10에 TM 4를 쥔다. TM 4는 POS 판매 INSERT가 요청하는 TM 3과 함께 쥘 수 없으므로 6.8초 동안 판매 저장이 멈춘다. ONLINE으로 만들면 DML을 막지 않는다. 대신 시작과 끝에 열린 트랜잭션을 기다린다(토요일 오후에는 1 ~ 3초짜리가 늘 몇 개 있다). ③ FK를 그냥 더하면 켜면서 옛 행 110만 개를 검사하고, 그 14.2초 동안 CF11의 DML을 막는 락을 쥔다. ENABLE NOVALIDATE로 켜면 새 행만 검사하므로 순간에 끝나고, 옛 행 검증은 MODIFY CONSTRAINT … VALIDATE로 따로 하면 DML을 막지 않는다. 지난주 확인으로 위반 행이 없으므로 검증도 성공한다. ④ 오프라인 MOVE는 0.6초 동안 CF15에 TM 6을 쥐어 입출고 INSERT를 막고, 끝나면 행이 옮겨져 CF15_PK가 UNUSABLE이 된다. 유니크 인덱스라서 그 뒤 입출고 INSERT가 REBUILD할 때까지 ORA-01502로 실패한다. 349블록의 절반을 돌려받는 일은 영업 중에 할 일이 아니므로 뺀다. 꼭 해야 하면 MOVE ONLINE(12.2 이상)으로 한다. ⑤ ONLINE 없는 리빌드는 9.5초 동안 CF11에 TM 4를 쥐어 판매상품 INSERT와 반품을 막는다. 그런데 얻는 것은 리프 7,850 → 7,790, 0.8%뿐이다. 분기마다 습관으로 하던 리빌드라 뺀다. 남는 위험은 둘이다. 짧은 DDL도 열린 트랜잭션 뒤에 서면 그동안 뒤의 POS가 줄을 선다. 그래서 DDL_LOCK_TIMEOUT을 3초로 주고, 못 잡으면 물러났다가 다시 시도한다. ONLINE 생성은 쉬고 있는 세션의 열린 트랜잭션 하나에 오래 붙잡힐 수 있다. 작업 중에는 블로킹 트리와 V$LOCKED_OBJECT를 30초마다 찍고, DDL이 3초 넘게 무언가를 기다리면 그 세션부터 확인한다.

해설

계획서 검토의 칸은 넷이다. 문장마다 ① 쥐는 락과 모드 ② 쥐는 시간(운영 크기에서) ③ 그동안 같은 테이블에 들어오는 DML ④ 끝난 뒤 남는 것(UNUSABLE 인덱스, 무효 커서, 통계)을 적는다. 개발 DB의 걸린 시간 한 칸으로는 아무것도 판단할 수 없다.
ONLINE과 NOVALIDATE는 락을 없애는 것이 아니라 줄이는 것이다. 둘 다 시작할 때 한 번은 TM을 얻어야 하고, ONLINE은 열린 트랜잭션을 기다린다. 그래서 짧은 DDL_LOCK_TIMEOUT과 함께 쓴다.
③의 FK 컬럼 SLE_NO는 CF11_PK의 맨 앞 컬럼이라 따로 인덱스가 없어도 된다. 부모 CF10의 판매를 지울 때 자식을 통째로 훑지 않는다(교재 12화 §8). FK 컬럼이 인덱스의 맨 앞이 아니었다면 그 인덱스부터 ONLINE으로 만들어야 한다.
지운 행이 많아 리프가 듬성듬성해진 인덱스가 아니라면 리빌드로 얻는 것은 거의 없다. 영업 중이라면 잃는 것만 있다. 꼭 해야 하면 REBUILD ONLINE이다.
컬럼 타입을 바꾸는 것처럼 테이블 구조를 크게 바꾸는 일은 이 계획서의 방법으로는 영업 중에 할 수 없다. DBMS_REDEFINITION으로 온라인 재정의를 하거나 새벽 작업으로 미룬다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · ONLINE 없는 CREATE INDEX의 TM 4는 교재 12화 실측(실습/05 [D4]) ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · ONLINE 없는 CREATE INDEX의 TM 4는 교재 12화 실측(실습/05 [D4]) ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황