분기 초마다 무너지는 실적 화면을 두고 세 사람이 서로 다른 통계 처방을 냈다

실전PLSQL한빛유통612 / 700

상황

3월 28일 분기 누적 화면 사고(통계의 최댓값 밖 추정) 뒤에 박팀장이 정책을 정하라고 했다. "T_ORD_BIG처럼 날마다 자라는 테이블은 통계를 어떻게 지킬지, 근거를 붙여서." 주문은 매일 01:00 적재 배치가 전날 주문 약 913건을 일반 INSERT로 싣는다. 3월 31일에 손으로 다시 모은 뒤로 통계는 그대로다. 세 사람이 안을 냈다. 오과장은 적재 배치 끝에서 매일 모으자고 했고, 정하은은 STALE_PERCENT를 10에서 1로 낮춰 자동 수집에 맡기자고 했다. 조민우는 인터넷에서 본 방법이라며 분기 첫날마다 ORD_DT의 최댓값을 그 분기 말일로 올려 두자고 했다. 조민우가 4월 14일(월) 복사본에서 세 안을 재 왔다.

테이블과 인덱스

T_ORD_BIG: 4월 14일 현재 1,094,039행 · 약 6,373블록 · 매일 01:00에 전날 주문 약 913건
인덱스: IX_ORD_BIG_DT_CUS_AMT (ORD_DT, CUS_ID, ORD_AMT) · IX_ORD_BIG_CUS_DT (CUS_ID, ORD_DT) · IX_ORD_BIG_ST_DT (ORD_ST, ORD_DT)
지금 통계: 3월 31일 10:20 수집 · NUM_ROWS 1,081,257 · ORD_DT 최댓값 2025-03-30 · NUM_DISTINCT 1,184 · 히스토그램 없음
수집 한 번: 테이블과 인덱스 셋 1.4 ~ 3.3초(10화 측정) · 자동 수집 창: 평일 22:00부터 4시간, 토 · 일 06:00부터 20시간
M_CUS 2,000행 · 30블록 · M_RGN 8행
날짜로 읽는 핵심 SQL 셋: (A) 분기 누적 지역별 매출(분기 첫날 ~ 다음 분기 첫날) (B) 어제 주문 등급별 집계(TRUNC(SYSDATE) - 1 이상 TRUNC(SYSDATE) 미만) (C) 고객 30일 주문 이력(CUS_ID = :c AND ORD_DT >= TRUNC(SYSDATE) - 30)

원본 SQL

sql

-- 지금 : 매일 01:00 주문 적재 (일반 INSERT · 통계는 자동 수집에 맡긴다 · STALE_PERCENT 10)
BEGIN
  INSERT 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_DAY;                       -- 전날 주문 약 913건
  COMMIT;
END;
/

-- 안 ① 오과장 : 적재 뒤에 매일 모은다
--   DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_ORD_BIG');
-- 안 ② 정하은 : 자동 수집이 더 자주 돌게 한다
--   DBMS_STATS.SET_TABLE_PREFS(USER, 'T_ORD_BIG', 'STALE_PERCENT', '1');
-- 안 ③ 조민우 : 분기 첫날마다 ORD_DT 최댓값을 그 분기 말일로 올려 둔다 (NUM_ROWS · NDV 는 그대로)
--   DBMS_STATS.SET_COLUMN_STATS(USER, 'T_ORD_BIG', 'ORD_DT', srec => ...);

실행계획 학습용 예시 · 복사본 측정 · 수집 시간은 교재 10화 측정

xplan

