포장 여부 컬럼을 넣는 배포 스크립트가 점심 직전 4분 동안 환불 화면을 세우고 ORA-02296으로 끝났다

중급DDL버거538 / 700

상황

버거 본사는 키오스크의 「포장/매장 식사」 선택을 주문에 남기려고 ORD에 컬럼 하나를 더하기로 했다. 배포 담당자는 개발 DB에서 쓰던 스크립트를 목요일 11:20에 운영에서 돌렸다. 점심 피크는 11:30부터다. 11:20부터 4분 동안 매장 POS의 「환불」 버튼이 돌기만 했다. 결제는 정상이었다. 스크립트는 마지막 줄에서 ORA-02296: cannot enable (DEV.) - null values found로 끝났고, 컬럼은 NULL을 허용한 채 남았다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_PK (ORD_NO) · ORD_X01 (ORD_DT, ORD_HMS) · PCTFREE 10(기본값) · 환불 표시는 상태 칸 ORD_STAT(문제 설정)
점심 전후에는 매장 200곳의 POS가 분당 300건 남짓 주문을 INSERT하고, 환불은 그날 주문을 ORD_NO로 찾아 ORD_STAT을 'R'로 UPDATE한다(하루 수십 건)
운영 DB는 19c다. 개발 DB의 ORD는 1만 행이라 스크립트가 2초 만에 끝났다
새 컬럼은 키오스크와 POS가 값을 넣기 시작할 때까지 'N'(매장 식사)이 기본이다

원본 SQL

sql

-- 배포 스크립트 (개발 DB 에서 2초)
ALTER TABLE ORD ADD (TAKEOUT_YN VARCHAR2(1));
UPDATE ORD SET TAKEOUT_YN = 'N';
COMMIT;
ALTER TABLE ORD MODIFY (TAKEOUT_YN DEFAULT 'N' NOT NULL);

실행계획 학습용 예시

xplan

[목요일 — 배포 세션 (SID 66) 과 매장]
시각          배포 (SID 66)                                   매장 POS
11:20:03      ALTER TABLE ORD ADD … (0.02초)
11:20:05      UPDATE ORD SET TAKEOUT_YN = 'N' 시작            결제(INSERT) 정상 · 환불(UPDATE) 하나씩 멈춤
11:24:12      1000000 rows updated (4분 07초) · COMMIT        멈췄던 환불 7건이 한꺼번에 진행
11:24:13      ALTER TABLE ORD MODIFY … NOT NULL
              → ORA-02296: cannot enable (DEV.) - null values found

[UPDATE 한 문장 — ALLSTATS LAST]
----------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |      |      1 |        |      0 |00:04:07.31 |   1043K |
|   1 |  UPDATE            | ORD  |      1 |        |      0 |00:04:07.31 |   1043K |
|   2 |   TABLE ACCESS FULL| ORD  |      1 |   1000K|   1000K|00:00:03.12 |   22422 |
----------------------------------------------------------------------------------
  세션 통계: redo size 268 MB · undo change vector size 96 MB

[V$SESSION — 11:22:00]
SID   EVENT                             SECS   BLOCKING_SESSION   하고 있는 문장
66    db file sequential read              0                      UPDATE ORD SET TAKEOUT_YN = 'N'
141   enq: TX - row lock contention       84   66                 UPDATE ORD SET ORD_STAT = 'R' WHERE ORD_NO = :B1   (매장 SH0117 환불)
187   enq: TX - row lock contention       31   66                 UPDATE ORD SET ORD_STAT = 'R' WHERE ORD_NO = :B1   (매장 SH0042 환불)

[11:30 · 확인]
SELECT COUNT(*) FROM ORD WHERE TAKEOUT_YN IS NULL;   → 1,236   (모두 11:20:05 ~ 11:24:12 에 들어온 주문)

문제

배포 스크립트의 문장마다 무엇을 잠그고 얼마나 쥐었는지 시간표를 근거로 설명하시오. 결제는 멀쩡했는데 환불만 멈춘 이유와, 마지막 줄이 ORA-02296으로 실패한 이유도 쓰시오.
같은 컬럼을 기존 행을 하나도 고치지 않고 더하도록 스크립트를 다시 쓰고, 그래도 남는 위험을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 점심 피크가 지난 15:00 · 5초까지만 기다린다 (못 잡으면 ORA-00054 로 물러났다가 다시)
ALTER SESSION SET DDL_LOCK_TIMEOUT = 5;

-- 기본값과 NOT NULL 을 한 문장에 — 기존 100만 행은 고치지 않고 딕셔너리에 'N' 을 적는다 (11g 이상)
ALTER TABLE ORD ADD (TAKEOUT_YN VARCHAR2(1) DEFAULT 'N' NOT NULL);

