월별 매출 대시보드가 아침 9시 전에는 3블록인데 첫 주문이 들어온 뒤로는 하루 종일 5,826블록이다

중급DDL한빛유통372 / 700

상황

지난주 DBA가 경영지원팀의 「월별 매출」 대시보드(1분마다 새로 고침, 하루 약 720번)를 빠르게 하려고 구체화 뷰 MV_ORD_MON을 만들고 쿼리 재작성(Query Rewrite)을 켰다. 화면 SQL은 손대지 않았다. 월요일 08:50에 재 보니 3블록이었는데, 오후에 AWR을 보니 이 SQL이 실행마다 5,826블록을 읽고 있었다. 대시보드 요구서에는 "어제까지의 숫자면 된다"라고 적혀 있다. 주문은 9시 무렵부터 낮 동안 계속 들어온다(하루 약 900건). MV는 매일 02:00 잡이 새로 고친다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_ST COMPLETE 90% · PENDING 8% · CANCEL 2% · 이 문제에서는 인덱스가 없다 · 낮 동안 주문이 계속 INSERT, COMMIT된다
MV_ORD_MON 구체화 뷰: 아래 DDL · 108행(36개월 × 상태 3) · 3블록 · 매일 02:00 DBMS_MVIEW.REFRESH('MV_ORD_MON', 'C')
인스턴스 파라미터: QUERY_REWRITE_ENABLED = TRUE · QUERY_REWRITE_INTEGRITY = ENFORCED(기본값)
대시보드는 커넥션 풀의 세션을 정산 화면들과 함께 쓴다. 정산 화면은 오늘 주문까지 정확해야 한다

원본 SQL

sql

-- 지난주 DBA 가 만든 MV
CREATE MATERIALIZED VIEW MV_ORD_MON
  BUILD IMMEDIATE
  REFRESH COMPLETE ON DEMAND
  ENABLE QUERY REWRITE
AS
SELECT TRUNC(ORD_DT, 'MM') AS ORD_MM, ORD_ST,
       COUNT(*) AS ORD_CNT, SUM(ORD_AMT) AS ORD_AMT
  FROM T_ORD_BIG
 GROUP BY TRUNC(ORD_DT, 'MM'), ORD_ST;

-- 대시보드 SQL (손대지 않았다)
SELECT TRUNC(ORD_DT, 'MM') AS ORD_MM,
       COUNT(*) AS ORD_CNT, SUM(ORD_AMT) AS ORD_AMT
  FROM T_ORD_BIG
 WHERE ORD_ST <> 'CANCEL'
 GROUP BY TRUNC(ORD_DT, 'MM')
 ORDER BY ORD_MM

실행계획 학습용 예시

xplan

[월요일 08:50 · 새로 고침 뒤 아직 주문이 없을 때]
-------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |      1 |        |     36 |00:00:00.01 |       3 |
|   1 |  SORT GROUP BY                |            |      1 |     36 |     36 |00:00:00.01 |       3 |
|*  2 |   MAT_VIEW REWRITE ACCESS FULL| MV_ORD_MON |      1 |     72 |     72 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("MV_ORD_MON"."ORD_ST"<>'CANCEL')

[월요일 14:10 · 같은 SQL]
-------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |     36 |00:00:00.31 |    5826 |          |
|   1 |  SORT GROUP BY     |           |      1 |     36 |     36 |00:00:00.31 |    5826 | 4096  (0)|
|*  2 |   TABLE ACCESS FULL| T_ORD_BIG |      1 |    980K|    980K|00:00:00.11 |    5826 |          |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ORD_ST"<>'CANCEL')

[14:10 · USER_MVIEWS]  MVIEW_NAME MV_ORD_MON · REWRITE_ENABLED Y · STALENESS STALE · LAST_REFRESH_DATE 06/24 02:00:41

문제

같은 SQL이 08:50에는 MV를 읽고 14:10에는 원장을 읽은 까닭을 MV의 상태와 QUERY_REWRITE_INTEGRITY로 설명하시오.
요구서(어제까지의 숫자)를 지키면서 하루 종일 MV를 읽도록 고치고, 세션의 QUERY_REWRITE_INTEGRITY를 STALE_TOLERATED로 바꾸는 안과 견주시오.
고친 뒤에 지켜봐야 할 것(새로 고침 실패, 숫자의 기준 시각)을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 대시보드 SQL 이 MV 를 직접 읽는다 : 재작성(MV 신선도)에 기대지 않는다 · 숫자는 마지막 새로 고침(02:00) 기준 = 어제까지
SELECT ORD_MM, SUM(ORD_CNT) AS ORD_CNT, SUM(ORD_AMT) AS ORD_AMT
  FROM MV_ORD_MON
 WHERE ORD_ST <> 'CANCEL'
 GROUP BY ORD_MM
 ORDER BY ORD_MM;

