JOIN-031

1분마다 새로 고치는 경영 대시보드가 주문 3년 치를 고객 마스터에 다시 맞대 본다

실전SELECT한빛유통181 / 700

상황

경영지원팀 대시보드의 「지역 · 등급 · 월 매출」 칸은 3년 치 주문을 지역, 등급, 월로 묶어 864칸짜리 표를 그린다. 07시부터 19시까지 1분마다 새로 고쳐 하루 720번 돈다. AWR의 CPU 상위 SQL 1위이고, DB CPU의 18%를 차지한다. 해시 조인이라 Buffers는 이미 5,860으로 작다. 박팀장은 "더 줄일 수 있는 안을 모두 모아 숫자와 대가를 붙여 가져오라"라고 했다. 대시보드는 어제까지의 주문만 보여 주면 된다. 주문은 낮 동안 계속 들어오고(하루 약 900건), 지난 주문의 금액 정정이 하루 수십 건 있다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 인덱스 없음 · 주문일은 2022-01-01부터 어제까지
M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · 지역 8곳에 250명씩 · CUS_GD A · B · C
M_RGN: 8행 · 4블록 · PK_M_RGN (RGN_ID)
통계 최신 · 아래 CPU는 같은 조건으로 10번 돌려 잰 CPU used by this session의 한 번 평균

원본 SQL

sql

SELECT TO_CHAR(o.ORD_DT, 'YYYYMM') AS ym, r.RGN_NM, c.CUS_GD,
       COUNT(*) AS ord_cnt, SUM(o.ORD_AMT) AS ord_amt
  FROM T_ORD_BIG o, M_CUS c, M_RGN r
 WHERE c.CUS_ID = o.CUS_ID
   AND r.RGN_ID = c.RGN_ID
   AND o.ORD_DT < TRUNC(SYSDATE)
 GROUP BY TO_CHAR(o.ORD_DT, 'YYYYMM'), r.RGN_NM, c.CUS_GD

실행계획 학습용 예시 · CPU 비율은 교재 18화 Q2 rev02 실측을 참고

xplan

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |      1 |        |    864 |00:00:00.82 |    5860 |       |       |          |
|   1 |  HASH GROUP BY         |           |      1 |    864 |    864 |00:00:00.82 |    5860 |  1394K|  1394K| 1408K (0)|
|*  2 |   HASH JOIN            |           |      1 |   1000K|   1000K|00:00:00.58 |    5860 |  1572K|  1572K| 1702K (0)|
|*  3 |    HASH JOIN           |           |      1 |   2000 |   2000 |00:00:00.01 |      34 |  1376K|  1376K|  950K (0)|
|   4 |     TABLE ACCESS FULL  | M_RGN     |      1 |      8 |      8 |00:00:00.01 |       4 |       |       |          |
|   5 |     TABLE ACCESS FULL  | M_CUS     |      1 |   2000 |   2000 |00:00:00.01 |      30 |       |       |          |
|*  6 |    TABLE ACCESS FULL   | T_ORD_BIG |      1 |   1000K|   1000K|00:00:00.06 |    5826 |       |       |          |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C"."CUS_ID"="O"."CUS_ID")
   3 - access("R"."RGN_ID"="C"."RGN_ID")
   6 - filter("O"."ORD_DT"<TRUNC(SYSDATE@!))

[한 번 실행]  CPU used by this session 41 cs · 하루 720번이면 Buffers 약 422만 · CPU 약 295초

문제

이 SQL의 일량을 Buffers와 조인에 대는 행 수, CPU로 나눠 설명하시오. 하루 단위의 일량도 계산하시오.
① 조인 전에 고객과 월로 먼저 묶기 ② 커버링 인덱스 (CUS_ID, ORD_DT, ORD_AMT) ③ 결과 캐시(RESULT_CACHE 힌트) ④ 밤에 한 번 결과를 만들어 두는 요약 테이블, 네 안을 블록과 CPU, 대가로 비교하시오.
권하는 안을 SQL(DDL 포함)로 작성하고, 지난 주문의 정정을 어떻게 반영하며 정합성을 어떻게 확인할지 서술하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ④ 결과 표: 하루에 한 번 통째로 다시 만든다 (어제까지의 숫자 · 지난 주문의 정정도 다음 날 아침에 반영된다)
CREATE TABLE T_DASH_SLS_MON (
  ORD_YM  VARCHAR2(6)   NOT NULL,
  RGN_NM  VARCHAR2(100) NOT NULL,
  CUS_GD  VARCHAR2(40)  NOT NULL,
  ORD_CNT NUMBER        NOT NULL,
  ORD_AMT NUMBER        NOT NULL,
  BAS_DT  DATE          NOT NULL,
  CONSTRAINT PK_T_DASH_SLS_MON PRIMARY KEY (ORD_YM, RGN_NM, CUS_GD)
);

