배포도 없었는데 월요일 아침 등급별 매출 화면이 30초 걸린다

기초PLSQL한빛유통591 / 700

상황

08:41에 박팀장이 알려 왔다. "등급별 매출 화면이 오늘 아침부터 30초 걸린답니다. 어제 당직이 볼 때는 0.1초였고, 주말 사이 배포는 없었습니다." 화면의 SQL(Q2)은 06화에서 고친 그대로다. 일요일 22:30에 T_ORD_BIG 재적재(비우고 100만 건 다시 싣기)가 돌았다. 오과장은 이번 주부터 배치 맨 끝의 통계 수집 단계를 뺐다. 오과장의 설명은 이렇다. "인덱스가 셋이 되고 04시를 넘겨요. 자동 통계 수집이 밤마다 도니까 중복이죠."

테이블과 인덱스

T_ORD_BIG: 실제 1,000,000행 · 5,826블록 · 통계 NUM_ROWS 0 · BLOCKS 0 · 마지막 수집 일요일 22:33 · STALE_STATS NO
M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID)
M_RGN: 8행 · PK_M_RGN (RGN_ID)
자동 통계 수집 창: 평일 22:00부터 4시간 · 토요일과 일요일은 06:00부터 20시간 · STALE인 객체만 · 선호도대로 · 인덱스까지
TRUNCATE하는 순간 테이블은 STALE_STATS = YES가 된다. 통계 이력은 31일 동안 보존한다.

원본 SQL

sql

-- 일요일 밤 재적재 (오과장 · 이번 주 판) — 원천 사본에서 100만 건을 다시 싣는다 (일반 INSERT)
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE T_ORD_BIG';
  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 T_ORD_BIG_SRC;
  COMMIT;
  -- 2019-09-02 hd.kang : 적재 직후 통계 수집. 빼지 말 것.
  -- DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_ORD_BIG');     ← 이번 주에 뺐다 (04시를 넘겨서)
END;
/

-- (참고) 느려진 화면 Q2 — SQL 은 06화 그대로다. 고치지 않는다
SELECT c.CUS_GD, COUNT(*) AS ord_cnt, SUM(o.ORD_AMT) AS amt,
       MAX((SELECT r.RGN_NM FROM M_RGN r WHERE r.RGN_ID = c.RGN_ID)) AS rgn_sample
  FROM T_ORD_BIG o, M_CUS c
 WHERE c.CUS_ID = o.CUS_ID
 GROUP BY c.CUS_GD;

실행계획 실측 · 교재 10화(복사본 재현)

xplan

[월요일 09:40 — 통계와 실제]
 TABLE_NAME   NUM_ROWS  BLOCKS  LAST_ANALYZED   STALE_STATS   실제 COUNT(*)
 T_ORD_BIG           0       0  일 22:33:36     NO               1,000,000

[Q2 — 월요일 아침 (복사본에서 일요일 밤의 순서를 그대로 재현)]
Plan hash value: 2289018667

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |           |      1 |        |      3 |00:00:00.79 |    1026K|
|   1 |  TABLE ACCESS BY INDEX ROWID  | M_RGN     |      8 |      1 |      8 |00:00:00.01 |      12 |
|*  2 |   INDEX UNIQUE SCAN           | PK_M_RGN  |      8 |      1 |      8 |00:00:00.01 |       4 |
|   3 |  HASH GROUP BY                |           |      1 |      1 |      3 |00:00:00.79 |    1026K|
|   4 |   NESTED LOOPS                |           |      1 |      1 |   1000K|00:00:00.76 |    1026K|
|   5 |    NESTED LOOPS               |           |      1 |      1 |   1000K|00:00:00.42 |   26840 |
|   6 |     TABLE ACCESS FULL         | T_ORD_BIG |      1 |      1 |   1000K|00:00:00.13 |    5829 |
|*  7 |     INDEX UNIQUE SCAN         | PK_M_CUS  |   1000K|      1 |   1000K|00:00:00.15 |   21011 |
|   8 |    TABLE ACCESS BY INDEX ROWID| M_CUS     |   1000K|      1 |   1000K|00:00:00.21 |    1000K|
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("R"."RGN_ID"=:B1)
   7 - access("C"."CUS_ID"="O"."CUS_ID")

[같은 Q2 · 지난 일요일 통계 (재적재 전에 모은 1,000,000 행)]  HASH JOIN · Buffers 5,868 · 0.18 초

문제

코드가 그대로인데 계획이 바뀐 이유를 실행계획의 어느 칸에서 알 수 있는지 쓰시오. 통계가 언제 0이 되었는지도 쓰시오.
오늘 아침 운영에서 할 응급 조치와, 다음 일요일에 같은 일이 생기지 않도록 고친 배치를 쓰시오. 통계를 잠그는 안과도 비교하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 오늘 아침 응급 (운영 · 업무 시간) — 재적재 전 시각의 통계로 되돌린다 (테이블 · 열 · 인덱스 통계 함께 · 이력 31일)
BEGIN
  DBMS_STATS.RESTORE_TABLE_STATS(ownname         => USER,
                                 tabname         => 'T_ORD_BIG',
                                 as_of_timestamp => TIMESTAMP '2024-05-05 22:00:00',
                                 no_invalidate   => FALSE);
END;
/

