체인 맨 앞에서 통계를 한 번에 다 모았는데 월요일 아침 두 곳이 무너졌다

실전PLSQL한빛유통605 / 700

상황

일요일 22:00 야간 체인은 네 단계다. 오과장이 체인을 정리하면서 ① 뒤의 통계 수집 단계를 뺐고("APPEND가 알아서 모은다"), 대신 체인 맨 앞(21:50)에 GATHER_SCHEMA_STATS 한 줄을 넣었다("한 번에 다 모으자"). 월말과 일요일이 겹친 6월 30일 밤 ④ 재처리가 15초 가까이 걸렸고, 7월 1일 월요일 09:00 정산팀의 「6월 취소 주문」 조회가 지난주보다 열 배 무거워졌다. 박팀장: "통계 정책을 체인 단위로 다시 짜 주세요. 테이블마다 언제, 어떻게, 왜."

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 매주 일요일 재적재(원천 STG_ORD_BIG) · 인덱스 IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT) · IX_ORD_BIG_ST_DT (ORD_ST, ORD_DT)(클러스터링 팩터 약 99만) · 선호도 FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 ORD_ST(정책서 1호)
T_ORD_STLM: 100,000행 · 640블록 · 인덱스 없음 · 체인마다 비우고 다시 싣는다 · 히스토그램이 필요한 열 없음
W_STLM_ERR 정산 오류 작업 테이블(이 문제용): ORD_SEQ · ERR_CD · 평소 0 ~ 300행, 월말 약 20,000행(64블록) · 인덱스 없음 · 체인 끝에 DELETE로 비운다
T_ORD: 100,000행 · 896블록 · PK_T_ORD · V_STLM_CHECK는 정산과 주문을 견주는 점검 뷰다.
GT_CUS_PICK: 상담 화면의 전역 임시 테이블 · 통계 없음 · M_CUS · M_RGN · M_ITM은 거의 바뀌지 않는다.
자동 통계 수집 창은 체인과 겹치지 않게 일요일 20:00에 닫힌다.

원본 SQL

sql

-- 일요일 21:50 · 야간 체인 (오과장 정리판)
BEGIN
  -- "한 번에 다 모으자"
  DBMS_STATS.GATHER_SCHEMA_STATS(ownname => USER, no_invalidate => FALSE);

  -- ① T_ORD_BIG 재적재 (통계 수집 단계는 뺐다 : "APPEND 가 알아서 모은다")
  EXECUTE IMMEDIATE 'TRUNCATE TABLE T_ORD_BIG';
  INSERT /*+ APPEND */ INTO T_ORD_BIG (ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT)
  SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT FROM STG_ORD_BIG;
  COMMIT;

  -- ② T_ORD_STLM 정산 적재
  EXECUTE IMMEDIATE 'TRUNCATE TABLE T_ORD_STLM';
  INSERT /*+ APPEND */ INTO T_ORD_STLM (STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT)
  SELECT ROWNUM, ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT FROM T_ORD;
  COMMIT;

  -- ③ 정산 오류 건 (평소 0 ~ 300행 · 월말 약 20,000행)
  INSERT INTO W_STLM_ERR (ORD_SEQ, ERR_CD)
  SELECT ORD_SEQ, ERR_CD FROM V_STLM_CHECK;
  COMMIT;

  -- ④ 재처리 → 비우기
  UPDATE T_ORD_STLM s
     SET s.STLM_AMT = (SELECT o.ORD_AMT FROM T_ORD o WHERE o.ORD_SEQ = s.ORD_SEQ)
   WHERE s.ORD_SEQ IN (SELECT e.ORD_SEQ FROM W_STLM_ERR e);
  COMMIT;
  DELETE FROM W_STLM_ERR;
  COMMIT;
END;

실행계획 학습용 예시 · 교재 10화와 14화의 실측 숫자

xplan

