지난달 교훈대로 ONLINE을 붙인 인덱스 생성이 38분째 끝나지 않는다

중급DDL유통539 / 700

상황

본사 레거시 유통은 지난달 10:30에 CF11에 인덱스를 만들다가 47초 동안 매장 POS의 판매 저장을 세웠다. 포스트모템의 재발방지 1번은 "영업시간의 인덱스 생성에는 ONLINE을 붙인다"였다. 이번 달에는 MD팀의 상품별 판매 조회용 인덱스를 같은 시간대에 ONLINE으로 만들었다. 매장 POS는 아무 문제가 없다. 그런데 리허설(같은 크기의 사본)에서 52초 걸린 생성이 11:08인 지금 38분째 끝나지 않는다. MD팀은 12:00 보고 전에 조회가 빨라지기를 기다리고 있다.

테이블과 인덱스

CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO)(ITM_NO만으로 찾으면 선두 컬럼이 아니다) · 영업 중에는 POS가 판매를 INSERT하고 반품과 수량 정정을 UPDATE한다
만드는 인덱스: CF11_X01 (ITM_NO, SLE_NO) · 운영 DB는 EE다
지난달 ONLINE 없이 만든 47초 동안 POS의 판매 저장은 enq: TM - contention으로 기다렸다(그때의 V$LOCK에서 DDL은 CF11에 TM 4)
본사 전산 담당자들은 SQL Developer를 자동 커밋을 끈 채 쓴다

원본 SQL

sql

-- 10:30 · 재발방지 1번대로
CREATE INDEX CF11_X01 ON CF11 (ITM_NO, SLE_NO) ONLINE;

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

xplan

[V$SESSION — 11:08:20]
SID   SERIAL#  PROGRAM          STATUS     EVENT                            SECS    BLOCKING_SESSION   하고 있는 문장 / 마지막 문장
152   8811     sqlplus (DBA)    ACTIVE     enq: TX - row lock contention    2,292   88                 CREATE INDEX CF11_X01 ON CF11 (ITM_NO, SLE_NO) ONLINE
88    4127     SQL Developer    INACTIVE   SQL*Net message from client      2,381                      (마지막) UPDATE CF11 SET SLE_QTY = 3 WHERE SLE_NO = '20231208-0217' AND ITM_NO = '1000044'
(POS 세션 41개 · 기다리는 세션 없음 · 판매 저장 평균 0.02초)

[V$LOCK — 같은 시각]
SID   TYPE  대상 / 이름표         LMODE  REQUEST  BLOCK
152   TM    CF11                    2       0       0
152   TX    88 의 트랜잭션           0       4       0
88    TM    CF11                    3       0       0
88    TX    88 의 트랜잭션           6       0       1
(POS 세션들: TM CF11 3 · 각자의 TX 6 — 누구도 152 에게 막히지 않는다)

[V$TRANSACTION — SID 88]
START_TIME           USED_UBLK   USED_UREC
12/08/23 10:28:39        1           1        ← 10:28 에 한 행을 고치고 COMMIT 하지 않았다

[지난달 · ONLINE 없이 같은 크기의 인덱스]
10:30:02 ~ 10:30:49   DDL 이 CF11 에 TM 4 · POS 판매 저장 enq: TM - contention (최대 46초)

문제

V$SESSION과 V$LOCK을 근거로 인덱스 생성이 무엇을 기다리는지, 그런데도 POS가 멀쩡한 이유를 설명하시오. 지난달의 ONLINE 없는 생성과 락이 어떻게 다른지도 쓰시오.
지금 할 일을 SQL로 쓰고, 하면 안 되는 일과 그 이유를 쓰시오.
다음 인덱스 생성 전에 확인할 것을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 1. CF11 에 COMMIT 하지 않은 트랜잭션이 누구인지, 되감을 일이 얼마나 되는지
SELECT s.sid, s.serial#, s.username, s.program, s.status, s.last_call_et,
       t.start_time, t.used_urec
  FROM v$locked_object lo
  JOIN v$session s     ON s.sid = lo.session_id
  JOIN v$transaction t ON t.addr = s.taddr
  JOIN dba_objects o   ON o.object_id = lo.object_id
 WHERE o.owner = 'PUP' AND o.object_name = 'CF11';

-- 2. 주인(본사 전산 담당자)에게 COMMIT 이나 ROLLBACK 을 부탁한다
--    연락이 안 되면 끊는다 — 되감을 일은 한 행(USED_UREC 1)뿐이다
ALTER SYSTEM KILL SESSION '88,4127' IMMEDIATE;

-- 3. 인덱스 생성(152)은 그대로 둔다 — 88 이 끝나면 바로 만들기 시작한다

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

xplan