-- 06:30 배치 (대시보드가 열리기 전): 만들 때는 ① 처럼 고객 · 월로 먼저 묶어 조인에 대는 행을 100만 → 72,000 으로 줄인다
TRUNCATE TABLE T_DASH_SLS_MON;
INSERT /*+ APPEND */ INTO T_DASH_SLS_MON (ORD_YM, RGN_NM, CUS_GD, ORD_CNT, ORD_AMT, BAS_DT)
SELECT o.ym, r.RGN_NM, c.CUS_GD, SUM(o.cnt), SUM(o.amt), TRUNC(SYSDATE) - 1
  FROM (SELECT CUS_ID, TO_CHAR(ORD_DT, 'YYYYMM') AS ym, COUNT(*) AS cnt, SUM(ORD_AMT) AS amt
          FROM T_ORD_BIG
         WHERE ORD_DT < TRUNC(SYSDATE)
         GROUP BY CUS_ID, TO_CHAR(ORD_DT, 'YYYYMM')) o,
       M_CUS c, M_RGN r
 WHERE c.CUS_ID = o.CUS_ID
   AND r.RGN_ID = c.RGN_ID
 GROUP BY o.ym, r.RGN_NM, c.CUS_GD;
COMMIT;

-- 배치의 마지막 단계: 표의 건수 · 금액 합계 = 원장(어제까지)의 합계. 다르면 대시보드에 '집계 확인 중' 을 띄운다
SELECT (SELECT SUM(ORD_CNT) FROM T_DASH_SLS_MON)                       AS dash_cnt,
       (SELECT COUNT(*)     FROM T_ORD_BIG WHERE ORD_DT < TRUNC(SYSDATE)) AS ledger_cnt,
       (SELECT SUM(ORD_AMT) FROM T_DASH_SLS_MON)                       AS dash_amt,
       (SELECT SUM(ORD_AMT) FROM T_ORD_BIG WHERE ORD_DT < TRUNC(SYSDATE)) AS ledger_amt
  FROM DUAL;

-- 대시보드 (하루 720번): 864행 표를 읽는다
SELECT ORD_YM AS ym, RGN_NM, CUS_GD, ORD_CNT, ORD_AMT, BAS_DT
  FROM T_DASH_SLS_MON;

개선 후 실행계획 학습용 예시 · CPU 비율은 교재 18화 Q2 rev02 실측을 참고

xplan

[06:30 배치 · 하루 한 번]
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT           |                |      1 |        |      0 |00:00:00.55 |    5878 |       |       |          |
|   1 |  LOAD AS SELECT            | T_DASH_SLS_MON |      1 |        |      0 |00:00:00.55 |    5878 |       |       |          |
|   2 |   HASH GROUP BY            |                |      1 |    864 |    864 |00:00:00.54 |    5860 |  1394K|  1394K| 1408K (0)|
|*  3 |    HASH JOIN               |                |      1 |  72000 |  72000 |00:00:00.52 |    5860 |  1572K|  1572K| 1702K (0)|
|*  4 |     HASH JOIN              |                |      1 |   2000 |   2000 |00:00:00.01 |      34 |  1376K|  1376K|  950K (0)|
|   5 |      TABLE ACCESS FULL     | M_RGN          |      1 |      8 |      8 |00:00:00.01 |       4 |       |       |          |
|   6 |      TABLE ACCESS FULL     | M_CUS          |      1 |   2000 |   2000 |00:00:00.01 |      30 |       |       |          |
|   7 |     VIEW                   |                |      1 |  72000 |  72000 |00:00:00.49 |    5826 |       |       |          |
|   8 |      HASH GROUP BY         |                |      1 |  72000 |  72000 |00:00:00.48 |    5826 |  5811K|  2433K| 6012K (0)|
|*  9 |       TABLE ACCESS FULL    | T_ORD_BIG      |      1 |   1000K|   1000K|00:00:00.06 |    5826 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------

[대시보드 · 하루 720번]
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                |      1 |        |    864 |00:00:00.01 |       6 |       |       |          |
|   1 |  TABLE ACCESS FULL         | T_DASH_SLS_MON |      1 |    864 |    864 |00:00:00.01 |       6 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------

[하루 합계]  원본 Buffers 약 422만 · CPU 약 295초  →  요약 표 Buffers 약 1만 · CPU 약 1초

모범 답안 서술

