본사 전광판 숫자가 결제 즉시 바뀌게 된 다음 날부터 점심 결제 저장이 1초를 넘긴다

실전DDL버거374 / 700

상황

본사 운영실의 「오늘 매장별 주문 전광판」은 매장 200곳의 오늘 주문 건수를 띄운다. 예전에는 전광판 프로그램이 1분마다 원장을 세었는데, 운영실이 결제가 끝났는데도 숫자가 늦게 바뀐다고 해서 지난주 금요일 조민우가 REFRESH FAST ON COMMIT 구체화 뷰로 바꾸고 전광판이 10초마다 MV를 읽게 했다. 월요일 점심 피크(11:30 ~ 13:00)에 POS 결제 저장이 평소 0.01초 안팎에서 1 ~ 2초로 늘었다. 박팀장은 세 안을 비교해 하나를 골라 달라고 했다. ㉠ MV를 ON DEMAND로 바꿔 1분마다 빠른 새로 고침을 한다. ㉡ MV를 없애고 전광판이 오늘 주문만 원장에서 센다. ㉢ 매장별 오늘 건수 표를 두고 POS가 결제할 때 함께 UPDATE한다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_NO(PK) · ORD_DT VARCHAR2(8) · ORD_HMS · SHOP_NO 200곳 · 날짜순으로 쌓인다 · 이 문제의 운영일에는 오늘 주문이 13시까지 약 9,000건이다(점심 피크 1분에 300건 남짓)
인덱스 ORD_PK (ORD_NO) · ORD_X01 (ORD_DT, ORD_HMS): BLEVEL 2 · 리프 약 3,500블록 · CF 22,223 · 통계 최신
SHOP 매장: 200행 · 5블록 · PK (SHOP_NO)
MV_SHOP_DAY(지난 금요일): 아래 DDL · 146,000행(730일 × 200곳) · 590블록 · 오라클이 만든 I_SNAP$_MV_SHOP_DAY (ORD_DT, SHOP_NO) 인덱스 · ORD에 MV 로그
POS 결제 저장은 ORD 1줄과 ORD_ITEM 1 ~ 4줄을 넣고 카드 승인 뒤 COMMIT한다 · 전광판 PC는 6대

원본 SQL

sql

-- 지난주 금요일 조민우
CREATE MATERIALIZED VIEW LOG ON ORD
  WITH ROWID, SEQUENCE (ORD_DT, SHOP_NO) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW MV_SHOP_DAY
  BUILD IMMEDIATE
  REFRESH FAST ON COMMIT
AS
SELECT ORD_DT, SHOP_NO, COUNT(*) AS ORD_CNT
  FROM ORD
 GROUP BY ORD_DT, SHOP_NO;

-- 전광판 (PC 6대 · 10초마다)
SELECT s.SHOP_NO, s.SHOP_NM, NVL(m.ORD_CNT, 0) AS ORD_CNT
  FROM SHOP s, MV_SHOP_DAY m
 WHERE m.SHOP_NO(+) = s.SHOP_NO
   AND m.ORD_DT(+)  = :today
 ORDER BY s.SHOP_NO

실행계획 학습용 예시

xplan

[POS 결제 저장 한 건 · 점심 피크 평균 (애플리케이션 로그)]
                           지난주 금요일(MV 전)   이번 주 월요일(ON COMMIT MV)
  INSERT ORD                     0.4 ms                 0.6 ms   (MV 로그 1줄 더)
  INSERT ORD_ITEM (2.5줄)        0.9 ms                 0.9 ms
  COMMIT                         3.1 ms             1,240 ms

[AWR · 월요일 11:30 ~ 13:00 · Top 대기]
  enq: JI - contention     waits 24,180 · 평균 0.94초 · DB time의 63%
  log file sync            waits 27,355 · 평균 4ms