[USER_TAB_STATISTICS — 7월 1일(월) 09:00]
 TABLE_NAME    NUM_ROWS   LAST_ANALYZED   비고
 T_ORD_BIG    1,000,000   일 22:04        NOTES = STATS_ON_LOAD · ORD_ST HISTOGRAM NONE
 T_ORD_STLM     100,000   일 22:06        NOTES = STATS_ON_LOAD
 W_STLM_ERR           0   일 21:51        (체인 맨 앞의 GATHER_SCHEMA_STATS)
 T_ORD          100,000   일 21:50
 M_CUS            2,000   일 21:50

[A] 7월 1일(월) 09:00 · 「6월 취소 주문」
    SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT FROM T_ORD_BIG
     WHERE ORD_ST = 'CANCEL' AND ORD_DT >= DATE '2024-06-01' AND ORD_DT < DATE '2024-07-01'
---------------------------------------------------------------------------------------
| Id  | Operation         | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |      1 |        |    549 |00:00:00.06 |    5826 |
|*  1 |  TABLE ACCESS FULL| T_ORD_BIG |      1 |   9445 |    549 |00:00:00.06 |    5826 |
---------------------------------------------------------------------------------------
   1 - filter(("ORD_ST"='CANCEL' AND "ORD_DT">=TO_DATE(' 2024-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
              AND "ORD_DT"<TO_DATE(' 2024-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
    (지난주 같은 조회 · ORD_ST FREQUENCY 가 있을 때 : E-Rows 567 · IX_ORD_BIG_ST_DT 범위 스캔 · Buffers 556)

[B] 6월 30일(일) 23:40 · ④ 재처리 (월말 · 오류 20,000건)
----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |            |      1 |        |      0 |00:00:14.82 |     12M |
|   1 |  UPDATE                      | T_ORD_STLM |      1 |        |      0 |00:00:14.82 |     12M |
|   2 |   NESTED LOOPS               |            |      1 |      1 |  20000 |00:00:14.60 |     12M |
|   3 |    SORT UNIQUE               |            |      1 |      1 |  20000 |00:00:00.02 |      64 |
|   4 |     TABLE ACCESS FULL        | W_STLM_ERR |      1 |      1 |  20000 |00:00:00.01 |      64 |
|*  5 |    TABLE ACCESS FULL         | T_ORD_STLM |  20000 |      1 |  20000 |00:00:14.55 |     12M |
|   6 |   TABLE ACCESS BY INDEX ROWID| T_ORD      |  20000 |      1 |  20000 |00:00:00.05 |   40000 |
|*  7 |    INDEX UNIQUE SCAN         | PK_T_ORD   |  20000 |      1 |  20000 |00:00:00.03 |   20000 |
----------------------------------------------------------------------------------------------
   5 - filter("S"."ORD_SEQ"="E"."ORD_SEQ")
   7 - access("O"."ORD_SEQ"=:B1)

문제

[A]와 [B]의 원인을 각각 그 테이블의 통계가 언제, 어떤 상태에서 모였는지로 설명하시오.
체인의 테이블마다(T_ORD_BIG, T_ORD_STLM, W_STLM_ERR, 마스터, GT_CUS_PICK) 통계를 언제 어떻게 모을지 정하고, 고친 체인을 쓰시오.
고른 방식마다의 대가와, 통계 수집 정책서에 남길 재검토 조건을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 일요일 22:00 · 야간 체인 (통계 정책 반영판) — 체인 맨 앞의 GATHER_SCHEMA_STATS 는 뺀다 (적재 '전' 상태를 모을 뿐이다)
BEGIN
  -- ① T_ORD_BIG : 재적재 COMMIT 직후 선호도(정책서 1호)대로 모은다 — 온라인 통계는 ORD_ST 히스토그램을 모으지 않는다 (1초 남짓)
  EXECUTE IMMEDIATE 'TRUNCATE TABLE T_ORD_BIG';
  INSERT /*+ APPEND */ INTO T_ORD_BIG (ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT)
  SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT FROM STG_ORD_BIG;
  COMMIT;
  DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'T_ORD_BIG', no_invalidate => FALSE);

  -- ② T_ORD_STLM : 빈 테이블에 직접 경로 = 온라인 통계로 충분하다 (히스토그램이 필요한 열이 없다 · 따로 모으지 않는다)
  EXECUTE IMMEDIATE 'TRUNCATE TABLE T_ORD_STLM';
  INSERT /*+ APPEND */ INTO T_ORD_STLM (STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT)
  SELECT ROWNUM, ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT FROM T_ORD;
  COMMIT;

  -- ③ W_STLM_ERR : 채운 직후 모은다 (0 ~ 20,000 행으로 달마다 달라 잠그지 않는다 · 64블록이라 1초 미만)
  INSERT INTO W_STLM_ERR (ORD_SEQ, ERR_CD)
  SELECT ORD_SEQ, ERR_CD FROM V_STLM_CHECK;
  COMMIT;
  DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'W_STLM_ERR', no_invalidate => FALSE);

  -- ④ 재처리 → 비우기 (SQL 은 그대로)
  UPDATE T_ORD_STLM s
     SET s.STLM_AMT = (SELECT o.ORD_AMT FROM T_ORD o WHERE o.ORD_SEQ = s.ORD_SEQ)
   WHERE s.ORD_SEQ IN (SELECT e.ORD_SEQ FROM W_STLM_ERR e);
  COMMIT;
  DELETE FROM W_STLM_ERR;
  COMMIT;
END;
-- M_CUS · M_RGN · M_ITM · T_ORD : 자동 수집 창에 맡긴다 (STALE 10%)
-- GT_CUS_PICK : 통계를 두지 않는다 (동적 샘플링 · gather_temp 는 기본값 FALSE)

개선 후 실행계획 학습용 예시 · 교재 10화와 14화의 실측 숫자

xplan

[A] 「6월 취소 주문」 · ① 뒤 수집으로 ORD_ST FREQUENCY 가 돌아온 뒤
------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                  |      1 |        |    549 |00:00:00.01 |     556 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_BIG        |      1 |    567 |    549 |00:00:00.01 |     556 |
|*  2 |   INDEX RANGE SCAN                  | IX_ORD_BIG_ST_DT |      1 |    567 |    549 |00:00:00.01 |       4 |
------------------------------------------------------------------------------------------------------------

[B] ④ 재처리 · ③ 뒤에 모은 W_STLM_ERR (20,000행) 로
----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |            |      1 |        |      0 |00:00:00.21 |     61K |
|   1 |  UPDATE                      | T_ORD_STLM |      1 |        |      0 |00:00:00.21 |     61K |
|*  2 |   HASH JOIN RIGHT SEMI       |            |      1 |  20000 |  20000 |00:00:00.03 |     704 |
|   3 |    TABLE ACCESS FULL         | W_STLM_ERR |      1 |  20000 |  20000 |00:00:00.01 |      64 |
|   4 |    TABLE ACCESS FULL         | T_ORD_STLM |      1 |    100K|    100K|00:00:00.01 |     640 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T_ORD      |  20000 |      1 |  20000 |00:00:00.05 |   40000 |
|*  6 |    INDEX UNIQUE SCAN         | PK_T_ORD   |  20000 |      1 |  20000 |00:00:00.03 |   20000 |
----------------------------------------------------------------------------------------------

모범 답안 서술

[A]의 원인: 21:50의 스키마 수집은 재적재 전의 T_ORD_BIG을 선호도대로(ORD_ST FREQUENCY 포함) 모았다. 그런데 22:04 ①의 TRUNCATE → INSERT /*+ APPEND */가 빈 테이블에 직접 경로로 실으면서 온라인 통계(STATS_ON_LOAD)로 테이블과 열 통계를 새로 썼다. 온라인 통계는 히스토그램을 모으지 않으므로 ORD_ST가 NONE이 되었다. 6월 AND CANCEL을 히스토그램 없이 셈하면 9,445건(실제 549)이라 인덱스 대신 FULL로 5,826블록을 읽는다. 지난주처럼 FREQUENCY가 있으면 567건으로 보고 IX_ORD_BIG_ST_DT로 556블록이다. [B]의 원인: 21:51의 스키마 수집은 지난 체인 끝에 비운 W_STLM_ERR를 0행으로 모았다. 월말 ③이 20,000행을 넣었지만 통계는 0 그대로라 ④에서 E-Rows 1로 보고, 오류 한 건마다 인덱스 없는 T_ORD_STLM을 통째로 읽는 NL을 골랐다(5번 줄 Starts 20,000 × 640블록 = 12.8M). 체인 맨 앞의 수집은 적재 전의 상태를 모을 뿐이다. 테이블별 정책: T_ORD_BIG은 재적재 COMMIT 직후 같은 체인에서 선호도대로 모은다(1초 남짓). T_ORD_STLM은 빈 테이블에 직접 경로로 실어 온라인 통계로 충분하다. 히스토그램이 필요한 열이 없기 때문이다. W_STLM_ERR는 크기가 0 ~ 20,000행으로 달마다 달라 잠그면 안 되고, 채운 직후 no_invalidate => FALSE로 모은다. 마스터(M_CUS, M_RGN, M_ITM)와 T_ORD는 자동 수집에 맡긴다. GT_CUS_PICK은 통계를 두지 않고 동적 샘플링에 맡긴다. 그러면 [A]는 Buffers 5,826 → 556, [B]는 12M → 61K(14.8초 → 0.21초)다. 대가와 재검토 조건: 체인에 수집 두 번(약 1.5초)이 늘고, 체인 안의 수집은 커서를 바로 무효화하므로 그 테이블을 읽는 SQL이 다시 파싱된다(새벽이라 부담이 작다). 정책서에는 테이블마다 수집 시점과 방법, 그 근거(온라인 통계는 히스토그램을 모으지 않는다, 비우는 테이블은 채운 뒤에)를 적고, 재검토 조건을 남긴다. T_ORD_STLM에 히스토그램이 필요한 열이 생기면 적재 뒤 수집을 더한다. W_STLM_ERR가 늘 비슷한 크기가 되면 대표 통계를 잠그는 쪽을 다시 본다. T_ORD_BIG 재적재가 일반 INSERT로 돌아가면 ① 뒤 수집이 유일한 수집이 된다.

해설

"한 번에 다 모으자"의 함정은 '한 번'이 아니라 '언제'에 있다. 체인 맨 앞에서는 체인이 바꿀 테이블들이 모두 바뀌기 전 상태다. 수집 시점은 테이블마다 그 테이블이 확정된 직후다.
APPEND는 통계를 모아 주지만 다 모아 주지는 않는다. 행 수, 블록, 열의 NDV와 최솟값, 최댓값, 인덱스 통계는 모으고 히스토그램은 모으지 않는다(14화 §4.5). 빈 세그먼트가 아니면(DELETE로 비운 테이블) 아예 모으지 않는다.
[B]만 보고 T_ORD_STLM에 인덱스를 만드는 안은 체인마다 10만 행을 다시 싣는 테이블에 적재 비용을 더한다(14화). 통계가 맞으면 해시 세미 조인으로 640블록을 한 번 읽는다.
체인이 자동 수집 창과 겹친다면 ③과 ④ 사이에 자동 수집이 끼어들 수 있다. 이 문제에서는 창을 20:00에 닫았다. 그렇지 않다면 OPT-001과 같은 일이 생긴다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 교재 10화와 14화의 실측 숫자 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 교재 10화와 14화의 실측 숫자 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황