[(A) 분기 누적 · 4월 14일 · 지금 통계]
------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                       |      1 |        |      8 |00:00:00.02 |   12180 |
|   1 |  HASH GROUP BY                 |                       |      1 |      8 |      8 |00:00:00.02 |   12180 |
|*  2 |   HASH JOIN                    |                       |      1 |    913 |  11869 |00:00:00.02 |   12180 |
|   3 |    TABLE ACCESS FULL           | M_RGN                 |      1 |      8 |      8 |00:00:00.01 |       4 |
|   4 |    NESTED LOOPS                |                       |      1 |    913 |  11869 |00:00:00.02 |   12176 |
|   5 |     NESTED LOOPS               |                       |      1 |    913 |  11869 |00:00:00.01 |     307 |
|*  6 |      INDEX RANGE SCAN          | IX_ORD_BIG_DT_CUS_AMT |      1 |    913 |  11869 |00:00:00.01 |      57 |
|*  7 |      INDEX UNIQUE SCAN         | PK_M_CUS              |  11869 |      1 |  11869 |00:00:00.01 |     250 |
|   8 |     TABLE ACCESS BY INDEX ROWID| M_CUS                 |  11869 |      1 |  11869 |00:00:00.01 |   11869 |
------------------------------------------------------------------------------------------------------------
   2 - access("R"."RGN_ID"="C"."RGN_ID")
   6 - access("O"."ORD_DT">=TO_DATE(' 2025-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."ORD_DT"<TO_DATE(' 2025-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   7 - access("C"."CUS_ID"="O"."CUS_ID")

[복사본 측정 · 4월 14일 · E-Rows / A-Rows]
                   지금(3/31 수집)    ① 매일 수집       ② STALE 1 %         ③ 최댓값 6/30
 (A) 분기 누적       913 / 11,869     11,872 / 11,869   10,046 / 11,869 *   77,180 / 11,869
 (B) 어제            913 / 913           913 / 913         913 / 913         1,762 / 913
 (C) 고객 30일         7 / 14             14 / 14           13 / 14             45 / 14
 (A)의 계획          NL · 12,180        HASH · 91         HASH · 91           HASH · 91
 * ②는 4월 12일(토) 06:00 자동 수집 뒤의 값이다. 바뀐 행이 1%(10,813행)를 넘은 것은 4월 12일 01:00 적재 때였다.
   4월 10일에는 (A)가 913 / 8,217로 NL · 8,434블록이었다.
 ③의 통계는 4월 1일 SET_COLUMN_STATS 뒤 그대로다(NUM_ROWS 1,081,257 · NUM_DISTINCT 1,184).

문제

세 SQL 가운데 어느 것이 언제 틀리는지, 지금 통계와 세 안의 측정값으로 설명하시오. (B)가 어느 안에서나 맞는 이유도 쓰시오.
세 안을 구멍(틀리는 기간), 비용, 유지 부담으로 비교하고 하나를 골라 적재 배치를 고쳐 쓰시오.
고른 안이 막지 못하는 것과 함께 정할 것(커서 무효화 시각, 다른 SQL의 계획 변화)을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 통계 수집 정책서 3호 · T_ORD_BIG (날마다 자라는 테이블)
--   적재 COMMIT 직후, 같은 배치 안에서 모은다 → 최댓값을 늘 '어제'로 맞춘다
BEGIN
  INSERT 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_DAY;
  COMMIT;

  -- 01시대라 화면이 없다 → 커서를 바로 무효화해 아침 첫 실행부터 새 통계로 파싱한다
  DBMS_STATS.GATHER_TABLE_STATS(ownname       => USER,
                                tabname       => 'T_ORD_BIG',
                                cascade       => TRUE,
                                no_invalidate => FALSE);
END;
/

-- 기각 : ② STALE_PERCENT 1 (수집 사이 최대 12일의 구멍) · ③ SET_COLUMN_STATS (다음 수집이 덮는다)
-- 아침 점검 (09:00) : 핵심 SQL 셋의 계획 해시를 어제와 비교한다
SELECT s.SQL_ID, s.CHILD_NUMBER, s.PLAN_HASH_VALUE, s.LAST_ACTIVE_TIME
  FROM V$SQL s
 WHERE s.SQL_ID IN ('&sql_a', '&sql_b', '&sql_c')
 ORDER BY s.SQL_ID;

모범 답안 서술

어느 SQL이 언제 틀리나: 크게 틀리는 것은 (A)뿐이다. 4월 14일의 범위는 4월 1일부터인데 지금 통계의 최댓값은 3월 30일이다. 범위가 통째로 최댓값 밖이면 오라클은 하루치(1,081,257 / 1,184 = 913)로 고정하므로 (A)는 913으로 보고 NL을 골라 M_CUS를 11,869번 찾아갔다(12,180블록). 분기 첫날부터 통계를 다시 모을 때까지 날마다 틀리고, 날이 갈수록 실제가 커진다. (B)는 원래 하루짜리 범위라 최댓값 밖의 하루치 913이 실제(하루 913건)와 같다. 그래서 어느 안에서나 맞는다. (C)는 고객 한 명의 몇 건이라 7과 14의 차이가 계획을 바꾸지 못한다. 세 안의 비교: ① 매일 수집은 최댓값이 늘 어제여서 (A)가 11,872 / 11,869로 맞고 구멍이 없다. 비용은 하루 1.4 ~ 3.3초다. ② STALE_PERCENT 1은 바뀐 행이 1%(10,813행)를 넘어야 모이는데, 하루 913건이면 12일이 걸린다. 게다가 자동 수집 창은 평일 22:00에 열리고 적재는 01:00에 끝나므로 기준을 넘긴 날 바로 모이지도 않는다. 이번에도 4월 1일부터 12일까지 (A)는 913으로 봤다(4월 10일 NL · 8,434블록). 분기가 바뀔 때마다 열흘 남짓의 구멍이 생긴다. ③ 최댓값을 분기 말일로 올려 두면 (A)는 해시로 가지만 77,180 / 11,869로 크게 본다. NUM_ROWS와 NUM_DISTINCT는 그대로라 (B)와 (C)도 두세 배 크게 보고, 손으로 넣은 통계는 다음 수집(자동 수집이든 사람이든)이 덮어써서 조용히 사라진다. 분기마다 누군가 잊지 않고 돌려야 한다는 유지 부담도 크다. 고른 것: ①이다. 적재 배치의 COMMIT 직후, 같은 배치 안에서 GATHER_TABLE_STATS로 모은다. 01시대에는 화면이 없으므로 no_invalidate => FALSE로 커서를 바로 무효화한다. 기본값(AUTO_INVALIDATE)이면 무효화를 몇 시간에 걸쳐 흩어서 아침 첫 화면이 옛 계획을 그대로 쓸 수 있다. (A)의 계획은 해시로 돌아가 12,180 → 91블록이다. 막지 못하는 것과 함께 정할 것: 매일 모으면 이 테이블을 읽는 모든 SQL의 계획이 날마다 다시 정해진다. 좋은 계획만 바뀌는 것이 아니므로 09:00에 핵심 SQL 셋의 계획 해시를 어제와 비교하고, 바뀌면 그 SQL의 추정부터 본다. 낮 동안 쌓이는 데이터(오늘 주문)는 여전히 최댓값 밖이지만 하루치라 913 근처에서 맞는다. 정책서에는 대상 테이블, 수집 시점(적재 COMMIT 직후), 무효화 방식, 기각한 안 두 개와 그 이유를 적는다.

해설

최댓값 밖의 크기는 '통계의 나이'에 달려 있다. DATE 열은 최댓값에서 하루 이틀 벗어나도 추정이 거의 줄지 않는다(912, 887 …). 문제는 석 달 묵은 통계로 여러 날짜의 범위를 물을 때다. 그래서 처방의 핵심은 수집 시점이다(교재 10화 §7 STALE 10%의 함정).
동적 샘플링 힌트(DYNAMIC_SAMPLING(o 4))를 (A)에 넣는 안도 있다. 그러나 6,373블록에서 64블록 남짓을 뜨는 표본에는 최근 날짜 블록이 몇 개 들어가지 않아 숫자가 실행마다 흔들리고, 다른 SQL은 그대로다.
파티션 테이블이라면 새 칸의 통계를 미리 복사하거나 설정하는 방법이 따로 있다(14화, OPT-013 · OPT-014). 이 테이블은 파티션이 아니다.
SPM 베이스라인으로 (A)의 해시 계획을 고정하는 안은 통계를 고치지 않는다. 같은 테이블을 최근 날짜로 읽는 다른 SQL은 계속 913을 본다(OPT-004와 같은 이유).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 복사본 측정 · 수집 시간은 교재 10화 측정 ​

문제 ​

모범 답안 SQL ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황

테이블과 인덱스

원본 SQL

실행계획 학습용 예시 · 복사본 측정 · 수집 시간은 교재 10화 측정

문제

모범 답안 SQL

모범 답안 서술

해설

관련 교재