월 상품 실적 MERGE가 매달 1일에만 블록 18만 개를 읽는다

중급MERGE유통597 / 700

상황

본사 레거시 유통의 「월 상품 실적」 배치는 매달 1일 03:00에 돈다. 매장 POS가 보낸 지난달 판매상품 파일을 작업 테이블 W_SLE_MON에 올리고(sqlldr 일반 경로), 매장 · 상품별로 묶어 CF19에 MERGE한 뒤, 저장 공간을 돌려주려고 작업 테이블을 비운다. DBA의 야간 잡 PKG_STATS.NIGHTLY(매일 23:40)는 목록 표 STATS_TARGET의 테이블을 하나씩 GATHER_TABLE_STATS로 모은다. W_SLE_MON은 배치를 만들 때 목록에 넣었다. 선배가 물었다. "그 잡이 이 테이블을 모을 때 테이블에 뭐가 들어 있었을까?"

테이블과 인덱스

W_SLE_MON 월 판매상품 작업 테이블(이 문제용): SLE_NO · ITM_NO · SLE_QTY · SLE_UPRC · 채웠을 때 약 45,900행(6월분 45,912행) · 342블록 · 인덱스 없음
W_SLE_MON 통계: NUM_ROWS 0 · BLOCKS 0 · 마지막 수집 6월 30일 23:40(야간 잡 · 달마다 채운 지 몇 분 만에 비우므로 야간 잡은 늘 빈 테이블을 본다)
CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO) · 6월 판매 20,953건 · 통계 최신
CF19 상품 월 실적(이 문제용): 매장 6 × 상품 약 109 × 월 · 달마다 약 654행 · PK (SHP_NO, ITM_NO, SLE_YM) · 28블록
야간 잡은 테이블마다 따로 GATHER_TABLE_STATS를 부른다. 달마다 크기는 45,000행 안팎으로 비슷하다.

원본 SQL

sql

-- 매달 1일 03:00 · 월 상품 실적
--   ① sqlldr 로 W_SLE_MON 에 지난달 판매상품 파일을 올린다 (일반 경로 · 약 45,900행)
--   ② MERGE
MERGE INTO CF19 t
USING (SELECT a.SHP_NO, w.ITM_NO,
              SUM(w.SLE_QTY) AS qty, SUM(w.SLE_QTY * w.SLE_UPRC) AS amt
         FROM W_SLE_MON w, CF10 a
        WHERE a.SLE_NO = w.SLE_NO
        GROUP BY a.SHP_NO, w.ITM_NO) s
   ON (t.SHP_NO = s.SHP_NO AND t.ITM_NO = s.ITM_NO AND t.SLE_YM = :ym)
 WHEN MATCHED THEN
   UPDATE SET t.SLE_QTY = s.qty, t.SLE_AMT = s.amt
 WHEN NOT MATCHED THEN
   INSERT (SHP_NO, ITM_NO, SLE_YM, SLE_QTY, SLE_AMT)
   VALUES (s.SHP_NO, s.ITM_NO, :ym, s.qty, s.amt);
COMMIT;
--   ③ 저장 공간을 돌려준다
TRUNCATE TABLE W_SLE_MON;

실행계획 학습용 예시

xplan

[7월 1일 03:00 · ② MERGE]
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                     |           |      1 |        |      0 |00:00:01.86 |    186K |   312 |
|   1 |  MERGE                              | CF19      |      1 |        |      0 |00:00:01.86 |    186K |   312 |
|   2 |   VIEW                              |           |      1 |        |    654 |00:00:01.84 |    184K |   312 |
|*  3 |    HASH JOIN OUTER                  |           |      1 |      1 |    654 |00:00:01.84 |    184K |   312 |
|   4 |     VIEW                            |           |      1 |      1 |    654 |00:00:01.83 |    184K |   312 |
|   5 |      HASH GROUP BY                  |           |      1 |      1 |    654 |00:00:01.83 |    184K |   312 |
|   6 |       NESTED LOOPS                  |           |      1 |      1 |  45912 |00:00:01.79 |    184K |   312 |
|   7 |        NESTED LOOPS                 |           |      1 |      1 |  45912 |00:00:00.93 |    138K |   118 |
|   8 |         TABLE ACCESS FULL           | W_SLE_MON |      1 |      1 |  45912 |00:00:00.01 |     342 |     0 |
|*  9 |         INDEX UNIQUE SCAN           | CF10_PK   |  45912 |      1 |  45912 |00:00:00.88 |    138K |   118 |
|  10 |        TABLE ACCESS BY INDEX ROWID  | CF10      |  45912 |      1 |  45912 |00:00:00.81 |   45912 |   194 |
|  11 |     TABLE ACCESS FULL               | CF19      |      1 |    654 |   3924 |00:00:00.01 |      28 |     0 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T"."SLE_YM"=:YM AND "T"."ITM_NO"="S"."ITM_NO" AND "T"."SHP_NO"="S"."SHP_NO")
   9 - access("A"."SLE_NO"="W"."SLE_NO")

