테마
통계 수집 정책서 (템플릿)
10화에서 제정했다. 테이블마다 "통계를 무엇으로 · 언제 · 어떻게 모으는가"를 정하고, 그 근거를 측정으로 남기는 문서다. T1에 "이 SQL을 어떻게 고쳤는가"를 적는다면, 이 문서에는 "옵티마이저가 보는 숫자를 누가 · 언제 만드는가"를 적는다. 옵티마이저는 데이터가 아니라 통계를 보고 계획을 세운다.
왜 이 문서가 있는가
이런 일이 두 번 있었다. 두 번 모두 SQL은 한 글자도 바뀌지 않았다.
| 화 | 무엇 | 옵티마이저가 본 것 | 실제 | 결과 (실측) |
|---|---|---|---|---|
| 01 | ORD_ST = 'CANCEL' | 값 3종 → 3분의 1 = 333,333 | 20,000 (2%) | 16배 오추정. 그때는 계획이 같아서 아무 일도 없었다 |
| 10 | 재적재 뒤의 Q1 · Q2 | T_ORD_BIG 0행 (적재 도중 빈 테이블을 모은 통계) | 1,000,000행 | Q1 Buffers 91 → 27,274 · Q2 5,869 → 1,026,852 (06화에서 힌트로 강제했던 그 NL) |
10화의 사고에는 근거가 둘 있었다. 둘 다 맞는 말이었다.
2019-09-02 hd.kang (재적재 배치 맨 끝)
적재 직후 통계 수집. 빼지 말 것.
자동 통계 수집 창이 TRUNCATE 직후의 빈 테이블을 수집한 적 있음 (월요일 오전 등급별 매출 지연)
(지난주) 오과장 (같은 배치)
통계 수집 단계 제외 — 적재 시간이 길어져 04:00 마감을 넘김. 자동 통계 수집이 매일 돈다.자동 통계 수집은 매일 돈다. 다만 언제 무엇을 보고 도는지를 문서로 정리해 둔 사람이 없었다.
통계는 '언젠가 누군가 모은 숫자'가 아니라 '관리하는 설정'이다. 그 설정의 이유를 적는 곳이 이 문서다.
일곱 원칙 — 전부 10화 실측이 근거다
| # | 원칙 | 근거 (10화 실측 · ora-hanorder 복사본) |
|---|---|---|
| P1 | 통계 상태가 계획을 정한다. 데이터가 아니다 | 같은 100만 행 · 같은 인덱스 · 같은 SQL. 통계 NUM_ROWS 0 → Q2 NESTED LOOPS 1,026,852 / 통계 복원 → HASH JOIN 5,868 |
| P2 | 적재 배치의 통계는 그 배치 '안에서', 적재 '직후'에 모은다. 자동 통계 수집 창에 기대지 않는다 | TRUNCATE 직후 STALE_STATS = YES · LIST STALE에 올라온다 → 창이 열려 있으면 빈 테이블을 수집한다. 일요일 창은 06:00부터 20시간 |
| P3 | 적재 방식이 통계를 바꾼다. 방식을 적는다 | 빈 테이블에 APPEND → 23ai 온라인 통계 수집(NOTES = STATS_ON_LOAD, 인덱스 통계도 갱신) / 일반 INSERT는 모으지 않는다 / NO_GATHER_OPTIMIZER_STATISTICS 힌트가 있으면 모으지 않는다 / 【14화】 온라인 통계는 히스토그램을 모으지 않는다. APPEND 재적재 직후 ORD_ST FREQUENCY → NONE. 히스토그램을 건 테이블은 APPEND로 바꿔도 적재 뒤 수집 단계를 남긴다(연습 DB 1.06초) |
| P4 | 틀린 통계는 없는 통계보다 나쁘다 | 통계를 지우면 동적 샘플링(level 2)이 표본을 떠서 Q2가 HASH JOIN 5,869 / 0행 통계가 있으면 그것을 믿고 NL |
| P5 | 히스토그램은 기본으로 끄고(SIZE 1), 측정으로 필요가 확인된 열에만 켠다. SIZE AUTO에 맡기지 않는다 | SIZE AUTO는 '그 열이 WHERE에 쓰였나'로 정한다(00_자료준비 04에서 밤사이 히스토그램이 생긴 사례). 버킷이 모자라면 오히려 틀린다. SIZE 2 HYBRID에서 CANCEL 1(실제 20,000) |
| P6 | 히스토그램 · 확장 통계는 '그 열을 함께 쓰는 SQL'로 영향을 잰다 | ORD_ST 히스토그램은 단독 조건의 추정은 바로잡지만 상관 AND의 추정은 더 틀리게 한다(CUS_ID와 함께: 167 → 10, 실제 500). 열에 히스토그램이 있으면 히스토그램 없는 컬럼 그룹은 무시된다 |
| P7 | 통계를 손으로 바꾸면(SET_*_STATS · LOCK · DELETE) 반드시 되돌리고, 되돌린 것을 쿼리로 확인한다 | USER_STATS = NO · 행수 확인. 통계 이력 보존 31일. RESTORE_TABLE_STATS로 '어제 것'을 되살릴 수 있다 |
날짜가 자라는 큰 테이블 — 최댓값 밖(out-of-range)
| 측정 | 값 |
|---|---|
STALE_PERCENT (기본) | 10% → 100만 행 테이블은 10만 행이 바뀌어야 '오래됨'이 된다. 하루 913건이면 약 110일 |
| 한 달치(27,390행 = 2.7%)를 더 실은 뒤 | STALE_STATS = NO. 자동 수집이 건너뛴다 |
| 그 한 달을 조회하면 | E-Rows 913 (하루치) vs A-Rows 27,390 |
| 같은 하루라도 최댓값에서 멀어질수록 (등치) | +1일 912 · +365일 609 · +730일 304 · +1,094일 1 (선형으로 줄어든다) |
"최신 날짜를 묻는 화면이 있는 테이블"은 P2(적재 직후 수집)나
STALE_PERCENT조정의 대상이다. 정책서에 그 화면을 적는다.
작성 규칙
| 규칙 | |
|---|---|
| 테이블 하나당 한 항목 | 같은 테이블의 열 설정은 한 항목 안에 적는다 |
| 「근거(측정)」와 「영향 분석」은 비울 수 없다 | 비어 있으면 반려한다. "좋다더라"만 믿고 히스토그램을 켜지 않는다 |
| 「적용하지 않은 것」도 적는다 | 재 보고 하지 않기로 한 것을 적어 두면 다음 사람의 시간을 아낄 수 있다 |
| 「재검토 조건」은 사건으로 쓴다 | "필요시"는 쓰지 않는다. "ORD_ST에 새 값이 생길 때", "바인드로 바뀔 때"처럼 쓴다 |
| 선호도로 건다 | GATHER_TABLE_STATS에 method_opt를 그때그때 주지 않는다. SET_TABLE_PREFS로 걸어서 자동 수집도 같은 규칙을 따르게 한다 |
양식
┌──────────────────────────────────────────────────────────────────────┐
│ 통계 수집 정책서 — ○호 │
├──────────────────────────────────────────────────────────────────────┤
│ 대상 : 스키마.테이블 ________ 행수 ______ · 적재 방식 ______ │
│ 관련 SQL : 조각 ID / 화면 ________________________________ │
│ 연결 리포트 : T1 __호 │
├──────────────────────────────────────────────────────────────────────┤
│ 1. 설정 │
│ METHOD_OPT 선호도 : ____________________________________________ │
│ 수집 시점 : (적재 직후 / 자동 수집 창 / 기타) _____________ │
│ 확장 통계 : (없음 / 컬럼 그룹 / 표현식) _____________________ │
│ │
│ 2. 근거 (측정) ★ 비우면 반려 │
│ 조건 / E-Rows 전 → 후 / A-Rows │
│ ________________________________________________________________ │
│ │
│ 3. 영향 분석 ★ 비우면 반려 (정합성 회귀 체크리스트 H) │
│ 이 테이블을 읽는 SQL 의 계획 해시 · Buffers 전 → 후 │
│ ________________________________________________________________ │
│ │
│ 4. 리스크 — 이 설정이 더 틀리게 만드는 경우 │
│ ________________________________________________________________ │
│ │
│ 5. 적용하지 않은 것과 이유 │
│ ________________________________________________________________ │
│ │
│ 6. 되돌리는 법 (그리고 확인 쿼리) │
│ ________________________________________________________________ │
│ │
│ 7. 재검토 조건 ★ 사건으로 │
│ ________________________________________________________________ │
│ │
│ 승인 : 튜너 ______ DBA ______ (배치 항목이면) 배치 담당 ______ │
└──────────────────────────────────────────────────────────────────────┘칸 설명
| 칸 | 적는 것 | 비었을 때 |
|---|---|---|
| 1. 설정 | GET_PREFS('METHOD_OPT', 소유자, 테이블)로 확인한 문자열 그대로 | 자동 수집이 무엇을 할지 아무도 모른다 |
| 2. 근거 ★ | 대표 조건마다 E-Rows 전 → 후와 A-Rows. 손계산이 있으면 같이 적는다 | 반려 |
| 3. 영향 분석 ★ | 그 테이블을 읽는 조각·화면 전부의 계획 해시와 Buffers 전 → 후 | 반려 |
| 4. 리스크 | 상관 AND · 바인드 변수 · 새 값 · 버킷 부족. 이 설정 때문에 오히려 틀리는 조건 | 다음 사고의 원인이 된다 |
| 5. 적용하지 않은 것 | 재 보고 기각한 안(예: 컬럼 그룹 · 다른 열의 히스토그램) | 다음 사람이 같은 실험을 반복한다 |
| 6. 되돌리는 법 | 선호도 원복 + 재수집 문장, 또는 RESTORE_TABLE_STATS 시각. 확인 쿼리 포함 | 13화에서 대가를 치른다 |
| 7. 재검토 조건 | 사건으로 | 반려 |
E-Rows vs A-Rows 추적 SOP — 계획이 이상할 때의 첫 30분
정책서를 쓰기 전에 어느 통계가 틀렸는지부터 짚는다. 자세한 내용은 10화
2_파고들기.md §1에 있다.
| 단계 | 할 일 | 도구 |
|---|---|---|
| ① | GATHER_PLAN_STATISTICS + DISPLAY_CURSOR(ALLSTATS LAST)로 실제 계획을 띄운다 | 01화 계기판 ② |
| ② | 줄마다 E-Rows × Starts 값과 A-Rows 값을 나란히 놓는다. 10배 이상 어긋난 가장 안쪽(아래) 줄이 출발점이다 | 10화 실습/02_손계산.sql [I] |
| ③ | 그 줄의 테이블 통계를 본다: NUM_ROWS · LAST_ANALYZED · STALE_STATS · 통계 이력 | USER_TAB_STATISTICS · USER_TAB_STATS_HISTORY |
| ④ | 그 줄의 조건을 손으로 계산한다. 손계산이 E-Rows와 맞으면 통계는 '읽힌 대로' 쓰였다는 뜻이다. 그렇다면 틀린 것은 통계 자체 또는 가정이다 | 10화 2_파고들기.md §2 |
| ⑤ | 원인을 하나로 지목한다 → 아래 표 | |
| ⑥ | 고친 뒤 같은 표를 다시 뽑아 그 줄이 맞는지, 6조각의 계획 해시가 그대로인지 확인한다 | 정합성 회귀 체크리스트 H |
| 지목 | 신호 | 처방 (이 정책서의 칸) |
|---|---|---|
| 통계가 낡았다 / 빈 테이블 통계 | NUM_ROWS ≠ 실제 · E-Rows 1 | 재수집 또는 RESTORE_TABLE_STATS (P2 · P7) |
| 분포를 모른다 (치우침) | 값마다 실제가 다른데 E-Rows가 같다(1/NDV) | 그 열에 히스토그램 (P5) |
| 최댓값 밖 | 최근 날짜 조건 · E-Rows가 하루치 | 적재 직후 수집 · STALE_PERCENT (out-of-range 표) |
| 상관 (독립 가정) | 조건 둘을 곱해 작아졌다 | 컬럼 그룹. 열에 히스토그램이 있으면 그룹에도 (P6) |
| 가공 · 표현식 | E-Rows가 1% · 5% · 0.25% 고정값 | SQL을 고친다(02·03화) → 못 고치면 표현식 통계 |
COUNT STOPKEY 아래 | 그 줄만 E-Rows가 크다(09화 포스트잇 999K) | 대조 대상이 아니다. VIEW 줄과 Cost로 본다 |
| 바인드 변수 | 같은 SQL이 값마다 다른 계획을 필요로 한다 | 11화 |
작성 예 (10화 · 1호 · 2호)
┌──────────────────────────────────────────────────────────────────────┐
│ 통계 수집 정책서 — 1호 │
├──────────────────────────────────────────────────────────────────────┤
│ 대상 : SQLP.T_ORD_BIG 1,000,000 행 · 재적재 (일반 INSERT) │
│ 관련 SQL : 01화 취소 건수 조회 · Q1 · Q2 · Q4 (6조각 중 이 표) │
│ 연결 리포트 : T1 8호 │
├──────────────────────────────────────────────────────────────────────┤
│ 1. 설정 │
│ METHOD_OPT 선호도 : FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 ORD_ST│
│ → ORD_ST 만 FREQUENCY (값 3종 · 버킷 3) │
│ 수집 시점 : 재적재 배치 맨 끝 (2호) │
│ 확장 통계 : 없음 │
│ │
│ 2. 근거 (측정) │
│ ORD_ST = 'CANCEL' 333,333 → 20,000 (A 20,000) │
│ ORD_ST = 'PENDING' 333,333 → 80,000 (A 80,000) │
│ ORD_ST = 'COMPLETE' 333,333 → 900,000 (A 900,000) │
│ 6월 AND CANCEL 9,445 → 567 (A 549) │
│ 없는 값 'CLOSED' 333,333 → 10,000 (A 0 · 가장 적은 값의 절반)│
│ │
│ 3. 영향 분석 (05_정책_적용.sql 전후 대조) │
│ 01 CANCEL 914281838 5,826 → 5,826 │
│ Q1 rev02 636720714 91 → 91 │
│ Q2 rev01 2292855265 5,868 → 5,868 │
│ Q3 rev01 2231864090 840 → 839 (FULL 의 1블록 흔들림) │
│ Q4 rev02 3610158121 15 → 15 │
│ → 6조각에 ORD_ST 조건이 없다. 계획 해시가 하나도 안 바뀌었다 │
│ │
│ 4. 리스크 │
│ · 상관 AND 에서 더 틀린다 : CUS_ID + ORD_ST 167 → 10 (A 500) │
│ → 이 조합을 쓰는 SQL 이 생기면 컬럼 그룹 + 그룹 히스토그램 (5번) │
│ · ORD_ST 를 바인드로 받는 SQL 이 생기면 값마다 다른 계획 → 11화 │
│ · 01~09화 스크립트 · 04_상태복구.sql 은 SIZE 1 로 다시 모은다 │
│ → 그 뒤에는 10화 05_정책_적용.sql 을 다시 돌린다 │
│ │
│ 5. 적용하지 않은 것 │
│ · (CUS_ID, ORD_ST) 컬럼 그룹 — 쓰는 SQL 이 아직 없다. 그룹만 만들면 │
│ ORD_ST 히스토그램 때문에 무시된다 (10 그대로). 그룹에도 히스토그램 │
│ (SIZE 2048 → FREQUENCY)을 줘야 500 │
│ · SIZE AUTO — 열 사용 기록으로 다른 열에도 히스토그램이 생길 수 있다 │
│ · 다른 열 히스토그램 — ORD_DT · CUS_ID · ORD_AMT 는 고르게 퍼져 있다 │
│ (하루 913~914 · 고객당 500 · 금액 4,901종) │
│ │
│ 6. 되돌리는 법 │
│ SET_TABLE_PREFS(USER,'T_ORD_BIG','METHOD_OPT','FOR ALL COLUMNS SIZE 1')│
│ GATHER_TABLE_STATS(USER,'T_ORD_BIG',cascade=>TRUE,no_invalidate=>FALSE)│
│ 확인: USER_TAB_COL_STATISTICS 의 HISTOGRAM 전부 NONE │
│ │
│ 7. 재검토 조건 │
│ · ORD_ST 에 새 값이 생길 때 (예: 'REFUND') │
│ · ORD_ST 를 조건으로 쓰는 화면·조각이 생길 때 — 특히 바인드로 │
│ · ORD_ST 와 다른 열을 함께 거는 SQL 이 생길 때 (4번의 상관) │
│ │
│ 승인 : 튜너 (나) 검토 윤세라 일자 5월 6일 │
└──────────────────────────────────────────────────────────────────────┘┌──────────────────────────────────────────────────────────────────────┐
│ 통계 수집 정책서 — 2호 (배치) │
├──────────────────────────────────────────────────────────────────────┤
│ 대상 : T_ORD_BIG 재적재 배치 (일요일 밤 · TRUNCATE + INSERT) │
│ 관련 SQL : Q1 · Q2 (5월 6일 월요일 오전 지연) │
│ 연결 리포트 : T1 8호 │
├──────────────────────────────────────────────────────────────────────┤
│ 1. 설정 │
│ 수집 시점 : 적재 COMMIT 직후, 같은 배치 안에서 │
│ GATHER_TABLE_STATS(..., cascade=>TRUE) — 선호도(1호)대로 │
│ 대안 : 적재를 INSERT /*+ APPEND */ 로 바꾸면 23ai 가 적재하며 │
│ 모은다 (STATS_ON_LOAD). 그때는 NOTES 를 확인하는 단계로 │
│ │
│ 2. 근거 (복사본 재현) │
│ 빈 테이블 통계 + 100만 행 : Q1 27,274 · Q2 1,026,852 (E-Rows 1) │
│ 어제 통계로 RESTORE : Q1 82 · Q2 5,868 │
│ 다시 수집 (1.2~1.4초) : Q2 5,868 │
│ APPEND 적재 : 온라인 통계 · Q1 82 · Q2 5,864 │
│ │
│ 3. 영향 분석 : 배치 시간 — 수집 단계가 다시 들어간다 (오과장과 합의) │
│ │
│ 4. 리스크 │
│ · 자동 수집 창과 적재가 겹치면 그 사이 빈 테이블 통계가 잠깐 생긴다 │
│ → 배치 끝의 수집이 덮어쓴다. 배치가 중간에 실패하면 남는다 (6번) │
│ │
│ 5. 적용하지 않은 것 │
│ · 통계 LOCK — 자동 수집은 막지만 배치의 수집에도 FORCE 가 필요해진다 │
│ 재적재 크기가 늘 같다는 전제가 깨지면 낡은 통계가 굳는다 │
│ │
│ 6. 되돌리는 법 (응급) │
│ RESTORE_TABLE_STATS(USER,'T_ORD_BIG', as_of_timestamp => TRUNCATE 전) │
│ → 몇 초. 통계 이력 보존 31일. 확인: NUM_ROWS · 인덱스 통계 │
│ │
│ 7. 재검토 조건 │
│ · 적재 방식이 바뀔 때 (일반 INSERT ↔ APPEND ↔ 파티션 교체 · 14화) │
│ · 적재 시각이나 자동 수집 창 시각이 바뀔 때 │
│ │
│ 승인 : 튜너 (나) 검토 윤세라 배치 담당 오과장 일자 5월 6일 │
└──────────────────────────────────────────────────────────────────────┘운용
| 언제 | 무엇 |
|---|---|
| 선호도를 걸 때마다 | 정책서 항목을 먼저 쓰고, 2 · 3번 칸을 측정으로 채운 뒤 건다 |
| 적재 배치를 바꿀 때 | 배치 항목(2호 형식)의 1 · 7번을 다시 본다. 수집 단계를 빼는 변경은 정책서 개정이다 |
| E-Rows가 10배 이상 어긋난 계획을 만났을 때 | 위 SOP ①~⑥을 따른다. 원인이 통계라면 이 문서의 해당 항목을 고친다 |
| 복구 스크립트(04_상태복구 등)를 돌렸을 때 | 정책 적용 스크립트를 다시 돌린다. 복구 스크립트에는 정책이 반영되어 있지 않다 |
| 정기 점검 (T8) | 항목마다 GET_PREFS와 히스토그램 종류가 정책서와 같은지 · STALE_STATS |
| 졸업 판정 (18화) | 6조각 원장 재측정 때 통계 상태(선호도 · 히스토그램 · 수집 시각)를 함께 적는다 |
관련: T1_단건SQL_튜닝리포트.md · 정합성_회귀_체크리스트.md · 힌트_유효기간_대장.md · 10화 2_파고들기.md
개정 이력: 10화에 제정했다(1호
ORD_ST· 2호 주간 재적재 배치). 14화에는 P3에 "온라인 통계는 히스토그램을 모으지 않는다"를 추가했다(APPEND재적재 실측).