[전광판 SQL · MV]
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                     |      1 |        |    200 |00:00:00.01 |      12 |          |
|   1 |  SORT ORDER BY                |                     |      1 |    200 |    200 |00:00:00.01 |      12 |18432  (0)|
|*  2 |   HASH JOIN OUTER             |                     |      1 |    200 |    200 |00:00:00.01 |      12 | 1316K (0)|
|   3 |    TABLE ACCESS FULL          | SHOP                |      1 |    200 |    200 |00:00:00.01 |       5 |          |
|   4 |    MAT_VIEW ACCESS BY INDEX ROWID| MV_SHOP_DAY      |      1 |    200 |    198 |00:00:00.01 |       7 |          |
|*  5 |     INDEX RANGE SCAN          | I_SNAP$_MV_SHOP_DAY |      1 |    200 |    198 |00:00:00.01 |       3 |          |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("M"."SHOP_NO"="S"."SHOP_NO")
   5 - access("M"."ORD_DT"=:TODAY)

문제

전광판을 MV로 바꾼 뒤 POS 결제 저장의 COMMIT이 3.1ms에서 1,240ms가 된 까닭을 ON COMMIT 새로 고침이 하는 일과 AWR의 대기로 설명하시오.
㉠, ㉡, ㉢ 세 안을 전광판의 일량, 결제 쪽 비용, 숫자가 늦는 정도, 정합성으로 비교하시오.
하나를 골라 SQL과 DDL로 쓰고, 고른 근거를 숫자로 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ㉡ : 결제 커밋에서 MV 새로 고침을 뗀다 · MV 와 MV 로그를 함께 없앤다 (로그만 남기면 결제마다 아무도 안 읽는 로그를 쓴다)
DROP MATERIALIZED VIEW MV_SHOP_DAY;
DROP MATERIALIZED VIEW LOG ON ORD;

-- 전광판 : 오늘 주문만 ORD_X01 의 한 구간으로 센다 (13시 기준 약 9,000건 · 날짜순으로 쌓여 오늘 치 블록만 읽는다)
SELECT s.SHOP_NO, s.SHOP_NM, NVL(t.ORD_CNT, 0) AS ORD_CNT
  FROM SHOP s,
       (SELECT SHOP_NO, COUNT(*) AS ORD_CNT
          FROM ORD
         WHERE ORD_DT = :today
         GROUP BY SHOP_NO) t
 WHERE t.SHOP_NO(+) = s.SHOP_NO
 ORDER BY s.SHOP_NO;

개선 후 실행계획 학습용 예시

xplan

[전광판 · 고친 뒤 · 13시]
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |         |      1 |        |    200 |00:00:00.01 |     239 |          |
|   1 |  SORT ORDER BY                          |         |      1 |    200 |    200 |00:00:00.01 |     239 |18432  (0)|
|*  2 |   HASH JOIN OUTER                       |         |      1 |    200 |    200 |00:00:00.01 |     239 | 1316K (0)|
|   3 |    TABLE ACCESS FULL                    | SHOP    |      1 |    200 |    200 |00:00:00.01 |       5 |          |
|   4 |    VIEW                                 |         |      1 |    200 |    198 |00:00:00.01 |     234 |          |
|   5 |     HASH GROUP BY                       |         |      1 |    200 |    198 |00:00:00.01 |     234 | 1394K (0)|
|   6 |      TABLE ACCESS BY INDEX ROWID BATCHED| ORD     |      1 |   9000 |   9012 |00:00:00.01 |     234 |          |
|*  7 |       INDEX RANGE SCAN                  | ORD_X01 |      1 |   9000 |   9012 |00:00:00.01 |      34 |          |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T"."SHOP_NO"="S"."SHOP_NO")
   7 - access("ORD_DT"=:TODAY)

[POS 결제 저장 · 고친 뒤 점심 피크]  COMMIT 3.1 ms 안팎 · enq: JI - contention 0

모범 답안 서술

