컬럼 하나 더하는 패치를 돌린 3분 남짓 동안 쇼핑몰 주문 화면이 전부 멈췄다

기초DDL한빛유통535 / 700

상황

화요일 14:05, 개발팀이 「주문 메모」 기능을 위해 T_ORD에 컬럼 하나를 더하는 패치를 SQL*Plus로 돌렸다. 처음에는 ORA-00054: resource busy and acquire with NOWAIT specified가 바로 떴다. 담당자는 검색해 본 대로 DDL_LOCK_TIMEOUT을 600초로 주고 다시 돌렸다. 그 뒤 14:05:02부터 14:08:31까지 쇼핑몰의 주문 등록과 결제 완료가 전부 멈췄다. 패치는 14:08:31에 "Table altered."를 찍고 끝났다. 담당자는 이렇게 말한다. "컬럼 추가는 0.01초짜리인데요?"

테이블과 인덱스

T_ORD 주문: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · 낮에는 주문 등록(INSERT)이 초당 5 ~ 10건, 결제 완료 콜백(UPDATE)이 초당 3 ~ 6건 들어온다
고객센터 「주문 수정」 화면은 상담원이 결제 수단을 바꾸면 그 주문 행을 UPDATE하고, 「저장」을 누를 때 COMMIT한다
쇼핑몰 WAS의 커넥션 풀은 120개다
이 DB의 DDL_LOCK_TIMEOUT 기본값은 0이다

원본 SQL

sql

-- 14:05 패치 (처음에는 ORA-00054 가 바로 나서 첫 줄을 붙였다)
ALTER SESSION SET DDL_LOCK_TIMEOUT = 600;
ALTER TABLE T_ORD ADD (ORD_MEMO VARCHAR2(200));

실행계획 학습용 예시 · 줄의 모양은 교재 12화 실측(실습/05 [D2])

xplan

[시간표 — 화요일]
시각        고객센터 상담원 (SID 77)            패치 (SID 140)                          주문 등록 · 결제 완료 (WAS 세션들)
14:03:12    주문 81,207 결제 수단 변경 UPDATE
            (통화 중 · COMMIT 전)
14:05:00                                        ALTER TABLE → ORA-00054 (0초)
14:05:02                                        DDL_LOCK_TIMEOUT 600 · ALTER TABLE → 기다림
14:05:03 ~                                                                                INSERT · UPDATE T_ORD → 하나씩 기다림
14:08:31    「저장」 → COMMIT                    → Table altered. (0.01초)               → 한꺼번에 진행 (기다린 세션 최대 97개)

[V$SESSION — 14:07:00]
SID      EVENT                            SECS      BLOCKING_SESSION   하고 있는 문장
77       SQL*Net message from client       228                         (통화 중 · 트랜잭션을 연 채)
140      enq: TM - contention              118      77                 ALTER TABLE T_ORD ADD (ORD_MEMO VARCHAR2(200))
(91개)   enq: TM - contention          1 ~ 117      140                INSERT INTO T_ORD … / UPDATE T_ORD SET ORD_ST = 'COMPLETE' …

[V$LOCK — 같은 시각 · TM 은 객체 이름으로]
SID   TYPE  대상                LMODE  REQUEST  BLOCK
77    TM    T_ORD                 3       0       1
77    TX    77 의 트랜잭션         6       0       0
140   TM    T_ORD                 0       6       0      ← 쥔 것은 없다 · 그런데 뒤의 세션들은 V$SESSION 에서 140 을 가리킨다
212   TM    T_ORD                 0       3       0      ← 주문 등록 하나 (나머지 90개도 같은 모양)

[같은 패치를 03:00 에 연습 DB 복사본에서 다시 잼]
  ALTER TABLE T_ORD ADD (ORD_MEMO VARCHAR2(200))   0.01초 · 기다림 0

문제

시간표와 V$LOCK을 근거로, 상담원 한 명과 부딪칠 일이 없는 주문 등록까지 왜 3분 넘게 기다렸는지 설명하시오.
같은 컬럼을 더하는 패치를 다시 쓰고, 언제 어떻게 돌릴지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 주문이 가장 적은 03:00 에 돌린다 · 5초까지만 기다리고 못 잡으면 물러난다
ALTER SESSION SET DDL_LOCK_TIMEOUT = 5;
ALTER TABLE T_ORD ADD (ORD_MEMO VARCHAR2(200));
-- ORA-00054 가 나면 1분 뒤 다시 (최대 다섯 번). 기다리는 5초 동안만 뒤의 주문이 줄을 선다

