점심 피크마다 POS 결제 저장이 몇 초씩 멈추는데, 기다리는 상대가 같은 버거를 판 다른 매장이다

실전PLSQL버거480 / 700

상황

버거원 본사의 「실시간 메뉴 판매판」은 전 매장의 오늘 상품별 판매 수량을 보여 준다. 2년 전 ORD_ITEM에 행 트리거를 달아, 주문상품이 들어올 때마다 합계 테이블 ITEM_DAY_QTY의 그 상품 행에 수량을 더하게 했다. 이 문제에서 POS는 첫 상품을 찍을 때부터 ORD와 ORD_ITEM에 넣기 시작해 결제가 끝나면 COMMIT한다(보통 20 ~ 40초). 점심 피크(11:30 ~ 13:30)에 결제 저장이 몇 초씩 멈춘다는 매장 문의가 늘었고, 월말에 POS 업체가 하루치를 다시 보내 오면 그 적재도 느리다. 개발팀은 두 안을 냈다. (가) 트리거 본문을 자율 트랜잭션으로 바꿔 합계를 바로 COMMIT한다. (나) 복합 트리거(COMPOUND TRIGGER)로 바꿔 문장이 끝날 때 상품별로 모아 한 번에 더한다.

테이블과 인덱스

ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · 하루 약 3,400행 · ORD_ITEM_PK (ORD_NO, ITEM_ID) · ORD_ITEM_X01 (ORD_DT, ORD_HMS)
ITEM 상품: 92행 · 인기 상품 상위 5개가 판매 수량의 절반이다.
ITEM_DAY_QTY 합계(문제 설정): ORD_DT · ITEM_ID · QTY · PK (ORD_DT, ITEM_ID) · 하루 92행 이하
판매판은 본사 화면 8개가 1분마다 새로 고친다. MD팀은 "1분쯤 늦는 것은 괜찮다"고 확인했다.
매장 200곳 · 계산대 약 450대. 03:00 배치 창에는 POS 입력이 없다.

원본 SQL

sql

-- 2년 전 · 판매판 합계 트리거
CREATE OR REPLACE TRIGGER TRG_ORD_ITEM_QTY
AFTER INSERT ON ORD_ITEM
FOR EACH ROW
BEGIN
  MERGE INTO ITEM_DAY_QTY q
  USING (SELECT :NEW.ORD_DT AS ORD_DT, :NEW.ITEM_ID AS ITEM_ID FROM DUAL) s
     ON (q.ORD_DT = s.ORD_DT AND q.ITEM_ID = s.ITEM_ID)
   WHEN MATCHED THEN UPDATE SET q.QTY = q.QTY + :NEW.ORD_ITEM_QTY
   WHEN NOT MATCHED THEN INSERT (ORD_DT, ITEM_ID, QTY)
        VALUES (s.ORD_DT, s.ITEM_ID, :NEW.ORD_ITEM_QTY);
END;

-- 판매판 (1분마다)
SELECT ITEM_ID, QTY
  FROM ITEM_DAY_QTY
 WHERE ORD_DT = TO_CHAR(SYSDATE, 'YYYYMMDD');

실행계획 학습용 예시 · 판매판 새 SQL의 Buffers는 교재 14화 규모(하루 약 3,400행)

xplan

[ASH — 점심 피크 · POS 세션의 대기]
  EVENT                             비율    대상
  enq: TX - row lock contention      61%    ITEM_DAY_QTY 의 상위 5개 상품 행 (I001 빅버거 세트 · I014 콜라 …)
  CPU · log file sync 등             39%
  POS 결제 저장 시간                 평소 0.05 초 · 피크 p95 2.4 초 · 최대 31 초

[V$SESSION — 12:14:05 표본]
  SID   PROGRAM      EVENT                            SECS   BLOCKING_SESSION   하고 있는 문장
  412   POS_0071_1   enq: TX - row lock contention       3   388                MERGE INTO ITEM_DAY_QTY …
  388   POS_0133_2   SQL*Net message from client        24                      (손님이 결제 중 · 트랜잭션을 연 채)