-- 화면 머리에 띄울 기준 시각 (새로 고침이 실패한 날을 사용자가 알 수 있게)
SELECT LAST_REFRESH_DATE, STALENESS
  FROM USER_MVIEWS
 WHERE MVIEW_NAME = 'MV_ORD_MON';

-- 정산 화면들은 그대로 원장을 읽는다 · 풀 세션의 QUERY_REWRITE_INTEGRITY 는 바꾸지 않는다

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

xplan

-----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |      1 |        |     36 |00:00:00.01 |       3 |
|   1 |  SORT GROUP BY        |            |      1 |     36 |     36 |00:00:00.01 |       3 |
|*  2 |   MAT_VIEW ACCESS FULL| MV_ORD_MON |      1 |     72 |     72 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ORD_ST"<>'CANCEL')

[14:10 · 같은 SQL]  같은 모양 · Buffers 3 (MV 가 STALE 이어도 직접 읽으므로 계획이 바뀌지 않는다)

모범 답안 서술

MV를 만들 때 붙인 ENABLE QUERY REWRITE는 '쓸 수 있으면 원장 대신 MV를 읽어도 된다'는 허락일 뿐이다. 쓸 수 있는지는 QUERY_REWRITE_INTEGRITY가 정한다. 기본값 ENFORCED는 MV의 내용이 원장과 똑같다고 보장될 때만 재작성한다. 02:00에 새로 고친 MV는 아침 첫 주문이 커밋되는 순간 원장과 달라지고 STALENESS가 STALE이 된다(14:10의 USER_MVIEWS). 그때부터 옵티마이저는 이 MV로 재작성하지 않고 원장 T_ORD_BIG을 FULL로 읽는다. 08:50에는 아직 주문이 없어 MV가 신선했고(3블록), 9시 이후로는 다음 날 02:00까지 STALE이라 하루 종일 5,826블록이다. 하루 720번이면 약 420만 블록이다. 요구서는 '어제까지의 숫자'이므로 하루 동안 낡은 MV가 요구에 맞는다. 그렇다면 재작성에 기대지 말고 대시보드 SQL이 MV를 직접 읽게 한다. MV는 월, 상태별 건수와 금액을 담고 있으니 ORD_ST <> 'CANCEL'로 거르고 월로 다시 더하면 원래 SQL과 같은 칸이 나온다. 하루 종일 3블록이고(5,826 → 3), 다른 화면에는 아무 영향이 없다. 세션에서 QUERY_REWRITE_INTEGRITY = STALE_TOLERATED로 바꾸는 안도 대시보드만 보면 같은 결과를 낸다. 하지만 대시보드는 커넥션 풀의 세션을 정산 화면들과 함께 쓴다. 그 세션을 받은 정산 화면의 SQL이 MV로 재작성될 수 있는 모양이면 오늘 주문이 빠진 숫자를 정답처럼 보여 준다. 화면마다 무엇이 재작성될지 따져야 하므로 위험하다. MV를 늘 신선하게 두려고 ON COMMIT 빠른 새로 고침으로 바꾸는 안은 주문 커밋마다 MV를 고치는 비용이 들어, 어제까지면 되는 대시보드에 비해 과하다. 직접 읽으면 숫자의 기준이 마지막 새로 고침 시각이 된다. 02:00 잡이 실패하면 대시보드는 그저께까지의 숫자를 보여 주므로, 화면 머리에 LAST_REFRESH_DATE로 기준 시각을 띄우고 잡의 실패를 감시한다.

해설

재작성은 약속이 아니라 기회다: 재작성은 조건이 맞을 때만 옵티마이저가 고르는 변환이라, MV가 STALE이거나 SQL 모양이 조금만 달라도(예: 묶는 키를 TO_CHAR(ORD_DT, 'YYYYMM')로 쓰면) 조용히 원장으로 돌아간다. 성능이 MV에 걸린 화면이라면 MV를 직접 읽게 하거나, 계획에 MAT_VIEW REWRITE ACCESS가 나오는지 정기적으로 확인한다.
DBMS_MVIEW.EXPLAIN_REWRITE로 재작성되지 않은 까닭을 메시지로 볼 수 있다. 이 사건에서는 MV가 ENFORCED 모드에서 STALE이라는 메시지가 나온다.
미리 더해 둔 숫자를 읽는다는 것은 '지금 이 순간'을 '마지막 새로 고침 시점'으로 바꾸는 약속이다. 기준 시각을 화면에 적는 것은 그 약속을 사용자에게 보이는 일이다(교재 08화 §9).
결과 캐시와 견주면, MV는 원장이 바뀌어도 내용이 남고(낡을 뿐) 결과 캐시는 원장이 커밋되면 지워진다(교재 15b화 §4). 낮 동안 주문이 계속 들어오는 원장 위의 '어제까지' 화면에는 결과 캐시보다 MV가 맞는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황