[USER_TAB_STATISTICS — W_SLE_MON]
 NUM_ROWS  BLOCKS  LAST_ANALYZED   STALE_STATS   STATTYPE_LOCKED
        0       0  06-30 23:40     NO            -

문제

8번 줄의 E-Rows 1이 어떻게 만들어졌는지 배치와 야간 잡의 시각으로 설명하고, 그 때문에 9번과 10번 줄에서 일어난 일을 숫자로 쓰시오.
이 테이블의 통계를 대표 크기로 잠그는 방법을 SQL로 쓰시오. 야간 잡과 부딪치는 곳과 잠금이 맞는 조건도 쓰시오.
다른 방법(끝에 비우지 않기, 적재 직후 수집, 통계를 지우고 잠가 동적 샘플링)과 견주시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 대표 크기일 때 한 번 모으고 잠근다 (6월분 45,912행을 다시 올려 둔 상태에서)
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname => 'PUP', tabname => 'W_SLE_MON', no_invalidate => FALSE);
  DBMS_STATS.LOCK_TABLE_STATS(ownname => 'PUP', tabname => 'W_SLE_MON');
END;
/
-- ② 야간 잡 목록에서 뺀다 (잠긴 테이블에 GATHER_TABLE_STATS 를 부르면 ORA-20005)
DELETE FROM STATS_TARGET WHERE TABLE_NAME = 'W_SLE_MON';
COMMIT;

-- ③ 배치 (sqlldr → MERGE → TRUNCATE) 는 그대로 · TRUNCATE 는 통계를 지우지 않고, 잠근 통계는 남는다
MERGE INTO CF19 t
USING (SELECT a.SHP_NO, w.ITM_NO,
              SUM(w.SLE_QTY) AS qty, SUM(w.SLE_QTY * w.SLE_UPRC) AS amt
         FROM W_SLE_MON w, CF10 a
        WHERE a.SLE_NO = w.SLE_NO
        GROUP BY a.SHP_NO, w.ITM_NO) s
   ON (t.SHP_NO = s.SHP_NO AND t.ITM_NO = s.ITM_NO AND t.SLE_YM = :ym)
 WHEN MATCHED THEN
   UPDATE SET t.SLE_QTY = s.qty, t.SLE_AMT = s.amt
 WHEN NOT MATCHED THEN
   INSERT (SHP_NO, ITM_NO, SLE_YM, SLE_QTY, SLE_AMT)
   VALUES (s.SHP_NO, s.ITM_NO, :ym, s.qty, s.amt);

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

xplan

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                     |           |      1 |        |      0 |00:00:00.41 |    5009 |  4583 |
|   1 |  MERGE                              | CF19      |      1 |        |      0 |00:00:00.41 |    5009 |  4583 |
|   2 |   VIEW                              |           |      1 |        |    654 |00:00:00.40 |    4953 |  4583 |
|*  3 |    HASH JOIN OUTER                  |           |      1 |    654 |    654 |00:00:00.40 |    4953 |  4583 |
|   4 |     VIEW                            |           |      1 |    654 |    654 |00:00:00.39 |    4925 |  4583 |
|   5 |      HASH GROUP BY                  |           |      1 |    654 |    654 |00:00:00.39 |    4925 |  4583 |
|*  6 |       HASH JOIN                     |           |      1 |  45912 |  45912 |00:00:00.35 |    4925 |  4583 |
|   7 |        TABLE ACCESS FULL            | W_SLE_MON |      1 |  45912 |  45912 |00:00:00.01 |     342 |     0 |
|   8 |        TABLE ACCESS FULL            | CF10      |      1 |    500K|    500K|00:00:00.29 |    4583 |  4583 |
|   9 |     TABLE ACCESS FULL               | CF19      |      1 |    654 |   3924 |00:00:00.01 |      28 |     0 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T"."SLE_YM"=:YM AND "T"."ITM_NO"="S"."ITM_NO" AND "T"."SHP_NO"="S"."SHP_NO")
   6 - access("A"."SLE_NO"="W"."SLE_NO")

