밤 체인의 고객 월별 구매 MV 새로 고침이 38초 걸리고 redo를 61MB 남긴다

기초PLSQL한빛유통375 / 700

상황

CRM팀의 「고객 월별 구매」 화면과 캠페인 추출은 구체화 뷰 MV_CUS_MON(고객 2,000명 × 36개월 = 72,000행)을 직접 읽는다. 밤 체인의 02:00 단계가 이 MV를 기본 옵션 그대로 COMPLETE로 새로 고친다. 체인이 아침을 넘기는 날이 생겨 단계마다 시간을 재 보니 이 단계가 38초였고, 아카이브 로그도 이 단계에서 61MB가 생겼다. 원장 T_ORD_BIG을 한 번 읽어 묶는 데는 1초도 걸리지 않는다. 이 MV를 읽는 화면과 추출은 08시부터 돈다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록
MV_CUS_MON: 72,000행 · 410블록 · CUS_ID · ORD_YM · ORD_CNT · ORD_AMT · 인덱스 IX_MV_CUS_MON (CUS_ID, ORD_YM) · REFRESH COMPLETE ON DEMAND · LOGGING
정의 쿼리: SELECT CUS_ID, TO_CHAR(ORD_DT, 'YYYYMM') AS ORD_YM, COUNT(*) AS ORD_CNT, SUM(ORD_AMT) AS ORD_AMT FROM T_ORD_BIG GROUP BY CUS_ID, TO_CHAR(ORD_DT, 'YYYYMM')
DB는 ARCHIVELOG 모드다 · 02:00 ~ 08:00에는 이 MV를 읽는 세션이 없다

원본 SQL

sql

-- 밤 체인 02:00 단계 (기본 옵션 그대로)
BEGIN
  DBMS_MVIEW.REFRESH(list => 'MV_CUS_MON', method => 'C');
END;

실행계획 학습용 예시

xplan

[02:00 단계 · TKPROF 요약 (새로 고침이 낸 재귀 SQL)]
  문장                                        count     cpu   elapsed     query    current      rows
  DELETE FROM "SQLP"."MV_CUS_MON"                 1    9.84     14.92       418    221,903    72,000
  INSERT INTO "SQLP"."MV_CUS_MON" SELECT …        1   11.20     22.37     6,004    219,377    72,000
  그 밖의 재귀 SQL (사전 조회 · 기록)             38    0.41      0.68     1,212         96         —

[같은 단계 · 세션 통계 차이]
  redo size                64,118,220
  undo change vector size  19,604,312
  db block changes            447,206

[INSERT … SELECT 의 실행계획]
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |            |      1 |        |      0 |00:00:22.37 |     225K|          |
|   1 |  LOAD TABLE CONVENTIONAL | MV_CUS_MON |      1 |        |      0 |00:00:22.37 |     225K|          |
|   2 |   HASH GROUP BY          |            |      1 |  72000 |  72000 |00:00:00.61 |    5826 | 5243K (0)|
|   3 |    TABLE ACCESS FULL     | T_ORD_BIG  |      1 |   1000K|   1000K|00:00:00.08 |    5826 |          |
-------------------------------------------------------------------------------------------------------------

문제

72,000행짜리 MV를 다시 만드는 데 redo가 61MB 생기고 38초가 걸리는 까닭을 TKPROF의 두 문장으로 설명하시오.
같은 MV를 같은 시각에 다시 만들되 redo와 시간을 줄이도록 새로 고침 호출을 고치시오.
고친 방식의 대가를 쓰고, 02시에 이 MV를 읽는 화면이 있었다면 어떻게 해야 하는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 02:00 단계 : 원자적이지 않은 COMPLETE → MV 를 TRUNCATE 하고 직접 경로(APPEND)로 넣은 뒤 인덱스를 다시 만든다
BEGIN
  DBMS_MVIEW.REFRESH(list => 'MV_CUS_MON', method => 'C', atomic_refresh => FALSE);
END;
-- 단계가 실패하면 MV 가 비어 있다 → 체인이 07:30 까지 이 단계의 성공을 확인하고, 실패면 같은 호출을 다시 돌린다

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

xplan

[고친 뒤 · TKPROF 요약]
  문장                                                 count     cpu   elapsed     query    current      rows
  TRUNCATE TABLE "SQLP"."MV_CUS_MON"                       1    0.02      0.05        12         61         0
  INSERT /*+ APPEND */ INTO "SQLP"."MV_CUS_MON" SELECT …   1    0.88      2.91     5,861        402    72,000
  인덱스 IX_MV_CUS_MON 다시 만들기                         1    0.21      0.36       420        118         0

