결제 독촉 대상이 80건으로 줄었는데 독촉 목록 SQL은 여전히 읽은 블록 수 1위다

중급UPDATE한빛유통601 / 700

상황

7월부터 30일 넘게 결제 대기(PENDING)인 주문을 자동으로 취소하는 월 배치가 매달 1일 01:00에 돈다. 7월 1일 첫 실행에서 PENDING 8,000건 가운데 7,920건이 CANCEL로 바뀌었다. 상담팀의 「결제 독촉」 목록은 상담원 40명이 하루 약 6,000번 연다. 목록은 80건으로 줄었는데 화면 속도는 그대로이고, 7월 2일 AWR에서도 이 SQL이 Buffer Gets 1위다. 오과장: "통계는 밤마다 자동으로 모이잖아요."

테이블과 인덱스

T_ORD: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · IX_T_ORD_ST_DT (ORD_ST, ORD_DT)(리프 약 300블록)
ORD_ST 실제 분포(자동 취소 뒤): COMPLETE 90,000 · CANCEL 9,920 · PENDING 80
통계: 6월 29일(토) 자동 수집 · ORD_ST FREQUENCY 히스토그램(COMPLETE 90,000 · PENDING 8,000 · CANCEL 2,000) · STALE_PERCENT 10(기본값)
M_CUS: 2,000행 · 30블록 · PK_M_CUS
자동 통계 수집 창: 평일 22:00부터 4시간 · 주말 06:00부터 20시간

원본 SQL

sql

-- 매달 1일 01:00 · 자동 취소 배치 (7월부터)
UPDATE T_ORD
   SET ORD_ST = 'CANCEL'
 WHERE ORD_ST = 'PENDING'
   AND ORD_DT < TRUNC(SYSDATE) - 30;
COMMIT;

-- 상담 화면 「결제 독촉」 (하루 약 6,000번)
SELECT o.ORD_SEQ, o.CUS_ID, c.CUS_NM, o.ORD_DT, o.ORD_AMT
  FROM T_ORD o, M_CUS c
 WHERE o.ORD_ST = 'PENDING'
   AND c.CUS_ID = o.CUS_ID
 ORDER BY o.ORD_DT

실행계획 학습용 예시

xplan

[7월 2일 10:00 · 「결제 독촉」]
----------------------------------------------------------------------------------------------------
| Id  | Operation           | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |      1 |        |     80 |00:00:00.03 |     926 |          |
|   1 |  SORT ORDER BY      |        |      1 |   8000 |     80 |00:00:00.03 |     926 | 9216  (0)|
|*  2 |   HASH JOIN         |        |      1 |   8000 |     80 |00:00:00.03 |     926 | 1269K (0)|
|   3 |    TABLE ACCESS FULL| M_CUS  |      1 |   2000 |   2000 |00:00:00.01 |      30 |          |
|*  4 |    TABLE ACCESS FULL| T_ORD  |      1 |   8000 |     80 |00:00:00.02 |     896 |          |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C"."CUS_ID"="O"."CUS_ID")
   4 - filter("O"."ORD_ST"='PENDING')

[USER_TAB_STATISTICS · USER_TAB_MODIFICATIONS — T_ORD · 7월 2일 10:00]
 NUM_ROWS  LAST_ANALYZED   STALE_STATS   INSERTS   UPDATES   DELETES
  100,000  06-29 07:12     NO                  0     7,920         0

문제

4번 줄의 E-Rows 8,000이 어디서 왔는지 쓰고, 자동 수집이 이틀 밤을 돌았는데도 그 숫자가 그대로인 까닭을 쓰시오.
같은 일이 매달 생기지 않도록 배치를 고치시오. 고친 뒤의 계획과 일량도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 매달 1일 01:00 · 자동 취소 배치 — 분포를 바꾼 그 배치가 바로 다시 모은다 (선호도대로 → ORD_ST 히스토그램 포함)
BEGIN
  UPDATE T_ORD
     SET ORD_ST = 'CANCEL'
   WHERE ORD_ST = 'PENDING'
     AND ORD_DT < TRUNC(SYSDATE) - 30;
  COMMIT;
  DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'T_ORD', no_invalidate => FALSE);
END;
/

-- (보조) 다른 날의 큰 변경도 밤에 잡히도록 이 테이블만 5%로 낮춘다
EXEC DBMS_STATS.SET_TABLE_PREFS(USER, 'T_ORD', 'STALE_PERCENT', '5');

