테마
04화 · 인덱스를 태웠더니 더 느려졌다
🗓️ 3월 25일 (월) · 03화에서 일주일 뒤 · 무대
ora-burger(버거원 POS) +ora-hanorder(한빛유통) 오늘 배울 것: 손익분기점은 책에 적힌 숫자가 아니라 그 테이블의 CF가 정하는 숫자다. 이 화는 1막의 반전이다. 02화에서 얻은 "인덱스로 64배"라는 믿음이 여기서 뒤집힌다.
파일 세 개
| 파일 | 무엇을 하나 | 시간 | |
|---|---|---|---|
| 읽는다 | 1_사건.md | 교과서대로 만든 인덱스가 전체 스캔보다 1.88배 느린 원인을 찾고, 손익분기점을 숫자로 알아내는 하루 | 35분 |
| 한다 | 3_실습과_기록.md | 대조군 측정 · 손익분기 곡선 작성 · REBUILD 3종 · 커버링/INVISIBLE · T2 승인서 1호 | 80~110분 |
| 찾는다 | 2_파고들기.md | CF의 정의 · 싱글/멀티블록 · 곡선표 전문 · 컬럼 순서 · 커버링 부작용 · DML 비용 | 필요할 때 |
1을 읽고 3을 따라 한다. 2는 막힐 때 펼친다. 시간이 없는 주에는 1_사건을 중단 지점 1까지 읽고 3_실습은 STEP 0, 1, 2, 3, 7만 한다(약 45분).
지난 화에서 들고 오는 것
Q1 = 91블록 | 02화에서 5,827 → 91 (64배). 커버링 인덱스 (ORD_DT, ORD_AMT) |
T_ORD_BIG 인덱스 | 2개 (IX_ORD_BIG_DT, IX_ORD_BIG_DT_AMT) |
| 02화에서 미뤄 둔 것 ① | CF = 1,000,000. 27,466이라는 숫자의 정체 |
| 02화에서 미뤄 둔 것 ② | "몇 퍼센트에서 뒤집히는가": 선배도 모른다고 했다. 오늘 잰다 |
| 01화에서 미뤄 둔 것 | 커피 캐리어. "캐리어 하나에 몇 잔이 들어 있느냐보다 더 고약한 문제가 있거든." |
그리고 박팀장이 새 안건을 하나 들고 왔다.
"우리 회사가 버거원을 인수했습니다. 그쪽 본사 직원들이 하나같이 하는 얘기가 있어요. 월마감 집계 화면이 느리다고요."
오늘의 전개 — 체크리스트를 다 통과했는데 느리다
| 한 일 | 결과 | |
|---|---|---|
| 발견 | 2년 전 강도현의 성능 점검 보고서. "인덱스 RANGE SCAN으로 변경 완료"라고만 적혀 있고 측정 수치는 한 줄도 없다 | |
| 1차 | 02화 체크리스트대로 점검: RANGE SCAN ✓ access ✓ 컬럼 가공 없음 ✓ | 41,825. 전체 스캔(22,234)보다 1.88배 느리다 |
| 대조 | 옆의 쌍둥이 테이블 ORD에서 같은 SQL을 돌린다 | 1,086. 38.5배 차이. 정의, 행수, 인덱스가 전부 같은데도 |
| 원인 | 다른 것은 CLUSTERING_FACTOR 하나 | 22,223 vs 999,958 |
| 2차 | REBUILD · REBUILD ONLINE · COALESCE | CF가 1도 안 변한다 |
| 3차 | ALTER TABLE MOVE | 안 변한다. 게다가 인덱스가 UNUSABLE 상태가 된다 |
| 4차 | CTAS ... ORDER BY로 테이블 재구성 | CF 22,223, 1,086. 단, 5%만 뒤섞어 넣어도 다시 무너진다 |
| 답 | 커버링 인덱스(INVISIBLE로 안전하게) | 160. rev00 대비 261배 |
왜 또 깨지게 짰을까? 02화에서는 "인덱스를 만들면 빨라진다"는 믿음이 깨졌다. 오늘은 "인덱스를 제대로 타면 빨라진다"는 믿음이 깨진다. 제대로 탔는데도 졌다. 그리고 이번에는 처음으로, 내가 잘못한 게 아니라 데이터가 바뀐 것이 원인이다.
오늘 배우는 한 문장
손익분기점은 책에 적힌 숫자가 아니라 그 테이블의 CF가 정하는 숫자다.
SQL도 같고 인덱스 정의도 같고 행도 똑같이 100만 개인데, 측정한 손익분기점은 이렇게 갈린다.
ORD_RANDOM (CF 999,958) : 2.2%
ORD (CF 22,223) : 85.5% <- 39배 차이
T_ORD_BIG (CF 1,000,000) : 0.58% <- 02화에서 잰 2.74% 는 이미 5배 넘긴 자리였다그리고 CF는 인덱스가 아니라 테이블이 쌓인 순서가 정하는 값이다. 그래서 REBUILD로는 고쳐지지 않는다.
🫙 다시 꺼낸 비유 — 커피 캐리어
01화에서 선배가 나중에 다시 꺼내겠다며 넣어 둔 그 캐리어다.
"169잔이 한 캐리어에 들어 있다고 했지. 그건 네가 필요한 169건이 같은 캐리어에 있을 때 얘기야."
ORD: 필요한 4만 잔이 캐리어 하나에 45잔씩 담겨 있다. 900번만 들고 오면 된다(실측 1,086).ORD_RANDOM: 필요한 4만 잔이 캐리어 하나에 한 잔씩 들어 있다. 4만 번을 들고 와야 한다(실측 41,825).
더 고약한 것도 있다. 들고 오는 방식까지 다르다.
| 물리 I/O 요청 | 한 번에 | ||
|---|---|---|---|
| 전체 스캔 (멀티블록) | 186회 | 128블록 | 수레로 한 번에 싣고 온다 |
| 인덱스 → 테이블 (싱글블록) | 18,934회 | 1블록 | 한 개씩 들고 온다 |
"왕복이 비싼 거지 커피가 비싼 게 아니야." (01화)
새 비유는 문서고의 전표 캐비닛과 색인 노트다. 색인 노트를 아무리 깔끔하게 다시 베껴 써도(REBUILD) 전표가 꽂힌 서랍 순서는 그대로다.
☠️ 오늘의 함정 — 커버링은 '그 쿼리'에만 듣는 약이다
sql
-- 160
SELECT o.SHOP_NO, COUNT(*) FROM ORD_RANDOM o WHERE ... GROUP BY o.SHOP_NO;
-- 41,819 <- 컬럼 하나 추가했을 뿐이다
SELECT o.SHOP_NO, COUNT(*), MAX(o.ORD_NO) FROM ORD_RANDOM o WHERE ... GROUP BY o.SHOP_NO;커버링 인덱스는 CF를 고친 게 아니라, CF 청구서를 받을 일을 피해 간 것이다. 실제로 ORD_RANDOM_X02의 CF는 999,850으로 여전히 최악이다. 그래서 커버링이 깨지는 순간 청구서가 그대로 돌아온다.
그래서 T2 승인서에는 「전제하는 컬럼 목록」 칸이 있다.
실습 준비
bash
docker start ora-burger ora-hanorder| 컨테이너 | 역할 | 접속 |
|---|---|---|
ora-burger | 인수한 버거원 POS 백업본 (ORD / ORD_RANDOM) | docker exec -i ora-burger sqlplus -s dev/dev@localhost/FREEPDB1 |
ora-hanorder | 한빛유통 (02화 종료 상태) | docker exec -i ora-hanorder sqlplus -s sqlp/sqlp@localhost/FREEPDB1 |
물리 I/O 측정과 V$SQL_PLAN_STATISTICS_ALL 조회에 필요한 권한은 한 번만 부여해 두면 된다. 방법은 3_실습과_기록.md STEP 0에 있다.
| 스크립트 | 컨테이너 | 무엇 | 소요 |
|---|---|---|---|
실습/00_CF_대조.sql | ora-burger | 같은 정의 · 같은 행수 · 다른 CF. 38.5배 차이와 왕복 횟수 | 10초 |
실습/01_손익분기_곡선.sql | ora-burger | 오늘의 산출물 ①: 24구간 × 3모드 × 2테이블 곡선표 | 15초 |
실습/02_REBUILD_와_재구성.sql | ora-burger | REBUILD 3종 · MOVE · CTAS ORDER BY · 재악화 | 35초 |
실습/03_커버링_INVISIBLE.sql | ora-burger | INVISIBLE 절차 · 커버링 · 부작용 | 15초 |
실습/04_한빛유통_검산.sql | ora-hanorder | 27,466 근사식 검산 · 우리 테이블 손익분기 0.58% | 5초 |
실습/05_DML_청구서.sql | ora-hanorder | 인덱스 0/1/2개 적재 비용 (14화 1차 증거) | 50초 |
모두 멱등하다. 맨 앞에서 작업용 객체를 지우고 시작하며, 02~05는 복사본에서만 작업하고 끝나면 반납한다.
05_DML_청구서.sql이 원본이 아니라 복사본에 적재하는 것은INSERT를 롤백해도 HWM이 되돌아가지 않기 때문이다(→ 05화).리비전은
리비전/에B1_rev00→rev01→rev02순서로 남는다.
이 화의 산출물
| T2 인덱스·힌트 변경 승인서 제정 | 40_보고서와_표준/템플릿/T2_인덱스힌트_변경승인서.md: 인덱스를 만들기 전에 쓰는 문서. 비용, 손익분기, 롤백 칸이 비어 있으면 반려한다 |
| 손익분기 곡선표 | 2_파고들기.md §3: 24구간 전문 |
B1_rev00 → rev02 | 41,825 → 22,234 → 160 |
이 화가 다음으로 넘기는 것
| 무엇 | 어디로 |
|---|---|
전체 스캔이 왜 BLOCKS와 정확히 맞지 않나(HWM) · 롤백해도 인덱스가 왜 안 줄어드나 | 05화 |
VARCHAR2 날짜에서 선택도 추정이 망가지는 모습(E-Rows 4,065 vs 실제 41,666) | 현상은 03화에서 봤다 · 고치는 법은 10화 |
| 힌트의 유효기간: rev00의 힌트는 2년 전에는 옳았다 | 06화 |
적재 청구서 본청구 · IX_ORD_BIG_DT 폐기(승인 대기) · 인덱스 순서를 테이블에 강제하는 법 | 14화 |
Q1은 왜 더 고치지 않나
04화에서는 Q1을 rev03이나 rev04로 고치지 않는다. 재 보니 그럴 이유가 없었다. 커버링 경로에는 CF 항이 없어서 조회 범위를 100%까지 넓혀도 전체 스캔에 지지 않는다(3년 전체 3,227 vs 5,826). 02화의 rev02(91)가 이미 바닥이다. 04화에서 한 일은 그 사실을 측정으로 확인하고, 이유를 식으로 설명한 것이다. 남은 개선 여지(IX_ORD_BIG_DT 접두 중복 폐기)는 SQL이 아니라 인덱스 구성 문제라서, T2 승인을 기다리며 14화로 넘긴다.
← 03화 「날짜가 문자로 박혀 있다」 · 교재 전체 안내 · 다음 → 05화 「블록이라는 그릇」