-- 돌리기 전에 T_ORD 에 열린 트랜잭션이 있는지 본다
SELECT s.sid, s.serial#, s.program, s.status, s.last_call_et, t.start_time
  FROM v$locked_object lo
  JOIN dba_objects o   ON o.object_id = lo.object_id
  JOIN v$session s     ON s.sid = lo.session_id
  JOIN v$transaction t ON t.addr = s.taddr
 WHERE o.object_name = 'T_ORD';

개선 후 실행계획 학습용 예시 · 줄의 모양은 교재 12화 실측(실습/05 [D2])

xplan

[03:00 · 같은 패치 (학습용 예시)]
  ALTER TABLE   0.01초 · 기다림 0 · 뒤에 선 주문 0
[만약 낮에 5초 한도로 돌렸다면]
  14:05:02 ~ 14:05:07   주문 등록이 최대 5초 기다림 → DDL 이 ORA-00054 로 물러나면 바로 진행

모범 답안 서술

상담원(SID 77)이 14:03:12에 주문 81,207을 고치고 COMMIT하지 않은 채 통화를 이어 갔다. 이 트랜잭션은 T_ORD에 TM 3을 쥐고 있다. ALTER TABLE … ADD는 테이블째 바꾸는 DDL이라 TM 6을 얻어야 하고, TM 6은 TM 3과 함께 쥘 수 없다. 처음에는 DDL_LOCK_TIMEOUT 기본값 0 때문에 기다리지 않고 바로 ORA-00054를 받았다. 600초를 주자 이번에는 140이 TM 6을 요청한 채 줄 맨 앞에 섰다(V$LOCK REQUEST 6, enq: TM - contention, blocking_session 77). 그 뒤에 온 주문 등록과 결제 완료는 TM 3만 원한다. 상담원의 TM 3과는 부딪치지 않는 모드다. 그런데 enqueue는 요청한 순서대로 줄을 세우고 앞사람을 건너뛰지 못한다. 그래서 91개가 TM 3을 요청한 채 140 뒤에 줄을 섰고, V$SESSION의 blocking_session도 77이 아니라 140을 가리킨다. 140은 쥔 것이 없는데도 모두를 막았다. 컬럼 추가 자체는 딕셔너리만 고치는 0.01초짜리 일이다. 14:05:02 ~ 14:08:31의 3분 29초는 모두 상담원이 저장을 누를 때까지 줄을 선 시간이다. 기다린 세션은 최대 97개였고, 커넥션 풀 120개 가운데 그만큼이 묶였다. 처음의 ORA-00054는 실패가 아니라 안전한 동작이었다. 바쁜 테이블에 DDL을 걸 때는 한도를 길게 주는 쪽이 더 위험하다. 고친 판은 주문이 가장 적은 03:00에 DDL_LOCK_TIMEOUT = 5로 돌리고, 못 잡으면 물러났다가 다시 시도한다. 그러면 운이 나빠도 뒤의 주문은 5초까지만 기다린다. 돌리기 전에는 V$LOCKED_OBJECT로 T_ORD에 열린 트랜잭션이 있는지 먼저 본다.

해설

기본값 0이 보호 장치다. DDL_LOCK_TIMEOUT을 길게 주면 DDL은 성공할 때까지 버티지만, 그동안 그 테이블의 DML이 모두 뒤에 선다. 교재 12화의 연습 DB에서도 TRUNCATE에 30초를 주자 뒤에 온 INSERT가 DDL 밑에 TM 3으로 줄을 섰다.
장애 중이라면 상담원을 끊기 전에 패치 세션(140)을 먼저 멈춘다. 140이 물러나면 뒤의 91개는 곧바로 지나간다. 상담원의 TM 3과는 부딪치지 않기 때문이다.
컬럼을 더하는 DDL은 그 테이블을 쓰는 SQL의 커서도 무효로 만든다. 주문이 몰리는 시간에 돌리면 그 직후 같은 SQL들이 한꺼번에 다시 파싱된다. 이것도 한가한 시간을 고르는 이유다.
아무리 짧은 DDL도 TM 6을 한 번은 얻어야 한다. 그래서 "0.01초짜리니까 아무 때나"는 틀린 계산이다. DDL의 비용은 실행 시간이 아니라 그 앞에 열린 가장 긴 트랜잭션의 길이로 정해진다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 줄의 모양은 교재 12화 실측(실습/05 [D2]) ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 줄의 모양은 교재 12화 실측(실습/05 [D2]) ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황