일량: 테이블은 T_ORD_BIG을 한 번 FULL로 읽는 5,826블록과 마스터 34블록이 전부라 Buffers는 5,860으로 작다. 일은 CPU에 있다. 2번 줄 해시 조인의 A-Rows가 1000K인데, 주문 100만 건을 하나씩 고객 해시 테이블에 맞대 조인 결과 100만 행을 만들고, 그것을 1번 줄이 864행으로 다시 묶는다. 한 번에 CPU 41cs다. 하루 720번이면 Buffers 약 422만 블록, CPU 약 295초이고, 그 결과는 하루 내내 같은 864행이다(어제까지의 숫자). ① 조인 전에 묶기: 인라인 뷰에서 T_ORD_BIG을 고객과 월로 먼저 묶으면 해시 조인에 대는 행이 100만 → 72,000(고객 2,000명 × 36개월)으로 준다. Buffers는 5,860 그대로이고 CPU는 41 → 16cs 안팎이다(18화 rev02에서도 블록은 같고 CPU가 3분의 1 남짓이 됐다). 결과가 바뀌지 않고 SQL 하나만 고치면 되지만, 하루 720번 100만 행을 묶는 일은 남는다. ② 커버링 인덱스: (CUS_ID, ORD_DT, ORD_AMT)를 만들면 테이블 대신 인덱스를 INDEX FAST FULL SCAN으로 읽어 약 3,800블록이 된다. 블록은 3분의 1쯤 줄지만 100만 행을 조인하고 묶는 CPU는 그대로다. 대신 이 SQL 하나를 위해 100만 행 테이블에 인덱스가 생겨, 주문을 적재하고 정정할 때마다 인덱스 갱신 비용과 redo가 붙는다(14화). 얻는 것에 비해 대가가 크다. ③ 결과 캐시: RESULT_CACHE 힌트는 같은 결과를 서버에 저장해 두고 다시 쓴다. 하지만 SQL이 읽는 테이블에 변경이 커밋되면 결과가 무효가 된다. T_ORD_BIG에는 낮 동안 주문이 1분에 한 건꼴로 들어오므로, 1분마다 새로 고칠 때쯤이면 결과가 이미 지워져 있다. 이 화면에서는 소용이 없다(15b화). ④ 요약 테이블: 대시보드가 보여 주는 숫자는 어제까지의 것이라 하루에 한 번만 바뀐다. 06:30 배치에서 결과 864행을 표로 만들어 두고, 대시보드는 그 표만 읽는다. 표를 만들 때는 ①처럼 고객과 월로 먼저 묶는다. 배치는 하루 한 번 5,878블록과 CPU 약 0.2초, 대시보드는 한 번에 6블록이다. 하루로 보면 Buffers 약 422만 → 약 1만, CPU 약 295초 → 약 1초다. 권고: ④를 권한다. 같은 답을 하루 720번 다시 계산하지 않는 것이 이 화면의 본질적인 개선이다. 대가는 배치 한 단계가 늘고, 대시보드가 '어제까지'라는 기준일(BAS_DT)을 함께 보여 줘야 한다는 점이다. 배치가 실패한 날에는 그 전날 숫자가 남으므로 기준일을 화면에 띄워 둔다. 정정 반영과 정합성: 표를 매일 통째로 다시 만들기 때문에 지난 주문의 금액 정정은 다음 날 아침 표에 저절로 반영된다. 어제까지에 더해 정정분만 고치는 증분 방식은 빠르지만 정정을 놓치기 쉬워 쓰지 않았다. 배치의 마지막 단계에서 표의 건수와 금액 합계가 원장(T_ORD_BIG의 어제까지) 합계와 같은지 대조하고, 다르면 대시보드에 '집계 확인 중'을 띄운다.

해설

CPU를 근거로 쓰는 법: Buffers가 같은 개선은 CPU를 같은 조건으로 재서 함께 적어야 근거가 된다(교재 18화 §6.5). 이 문제의 ①이 그런 경우다.
MV라는 다른 답: 같은 결과를 CREATE MATERIALIZED VIEW ... REFRESH COMPLETE로 만들고 06:30에 새로 고쳐도 된다. 새로 고치는 동안에도 이전 결과를 읽을 수 있어서, TRUNCATE 뒤 INSERT하는 표보다 화면이 비는 순간이 없다. 다만 MV의 새로 고침 일정과 실패 감시를 따로 챙겨야 한다.
오늘 숫자까지 보여 달라고 하면: 요약 표(어제까지)에 오늘 주문만 원장에서 읽어 더한다(UNION ALL). 오늘 주문은 900건 안팎이라 ORD_DT 인덱스가 있으면 몇십 블록이다. 이때 요약 표의 단위는 결과(지역 · 등급)가 아니라 고객 · 월이어야 오늘 주문을 같은 기준으로 붙일 수 있다.
결과 캐시가 맞는 곳: 거의 바뀌지 않는 테이블(코드, 마스터)만 읽는 SQL이다. 계속 들어오는 원장을 읽는 SQL에 붙이면 무효화와 다시 계산이 반복될 뿐이다 (교재 15b화 §2).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · CPU 비율은 교재 18화 Q2 rev02 실측을 참고 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · CPU 비율은 교재 18화 Q2 rev02 실측을 참고 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황