[11:10 · 88 을 끊은 뒤 (학습용 예시)]
  11:10:04   SID 88 끊김 · 한 행 되감기 0.01초
  11:10:04   SID 152 기다림 끝 → 인덱스 만들기 시작 (그동안 POS 의 변경은 저널 테이블에 적는다)
  11:11:02   Index created. (58초 · 리허설 52초보다 조금 길다) · POS 판매 저장 평균 0.02 → 0.03초

모범 답안 서술

인덱스 생성(SID 152)은 enq: TX - row lock contention으로 2,292초째 기다리고, blocking_session은 88이다. V$LOCK을 보면 152는 CF11에 TM 2를 쥐었고, 88의 트랜잭션을 REQUEST 4로 요청하고 있다. 행 하나를 원하는 것이 아니라 그 트랜잭션이 끝나기를 기다리는 모양이다. ONLINE으로 만들 때는 만드는 동안 들어오는 변경을 저널 테이블에 적어 두었다가 반영한다. 그래서 시작하는 순간 그 테이블에 열려 있는 트랜잭션이 모두 끝나야 하고(끝낼 때도 한 번 더 기다린다), 그 전에 시작한 트랜잭션이 끝날 때까지 기다린다. 88은 10:28:39에 SQL Developer로 한 행을 고치고 COMMIT하지 않은 채 40분째 쉬고 있는 세션이다. 인덱스는 아직 한 블록도 만들지 않았다. 그런데도 POS는 멀쩡하다. ONLINE 생성이 쥔 TM 2는 POS의 TM 3과 부딪치지 않고, 152가 요청한 것은 테이블이 아니라 88의 TX이므로 POS가 그 뒤에 줄을 서지 않는다. 지난달의 ONLINE 없는 생성은 달랐다. 만드는 내내 CF11에 TM 4를 쥐었고, TM 4는 판매 저장이 요청하는 TM 3과 부딪치므로 47초 동안 POS가 enq: TM - contention으로 기다렸다. 지금 할 일은 뿌리 88을 끝내는 것이다. V$LOCKED_OBJECT와 V$TRANSACTION으로 주인과 되감을 일을 확인하고(USED_UREC 1, 한 행), 주인에게 COMMIT이나 ROLLBACK을 부탁한다. 연락이 안 되면 세션을 끊어도 된다. 되감기는 순식간이다. 88이 끝나면 152는 바로 만들기 시작해 1분 안에 끝난다. 하면 안 되는 일은 인덱스 생성 세션(152)을 서둘러 끊는 것이다. ONLINE 생성이 중간에 끊기면 만들다 만 인덱스와 저널 테이블이 남아, DBMS_REPAIR.ONLINE_INDEX_CLEAN으로 정리할 때까지 같은 인덱스를 다시 만들지 못할 수 있다. 끊어도 88은 그대로라 다시 만들면 또 기다린다. ONLINE을 떼고 다시 만드는 것은 더 나쁘다. 88의 TM 3 뒤에서 TM 4를 요청하며 줄 맨 앞에 서면, 그 뒤의 POS 판매 저장이 모두 줄을 선다. 다음 생성 전에는 V$LOCKED_OBJECT로 그 테이블에 열린 트랜잭션이 있는지 먼저 보고, 오래 쉬는 도구 세션은 주인에게 정리하게 한다. 프로파일의 IDLE_TIME이나 다른 세션을 막는 유휴 세션 한도(MAX_IDLE_BLOCKER_TIME)를 두면 같은 뿌리가 40분씩 남지 않는다.

해설

ONLINE은 아무것도 기다리지 않는다는 뜻이 아니다. DML을 막지 않는 대신 그 테이블의 열린 트랜잭션을 기다린다. 쉬고 있는 세션 하나가 몇 시간을 붙잡을 수 있다. 기다리는 동안에도 POS를 막지 않는다는 것이 ONLINE을 쓰는 이유다.
ONLINE에도 비용이 있다. 만드는 동안의 변경을 저널에 적고 끝에 반영하므로 그 시간의 DML이 조금 무거워지고, 생성도 ONLINE 없을 때보다 오래 걸린다. 변경이 아주 많은 시간대라면 끝에 반영할 양이 늘어 마무리가 길어진다.
88을 끊기 전에 반드시 USED_UREC를 본다. 여기서는 한 행이지만, 몇 시간짜리 배치라면 끊는 순간부터 되감는 동안 락이 그대로 남는다(교재 12화 §5). 그때는 기다리는 편이 빠를 수 있다.
오라클 10g까지는 ONLINE 생성도 시작과 끝에 잠깐 TM 4를 요청해 그 뒤에 DML이 줄을 섰다. 모드는 버전마다 다르므로 운영 버전에서 V$LOCK을 찍어 확인하고 계획서에 적는다.
ONLINE 인덱스 생성은 EE 기능이다. SE라면 영업시간 밖에 ONLINE 없이 만든다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

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

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황