분기 누적 지역별 매출 화면이 읽는 블록이 1월 초 6천에서 3월 말 8만으로 늘었다

기초SELECT한빛유통611 / 700

상황

영업기획팀의 「분기 누적 지역별 매출」은 분기 첫날부터 지금까지의 주문을 지역별로 묶는다. 팀장 여덟 명이 하루에 수십 번 열고, 화면은 분기 첫날과 다음 분기 첫날을 리터럴로 넣는다. 1월 첫 주에는 6천 블록 남짓 읽던 SQL이 3월 28일 AWR에서 Buffer Gets 상위에 올라왔다. 지금은 한 번에 8만 블록을 읽는다. 2025년부터 주문은 매일 밤 약 913건씩 T_ORD_BIG 끝에 날짜순으로 붙고, 통계는 자동 수집에 맡겨 두었다.

테이블과 인덱스

T_ORD_BIG: 지금 1,078,518행 · 6,283블록(2024년까지 1,000,000행 + 2025-01-01 ~ 03-27의 78,518행)
통계: 2024-12-31 22:10 자동 수집 · NUM_ROWS 1,000,000 · ORD_DT NUM_DISTINCT 1,095 · 최솟값 2022-01-01 · 최댓값 2024-12-30 · 히스토그램 없음
STALE_PERCENT 10(기본값) · STALE_STATS NO · USER_TAB_MODIFICATIONS INSERTS 78,518
인덱스 IX_ORD_BIG_DT_CUS_AMT (ORD_DT, CUS_ID, ORD_AMT): 이 화면을 위해 작년에 만든 커버링 인덱스 · BLEVEL 2 · 리프 약 4,900블록
M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · RGN_ID 8곳 / M_RGN: 8행 · 4블록 · PK_M_RGN (RGN_ID)

원본 SQL

sql

SELECT r.RGN_NM, COUNT(*) AS ord_cnt, SUM(o.ORD_AMT) AS amt
  FROM T_ORD_BIG o, M_CUS c, M_RGN r
 WHERE o.ORD_DT >= DATE '2025-01-01'
   AND o.ORD_DT <  DATE '2025-04-01'
   AND c.CUS_ID = o.CUS_ID
   AND r.RGN_ID = c.RGN_ID
 GROUP BY r.RGN_NM

실행계획 학습용 예시 · 최댓값 밖 추정은 교재 10화 측정

xplan

