10분마다 도는 매장 일별 판매 MV 새로 고침 잡이 유통 DB 논리 읽기 1위다

중급DDL유통373 / 700

상황

본사 영업지원팀의 「매장 일별 판매 현황」 화면은 작년에 만든 구체화 뷰 MV_SHP_DAY를 직접 읽어 바로 뜬다. 숫자가 늦지 않게 07:00 ~ 21:00에 10분마다 COMPLETE로 새로 고치는 잡을 걸어 두었다(하루 84번). DBA가 보니 AWR 논리 읽기 1위가 이 잡이고, 새로 고침 한 번에 3.4초와 redo 1.9MB를 쓴다. 판매는 10분에 평균 5건(판매상품 약 11줄) 들어온다. 오과장은 "10분 사이에 바뀌는 건 열 줄 남짓인데 왜 매번 처음부터 다시 만드나"라고 묻는다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록(전체 스캔 4,510) · SLE_NO(PK) · SLE_DT · SHP_NO
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · SLE_QTY · SLE_UPRC
MV_SHP_DAY: 3,650행(730일 × 매장 5곳) · 25블록 · 아래 DDL · 원장에 MV 로그는 없다
판매 등록은 POS 연동 프로그램이 판매 한 건(CF10 1줄 + CF11 평균 2.2줄)을 한 트랜잭션으로 넣는다 · 하루 약 700건 · 지난 판매의 수량 정정(CF11 UPDATE)이 하루 수십 건
이 MV를 읽는 것은 「매장 일별 판매 현황」 화면 하나다

원본 SQL

sql

CREATE MATERIALIZED VIEW MV_SHP_DAY
  BUILD IMMEDIATE
  REFRESH COMPLETE ON DEMAND
AS
SELECT h.SLE_DT, h.SHP_NO,
       COUNT(*)                    AS LINE_CNT,
       SUM(d.SLE_QTY)              AS QTY,
       SUM(d.SLE_QTY * d.SLE_UPRC) AS AMT
  FROM CF10 h, CF11 d
 WHERE d.SLE_NO = h.SLE_NO
 GROUP BY h.SLE_DT, h.SHP_NO;

-- 10분마다 (07:00 ~ 21:00)
BEGIN
  DBMS_MVIEW.REFRESH('MV_SHP_DAY', method => 'C');
END;

실행계획 학습용 예시

xplan

[새로 고침 한 번 · 잡 세션의 통계 차이]
  session logical reads        13,190
  db block changes              7,642
  redo size                 1,962,404
  undo change vector size     612,880
  경과 시간                       3.4초

[새로 고침 안의 INSERT … SELECT]
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |            |      1 |        |      0 |00:00:03.21 |   13120 |          |
|   1 |  LOAD TABLE CONVENTIONAL | MV_SHP_DAY |      1 |        |      0 |00:00:03.21 |   13120 |          |
|   2 |   HASH GROUP BY          |            |      1 |   3650 |   3650 |00:00:03.05 |   12714 | 1123K (0)|
|*  3 |    HASH JOIN             |            |      1 |   1096K|   1096K|00:00:02.71 |   12714 |   31M (0)|
|   4 |     TABLE ACCESS FULL    | CF10       |      1 |    500K|    500K|00:00:00.05 |    4510 |          |
|   5 |     TABLE ACCESS FULL    | CF11       |      1 |   1096K|   1096K|00:00:00.12 |    8204 |          |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("D"."SLE_NO"="H"."SLE_NO")

[그 앞의 DELETE FROM MV_SHP_DAY]  3,650행 · Buffers 70 (기본 COMPLETE 새로 고침은 지우고 다시 넣는 한 트랜잭션이다)

문제

새로 고침 한 번이 13,190블록과 redo 1.9MB를 쓰는 까닭을 COMPLETE 새로 고침이 하는 일로 설명하시오.
10분 사이에 바뀐 판매만 반영하도록 MV와 MV 로그를 다시 만들고, 빠른 새로 고침이 되려면 MV에 무엇이 더 있어야 하는지 쓰시오.
바꾼 뒤 판매 등록 쪽과 운영에서 생기는 대가를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 원장에 MV 로그 : 판매 등록과 정정이 바꾼 줄을 기록한다 (MV 가 쓰는 컬럼과 조인 키를 다 담는다)
CREATE MATERIALIZED VIEW LOG ON CF10
  WITH ROWID, SEQUENCE (SLE_NO, SLE_DT, SHP_NO) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON CF11
  WITH ROWID, SEQUENCE (SLE_NO, SLE_QTY, SLE_UPRC) INCLUDING NEW VALUES;

-- ② MV 를 빠른 새로 고침으로 다시 만든다 : SUM 마다 COUNT(식), 그리고 COUNT(*)
DROP MATERIALIZED VIEW MV_SHP_DAY;
CREATE MATERIALIZED VIEW MV_SHP_DAY
  BUILD IMMEDIATE
  REFRESH FAST ON DEMAND
