가격 한 칸 고치는 상품 화면이 점심때마다 1분씩 멈춘다

중급UPDATE한빛유통512 / 700

상황

12화 포스트모템 뒤 한빛유통도 FK를 하나씩 켜기 시작했다. 6월 3일에 첫 단계로 T_ORD_DET → M_ITM FK를 켰다(고아 0건 확인). 그 주부터 상품팀의 「상품 정보」 화면이 저장 버튼에서 멈춘다. 점심 주문이 몰리는 11:30~13:00에는 평균 41초, 길면 2분 가까이 걸리고, 새벽에는 바로 저장된다. 개발팀은 이렇게 말한다. "가격 한 칸만 바꾸는 UPDATE예요. 키는 건드리지도 않아요." 화면의 SQL은 사내 ORM 프레임워크가 만든다. 엔티티의 컬럼을 전부 SET에 넣는 기본 설정 그대로다.

테이블과 인덱스

M_ITM 상품(부모): 100행 · 5블록 · PK_M_ITM (ITM_ID) · 상품팀이 하루 300번쯤 저장한다
T_ORD_DET 주문상세(자식): 200,000행 · 896블록 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) · 6월 3일에 켠 FK_ORD_DET_ITM (ITM_ID → M_ITM) ENABLED VALIDATED · ITM_ID가 맨 앞인 인덱스는 없다
T_ORD 주문: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · 다른 FK는 아직 없다
주문 화면은 T_ORD와 T_ORD_DET를 INSERT한 뒤 결제창이 닫힐 때 COMMIT한다(보통 30초~2분). 점심 피크에는 COMMIT 전 주문이 늘 20~40건 있다.

원본 SQL

sql

-- 「상품 정보」 저장: ORM 이 만든 SQL (가격 한 칸만 바꿔도 모양이 같다)
UPDATE M_ITM
   SET ITM_ID  = :1,
       ITM_NM  = :2,
       ITM_TP  = :3,
       UNT_PRC = :4
 WHERE ITM_ID  = :5;
COMMIT;

실행계획 학습용 예시 · 기다림의 모양은 교재 12화 실측(26ai Free)

xplan

[「상품 정보」 저장 한 번 — 새벽에 혼자 돌렸을 때]
                              FK 켜기 전    FK 켠 뒤
  session logical reads            3          903      ← T_ORD_DET 896 블록을 통째로 훑는다
  경과                           1 ms        38 ms

[시간표 — 6월 5일 12:10 · 점심 피크 · 상품 1042 의 가격만 바꿨다]
시각       상품 화면 (SID 64)                          주문 화면 (SID 131 · 152 · 177 …)
12:10:00                                               주문 INSERT (T_ORD · T_ORD_DET) · 결제창 — COMMIT 전
12:10:05   저장 → UPDATE M_ITM … → 기다림 (SID 131)
12:10:41                                               SID 131 COMMIT
12:10:41   계속 기다림 (이번엔 SID 152)
12:11:18                                               SID 152 COMMIT · 그다음 SID 177 …
12:11:52   UPDATE 끝 · COMMIT (1분 47초)

[V$SESSION — 12:10:30]
SID   EVENT                            SECS   BLOCKING_SESSION   하고 있는 문장
64    enq: TX - row lock contention      25   131                UPDATE M_ITM SET ITM_ID = :1, ITM_NM = :2 …
131   SQL*Net message from client        30                      (결제창 — 트랜잭션을 연 채)

[V$LOCK — 12:10:30 (TM 은 객체 이름으로)]
SID   TYPE  대상                     LMODE  REQUEST  BLOCK
64    TM    M_ITM                      3      0        0
64    TX    상품 화면의 트랜잭션         6      0        0
64    TX    SID 131 의 트랜잭션          0      4        0      ← 행 락(6)이 아니라 4
131   TM    T_ORD                      3      0        0
131   TM    T_ORD_DET                  3      0        0
131   TM    M_ITM                      3      0        0      ← 자식에 넣은 세션은 부모에도 TM 3
131   TX    SID 131 의 트랜잭션          6      0        1
      (SID 131 이 넣은 주문상세의 상품은 1077 · 1015 — 1042 가 아니다)

[같은 날 「상품 정보」 저장 312 번 — 애플리케이션 로그]
  11:30 ~ 13:00   평균 41초 · 최대 1분 52초        그 밖의 시간   평균 0.04초

문제

세션 통계와 시간표, V$LOCK을 근거로 가격만 고친 UPDATE가 왜 T_ORD_DET를 통째로 읽고 주문 화면을 기다리는지 설명하시오.
저장 SQL과 필요한 DDL을 고치시오. 둘 중 하나만 한다면 무엇을 먼저 할지와 그 이유도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① ORM 설정: 키 컬럼은 갱신 대상에서 뺀다(updatable = false) · 바뀐 컬럼만 SET 에 넣는다(동적 UPDATE)
UPDATE M_ITM
   SET UNT_PRC = :unt_prc
 WHERE ITM_ID  = :itm_id;
COMMIT;

-- ② 자식 FK 컬럼에 인덱스: 상품을 지우거나 키를 다시 쓰는 일이 생겨도 그 상품의 상세만 확인한다
--    낮에 만든다면 ONLINE (아니면 만드는 동안 T_ORD_DET 에 TM 4 → 주문 INSERT 가 줄을 선다)
CREATE INDEX IX_T_ORD_DET_ITM ON T_ORD_DET (ITM_ID) ONLINE;

