고객 월 실적 마감 배치가 고객 2,000명마다 주문 테이블을 세 번 뒤진다

중급INSERT한빛유통245 / 700

상황

매월 1일 새벽 정산 마감 배치의 한 단계는 지난달 고객별 주문 건수, 주문 금액, 마지막 주문 일시를 고객 월 실적 테이블에 넣는다. 강도현이 남긴 인수인계 메모에는 "이 단계 0.7초, 고객 수가 늘면 같이 늘어남"이라고 적혀 있다. 오과장은 내년에 제휴사 고객을 받아 고객 수가 열 배로 는다며 미리 봐 달라고 했다.

테이블과 인덱스

M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID)
T_ORD: 100,000행 · 896블록 · 고객마다 50건(2,000행 간격으로 흩어져 있다) · 2024년 6월 4,110건(고객마다 2 ~ 3건) · PK_T_ORD (ORD_SEQ) · IX_ORD_CUS (CUS_ID)
T_CUS_MON 고객 월 실적: 이 배치가 채우는 집계 테이블(BASE_YM, CUS_ID, ORD_CNT, ORD_AMT, LAST_ORD_DT) · 인덱스 없음
운영 DB는 2021년 결과 오류 버그를 피하려고 _optimizer_unnest_scalar_sq = FALSE를 시스템 수준에 걸어 두었다(스칼라 서브쿼리 Unnesting을 하지 않는다)
통계 최신

원본 SQL

sql

INSERT INTO T_CUS_MON (BASE_YM, CUS_ID, ORD_CNT, ORD_AMT, LAST_ORD_DT)
SELECT '202406', c.CUS_ID,
       (SELECT COUNT(*)
          FROM T_ORD o
         WHERE o.CUS_ID = c.CUS_ID
           AND o.ORD_DT >= DATE '2024-06-01' AND o.ORD_DT < DATE '2024-07-01'),
       (SELECT NVL(SUM(o.ORD_AMT), 0)
          FROM T_ORD o
         WHERE o.CUS_ID = c.CUS_ID
           AND o.ORD_DT >= DATE '2024-06-01' AND o.ORD_DT < DATE '2024-07-01'),
       (SELECT MAX(o.ORD_DT)
          FROM T_ORD o
         WHERE o.CUS_ID = c.CUS_ID
           AND o.ORD_DT >= DATE '2024-06-01' AND o.ORD_DT < DATE '2024-07-01')
  FROM M_CUS c

실행계획 학습용 예시