ON COMMIT 빠른 새로 고침은 따로 도는 잡이 아니라, 원장을 바꾼 트랜잭션이 커밋할 때 그 자리에서 MV를 고치는 방식이다. 그래서 POS 결제 저장의 COMMIT 안에서 MV 로그를 읽고 MV_SHOP_DAY의 그 매장 줄을 고치는 일까지 끝나야 결제가 끝난다. 게다가 같은 MV를 고치는 커밋끼리는 한 번에 하나씩만 들어갈 수 있어서 줄을 선다. 그 줄이 AWR 1위 대기 enq: JI - contention이다. 1분에 300건씩 커밋하는 점심 피크에는 커밋 하나가 평균 0.94초를 기다려 COMMIT이 3.1ms에서 1,240ms가 되었다. 전광판 한 번을 12블록으로 줄인 대가를 점심 결제 2만 7천 건이 나눠 낸 셈이다. 세 안을 견준다. ㉠ ON DEMAND로 바꾸고 1분마다 빠른 새로 고침을 하면 커밋의 줄은 사라진다. 하지만 결제마다 MV 로그에 한 줄씩 쓰는 일은 남고, 숫자가 최대 1분 늦어 운영실이 처음 불평한 상태로 돌아간다. ㉡ MV를 없애고 전광판이 오늘 주문만 세면, 오늘 주문 약 9,000건은 ORD_X01의 ORD_DT = :today 한 구간이고 날짜순으로 쌓인 테이블이라 오늘 치 블록 약 200개에 모여 있어 한 번에 239블록이다. 전광판 6대가 10초마다 읽어도 1초에 140블록 남짓이고, 결제 쪽에는 아무 일도 더하지 않으며 숫자는 늘 지금이다. ㉢ 매장별 건수 표를 결제 때 함께 UPDATE하면 읽기는 가장 싸지만 POS 코드를 고쳐야 하고, 같은 매장의 결제끼리 그 행의 락을 기다리며, 결제 취소나 실패 처리에서 한 번이라도 빠지면 원장과 어긋난다. ON COMMIT MV에서 겪은 '커밋 안의 일'과 '줄'을 애플리케이션으로 옮긴 것일 뿐이다. 그래서 ㉡을 고른다. 오늘 하루 치는 작고 인덱스의 한 구간이라 미리 계산해 둘 까닭이 없다. COMMIT은 1,240ms → 3ms 안팎으로 돌아가고 JI 대기는 사라진다. 전광판은 12 → 239블록이 되지만 결제 쪽 비용은 0이다. 전광판 일량을 더 줄이려면 주기를 30초로 늘리거나(운영실에 물어 본다), (ORD_DT, SHOP_NO) 인덱스로 테이블 방문을 없앨 수 있다. 그 인덱스는 결제마다 고칠 항목이 하나 늘어나는 대가가 있어 지금은 만들지 않는다.

해설

ON COMMIT MV가 맞는 자리: 원장이 드물게 바뀌고(마스터, 코드) 읽기가 아주 잦을 때다. 1분에 수백 번 커밋되는 OLTP 원장에 붙이면 보고서 비용을 모든 트랜잭션이 나눠 낸다. 원장에 무엇을 붙일 때는 읽는 쪽 이득과 쓰는 쪽 비용을 함께 재야 한다(교재 14화 §1).
평균으로는 늦게 보인다: 하루 평균 커밋 시간은 수십 ms라 눈에 띄지 않을 수 있다. 줄은 피크에만 길어지므로 점심 90분을 잘라서 본다(교재 17화 §4).
MV를 없앨 때는 MV 로그도 함께 지운다. MV만 지우고 로그를 남기면 결제마다 아무도 읽지 않는 로그를 계속 쓰고, 로그는 지워지지 않고 쌓인다.
LOCK-023에서는 비트맵 인덱스가 POS INSERT를 막았다. 리포트를 빠르게 하려고 원장에 붙인 장치가 OLTP를 막는 모양은 같고, 장치만 다르다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황