개선 후 실행계획 학습용 예시 · 기다림의 모양은 교재 12화 실측(26ai Free)

xplan

[고친 뒤 — 같은 점심 피크 (학습용 예시)]
  「상품 정보」 저장   session logical reads 903 → 3 (① 만으로도 FK 확인이 돌지 않는다)
                      주문 화면을 기다리지 않는다 · 11:30 ~ 13:00 평균 41초 → 0.04초
  ② 의 인덱스가 있으면 키를 SET 에 넣은 화면이 남아 있어도 그 상품의 상세만 인덱스로 확인한다 (903 → 약 5) · 기다림 0

모범 답안 서술

세션 통계를 보면 가격 한 칸을 바꾸는 UPDATE가 FK를 켠 뒤 3 → 903블록을 읽는다. 늘어난 900블록은 T_ORD_DET 896블록을 통째로 훑은 몫이다. ORM이 SET에 ITM_ID = :1을 넣었기 때문이다. 오라클은 부모 키 컬럼이 SET에 있으면 값이 그대로여도 키를 고친 것으로 보고, 옛 키를 가리키는 자식이 없는지 FK를 확인한다. 그런데 자식 FK 컬럼 ITM_ID가 맨 앞인 인덱스가 없어서(PK는 ORD_SEQ가 맨 앞이다) 자식 테이블 전체를 훑는다. 훑다가 COMMIT 전 주문 트랜잭션이 넣은 행을 만나면, 그 트랜잭션이 끝나야 판단할 수 있으므로 기다린다. V$LOCK에서 상품 화면(SID 64)은 SID 131의 TX를 REQUEST 4로 기다리고(행 락이라면 6이다), 이벤트는 enq: TX - row lock contention이다. SID 131이 넣은 상세의 상품이 1042가 아닌데도 기다린다. 131이 COMMIT하면 다음 152를 기다리는 식으로 결제창을 연 주문을 하나씩 기다리므로, 점심 피크에 1분 47초가 걸렸다. 새벽에는 열린 주문이 없어서 38ms에 끝난다. 고칠 곳은 둘이다. 먼저 저장 SQL에서 키 컬럼을 뺀다. ORM에서 ITM_ID를 갱신 대상에서 빼고(updatable = false) 바뀐 컬럼만 SET에 넣게(동적 UPDATE) 하면 UPDATE M_ITM SET UNT_PRC = :p WHERE ITM_ID = :id가 된다. 키를 고치지 않으니 FK 확인이 돌지 않고 903 → 3블록, 기다림 0이다. 둘 중 하나만 한다면 이것을 먼저 한다. 지금 증상의 직접 원인이고, 인덱스 없이도 고쳐지며, 대가가 없다. 다음으로 자식 FK 컬럼에 인덱스 CREATE INDEX IX_T_ORD_DET_ITM ON T_ORD_DET (ITM_ID) ONLINE을 만든다. 상품을 지우는 정리 작업이나 키를 다시 쓰는 다른 화면이 생겨도 그 상품의 상세만 인덱스로 확인하므로 기다리지 않는다. 대가는 주문상세 INSERT마다 인덱스 한 줄을 더 고치는 적재 비용이다. 낮에 만든다면 ONLINE이어야 만드는 동안 주문 INSERT가 줄을 서지 않는다.

해설

값이 같아도 키를 고친 것이다. 오라클은 SET에 부모 키 컬럼이 있으면 값이 바뀌었는지 따지지 않고 FK를 확인한다. ORM의 '모든 컬럼 UPDATE' 설정이나, 화면이 읽은 값을 그대로 다시 쓰는 4GL 도구에서 자주 생긴다. 그래서 "키는 안 건드린다"는 말은 실제 SQL을 보기 전까지 근거가 되지 못한다.
인덱스만 만들고 SQL을 그대로 두면 기다림은 사라지지만 저장마다 쓸데없는 FK 확인이 남고, 키 컬럼을 고치는 redo와 UPDATE OF ITM_ID 트리거 같은 부수 효과도 남는다. 반대로 SQL만 고치고 인덱스를 두지 않으면 지금 증상은 사라지지만, 상품을 지우는 정리 작업이 생기는 날 같은 줄이 다시 선다(LOCK-002의 모양).
기다림의 모양은 버전마다 다르다. 이 교재의 컨테이너(26ai Free)에서는 부모 쪽이 자식 트랜잭션을 TX 4로 하나씩 기다렸고 주문 INSERT는 막히지 않았다. 문서와 이전 버전은 자식 테이블 락을 적는다. 그 모양이라면 상품 화면의 저장 하나 뒤에 주문 INSERT 전체가 줄을 선다(교재 12화 §8).
FK를 하나씩 켤 때마다 그 FK의 부모 쪽에서 무슨 일이 일어나는지(부모를 지우는가, 키를 다시 쓰는가)를 FK 지도에 함께 적는다. 이번 FK는 '상품은 지우지 않으니 인덱스가 필요 없다'고 판단하기 쉬운 경우였다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 기다림의 모양은 교재 12화 실측(26ai Free) ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 기다림의 모양은 교재 12화 실측(26ai Free) ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황