[월말 재전송 — 하루치 3,418행 다시 넣기 · TKPROF]
  INSERT INTO ORD_ITEM … SELECT … FROM STG_ORD_ITEM      Execute 1       rows 3,418
  MERGE INTO ITEM_DAY_QTY … (트리거 안)                  Execute 3,418
  경과 1.9 초 (트리거를 끈 시험 DB 0.2 초)

[판매판 — 지금] ITEM_DAY_QTY 에서 읽기 · Buffers 3

문제

점심 피크의 POS 대기와 재전송 적재의 1.9초가 각각 트리거의 어떤 성질에서 나오는지 설명하시오.
개발팀의 (가)와 (나)를 평가하고, 판매판을 유지하는 방법을 SQL로 작성하시오.
고친 방법의 대가와, 운영에서 바꿀 때의 순서를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 합계를 행마다 고치는 트리거를 없앤다 → POS 트랜잭션이 인기 상품 합계 행을 잡지 않는다
DROP TRIGGER TRG_ORD_ITEM_QTY;

-- ② 판매판 (1분마다) : 오늘 주문상품을 그때 센다 — ORD_ITEM_X01 범위 · 하루가 끝날 무렵에도 약 120블록
SELECT ITEM_ID, SUM(ORD_ITEM_QTY) AS QTY
  FROM ORD_ITEM
 WHERE ORD_DT = TO_CHAR(SYSDATE, 'YYYYMMDD')
 GROUP BY ITEM_ID;

-- ③ 지난 날짜 합계는 03:00 배치가 하루 한 번 한 문장으로 만든다 (재전송이 있던 날은 그날을 다시)
DELETE FROM ITEM_DAY_QTY WHERE ORD_DT = :v_dt;
INSERT INTO ITEM_DAY_QTY (ORD_DT, ITEM_ID, QTY)
SELECT ORD_DT, ITEM_ID, SUM(ORD_ITEM_QTY)
  FROM ORD_ITEM
 WHERE ORD_DT = :v_dt
 GROUP BY ORD_DT, ITEM_ID;
COMMIT;

개선 후 실행계획 학습용 예시 · 판매판 새 SQL의 Buffers는 교재 14화 규모(하루 약 3,400행)

xplan

[판매판 새 SQL — 23:50 (그날 3,412행)]
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |              |      1 |        |     88 |00:00:00.01 |     118 |
|   1 |  HASH GROUP BY                        |              |      1 |     92 |     88 |00:00:00.01 |     118 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED | ORD_ITEM     |      1 |   3400 |   3412 |00:00:00.01 |     118 |
|*  3 |    INDEX RANGE SCAN                   | ORD_ITEM_X01 |      1 |   3400 |   3412 |00:00:00.01 |      16 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ORD_DT"=TO_CHAR(SYSDATE@!,'YYYYMMDD'))

[바꾼 뒤]
  POS 결제 저장  피크 p95 2.4 초 → 0.06 초 · enq: TX - row lock contention 0
  판매판         화면 8개 × 1분 = 분당 약 950블록
  월말 재전송    1.9 → 0.2 초 (트리거 없음) · 그날 합계는 03:00 에 다시 만든다

모범 답안 서술