[같은 단계 · 세션 통계 차이]
  redo size                 4,212,604
  undo change vector size      38,120
  경과 시간                      3.6초

[INSERT … SELECT 의 실행계획]
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |            |      1 |        |      0 |00:00:02.91 |    6263 |          |
|   1 |  LOAD AS SELECT          | MV_CUS_MON |      1 |        |      0 |00:00:02.91 |    6263 |          |
|   2 |   HASH GROUP BY          |            |      1 |  72000 |  72000 |00:00:00.62 |    5826 | 5243K (0)|
|   3 |    TABLE ACCESS FULL     | T_ORD_BIG  |      1 |   1000K|   1000K|00:00:00.08 |    5826 |          |
-------------------------------------------------------------------------------------------------------------

모범 답안 서술

기본 옵션의 COMPLETE 새로 고침은 원자적(atomic_refresh => TRUE)이다. 새로 고치는 동안에도 다른 세션이 옛 내용을 보고, 실패하면 옛 내용으로 돌아가야 하므로 한 트랜잭션 안에서 MV의 72,000행을 DELETE로 지우고 정의 쿼리 결과를 일반 INSERT로 다시 넣는다. 지우는 행마다 undo와 redo가 생기고 넣는 행마다 redo가 생기며, 인덱스 IX_MV_CUS_MON의 항목도 행마다 지웠다 넣는다. TKPROF의 current 22만 블록씩이 그 흔적이고, redo 61MB와 undo 19MB, 38초의 거의 전부가 이 두 문장이다. 정작 답을 만드는 T_ORD_BIG 읽기와 묶기는 1초가 안 된다. 02:00 ~ 08:00에는 이 MV를 읽는 세션이 없으므로 '새로 고치는 동안에도 옛 내용을 보여 준다'는 약속이 필요 없다. atomic_refresh => FALSE로 부르면 MV를 TRUNCATE하고 직접 경로(APPEND)로 넣은 뒤 인덱스를 다시 만든다. TRUNCATE와 직접 경로 적재는 행마다 undo를 남기지 않고, redo는 LOGGING이라도 새로 쓴 블록 몇 MB로 준다. redo 61MB → 약 4MB, 38초 → 약 3.6초다. 대가는 새로 고치는 동안 MV가 비어 보인다는 것이다. 단계가 중간에 실패하면 다음 새로 고침까지 빈 MV가 남는다. 그래서 체인이 07:30까지 이 단계의 성공을 확인하고, 실패면 다시 돌린다. 이 시간에 MV를 읽는 화면이 있었다면 원자적이지 않은 방식은 쓸 수 없다. 그때는 out_of_place => TRUE(12c부터, atomic_refresh => FALSE와 함께)로 새 사본을 따로 만든 뒤 바꿔 끼우면 읽는 쪽은 옛 내용을 계속 본다. 대신 MV 한 벌만큼의 공간이 잠시 더 든다.

해설

DELETE와 INSERT인 까닭: 원자적 새로 고침은 MV가 비는 순간이 없도록 지우기와 넣기를 한 트랜잭션에 담는다. TRUNCATE는 DDL이라 그 안에 넣을 수 없다. '읽는 사람에게 빈 MV를 보이지 않는다'는 약속의 값이 undo와 redo다.
NOLOGGING까지 가지 않는다: MV를 NOLOGGING으로 두면 redo가 더 줄지만, 그 뒤에 백업으로 복구하면 MV 블록이 쓸 수 없는 채로 남는다. 다시 새로 고치면 되는 MV라 감수할 수는 있어도, FORCE LOGGING이나 대기 DB가 있는 곳에서는 효과가 없고 운영 절차가 는다(교재 14화 §5). 4MB면 충분하다.
같은 MV를 빠른 새로 고침으로 바꾸면 날마다 바뀐 줄만 고친다. 하지만 MV 로그와 COUNT 칸 같은 조건을 갖춰야 하므로 EXPLAIN_MVIEW로 먼저 확인한다(SORT-068). 하루 한 번 새로 고치는 MV라면 COMPLETE를 싸게 하는 이 방법이 단순하다.
이 단계는 '정의 쿼리는 1초인데 단계는 38초'였다. 배치 단계는 TKPROF나 세션 통계로 문장별 시간을 나눠 봐야 숨은 문장(DELETE)이 보인다(교재 14화 §2).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황