테마
10화 · 어제는 0.1초 오늘은 30초
5월 6일 월요일, 코드는 한 글자도 안 바뀌었는데 화면 두 개가 느려졌다. 오늘은 옵티마이저가 무엇을 보고 계획을 세우는지 처음으로 정면에서 본다. 그리고 01화에서 미뤄 둔 16배를 갚는다.
오전 8시 50분
자리에 앉기도 전에 박팀장 메시지가 와 있었다.
박팀장 08:41
정산팀 조민우 씨 건입니다. 등급별 매출 화면이 오늘 아침부터 30초 걸린답니다.
어제 당직이 볼 때는 0.1초였다고 합니다.
기간별 주문 조회도 느리다는 문의가 같이 들어왔습니다.
주말 사이 배포는 없었습니다. 확인 부탁드립니다.등급별 매출은 Q2였다. 06화에서 힌트 29글자를 지워 1,026,849블록을 5,869블록으로 줄인 바로 그 조각이다. 기간별 주문 조회는 02화와 03화에서 91블록으로 줄인 Q1이었다.
둘 다 내가 고친 조각이었다.
선배가 가방을 내려놓으며 화면을 봤다. "네 조각 두 개가 같이 느려졌네."
"배포는 없었대요."
"그럼 뭐부터 볼래?"
오전 9시 10분 — 코드는 그대로다
매주 처음 하는 일부터 했다. 리비전을 열어 보니 Q2_rev01.sql은 06화에 만든 그대로였고, Q1_rev03.sql도 03화 그대로였다. 운영 패키지의 소스와도 대조했다. 한 글자도 다르지 않았다.
그리고 연습 DB에서 두 조각을 돌렸다.
TAG PHV BUFFERS MS FIRST_READ E_ROWS A_ROWS
[A1] 636720714 91 3.3 INDEX RANGE SCAN IX_ORD_BIG_DT_AMT 28336 27390
[A2] 2292855265 5869 177.4 TABLE ACCESS FULL T_ORD_BIG 1000000 100000091, 5,869. Q2는 0.18초였다. 조민우 씨가 말한 "어제" 그대로였다.
"여기선 멀쩡해요."
"그래서?"
07화가 떠올랐다. 히트율은 99%인데 서버가 타던 날이었다. "운영 서버가 바쁜 거 아닐까요. 월요일 아침이라 동시 사용자가 몰렸거나…"
"월요일 아침은 매주 와." 선배가 의자를 끌어왔다. "지난주 월요일엔 0.1초였고, 이번 주에만 30초야. 같은 것과 다른 것을 나눠 봐. 코드는 같아. 그럼 뭐가 남지?"
"…데이터요?"
"데이터, 그리고 하나 더." 선배는 답을 말하지 않고 전화기를 들었다. "오과장님, 주말에 T_ORD_BIG 건드리신 거 있어요?"
오전 9시 40분 — 일요일 밤에 있었던 일
오과장이 노트북을 들고 왔다. 배치 담당으로, 04시 마감에 매여 사는 사람이다.
"있죠. 일요일 밤 재적재요. T_ORD_BIG을 비우고 원천에서 100만 건을 다시 싣는 거. 매주 하던 거예요."
"매주 하던 거면, 이번 주에 바뀐 건요?"
오과장이 잠깐 말을 멈췄다. "…마지막 단계 하나를 뺐어요. 통계 수집이요. 인덱스가 세 개가 되고 나서 적재가 길어졌거든요. 통계까지 돌리면 04시를 넘겨요. 그런데 어차피 밤마다 자동 통계 수집이 돌잖아요. 중복이라서 뺐죠."
인덱스 세 개는 02화의 두 개와 09화의 하나였다. 나는 속으로 움찔했다.
선배는 운영 딕셔너리를 조회할 권한이 있었다. 한 줄을 쳤다.
sql
SELECT num_rows, blocks, last_analyzed FROM dba_tab_statistics
WHERE owner = 'SQLP' AND table_name = 'T_ORD_BIG';"운영 T_ORD_BIG 통계 행수가 0이야. 마지막 수집은 일요일 밤이고."
"0이요? 지금 100만 건이 다 들어 있는데요."
"들어 있지. 그런데 옵티마이저가 알고 있는 건 0이야."
오과장이 배치 스크립트를 열었다. 뺀 단계 바로 위에 주석이 하나 남아 있었다.
-- 2019-09-02 hd.kang
-- 적재 직후 통계 수집. 빼지 말 것.
-- 자동 통계 수집 창이 TRUNCATE 직후의 빈 테이블을 수집한 적 있음 (월요일 오전 등급별 매출 지연)강도현은 이 일을 이미 겪었다. 2019년의 어느 월요일이었다.
오과장이 머리를 긁적였다. "이거… 봤는데요. '자동 수집이 있으니 중복'이라고 읽었어요."
"틀린 말은 아니에요." 선배가 말했다. "자동 수집은 정말로 밤마다 돌아요. 강도현 씨는 언제 도는지를 적었고, 오과장님은 돈다는 것을 읽으신 거죠. 둘 다 맞는 말이에요. 다만 그 사이에 적혀 있지 않은 게 하나 있었어요."
오전 10시 10분 — 일요일 밤을 복사본에서 재현한다
"재현해 볼게요." 내가 말했다. "연습 DB의 T_ORD_BIG을 TRUNCATE하고…"
"안 돼." 선배가 바로 끊었다. "그 테이블 위에 지난 아홉 주의 숫자가 다 올라가 있어. 한 번 비우면 91도 5,869도 다시는 같은 값으로 안 나와. 복사본 떠."
복사본 T_ORD_BIG_C10을 만들었다. 100만 행에 인덱스 세 개(원본과 같은 컬럼)를 달고, 통계 선호도도 원본처럼 SIZE 1로 맞췄다. 통계를 모으고 Q1과 Q2를 돌렸다. 91, 5,863. 어제의 운영 그대로였다.
그리고 일요일 밤을 순서대로 되풀이했다. 먼저 TRUNCATE부터 했다.
TABLE_NAME NUM_ROWS STALE_S
T_ORD_BIG_C10 1000000 YES
TABLE_NAME INSERTS DELETES TRU
T_ORD_BIG_C10 0 1000000 YES
LIST STALE -> T_ORD_BIG_C10"TRUNCATE하는 순간 이 테이블은 '통계가 오래됐다'는 상태가 돼. LIST STALE은 자동 수집이 오늘 밤 고를 목록이야. 거기 올라왔지." 선배가 연습 DB의 자동 수집 창을 띄웠다. 평일은 밤 10시부터 4시간, 일요일은 아침 6시부터 20시간이었다. "일요일 밤 재적재는 통째로 이 창 안에서 돌아. 적재가 커밋되기 전에 자동 수집이 이 테이블을 집으면 무엇을 볼까?"
"…빈 테이블이요."
그 장면은 손으로 대신했다. 자동 수집이 하는 것처럼 선호도대로, 인덱스까지 수집했다.
TABLE_NAME NUM_ROWS BLOCKS LA STALE_S
T_ORD_BIG_C10 0 0 22:33:36 NO그리고 100만 행을 다시 실었다.
💡 재현 조건. 운영 배치는 일반
INSERT방식이다. 연습 DB에서 인덱스 세 개를 단 채 일반 INSERT로 100만 행을 넣으면 9분 13초가 걸렸다(실측. 10MB짜리 리두 로그 두 개가 계속 차서 기다린다. 다른 작업과 겹친 날은 22분 46초였다. 그 청구서는 14화에서 온다). 그래서 같은 통계 상태를 1분 안에(실측 9~36초) 만드는 힌트INSERT /*+ APPEND NO_GATHER_OPTIMIZER_STATISTICS */를 썼다. 이 힌트가 무엇을 끄는지는 잠시 뒤에 나온다. 일반 INSERT 판은실습/90_적재_일반INSERT.sql에 있다.
적재가 끝났다. 오과장의 새 배치처럼 통계 수집은 없었다.
이제 월요일 아침이었다. 같은 Q1과 Q2를 돌렸다. SQL은 한 글자도 안 바꿨다.
xplan
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.02 | 27274 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.02 | 27274 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_BIG_C10 | 1 | 1 | 27390 |00:00:00.14 | 27274 |
|* 3 | INDEX RANGE SCAN | IX_C10_DT | 1 | 1 | 27390 |00:00:00.01 | 69 |Q1은 91 → 27,274로 뛰었다. 커버링 인덱스를 두고 날짜 인덱스를 타면서 테이블을 2만 7천 번 다녀왔다. 02화에서 힌트로 억지로 태웠을 때의 27,466과 거의 같았다.
xplan
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.73 | 1026K |
| 3 | HASH GROUP BY | | 1 | 1 | 3 |00:00:00.73 | 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_C10 | 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 |Q2는 5,863 → 1,026,852였다. NESTED LOOPS였고, M_CUS 쪽 Starts가 1000K였다.
그 숫자는 알고 있었다. 06화 첫 장면에서 강도현의 USE_NL 힌트가 만들던 1,026,849였다. 세 블록 차이였다.
"선배님. 이거… 06화의 그 계획이에요. 힌트는 지웠는데요."
"4층 카드함 기억나?" 선배가 말했다. "주문 한 장마다 계단을 올라가 고객 카드를 한 장씩 꺼내 오는 거. 거기서 너는 카드함을 통째로 복사해 책상에 펼쳤어. 그게 해시 조인이야. 그런데 오늘 옵티마이저는 다시 계단을 오르기로 했어. 누가 시킨 게 아니라 스스로."
"왜요?"
"E-Rows 칸 읽어 봐."
1. 계획의 모든 줄이 1이었다. 옵티마이저는 T_ORD_BIG_C10에 한 줄만 있다고 믿고 있었다. 한 줄짜리 테이블이라면 고객 카드를 한 번 가져오는 게 맞다. 카드함을 복사할 이유가 없다. 판단은 옳았다. 믿음이 틀렸을 뿐이다.
"06화에서 힌트를 지우면서 네가 뭐라고 했지?"
"'조인 방식의 선택을 옵티마이저에게 돌려준다'…"
"그래. 그런데 옵티마이저는 무엇을 보고 고르지?" 선배가 통계 한 줄과 실제 한 줄을 나란히 띄웠다.
WHAT NUM_ROWS BLOCKS
STATS 0 0
ACTUAL 1000000"옵티마이저는 데이터를 보지 않아. 통계를 봐. 힌트를 지운 그날, 너는 판단을 옵티마이저에게 돌려줬고, 그 판단의 재료는 통계에 맡긴 거야. 오늘 그 재료가 0이었어."
오전 10시 50분 — 0이면 세어 보면 되잖아요
좀 억울했다. "그래도 이상하잖아요. 100만 건짜리 테이블이 0건일 리가 없는데, 의심스러우면 실제로 세어 보면 되지 않아요?"
"세게 해 줄까?" 선배가 말했다. "통계를 아예 지워 봐. 0이 아니라 '없음'으로."
복사본을 다시 비우고, 통계를 지우고(DELETE_TABLE_STATS), 같은 100만 행을 실었다. 그리고 Q2를 돌렸다.
xplan
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.17 | 5869 |
|* 4 | HASH JOIN | | 1 | 889K| 1000K|00:00:00.21 | 5857 |
| 5 | TABLE ACCESS FULL | M_CUS | 1 | 2000 | 2000 |00:00:00.01 | 30 |
| 6 | TABLE ACCESS FULL | T_ORD_BIG_C10 | 1 | 889K| 1000K|00:00:00.09 | 5827 |
Note
-----
- dynamic statistics used: dynamic sampling (level=2)해시 조인, 5,869. E-Rows는 889,390이었다. 정확하지는 않지만 100만 근처였다. 맨 아래에는 dynamic sampling 한 줄이 붙어 있었다.
"통계가 없으면 오라클은 파싱할 때 표본을 떠서 세어 봐. 그게 동적 샘플링이야." 선배가 말했다. "그런데 통계가 있으면, 그게 0이든 뭐든 믿어. 의심하지 않아."
"그럼… 틀린 통계가 없는 통계보다 나쁘네요."
"오늘 아침 운영이 그 증거야."
한 번 더 버텨 봤다. "오과장님 배치를 APPEND로 바꾸면요? 23ai는 적재하면서 통계를 모은다고 들었어요."
"그것도 재 봐. 아까 쓴 힌트에서 NO_GATHER_OPTIMIZER_STATISTICS만 빼고."
COLUMN_N NUM_DISTINCT HISTOGRAM NOTES
CUS_ID 2000 NONE STATS_ON_LOAD
ORD_DT 1095 NONE STATS_ON_LOAD
...
TABLE_NAME NUM_ROWS BLOCKS
T_ORD_BIG_C10 1000000 5923STATS_ON_LOAD였다. 적재가 끝나는 순간 100만 행이 통계에 찍혀 있었고, 인덱스 통계도 새로 찍혔다. Q1 82, Q2 5,864. 사고는 나지 않았다.
"온라인 통계 수집이야. 빈 테이블에 direct path로 실을 때만 돌아. 일반 INSERT에는 없어. 아까 쓴 힌트가 바로 이걸 끄는 거였고." 선배가 덧붙였다. "그러니 '배치를 APPEND로 바꾼다'는 좋은 안이야. 다만 그건 적재 방식을 바꾸는 결정이고, 적재 방식이 통계를 바꾼다는 걸 문서에 적어 둬야 해. 안 그러면 내년에 누가 '왜 APPEND야?' 하고 일반 INSERT로 되돌려. 강도현 씨 주석처럼."
마지막으로 하나가 궁금했다. "통계가 얼마나 틀려야 계획이 바뀌어요? 0이니까 이렇게 된 거지, 조금 틀린 거면요?"
"좋은 질문이야. 재 봐."
어제 상태를 1만 행으로 바꿔 통계를 모으고, 100만 행을 다시 실었다. 통계는 실제의 100분의 1이었다. 그런데 Q1 82, Q2는 해시 조인 5,872. 아무 일도 일어나지 않았다.
통계의 행수만 손으로 바꿔 가며 Q2의 계획을 봤다(SET_TABLE_STATS. 다 보고 나서 다시 모았다).
NUM_ROWS 0 -> NESTED LOOPS Cost 4
NUM_ROWS 1,000 -> NESTED LOOPS Cost 13
NUM_ROWS 2,000 -> NESTED LOOPS Cost 15
NUM_ROWS 2,500 -> HASH JOIN Cost 16
NUM_ROWS 10,000 -> HASH JOIN Cost 28"2,000과 2,500 사이." 선배가 말했다. "M_CUS가 2,000행이야. 주문이 고객보다 적어 보이면 계단을 오르고, 많아 보이면 카드함을 복사해. 통계가 100배 틀려도 그 문턱을 안 넘으면 계획은 안 바뀌어. 오늘은 0이라서 넘은 거야."
⛳ 오늘은 여기까지(중단 지점 1) 여기까지가 오늘의 절반이다. "코드가 같은데 왜 계획이 바뀌었나"의 답(옵티마이저는 통계를 본다)을 복사본에서 재현했다. 이어서 읽으면 응급 조치를 하고, 오후에는 01화의 16배를 손으로 계산해서 고친다. 그리고 그 처방이 어디서 독이 되는지 본다.
오전 11시 30분 — 지금 운영에서 할 수 있는 것
박팀장에게서 두 번째 메시지가 와 있었다. "원인 나왔습니까? 정산팀이 오전 마감 중입니다."
"운영에서 지금 할 수 있는 건 두 가지야." 선배가 말했다. "다시 모으거나, 되돌리거나."
"되돌려요?"
"오라클은 통계를 바꿀 때마다 이전 통계를 이력으로 남겨. 31일 동안." 선배가 복사본의 통계 이력을 띄웠다.
TABLE_NAME STATS_UPDATE_TIME
T_ORD_BIG_C10 ... 10.33.30.403036 PM ← TRUNCATE 전에 모은 것 (100만)
T_ORD_BIG_C10 ... 10.33.36.524315 PM ← 빈 테이블을 모은 것 (0)"데이터는 어제와 똑같은 100만 건이야. 그럼 어제의 통계가 맞는 통계야."
RESTORE_TABLE_STATS로 복사본의 통계를 TRUNCATE 전 시각으로 돌렸다. 테이블 통계는 1,000,000행, 5,915블록으로 돌아왔고 인덱스 통계도 함께 돌아왔다. Q1 82, Q2 5,868. 해시 조인이었다.
다시 모으는 쪽도 재 봤다. 복사본에서는 1초 남짓이었다. "운영은 더 크고, 지금은 업무 시간이야. 오과장님이 말한 '오래 걸린다'는 운영 얘기고." 선배는 운영 DBA에게 전화해 되돌리기를 요청했다. 10분 뒤 박팀장 메시지가 왔다. "조민우 씨 화면이 돌아왔답니다. 0.1초대."
나는 안도했다. 하지만 선배는 안도하지 않았다.
"오늘 밤 재적재가 또 돌면?"
"…또 0이 되겠네요."
"그러니까 이건 아직 고친 게 아니야. 응급 처치야." 선배가 말했다. "고치는 건 저녁에 오과장님하고 같이 해. 그 전에 밥부터 먹자. 오늘 보여 줄 게 있어."
오후 12시 10분 — 식수표
구내식당은 월요일이라 붐볐다. 그런데 이상했다. 한식 줄은 벌써 반찬이 떨어져 영양사가 뛰어다니고 있었는데, 샐러드 바에는 샐러드가 산더미로 남아 있었다.
선배가 배식대 옆 벽을 턱으로 가리켰다. 화이트보드가 한 장 걸려 있었다.
오늘의 식수 (예상) 300 명
메뉴 한식 · 양식 · 샐러드 (3종)영양사가 지나가며 한숨을 쉬었다. "식수표대로 했는데요. 300명에 메뉴가 셋이니까 100인분씩이요. 그런데 샐러드는 맨날 몇 그릇 안 나가요. 메뉴별 신청은 안 받거든요. 저희한테 오는 건 총인원이랑 메뉴 수뿐이에요."
웃음이 나오다 멈췄다. "…01화의 333K네요."
"그래." 선배가 식판을 들었다. "옵티마이저도 영양사야. 사람을 보고 밥을 짓는 게 아니라 식수표를 보고 지어. 식수표에 '메뉴 3종'만 있으면 샐러드를 3분의 1만큼 준비해. 실제로는 2%만 먹는데."
"그럼 메뉴별로 지난주에 몇 그릇 나갔는지 적어 두면…"
"그게 히스토그램이야. 이따 해 볼 거야." 선배가 자리에 앉았다. "그리고 오늘 아침 운영은 이거였어. 월요일 식수표를 일요일 밤 빈 사무실에서 셌어. 0명. 그러니 영양사는 밥을 한 솥만 짓고 한 명씩 주문을 받기로 했지. 100만 명이 왔는데."
샐러드 바를 다시 봤다. 저 산더미가 어떤 숫자로 계산된 것인지 이제 보였다.
| 구내식당 | 오라클 |
|---|---|
| 식수표 (전날 집계한 인원 · 메뉴 수) | 통계: NUM_ROWS · NUM_DISTINCT |
| 영양사의 조리 · 배식 계획 | 실행계획 |
| 실제로 온 사람 | 데이터: A-Rows |
| "메뉴 3종이니 100인분씩" | 히스토그램 없는 등치 조건: 1 / NDV |
| 메뉴별로 지난주에 나간 그릇 수 | FREQUENCY 히스토그램 |
| 일요일 밤 빈 사무실에서 센 월요일 식수표 | 적재 도중 빈 테이블에서 모은 통계(NUM_ROWS 0) |
| 한 명씩 주문받기 / 뷔페 라인 | NESTED LOOPS / HASH JOIN |
오후 1시 20분 — E-Rows는 산수다
돌아오자 선배가 화이트보드를 지웠다.
"오늘 오후에는 E-Rows를 손으로 계산할 거야. 지난 아홉 주 동안 계획에서 본 숫자들을. 손계산이 E-Rows와 맞으면 옵티마이저는 통계를 '적힌 그대로' 쓴 거야. 그러면 틀린 건 통계나 가정이지, 옵티마이저가 아니야."
재료부터 뽑았다. T_ORD_BIG의 통계는 행수 1,000,000, ORD_DT의 종류 1,095, 최솟값 2022-01-01, 최댓값 2024-12-30으로 폭이 1,094일이었다.
"하루."
"100만 나누기 1,095… 913.2." 계획을 띄웠다. E-Rows 913, A-Rows 913.
"6월 한 달. 02화에서 28,336이라고 나왔던 거."
화이트보드에 썼다. 30일 나누기 1,094일은 0.027422. 100만을 곱하면 27,422.
"안 맞는데요. 28,336인데."
">=를 봐. 끝이 닫혀 있지?" 선배가 말했다. "닫힌 끝은 그 값 하루치를 한 번 더 쳐줘. 1/NDV."
0.027422 + 0.000913 = 0.028335. 28,335.5. 화면의 28,336과 맞았다. 02화에서는 이 숫자를 "거의 정확하다"고만 적었다. 그게 산수였다는 걸 그때는 몰랐다.
그다음은 내가 먼저 했다. 02화 rev00의 TO_CHAR(ORD_DT,...) BETWEEN은 2,500, 03화의 TO_CHAR(ORD_DT,'YYYYMM') = :p_ym은 10,000이었다.
"컬럼을 가공하면 통계가 없으니까… 정해진 비율로 찍는다고 하셨죠."
STATEM E_ROWS RULE
D_BTW 2500 1,000,000 x 5% x 5%
D_EQ 10000 1,000,000 x 1%
D_GE 50000 1,000,000 x 5%"등치는 1%, 한쪽 범위는 5%, 양쪽 범위는 5% 곱하기 5%." 02화의 2,500과 03화의 10,000은 같은 규칙에서 나온 숫자였다.
"조인."
06화의 Q2는 T_ORD_BIG 100만 × M_CUS 2,000 × 1/(둘 중 큰 NDV 2,000) = 1,000,000. 08화의 Q3는 T_ORD_DET 200,000 × T_ITM_EVL 5,000 × 1/100 = 10,000,000. 돌려 보니 E-Rows 10M, A-Rows 10M이었다. 08화의 50배는 계획의 E-Rows 칸에 처음부터 적혀 있었다.
"이제 09화가 넘긴 숙제." 선배가 말했다. "포스트잇 계획에서 E-Rows가 999K인데 A-Rows가 11이었지. 오추정이야?"
"…네, 9만 배요."
"ROWNUM을 떼고 같은 조건의 카디널리티를 봐."
EXPLAIN PLAN으로 보니 999,990이었다. 손으로도 계산됐다. 날짜 조건이 거의 1, ORD_SEQ 조건이 0.98878, 둘의 OR가 0.99998976이다. 100만을 곱하면 999,990.
"999K는 멈추기 전의 숫자야. 옵티마이저도 11건에서 멈출 걸 알아. 그건 Cost(15)와 인덱스 줄의 E-Rows(12)에 반영돼. 테이블 줄의 E-Rows에는 반영하지 않아. 그 줄은 대조할 줄이 아니야." 선배가 말했다. "COUNT STOPKEY 아래는 예외야. 이건 오늘 SOP에 적어."
다음은 935페이지였다. 09화에서 나는 "Cost에 정렬 CPU가 들어 있기 때문"이라고 적었다. 10053 트레이스를 떴다.
ORDER BY sort
SORT ressource Sort statistics
Sort width: 598 Area size: 536576 Max Area size: 104857600
Blocks to Sort: 5020 Row size: 41 Total Rows: 1000000
Total IO sort cost: 7740.000000 Total CPU sort cost: 1063636731
Total Temp space used: 40166000정렬 비용 7,766 중 7,740이 IO였다. CPU는 26 남짓이었다. 옵티마이저는 100만 행 정렬이 TEMP로 40MB를 넘칠 거라고 셈하고 있었다. 실제 Top-N 정렬은 책상 206K 안에서 끝났는데 말이다.
"제가 09화에 틀리게 적었네요. CPU가 아니라 TEMP를 쓸 거라는 IO였어요."
"고쳐 적으면 돼. 그래서 리포트에 숫자를 쓰는 거야." 선배가 말했다. "인덱스 쪽은 인덱스 34블록에 N건마다 테이블 한 블록이야. CF가 100만이니까. 정렬 쪽은 9,378로 고정이고. 인덱스 쪽이 9,378을 넘는 N이 9,342 근처야. 934페이지는 9,340번째 행까지, 935페이지는 9,350번째 행까지고. 935페이지. 산수로 딱 떨어지지?"
마지막은 최댓값 밖이었다. 통계의 최댓값은 2024-12-30이다. 그 뒤의 하루를 물으면 어떻게 될까?
ORD_DT = 2024-12-30 (max) E-Rows 913 by hand 913.2
ORD_DT = 2024-12-31 (+1) E-Rows 912 by hand 912.4
ORD_DT = 2025-12-30 (+365) E-Rows 609 by hand 608.5
ORD_DT = 2026-12-30 (+730) E-Rows 304 by hand 303.9
ORD_DT = 2027-12-29 (+1094) E-Rows 1 by hand 1.0
ORD_DT in 2025-01 (whole month) E-Rows 913"최댓값에서 멀어진 만큼 줄어. 범위 폭(1,094일)만큼 멀어지면 1이 돼." 선배가 말했다. "그리고 맨 아랫줄. 통째로 밖에 있는 한 달은 하루치로 봐."
"하루 지난 건 거의 안 줄었네요. 912."
"그래서 매일 증분 적재하는 테이블은 하루 이틀로는 티가 안 나. 문제는 얼마나 오래 안 모으느냐야." 선배가 복사본에 한 달치(27,390행)를 더 싣고 STALE_STATS를 보여 줬다. NO였다. "자동 수집은 10%가 바뀌어야 움직여. 100만 행이면 10만 행이야. 하루 913건이면 110일이 걸려. 그동안 '이번 달'을 물으면 E-Rows는 913, 실제는 27,390이야."
💡 손계산 전체와 공식은
2_파고들기.md §2에, 10053 읽는 법은§9에 있다.
오후 2시 30분 — 16배의 정체
"이제 01화의 빚을 갚자."
01화 첫날 선배가 보여 준 그 쿼리였다. ORD_ST = 'CANCEL', E-Rows 333K, A-Rows 20,000. 그때 선배는 "고칠 수 있어. 근데 오늘은 아니야"라고 했다.
손계산은 이제 쉬웠다. 100만 × 1/3 = 333,333. 식수표의 샐러드 100인분이었다.
"원본에서 히스토그램 실험하지 마. 이것도 복사본에서." 선배가 말했다. "통계는 원본에 한 번만 건다. 정책으로."
복사본 T_ORD_ST_C10에서 ORD_ST에 버킷 254개를 줬다. 값이 3종이니 한 값이 한 칸씩 차지하는 FREQUENCY 히스토그램이다.
ENDPOINT_NUMBER VAL5
20000 CANCE
920000 COMPL
1000000 PENDI
H1 CANCEL E-Rows 20000 A-Rows 20000
H1 PENDING E-Rows 80000 A-Rows 80000
H1 COMPLETE E-Rows 900000 A-Rows 900000
H1 CLOSED E-Rows 10000 A-Rows 0"ENDPOINT_NUMBER는 누적이야. 20,000까지 CANCEL, 920,000까지 COMPLETE, 끝까지 PENDING." 메뉴별 그릇 수였다. 없는 값 CLOSED는 가장 적은 값의 절반인 10,000으로 잡혔다.
333,333 → 20,000. 01화부터 아홉 주를 끌고 온 16배가 한 줄로 사라졌다.
"그럼 전부 히스토그램을 켜면 되겠네요. SIZE AUTO로 두면 오라클이 알아서…"
선배가 웃지도 않고 말했다. "버킷을 두 개만 줘 봐."
ORD_ST 3 .333332993 HYBRID 2
ENDPOINT_NUMBER ENDPOINT_REPEAT_COUNT VAL5
1 1 CANCE
980001 80000 PENDI
H2 CANCEL E-Rows 1 A-Rows 20000
H2 PENDING E-Rows 81633 A-Rows 80000
H2 COMPLETE E-Rows 333333 A-Rows 900000CANCEL 1. 2만 건이 1건이 됐다.
"값이 버킷보다 많으면 HYBRID가 돼. 첫 칸에는 최솟값을 딱 한 건만 담아. 그게 CANCEL이었어." 같은 버킷 두 개를 표본 100%로 모으니 구형 HEIGHT BALANCED가 나왔고, 세 값 모두 333,333이었다. 히스토그램이 없는 것과 같았다.
본사 시스템에서도 하나 봤다. 판매단가 11종에 버킷 5개를 주니 TOP-FREQUENCY가 됐다. 상위 세 단가는 정확했다. 그런데 오라클이 최솟값(2,000원)과 최댓값(12,000원)을 억지로 끼워 넣으면서 둘 다 E-Rows가 1이 됐다. 실제는 12,556과 18,789였다.
"히스토그램은 '있다, 없다'의 문제가 아니야. 무엇을 기억하느냐의 문제야." 선배가 말했다. "그리고 SIZE AUTO는 버킷 수를 오라클이 정하고, 어느 열에 만들지는 'WHERE에 쓰인 적이 있나'와 '치우쳤나'로 정해. 00_자료준비에서 밤사이 히스토그램이 저절로 생겨서 01화의 333K가 사라졌던 거, 그게 바로 이거야. 네가 모르는 사이에 계획이 바뀌는 길이 하나 더 생기는 거지."
💡 히스토그램 4종이 각각 식을 어떻게 바꾸는지는
2_파고들기.md §4에서 다룬다.
오후 3시 20분 — 히스토그램 때문에 더 틀리는 곳
"FREQUENCY는 완벽하네요. 이건 원본에도 거는 거죠?"
"걸 거야. 그 전에 하나만 더." 선배가 말했다. "CUS_000001 고객의 취소 건."
복사본에서 히스토그램을 지우고(SIZE 1) 먼저 돌렸다. 손계산은 100만 × 1/2,000 × 1/3 = 167. E-Rows 167, A-Rows 500으로 3배 과소 추정이었다.
그리고 FREQUENCY를 다시 걸었다. 이제 CANCEL은 정확히 2%다.
|* 2 | TABLE ACCESS FULL| T_ORD_ST_C10 | 1 | 10 | 500 |10. 손계산으로도 100만 × 1/2,000 × 0.02 = 10이다.
"…더 틀렸어요. 히스토그램을 넣었더니 3배에서 50배가 됐어요."
"데이터를 봐."
CUS_ST_PAIRS CUSTOMERS
2000 2000
ORD_ST CUSTOMERS ORDERS
CANCEL 40 20000
COMPLETE 1800 900000
PENDING 160 80000고객마다 상태가 하나뿐이었다. CANCEL 고객은 40명이고, 한 명당 주문 500건이 전부 CANCEL이었다. CUS_000001은 그 40명 중 한 명이었다.
"옵티마이저는 조건 두 개를 곱해. '고객이 이 사람일 확률' 곱하기 '취소일 확률'. 둘이 서로 상관없다고 가정하고." 선배가 말했다. "식수표로 말하면, '3층 사람 30명'과 '샐러드 2%'를 따로 알고 있어서 3층 샐러드는 0.6그릇이라고 계산하는 거야. 그런데 3층이 전부 다이어트 중인 팀이면?"
"…30그릇이요."
"히스토그램은 한 열의 분포만 알아. 열과 열 사이는 몰라. 그래서 치우친 열에 히스토그램을 넣으면, 그 열이 상관된 다른 열과 같이 쓰일 때 곱셈이 더 정밀하게 틀려."
같은 쿼리를 한 번 더 돌렸다. 이번엔 다른 숫자가 나왔다.
SQL_ID 7mjsvwmqdk0ut, child number 1
|* 2 | TABLE ACCESS FULL| T_ORD_ST_C10 | 1 | 500 | 500 |00:00:00.02 | 5821 |
Note
-----
- statistics feedback used for this statement"500. 맞았어요. 오라클이 배운 거예요?"
"첫 실행에서 E-Rows와 A-Rows가 크게 어긋나면 그 커서에 '다시 최적화할 것'이라는 표시(IS_REOPTIMIZABLE = Y)를 해. 그리고 두 번째 실행 때 실제 건수로 계획을 다시 세워. Statistics Feedback이야." 선배가 말했다. "그런데 그건 그 SQL에만 해당돼. 주석 한 글자만 바꿔서 돌려 봐."
글자 하나 다른 새 SQL에서는 E-Rows가 10이었다. 처음으로 돌아갔다.
"배운 게 아니라 그 커서에 메모를 붙인 거야. 커서가 공유 풀에서 밀려나면 메모도 같이 사라져." 선배가 말했다. "오라클에는 이런 걸 테이블 단위로 적어 두는 장치도 있어. SQL Plan Directive. 그런데 이 버전의 기본 설정에서는 적어 두기만 하고 계획에는 안 써. 그건 실습/92에서 직접 봐."
"그럼 영구적으로 고치려면요?"
"열과 열 사이의 관계를 통계에 알려 줘야지. 컬럼 그룹."
(CUS_ID, ORD_ST)를 하나의 확장 통계로 만들고 다시 모았다. 조합의 종류는 고객 수와 같은 2,000이었다. 100만 / 2,000 = 500.
그런데 결과는 10이었다.
"…안 쓰는데요?"
"ORD_ST에 히스토그램이 있으니까." 선배가 말했다. "열에는 히스토그램이 있는데 그룹에는 없으면 오라클은 그룹을 무시해. 그룹에도 히스토그램을 줘."
그룹에 버킷 2,048개를 줬다. 조합 2,000종이 다 들어가는 FREQUENCY다.
L3 CUS_000001 CANCEL E-Rows 500 A-Rows 500
L3 CUS_000002 CANCEL E-Rows 250 A-Rows 0
L3 CUS_000002 COMPLETE E-Rows 500 A-Rows 500500이 나왔다. 없는 조합은 250이었다. 이것도 '가장 적은 값의 절반'이었다.
마지막으로 선배가 02화의 한 줄을 꺼냈다. "E-Rows가 정확해도 여전히 틀리는 경우 → 10화."
6월 한 달은 28,336으로 정확했다. 여기에 ORD_ST = 'CANCEL'을 더했다. 히스토그램 없이는 9,445, 실제는 549로 17배 차이였다. 히스토그램을 넣으면 567이었다.
"02화 때 너는 6월만 물었어. 그건 고르게 퍼진 열이라 정확했고." 선배가 말했다. "정확한 E-Rows는 그 조건에서만 정확해. 조건이 하나 더 붙으면 곱셈이 시작되고, 곱하는 열이 치우쳤거나 서로 엮여 있으면 거기서 틀려."
나는 화이트보드에 오늘 본 것을 한 줄로 적었다.
통계는 열마다 따로 있다. 옵티마이저는 열과 열 사이를 모르고, 곱할 뿐이다.
⛳ 오늘은 여기까지(중단 지점 2) 01화의 16배가 FREQUENCY 히스토그램으로 333,333 → 20,000이 됐다. 그리고 같은 히스토그램이 상관 AND에서는 167 → 10(실제 500)으로 더 틀린다는 것도 봤다. 오늘 몫은 여기까지만 해도 다 한 것이다. 이어지는 저녁 장면에서는 이 둘을 정책으로 정리해 원본에 건다.
오후 4시 30분 — 정책서 1호와 2호
"원본에 거는 건 하나야." 선배가 말했다. "그리고 거는 이유와 안 거는 이유를 같이 적어."
새 문서를 만들었다. 「통계 수집 정책서」. 1호는 T_ORD_BIG이다.
METHOD_OPT 선호도 = 'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 ORD_ST'"전부 SIZE 1, ORD_ST 하나만 254. SIZE AUTO에 맡기지 않는다." 선배가 말했다. "그리고 GATHER에 method_opt를 그때그때 주지 말고 선호도로 걸어. 그래야 밤의 자동 수집도 같은 규칙으로 모아."
연습 DB의 원본에 걸었다. 선호도를 걸고, method_opt 없이 모았다(몇 초 걸렸다). ORD_ST만 FREQUENCY가 됐다. 01화의 그 쿼리를 돌렸다.
|* 2 | TABLE ACCESS FULL| T_ORD_BIG | 1 | 20000 | 20000 |00:00:00.01 | 5826 |E-Rows 20000, A-Rows 20000. 계획은 그대로 FULL이었고, Buffers도 5,826 그대로였다.
"계획이 안 바뀌었는데 의미가 있어요?"
"이 쿼리만 보면 없어. 그런데 이 20,000이 조인 위로 올라가고, 정렬 책상 크기가 되고, 다음 조건과 곱해져. 틀린 숫자는 위로 갈수록 커져." 선배가 말했다. "그리고 반대쪽도 봐야 해. 영향 분석. 이걸 걸어서 바뀐 게 없는지 6조각을 다 돌려."
정책을 걸기 전과 후로 6조각을 돌려 나란히 놓았다.
| 조각 | 계획 해시 (전 → 후) | Buffers (전 → 후) |
|---|---|---|
| 01화 CANCEL | 914281838 → 914281838 | 5,826 → 5,826 (E-Rows 333K → 20,000) |
| Q1 rev02 | 636720714 → 636720714 | 91 → 91 |
| Q2 rev01 | 2292855265 → 2292855265 | 5,868 → 5,868 |
| Q3 rev01 | 2231864090 → 2231864090 | 840 → 839 |
| Q4 rev02 | 3610158121 → 3610158121 | 15 → 15 |
6조각에는 ORD_ST 조건이 하나도 없었다. 바뀐 것도 없었다.
"리스크 칸에는 오늘 오후의 그 10을 적어." 선배가 말했다. "그리고 하나 더. 이 히스토그램은 'CANCEL'이라고 글자로 박혀 있을 때 얘기야. 화면이 상태값을 바인드 변수로 넘기면 어떻게 될까?"
"…값마다 맞는 계획이 다른데, SQL은 하나네요."
"그건 다음 주야." 선배가 말했다. "리스크 칸에 '11화'라고만 적어 둬."
그리고 2호다. 배치 항목이라 오과장을 다시 불렀다.
수집 시점 : 적재 COMMIT 직후, 같은 배치 안에서 (1호의 선호도대로)
대안 : INSERT /*+ APPEND */ 로 바꾸면 적재하며 모은다 (STATS_ON_LOAD) — 그때는 NOTES 확인 단계로
응급 : RESTORE_TABLE_STATS — TRUNCATE 전 시각으로 (통계 이력 31일)
기각 : 통계 LOCK — 자동 수집은 막지만, 재적재 크기가 바뀌는 날 낡은 통계가 굳는다오과장이 한참 읽었다. "통계 수집을 다시 넣으면 04시가 빠듯한데요."
"빠듯한 이유는 따로 재야 해요. 인덱스 세 개요." 선배가 말했다. "그건 몇 주 뒤에 오과장님하고 같이 볼 거예요. 그때까지는 이 단계 빼지 마세요. 강도현 씨 주석 옆에 오늘 날짜로 한 줄 더 적어 주시고요."
오과장이 배치 스크립트를 열어 그 주석 아래에 적었다.
-- 5월 6일 (오과장) 재확인. 빼면 월요일 오전 Q1·Q2 가 NL/인덱스로 바뀐다 (통계 수집 정책서 2호)오후 5시 20분 — 본사의 문자 날짜
하나가 더 남아 있었다. 03화와 04화가 넘긴 숙제인 문자로 박힌 날짜의 E-Rows 문제였다.
본사 시스템(ora-dist)의 CF10.SLE_DT는 VARCHAR2(8)이었다. 2023년 6월을 물으면 E-Rows는 5,177인데 실제는 20,953이다. 03화에서 선배는 "문자를 숫자로 인코딩해서 추정한다"고만 했었다.
"이제 손으로 계산할 수 있어?"
10053을 떴다. 조건마다 선택도가 찍혀 있었다. >= '20230601'은 0.057974, < '20230701'은 0.952381. 그 숫자를 한참 봤다. 0.952381 = 20/21.
"…이거, 날짜를 숫자로 본 거예요."
sel(>= '20230601') = (20231231 - 20230601) / (20231231 - 20220101) + 1/730 = 0.057974
sel(< '20230701') = (20230701 - 20220101) / (20231231 - 20220101) = 0.952381
둘 다 → 500,000 × 0.010355 = 5,177수직선 위의 20220101부터 20231231까지다. 그 위에서 한 달은 100칸이고, 12월 31일에서 1월 1일로 넘어가는 하루는 8,870칸이었다. 04화의 버거원에서 하루를 넘기자 E-Rows가 102K에서 899K로 뛰었던 것도 같은 수직선 때문이었다.
"그래서 고치는 길이 셋이야. 재 봐."
복사본에서 재 봤다. SLE_DT에 히스토그램(HYBRID)을 주면 수직선 대신 실제 분포를 쓴다. 6월 E-Rows가 2만 안팎이 됐다. 다만 HYBRID는 표본(5천여 행)으로 만들기 때문에 모을 때마다 조금씩 달랐다. 다섯 번 모았더니 19,630에서 21,780 사이였다. TO_DATE(SLE_DT,'YYYYMMDD')에 표현식 통계를 달면 03화의 1,250(0.25% 고정값)이 21,261이 됐다. 03화의 가상 컬럼과 같은 숫자였다.
그리고 이 시스템에는 진짜 상관이 있었다. 매장과 등록 직원이다. 매장마다 직원이 한 명이라, 독립 가정으로는 20,000인데 실제는 100,167이었다. 컬럼 그룹을 주니 100,000이 나왔다. 판매일자와 요일도 그랬다. 6월 3일 토요일은 98에서 685(실제 648)가 됐다. 그런데 같은 날을 '월요일'로 물어도 그룹은 685라고 했다. 실제는 0이었다.
"원본에 걸어요?"
"아니. 본사 원본 통계 위에는 03화 이후의 숫자가 다 올라가 있어. 그리고 그 시스템 주인은 우리가 아니야." 선배가 말했다. "재 본 것과 걸지 않은 이유를 정책서 후보로 적어서 본사 유통지원팀에 보내. 09화처럼."
복사본은 지웠다. 원본 CF10의 통계는 아침에 적어 둔 것과 같았다.
오후 6시 20분 — 박팀장에게
답장을 썼다.
나 18:12
원인은 일요일 밤 재적재 중에 자동 통계 수집이 비어 있는 T_ORD_BIG 을 수집한 것입니다.
통계가 '0건' 이라고 믿은 오라클이 Q2 를 고객 한 명씩 찾아가는 방식으로 바꿨습니다
(연습 DB 재현: 한 번 조회에 읽는 양 5,868 → 1,026,852 블록, 175배).
코드는 바뀌지 않았고, 오전 11시 40분에 통계를 일요일 이전 것으로 되돌려 정상화했습니다.
재발 방지로 배치 마지막의 통계 수집 단계를 복원했고(오과장 합의), 통계 수집 정책서 1·2호를 만들었습니다.
상세는 T1 8호로 드립니다.선배가 어깨 너머로 읽었다. "'175배'를 박팀장이 알아들을까?"
"…'한 번 조회에 읽는 양이 175배가 됐다'로 쓸게요."
"좋아. 그리고 마지막 질문." 선배가 의자를 돌려 앉았다. "오늘 네가 고친 건 SQL이야?"
"아니요. 한 글자도 안 고쳤어요. 리비전도 없어요."
"그럼 오늘 리포트의 대상은?"
잠깐 생각했다. "…옵티마이저가 보는 숫자요. 그걸 누가, 언제, 어떻게 만드는지."
"그게 정책서야." 선배가 일어섰다. "그리고 기억해 둬. 06화에서 힌트를 지운 결정은 옳았어. 지금도 옳고. 다만 그 결정은 '통계가 맞다'는 전제 위에 서 있었는데, 그 전제를 지키는 사람이 없었어. 판단을 옵티마이저에게 돌려줬으면, 옵티마이저가 보는 것도 관리해야 해."
퇴근하며 구내식당 앞을 지나는데 화이트보드가 바뀌어 있었다.
내일의 식수 (예상) 300 명
한식 · 양식 · 샐러드 (3종)
※ 샐러드는 지난주 하루 평균 6그릇영양사도 히스토그램을 하나 만든 셈이었다.
오늘 남은 것
| 무엇 | 값 |
|---|---|
| 사고 재현 (복사본) | 빈 테이블 통계 + 100만 행 → Q1 91 → 27,274 · Q2 5,863 → 1,026,852 (E-Rows 1) |
| 응급 조치 | RESTORE_TABLE_STATS → Q1 82 · Q2 5,868 / 재수집 1초 남짓 (1.2~1.4초) |
| 16배의 정체 | ORD_ST 1/NDV = 333,333 → FREQUENCY 20,000 (원본 적용) |
| 히스토그램의 독 | 상관 AND (CUS_ID + ORD_ST) 167 → 10 (실제 500) · 컬럼 그룹은 그룹 히스토그램이 함께 있어야 500 |
| 「통계 수집 정책서」 1호 · 2호 ★ | 새 표준 문서: 40_보고서와_표준/템플릿/통계수집_정책서.md |
| T1 리포트 8호 | 이 교재에서 처음으로 리비전이 없는 리포트. 대상은 SQL이 아니라 통계 |
| 리비전 | 없음. Q1 rev03 · Q2 rev01 · Q3 rev01 · Q4 rev02 그대로 |
오늘 배운 한 문장
옵티마이저는 데이터가 아니라 통계를 보고 계획을 세운다. 그리고 통계는 열마다 따로 있다.
E-Rows는 산수다. 손으로 계산한 값과 맞는다면, 틀린 것은 옵티마이저가 아니라 통계나 가정이다. 판단을 옵티마이저에게 돌려줬다면, 옵티마이저가 보는 숫자를 누가, 언제, 어떻게 만드는지도 문서로 남겨야 한다.
다음으로 넘기는 것
ORD_ST를 바인드 변수로 받으면: 값마다 맞는 계획이 다른데 SQL은 하나다 → 11화(히스토그램 위의 바인드 피킹)- 인덱스 3개의 적재 비용: 오과장의 04시와 통계 수집 단계가 부딪치는 이유 → 14화
- 본사
CF10의 정책서 후보(문자 날짜 · 매장/직원 상관): 유통지원팀 회신 대기 - 정책서의 재검토 조건(
ORD_ST새 값 · 적재 방식 변경) → 조건이 걸리면 다시 잰다 · 18화 원장 재측정
다음 →
3_실습과_기록.md에서 일요일 밤을 복사본에서 재현하고,E-Rows를 손으로 계산하고, 히스토그램 4종을 만들어 보고, 정책서 1호를 원본에 건다. 개념이 막히면2_파고들기.md를 펼친다.