통계를 매일 모으기 시작한 다음 날부터 지역 주간 주문 화면이 블록을 일흔 배 넘게 읽는다

중급PLSQL한빛유통641 / 700

상황

분기 누적 화면 사고 뒤에 한빛유통은 4월 16일 새벽부터 01:00 적재 배치 끝에서 T_ORD_BIG 통계를 매일 모은다. 그날 아침부터 영업 담당 40명이 쓰는 「지역 주간 주문」 화면(하루 약 3,000번)이 무거워졌다. 오과장은 이렇게 말했다. "매일 수집 때문이네요. 지난주 통계로 되돌리고 매일 수집은 빼죠." 선배는 고개를 저었다. "분기 화면은 그 수집 덕에 살아났어." 조민우가 AWR과 커서 캐시를 뽑았다.

테이블과 인덱스

T_ORD_BIG: 2024년 말까지 1,000,000행 + 2025년 1월부터 매일 01:00에 전날 주문 약 913건씩 날짜순으로 끝에 붙는다(4월 16일 현재 약 1,096,000행 · 6,386블록)
2022 ~ 2024년의 100만 행은 날짜와 상관없는 순서로 실려 있어 IX_ORD_BIG_DT (ORD_DT)의 CLUSTERING_FACTOR가 약 1,000,000이다. 2025년 행은 테이블 끝 블록에 날짜순으로 몰려 있다(하루치가 6블록 안팎).
M_CUS: 2,000행 · 30블록 · RGN_ID 8곳(지역마다 약 250명) · PK_M_CUS (CUS_ID)
통계: 4월 16일 01:12부터 매일 수집(무효화 기본값). 그 전 통계는 3월 31일 것(ORD_DT 최댓값 2025-03-30)
AWR: 1시간 간격 · 보존 8일 · Diagnostic Pack 계약 있음

원본 SQL

sql

-- 「지역 주간 주문」 (SQL_ID 4kq7c2v9x1m0d) : 지난 7일 주문을 지역 단위로
SELECT c.CUS_ID, c.CUS_NM, o.ORD_DT, o.ORD_ST, o.ORD_AMT
  FROM M_CUS c, T_ORD_BIG o
 WHERE c.RGN_ID = :rgn
   AND o.CUS_ID = c.CUS_ID
   AND o.ORD_DT >= TRUNC(SYSDATE) - 7
   AND o.ORD_DT <  TRUNC(SYSDATE)
 ORDER BY o.ORD_DT DESC, c.CUS_ID;

-- 오과장 안 : 통계를 지난주로 되돌린다 (매일 수집도 뺀다)
BEGIN
  DBMS_STATS.RESTORE_TABLE_STATS(USER, 'T_ORD_BIG',
    as_of_timestamp => TIMESTAMP '2025-04-15 23:00:00',
    no_invalidate   => FALSE);
END;
/

실행계획 학습용 예시

xplan

[DBA_HIST_SQLSTAT · SQL_ID 4kq7c2v9x1m0d]
 SNAP_ID  시작          PLAN_HASH_VALUE  EXECUTIONS_DELTA  BUFFER_GETS_DELTA  실행당 블록
    9101  04-15 10:00      3528841177               262             23,318           89
    9102  04-15 11:00      3528841177               275             24,475           89
    9122  04-16 07:00      3528841177                31              2,759           89
    9123  04-16 08:00      1201550986               248          1,594,640        6,430
    9124  04-16 09:00      1201550986               281          1,806,830        6,430

[DBA_OPTSTAT_OPERATIONS]
 04-16 01:12:07 ~ 01:12:09  gather_table_stats  SQLP.T_ORD_BIG  (적재 배치 · no_invalidate 기본값)

[V$SQL · 지금]  자식 0 하나 · PLAN_HASH_VALUE 1201550986 · LAST_LOAD_TIME 04-16 07:41 (3528841177 커서는 없다)

[지금 계획 · ALLSTATS LAST · :rgn = 3]
Plan hash value: 1201550986

