고객 월 실적 적재가 지난달부터 논리 읽기 1위로 올라왔다

중급INSERT한빛유통286 / 700

상황

월초 배치의 「고객 월 실적 적재」는 지난달 고객별 주문 건수와 금액을 T_CUS_MON에 넣는다. 지난달 배치 소스를 정리하면서 이 단계의 SELECT를 영업팀 「VIP 월 실적」 화면 SQL에서 그대로 옮겨 왔고, 그 뒤로 이 INSERT가 AWR 논리 읽기 1위에 올랐다. 화면 SQL의 힌트 옆에는 -- A 등급 20명만 보는 화면이라 고객마다 뷰를 연다라는 주석이 있다. 배치에는 등급 조건이 없다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 고객 2,000명이 약 500건씩 · 한 달 약 27,390건(고객마다 약 14건) · 한 고객의 주문은 테이블 곳곳에 흩어져 있다
인덱스 IX_ORD_BIG_CUS (CUS_ID): 콜센터 화면용 · 높이 3 · 고객 한 명의 항목이 리프 1~2블록
M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID)
T_CUS_MON(이 문제에서 만든 집계 테이블): YM · CUS_ID · CUS_GD · ORD_CNT · ORD_AMT · 인덱스 없음 · 한 달에 2,000행씩 쌓는다
바인드: :ym = '202406', :d1과 :d2는 6월 1일과 7월 1일 · 통계 최신

원본 SQL

sql

INSERT INTO T_CUS_MON (YM, CUS_ID, CUS_GD, ORD_CNT, ORD_AMT)
SELECT /*+ LEADING(c) USE_NL(v) NO_MERGE(v) PUSH_PRED(v) */
       :ym, c.CUS_ID, c.CUS_GD, v.ORD_CNT, v.ORD_AMT
  FROM M_CUS c,
       (SELECT CUS_ID, COUNT(*) AS ORD_CNT, SUM(ORD_AMT) AS ORD_AMT
          FROM T_ORD_BIG
         WHERE ORD_DT >= :d1
           AND ORD_DT <  :d2
         GROUP BY CUS_ID) v
 WHERE v.CUS_ID = c.CUS_ID

실행계획 학습용 예시

xplan

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                       |                |      1 |        |      0 |00:00:00.93 |    1008K|
|   1 |  LOAD TABLE CONVENTIONAL               | T_CUS_MON      |      1 |        |      0 |00:00:00.93 |    1008K|
|   2 |   NESTED LOOPS                         |                |      1 |   2000 |   2000 |00:00:00.91 |    1008K|
|   3 |    TABLE ACCESS FULL                   | M_CUS          |      1 |   2000 |   2000 |00:00:00.01 |      30 |
|   4 |    VIEW PUSHED PREDICATE               |                |   2000 |      1 |   2000 |00:00:00.90 |    1008K|
|   5 |     SORT GROUP BY                      |                |   2000 |      1 |   2000 |00:00:00.89 |    1008K|
|*  6 |      TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_BIG     |   2000 |     14 |  27390 |00:00:00.85 |    1008K|
|*  7 |       INDEX RANGE SCAN                 | IX_ORD_BIG_CUS |   2000 |    500 |   1000K|00:00:00.12 |    8000 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - filter(("ORD_DT">=:D1 AND "ORD_DT"<:D2))
   7 - access("CUS_ID"="C"."CUS_ID")

문제

1,008K 블록이 어디서 나오는지 4번, 6번, 7번 줄의 Starts와 A-Rows로 설명하시오. 화면에서는 문제가 없던 힌트가 배치에서 문제가 된 이유도 설명하시오.
배치 INSERT를 고치고 고친 뒤의 처리 방식과 읽는 블록 수를 쓰시오. 원래 화면 SQL도 더 낫게 할 방법이 있으면 함께 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 고객 2,000명 전부의 실적: 6월 주문을 한 번 읽어 묶고, 고객과 해시 조인한다
INSERT INTO T_CUS_MON (YM, CUS_ID, CUS_GD, ORD_CNT, ORD_AMT)
SELECT :ym, c.CUS_ID, c.CUS_GD, v.ORD_CNT, v.ORD_AMT
  FROM M_CUS c,
       (SELECT CUS_ID, COUNT(*) AS ORD_CNT, SUM(ORD_AMT) AS ORD_AMT
          FROM T_ORD_BIG
         WHERE ORD_DT >= :d1
           AND ORD_DT <  :d2
         GROUP BY CUS_ID) v
 WHERE v.CUS_ID = c.CUS_ID;