모범 답안 서술

W_SLE_MON은 한 달에 한 번, 1일 03:00부터 몇 분 동안만 차 있다. 배치가 끝나면 비우므로 23:40에 도는 야간 잡은 늘 빈 테이블을 모은다. 그래서 통계가 NUM_ROWS 0이고, 8번 줄의 E-Rows는 1이다. 옵티마이저는 한 행이라면 CF10_PK로 한 번 찾아가면 된다고 보고 NL을 골랐다. 실제로는 45,912행이라 9번 줄 Starts가 45,912, 인덱스에서만 138K블록을 읽고 10번 줄에서 테이블을 다시 45,912번 읽었다(합계 Buffers 184K, 단일 블록 읽기 312번). 이 테이블은 비었다 찼다를 되풀이하지만 찰 때의 크기는 달마다 45,000행 안팎으로 비슷하다. 이런 테이블은 대표 크기일 때 통계를 한 번 모으고 잠그는 것이 맞다. 6월분을 다시 올려 둔 상태에서 모은 뒤 LOCK_TABLE_STATS로 잠그면, 비운 뒤에도(TRUNCATE는 통계를 지우지 않는다) 옵티마이저는 45,912행으로 본다. MERGE는 해시 조인으로 CF10을 한 번 읽고 Buffers가 186K → 5,009로 준다. 대가와 주의가 있다. 잠긴 테이블에 GATHER_TABLE_STATS를 부르면 ORA-20005로 실패하므로, 테이블마다 따로 부르는 야간 잡의 목록에서도 함께 뺀다. 크기가 철마다 열 배씩 달라지는 테이블이라면 잠금은 낡은 숫자를 굳힐 뿐이다. 그때는 적재 직후 수집이 맞다. 정책서에는 잠근 이유와 대표 크기(45,912행), 다시 볼 조건(크기가 두 배 넘게 바뀌면)을 적는다. 다른 방법도 견줘 보자. 가장 싼 답은 끝의 TRUNCATE를 배치 맨 앞으로 옮겨 지난달 데이터를 남겨 두는 것이다. 그러면 야간 잡이 대표 통계를 모으고, 대가는 342블록(약 2.7MB)뿐이다. 적재 직후 수집은 sqlldr 다음에 한 단계를 더한다(1초 남짓). 통계를 지우고(DELETE_TABLE_STATS) 잠그면 하드 파싱 때마다 동적 샘플링(레벨 2, 64블록)으로 세어 보는데, 커서가 공유 풀에 남아 있으면 다시 세지 않는다는 점을 알고 써야 한다.

해설

잠금이 맞는 테이블은 크기가 들쭉날쭉한 테이블이 아니라, 비어 있는 때와 차 있는 때가 번갈아 오되 찰 때의 크기가 비슷한 테이블이다. 휘발성 작업 테이블이 대표적이다.
GATHER_SCHEMA_STATS는 잠긴 테이블을 건너뛰지만 GATHER_TABLE_STATS는 에러를 낸다. 야간 잡이 루프 안에서 에러를 어떻게 처리하는지도 확인한다. 예외 하나에 루프 전체가 끝나는 잡이면 뒤의 테이블이 모두 멈춘다(OPT-018).
CF10_PK가 문제가 아니다. 통계가 맞으면 옵티마이저는 이 인덱스를 쓰지 않고 해시 조인을 고른다. 힌트(USE_HASH)로 MERGE만 고치면 같은 테이블을 읽는 다른 단계가 같은 오판을 한다.
잠근 통계를 바꿀 때는 UNLOCK_TABLE_STATS → 수집 → LOCK_TABLE_STATS 순서로 하거나 force => TRUE를 준다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황