-------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |      1 |        |    801 |00:00:00.06 |    6430 |          |
|   1 |  SORT ORDER BY      |           |      1 |    799 |    801 |00:00:00.06 |    6430 |   83968 (0)|
|*  2 |   HASH JOIN         |           |      1 |    799 |    801 |00:00:00.06 |    6430 | 1236K (0)|
|*  3 |    TABLE ACCESS FULL| M_CUS     |      1 |    250 |    250 |00:00:00.01 |      30 |          |
|*  4 |    TABLE ACCESS FULL| T_ORD_BIG |      1 |   6391 |   6391 |00:00:00.05 |    6400 |          |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("O"."CUS_ID"="C"."CUS_ID")
   3 - filter("C"."RGN_ID"=:RGN)
   4 - filter(("O"."ORD_DT">=TRUNC(SYSDATE@!)-7 AND "O"."ORD_DT"<TRUNC(SYSDATE@!)))

[4월 15일의 계획 · DBMS_XPLAN.DISPLAY_AWR('4kq7c2v9x1m0d', 3528841177) · AWR에는 실행 통계와 조건절이 남지 않는다]
---------------------------------------------------------------------------------
| Id  | Operation                             | Name          | Rows  | Cost    |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |               |       |     951 |
|   1 |  SORT ORDER BY                        |               |   114 |     951 |
|   2 |   HASH JOIN                           |               |   114 |     950 |
|   3 |    TABLE ACCESS FULL                  | M_CUS         |   250 |      11 |
|   4 |    TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_BIG     |   913 |     938 |
|   5 |     INDEX RANGE SCAN                  | IX_ORD_BIG_DT |   913 |       4 |
---------------------------------------------------------------------------------

문제

이 화면의 계획이 언제, 무엇 때문에 바뀌었는지 위 자료로 설명하시오. 통계는 맞아졌는데 왜 더 많이 읽는 계획을 골랐는지도 쓰시오.
오과장 안의 문제를 쓰고, 통계는 그대로 둔 채 이 화면만 4월 15일의 계획으로 돌아가게 하는 SQL을 쓰시오. 남는 일도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 통계는 그대로 둔다 (분기 누적 화면 같은 다른 SQL 은 매일 수집이 맞다)
-- 이 SQL 만 4월 15일의 계획(3528841177)을 지킨다 : 커서 캐시에는 없고 AWR 에만 남아 있다
DECLARE
  n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.LOAD_PLANS_FROM_AWR(
         begin_snap   => 9100,
         end_snap     => 9122,
         basic_filter => q'[sql_id = '4kq7c2v9x1m0d' and plan_hash_value = 3528841177]');
  DBMS_OUTPUT.PUT_LINE(n || ' plan(s) loaded');
END;
/

-- 확인 : 받아들인 계획 하나 (ORIGIN = MANUAL-LOAD-FROM-AWR)
SELECT b.SQL_HANDLE, b.PLAN_NAME, b.ORIGIN, b.ENABLED, b.ACCEPTED, b.FIXED
  FROM DBA_SQL_PLAN_BASELINES b
 WHERE b.SIGNATURE IN (SELECT s.EXACT_MATCHING_SIGNATURE FROM V$SQL s WHERE s.SQL_ID = '4kq7c2v9x1m0d');

-- 다음 하드 파싱부터 쓰인다. 기다리지 않으려면 지금 커서만 지운다
-- EXEC DBMS_SHARED_POOL.PURGE('&address,&hash_value', 'C');

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

xplan

Plan hash value: 3528841177

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |               |      1 |        |    801 |00:00:00.01 |      89 |          |
|   1 |  SORT ORDER BY                        |               |      1 |    799 |    801 |00:00:00.01 |      89 |   83968 (0)|
|*  2 |   HASH JOIN                           |               |      1 |    799 |    801 |00:00:00.01 |      89 | 1236K (0)|
|*  3 |    TABLE ACCESS FULL                  | M_CUS         |      1 |    250 |    250 |00:00:00.01 |      30 |          |
|   4 |    TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_BIG     |      1 |   6391 |   6391 |00:00:00.01 |      59 |          |
|*  5 |     INDEX RANGE SCAN                  | IX_ORD_BIG_DT |      1 |   6391 |   6391 |00:00:00.01 |      20 |          |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("O"."CUS_ID"="C"."CUS_ID")
   3 - filter("C"."RGN_ID"=:RGN)
   5 - access("O"."ORD_DT">=TRUNC(SYSDATE@!)-7 AND "O"."ORD_DT"<TRUNC(SYSDATE@!))

Note
-----
   - SQL plan baseline "SQL_PLAN_d8w2k3m1f7x0a4b6c9e2" used for this statement

모범 답안 서술