-- 고정하려면 /*+ LEADING(c v) USE_HASH(v) NO_PUSH_PRED(v) */

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

xplan

----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT        |           |      1 |        |      0 |00:00:00.12 |    5876 |          |
|   1 |  LOAD TABLE CONVENTIONAL| T_CUS_MON |      1 |        |      0 |00:00:00.12 |    5876 |          |
|*  2 |   HASH JOIN             |           |      1 |   2000 |   2000 |00:00:00.11 |    5856 | 1347K (0)|
|   3 |    TABLE ACCESS FULL    | M_CUS     |      1 |   2000 |   2000 |00:00:00.01 |      30 |          |
|   4 |    VIEW                 |           |      1 |   2000 |   2000 |00:00:00.10 |    5826 |          |
|   5 |     HASH GROUP BY       |           |      1 |   2000 |   2000 |00:00:00.10 |    5826 | 1189K (0)|
|*  6 |      TABLE ACCESS FULL  | T_ORD_BIG |      1 |  27390 |  27390 |00:00:00.08 |    5826 |          |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("V"."CUS_ID"="C"."CUS_ID")
   6 - filter(("ORD_DT">=:D1 AND "ORD_DT"<:D2))

모범 답안 서술

PUSH_PRED(v)와 USE_NL(v) 때문에 고객 2,000명마다 뷰를 한 번씩 열었다(4번 줄 Starts 2,000). 뷰가 열릴 때마다 IX_ORD_BIG_CUS로 그 고객의 주문 500건을 모두 찾고(7번 줄 A-Rows 1000K, 8,000블록), 인덱스에 ORD_DT가 없으므로 500건을 전부 테이블에서 읽은 뒤에야 6번 줄에서 6월 14건만 남긴다(A-Rows 1000K → 27,390). 한 고객의 주문은 흩어져 있어 행마다 블록 하나씩, 100만 블록이 여기서 나온다. 화면에서는 A 등급 20명만 바깥에 있었으므로 같은 모양으로 20 × 약 504블록, 약 1만 블록에 그쳐 눈에 띄지 않았다. 조인 조건을 뷰 안으로 밀어 넣는 JPPD는 바깥 집합이 작을 때의 판단이다. 바깥이 고객 전부인 배치에 옮겨 오면 일이 바깥 행 수만큼 100배로 늘어난다. 배치에서는 힌트를 떼고 6월 주문을 한 번 읽어 고객별로 묶은 뒤(테이블 전체 스캔 5,826블록, 27,390건) 고객 2,000명과 해시 조인한다. Buffers는 1,008K → 5,876으로 주고(INSERT 몫 약 20블록 포함), 시간도 0.93초 → 0.12초가 된다. 옵티마이저에 맡겨도 이 모양이 나오며, 고정하려면 USE_HASH(v) NO_PUSH_PRED(v)를 적는다. 화면 쪽은 JPPD를 그대로 쓰되 인덱스를 (CUS_ID, ORD_DT)로 바꾸면 고객마다 6월 범위만 찾아 약 17블록, 20명이면 약 370블록이 된다. 다만 콜센터 화면도 이 인덱스를 쓰므로 바꾸기 전에 그쪽 계획을 함께 확인한다.

해설

복사한 힌트에는 전제가 따라오지 않는다: 힌트 옆 주석이 '20명만 보는 화면'이라는 전제를 적어 두었기 때문에 원인을 빨리 찾을 수 있었다. 힌트에는 그 힌트가 기대는 규모를 함께 적는다(교재 06화 힌트 유효기간 대장).
JPPD가 이기는 조건: 바깥 행 수 × 뷰를 한 번 열 때의 블록 수 < 뷰를 통째로 한 번 만드는 블록 수. 이 배치는 2,000 × 504 = 약 100만 대 5,826이고, 인덱스를 (CUS_ID, ORD_DT)로 바꿔도 2,000 × 17 = 34,000이라 여전히 통째로 묶는 쪽이 싸다.
더 줄이려면: (ORD_DT, CUS_ID, ORD_AMT) 인덱스면 6월 범위만 인덱스에서 읽어(약 130블록) 테이블에 가지 않는다. 월 1회 배치를 위해 100만 행 인덱스를 늘릴지는 적재 비용과 함께 따진다(교재 14화).
배치 소스를 정리할 때는 화면 SQL을 옮기기 전에 바깥 집합의 크기(화면 20명, 배치 2,000명)부터 비교한다. 같은 SQL이라도 규모가 다르면 다른 계획이 맞다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황