-- 화면 SQL 은 그대로
SELECT o.ORD_SEQ, o.CUS_ID, c.CUS_NM, o.ORD_DT, o.ORD_AMT
  FROM T_ORD o, M_CUS c
 WHERE o.ORD_ST = 'PENDING'
   AND c.CUS_ID = o.CUS_ID
 ORDER BY o.ORD_DT;

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

xplan

-------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |      1 |        |     80 |00:00:00.01 |     246 |
|   1 |  NESTED LOOPS                 |                |      1 |     80 |     80 |00:00:00.01 |     246 |
|   2 |   NESTED LOOPS                |                |      1 |     80 |     80 |00:00:00.01 |     166 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T_ORD          |      1 |     80 |     80 |00:00:00.01 |      84 |
|*  4 |     INDEX RANGE SCAN          | IX_T_ORD_ST_DT |      1 |     80 |     80 |00:00:00.01 |       3 |
|*  5 |    INDEX UNIQUE SCAN          | PK_M_CUS       |     80 |      1 |     80 |00:00:00.01 |      82 |
|   6 |   TABLE ACCESS BY INDEX ROWID | M_CUS          |     80 |      1 |     80 |00:00:00.01 |      80 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("O"."ORD_ST"='PENDING')
   5 - access("C"."CUS_ID"="O"."CUS_ID")

모범 답안 서술

4번 줄의 E-Rows 8,000은 ORD_ST FREQUENCY 히스토그램의 PENDING 건수다. 이 히스토그램은 6월 29일 자동 수집 때의 분포를 기억한다. 7월 1일 자동 취소 배치가 PENDING 7,920건을 CANCEL로 바꿨지만 히스토그램은 자동 취소 전 그대로이고, 실제 PENDING은 80건이다. 자동 수집이 이틀 밤을 돌고도 건너뛴 이유는 STALE 기준이다. 자동 수집은 마지막 수집 뒤 바뀐 행이 STALE_PERCENT(10%, 곧 10,000행)를 넘은 테이블만 다시 모은다. 7,920건은 7.9%라 STALE_STATS가 NO다. 행 수는 그대로이고 값의 분포만 크게 바뀌는 변경은 이 기준에 잘 걸리지 않는다. 8,000건이라고 본 옵티마이저는 인덱스로 8,000번 테이블에 가느니 T_ORD를 통째로 읽고 M_CUS와 해시 조인한 뒤 정렬하는 편을 골랐다(Buffers 926). 80건이면 IX_T_ORD_ST_DT로 PENDING만 날짜순으로 읽고 고객을 NL로 찾는 계획이 맞다. 인덱스가 날짜순이라 정렬도 사라진다. 분포를 바꾼 것은 이 배치이므로 다시 모을 때를 가장 잘 아는 것도 이 배치다. UPDATE를 COMMIT한 직후에 같은 배치에서 T_ORD를 선호도대로(히스토그램 포함) 모으고 커서를 무효화한다. 밤의 수집 창(22:00)까지 21시간을 기다리지 않아도 된다. 그러면 화면은 NL로 바뀌어 Buffers 926 → 246이고, 하루 6,000번이면 약 556만 → 148만 블록이다. 보조로 이 테이블의 STALE_PERCENT를 5로 낮춰 다른 날의 큰 변경도 밤에 잡히게 한다. 그 대가로 자동 수집이 이 테이블을 더 자주 모은다(896블록이라 부담은 작다).

해설

STALE 기준은 '행이 얼마나 바뀌었나'만 센다. 7,920건이 모두 한 값에서 다른 값으로 옮겨 가 히스토그램이 통째로 틀려도, 비율이 기준에 못 미치면 오래된 통계가 아니다.
STALE_PERCENT만 낮추는 안은 반쪽이다. 01:00에 바뀐 분포가 그날 22:00에야 반영되므로 하루 동안 6,000번이 그대로 FULL이다.
반대편도 본다. CANCEL은 2,000 → 9,920건이 되었으니 CANCEL로 거르는 다른 화면은 거꾸로 작게 본다. 히스토그램이 걸린 열을 바꾸는 배치는 그 열을 쓰는 SQL 목록과 함께 관리한다.
히스토그램을 지워 1/NDV로 돌아가면 PENDING을 33,333건으로 보아 더 나빠진다. 고칠 것은 히스토그램의 시점이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황