언제, 무엇이 바뀌었나: AWR의 SQL 이력에서 PLAN_HASH_VALUE가 4월 16일 07시 스냅샷까지는 3528841177, 08시부터는 1201550986이다. 실행당 블록은 89에서 6,430으로 늘었다. 그 사이에 있었던 일은 01:12의 T_ORD_BIG 통계 수집뿐이고, 지금 커서는 07:41에 새로 만들어졌다(무효화 기본값이라 수집 뒤 몇 시간 안에 다시 파싱된다). 4월 15일 계획은 커서 캐시에 없고 AWR에만 남아 있다. 왜 더 많이 읽는 계획을 골랐나: 옛 통계는 ORD_DT 최댓값이 3월 30일이라, 지난 7일을 통째로 범위 밖으로 보고 하루치 913건으로 셈했다. 913건이면 IX_ORD_BIG_DT로 찾는 편이 FULL보다 싸다. 새 통계로는 6,391건을 정확히 본다. 그런데 인덱스 경로의 비용은 CLUSTERING_FACTOR(약 1,000,000)로 셈하므로, 6,391건이면 테이블 블록을 6천 번 넘게 따로 읽는다고 보고 FULL(6,400블록)을 골랐다. 실제로는 2025년 행이 날짜순으로 테이블 끝에 몰려 있어서 인덱스로 가도 테이블 블록 39개만 읽는다. 추정은 맞아졌지만, 클러스터링 팩터는 테이블 전체의 평균이라 최근 행이 몰려 있다는 사실을 모른다. 오과장 안의 문제: 통계를 되돌리면 분기 누적 화면처럼 최근 날짜를 묻는 다른 SQL이 다시 범위 밖 추정으로 무너진다. 게다가 매일 수집을 그대로 두면 내일 01:00에 덮어쓰이고, 빼면 3월의 사고가 돌아온다. SQL 하나를 위해 테이블의 모든 SQL을 바꾸는 일이다. 응급 조치: 통계는 그대로 두고, 이 SQL만 4월 15일 계획을 지키게 한다. DBMS_SPM.LOAD_PLANS_FROM_AWR로 AWR에서 SQL_ID와 PLAN_HASH_VALUE 3528841177을 골라 베이스라인으로 올린다. 다음 파싱부터 Note에 SQL plan baseline ... used가 뜨고 Buffers는 6,430 → 89, 하루 3,000번이면 약 1,929만 → 27만 블록이다. 옵티마이저가 다시 FULL을 만들면 그 계획은 대기 줄(ACCEPTED NO)에 남는다. 남는 일: 베이스라인은 통계를 고치지 않는다. 최근 며칠을 읽는 다른 SQL도 같은 이유로 FULL을 골랐을 수 있으니 AWR 이력에서 4월 16일에 PLAN_HASH_VALUE가 바뀐 SQL을 함께 찾는다. 근본 처방(아래 해설의 커버링 인덱스나 월 파티션)을 정한 뒤에는 대기 줄의 계획을 진화로 다시 본다.

해설

AWR에서 계획을 올릴 때는 PLAN_HASH_VALUE까지 준다. basic_filter에 SQL_ID만 주면 구간 안의 계획이 모두 받아들인 계획으로 올라간다. 구간에 4월 16일 08시 스냅샷이 들어가면 FULL 계획까지 올라간다. 18c 이전 버전이라면 AWR 구간을 SQL 튜닝 세트에 담고(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY) LOAD_PLANS_FROM_SQLSET으로 올린다.
통계를 되돌리는 처방은 통계 자체가 틀렸을 때의 것이다(OPT-001의 빈 테이블 통계). 이번에는 통계가 맞고, 클러스터링 팩터라는 평균이 이 SQL의 범위를 대표하지 못할 뿐이다.
근본 처방 후보: 이 화면에 필요한 열을 모두 담은 (ORD_DT, CUS_ID, ORD_ST, ORD_AMT) 인덱스면 테이블에 가지 않으므로 클러스터링 팩터와 상관없이 옵티마이저가 스스로 인덱스를 고른다. 매일 913건을 넣을 때 인덱스를 하나 더 고치는 비용이 따른다(교재 14화). 월 파티션과 로컬 인덱스로 최근 행과 옛 행을 나누는 길도 있다.
커서가 밀려나기 전에 알아챘다면 LOAD_PLANS_FROM_CURSOR_CACHE로 올리는 편이 간단했다. AWR은 Diagnostic Pack 기능이고 보존이 8일이므로, 계획이 바뀐 것을 알아채는 데 일주일을 넘기면 이 길도 닫힌다(OPT-048).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황