매장 리포트를 빠르게 했다는 다음 날 점심, POS 결제가 수십 초씩 멈춘다

중급INSERT버거518 / 700

상황

버거 본사 BI팀은 월요일 밤 「매장별 일 매출」 리포트를 빠르게 하려고 ORD에 인덱스 두 개를 만들었다(리포트 22,422 → 380블록). 화요일 11:50부터 전국 매장 POS의 결제 완료가 수십 초씩 걸렸고, 점장들의 전화가 본사로 몰렸다. 운영팀은 "DB CPU는 20%라 한가하다"고 했다. POS는 결제 버튼을 누르면 주문과 주문상품을 넣고, 카드 승인 응답을 받은 뒤 COMMIT한다(보통 1~2초). 점심 피크에는 매장 200곳이 1분에 300건 남짓 결제한다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_NO NUMBER · ORD_DT VARCHAR2(8) · SHOP_NO VARCHAR2(10) ('SH0001' ~ 'SH0200') · ORD_PK (ORD_NO) · ORD_X01 (ORD_DT, ORD_HMS)
월요일 밤 BI팀이 만든 인덱스: ORD_BX01 BITMAP (ORD_DT) · ORD_BX02 BITMAP (SHOP_NO)
ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) · ORD_ITEM_X01 (ORD_DT, ORD_HMS) (비트맵 없음)
리포트는 하루 두 번(09:00, 18:00) 본사에서 돈다. 지역 매장 묶음(5~20곳)과 기간(한 달)으로 매장별 일 주문 건수를 센다. 매장 하나의 하루 주문은 7건 안팎이다.

원본 SQL

sql

-- POS 결제 (매장 200곳 · 계산대마다)
INSERT INTO ORD (ORD_NO, ORD_DT, ORD_HMS, SHOP_NO)
VALUES (:ord_no, TO_CHAR(SYSDATE, 'YYYYMMDD'), TO_CHAR(SYSDATE, 'HH24MISS'), :shop_no);
INSERT INTO ORD_ITEM (ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS)
VALUES (:ord_no, :item_id, :qty, TO_CHAR(SYSDATE, 'YYYYMMDD'), TO_CHAR(SYSDATE, 'HH24MISS'));   -- 상품마다
-- 카드 승인 응답을 기다린다 (1 ~ 2초)
COMMIT;

-- 「매장별 일 매출」 리포트 (하루 두 번)
SELECT o.SHOP_NO, o.ORD_DT, COUNT(*) AS ord_cnt
  FROM ORD o
 WHERE o.SHOP_NO IN (:s1, :s2, :s3, :s4, :s5)
   AND o.ORD_DT BETWEEN :from_dt AND :to_dt
 GROUP BY o.SHOP_NO, o.ORD_DT;

-- 월요일 밤 BI 팀
CREATE BITMAP INDEX ORD_BX01 ON ORD (ORD_DT);
CREATE BITMAP INDEX ORD_BX02 ON ORD (SHOP_NO);

실행계획 학습용 예시 · ASH 읽는 법은 교재 17화

xplan

[ASH — 화요일 11:50 ~ 12:20 · 상위 이벤트]
EVENT                              표본     %     SQL
enq: TX - row lock contention      4,812   81%   INSERT INTO ORD (ORD_NO, ORD_DT, ORD_HMS, SHOP_NO) VALUES (:1, …)
CPU                                  702   12%
log file sync                        298    5%
(INSERT INTO ORD_ITEM 의 enq: TX 표본은 0 이다)

[V$LOCK — 12:05:12 · POS 세션 일부]
SID   TYPE  대상                   LMODE  REQUEST  BLOCK
407   TM    ORD                      3      0        0
407   TX    SID 407 의 트랜잭션        6      0        1      ← SH0081 · 카드 승인 응답을 기다리는 중 (COMMIT 전)
512   TX    SID 407 의 트랜잭션        0      4        0      ← SH0117 의 주문
388   TX    SID 407 의 트랜잭션        0      4        0      ← SH0042 의 주문
611   TX    SID 512 의 트랜잭션        0      4        0      ← SH0117 의 다른 계산대
      … REQUEST 4 로 기다리는 POS 세션 41개

[POS 결제 한 건 — 점심 피크]
  지난주 화요일   평균 1.4초 (거의 카드 승인)
  이번 화요일     평균 23초 · 최대 71초 · 결제 포기(타임아웃 60초) 186건

[리포트 — BI팀의 측정]  인덱스 전 TABLE ACCESS FULL 22,422블록 → 인덱스 뒤 BITMAP AND · 380블록

문제

ASH와 V$LOCK을 근거로 서로 다른 매장의 주문 INSERT가 왜 한 줄로 서는지 설명하시오. ORD_ITEM INSERT는 왜 기다리지 않는지도 쓰시오.
POS가 기다리지 않게 고치고, 리포트를 위한 인덱스를 다시 설계하시오. 그 인덱스의 대가도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- POS 를 막는 비트맵 인덱스 둘을 없앤다
DROP INDEX ORD_BX01;
DROP INDEX ORD_BX02;

-- 리포트는 B-Tree 결합 인덱스로: 매장(IN 목록 · 등치) → 날짜(범위) · 건수는 인덱스만으로 센다
CREATE INDEX ORD_X02 ON ORD (SHOP_NO, ORD_DT) ONLINE;

-- 리포트 SQL 은 그대로
SELECT o.SHOP_NO, o.ORD_DT, COUNT(*) AS ord_cnt
  FROM ORD o
 WHERE o.SHOP_NO IN (:s1, :s2, :s3, :s4, :s5)
   AND o.ORD_DT BETWEEN :from_dt AND :to_dt
 GROUP BY o.SHOP_NO, o.ORD_DT;