[3월 28일]
------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                       |      1 |        |      8 |00:00:00.09 |   80538 |
|   1 |  HASH GROUP BY                 |                       |      1 |      8 |      8 |00:00:00.09 |   80538 |
|*  2 |   HASH JOIN                    |                       |      1 |    913 |  78518 |00:00:00.08 |   80538 |
|   3 |    TABLE ACCESS FULL           | M_RGN                 |      1 |      8 |      8 |00:00:00.01 |       4 |
|   4 |    NESTED LOOPS                |                       |      1 |    913 |  78518 |00:00:00.07 |   80534 |
|   5 |     NESTED LOOPS               |                       |      1 |    913 |  78518 |00:00:00.03 |    2016 |
|*  6 |      INDEX RANGE SCAN          | IX_ORD_BIG_DT_CUS_AMT |      1 |    913 |  78518 |00:00:00.01 |     360 |
|*  7 |      INDEX UNIQUE SCAN         | PK_M_CUS              |  78518 |      1 |  78518 |00:00:00.01 |    1656 |
|   8 |     TABLE ACCESS BY INDEX ROWID| M_CUS                 |  78518 |      1 |  78518 |00:00:00.03 |   78518 |
------------------------------------------------------------------------------------------------------------
   2 - access("R"."RGN_ID"="C"."RGN_ID")
   6 - access("O"."ORD_DT">=TO_DATE(' 2025-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."ORD_DT"<TO_DATE(' 2025-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   7 - access("C"."CUS_ID"="O"."CUS_ID")

[같은 SQL · 1월 7일]  같은 계획 · E-Rows 913 / A-Rows 6,391 · Buffers 6,560

문제

6번 줄의 E-Rows 913이 어디서 나온 숫자인지 통계로 설명하고, 날이 갈수록 Buffers가 늘어난 이유를 Starts로 설명하시오.
SQL은 그대로 두고 오늘 할 조치와, 다음 분기에 같은 일이 생기지 않게 할 방법을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 오늘 : 다시 모은다 (최댓값이 2025-03-27 로 올라간다 · 커서를 바로 무효화)
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname       => USER,
                                tabname       => 'T_ORD_BIG',
                                cascade       => TRUE,
                                no_invalidate => FALSE);
END;
/
-- ② 재발 방지 : 매일 밤 적재 배치의 COMMIT 바로 뒤에 같은 수집을 넣는다 (최댓값을 늘 '어제'로)

-- SQL 은 그대로
SELECT r.RGN_NM, COUNT(*) AS ord_cnt, SUM(o.ORD_AMT) AS amt
  FROM T_ORD_BIG o, M_CUS c, M_RGN r
 WHERE o.ORD_DT >= DATE '2025-01-01'
   AND o.ORD_DT <  DATE '2025-04-01'
   AND c.CUS_ID = o.CUS_ID
   AND r.RGN_ID = c.RGN_ID
 GROUP BY r.RGN_NM;

개선 후 실행계획 학습용 예시 · 최댓값 밖 추정은 교재 10화 측정

xplan

----------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                       |      1 |        |      8 |00:00:00.03 |     394 |          |
|   1 |  HASH GROUP BY       |                       |      1 |      8 |      8 |00:00:00.03 |     394 |          |
|*  2 |   HASH JOIN          |                       |      1 |  78537 |  78518 |00:00:00.03 |     394 | 1452K (0)|
|*  3 |    HASH JOIN         |                       |      1 |   2000 |   2000 |00:00:00.01 |      34 | 1230K (0)|
|   4 |     TABLE ACCESS FULL| M_RGN                 |      1 |      8 |      8 |00:00:00.01 |       4 |          |
|   5 |     TABLE ACCESS FULL| M_CUS                 |      1 |   2000 |   2000 |00:00:00.01 |      30 |          |
|*  6 |    INDEX RANGE SCAN  | IX_ORD_BIG_DT_CUS_AMT |      1 |  78537 |  78518 |00:00:00.01 |     360 |          |
----------------------------------------------------------------------------------------------------------
   2 - access("C"."CUS_ID"="O"."CUS_ID")
   3 - access("R"."RGN_ID"="C"."RGN_ID")
   6 - access("O"."ORD_DT">=TO_DATE(' 2025-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."ORD_DT"<TO_DATE(' 2025-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

모범 답안 서술

통계가 아는 ORD_DT의 최댓값은 2024-12-30인데, 조건은 2025년 1분기다. 범위가 통째로 통계의 최댓값 밖이면 오라클은 "그런 날짜는 거의 없다"고 보고 하루치(1,000,000 / 1,095 = 913)로 고정한다(교재 10화 §7). 그래서 1월 7일에도 3월 28일에도 6번 줄의 E-Rows는 913이다. 913건이라고 믿은 옵티마이저는 주문마다 고객을 한 명씩 찾아가는 NL을 골랐다. 실제로는 분기 첫날부터 지금까지의 주문 78,518건이 나와서 7번과 8번 줄의 Starts가 78,518이고, M_CUS 테이블에서만 78,518블록을 읽었다. 1월 7일에는 6,391번이었다. 주문이 하루 913건씩 늘 때마다 왕복도 913번씩 늘어서, 화면이 3월로 갈수록 무거워졌다. 통계가 석 달째 그대로인 것은 자동 수집의 기준 때문이다. STALE_PERCENT가 10이라 100만 행 테이블은 10만 행이 바뀌어야 다시 모으는데, 지금까지 바뀐 것은 78,518행(7.9%)이라 STALE_STATS가 NO다. 하루 913건이면 약 110일이 걸리므로 그 전에 분기가 끝난다. 오늘은 GATHER_TABLE_STATS로 다시 모은다(커서 무효화). 최댓값이 3월 27일로 올라가 E-Rows가 78,537이 되고, 고객 2,000명을 한 번 올려 두는 해시 조인으로 바뀐다. Buffers는 80,538 → 394다. 4월 1일이 되면 새 분기 범위가 다시 최댓값 밖이 되므로 같은 일이 되풀이된다. 그래서 매일 밤 적재 배치의 COMMIT 직후에 같은 수집을 넣어 최댓값을 늘 '어제'로 맞춘다.

해설

틀린 추정이 늘 나쁜 계획을 고르지는 않는다. 교재 10화 §7의 복사본에서는 같은 913으로 고른 계획이 오히려 덜 읽었다(5,008 대 6,031). 여기서는 커버링 인덱스 때문에 반대가 됐다. 맞아서가 아니라 운에 따라 갈리는 일이다.
STALE_PERCENT를 1로 낮추는 안도 있다. 그러나 자동 수집 창(평일 22:00)과 적재 시각(01:00)이 엇갈리고 수집 사이가 열흘 넘게 빌 수 있어서, 분기 초 며칠은 여전히 최댓값 밖이다. 여러 안의 비교는 OPT-022에서 다룬다.
USE_HASH 힌트로 이 SQL만 고정하면 같은 테이블을 최근 날짜로 읽는 다른 SQL은 계속 913을 본다. 통계를 고치면 모두 돌아온다.
통계가 있으면 기본 수준(2)의 동적 샘플링은 표본을 뜨지 않는다. "자동으로 세어 보겠지"는 통계가 아예 없을 때의 이야기다(10화 §5).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 최댓값 밖 추정은 교재 10화 측정 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 최댓값 밖 추정은 교재 10화 측정 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황