AS
SELECT h.SLE_DT, h.SHP_NO,
       COUNT(*)                      AS LINE_CNT,
       COUNT(d.SLE_QTY)              AS QTY_CNT,
       SUM(d.SLE_QTY)                AS QTY,
       COUNT(d.SLE_QTY * d.SLE_UPRC) AS AMT_CNT,
       SUM(d.SLE_QTY * d.SLE_UPRC)   AS AMT
  FROM CF10 h, CF11 d
 WHERE d.SLE_NO = h.SLE_NO
 GROUP BY h.SLE_DT, h.SHP_NO;

-- 확인 : DBMS_MVIEW.EXPLAIN_MVIEW('MV_SHP_DAY') → MV_CAPABILITIES_TABLE 의 REFRESH_FAST_AFTER_ANY_DML 이 Y 인지

-- ③ 10분마다 : 바뀐 줄만
BEGIN
  DBMS_MVIEW.REFRESH('MV_SHP_DAY', method => 'F');
END;

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

xplan

[빠른 새로 고침 한 번 (10분 치 · 판매 5건, 판매상품 11줄) · 잡 세션의 통계 차이]
  session logical reads           212
  db block changes                 61
  redo size                    24,880
  경과 시간                      0.09초

[판매 등록 한 건 · MV 로그를 만든 뒤]
  INSERT CF10 1줄 + MLOG$_CF10 1줄 · INSERT CF11 2.2줄 + MLOG$_CF11 2.2줄
  트랜잭션 redo 약 2.1KB → 약 3.4KB · 하루 700건이면 약 0.9MB 늘어난다

모범 답안 서술

기본 COMPLETE 새로 고침은 바뀐 줄이 몇 줄이든 MV를 처음부터 다시 만든다. 한 트랜잭션 안에서 MV의 3,650줄을 모두 지우고(DELETE), 정의 쿼리를 다시 돌려 CF10 4,510블록과 CF11 8,204블록을 FULL로 읽고 110만 행을 해시 조인해 3,650줄로 묶은 뒤 다시 넣는다(INSERT). 그래서 한 번에 13,190블록을 읽고, 지우고 넣은 3,650줄과 그 블록의 변경이 redo 1.9MB와 undo로 남는다. 10분 사이에 바뀐 판매상품은 11줄 남짓인데 일은 전체만큼 한다. 하루 84번이면 약 110만 블록, redo 약 165MB다. 바뀐 줄만 반영하려면 빠른 새로 고침(FAST)을 쓴다. 원장 두 테이블에 MV 로그를 만들어 판매 등록과 정정이 바꾼 줄을 기록하게 하고, MV를 REFRESH FAST로 다시 만든다. 새로 고침은 MV 로그에 쌓인 줄만 읽어 원장과 맞춰 보고 MV의 해당 날짜, 매장 줄만 고친다. 10분 치면 한 번에 약 212블록, redo 약 25KB다. 하루로는 110만 블록이 약 1만 8천 블록이 된다. 빠른 새로 고침이 되려면 갖출 것이 있다. MV 로그는 WITH ROWID, SEQUENCE에 MV가 쓰는 컬럼과 조인 키를 모두 담고 INCLUDING NEW VALUES로 만든다. 집계 MV에는 COUNT(*)와 함께 SUM(식)마다 COUNT(식)을 두어야, 정정 UPDATE나 삭제가 들어와도 그 묶음의 합계를 빼고 더하며 묶음이 비었는지도 알 수 있다. 다 갖췄는지는 DBMS_MVIEW.EXPLAIN_MVIEW로 REFRESH_FAST_AFTER_ANY_DML이 Y인지 보고 확인한다. 대가도 있다. 판매 등록 트랜잭션은 이제 CF10, CF11에 넣을 때마다 MV 로그에도 한 줄씩 더 넣는다(한 건에 3줄 남짓, redo가 조금 는다). 하루 700건이라 부담은 작지만 쓰는 쪽에 일이 생긴 것이다. 새로 고침 잡이 멈추면 MV 로그가 지워지지 않고 쌓이므로 MLOG$ 줄 수를 감시한다. 빠른 새로 고침은 MV의 묶음 줄을 찾아 고치므로 MV의 (SLE_DT, SHP_NO)에 인덱스가 있는지(오라클이 만든 I_SNAP$_ 인덱스) 확인한다.

해설

잡 주기만 늘리는 답: 10분을 30분으로 늘리면 일량은 3분의 1이 되지만 숫자가 30분 늦는다. 화면 요구(10분)를 지키면서 일량을 줄이는 것은 새로 고침 방식이다.
ON COMMIT으로 바꾸지 않는다: 판매 등록이 커밋할 때마다 MV를 고치면 늘 최신이지만, 그 비용이 판매 등록 트랜잭션 안으로 들어간다(SORT-069). 10분 늦어도 되는 화면에는 ON DEMAND 빠른 새로 고침이 맞다.
MV 로그는 원장에 붙는 장치라 원장에 대량 작업(2년 치 재적재, 일괄 정정)을 할 때도 그만큼 쌓인다. 그런 날은 작업 뒤 MV를 COMPLETE로 한 번 다시 만들고 MV 로그를 비우는 절차를 정해 둔다.
COMPLETE 새로 고침의 DELETE와 INSERT 비용을 줄이는 다른 방법(atomic_refresh => FALSE)은 SORT-070에서 다룬다. 낮 동안 사람들이 읽는 이 MV에는 비어 보이는 순간이 생겨 맞지 않는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황