테마
04화 · 인덱스를 태웠더니 더 느려졌다
3월 25일 월요일. 2주 전에는 인덱스로 64배를 얻었다. 오늘 그 믿음이 한 번 더 깨진다. 이번에는 내 잘못도 아니다.
오전 9시 05분
박팀장이 회의실로 불렀다. 노트북에 띄워 놓은 건 계약서였다.
"우리 회사가 버거원을 인수했습니다."
가맹점 200개짜리 햄버거 체인이다. 본사 건물 1층에도 매장이 하나 있다.
"물류는 이미 우리가 넣고 있었고, 이번에 전산까지 통째로 넘겨받게 됐어요. 그쪽 POS 백업본이 어제 들어왔습니다." 박팀장이 나를 봤다. "인수 실사 때 그쪽 본사 직원들이 하나같이 한 얘기가 있어요. 월마감 집계 화면이 느리다고요."
2주 전 같았으면 긴장했을 텐데, 솔직히 조금 자신이 있었다. "얼마나 느린가요?"
"그건 모릅니다. '느리다'고만 하더군요."
선배가 옆에서 한마디 했다. "그럼 그것부터 재면 되겠네."
오전 9시 30분 — 문서고
자리로 가려는데 선배는 복도 반대쪽으로 향했다. 문서고였다.
"버거원이랑 일하는 게 처음이 아니야. 2년 전에 우리가 그쪽 POS 성능 점검을 해 줬거든."
선배가 캐비닛에서 얇은 바인더를 꺼냈다. 표지에는 「버거원 POS 성능 점검 결과」라고 적혀 있었고, 작성자 칸에는 익숙한 이름이 있었다. 강도현.
"퇴사하기 직전이네요."
"마지막 외부 건이었을 거야."
세 장짜리였다. 1페이지 '조치' 항목에 이렇게 적혀 있었다.
3. 조치
월마감 집계 쿼리를 ORD_DT 선두 인덱스 RANGE SCAN 으로 변경 완료다음 줄을 찾았다. 없었다. 뒷장을 넘겼다. 거기에도 없었다.
"선배님, 숫자가 없는데요. 뭘 했는지는 있는데 그래서 얼마나 좋아졌는지가 없어요. 몇 블록이었는지도, 그때 데이터가 몇 건이었는지도요."
선배가 바인더를 받아 한 번 훑어보고 덮었다. "그게 오늘의 적이야."
"…강도현 씨요?"
"아니. 근거를 안 남긴 결정. 첫날 말했지. 미워해야 할 건 사람이 아니라 그거라고." 선배가 표지를 짚었다. "이 사람 결정이 틀렸다고는 아직 말 못 해. 맞았을 수도 있어. 근데 맞았는지 틀렸는지 지금 우리가 확인할 방법이 없잖아. 그게 손해야."
"그럼 어떻게 해요?"
"다시 재야지. 전부."
오전 10시 00분 — 재 본다
버거원 POS 백업본을 띄웠다. 주문 테이블이 두 개 있었다.
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
-------------- ---------- ---------- -----------
ORD 1000000 22422 154
ORD_RANDOM 1000000 22414 154"ORD_RANDOM은 이름이 왜 저래요?"
"이관할 때 붙인 임시 이름이 그대로 굳은 거야. 현장엔 저런 거 많아." 선배가 말했다. "ORD가 이관 전 보관본이고, ORD_RANDOM이 지금 돌아가는 운영본이야. 1년 전에 서버를 바꾸면서 다시 적재했대."
문제의 화면은 본사 월마감 화면인 「가맹점별 주문 건수」였다. 강도현이 손댔다는 그 쿼리를 찾아서 02화에서 배운 대로 쟀다.
sql
SELECT /*+ GATHER_PLAN_STATISTICS INDEX(o ORD_RANDOM_X01) */
o.SHOP_NO, COUNT(*) AS ord_cnt
FROM ORD_RANDOM o
WHERE o.ORD_DT BETWEEN '20120101' AND '20120131'
GROUP BY o.SHOP_NO;xplan
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 |00:00:00.05 | 41825 |
| 1 | HASH GROUP BY | | 200 |00:00:00.05 | 41825 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| ORD_RANDOM | 41666 |00:00:00.04 | 41825 |
|* 3 | INDEX RANGE SCAN | ORD_RANDOM_X01 | 41666 |00:00:00.01 | 160 |
-----------------------------------------------------------------------------------------------
3 - access("O"."ORD_DT">='20120101' AND "O"."ORD_DT"<='20120131')실행계획을 한 줄씩 짚어 봤다. INDEX RANGE SCAN이니 인덱스를 탄다. access니 찾아간다. 컬럼 가공도 없다. 02화에서 배운 체크리스트를 전부 통과했다.
그런데 41,825블록이었다. 4만 1천 건을 세는 데 4만 2천 블록을 읽었다.
"선배님, 교과서대로인데도 느린데요."
"그럼 비교군을 만들어야지. 전체 스캔은 얼마야?"
|* 2 | TABLE ACCESS FULL | ORD_RANDOM | 41666 |00:00:00.06 | 22234 |22,234. 두 숫자를 나란히 적었다. 인덱스를 쓰는 쪽이 1.88배 더 느렸다.
02화에서 한 번 본 장면이었다. 그때 T_ORD_BIG에서 억지로 인덱스를 태웠더니 5,827이 27,466이 됐다. 하지만 그때는 옵티마이저가 안 쓰겠다는 걸 내가 억지로 태운 거였다. 지금은 다르다. 이건 운영에서 2년째 돌고 있는 코드다.
"강도현 씨가 이렇게 만들어 놓은 거예요?"
"그렇게 적혀 있지. 근데 아직 결론 내리지 마. 옆에 ORD 있잖아. 똑같이 해 봐."
오전 10시 30분 — 쌍둥이
테이블 이름만 바꿔서 똑같이 돌렸다.
xplan
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| ORD | 41666 |00:00:00.02 | 1086 |
|* 3 | INDEX RANGE SCAN | ORD_X01 | 41666 |00:00:00.01 | 160 |1,086.
아래쪽 INDEX RANGE SCAN 줄은 양쪽 다 160으로 똑같았다. 읽어 들인 행수도 양쪽 다 41,666이었다. 그런데 위쪽 TABLE ACCESS 줄에서 갈렸다. 1,086 대 41,825, 38.5배였다.
"뭐가 다르죠? 같은 SQL이고 건수도 같은데요."
"그러니까 찾아봐. 다른 게 하나는 있을 거 아니야."
틀린 답 1 — 인덱스가 깨졌나
02화에서도 거기부터 봤다.
INDEX_NAME STATUS BLEVEL LEAF_BLOCKS NUM_ROWS DISTINCT_KEYS
------------------ ------ ------- ----------- ---------- -------------
ORD_RANDOM_X01 VALID 2 3774 1000000 986510
ORD_X01 VALID 2 3774 1000000 986510전부 같았다. 깊이도 2, 리프 블록도 3,774, 키 개수도 100만이었고, 서로 다른 값의 개수까지 986,510으로 똑같았다.
틀린 답 2 — 통계가 틀렸나
그럼 통계가 낡아서 옵티마이저가 헛다리를 짚었나 싶었다. 그런데 통계는 실행계획을 고를 때 영향을 줄 뿐, 이미 정해진 계획이 읽는 블록 수를 바꾸지는 못한다. 그 생각이 들자 나는 입을 다물었다.
"말해 봐. 왜 그만뒀어?"
"지금은 양쪽 다 같은 실행계획이고, 제가 힌트로 고정까지 했고, A-Rows도 같아요. 같은 행을 같은 순서로 읽는데 블록 수만 38배 차이 나는 거예요."
선배가 처음으로 웃었다. "제대로 좁혔네. 그럼 남은 건 하나야. 같은 행을 읽는데 블록이 더 필요하다면, 그 행들이 블록에 흩어져 있는 모양이 다른 거야."
오전 11시 00분 — 숫자 하나
선배가 키보드를 가져가서 컬럼을 하나 더 붙였다.
sql
SELECT i.index_name, i.clustering_factor AS cf, t.blocks, t.num_rows
FROM user_indexes i JOIN user_tables t ON t.table_name = i.table_name
WHERE i.index_name IN ('ORD_X01','ORD_RANDOM_X01');ORD | ORD_RANDOM | |
|---|---|---|
| 행수 | 1,000,000 | 1,000,000 |
| 테이블 블록 | 22,422 | 22,414 |
| 인덱스 리프 블록 | 3,774 | 3,774 |
| CLUSTERING_FACTOR | 22,223 | 999,958 |
"ORD는 CF가 테이블 블록 수랑 거의 같아. ORD_RANDOM은 행수랑 거의 같고."
02화 마지막에 봤던 숫자였다. 그때는 "최악"이라는 말만 듣고 넘어갔다.
"클러스터링 팩터가… 뭘 세는 거였죠?"
"인덱스를 순서대로 따라가면서 테이블 블록을 몇 번 '바꿔 타는지' 세는 거야." 선배가 종이에 적었다.
인덱스 순서대로 읽어 나가면서,
직전 행이 있던 테이블 블록 != 지금 행이 있는 테이블 블록 -> +1
같으면 -> 그대로"100만 행인데 CF가 100만이면?"
"매번 바뀐다는 거요. 한 번도 안 겹친다는 거고요."
"그래. 22,223이면?"
100만 행을 읽는 동안 블록을 2만 2천 번만 바꿔 탔다. 평균 45행을 읽는 동안 같은 블록에 머물렀다는 뜻이다.
"따라와."
오전 11시 30분 — 캐리어가 돌아왔다
선배가 향한 곳은 탕비실이었다. 첫날 왔던 그 자리였다.
캐비닛에 네 잔짜리 종이 캐리어가 쌓여 있었다. 첫날 선배가 블록을 설명하면서 썼던 물건이다. 캐리어 하나에 네 잔, 블록 하나에 169건. 그날 선배는 이렇게 말하고 이야기를 끝냈었다. "이 캐리어, 기억해 둬. 나중에 다시 꺼낼 거야. 캐리어 하나에 몇 잔이 들어 있느냐보다 더 고약한 문제가 있거든."
"오늘이 그날이에요?"
"오늘이 그날이야." 선배가 캐리어를 하나 집었다. "커피 4만 잔이 필요해. 캐리어 하나에 45잔씩 들어 있어. 몇 번 들고 와야 돼?"
"…900번쯤이요."
"그게 ORD야. 실측 1,086." 선배가 다른 캐리어를 집었다. "이번엔 캐리어마다 한 잔씩 들어 있어. 4만 잔이 필요해."
"4만 번이요."
"그게 ORD_RANDOM이야. 실측 41,825. 블록이 커서 손해를 본 게 아니라, 블록이 잘못 채워져 있어서 손해를 본 거야."
그리고 더 고약한 것
"블록 수만 봐서는 안 보이는 게 하나 더 있어. 들고 오는 방식이 달라."
자리로 돌아와 버퍼 캐시를 비우고 물리 I/O 요청 건수를 셌다.
ORD · FULL 강제 : IO요청 186 / 물리블록 22,229 / 멀티블록요청 175
ORD_RANDOM · 인덱스 강제 : IO요청 18,934 / 물리블록 19,107 / 멀티블록요청 0첫 줄을 두 번 봤다. 2만 2천 블록을 186번 만에 읽었다.
"전체 스캔은 한 번에 128개씩 들고 와. 멀티블록 읽기야. 수레를 끌고 가서 캐리어를 128개씩 싣고 오는 거지."
"인덱스는요?"
"인덱스를 거쳐 테이블에 갈 때는 ROWID 하나로 블록 하나를 콕 집어서 가져와. 싱글블록 읽기야. 한 번에 하나씩이지. 그래서 18,934번을 왔다 갔다 한 거야."
첫날 선배가 한 말이 그대로 떠올랐다. "왕복이 비싼 거지 커피가 비싼 게 아니야."
"블록 수로 봐도 인덱스가 1.88배 지는데, 왕복 횟수로 보면 100배 지는 거네요."
"그래. 그게 오늘 네가 건진 문장이야."
⛳ 오늘은 여기까지(중단 지점 1) 여기까지가 오늘의 절반이다. 같은 인덱스인데 왜 한쪽만 느린지, 그리고 그 답(CF)을 봤다. 이어서 읽으면 고치려다 두 번 실패하고, 손익분기점을 숫자로 찾는다.
오후 1시 30분 — "REBUILD 하면 되는 거 아니에요?"
점심을 먹으면서 나는 이미 답을 정해 두었다. 인덱스에 붙은 숫자가 나쁘면 인덱스를 새로 만들면 된다.
sql
ALTER INDEX ORD_RANDOM_X01 REBUILD;2초 만에 끝났다. 통계를 다시 수집하고 확인했다. 온라인 옵션으로도, COALESCE로도 해 봤다.
REBUILD 2.05초 -> CF 999958
REBUILD ONLINE 2.37초 -> CF 999958
COALESCE -> CF 999958쿼리도 다시 쟀다. 41,825. 한 블록도 줄지 않았다.
"안 고쳐지는데요."
"왜 안 고쳐질 것 같아?"
모르겠다고 했다. 선배가 일어서더니 아침에 갔던 문서고로 다시 향했다.
문서고 — 전표철과 색인 노트
선배가 캐비닛 하나를 열었다. 서랍마다 전표철이 꽂혀 있었고, 캐비닛 옆에는 얇은 색인 노트가 걸려 있었다. 노트에는 날짜순으로 "3월 2일 → 2서랍 뒤쪽", "3월 3일 → 5서랍 앞쪽" 같은 내용이 적혀 있었다.
"이 노트가 인덱스고, 서랍 속 전표가 테이블이야. 자, 오늘 밤에 이 노트를 새로 베껴 쓸게. 글씨도 예쁘게, 빈 줄도 없이." 선배가 노트를 흔들었다. "그럼 내일 아침에 3월 한 달치 전표를 뽑는 게 빨라질까?"
서랍들을 봤다. 노트를 새로 쓰든 말든 전표는 그 자리에 그대로 꽂혀 있었다.
"…안 빨라지네요. 서랍 여는 횟수가 똑같으니까."
"그게 REBUILD야. 색인 노트를 다시 베껴 쓰는 것이지. 노트가 깔끔해지고 두께도 줄어. 그건 진짜 효과야. 근데 전표가 어느 서랍에 꽂혀 있는지는 하나도 안 바뀌어."
"CF는 노트의 성질이 아니라…"
"서랍의 성질이야. 더 정확히 말하면 노트 순서와 서랍 순서가 얼마나 어긋나 있느냐지. CLUSTERING_FACTOR라는 이름이 인덱스 뷰(USER_INDEXES)에 붙어 있어서 다들 인덱스 숫자라고 착각하는데, 사실은 테이블이 어떻게 쌓였는지를 재는 숫자야. 인덱스는 그걸 기록해 두고 있을 뿐이고."
오후 2시 10분 — 그럼 전표를 다시 꽂는다
"그럼 테이블을 다시 쌓으면 되잖아요."
"해 봐. 단, 원본은 건드리지 마. 복사본에서 해."
먼저 그냥 옮기기만 해 봤다.
sql
CREATE TABLE ORD_MOVE AS SELECT * FROM ORD_RANDOM;
CREATE INDEX ORD_MOVE_X01 ON ORD_MOVE(ORD_DT, ORD_HMS);
ALTER TABLE ORD_MOVE MOVE;INDEX_NAME STATUS
-------------- --------
ORD_MOVE_X01 UNUSABLE인덱스가 못 쓰는 상태가 됐다.
"테이블을 옮기면 모든 행의 ROWID가 바뀌어. 인덱스에 적힌 주소가 전부 틀린 주소가 된 거야." 선배가 말했다. "운영에서 이걸 모르고 MOVE를 치면, 그 순간 그 테이블을 쓰는 쿼리가 전부 전체 스캔으로 떨어져."
인덱스를 다시 만들고 CF를 봤다. 999,958. 그대로였다.
"MOVE는 '옮기기'지 '정렬'이 아니야. 읽은 순서 그대로 다시 쓸 뿐이야. 전표를 서랍째 들어서 옆 캐비닛에 넣은 거지, 날짜순으로 다시 꽂은 게 아니야."
그래서 이번에는 정렬해서 다시 썼다.
sql
CREATE TABLE ORD_SORTED AS
SELECT * FROM ORD_RANDOM ORDER BY ORD_DT, ORD_HMS;
CREATE INDEX ORD_SORTED_X01 ON ORD_SORTED(ORD_DT, ORD_HMS);INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
------------------ ------ ----------- -----------------
ORD_SORTED_X01 2 3774 2222322,223. ORD와 정확히 같은 숫자였다. 쿼리를 다시 쟀다. 41,825 → 1,086.
"됐네요!"
"됐지. 근데 영수증을 봐. 공짜로 한 게 아니야."
테이블 재작성 2.28초 · 논리읽기 48,414 인덱스 재생성 1.99초
통계 재수집 0.87초 그동안 테이블이 잠긴다"100만 건에 5초네요. 생각보다 싼데요."
"100만 건이니까 5초인 거야. 버거원 본사 실제 주문 테이블은 몇 건일 것 같아?" 선배가 말했다. "그리고 그보다 중요한 게 하나 더 있어. 고쳐 놓으면 그 상태가 계속 갈 것 같아?"
고쳐 놓으면 그대로 있나
방금 정렬해 놓은 ORD_SORTED에 5만 건씩 더 넣어 봤다.
(가) POS가 실시간으로 쌓는 경우: 들어오는 순서가 곧 날짜와 시각 순서다.
CF 22,223 -> 23,387 (테이블 블록도 22,414 -> 23,548 로 늘었다)CF는 늘어난 블록 수만큼만 늘었다. 여전히 CF ≈ 블록 수다. 좋은 상태가 유지됐다.
(나) 이관이나 백필로 밀어 넣는 경우: 날짜가 뒤섞여 들어온다.
CF 23,387 -> 122,193전체의 5%만 뒤섞어 넣었는데 CF가 5.2배 나빠졌다.
"이게 1년 전에 버거원에서 일어난 일이야. 서버를 바꾸면서 100만 건을 ORDER BY 없이 퍼 넣은 거지." 선배가 말했다. "그 순간 강도현 씨 보고서가 틀린 문서가 된 거야. 그 사람이 뭘 잘못해서가 아니라."
"그러면… 그때는 맞았던 거예요?"
"재 봐."
이관 전 테이블인 ORD에서 같은 SQL을 돌리면 1,086, 전체 스캔은 22,239였다. 20.5배 개선이다. 강도현의 보고서는 옳았다.
"맞았네요. 그런데 그걸 확인하는 데 오전을 다 썼어요."
"그래. 그 오전이, 그 사람이 숫자 세 줄을 안 적은 값이야."
오후 3시 00분 — 그래서 몇 퍼센트에서 뒤집히는가
"이제 02화에 남겨 둔 숙제를 할 거야. 그때 내가 뭘 물어봤지?"
"2.7%는 인덱스가 이겼어. 50%는 당연히 전체 스캔이 이기고. 그 사이 어딘가에 뒤집히는 지점이 있어. 그게 몇 퍼센트일 것 같아?"
"그때 모른다고 하셨잖아요."
"지금도 몰라. 재 보면 알아. 조회 범위를 하루부터 2년까지 넓혀 가면서 인덱스 강제랑 전체 스캔 강제를 둘 다 재. 테이블 두 개 다."
스크립트를 짜서 돌렸다. 결과가 쌓이는 걸 보는데 뭔가 이상했다. 두 테이블의 전환점이 전혀 다른 자리에 있었다.
| 조회 범위 | 선택도 | ORD 인덱스 | ORD_RANDOM 인덱스 | 전체 스캔 |
|---|---|---|---|---|
| 1일 | 0.14% | 39 | 1,378 | 22,23x |
| 1주 | 0.93% | 246 | 9,362 | 22,23x |
| 16일 | 2.14% | 558 | 21,458 ← 아직 이긴다 | 22,234 |
| 17일 | 2.28% | 597 | 22,917 ← 여기서 진다 | 22,234 |
| 1달 | 4.17% | 1,086 | 41,825 | 22,23x |
| 6달 | 25.17% | 6,545 | 252,629 | 22,23x |
| 1년 | 50.03% | 13,010 | 502,212 | 22,23x |
| 624일 | 85.39% | 22,200 ← 아직 이긴다 | 857,043 | 22,239 |
| 626일 | 85.67% | 22,273 ← 여기서 진다 | 859,887 | 22,239 |
| 2년 | 99.87% | 25,964 | 1,002,401 | 22,239 |
「전체 스캔」 칸에는 각 줄에서 문제가 되는 쪽 테이블의 값을 적었다(
ORD22,239 /ORD_RANDOM22,234). 전 구간 값은2_파고들기.md §3에 있다.
ORD_RANDOM (CF 999,958) 의 손익분기점 : 약 2.2%
ORD (CF 22,223) 의 손익분기점 : 약 85.5%39배 차이였다.
"선배님, 이거… 책에서 '인덱스는 5% 넘으면 불리하다' 같은 걸 본 적이 있거든요. 근데 그런 숫자는 아예 없는 거네요. 테이블마다 다른 거예요."
"그래. 그리고 무엇이 그 숫자를 정하는지도 이제 알잖아."
02화 2_파고들기에 적어 뒀던 근사식을 옮겨 적었다.
인덱스 경유 비용 ≈ BLEVEL + (LEAF_BLOCKS × 선택도) + (CF × 선택도)
전체 스캔 비용 ≈ 테이블 블록 수 (단, 한 번에 여러 개씩 읽으므로 왕복은 그 1/128)"세 번째 항만 다르잖아요. CF가 45배 차이 나니까, 그 항이 전체 스캔을 넘어서는 지점도 그만큼 일찍 오는 거고요."
"그게 오늘의 핵심 문장이야. 적어 둬."
손익분기점은 책에 적힌 숫자가 아니다. 그 테이블의 CF가 정하는 숫자다.
그런데 옵티마이저는 다른 데서 갈아탔다
표를 보다가 한 칸이 걸렸다. 힌트 없이 돌렸을 때 옵티마이저가 스스로 고른 실행계획이다.
ORD_RANDOM : 31일(4.17%) 까지 인덱스 -> 46일(6.40%) 부터 전체 스캔
ORD : 366일(50.03%) 까지 인덱스 -> 457일(62.68%) 부터 전체 스캔"방향은 맞는데 자리가 어긋나요. ORD_RANDOM은 2.2%에서 뒤집혀야 하는데 4%까지 인덱스를 쓰고 있어요."
"옵티마이저는 실제 선택도로 계산하지 않아. 자기가 믿는 선택도(E-Rows)로 계산해." 선배가 E-Rows 칸을 가리켰다. 한 달 범위에서 E-Rows는 4,065, 실제는 41,666이었다. 10배나 낮게 본 것이다.
"또 추정이 틀린 거예요?"
"이 테이블 ORD_DT가 무슨 타입이야?"
ORD_DT VARCHAR2(8)문자였다. '20120101' 같은 여덟 자리 문자열이었다.
"…03화에서 본 그거네요."
"그래. 그건 그거대로 따로 파고, 오늘은 여기까지만." 선배가 손을 들었다. "오늘 가져갈 문장은 이거야. CF가 손익분기점을 정하고, E-Rows는 옵티마이저를 그 지점까지 데려간다. 둘 중 하나만 틀려도 실행계획이 엉뚱한 데서 바뀐다."
오후 3시 40분 — 집으로 돌아온다
선배가 한빛유통 쪽 창을 띄웠다. 2주 전에 손봐 둔 T_ORD_BIG이었다.
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
------------------- ------ ----------- -----------------
IX_ORD_BIG_DT 2 2653 1000000
IX_ORD_BIG_DT_AMT 2 3206 999999CF 1,000,000. 버거원의 ORD_RANDOM과 같은 부류, 최악이다.
"02화에서 억지로 인덱스 태웠을 때 몇 나왔지?"
"27,466이요."
"그 숫자가 어디서 나왔는지 이제 계산할 수 있어. 해 봐."
6월 한 달은 27,390건으로, 전체의 2.739%다.
① 수직 탐색 BLEVEL = 2
② 수평 탐색 2,653 × 0.02739 = 73
③ 테이블 왕복 1,000,000 × 0.02739 = 27,390
--------
합계 27,465실측은 27,466이었다. 1블록 차이다.
2주 전에는 그냥 "크다"고만 느꼈던 숫자가 이제는 세 조각으로 나뉘어 보였다. 그리고 셋 중 하나가 전체의 99.7%를 차지했다.
"③이 전부네요."
"그래. 그리고 02화에서 만든 커버링 인덱스는 ③을 통째로 지운 거야." 계산해 보면 3,206 × 0.02739 + 2 = 90이다. 실측은 91이었다.
그럼 우리 테이블의 손익분기점은
| 조회 범위 | 행수 | 선택도 | IX_ORD_BIG_DT | 전체 스캔 |
|---|---|---|---|---|
| 1일 | 913 | 0.09% | 919 | 5,826 |
| 5일 | 4,565 | 0.46% | 4,580 | 5,826 |
| 6일 | 5,478 | 0.55% | 5,496 ← 이긴다 | 5,826 |
| 7일 | 6,391 | 0.64% | 6,411 ← 진다 | 5,826 |
| 1달 | 27,390 | 2.74% | 27,466 | 5,826 |
| 1년 | 333,245 | 33.32% | 334,132 | 5,826 |
0.58%.
"…엿새요. 일주일치만 조회해도 인덱스가 지는 거예요."
"그래. 그리고 02화에서 네가 잰 건 한 달치(2.74%)였지. 이미 손익분기점을 다섯 배 넘긴 자리였어. 그때 옵티마이저가 전체 스캔을 고른 게 왜 옳았는지 이제 숫자로 알겠지."
"그때는 그냥 '오라클이 옳았다'고만 외웠는데요."
"오늘은 몇 퍼센트부터 옳았는지를 알잖아. 그게 2주 사이에 생긴 차이야."
그런데 커버링은?
"그럼 1년치를 조회하면 rev02는 어떻게 돼요?" 02화 마지막에 선배가 던진 질문이었다.
한 달 (2.74%) : 91
6개월(16.62%) : 535
1년 (33.32%) : 3,227 <- 옵티마이저가 INDEX FAST FULL SCAN 으로 갈아탔다
3년 (100%) : 3,227
전체 스캔 : 5,826 (내내 고정)안 뒤집혔다. 100%까지 가도 커버링이 이겼다.
커버링 경로에는 ③ CF 항이 아예 없다. 남는 건 ② 리프 블록뿐인데, 리프 블록 3,206은 애초에 테이블 블록 5,826보다 적다.
"뒤집힐 수가 없네요. 인덱스가 테이블보다 작으니까."
"그게 커버링이 센 이유야. 그리고…" 선배가 말을 끊었다. "그래서 위험하기도 하고. 그건 좀 이따 얘기하자."
오후 4시 30분 — 그럼 버거원은 뭘 해야 하나
"돌아가자. 버거원 월마감 화면. 뭘 할 거야?"
선택지는 두 개였다. 하나는 힌트를 떼는 것으로, 41,825 → 22,234가 된다. 둘은 테이블을 정렬해서 다시 쌓는 것으로, 41,825 → 1,086이 된다. 대신 운영 테이블을 통째로 다시 써야 하고, 적재가 계속되면 다시 나빠진다.
"하나는 너무 약하고 둘은 너무 큰데요."
"세 번째가 있어. 2주 전에 네가 찾은 거야."
아, 하고 깨달았다. 테이블에 안 가면 된다. 이 쿼리가 쓰는 컬럼은 ORD_DT(조건)와 SHOP_NO(집계 기준) 둘뿐이다. 기존 인덱스는 (ORD_DT, ORD_HMS)라서 SHOP_NO가 없다. 그래서 테이블에 가는 것이다.
"(ORD_DT, SHOP_NO)로 하나 만들면 되겠네요."
"만들어. 근데 운영 테이블이야."
INVISIBLE — 02화에서 한 줄만 던지고 넘어간 것
sql
CREATE INDEX ORD_RANDOM_X02 ON ORD_RANDOM(ORD_DT, SHOP_NO) INVISIBLE;만들어 놓고 쿼리를 돌렸다. 41,825. 달라진 게 없었다.
"힌트를 줘 봐."
/*+ INDEX(o ORD_RANDOM_X02) */를 붙였다. 41,825. 여전히 ORD_RANDOM_X01을 탔다.
"힌트도 안 먹네요."
"INVISIBLE은 힌트보다 세. 02화에서 '따를 수 없는 힌트는 조용히 무시된다'고 했지. 이건 아예 없는 셈 치는 거야." 선배가 말했다. "그래서 안전해. 운영 중에 인덱스가 갑자기 생기면 그 테이블을 쓰는 쿼리 수십 개의 실행계획이 한꺼번에 바뀌어. 하나 좋아지자고 아홉 개를 망칠 수 있어."
"근데 그러면 검증은 어떻게 해요?"
"네 세션에서만 켜."
sql
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;xplan
-------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 |00:00:00.01 | 160 |
| 1 | HASH GROUP BY | | 200 |00:00:00.01 | 160 |
|* 2 | INDEX RANGE SCAN | ORD_RANDOM_X02 | 41666 |00:00:00.01 | 160 |
-------------------------------------------------------------------------------160. TABLE ACCESS 줄이 사라졌다. 2주 전에 봤던 그 장면이었다.
41,825 → 160. 261배.
"그리고 이거 봐." 선배가 방금 조회한 창을 가리켰다. ORD_RANDOM_X02의 CF는 999,850이었다. 여전히 최악이었다.
"…CF가 안 고쳐졌는데 빨라졌어요."
"그래. CF는 테이블에 갈 때만 청구돼. 안 가면 숫자가 아무리 나빠도 상관없어." 선배가 말했다. "오늘 배운 걸 정리하면 세 가지야. CF를 고치는 길(테이블 재구성), 피하는 길(커버링), 인정하는 길(전체 스캔으로 두기). 상황마다 답이 달라."
그런데 커버링에는 함정이 있다
"아까 말하려다 만 거 마저 할게. 다음 분기에 그 화면에 요구사항이 하나 추가된다고 치자. '마지막 주문번호도 같이 보여 주세요.'"
SQL에 MAX(o.ORD_NO)를 하나 더했다.
xplan
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| ORD_RANDOM | 41666 | 41819 |
|* 3 | INDEX RANGE SCAN | ORD_RANDOM_X02 | 41666 | 160 |41,819. TABLE ACCESS 줄이 돌아왔다.
"커버링은 '그 쿼리'에만 듣는 약이야. 컬럼 하나만 더 달라고 해도 무너져. 그때 돌아오는 게 뭐지?"
"CF 청구서요."
"그래. 커버링으로 CF를 고친 게 아니라 미뤄 둔 거야. 그걸 잊으면, 반년 뒤에 화면 하나 고쳤을 뿐인데 갑자기 느려진 이유를 아무도 모르게 돼. 그래서 커버링 인덱스는 어떤 컬럼 목록을 전제로 하는지 반드시 문서에 적어 둬야 해."
⛳ 오늘은 여기까지(중단 지점 2)41,825 → 160을 얻었고, 손익분기점도 숫자로 찾았다(2.2% / 85.5% / 0.58%). 오늘 몫은 여기까지만 해도 다 한 것이다. 이어지는 저녁 작업에서는 오늘의 산출물(T2 승인서)을 만들고, 2주 전에 미뤄 둔 청구서 금액을 확인한다.
오후 5시 40분 — 아침의 그 바인더
선배가 아침에 꺼내 온 강도현의 바인더를 다시 책상에 올렸다.
"오늘 네가 한 일을 저 문서에 적는다고 생각해 봐. 뭘 쓸 거야?"
"조치: 커버링 인덱스 (ORD_DT, SHOP_NO) 생성. 효과: Buffers 41,825 → 160…"
"거기서 멈춰. 그게 저 사람이 쓴 거랑 뭐가 달라?"
말이 막혔다. 숫자가 하나 더 있을 뿐, 구조는 똑같았다. 무엇을 했는가만 적혀 있었다.
"2년 뒤에 누가 이 문서를 읽어. 그 사람이 알아야 하는 게 뭘까?"
"…그때와 지금이 뭐가 달라졌는지요. 그러니까 쟀을 때 몇 건이었는지, 조회 범위가 몇 %였는지요. '261배'는 범위를 안 쓰면 거짓말이니까요. 그리고 손익분기점이 몇 %인지, CF가 얼마였는지, DML에 얼마를 청구하는지, 되돌리려면 뭘 해야 하는지도요."
"그걸 양식으로 만들어야겠네. 오늘 만들어. 이름은 T2 인덱스·힌트 변경 승인서. 리포트가 아니라 승인서야. 차이 알아?"
"…만들고 나서 쓰는 게 아니라 만들기 전에 쓰는 거요."
"그래. T1은 '내가 뭘 고쳤습니다'야. T2는 '이걸 해도 됩니까'야. 그래서 비용 칸이 비면 반려돼."
📋 T2 전문 →
40_보고서와_표준/템플릿/T2_인덱스힌트_변경승인서.md
오후 6시 20분 — 비용 칸
T2의 3번 칸은 비용이었다. redo size, db block changes, 적재 소요 시간을 적는 칸이다.
"이거 뭘로 채워요?"
"재야지. 그리고 이건 우리 테이블로 해. 2주 전에 네가 인덱스 두 개 만들었잖아."
02화 마지막에 선배가 던진 그 질문이었다. "공짜인 것 같아?"
T_ORD_BIG 복사본을 만들고, 인덱스를 0개 → 1개 → 2개로 늘려 가며 5만 건씩 밀어 넣었다.
redo 블록 변경 논리 읽기
인덱스 0개 2.44 MB 1,955 2,611
인덱스 1개 14.13 MB 102,500 153,800
인덱스 2개 26.10 MB 201,200 302,170redo 10.7배, 블록 변경 103배, 논리 읽기 116배.
"5만 건에 이 정도면…"
"야간 배치가 미는 게 몇 건이지?"
100만 건이다. 스무 배다.
"14화 청구서야." 선배가 말했다. "오늘은 금액만 확인하고 접어. 지금 손대면 엉뚱한 걸 고치게 돼."
그리고 겹치는 인덱스
선배가 T_ORD_BIG의 인덱스 두 개를 띄웠다.
IX_ORD_BIG_DT (ORD_DT)
IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT)"뭐 보여?"
한참 보다가 알았다. 앞쪽이 같았다. (ORD_DT)는 (ORD_DT, ORD_AMT)의 맨 앞부분이다.
"(ORD_DT)로 할 수 있는 건 (ORD_DT, ORD_AMT)로도 다 되는 거 아니에요?"
"대체로 그래. 접두 중복이라고 해. 그럼 IX_ORD_BIG_DT를 지우면 어떻게 될까?"
위의 표에 답이 있었다. 인덱스가 2개에서 1개로 줄면 redo가 26.10 → 14.13MB로 줄어든다. 적재 비용이 거의 절반이 된다. 조회는? Q1은 커버링 인덱스를 쓰니까 영향이 없다.
"그럼 지우면 되잖아요!"
이미 DROP INDEX를 치고 있었다. 선배가 손으로 키보드를 막았다.
"T2 1번 칸부터 채워. 그 테이블을 쓰는 SQL이 Q1뿐이야?"
입을 다물었다. 01화에서 받은 조각이 여섯 개였는데, 그중 몇 개가 T_ORD_BIG을 쓰는지 전수 확인을 한 적이 없었다.
"'명백하다'는 게 가장 위험한 단어야." 선배가 손을 뗐다. "인덱스를 만드는 건 되돌리기 쉬워. INVISIBLE로 바꾸면 끝이거든. 근데 지우는 건 되돌리는 데 시간이 걸려. 100만 건이면 2초지만 억 단위면 새벽 내내 걸려. 그리고 다시 만드는 동안 그 인덱스를 쓰던 쿼리들은 전부 전체 스캔으로 떨어져 있어."
"그럼 오늘은…"
"승인서까지만. 제안하고, 근거 적고, 사인 받고, 다음 주에 지워. 그 일주일 동안 네가 전수 확인을 하는 거고."
오후 6시 50분
퇴근 준비를 하는데 선배가 바인더를 들고 와서 내 책상에 놓았다.
"이거 네가 보관해."
"왜요?"
"오늘 네가 한 일의 절반이 저거 때문이었어. 저 문서에 숫자 세 줄만 더 있었으면, 너는 오전에 '아, 그때는 ORD였고 CF가 좋았구나' 하고 넘어갔을 거야. 근데 그게 없어서 두 테이블을 다 재고 나서야 같은 결론에 도착했지."
"…그 사람이 나쁜 건 아니죠."
"아니지. 그때는 옳았어. 20.5배 개선이었잖아." 선배가 말했다. "다만 자기 결정이 언제 틀려지는지를 안 적었어. 그게 다야."
선배가 문 쪽으로 가다가 돌아섰다.
"그리고 오늘 네가 쓴 T2도 2년 뒤에 누가 읽을 거야. 그 사람이 너처럼 오전을 날릴지 말지는 네가 오늘 비워 둔 칸이 몇 개냐에 달렸어."
오늘 남은 것
| 무엇 | 값 |
|---|---|
B1_rev00 | 강도현의 인덱스 강제. 41,825, 전체 스캔(22,234)보다 1.88배 악화 |
B1_rev01 | 힌트 제거. 22,234 |
B1_rev02 | 커버링 (ORD_DT, SHOP_NO). 160 (rev00 대비 261배) |
| CF 대조 | 같은 정의 · 같은 행수. CF는 ORD 22,223 vs ORD_RANDOM 999,958, 블록 수 차이는 38.5배 |
| 손익분기점 | ORD 85.5% · ORD_RANDOM 2.2% · T_ORD_BIG 0.58% |
| 27,466 검산 | 근사식 27,465 (오차 1) |
| DML 청구서 1차 | 인덱스 0→2개: redo 2.44 → 26.10MB · 블록 변경 103배 |
| T2 제정 | 이 교재의 두 번째 표준 문서. 만들기 전에 쓰는 승인서 |
오늘 배운 한 문장
손익분기점은 책에 적힌 숫자가 아니라 그 테이블의 CF가 정하는 숫자다. 그리고 CF는 인덱스가 아니라 테이블이 쌓인 순서가 정하는 값이다.
REBUILD로는 고쳐지지 않는다.
다음으로 넘기는 것
- 전체 스캔이 왜
BLOCKS통계와 정확히 맞지 않는가(HWM), 롤백했는데 인덱스가 왜 안 줄어드는가 → 05화 E-Rows가 문자 날짜에서 어떻게 망가지는가: 현상은 03화에서 봤다. 통계로 고치는 법은 10화에서 다룬다.- 야간 배치가 치르는 진짜 금액(오늘은 5만 건만 쟀다),
IX_ORD_BIG_DT폐기(T2 승인 대기), 인덱스 순서를 테이블에 강제하는 방법 → 14화
다음 →
3_실습과_기록.md에서 오늘의 측정을 직접 재현하고, 손익분기 곡선표를 손으로 만들어 본 뒤 T2 1호를 쓴다. 개념이 막히면2_파고들기.md를 펼친다.