버거 ORD_RANGE는 달마다 칸을 손으로 만든다. 12월 말 담당자가 바뀌면서 2014년 칸을 만드는 일이 빠졌고, 1월 1일부터 들어온 주문이 모두 P_DEFAULT (MAXVALUE)에 쌓였다. 2월 6일 오과장이 알아채고 아래 두 문장을 준비했다. 시험 DB에서 먼저 돌려 본 결과가 아래에 있다.
POS는 24시간 주문을 넣고, 영수증 재조회는 하루 약 6만 번 호출된다. 오과장: "칸을 떼어 내고, 이참에 INTERVAL로 바꿔서 다시는 손으로 만들지 않게 하겠다."
-- (1) 1월 칸을 떼어 낸다ALTER TABLE ORD_RANGE SPLIT PARTITION P_DEFAULT AT ('201402') INTO (PARTITION P201401, PARTITION P_DEFAULT);-- (2) 앞으로는 칸을 손으로 만들지 않게 INTERVAL 로 바꾼다ALTER TABLE ORD_RANGE SET INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'));
[시험 DB · (1) SPLIT · 경과 1.4초 · redo 약 11MB] P201401 42,117행 (새 세그먼트) P_DEFAULT 6,593행 (새 세그먼트) ORD_RANGE_PK · ORD_RANGE_X01 P201401 · P_DEFAULT 칸 UNUSABLE (다른 23칸은 USABLE) ORD_RANGE_GX01 UNUSABLE[그 직후 · POS 주문 INSERT] ORA-01502: index 'DEV.ORD_RANGE_GX01' or partition of such index is in unusable state[그 직후 · 영수증 재조회 (WHERE ORD_NO = :ord_no)] ORD_RANGE_GX01 을 쓰지 못한다 · 깨진 두 칸은 FULL 로 읽는다 · 4 → 약 1,300블록 (에러 없음)[시험 DB · (2) SET INTERVAL] ORA-14751: invalid data type for partitioning column of an interval partitioned table
-- 지금 (03:00 · 주문이 가장 적은 시각) : 1월 · 2월을 한 번에 떼어 내고 석 달 치 빈 칸을 미리 만든다-- 로컬 · 글로벌 인덱스를 DDL 안에서 같이 고친다 (GLOBAL 이 아니라 INDEXES)ALTER TABLE ORD_RANGE SPLIT PARTITION P_DEFAULT INTO ( PARTITION P201401 VALUES LESS THAN ('201402'), PARTITION P201402 VALUES LESS THAN ('201403'), PARTITION P201403 VALUES LESS THAN ('201404'), PARTITION P201404 VALUES LESS THAN ('201405'), PARTITION P_DEFAULT ) UPDATE INDEXES;-- 확인SELECT index_name, partition_name, status FROM user_ind_partitions WHERE index_name IN ('ORD_RANGE_PK', 'ORD_RANGE_X01') AND status <> 'USABLE';SELECT index_name, status FROM user_indexes WHERE index_name = 'ORD_RANGE_GX01';-- 다음부터 (매월 1일 월 정리에 넣는다) : P_DEFAULT 가 비어 있을 때 석 달 뒤 칸을 쪼개 만든다ALTER TABLE ORD_RANGE SPLIT PARTITION P_DEFAULT AT ('201406') INTO (PARTITION P201405, PARTITION P_DEFAULT) UPDATE INDEXES;-- 그리고 P_DEFAULT 에 행이 하나라도 있으면 알린다SELECT COUNT(*) FROM ORD_RANGE PARTITION (P_DEFAULT);
(1)의 문제는 인덱스다. P_DEFAULT에는 1월과 2월 주문 48,710행이 섞여 있어서, SPLIT은 이 행들을 두 칸으로 나눠 새 세그먼트에 옮겨 쓴다. 행이 옮겨지면 ROWID가 바뀐다. UPDATE INDEXES가 없으니 로컬 인덱스 ORD_RANGE_PK와 ORD_RANGE_X01의 두 칸(P201401, P_DEFAULT)이 UNUSABLE이 되고, 옛 ROWID를 들고 있는 글로벌 인덱스 ORD_RANGE_GX01도 통째로 UNUSABLE이 된다. 영수증 재조회는 에러 없이 글로벌 인덱스를 건너뛰고 깨진 칸을 FULL로 읽어 4 → 약 1,300블록이 되고, 하루 6만 번이면 약 7,800만 블록이다. 더 큰 문제는 적재다. 2월 주문은 P_DEFAULT로 들어가는데 그 칸의 로컬 PK와 글로벌 PK가 모두 깨진 유니크 인덱스라서, POS 주문 INSERT가 ORA-01502로 멈춘다.
(2)는 처음부터 될 수 없다. INTERVAL 파티션은 파티션 키가 DATE나 NUMBER 컬럼 하나여야 하는데 ORD_YM은 VARCHAR2 문자라서 ORA-14751이 난다. 키가 DATE였더라도 MAXVALUE 칸(P_DEFAULT)이 있으면 INTERVAL로 바꿀 수 없어서, 빈 상태로 만든 뒤 지워야 한다.
지금은 SPLIT PARTITION P_DEFAULT INTO (P201401, P201402, P201403, P201404, P_DEFAULT) UPDATE INDEXES 한 문장으로 1월과 2월을 나누고 석 달 치 빈 칸을 함께 만든다. UPDATE INDEXES는 옮기는 행의 로컬과 글로벌 인덱스 키를 DDL 안에서 다시 적으므로, 끝났을 때 모든 인덱스가 그대로 쓸 수 있는 상태다. 대가는 시간과 redo다(약 3.5초, redo 약 26MB, 붙이지 않을 때의 1.4초와 11MB보다 크다). 그동안 P_DEFAULT로 들어오는 주문 INSERT는 DDL이 끝날 때까지 기다리므로 주문이 가장 적은 03:00에 돌린다.
나중에 REBUILD하는 안은 SPLIT 자체는 빠르다. 그러나 SPLIT이 끝난 순간부터 REBUILD가 끝날 때까지 글로벌 인덱스(100만 행)와 로컬 두 칸을 다시 만드는 동안 영수증 조회는 FULL로 느려지고, 주문 INSERT는 ORA-01502로 멈춘다. 24시간 주문을 받는 POS에서는 그 틈이 장애다. 그래서 UPDATE INDEXES를 권한다.
재발 방지의 핵심은 칸을 행이 들어오기 전에 만드는 것이다. 빈 P_DEFAULT를 쪼개면 옮길 행이 없어서 인덱스 일이 거의 없고 0.1초도 걸리지 않는다. 그래서 매월 1일 월 정리에 "석 달 뒤 칸을 미리 쪼개 만든다"를 넣고, P_DEFAULT에 행이 하나라도 있으면 알리는 점검을 둔다. INTERVAL을 쓰려면 ORD_YM 대신 DATE 파티션 키(예: 주문월 첫날)를 두고 테이블을 다시 나눠야 한다. 키를 바꾸면 화면의 ORD_YM 조건도 모두 바꿔야 하므로, 다음 재설계 때 따로 검토한다.