개선 후 실행계획 학습용 예시

xplan

[15:00 · 같은 운영 (학습용 예시)]
  ALTER TABLE ORD ADD (… DEFAULT 'N' NOT NULL)   0.03초 · redo 수 KB · 고친 행 0 · 환불 기다림 0
  SELECT COUNT(*) FROM ORD WHERE TAKEOUT_YN = 'N'   → 1,000,000 (옛 행은 딕셔너리의 기본값으로 읽힌다)
  새 주문 INSERT (컬럼을 적지 않음)                 → 'N' 이 들어간다

모범 답안 서술

첫 줄 ADD (TAKEOUT_YN VARCHAR2(1))는 기본값이 없는 컬럼이라 딕셔너리만 고치고 0.02초에 끝났다. 배타 테이블 락(TM 6)을 잠깐 얻었다가 바로 놓았다. 둘째 줄 UPDATE ORD SET TAKEOUT_YN = 'N'이 문제였다. 한 문장이 100만 행을 모두 고치며 행마다 행 락을 걸고, COMMIT하는 11:24:12까지 4분 07초 동안 쥐었다(Buffers 1,043K, redo 268MB, undo 96MB). 환불은 그날 주문을 UPDATE하므로 이미 잠긴 행을 만나 TX 6으로 기다렸다(enq: TX - row lock contention, blocking_session 66). 결제는 새 행을 INSERT할 뿐이라 잠긴 행을 건드리지 않아 막히지 않았다. TM은 둘 다 3이라 서로 막지 않는다. 마지막 줄 MODIFY … NOT NULL이 실패한 것은 UPDATE가 도는 동안 들어온 주문 때문이다. UPDATE 한 문장은 시작한 시점(11:20:05)의 행만 고친다. 그 뒤 4분 동안 POS가 넣은 주문 1,236건은 TAKEOUT_YN이 NULL로 남았고, NOT NULL을 검사하다 ORA-02296으로 실패했다. 개발 DB에서는 2초 만에 끝나 그 사이에 들어온 주문이 없었다. 고친 판은 ADD (TAKEOUT_YN VARCHAR2(1) DEFAULT 'N' NOT NULL) 한 문장이다. 11g부터는 기본값과 NOT NULL을 함께 준 ADD가 기존 행을 고치지 않고 딕셔너리에 기본값만 적는다. 옛 행을 읽으면 오라클이 딕셔너리의 'N'을 돌려준다. 그래서 100만 행의 행 락도, redo 268MB와 undo 96MB도 사라지고 0.03초에 끝난다. 새 주문은 컬럼을 적지 않아도 'N'이 들어가므로 NULL이 생길 틈도 없다. 남는 위험은 DDL 자체의 락이다. 아무리 짧아도 ADD는 TM 6을 한 번 얻어야 하므로, 열린 트랜잭션이 있으면 그 뒤에 서고 그동안 뒤의 결제가 줄을 선다. 그래서 피크가 지난 15:00에 DDL_LOCK_TIMEOUT = 5로 돌린다. 컬럼을 더한 직후 ORD를 쓰는 커서가 한 번씩 다시 파싱되는 것도 피크를 피하는 이유다.

해설

빠른 ADD에는 버전 조건이 있다. 기본값과 NOT NULL을 함께 준 ADD가 딕셔너리만 고치는 것은 11g부터이고, NULL을 허용하는 컬럼에 기본값을 준 ADD까지 그렇게 되는 것은 12c부터다. 그보다 옛 버전에서는 ADD가 모든 행에 값을 써 넣으며 그동안 테이블 락을 쥔다. 운영 버전에서 먼저 재 본다.
개발 DB가 1만 행이라 2초에 끝났다는 것은 근거가 되지 못한다. 운영 크기에서 문장마다 무엇을 잠그고 얼마나 쥐는지를 따져야 한다. 모든 행을 고치는 UPDATE는 크기에 비례해 락을 오래 쥔다.
꼭 행마다 다른 값을 채워야 한다면 한 문장 대신 범위를 나눠 조금씩 고치고 커밋한다. 한 번에 쥐는 행과 시간이 준다. 대신 반쯤 채워진 상태가 생기므로 NOT NULL은 다 채운 뒤에 건다.
PCTFREE가 0인 테이블이었다면 값을 채우는 UPDATE가 행을 늘려 행 이전까지 만들었을 것이다. 딕셔너리의 기본값은 행을 늘리지 않는다(교재 05화).
나중에 기본값을 바꿔도 이 ADD 때 딕셔너리에 적힌 'N'으로 읽히는 옛 행의 값은 바뀌지 않는다. 기본값을 바꾸는 것은 앞으로 들어올 행의 이야기다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황