-- ② 재발 방지 — 적재 COMMIT 직후, 같은 배치 안에서 모은다 (선호도대로 · 인덱스까지 · 커서 무효화)
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE T_ORD_BIG';
  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 T_ORD_BIG_SRC;
  COMMIT;
  -- 2019-09-02 hd.kang : 적재 직후 통계 수집. 빼지 말 것.
  -- 2024-05-06 재확인 : 빼면 자동 수집 창(토 · 일 06:00~)이 TRUNCATE 직후의 빈 테이블을 모은다
  --                     → 월요일 오전 Q2 가 NL(E-Rows 1)로 바뀐다 (통계 수집 정책서 2호)
  DBMS_STATS.GATHER_TABLE_STATS(ownname       => USER,
                                tabname       => 'T_ORD_BIG',
                                cascade       => TRUE,
                                no_invalidate => FALSE);
END;
/

개선 후 실행계획 실측 · 교재 10화(복사본 재현)

xplan

[Q2 — RESTORE_TABLE_STATS 뒤 (SQL 은 그대로)]
Plan hash value: 2292855265

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |      1 |        |      3 |00:00:00.25 |    5868 |          |
|   1 |  TABLE ACCESS BY INDEX ROWID | M_RGN     |      8 |      1 |      8 |00:00:00.01 |      12 |          |
|*  2 |   INDEX UNIQUE SCAN          | PK_M_RGN  |      8 |      1 |      8 |00:00:00.01 |       4 |          |
|   3 |  HASH GROUP BY               |           |      1 |      3 |      3 |00:00:00.25 |    5868 |          |
|*  4 |   HASH JOIN                  |           |      1 |   1000K|   1000K|00:00:00.16 |    5856 | 1685K (0)|
|   5 |    TABLE ACCESS FULL         | M_CUS     |      1 |   2000 |   2000 |00:00:00.01 |      30 |          |
|   6 |    TABLE ACCESS FULL         | T_ORD_BIG |      1 |   1000K|   1000K|00:00:00.05 |    5826 |          |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("R"."RGN_ID"=:B1)
   4 - access("C"."CUS_ID"="O"."CUS_ID")

모범 답안 서술

계획의 E-Rows가 모든 줄에서 1이다. 통계가 NUM_ROWS 0이어서 옵티마이저는 T_ORD_BIG에 한 줄이 있다고 믿었다. 한 줄짜리 테이블이라면 고객을 한 명씩 찾아가는 NL이 맞는 판단이다. 그 결과 7번과 8번 줄의 Starts가 1000K로, 주문 100만 건마다 PK_M_CUS와 M_CUS를 왕복했다. Buffers는 5,868 → 1,026,852가 됐다. 판단은 옳았지만 판단의 근거인 통계가 틀렸다. 0은 일요일 밤에 만들어졌다. TRUNCATE하는 순간 테이블이 '오래된 통계' 목록에 오르고, 일요일 06:00부터 20시간 동안 열린 자동 통계 수집 창이 적재가 커밋되기 전의 빈 테이블을 모았다(22:33, 0행). 그 뒤 100만 행이 들어왔지만 통계는 다시 모이지 않았다. 오라클은 통계가 없으면 동적 샘플링으로 세어 보지만, 0이라고 적혀 있으면 그대로 믿는다. 자동 수집이 '밤마다 돈다'는 오과장의 말은 맞았다. 빠진 것은 '언제'였다. 응급 조치로는 통계를 되돌린다. 데이터는 지난주와 같은 100만 건이므로 재적재 전의 통계가 맞는 통계다. RESTORE_TABLE_STATS로 그 시각의 통계(인덱스 통계 포함)를 되돌리고 커서를 무효화하면 Q2는 해시 조인으로 5,868블록을 읽는다. 업무 시간에 다시 수집하는 것보다 짧고 확실하다. 재발을 막으려면 적재 COMMIT 직후, 같은 배치 안에 GATHER_TABLE_STATS를 다시 넣고 2019년 주석 옆에 '왜'를 적는다. 다른 방법으로 INSERT /*+ APPEND */로 바꾸면 빈 테이블에 직접 경로로 실을 때 통계를 함께 모은다(온라인 통계). 그때는 적재 방식이 통계를 정한다는 사실까지 문서에 남긴다. 통계 잠금(LOCK_TABLE_STATS)은 자동 수집이 빈 테이블 통계로 덮어쓰는 것은 막지만, 배치에서 수집할 때도 force가 필요해지고 재적재 크기가 바뀌는 날 낡은 통계가 굳는다. 그래서 잠금은 고르지 않는다. 수집 시점과 대안, 응급 조치, 기각한 안을 통계 수집 정책서에 남긴다.

해설

통계를 지우면 어떻게 되나? 통계가 아예 없으면 오라클은 파싱할 때 표본을 떠서(동적 샘플링) 해시를 고른다(E-Rows 889,390, 5,869블록). 틀린 통계가 없는 통계보다 나쁘다는 뜻이다. 그래도 '지우기'는 처방이 아니다.
문턱이 있다. 통계 행수만 바꿔 보면 2,000과 2,500 사이에서 NL과 해시가 갈린다(M_CUS 2,000행). 통계가 100배 틀려도(1만 행) 문턱을 넘지 않으면 계획은 그대로다. 이날은 0이라서 문턱을 넘었다.
힌트로 Q2만 고정하는 안은 기각한다. 같은 0행 통계를 보는 Q1도 함께 무너졌고(91 → 27,274), 06화에서 뗀 힌트를 다시 박는 일이기도 하다. 통계를 고치면 둘 다 돌아온다.
04시가 빠듯한 진짜 이유는 인덱스 셋을 단 채 일반 INSERT로 100만 행을 넣는 비용이다(연습 DB 9분 13초). 1초 남짓 걸리는 통계 수집을 빼서 풀 일이 아니다(14화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 실측 · 교재 10화(복사본 재현) ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 실측 · 교재 10화(복사본 재현) ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황