개선 후 실행계획 학습용 예시 · ASH 읽는 법은 교재 17화

xplan

[리포트 — 고친 뒤 (학습용 예시) · 매장 5곳 · 한 달]
------------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |      1 |        |    150 |00:00:00.01 |      19 |
|   1 |  SORT GROUP BY NOSORT |         |      1 |    150 |    150 |00:00:00.01 |      19 |
|   2 |   INLIST ITERATOR     |         |      1 |        |   1046 |00:00:00.01 |      19 |
|*  3 |    INDEX RANGE SCAN   | ORD_X02 |      5 |   1027 |   1046 |00:00:00.01 |      19 |
------------------------------------------------------------------------------------------
   3 - access((("O"."SHOP_NO"=:S1 OR "O"."SHOP_NO"=:S2 OR "O"."SHOP_NO"=:S3 OR "O"."SHOP_NO"=:S4
              OR "O"."SHOP_NO"=:S5)) AND "O"."ORD_DT">=:FROM_DT AND "O"."ORD_DT"<=:TO_DT)

  리포트  BITMAP AND 380블록 → 19블록 (테이블에 가지 않는다)
  POS     결제 평균 23초 → 1.4초 · enq: TX 기다림 0

모범 답안 서술

ASH를 보면 점심 30분 표본의 81%가 INSERT INTO ORD의 enq: TX - row lock contention이고, V$LOCK에서 서로 다른 매장(SH0117, SH0042)의 POS가 SH0081 POS(SID 407)의 TX를 REQUEST 4로 기다린다. 새 주문을 넣는 것이라 잠긴 행이 있을 리 없는데 기다리는 것이고, 행 락이라면 6이어야 한다. 이들이 기다리는 것은 월요일 밤에 생긴 비트맵 인덱스의 항목이다. 비트맵 인덱스의 항목 하나는 키 하나와 ROWID 범위의 비트로 되어 있어 수많은 행을 덮는다. 오늘 들어오는 주문은 모두 ORD_DT가 같은 값이므로 INSERT마다 ORD_BX01의 같은 항목을 고쳐야 하고, 비트 하나만 잠글 수 없어 항목 전체를 잠근다. 그 락은 COMMIT까지 가는데, POS는 카드 승인 응답(1~2초)을 받은 뒤에 COMMIT한다. 그래서 전국 200곳의 결제가 앞사람의 카드 승인을 기다리며 한 줄로 섰다. ORD_BX02(SHOP_NO)는 같은 매장 계산대끼리 같은 줄을 하나 더 만든다(611이 512를 기다린다). CPU가 20%로 한가한 것은 일이 없어서가 아니라 줄을 서 있어서다. ORD_ITEM에는 비트맵 인덱스가 없다. PK와 B-Tree 인덱스는 항목 하나가 행 하나라서 INSERT는 자기 행만 잠그므로 기다리지 않는다(ASH 표본 0). 고친 판은 비트맵 인덱스 둘을 없애고, 리포트를 위해 B-Tree 결합 인덱스 ORD (SHOP_NO, ORD_DT)를 ONLINE으로 만든다. 등치 조건인 매장을 앞에, 범위 조건인 날짜를 뒤에 두면 매장마다 한 달 범위만 읽고, 건수는 인덱스만으로 센다. 리포트는 380 → 19블록으로 더 줄고, POS 결제는 평균 23초 → 1.4초로 돌아온다. 대가는 POS INSERT마다 인덱스 한 줄을 더 고치는 일이다. ORD에는 이미 PK와 ORD_X01이 있으므로 세 번째 인덱스가 된다. 리프는 약 2,800블록이다. 비트맵과 달리 행 단위로 잠그므로 동시 INSERT를 줄 세우지 않는다. 낮에 만든다면 ONLINE이어야 만드는 동안 POS INSERT가 TM 줄에 서지 않는다.

해설

인덱스를 만드는 일도 변경이다. 리포트를 380블록으로 줄인 측정은 맞았다. 하지만 그 인덱스가 POS INSERT에 무엇을 하는지는 재지 않았다. 인덱스를 만드는 승인서에는 읽는 쪽 이득과 함께 쓰는 쪽(동시 DML) 비용을 같이 적는다(교재 14화).
평균 지표가 한가해 보여도 줄은 설 수 있다. CPU 20%는 '일이 적다'가 아니라 '모두 기다리고 있다'일 수 있다. 시간 축으로 자른 ASH 표본과 대기 이벤트로 확인한다(교재 17화).
비트맵 인덱스가 정말 필요한 리포트(여러 저카디널리티 컬럼을 자유롭게 조합하는 분석)라면, POS가 쓰는 테이블이 아니라 밤마다 적재하는 리포트용 사본이나 DW 쪽 테이블에 만든다.
비트맵 인덱스가 있는 테이블의 동시 DML은 ORA-00060으로도 나타난다. INSERT는 비트맵 인덱스 둘의 항목을 세션마다 다른 순서로 잡아서 원이 생긴다. 비트맵 컬럼의 값을 바꾸는 UPDATE(예: 주문 상태를 PENDING에서 COMPLETE로)는 인덱스가 하나뿐이어도 한 행마다 옛 값의 항목과 새 값의 항목 둘을 잠근다. 그래서 상태를 반대 방향으로 바꾸는 세션(결제 완료와 결제 취소)이나 여러 행을 고치는 트랜잭션끼리 두 항목을 엇갈린 순서로 잡으면, 서로 다른 행을 고쳐도 데드락이 난다. 어느 쪽이든 처방은 같다(OLTP 테이블에서 비트맵 인덱스를 없앤다).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · ASH 읽는 법은 교재 17화 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · ASH 읽는 법은 교재 17화 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황