xplan

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                      |            |      1 |        |      0 |00:00:00.71 |     316K|
|   1 |  LOAD TABLE CONVENTIONAL              | T_CUS_MON  |      1 |        |      0 |00:00:00.71 |     316K|
|   2 |   SORT AGGREGATE                      |            |   2000 |      1 |   2000 |00:00:00.22 |     104K|
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD      |   2000 |      2 |   4110 |00:00:00.21 |     104K|
|*  4 |     INDEX RANGE SCAN                  | IX_ORD_CUS |   2000 |     50 |    100K|00:00:00.03 |    4612 |
|   5 |   SORT AGGREGATE                      |            |   2000 |      1 |   2000 |00:00:00.22 |     104K|
|*  6 |    TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD      |   2000 |      2 |   4110 |00:00:00.21 |     104K|
|*  7 |     INDEX RANGE SCAN                  | IX_ORD_CUS |   2000 |     50 |    100K|00:00:00.03 |    4612 |
|   8 |   SORT AGGREGATE                      |            |   2000 |      1 |   2000 |00:00:00.22 |     104K|
|*  9 |    TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD      |   2000 |      2 |   4110 |00:00:00.21 |     104K|
|* 10 |     INDEX RANGE SCAN                  | IX_ORD_CUS |   2000 |     50 |    100K|00:00:00.03 |    4612 |
|  11 |   TABLE ACCESS FULL                   | M_CUS      |      1 |   2000 |   2000 |00:00:00.01 |      30 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(("O"."ORD_DT">=TO_DATE(' 2024-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."ORD_DT"<TO_DATE(' 2024-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
   4 - access("O"."CUS_ID"=:B1)
   (6 · 7 · 9 · 10 도 같은 모양)

(DBMS_XPLAN 이 줄여 쓴 값 — 104K = 104,612 · 316K = 315,962)

문제

이 단계의 일량이 어디서 나오는지 Starts와 Buffers로 설명하시오. 고객 수가 열 배가 되면 어떻게 되는지도 쓰시오.
같은 결과가 들어가도록 INSERT 문을 고치시오. 그 달에 주문이 없는 고객의 값이 원래와 같아야 한다는 점에 주의하시오.
파라미터 _optimizer_unnest_scalar_sq를 기본값으로 되돌리는 안과 SQL을 고치는 안을 비교하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 6월 주문을 한 번 읽어 고객별로 건수 · 금액 · 마지막 일시를 함께 묶고, 고객에 외부 조인으로 붙인다
-- 주문이 없는 고객 : 원래(스칼라)는 0 · 0 · NULL 이었다 → NVL 로 같게 만든다
INSERT INTO T_CUS_MON (BASE_YM, CUS_ID, ORD_CNT, ORD_AMT, LAST_ORD_DT)
SELECT '202406', c.CUS_ID,
       NVL(m.ord_cnt, 0), NVL(m.ord_amt, 0), m.last_ord_dt
  FROM M_CUS c,
       (SELECT o.CUS_ID,
               COUNT(*)       AS ord_cnt,
               SUM(o.ORD_AMT) AS ord_amt,
               MAX(o.ORD_DT)  AS last_ord_dt
          FROM T_ORD o
         WHERE o.ORD_DT >= DATE '2024-06-01'
           AND o.ORD_DT <  DATE '2024-07-01'
         GROUP BY o.CUS_ID) m
 WHERE m.CUS_ID(+) = c.CUS_ID;

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

xplan

----------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |           |      1 |        |      0 |00:00:00.03 |    3022 |          |
|   1 |  LOAD TABLE CONVENTIONAL | T_CUS_MON |      1 |        |      0 |00:00:00.03 |    3022 |          |
|*  2 |   HASH JOIN OUTER        |           |      1 |   2000 |   2000 |00:00:00.02 |     926 | 1559K (0)|
|   3 |    TABLE ACCESS FULL     | M_CUS     |      1 |   2000 |   2000 |00:00:00.01 |      30 |          |
|   4 |    VIEW                  |           |      1 |   2000 |   2000 |00:00:00.02 |     896 |          |
|   5 |     HASH GROUP BY        |           |      1 |   2000 |   2000 |00:00:00.02 |     896 | 1376K (0)|
|*  6 |      TABLE ACCESS FULL   | T_ORD     |      1 |   4110 |   4110 |00:00:00.01 |     896 |          |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("M"."CUS_ID"(+)="C"."CUS_ID")
   6 - filter(("O"."ORD_DT">=TO_DATE(' 2024-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."ORD_DT"<TO_DATE(' 2024-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

[대조]  고친 뒤 2,000 행 · ORD_CNT 합 4,110 · MINUS 양방향 0 / 0 (원래 SQL 로 넣은 행과)

모범 답안 서술

2번, 5번, 8번 줄의 Starts 2,000은 고객 수다. 스칼라 서브쿼리 세 개가 고객마다 한 번씩 돌아 6,000번 실행됐다. 한 번 실행할 때 IX_ORD_CUS로 그 고객의 주문 50건을 찾고(4번 줄 A-Rows 100K = 2,000 × 50), 인덱스에 날짜가 없어 50건을 모두 테이블까지 가서 읽은 뒤 6월 2 ~ 3건만 남긴다(3번 줄 filter). 서브쿼리 하나에 104,612블록, 셋이면 313,836블록이다. 세 서브쿼리는 같은 고객의 같은 주문을 세 번 읽고, 입력(고객번호)이 행마다 다르므로 캐시도 줄여 줄 것이 없다. 스칼라 서브쿼리는 바깥 행마다 도는 NL 조인과 같아서, 고객 수가 열 배가 되면 실행 횟수와 블록도 열 배(약 316만 블록)로 는다. 6월 주문 4,110건을 T_ORD 전체 스캔 한 번(896블록)으로 읽어 고객별로 건수, 금액, 마지막 일시를 함께 묶은 뒤, 고객에 외부 조인으로 붙인다. Buffers는 316K(315,962) → 3,022이고, 그중 2,096블록은 2,000행을 넣는 몫이다. 고객 수가 늘어도 6월 주문을 한 번 읽는 일은 그대로다. 주문이 없는 고객은 원래 SQL에서 ORD_CNT 0, ORD_AMT 0, LAST_ORD_DT NULL이었다. 스칼라 서브쿼리의 COUNT는 0을 돌려주지만 외부 조인한 집계 뷰의 컬럼은 NULL이 되므로, NVL(m.ord_cnt, 0)처럼 0으로 바꿔야 같은 값이 들어간다. 6월에는 모든 고객에게 주문이 있지만, 가입만 하고 주문하지 않은 고객이 생기는 달에는 이 차이가 드러난다. 파라미터를 기본값으로 되돌리면 옵티마이저가 스칼라 서브쿼리를 펼칠 수 있게 되지만, 서브쿼리마다 따로 펼치면 T_ORD를 세 번 읽는다. 게다가 밑줄 파라미터는 시스템의 모든 SQL에 영향을 주므로 배치 한 단계 때문에 바꿀 일이 아니다. SQL을 고치면 파라미터와 상관없이 한 번만 읽는다.

해설

COUNT(*)의 함정: 집계 뷰 없이 T_ORD를 고객에 바로 외부 조인하고 GROUP BY c.CUS_ID로 묶는다면 COUNT(*)는 주문이 없는 고객을 1로 센다. 그때는 COUNT(o.ORD_SEQ)처럼 주문 쪽 컬럼을 센다.
인덱스로 푸는 안: IX_ORD_CUS를 (CUS_ID, ORD_DT, ORD_AMT)로 바꾸면 한 번 실행이 인덱스 2 ~ 3블록으로 줄지만(전체 약 1만 5천 블록), 실행 횟수는 여전히 고객 수에 비례한다. 모든 고객을 다루는 배치에는 한 번 읽고 묶는 쪽이 맞다.
APPEND는? 2,000행이라 직접 경로 적재의 이득이 작고, 테이블에 배타 잠금이 걸린다. 넣는 몫 2,096블록은 그대로 둔다.
바꾼 뒤에는 원래 SQL로 넣은 달과 MINUS 양방향, 건수(2,000행), 합계(ORD_CNT 합 4,110)를 대조한다(교재 08화 정합성 회귀 체크리스트).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황