테마
04화 · 파고들기
필요할 때 찾아보는 문서다. 처음 읽을 때는
1_사건.md만으로 충분하다.
| 찾는 것 | 바로 가기 |
|---|---|
| CF는 정확히 무엇을 세는가 | §1 CF |
| 블록 수가 같아도 비용이 다른 이유 | §2 읽기 경로 |
| ★ 손익분기 곡선표 (실측 전부) | §3 손익분기 |
REBUILD로 왜 안 고쳐지나 · 그럼 뭘 해야 하나 | §4 고치기 |
| 옵티마이저의 전환점이 이론과 어긋난 이유 | §5 비용식 |
| 결합 인덱스 컬럼 순서 | §6 컬럼 순서 |
| 커버링과 그 부작용 | §7 커버링 |
INVISIBLE 안전 검증 절차 | §8 INVISIBLE |
| 인덱스가 DML에 청구하는 금액 | §9 DML 비용 |
| 측정값 전부 | §10 측정 |
| 공식문서 좌표 | §11 좌표 |
1. 클러스터링 팩터 — 무엇을 세는가
정의
인덱스를 키 순서대로 처음부터 끝까지 훑으면서, 직전 행이 들어 있던 테이블 블록과 지금 행이 들어 있는 테이블 블록이 다를 때마다 1씩 더한 값이다.
인덱스 리프 (키 순서) 가리키는 테이블 블록 카운터
'20120101' 09:01 -> 블록 #41 +1
'20120101' 09:02 -> 블록 #41 - (같다)
'20120101' 09:03 -> 블록 #41 -
'20120101' 09:04 -> 블록 #7382 +1 (바뀌었다)
...따라서 CF는 그 인덱스로 테이블 전체를 훑을 때 블록을 방문하는 횟수의 상한이다. 선택도 s만큼만 읽으면 그 비율만큼인 CF × s번 방문한다.
기준점 두 개
| CF가 이 값에 가까우면 | 뜻 | 04화 실측 |
|---|---|---|
| 테이블 블록 수 | 최선. 인덱스 순서와 저장 순서가 거의 같다 | ORD: CF 22,223 / BLOCKS 22,422 |
| 테이블 행수 | 최악. 행마다 블록이 바뀐다 | ORD_RANDOM: CF 999,958 / NUM_ROWS 1,000,000 |
두 테이블은 컬럼 정의, 행수, 평균 행 길이, 인덱스 정의, BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS가 전부 같다. 다른 것은 CF 하나뿐이고, 그 CF를 만든 것은 적재 순서다.
⚠️ CF는 인덱스의 성질이 아니다
CLUSTERING_FACTOR는 USER_INDEXES에 들어 있어서 인덱스의 품질 지표로 오해받는다. 실제로는 다음을 재는 값이다.
인덱스의 키 순서와 테이블의 저장 순서가 얼마나 어긋나 있는가
둘 중 바꿀 수 있는 쪽은 테이블의 저장 순서다(§4).
한 테이블에 인덱스가 여러 개면 CF도 여러 개다
테이블은 한 가지 순서로만 쌓인다. 그래서 여러 인덱스의 CF가 동시에 좋을 수는 없다.T_ORD_BIG처럼 두 인덱스의 CF가 모두 1,000,000일 수는 있어도(둘 다 나쁨), 둘 다 5,923일 수는 없다.
2. 싱글블록 · 멀티블록 읽기 경로
Buffers(논리적 블록 접근 횟수)가 같아도 실제 I/O 요청 횟수는 전혀 다르다.
| 경로 | 한 번에 읽는 블록 | 대기 이벤트 | 쓰이는 곳 |
|---|---|---|---|
| 싱글블록 읽기 | 1개 | db file sequential read | 인덱스 수직 탐색 · ROWID로 테이블 접근 |
| 멀티블록 읽기 | 최대 db_file_multiblock_read_count개 | db file scattered read | TABLE ACCESS FULL · INDEX FAST FULL SCAN |
실측 (버퍼캐시를 비우고 측정)
| IO 요청 | 물리 블록 | 멀티블록 요청 | 블록/요청 | |
|---|---|---|---|---|
ORD · FULL 강제 | 186 | 22,229 | 175 | 120 |
ORD · 인덱스 강제 | 약 150 | 1,136 | 0 | 약 7.5 ※ |
ORD_RANDOM · FULL 강제 | 184 | 22,224 | 174 | 121 |
ORD_RANDOM · 인덱스 강제 | 18,934 | 19,107 | 0 | 1 |
실측 환경은
db_file_multiblock_read_count = 128이다. 2만 2천 블록을 186번 만에 가져온다(IO 요청 횟수는 실행마다 ±2 정도 흔들린다). 인덱스를 거쳐 테이블에 갈 때는 ROWID가 블록 하나를 가리키므로, 흩어진 테이블에서는 묶어 읽을 수가 없다.ORD_RANDOM은 블록 하나에 요청 하나다. ※ 잘 정렬된ORD는 다음 ROWID들이 바로 옆 블록을 가리키므로, 오라클이 이웃 블록을 미리 한꺼번에 읽어 온다(db file scattered read로 찍힌다). 그래서 요청 수가 블록 수보다 적다. CF가 좋으면 왕복 횟수까지 줄어든다.그래서
Buffers로 비교하면 인덱스 경로가 실제보다 유리해 보인다.Buffers에서 1.88배 지고 있다면 실제 왕복 횟수로는 100배 지고 있는 셈이다. 블록 수 차이는 하한으로 봐야 한다.
3. 손익분기점 — 실측 곡선
공식
인덱스 경유 비용 ≈ BLEVEL + (LEAF_BLOCKS × 선택도) + (CLUSTERING_FACTOR × 선택도)
└ 수직 ┘ └──── 수평 ────┘ └──── 테이블 랜덤 액세스 ────┘
전체 스캔 비용 ≈ 테이블 블록 수 (논리 블록 기준)
≈ 테이블 블록 수 / MBRC (I/O 요청 기준)손익분기 선택도는 두 식을 같게 놓고 푼 값이다.
(LEAF_BLOCKS + CF) × s = 테이블 블록 수| 테이블 | LEAF + CF | 테이블 블록 | 계산한 s | 실측 s |
|---|---|---|---|---|
ORD | 3,774 + 22,223 = 25,997 | 22,239 | 85.5% | 85.5% |
ORD_RANDOM | 3,774 + 999,958 = 1,003,732 | 22,234 | 2.22% | 2.2% |
T_ORD_BIG | 2,653 + 1,000,000 = 1,002,653 | 5,826 | 0.58% | 0.58% |
★ 곡선표 — ORD (CF 22,223) vs ORD_RANDOM (CF 999,958)
SQL, 인덱스 정의, 행수가 모두 같다. Buffers는 실행계획 0번 줄에서 읽은 값이다.
| 일수 | 끝날짜 | 결과행수 | 선택도 | ORD 인덱스 | ORD FULL | 배수 | ORD_RANDOM 인덱스 | R FULL | 배수 |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 20120101 | 1,371 | 0.14% | 39 | 22,239 | 0.002 | 1,378 | 22,234 | 0.062 |
| 2 | 20120102 | 2,689 | 0.27% | 73 | 22,239 | 0.003 | 2,701 | 22,234 | 0.121 |
| 3 | 20120103 | 3,948 | 0.39% | 105 | 22,239 | 0.005 | 3,964 | 22,234 | 0.178 |
| 5 | 20120105 | 6,628 | 0.66% | 176 | 22,239 | 0.008 | 6,655 | 22,234 | 0.299 |
| 7 | 20120107 | 9,325 | 0.93% | 246 | 22,239 | 0.011 | 9,362 | 22,234 | 0.421 |
| 10 | 20120110 | 13,364 | 1.34% | 350 | 22,239 | 0.016 | 13,416 | 22,234 | 0.603 |
| 14 | 20120114 | 18,751 | 1.88% | 490 | 22,239 | 0.022 | 18,823 | 22,234 | 0.847 |
| 16 | 20120116 | 21,376 | 2.14% | 558 | 22,239 | 0.025 | 21,458 | 22,234 | 0.965 |
| 17 | 20120117 | 22,829 | 2.28% | 597 | 22,239 | 0.027 | 22,917 | 22,234 | 1.031 ☚ |
| 21 | 20120121 | 28,254 | 2.83% | 737 | 22,239 | 0.033 | 28,362 | 22,234 | 1.276 |
| 31 | 20120131 | 41,666 | 4.17% | 1,086 | 22,239 | 0.049 | 41,825 | 22,234 | 1.881 |
| 46 | 20120215 | 64,006 | 6.40% | 1,667 | 22,239 | 0.075 | 64,247 | 22,234 | 2.890 |
| 61 | 20120301 | 84,668 | 8.47% | 2,204 | 22,239 | 0.099 | 84,987 | 22,234 | 3.822 |
| 91 | 20120331 | 125,027 | 12.50% | 3,253 | 22,239 | 0.146 | 125,495 | 22,234 | 5.644 |
| 122 | 20120501 | 168,151 | 16.82% | 4,374 | 22,239 | 0.197 | 168,781 | 22,234 | 7.591 |
| 183 | 20120701 | 251,687 | 25.17% | 6,545 | 22,239 | 0.294 | 252,629 | 22,234 | 11.362 |
| 244 | 20120831 | 333,836 | 33.38% | 8,681 | 22,239 | 0.390 | 335,087 | 22,234 | 15.071 |
| 305 | 20121031 | 416,926 | 41.69% | 10,841 | 22,239 | 0.487 | 418,487 | 22,234 | 18.822 |
| 366 | 20121231 | 500,342 | 50.03% | 13,010 | 22,239 | 0.585 | 502,212 | 22,234 | 22.588 |
| 457 | 20130401 | 626,800 | 62.68% | 16,297 | 22,239 | 0.733 | 629,141 | 22,234 | 28.296 |
| 548 | 20130701 | 751,116 | 75.11% | 19,529 | 22,239 | 0.878 | 753,923 | 22,234 | 33.909 |
| 624 | 20130915 | 853,850 | 85.39% | 22,200 | 22,239 | 0.998 | 857,043 | 22,234 | 38.547 |
| 626 | 20130917 | 856,684 | 85.67% | 22,273 | 22,239 | 1.002 ☚ | 859,887 | 22,234 | 38.674 |
| 730 | 20131230 | 998,672 | 99.87% | 25,964 | 22,239 | 1.167 | 1,002,401 | 22,234 | 45.084 |
ORD_RANDOM 의 손익분기점 : 2.2% (16일 ~ 17일 사이)
ORD 의 손익분기점 : 85.5% (624일 ~ 626일 사이)
------
39배 차이. 차이를 만든 변수는 CF 하나뿐이다.한빛유통 T_ORD_BIG (CF 1,000,000) — IX_ORD_BIG_DT 경로
| 범위 | 행수 | 선택도 | DT 인덱스 강제 | FULL 강제 | 커버링(자율) |
|---|---|---|---|---|---|
| 1일 | 913 | 0.09% | 919 | 5,826 | 6 |
| 2일 | 1,826 | 0.18% | 1,834 | 5,826 | 9 |
| 3일 | 2,739 | 0.27% | 2,749 | 5,826 | 12 |
| 5일 | 4,565 | 0.46% | 4,580 | 5,826 | 18 |
| 6일 | 5,478 | 0.55% | 5,496 | 5,826 | 21 |
| 7일 | 6,391 | 0.64% | 6,411 ☚ | 5,826 | 24 |
| 10일 | 9,130 | 0.91% | 9,157 | 5,826 | 33 |
| 14일 | 12,782 | 1.28% | 12,819 | 5,826 | 44 |
| 21일 | 19,173 | 1.92% | 19,227 | 5,826 | 65 |
| 1달 | 27,390 | 2.74% | 27,466 | 5,826 | 91 |
| 3달 | 83,083 | 8.31% | 83,307 | 5,826 | 269 |
| 6달 | 166,166 | 16.62% | 166,610 | 5,826 | 535 |
| 1년 | 333,245 | 33.32% | 334,132 | 5,826 | 3,227 |
| 3년 | 1,000,000 | 100.00% | 1,002,655 | 5,826 | 3,227 |
02화에서 잰 "6월 한 달, 2.74%"는 이미 손익분기점(0.58%)을 다섯 배 넘긴 지점이었다. 그래서 옵티마이저가
TABLE ACCESS FULL을 고른 것이 옳았다.커버링 칸은 100%까지 가도 뒤집히지 않는다(§7).
⚠️
FULL칸이 5,826이 아니라면 전체 스캔은
BLOCKS통계가 아니라 HWM(고수위선)까지 읽는다. 그리고 HWM은INSERT를 롤백해도 내려가지 않는다. 이 테이블에 한 번이라도 적재 실험을 했다면 FULL 칸이 몇 블록 달라진다. 손익분기점(0.58%)은 그 몇 블록에 영향받지 않는다. HWM은 05화에서 다룬다. 그래서05_DML_청구서.sql은 원본이 아니라 복사본에만 INSERT한다.
27,466의 분해 (02화가 남긴 숙제)
선택도 = 27,390 / 1,000,000 = 0.02739
① 수직 탐색 BLEVEL = 2
② 수평 탐색 LEAF_BLOCKS 2,653 × 0.02739 = 73
③ 테이블 왕복 CF 1,000,000 × 0.02739 = 27,390
-------
합계 27,465 (실측 27,466)커버링(IX_ORD_BIG_DT_AMT, LEAF 3,206)에는 ③이 없어서 2 + 88 = 90이 된다(실측 91).
4. CF를 고치는 것과 못 고치는 것
| 방법 | 소요(100만 행) | CF | 결과 |
|---|---|---|---|
ALTER INDEX ... REBUILD | 2.05초 | 999,958 → 999,958 | ❌ 변화 없음 |
ALTER INDEX ... REBUILD ONLINE | 2.37초 | 999,958 → 999,958 | ❌ |
ALTER INDEX ... COALESCE | 즉시 | 999,958 → 999,958 | ❌ |
ALTER TABLE ... MOVE (정렬 없음) | 0.42초 | 999,958 → 999,958 | ❌ + 인덱스 전부 UNUSABLE |
CREATE TABLE ... AS SELECT ... ORDER BY | 2.28초 | 999,958 → 22,223 | ✅ |
왜 REBUILD로는 안 되는가
REBUILD는 인덱스를 다시 만드는 작업이다. 리프의 빈 공간이 정리되고 LEAF_BLOCKS가 줄어들 수 있는데, 이건 진짜 효과다. 그러나 테이블 블록 안의 행 배치는 한 바이트도 건드리지 않는다. CF는 그 배치를 재는 값이므로 변할 이유가 없다.
REBUILD가 고치는 것: 인덱스의 내부 단편화(LEAF_BLOCKS가 실제 키 수에 비해 지나치게 클 때)REBUILD가 못 고치는 것: CF, 그리고 CF 때문에 생기는 테이블 랜덤 액세스 비용
MOVE도 안 되는 이유
ALTER TABLE ... MOVE는 세그먼트를 새로 만들어 행을 읽은 순서 그대로 옮겨 쓴다. 정렬은 하지 않는다. 게다가 모든 행의 ROWID가 바뀌므로 그 테이블의 인덱스가 모두 UNUSABLE 상태가 된다.
sql
ALTER TABLE ORD_MOVE MOVE;
-- INDEX_NAME STATUS
-- -------------- --------
-- ORD_MOVE_X01 UNUSABLE <- 이 순간 그 인덱스를 쓰던 쿼리가 전부 FULL 로 떨어진다
ALTER TABLE ... MOVE ONLINE을 쓰면 인덱스가 자동으로 유지되지만(에디션과 버전에 따라 다르다), CF는 여전히 그대로다. 정렬하지 않기 때문이다.
고쳐도 영구하지 않다 ★
ORD_SORTED(CF 22,223)에 5만 건(전체의 5%)을 더 넣어 보고 잰 값이다.
| 적재 방식 | CF 변화 | 해석 |
|---|---|---|
인덱스 키 순서대로 (ORD_DT 고정 + ORD_HMS 오름차순) | 22,223 → 23,387 | 테이블 블록이 22,414 → 23,548로 늘어난 만큼만 늘었다. CF ≈ 블록 수 유지 |
| 날짜를 뒤섞어서 (이관·백필) | 23,387 → 122,193 (실행마다 몇 개씩 흔들린다) | 5.2배 악화 |
적재 순서가 인덱스 키 순서와 같으면 좋은 CF가 유지된다. POS나 주문처럼 시간 순으로 쌓이는 테이블이 날짜 인덱스에서 CF가 좋은 것도 이 때문이다. 반대로
ORDER BY없이 대량 이관을 한 번만 해도 그 성질이 통째로 사라진다.
재구성의 대가 (100만 행 기준 실측)
| 항목 | 값 |
|---|---|
CREATE TABLE ... ORDER BY | 2.28초 · 논리읽기 약 4만 8천 · redo 0.26MB |
| 인덱스 재생성 | 1.99초 |
| 통계 재수집 | 0.87초 |
| 그동안 | 테이블 잠김 · (MOVE 방식이면) 인덱스 UNUSABLE |
⚠️ redo가 작게 나온 것은 이 환경이
NOARCHIVELOG이기 때문이다. 직접 경로 적재는NOARCHIVELOG에서 redo를 거의 남기지 않는다. 운영(ARCHIVELOG)에서는 비교할 수 없을 만큼 커진다. 이 항목만큼은 여기 숫자를 운영 추정에 쓰면 안 된다.
5. 옵티마이저는 어디서 갈아타는가
실측 손익분기점(§3)과 옵티마이저가 실제로 실행계획을 바꾼 지점은 같지 않았다.
| 블록 기준 손익분기 | 옵티마이저가 바꾼 지점 | |
|---|---|---|
ORD | 85.5% | 50.03% → 62.68% 사이 |
ORD_RANDOM | 2.2% | 4.17% → 6.40% 사이 |
이유 ① — 옵티마이저는 Buffers가 아니라 Cost로 고른다
Cost는 싱글블록 읽기 한 번을 1로 놓고 환산한 값이다. 전체 스캔은 한 번에 여러 블록을 읽으므로 블록 수보다 훨씬 싸게 환산된다.
한 달 범위에서 잰 Cost(+COST 포맷)는 다음과 같다.
| 인덱스 경유 | 전체 스캔 | |
|---|---|---|
ORD | 109 | 6,083 |
ORD_RANDOM | 4,085 | 6,081 |
전체 스캔 비용 6,083을 테이블 블록 22,422로 나누면 0.27이다. 즉 옵티마이저는 전체 스캔 비용을 블록 수의 약 1/3.7로 친다.
인덱스 경유 비용은 §3의 식과 정확히 맞는다. 선택도에는 옵티마이저가 믿는 값인 E-Rows/NUM_ROWS = 4,065/1,000,000 = 0.004065를 넣는다.
ORD : 2 + 3,774×0.004065 + 22,223×0.004065 = 108 (실측 Cost 109)
ORD_RANDOM : 2 + 3,774×0.004065 + 999,958×0.004065 = 4,082 (실측 Cost 4,085)따라서 옵티마이저가 갈아타는 추정 선택도는 다음과 같이 구한다.
(LEAF_BLOCKS + CF) × s + BLEVEL = 전체 스캔 Cost
ORD : 25,997 × s + 2 = 6,083 -> s = 23.4%
ORD_RANDOM : 1,003,732 × s + 2 = 6,081 -> s = 0.606%이유 ② — 그 "추정 선택도"가 틀려 있었다
ORD_DT는 VARCHAR2(8) 타입이다. 문자 컬럼에 범위 조건을 주면 오라클은 문자열을 내부에서 숫자로 환산해 선택도를 보간한다. 그래서 '20120101' ~ '20120131' 같은 구간이 실제 날짜 비율과 전혀 다르게 계산된다.
| 범위 | 실제 행수 | E-Rows | 비율 |
|---|---|---|---|
31일 (~20120131) | 41,666 | 4,065 | 10배 과소 |
46일 (~20120215) | 64,006 | 11,612 | 5.5배 과소 |
366일 (~20121231) | 500,342 | 102K | 4.9배 과소 |
457일 (~20130401) | 626,800 | 926K | 1.5배 과대 |
'20121231'에서 '20130101'로 하루 넘어갈 때 E-Rows가 102K → 899K로 뛴다. 문자열을 수로 보면 그 하루가 전체 구간의 90%를 차지하기 때문이다.
두 전환점을 추정 선택도로 환산해 보면 이론값과 맞는다.
바뀌기 직전 E-Rows | 바뀐 뒤 E-Rows | 이론 전환점 | |
|---|---|---|---|
ORD_RANDOM | 4,065 (0.41%) | 10,355 (1.04%) | 0.606% ✅ 그 사이 |
ORD | 102K (10.2%) | 899K (89.9%) | 23.4% ✅ 그 사이 |
결론: 비용식은 정확히 작동했다. 틀린 것은 입력값인 선택도였다. CF가 손익분기점을 정하고,
E-Rows는 옵티마이저를 그 지점까지 데려간다. 둘 중 하나만 틀려도 실행계획은 엉뚱한 자리에서 바뀐다.문자로 박힌 날짜 자체의 문제는 03화에서 봤다. 통계가 분포를 담지 못하는 문제는 10화에서 다룬다.
6. 결합 인덱스 컬럼 순서
컬럼 순서를 정하는 기준은 네 가지이고, 위에 있는 기준이 먼저다.
| 순위 | 기준 | 이유 |
|---|---|---|
| 1 | 항상 =로 들어오는 컬럼을 앞에 | 선두 컬럼이 등치 조건이어야 리프에서 연속 구간을 지목할 수 있다. 선두가 범위 조건이면 그 뒤 컬럼은 스캔 시작점만 좁힐 뿐이다 |
| 2 | WHERE에 항상 등장하는 컬럼을 앞에 | 선두 컬럼 조건이 없으면 RANGE SCAN이 안 된다(02화 §3 ⑥) |
| 3 | 선택도가 좋은(=걸러지는 양이 많은) 컬럼을 앞에 | 수평 탐색 길이가 줄어든다 |
| 4 | ORDER BY / GROUP BY 순서와 맞추기 | 정렬 연산을 통째로 없앨 수 있다 |
CF와 컬럼 순서
선두 컬럼이 테이블의 물리적 적재 순서와 일치할수록 CF가 좋아진다.
ORD_X01(ORD_DT, ORD_HMS)의 CF가 22,223인 것은 ORD가 ORD_DT, ORD_HMS 순으로 쌓여 있기 때문이다. 정의가 같은 인덱스가 ORD_RANDOM에서 999,958인 것은 그 테이블이 그 순서로 쌓이지 않아서다.
그래서 CF는 컬럼 순서를 고르는 근거가 되지만, 그 혜택은 테이블당 인덱스 하나만 받는다(§1 맨 끝).
접두 중복 (prefix redundancy)
IX_ORD_BIG_DT (ORD_DT)
IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT)(A)는 (A, B)의 접두부다. (A)가 하던 일은 (A, B)로 대체로 할 수 있다.
| 지울 때 얻는 것 | 지울 때 잃을 수 있는 것 |
|---|---|
| DML 비용 절감 (실측 redo 26.10 → 14.13MB) | (A)가 더 작아서(2,653 vs 3,206) 얻던 FAST FULL SCAN 이득 |
| 세그먼트 공간 | (A) 쪽 통계/히스토그램에 의존하던 계획 |
⚠️ 지우기 전에 반드시 ⓐ 그 테이블을 참조하는 SQL을 전수 조사하고 ⓑ
DBA_INDEX_USAGE를 관찰하고 ⓒ T2 승인서를 쓴다. 인덱스 생성은INVISIBLE로 즉시 되돌릴 수 있지만, 삭제를 되돌리려면 재생성 시간이 통째로 걸린다.
7. 커버링 — CF를 피하는 길
인덱스 경유 비용 ≈ BLEVEL + (LEAF_BLOCKS × s) + (CF × s)
^^^^^^^
커버링이면 이 항이 통째로 없다CF는 테이블에 갈 때만 청구된다. 그래서 커버링 인덱스는 CF를 고치지 않고도 이긴다.
실측 — ORD_RANDOM_X02(ORD_DT, SHOP_NO)의 CF는 999,850 (여전히 최악)
| 범위 | 선택도 | 커버링 | FULL | 계획 |
|---|---|---|---|---|
| 1달 | 4.17% | 160 | 22,234 | INDEX RANGE SCAN |
| 1년 | 50.03% | 1,891 | 22,234 | INDEX RANGE SCAN |
| 2년 | 99.87% | 3,800 | 22,234 | INDEX FAST FULL SCAN |
손익분기점이 생기지 않는다. LEAF_BLOCKS(3,774)가 테이블 블록(22,414)보다 작아서 선택도가 100%가 돼도 전체 스캔을 넘어설 수 없다.
☠️ 부작용 — "그 쿼리"에만 듣는 약
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;160 → 41,819. TABLE ACCESS BY INDEX ROWID가 돌아오고, 그 순간 CF 청구서가 다시 날아온다.
| 대가 | 측정값 / 내용 |
|---|---|
| 세그먼트 증가 | 인덱스 하나당 31MB (테이블은 176MB) |
| DML 비용 | → §9 |
| 컬럼 추가에 취약 | 한 컬럼만 더 요구돼도 효과가 통째로 사라진다 |
| 접두 중복 유발 | (ORD_DT, ORD_HMS)와 (ORD_DT, SHOP_NO)가 함께 존재하게 된다 |
규칙: 커버링 인덱스를 만들면 T2 승인서에 "전제하는 컬럼 목록"을 반드시 적는다. 그 목록이 바뀌는 순간 이 인덱스의 근거가 사라진다.
8. INVISIBLE 검증 절차
운영 중인 시스템에 인덱스를 추가할 때 안전한 순서는 이것 하나뿐이다.
sql
-- ① 만들되 옵티마이저에게는 숨긴다
CREATE INDEX ORD_RANDOM_X02 ON ORD_RANDOM(ORD_DT, SHOP_NO) INVISIBLE;
-- ② 내 세션에서만 켠다 (다른 세션은 아무 영향 없음)
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;
-- 여기서 전/후 Buffers 를 잰다
-- ③ T2 승인
-- ④ 공개
ALTER INDEX ORD_RANDOM_X02 VISIBLE;
-- ⑤ 관찰기간 (DBA_INDEX_USAGE · Buffers · 배치 소요)
-- ⑥ 확정 또는 폐기
-- 롤백 1단계(초 단위) : ALTER INDEX ORD_RANDOM_X02 INVISIBLE;
-- 롤백 2단계 : DROP INDEX ORD_RANDOM_X02;실측으로 확인한 성질
| 상태 | 옵티마이저 자율 | 힌트로 이름을 찍어도 |
|---|---|---|
INVISIBLE | 41,825 (X01 사용) | 41,825 (X01 사용) |
INVISIBLE + 세션 파라미터 ON | 160 (X02 사용) | — |
VISIBLE | 160 (X02 사용) | — |
INVISIBLE은 힌트보다 강하다. 02화에서 본 "못 따를 힌트는 조용히 무시된다"는 규칙이 여기서도 그대로 통한다. 인덱스를 없는 것으로 취급하므로 운영 중인 실행계획에 주는 영향이 0이다.⚠️
INVISIBLE이어도 DML은 그 인덱스를 갱신한다. 조회할 때만 안 보일 뿐, 적재 비용은 이미 치르고 있다. 즉INVISIBLE로 "조회 위험"은 0으로 만들 수 있어도 "적재 비용"은 0으로 만들지 못한다.
9. 인덱스의 DML 비용
T_ORD_BIG 복사본에 5만 건을 INSERT ... SELECT로 넣고 쟀다(워밍업 2회 후 측정). redo size는 소수점까지 똑같이 재현되고, 나머지 두 칸은 회차마다 0.1% 안쪽으로 흔들린다.
| 인덱스 | redo size | db block changes | session logical reads |
|---|---|---|---|
| 0개 | 2.44 MB | 1,955 | 2,611 |
1개 (ORD_DT) | 14.13 MB | 102,500 | 153,800 |
2개 +(ORD_DT, ORD_AMT) | 26.10 MB | 201,200 | 302,170 |
| 0→2개 배수 | 10.7배 | 103배 | 116배 |
2회 실행 실측: redo
2.44 / 14.13 / 26.10(동일) · 블록 변경1,955~1,956 / 102,494~102,552 / 201,206~201,210· 논리 읽기2,611~2,612 / 153,747~153,879 / 302,161~302,191.
5만 건을 적재할 때 인덱스 하나마다 redo 약 11.8MB와 블록 변경 약 10만 회가 추가된다. 행 하나, 인덱스 하나당 블록 변경이 2회인 셈이다.
측정 시 함정 ★ 실측으로 걸렸다
워밍업 없이 재면 "인덱스 1개"가 "2개"보다 비싸게 나온다(33.20MB vs 26.10MB). 인덱스를 막 만든 직후에는 리프 블록이 꽉 차 있어서 첫 적재 때 블록 분할이 한꺼번에 일어나기 때문이다. 이 일회성 비용이 측정값에 섞인다. 그래서 구성마다 워밍업 2회(INSERT + ROLLBACK)를 돌린 뒤에 잰다.
롤백해도 인덱스는 줄어들지 않는다
IX_LOAD_DT LEAF_BLOCKS | |
|---|---|
| 생성 직후 | 2,653 |
| 5만 건 INSERT → ROLLBACK 후 | 3,747 |
행은 전부 사라졌는데 인덱스는 41% 커진 채로 남는다. 쪼개진 블록은 다시 합쳐지지 않는다. 그래서 INSERT + ROLLBACK 테스트는 복사본에서 해야 한다. 테이블의 HWM도 같은 이유로 돌아오지 않는다(05화).
10. 이 화의 측정값 전부
모두 실측값이다(2026-10-18). 측정 환경: 컨테이너 ora-burger(dev) · ora-hanorder(sqlp) · Oracle AI Database 26ai Free 23.26.2.0.0 · db_block_size 8192 · db_file_multiblock_read_count 128 · cpu_count 2 · optimizer_mode ALL_ROWS · 시스템 통계 noworkload.
객체 상태
| 테이블 | 행수 | BLOCKS | AVG_ROW_LEN | 인덱스 | BLEVEL | LEAF_BLOCKS | CF |
|---|---|---|---|---|---|---|---|
ORD | 1,000,000 | 22,422 | 154 | ORD_X01(ORD_DT,ORD_HMS) | 2 | 3,774 | 22,223 |
ORD_RANDOM | 1,000,000 | 22,414 | 154 | ORD_RANDOM_X01(ORD_DT,ORD_HMS) | 2 | 3,774 | 999,958 |
| 〃 | 〃 | 〃 | 〃 | ORD_RANDOM_X02(ORD_DT,SHOP_NO) ★04화 | 2 | 3,774 | 999,850 |
T_ORD_BIG | 1,000,000 | 5,923 | 37 | IX_ORD_BIG_DT(ORD_DT) | 2 | 2,653 | 1,000,000 |
| 〃 | 〃 | 〃 | 〃 | IX_ORD_BIG_DT_AMT(ORD_DT,ORD_AMT) | 2 | 3,206 | 999,999 |
B1 리비전 (버거원 월마감 집계 · 한 달 = 41,666행 = 4.17%)
| rev | 내용 | 계획 | Buffers |
|---|---|---|---|
| rev00 | 강도현의 INDEX 힌트 | INDEX RANGE SCAN + TABLE ACCESS BY ROWID | 41,825 |
| — | 같은 조건, FULL 강제 | TABLE ACCESS FULL | 22,234 |
| rev01 | 힌트 제거 (옵티마이저 자율) | TABLE ACCESS FULL | 22,234 |
| rev02 | 커버링 (ORD_DT, SHOP_NO) | INDEX RANGE SCAN만 | 160 |
| — | rev02 + 컬럼 1개 추가 | INDEX RANGE SCAN + TABLE ACCESS BY ROWID | 41,819 |
| 참고 | 같은 SQL을 ORD에서 (이관 전) | INDEX RANGE SCAN + TABLE ACCESS BY ROWID | 1,086 |
손익분기점
| 테이블 | CF | 손익분기 선택도 | 옵티마이저 전환 구간 |
|---|---|---|---|
ORD | 22,223 | 85.5% | 50.03% ~ 62.68% |
ORD_RANDOM | 999,958 | 2.2% | 4.17% ~ 6.40% |
T_ORD_BIG | 1,000,000 | 0.58% | (커버링이 항상 이김) |
Cost (한 달 범위, +COST)
| 인덱스 경유 | 전체 스캔 | |
|---|---|---|
ORD | 109 | 6,083 |
ORD_RANDOM | 4,085 | 6,081 |
재현성
| 스크립트 | 2회 실행 결과 |
|---|---|
01_손익분기_곡선.sql | 완전 동일(바이트 단위). 컨테이너를 막 켜서 캐시가 비어 있으면 FULL 칸이 8블록 작게(22,231 / 22,226), BATCHED 인덱스 칸이 100블록쯤 작게 찍힌다. 그래서 00~03 스크립트는 맨 앞에서 두 테이블을 캐시에 올려 두고 시작한다([준비] 캐시 데우기). 인덱스 칸과 교차 지점은 어느 쪽이든 그대로다 |
02_REBUILD_와_재구성.sql | CF와 Buffers는 전부 동일. CTAS 논리 읽기(48,356 ~ 48,376)와 [D]의 5만 건 적재 뒤 CF(23,387~23,391 / 122,193~122,197)만 실행마다 몇 개씩 흔들린다. 일반 INSERT가 빈 블록을 고르는 순서가 매번 조금씩 달라서다 |
03_커버링_INVISIBLE.sql | 실행계획과 Buffers 전부 동일 |
04_한빛유통_검산.sql | 동일 |
05_DML_청구서.sql | redo 동일(2.44 / 14.13 / 26.10MB), 블록 변경과 논리 읽기는 0.1% 이내 |
11. 원천 좌표
전체 지도 →
00_자료준비/06_원서와_공식문서_지도.mdPDF 페이지 = 책 페이지 − 2 ·성능고도화II_목차색인.md(공개 사이트에는 없음)
성능고도화 (주 원천)
| 주제 | 권-장-절 |
|---|---|
| 클러스터링 팩터 | II권 Ch1 05절 |
| 인덱스 손익분기점 · 랜덤 액세스 비용 | II권 Ch1 06절 |
| 인덱스 컬럼 순서 · 결합 인덱스 설계 | II권 Ch1 07절 |
| 인덱스 무력화 (02화) | II권 Ch1 03절 |
| 블록·HWM (05화) | I권 |
오라클 공식문서
| 주제 | 매뉴얼 | 장 제목 |
|---|---|---|
| 인덱스 접근 경로와 비용 | tgsql | Optimizer Access Paths |
| 클러스터링 팩터 | tgsql | Optimizer Statistics Concepts |
INVISIBLE 인덱스 | admin | Managing Indexes |
ALTER INDEX REBUILD / COALESCE | admin | Managing Indexes |
ALTER TABLE ... MOVE | admin | Managing Tables |
멀티블록 읽기 / db_file_multiblock_read_count | refrn | Initialization Parameters |
이 화에서 꼭 확인할 뷰
sql
USER_INDEXES -- clustering_factor · blevel · leaf_blocks · visibility · status
USER_TABLES -- blocks · num_rows · avg_row_len
USER_IND_COLUMNS -- 컬럼 순서
USER_SEGMENTS -- 인덱스가 실제로 차지한 공간
V$MYSTAT + V$STATNAME -- redo size · db block changes · physical read IO requests
V$SQL_PLAN_STATISTICS_ALL -- 실행계획 각 줄의 정확한 Buffers (DISPLAY_CURSOR 는 큰 수를 K 로 줄여 쓴다)
DBA_INDEX_USAGE -- 인덱스가 실제로 쓰이는가 (T2 4번 칸)다음 화가 여기서 가져가는 것
| 화 | 가져가는 것 |
|---|---|
| 05 | 전체 스캔이 BLOCKS 통계와 정확히 안 맞는 이유(HWM) · 롤백해도 인덱스와 HWM이 안 줄어드는 이유 · 블록 안에서 행이 어떻게 배치되는가 |
| 03 · 10 | VARCHAR2 날짜에서 범위 선택도 추정이 망가지는 모습(E-Rows 4,065 vs 실제 41,666) |
| 06 | 또 하나의 레거시 힌트인 Q2의 USE_NL. 오늘의 INDEX 힌트와는 다른 종류의 실패다 · 힌트 유효기간 대장 제정 |
| 14 | 인덱스 2개의 적재 청구서 본청구 (오늘은 5만 건만 쟀다) · IX_ORD_BIG_DT 폐기 (T2 승인 대기) · 인덱스 순서를 테이블에 강제하는 방법(파티션·IOT) |
| 전 화 | T2 인덱스·힌트 변경 승인서: 이후 모든 인덱스/힌트 변경은 이 양식을 거친다 |