테마
04화 · 실습과 기록
여기서 하는 일: 손익분기점을 직접 찾아낸다.
1_사건.md를 읽은 것은 "선배가 하는 걸 지켜본 것"이고, 이 문서를 끝내야 직접 해 본 것이 된다. 개념이 막히면2_파고들기.md를 펼친다.시간이 없는 주에는 STEP 0 → 1 → 2 → 3 → 7만 한다(약 45분). 오늘은 컨테이너가 두 개다. STEP 2~5는
ora-burger, STEP 6은ora-hanorder에서 한다.
STEP 0 — 전제 확인 (3분)
bash
docker start ora-burger ora-hanorder버거원 쪽 (ora-burger · dev/dev)
sql
SELECT table_name, num_rows, blocks FROM user_tables WHERE table_name IN ('ORD','ORD_RANDOM');
SELECT index_name, leaf_blocks, clustering_factor FROM user_indexes
WHERE index_name IN ('ORD_X01','ORD_RANDOM_X01');| 기대 | |
|---|---|
ORD / ORD_RANDOM | 둘 다 1,000,000행 · BLOCKS 22,422 / 22,414 |
ORD_X01 | LEAF 3,774 · CF 22,223 |
ORD_RANDOM_X01 | LEAF 3,774 · CF 999,958 |
CF 두 값이 이대로 나오지 않으면 여기서 멈춘다. 이 화의 모든 실험은 이 대조군을 전제로 한다. 버거원 데이터는
00_적재_한번에.sh가 난수 시드를 고정해서 만든다. 그래서 화면에 찍히는 숫자는 이 화의 숫자와 끝자리까지 같아야 한다. CF가 999,958이 아니면 시드 없이 적재한 것이므로 L2 카드대로 다시 적재한다.💡
00~03스크립트는 맨 앞에서 2초쯤 캐시를 데운다.docker start직후처럼 버퍼 캐시가 비어 있으면 디스크에서 올라오는 블록이 섞여Buffers가 수십~100블록 덜 찍히기 때문이다(41,825가 41,72x로). 캐시를 데우고 나면 첫 실행부터 교재 값이 나온다.
한빛유통 쪽 (ora-hanorder · sqlp/sqlp) — 02화 종료 상태
sql
SELECT index_name, leaf_blocks, clustering_factor FROM user_indexes WHERE table_name='T_ORD_BIG';기대: IX_ORD_BIG_DT LEAF 2,653 / CF 1,000,000 · IX_ORD_BIG_DT_AMT LEAF 3,206 / CF 999,999
한 번만 해 두는 준비 (버거원 쪽 물리 I/O 측정용)
00_CF_대조.sql의 [D]는 버퍼 캐시를 비우고 물리 I/O 요청을 센다. 권한이 없으면 [D]만 실패하고 나머지는 정상으로 진행된다.
bash
docker exec -i ora-burger bash -lc "sqlplus -s / as sysdba <<'EOF'
ALTER SESSION SET CONTAINER=FREEPDB1;
GRANT ALTER SYSTEM TO dev;
GRANT SELECT ON v_\$mystat TO dev;
GRANT SELECT ON v_\$statname TO dev;
GRANT SELECT ON v_\$sql TO dev;
GRANT SELECT ON v_\$sql_plan TO dev;
GRANT SELECT ON v_\$sql_plan_statistics_all TO dev;
EOF"ora-hanorder 쪽에서도 sqlp에게 같은 뷰 권한을 줘야 한다.
STEP 1 — 예측을 봉인한다 (4분)
오늘 다룰 두 테이블은 컬럼 정의 · 행수 · 인덱스 정의가 완전히 같다. 적재 순서만 다르다.
Q1. 같은 SQL 을 같은 인덱스 힌트로 돌리면 Buffers 가 몇 배쯤 차이 날 것 같은가? ____ 배
Q2. CF 가 나쁜 테이블에서, 인덱스를 ALTER INDEX ... REBUILD 하면 CF 는?
① 좋아진다 ② 안 변한다 ③ 더 나빠진다 ______
Q3. "인덱스는 전체의 몇 % 를 넘게 읽으면 전체 스캔보다 불리해진다"
— 당신이 알고 있는 숫자를 적어라. ____ %
Q4. 02화에서 만든 인덱스 2개가 INSERT 를 몇 배쯤 느리게 할 것 같은가? ____ 배▶ 적고 나서 펼치기
실측값은 Q1 = 38.5배, Q2 = ②(1도 안 변한다), Q4 = redo 10.7배 / 블록 변경 103배다.
이 화의 핵심은 Q3이다. 대부분 "5%"나 "10%"처럼 숫자 하나를 적는다. 그런 숫자는 없다. SQL과 인덱스 정의가 같아도 재 보면 손익분기점이 2.2%와 85.5%로 갈린다. 손익분기점을 정하는 것은 책이 아니라 그 테이블의 CF다.
STEP 2 — 대조군을 눈으로 본다 (8분) ★ 오늘의 출발점
bash
cd 10_본편_튜너의_1년/04화_인덱스를_태웠더니_더_느려졌다/실습
docker exec -i ora-burger sqlplus -s dev/dev@localhost/FREEPDB1 < 00_CF_대조.sqlpowershell
Set-Location 10_본편_튜너의_1년\04화_인덱스를_태웠더니_더_느려졌다\실습
Get-Content 00_CF_대조.sql -Encoding UTF8 | docker exec -i ora-burger sqlplus -s dev/dev@localhost/FREEPDB1| 단계 | 무엇 | 기대 | ☐ |
|---|---|---|---|
| [A] | 두 테이블의 정의 비교 | 행수·BLOCKS·BLEVEL·LEAF·DISTINCT_KEYS 전부 같다. CF만 다르다 | ☐ |
| [B] | 같은 SQL · 같은 인덱스 강제 | ORD 1,086 vs ORD_RANDOM 41,825 | ☐ |
| [C] | FULL 강제 | 둘 다 22,23x. ORD_RANDOM은 인덱스가 1.88배 손해 | ☐ |
| [D] | 물리 I/O 요청 | FULL 186회 vs 인덱스 18,934회 | ☐ |
[B]에서 반드시 눈으로 짚을 것
xplan
ORD ORD_RANDOM
| 2 | TABLE ACCESS BY INDEX ROWID | 1086 | | 2 | TABLE ACCESS BY INDEX ROWID | 41825 |
|* 3 | INDEX RANGE SCAN | 160 | |* 3 | INDEX RANGE SCAN | 160 |
☐ 아래 줄(INDEX RANGE SCAN)이 양쪽 다 160 인가? -> 인덱스는 똑같이 싸다
☐ A-Rows 가 양쪽 다 41,666 인가? -> 같은 행을 같은 순서로 읽는다
☐ 그런데 위 줄에서 38.5배가 갈린다 -> 차이는 '테이블에 가는 비용' 하나다"인덱스가 느리다"는 틀린 문장이다. 인덱스는 양쪽 다 160이었다. 느린 것은 인덱스가 가리킨 곳으로 가는 왕복이고, 그 횟수를 정하는 것이 CF다.
STEP 3 — 손익분기점을 직접 찾는다 (10분) ★ 오늘의 산출물 ①
bash
docker exec -i ora-burger sqlplus -s dev/dev@localhost/FREEPDB1 < 01_손익분기_곡선.sql스크립트는 하루부터 2년까지 24개 구간을 두 테이블에서 인덱스 강제 / FULL 강제 / 옵티마이저 자율로 세 번씩 돌려 표 하나로 뽑는다(약 15초).
표에서 찾을 것
☐ ① 「배수」칸이 1.000 을 처음 넘는 줄을 각 테이블에서 찾아 동그라미 쳐라
ORD_RANDOM : 17일 2.28% 배수 1.031 <- 여기
ORD : 626일 85.67% 배수 1.002 <- 여기
☐ ② 두 전환점이 몇 배 차이나는가? -> 39배
☐ ③ 「옵티마이저가 고른 것」칸이 바뀌는 줄도 찾아라. ①과 자리가 다르다
ORD_RANDOM : 31일 -> 46일 사이
ORD : 366일 -> 457일 사이③이 왜 어긋나는가 — 스스로 답해 보고 확인한다
▶ 답
옵티마이저는 실제 선택도가 아니라 자기가 추정한 선택도(E-Rows)로 비용을 계산한다. 이 테이블의 ORD_DT는 VARCHAR2(8)이라 범위 선택도 추정이 크게 틀어져 있다. 한 달 범위에서 E-Rows는 4,065인데 실제는 41,666으로, 10배나 적게 추정했다.
추정 선택도로 환산하면 이론값과 맞는다. 자세한 계산은 2_파고들기.md §5에 있다.
CF가 손익분기점을 정하고,
E-Rows는 옵티마이저를 그 지점까지 데려간다.
STEP 4 — 무엇으로 CF를 고칠 수 있는가 (10분)
bash
docker exec -i ora-burger sqlplus -s dev/dev@localhost/FREEPDB1 < 02_REBUILD_와_재구성.sql작업용 복사본(ORD_MOVE, ORD_SORTED)에서만 작업하고, 끝나면 복사본을 지워 공간을 반납한다(약 35초).
| 시도 | 기대 CF | ☐ | |
|---|---|---|---|
| [A] | REBUILD / REBUILD ONLINE / COALESCE | 999,958 그대로 (세 번 다) | ☐ |
| [B] | ALTER TABLE MOVE (정렬 없음) | 999,958 그대로 + 인덱스 UNUSABLE | ☐ |
| [C] | CTAS ... ORDER BY | 22,223 → Buffers 41,825 → 1,086 | ☐ |
| [D] | 고친 뒤 5만 건 적재 | 키 순서대로 넣으면 23,387, 뒤섞어 넣으면 122,193 (둘 다 실행마다 ±5 안팎) | ☐ |
한 문장으로 설명해 보자 (STEP 7의 예행연습)
"
REBUILD로는 CF가 안 고쳐진다. 왜냐하면 ______________________________이기 때문이다."
▶ 기준 답
CF는 인덱스가 아니라 테이블이 쌓인 순서가 정하는 값이기 때문이다. REBUILD는 인덱스를 다시 만들 뿐, 테이블 블록 안의 행 배치를 건드리지 않는다. CLUSTERING_FACTOR가 USER_INDEXES에 들어 있어서 인덱스 지표처럼 보이지만, 실제로 재는 것은 인덱스 키 순서와 테이블 저장 순서가 어긋난 정도다.
그리고 [D]가 더 중요하다. 고쳐 놓아도 영구하지 않다. 전체의 5%만 뒤섞어 넣어도 CF가 5.2배 나빠진다.
STEP 5 — CF를 피하는 길 · INVISIBLE 절차 (10분) ★ 오늘의 성취
bash
docker exec -i ora-burger sqlplus -s dev/dev@localhost/FREEPDB1 < 03_커버링_INVISIBLE.sql| 무엇 | 기대 | ☐ | |
|---|---|---|---|
| [A-1] | INVISIBLE로 만든 직후 | 41,825 (달라지는 게 없다) | ☐ |
| [A-2] | 그 인덱스를 힌트로 찍어도 | 41,825. INVISIBLE은 힌트보다 강하다 | ☐ |
| [B] | 세션 파라미터만 켜기 | 160 · TABLE ACCESS 줄이 사라진다 | ☐ |
| [C] | 1년 / 2년으로 넓혀도 | 1,891 / 3,800. 뒤집히지 않는다 | ☐ |
| [D] | ☠️ 컬럼 하나 추가 | 41,819. 커버링이 깨지면 CF 청구서가 돌아온다 | ☐ |
| [E] | VISIBLE로 공개 | 세션 파라미터를 꺼도 160 | ☐ |
📌 [B]에서 꼭 확인할 것
ORD_RANDOM_X02의 CF는 999,850으로 여전히 최악이다. 그런데도 160이 나왔다.CF는 테이블에 갈 때만 청구된다. 커버링은 CF를 고친 게 아니라 청구서를 받을 일을 피해 간 것이다. 그래서[D]처럼 커버링이 깨지는 순간 청구서가 그대로 돌아온다.
STEP 6 — 02화가 남긴 숙제를 푼다 (12분) ★ 컨테이너가 바뀐다
bash
docker exec -i ora-hanorder sqlplus -s sqlp/sqlp@localhost/FREEPDB1 < 04_한빛유통_검산.sql
docker exec -i ora-hanorder sqlplus -s sqlp/sqlp@localhost/FREEPDB1 < 05_DML_청구서.sql6-1. 27,466을 손으로 계산해 본다
선택도 = 27,390 / 1,000,000 = 0.02739
① BLEVEL = ______
② LEAF_BLOCKS 2,653 × 0.02739 = ______
③ CF 1,000,000 × 0.02739 = ______
--------
합계 ______ 실측 27,466 과 몇 차이인가? ____☐ 세 항 중 하나가 전체의 99.7%를 차지한다는 것을 확인했다 ☐ 02화 rev02(커버링)에 ③이 없다는 것을 확인했다(2 + 3,206×0.02739 = 90, 실측 91)
6-2. 우리 테이블의 손익분기점
☐ IX_ORD_BIG_DT 경로가 FULL을 넘어서는 줄을 찾았다: 6일(0.55%) ~ 7일(0.64%) 사이, 약 0.58% ☐ 02화에서 잰 2.74%는 그 지점을 5배 넘긴 자리였다는 것을 확인했다 ☐ 커버링 칸은 100%까지 안 뒤집힌다는 것을 확인했다(3년 전체에서도 3,227 < 5,826)
FULL칸에는 5,826이 나온다(±1). 이 테이블에 적재 실험을 한 적이 있으면 HWM 때문에 몇 블록 달라지지만 결론은 바뀌지 않는다(HWM은 05화에서 다룬다).
6-3. 청구서
| 인덱스 | redo size | db block changes | session logical reads |
|---|---|---|---|
| 0개 | 2.44 MB | 1,955 | 2,611 |
| 1개 | 14.13 MB | 102,500 | 153,800 |
| 2개 | 26.10 MB | 201,200 | 302,170 |
☐ 02화에서 조회를 64배 빠르게 만든 대가가 redo 10.7배, 블록 변경 103배라는 것을 숫자로 확인했다 ☐ 롤백했는데도 IX_LOAD_DT가 2,653 → 3,747로 커진 채 남은 것을 확인했다
STEP 7 — T2 승인서 1호 ★ 오늘의 산출물 ②
양식은 T2_인덱스힌트_변경승인서.md를 따른다.
먼저 T1과 무엇이 다른지 짚고 넘어가자.
| T1 리포트 | T2 승인서 | |
|---|---|---|
| 언제 쓰나 | 고치고 나서 | 만들기 전에 |
| 무엇을 말하나 | "내가 이렇게 고쳤습니다" | "이걸 해도 됩니까" |
| 비면 | 불완전 | 반려 |
7-1. 버거원 커버링 인덱스 (T2-001)
아래 빈칸을 채운다. 괄호 안은 오늘 실측한 값이다.
1. 대상 : BURGER1.ORD_RANDOM
제안 DDL : CREATE INDEX ORD_RANDOM_X02 ON ORD_RANDOM(ORD_DT, SHOP_NO) INVISIBLE;
컬럼 순서 근거 : ______________________________________________
접두 중복 : 기존 ORD_RANDOM_X01(ORD_DT, ORD_HMS) 와 선두 컬럼이 겹친다 -> ______
2. 기대 이득 : 월마감 집계 Buffers ______ -> ______ (조회 범위 ______행 = ____%)
손익분기 : 기존 경로 ____% / 제안 경로 ______________
★ 전제하는 컬럼 목록 : ORD_DT, SHOP_NO (여기 없는 컬럼이 추가되면 ______)
3. 비용 : redo ______ -> ______ (5만 건 기준) / 세그먼트 ______MB
DML 블록변경 ______ -> ______
4. 중복·미사용 : DBA_INDEX_USAGE 관찰 ____일
5. 생성 방식 : 소요 실측 ____초 / ONLINE 여부 ______
6. 단계적 적용 : ① INVISIBLE ② 세션 검증 ③ 승인 ④ VISIBLE ⑤ 관찰 ____일 ⑥ 확정
7. 롤백 : 1단계 ____________________ (실측 소요 ____)
2단계 ____________________▶ 채운 뒤에 보는 선배의 첨삭 포인트
① 2번의 "조회 범위"를 비우면 반려다. "261배 개선"은 한 달(4.17%) 범위에서라는 조건이 붙어야 의미 있는 숫자가 된다. 조건 없는 배수는 인상일 뿐이다.
② 2번의 「전제하는 컬럼 목록」이 T2의 핵심이다. 커버링은 그 목록이 바뀌는 순간 근거가 사라진다. 실측에서도 160 → 41,819가 됐다. 이 줄이 없으면 반년 뒤 화면을 한 번 고쳤을 때 아무도 원인을 모르게 된다.
③ 3번의 비용을 "작다"고 쓰면 안 된다.redo 2.44MB → 26.10MB처럼 N→M으로만 쓰고, 그 값이 5만 건 기준이라는 것도 함께 적는다.
④ 7번 롤백의 1단계는 DROP이 아니다.ALTER INDEX ... INVISIBLE;이 먼저다. 초 단위로 되돌릴 수 있고, 판단이 틀렸다고 확인되면 그때 DROP한다. 만드는 건 되돌리기 쉽고 지우는 건 어렵다. T2의 6번과 7번 칸은 이 비대칭 때문에 이렇게 짜여 있다.
7-2. 접두 중복 인덱스 폐기 제안 (T2-002) — 승인 대기로 남긴다
T_ORD_BIG의 IX_ORD_BIG_DT(ORD_DT)는 IX_ORD_BIG_DT_AMT(ORD_DT, ORD_AMT)의 접두부다.
제안 : IX_ORD_BIG_DT 폐기
근거 : 5만 건 적재 기준 redo 26.10MB -> 14.13MB (46% 감소)
Q1 rev02 는 IX_ORD_BIG_DT_AMT 를 쓰므로 영향 없음 (91 유지, 실측)
선결 조건 : T_ORD_BIG 참조 SQL 전수 조사 + DBA_INDEX_USAGE 관찰 7일
롤백 : CREATE INDEX IX_ORD_BIG_DT ON T_ORD_BIG(ORD_DT); -- 실측 1.5초
적용 : 승인 대기 -> 14화☠️ 오늘 실제로 지우지는 않는다. 근거는 충분해 보이지만 "그 테이블을 쓰는 SQL이
Q1뿐인가"를 아직 전수 확인하지 않았다. '명백하다'가 가장 위험한 단어다.
STEP 8 — 서술 답안 (400~600자)
박팀장에게 보낼 버거원 1차 진단 회신을 쓴다. 4단계 프레임(현상/진단 → 원인 → 개선안 → 근거/효과)은 01화와 같다.
▶ 선배의 첨삭 포인트 (쓰고 나서)
① "전임자가 잘못 만들어 놨습니다"는 틀린 보고다. 그 결정은 그때 옳았다. 같은 SQL을 이관 전 테이블(ORD)에서 돌리면 1,086 vs 22,239로 20.5배 개선이다. 틀어진 원인은 1년 전 이관 때 적재 순서가 바뀐 것이다. 사람이 아니라 사건을 쓴다.
② 박팀장이 알아듣는 말로 쓴다. "CF가 999,958입니다"로는 뜻이 전달되지 않는다. "같은 데이터인데 저장 순서가 흐트러져서, 한 번 조회할 때 읽는 양이 1.9배가 됐습니다"라고 옮겨야 한다.
③ 선택지를 비용과 함께 제시한다. ⓐ 힌트 제거(즉시, 위험 없음, 22,234) ⓑ 커버링 인덱스(160, 적재 비용 증가) ⓒ 테이블 재구성(1,086, 중단 필요, 재발 가능). 하나만 쓰면 보고가 아니라 통보다.
④ 재발 조건을 쓴다. 다음에 대량 이관을 하면 또 틀어진다. "이관할 때 ORDER BY를 넣을 것"을 운영 수칙으로 제안한다. 이번 화에서 영구적인 개선은 이것 하나뿐이다.
STEP 9 — 파인만 자가설명
절차는 01화와 같다. 1단 백지 설명 → 2단 막힘 태그(최소 1개) → 3단 원천 복귀 → 4단 재설명 순서로 진행한다.
오늘 자주 나오는 태그 세 가지
| 태그 | 전형적인 문장 |
|---|---|
#인과 | "REBUILD로 왜 CF가 안 고쳐지는지 설명하다 막혔다" → §4 |
#수치 | "손익분기 선택도를 식으로 못 끌어내겠다" → §3의 공식에 직접 대입해 본다 |
#용어 | "싱글블록 읽기와 멀티블록 읽기의 차이를 말로 못 하겠다" → §2 |
4단 되묻기 3문 (오늘 버전)
- 조건 반전: "CF가 좋은데도 인덱스가 지는 경우가 있나?" (힌트: 85.5%)
- 반례 요구: "커버링 인덱스가 CF를 고친 게 아니라는 증거를 하나 든다면?" (힌트: 999,850)
- 측정 지목: "'이관 때문에 느려졌다'를 증명하려면 무엇을 재야 하나?"
태그는 _막힘로그.md로 옮긴다.
완료 체크리스트
- [ ] STEP 1: 보기 전에 예측 4개를 적었다(특히 Q3에 숫자 하나를 적었다)
- [ ] STEP 2:
INDEX RANGE SCAN줄이 양쪽 다 160인 것을 눈으로 짚었다 - [ ] STEP 3: 곡선표에서 1.000을 넘는 첫 줄을 두 테이블에서 각각 찾았다
- [ ] STEP 4:
REBUILD세 가지가 모두 CF를 바꾸지 못하는 것을 봤다 - [ ] STEP 4: 5만 건만 뒤섞어 넣어도 CF가 5.2배 나빠지는 것을 봤다
- [ ] STEP 5:
INVISIBLE이 힌트보다 강하다는 것을 확인했다 - [ ] STEP 5: 커버링이 깨지면 160 → 41,819로 돌아오는 것을 봤다
- [ ] STEP 6: 27,466을 손으로 계산해서 27,465를 얻었다
- [ ] STEP 7: T2-001을 쓰고 「전제하는 컬럼 목록」과 「롤백 1단계」 칸을 채웠다
- [ ] STEP 7: T2-002를 쓰고 실제로는 지우지 않았다
- [ ] STEP 9: 막힘 태그를 최소 1개
_막힘로그.md에 옮겼다
완결성 게이트: STEP 3의 전환점 두 개와 STEP 7의 「전제하는 컬럼 목록」 칸이 비어 있으면 이 화는 미완성이다.
다음 화로 넘기는 상태
| 무엇 | 값 |
|---|---|
ORD_RANDOM 인덱스 | 3개 (_PK, _X01, _X02 ← 04화 산출물) |
| 버거원 조각 현재 리비전 | B1_rev02 · Buffers 160 |
T_ORD_BIG | 건드리지 않았다. 인덱스 2개 그대로 (02화 종료 상태) |
Q1 현재 리비전 | rev02 유지. 범위를 100%까지 넓혀도 뒤집히지 않는 것을 확인했으므로 더 고치지 않는다 |
| 남긴 빚 | 인덱스 2개의 적재 비용 본청구 → 14화 · IX_ORD_BIG_DT 폐기 승인 대기 → 14화 |
| 열어 둔 질문 | 전체 스캔이 왜 BLOCKS와 안 맞나 → 05화 |
다음 → 05화 「블록이라는 그릇」: 오늘은 내내 "블록 수"로 이야기했다. 그런데 그 블록 안에 정확히 무엇이 들어 있는지는 아직 보지 않았다.