두 증상은 같은 트리거의 다른 성질에서 나온다. 첫째, 행 트리거의 MERGE는 POS 트랜잭션 안에서 돈다. 그래서 POS가 빅버거 세트를 찍는 순간 ITEM_DAY_QTY의 그 상품 행을 고치고, 손님이 결제를 마쳐 COMMIT할 때까지(20 ~ 40초) 그 행의 잠금을 쥔다. 판매 수량의 절반이 상위 5개 상품에 몰려 있으므로, 같은 버거를 파는 다른 매장 POS가 그 합계 행 앞에서 줄을 선다. ASH의 61%가 enq: TX - row lock contention이고, 412번 POS가 결제 중인 388번 POS를 기다리는 표본이 그 모습이다. 둘째, 행 트리거는 넣는 행마다 돈다. 월말 재전송은 INSERT 한 문장이지만 트리거 안 MERGE가 3,418번 실행되어 0.2초 일이 1.9초가 됐다. (가) 자율 트랜잭션은 잠금 시간을 줄이지만 숫자를 틀리게 만든다. 합계를 먼저 커밋해 버리므로, 손님이 결제를 취소해 POS가 ROLLBACK해도 더한 수량은 되돌아가지 않는다. 행마다 커밋하는 비용도 붙고, 인기 상품 행을 두고 짧게 줄을 서는 일은 남는다. (나) 복합 트리거는 한 문장이 넣는 여러 행을 모아 상품별로 한 번에 더하므로 재전송처럼 여러 행을 넣는 문장에는 이득이다. 하지만 POS는 상품을 찍을 때마다 한두 행씩 넣으므로 모을 것이 없고, 합계 행을 커밋까지 잡는 잠금은 그대로다. 둘 다 점심 피크를 풀지 못한다. 그래서 합계를 넣을 때마다 고치는 구조 자체를 없앤다. 트리거를 지우고, 판매판은 1분마다 오늘 주문상품을 ORD_ITEM에서 바로 센다. ORD_ITEM_X01로 오늘 범위만 읽으므로 하루가 끝날 무렵에도 118블록이고, 화면 8개가 1분마다 읽어도 분당 약 950블록이다. 지난 날짜의 합계는 03:00 배치가 하루 한 번 한 문장으로 만들고, 재전송이 있던 날은 그날 합계를 지우고 다시 만든다. POS 트랜잭션은 이제 공유 행을 하나도 고치지 않으므로 행 잠금 대기가 사라진다. 대가도 적는다. 판매판이 최대 1분 늦다. MD팀이 1분쯤 늦어도 괜찮다고 확인했으므로 요구 안이다. 판매판 조회가 3블록에서 118블록으로 늘지만 분당 950블록은 무시할 만하다. 운영에서 바꿀 때는 판매판 SQL을 먼저 ORD_ITEM 집계로 바꿔 숫자가 기존 합계와 맞는지 하루 동안 대조하고, 그다음 트리거를 지운다. 순서를 거꾸로 하면 그사이 판매판 숫자가 멈춘다.

해설

원리: 여러 세션이 함께 쓰는 '합계 한 행'을 트랜잭션 안에서 고치면, 그 행이 모든 트랜잭션의 줄 서는 자리가 된다. 트랜잭션이 길수록(POS는 손님 결제까지) 줄이 길다. 교재 12화의 채번 테이블과 같은 모양이다.
다른 답: ON COMMIT 빠른 새로 고침 구체화 뷰(MV)도 합계를 자동으로 유지한다. 하지만 MV 로그에 행마다 한 줄을 쓰고, 커밋할 때마다 새로 고침이 같은 MV를 두고 줄을 서므로 POS의 커밋이 느려진다. 1분 지연이 허용되면 MV를 1분 주기 COMPLETE 새로 고침으로 두는 것은 이 문제의 답과 같은 일이다.
오늘 주문상품이 수만 행으로 늘어 판매판 조회가 무거워지면 (ORD_DT, ITEM_ID, ORD_ITEM_QTY) 커버링 인덱스나 5분 단위 중간 합계 배치를 검토한다. 그때도 POS 트랜잭션 안에서 공유 행을 고치지 않는다는 원칙은 지킨다.
트리거를 지우기 전에 다른 프로그램이 ITEM_DAY_QTY를 읽는지(DBA_DEPENDENCIES, AWR의 SQL) 확인한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 판매판 새 SQL의 Buffers는 교재 14화 규모(하루 약 3,400행) ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 판매판 새 SQL의 Buffers는 교재 14화 규모(하루 약 3,400행) ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황