테마
06화 · 실습과 기록
여기서 하는 일: 네 번 틀리고, 29글자를 지운다.
1_사건.md를 읽은 것은 "선배가 하는 걸 지켜본 것"이고, 이 문서를 끝내야 직접 해 본 것이 된다. 개념이 막히면2_파고들기.md를 펼친다.시간이 없는 주에는 STEP 0 → 1 → 2 → 6만 한다(약 45분).
STEP 0 — 직전 리비전을 연다 (3분) ★ 매주 처음 하는 일
bash
docker start ora-hanorderbash
cat 리비전/Q2_rev00.sql오늘 고칠 SQL부터 눈으로 확인한다. 01화에서 잰 값은 Buffers 1,026,849였다.
그리고 두 가지를 확인한다.
sql
-- ① 전제: 02화를 마친 상태인가 (T_ORD_BIG 인덱스 2개)
SELECT index_name FROM user_indexes WHERE table_name='T_ORD_BIG';
-- 기대: IX_ORD_BIG_DT, IX_ORD_BIG_DT_AMT
-- ② M_CUS 와 M_RGN 의 크기
SELECT COUNT(*) FROM M_CUS; -- 기대 2000
SELECT COUNT(*) FROM M_RGN; -- 기대 8오늘 나오는 숫자는 모두
M_CUS가 2,000행이라는 사실에서 나온다. 이것부터 확인하고 시작한다.
STEP 1 — 예측을 봉인한다 (4분)
Q2 에는 /*+ LEADING(o c) USE_NL(c) */ 가 못박혀 있고, 그 위에 "건드리지 말 것"이 적혀 있다.
2018-11-06 회의록에는 "등급별 집계 3초 -> USE_NL 적용 후 0.4초. 적용 승인" 이라고 되어 있다.
그때 주문 데이터는 20,000 건이었다. 지금은 1,000,000 건이다.
Q1. M_CUS 는 2,000행 28블록이다. 그런데 Q2 가 M_CUS 에서 읽는 블록은 몇일 것 같은가?
______________
Q2. 힌트를 그냥 지우면 Buffers 가 얼마가 될 것 같은가?
______________
Q3. 2018년 그 시점(20,000건)으로 돌아가면, NL 과 해시 중 어느 쪽이 이길 것 같은가?
______________
Q4. NL 이 해시를 이기는 구간이 있다면, outer 가 몇 건까지일 것 같은가?
______________▶ 적고 나서 펼치기
실측값은 Q1 = 1,000,000, Q2 = 5,869(175배)다.
Q3에는 대부분 "2018년엔 NL이 이겼을 것"이라고 쓴다. 실측해 보면 20,000건에서도 해시가 약 138배 차이로 이긴다. 이 화에서 가장 많이 빗나가는 예측이다.
Q4에는 흔히 "수천 건"이라고 쓴다. 실측값은 20건까지다(두 비용이 같아지는 분기점이 21건).
STEP 2 — Q2를 열고 힌트를 뗀다 (10분) ★ 오늘의 본체
bash
cd 10_본편_튜너의_1년/06화_100만_번의_왕복/실습
docker exec -i ora-hanorder sqlplus -s sqlp/sqlp@localhost/FREEPDB1 < 00_조인_3종.sqlpowershell
Set-Location 10_본편_튜너의_1년\06화_100만_번의_왕복\실습
Get-Content 00_조인_3종.sql -Encoding UTF8 | docker exec -i ora-hanorder sqlplus -s sqlp/sqlp@localhost/FREEPDB1스크립트는 맨 앞에서 임시 객체를 치우고 통계를 다시 수집한다. 그래서 몇 번을 돌려도 결과가 같다. [C]에서 인덱스를 하나 만들지만 그 자리에서 바로 지운다. 끝에는 뒷정리를 확인하는 부분이 붙어 있다.
여덟 단계에서 확인할 것
| 단계 | 무엇 | 기대 | ☐ |
|---|---|---|---|
| [A] | rev00 (힌트 그대로) | NESTED LOOPS · 1,026,849 · inner Starts 1,000,000 | ☐ |
| [B] | 비용 분해 | 5,826 + 21,011 + 1,000,000 + 12 | ☐ |
| [C] | M_CUS 커버링 인덱스 + NL | 36,852(28배) · 그런데 Starts는 그대로 1,000,000 | ☐ |
| [D] | rev01: 힌트 29글자 제거 | HASH JOIN · 5,869 · Used-Mem 뒤 (0) | ☐ |
| [E] | USE_HASH 명시 | plan_hash_value가 [D]와 같다(2292855265) | ☐ |
| [F] | build 뒤집기 | Buffers는 같은데 Used-Mem 1,700K → 77M | ☐ |
| [G] | USE_MERGE | MERGE JOIN · 5,868 · SORT JOIN 두 개 | ☐ |
| [H] | 스칼라 서브쿼리 제거 | 1,026,837 · 12블록 차이 | ☐ |
각 단계에서 눈으로 짚을 것
xplan
[A] — Starts 열을 먼저 보라. Buffers 보다 먼저
| 6 | TABLE ACCESS FULL | T_ORD_BIG | 1 | ... ← outer, 1
|* 7 | INDEX UNIQUE SCAN | PK_M_CUS | 1000K| ... ← inner, 100만
| 8 | TABLE ACCESS BY INDEX ROWID | M_CUS | 1000K| ... ← inner, 100만
☐ outer 의 A-Rows 와 inner 의 Starts 가 같은가? -> 그게 왕복 횟수다
☐ M_CUS 는 2,000행인데 1,000,000 블록을 읽는가?
[C] — 블록은 줄었는데 Starts 는 안 줄었다
☐ TABLE ACCESS BY INDEX ROWID 줄이 사라졌는가? (02화와 같은 장면)
☐ Starts 가 여전히 1,000,000 인가? ← 오늘의 핵심
=> 인덱스는 '왕복 한 번의 비용'을 줄인다. '왕복 횟수'는 조인 방식만 줄인다
[D] — 계획이 통째로 바뀐 것을 확인
☐ NESTED LOOPS -> HASH JOIN 인가?
☐ 모든 Starts 가 1 인가?
☐ 결과 3행(900000 / 90000 / 10000)이 [A] 와 같은가? ← 반드시 확인
☐ OMem / 1Mem / Used-Mem 열이 새로 생겼는가?
☐ Used-Mem 뒤 괄호가 (0) 인가?
[E] — 두 Plan hash value 를 나란히 놓아라
☐ 같은가? -> 같으면 "옵티마이저는 이미 같은 답을 갖고 있었다"
[F] — Buffers 가 아니라 OMem 을 보라
☐ Buffers 는 [D] 와 거의 같은가?
☐ OMem 이 1,423K -> 60M 인가?
=> build 를 잘못 잡으면 블록이 아니라 메모리에 나타난다
[H] — M_RGN 쪽 Starts 를 보라
☐ 100만이 아니라 8 인가?
=> 왜 8인지는 07화. 오늘은 "범인이 아니다" 까지만STEP 1의 예측을 꺼낸다
Q1(M_CUS에서 읽는 블록)을 맞혔나? | |
| Q2(힌트 제거 후)를 맞혔나? | |
| "커버링 인덱스로도 28배는 번다"를 예상했나? | |
"Starts는 인덱스로 안 줄어든다"는? |
STEP 3 — 2018년으로 돌아간다 (10분) ★ 이 화에서 가장 많이 뒤집히는 곳
bash
docker exec -i ora-hanorder sqlplus -s sqlp/sqlp@localhost/FREEPDB1 < 01_2018년_재현.sqlpowershell
Get-Content 01_2018년_재현.sql -Encoding UTF8 | docker exec -i ora-hanorder sqlplus -s sqlp/sqlp@localhost/FREEPDB1T_ORD_BIG에서 N건만 떼어 T_ORD_SZ를 만들고, 같은 조인을 NL과 해시로 각각 돌린다. 끝에서 T_ORD_SZ를 지운다. 06화의 산출물은 아니다.
채점표: 돌리기 전에 승자를 먼저 적어 보자.
N ≥ 2,000 구간의 NL 값은 실행할 때마다 ±0.5% 흔들린다(
T_ORD_SZ를 매번 새로 쌓기 때문이다). 배수는 변하지 않는다.
| outer 행수 | 내 예상 | NL | 해시 | 실측 승자 |
|---|---|---|---|---|
| 10 | 25 | 36 | NL | |
| 20 | 35 | 36 | NL | |
| 25 | 40 | 36 | 해시 | |
| 100 | 115 | 36 | 해시(3.2배) | |
| 2,000 | 2,088 | 45 | 해시(46.4배) | |
| 20,000 ← 2018년 | 20,798 | 151 | 해시(약 138배) | |
| 200,000 | 211,665 | 1,229 | 해시(172.2배) | |
| 1,000,000 ← 지금 | 1,026,837 | 5,856 | 해시(175.3배) |
📌 여기서 서사가 뒤집힌다
"2018년엔 NL이 옳았는데 데이터가 커지면서 틀려졌다"는 틀린 설명이다.2만 건에서도 NL은 약 138배 지고 있었다.
달라진 것은 옳고 그름이 아니라 틀린 대가의 크기다.
2018년 지금 NL의 손해 약 2만 블록 ≈ 10밀리초 약 102만 블록 ≈ 0.5초 10밀리초는 아무도 못 느끼지만, 0.5초는 민원이 된다. 그래서 결론도 달라진다. "그때는 옳았으니 어쩔 수 없다"가 아니라 "주기적으로 다시 재야 한다"가 결론이다.
손익분기점을 손으로 구한다
작은 N 구간의 두 열에서 식을 읽어 낸다.
NL = ____ + N (작은 N 구간에서 1씩 늘어난다)
해시 = ____ (N 과 무관하게 고정이다)
두 식이 만나는 N = ______▶ 답
NL = 15 + N (outer 6블록 + PK 인덱스 9블록 + 건당 1블록)
해시 = 36 (M_CUS 30블록 + outer 6블록)
15 + N = 36 -> N = 2121은 해시의 상수항 36 가운데 30이 M_CUS를 읽는 값이어서 나온 숫자다. inner가 커지면 분기점은 뒤로 밀린다. 외울 것은 숫자가 아니라 식의 모양, 곧 한쪽만 N에 비례한다는 사실이다.
STEP 4 — 책상을 좁혀 본다 (10분 · 실행에 1분 가까이 걸린다)
bash
docker exec -i ora-hanorder sqlplus -s sqlp/sqlp@localhost/FREEPDB1 < 02_책상_좁히기.sqlpowershell
Get-Content 02_책상_좁히기.sql -Encoding UTF8 | docker exec -i ora-hanorder sqlplus -s sqlp/sqlp@localhost/FREEPDB1⚠️ 느려지는 것이 정상이다
[W2]는 약 5~8초,[W3]은 약 22~32초 걸린다. 멈춘 게 아니다. 모두ALTER SESSION으로 바꾸므로 다른 세션에는 영향이 없고, 스크립트 끝에서AUTO로 되돌린다.
| 단계 | 설정 | 기대 | ☐ |
|---|---|---|---|
| [W1] | AUTO (기본값) | Used-Mem 약 1,700K (0) · Used-Tmp 열 없음 | ☐ |
| [W2] | MANUAL · hash_area_size 64KB | Used-Mem 약 250~300K (4) · Used-Tmp 24M | ☐ |
| [W3] | 〃 · 소트 머지 | SORT JOIN이 각각 (1), (100) · Used-Tmp 1M / 25M | ☐ |
☠️ 이 표가 STEP 4의 전부다
Buffers | Reads | Writes | 시간 | |
|---|---|---|---|---|
| [W1] optimal | 5,869 | 0 | 0 | 0.17초 |
| [W2] 4패스 | 5,868 | 11,784 | 2,952 | 5~8초 |
일량은 한 블록도 늘지 않았다. 일하는 장소가 메모리에서 디스크로 바뀌었을 뿐이다.
☐ Buffers 가 거의 안 움직인 것을 확인했는가?
☐ 그런데 시간이 수십 배(실측 28~47배)가 된 것을 확인했는가?
☐ Reads / Writes 가 0 에서 만 단위로 뛴 것을 확인했는가?
☐ V$SQL_WORKAREA 의 LAST_EXECUTION 이 OPTIMAL / 4 PASSES / 100 PASSES 인가?
☐ 마지막에 workarea_size_policy 가 AUTO 로 돌아갔는가?📌 T0 측정규약의 첫 사각지대
01화에서 정한 T0은 "
Buffers로 말한다"이고, 그 규약은 여전히 옳다. 다만 해시 조인이나 정렬이 있는 실행계획에서는Buffers에 찍히지 않는 경로가 하나 있다. 그때는Used-Mem,Used-Tmp,Reads,Writes를 함께 본다. 본격적인 이야기는 09화에서 한다. 오늘은 여기까지만 하고, 결과를 리포트의 「환경 의존성」 칸에 옮겨 적는다.
STEP 5 — 「힌트 유효기간 대장」 1호 ★ 이 교재의 새 표준
양식은 힌트_유효기간_대장.md를 따른다.
먼저 — 2018년 회의록에서 빠진 줄을 찾는다
2018-11-06 성능개선 회의록 안건3
- 현상 : 조회 시 약 3초. 월말에 민원 집중
- 조치 : 조인 방식을 USE_NL 로 고정
- 결과 : 3초 -> 0.4초
- 결론 : 적용 승인이 기록에는 틀린 줄이 하나도 없다. 그런데 몇 년 뒤 이 SQL은 102만 블록을 더 읽고 있었다. 빠진 줄 두 개를 적어 보자.
▶ 답
- "그때 데이터는 20,000건이었다": 전제가 본문에 없다
- "언제 다시 잴 것인가": 재검토 책임이 누구에게도 없다
대장 1호를 채운다
| # | 힌트 | 위치 | 붙인 시점 | 근거 | 그때의 규모 | 그때의 효과 | 현재 규모 | 현재 재측정값 | 판정 | 다음 재검토 조건 |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | USE_NL(c) | PKG_ORD_STLM.Q2 | ||||||||
| 2 | LEADING(o c) | 〃 | ||||||||
| 3 | (힌트 없음 = 해시 자율선택) | Q2 rev01 |
★ 3번 행을 비우면 이 화는 미완성이다
오늘 내린 판단도 똑같이 늙는다.
rev01이 이긴 것은M_CUS가 2,000행(30블록)이고 PGA가 넉넉해서다. 둘 다 변할 수 있다.3번 행에 반드시 들어가야 할 것:
- 전제 ①:
M_CUS2,000행 / 30블록 → 손익분기점이 21건인 근거- 전제 ②:
pga_aggregate_target512M → 해시 조인이optimal(0패스)로 도는 근거- 재검토 조건: 숫자로 적는다. "필요시"라고 쓰면 반려된다
STEP 6 — T1 리포트 4호 ★ 오늘의 산출물
양식은 T1_단건SQL_튜닝리포트.md를 따른다.
┌──────────────────────────────────────────────────────────────────┐
│ T1 단건 SQL 튜닝 리포트 — 4호 │
├──────────────────────────────────────────────────────────────────┤
│ 1. 대상 : PKG_ORD_STLM.Q2 · rev00 → rev01 · diff 첨부 │
│ 2. 측정 환경 : ora-hanorder · 26ai Free 23.26.2.0.0 · PGA 512M │
│ T_ORD_BIG 1,000,000 / M_CUS 2,000 / M_RGN 8 │
│ 통계: FOR ALL COLUMNS SIZE 1 · 히스토그램 없음 │
│ 3. 부하 조건 : 단일 세션 · 1회 실행 ★ 비면 리포트 무효 │
│ 4. 현상 : ______________________________________________ │
│ (계획 전문 + Starts · E/A-Rows · Buffers) │
│ 5. 원인 : ______________________________________________ │
│ [성능고도화 II권 Ch2 01] [tgsql - Joins] │
│ 6. 개선안 : 채택 ______________________________________ │
│ 기각 ______________________________________ │
│ 7. 효과 : Buffers ________ → ________ │
│ 8. 정합성 : ______________________________________________ │
│ 9. 환경 의존성 ★ 오늘 처음으로 쓸 것이 있다 │
│ ______________________________________________ │
│ 10. 리스크 : ______________________________________________ │
│ 11. 롤백 절차: ______________________________________________ │
│ 12. 재측정 : ______________________________________________ │
└──────────────────────────────────────────────────────────────────┘6번 「기각한 대안」에 반드시 들어갈 것
지난 두 화에서는 채택안만 적었다. 오늘은 진짜로 기각한 대안이 있다.
M_CUS(CUS_ID, CUS_GD, RGN_ID)커버링 인덱스 + NL 유지: 실측 1,026,849 → 36,852(28배) 기각 사유: ① 5,869보다 6.3배 비싸다. ②Starts가 여전히 1,000,000이다. ③ 인덱스가 하나 늘어M_CUS의 DML 비용이 커진다. 힌트를 지우는 쪽은 객체를 하나도 만들지 않고도 더 많이 번다.
9번 「환경 의존성」 — 이 화에서 가장 중요한 칸
STEP 4에서 잰 값이 그대로 들어간다.
- 오늘의 175배는 해시 조인이
optimal(0패스)로 돌 때의 값이다 - work area가 모자라면 같은 실행계획이 TEMP를 쓴다. 실측:
Buffers5,869 → 5,868(변화 없음), 시간 0.17초 → 7.7초 - 즉
Buffers만 감시해서는 이 회귀를 잡지 못한다.pga_aggregate_target을 바꾸면 다시 측정해야 한다
8번 「정합성」 — 오늘은 쉽지만 건너뛰면 안 된다
sql
-- rev00 과 rev01 의 결과가 같은가
-- 기대: 양쪽 다 C 900000 / B 90000 / A 1000002화에서 본 것처럼 "빠른데 틀린 코드"가 제일 고약하다. 오늘은 실행계획만 바꿨으니 답이 바뀔 이유가 없지만, "바뀔 이유가 없다"와 "안 바뀌었다"는 다르다.
12번 「재측정 계획」 — 대장과 같은 조건을 쓴다
STEP 5의 3번 행에 적은 재검토 조건을 그대로 옮긴다. 두 문서가 어긋나면 결국 둘 다 지켜지지 않는다.
STEP 7 — 서술 답안 (400~600자)
박팀장에게 보낼 회신을 쓴다. 조민우 씨가 말한 "등급별 집계 화면" 건에 답하는 글이다.
4단계 프레임(현상/진단 → 원인 → 개선안 → 근거/효과)은 01화 3_실습과_기록.md와 같다.
▶ 선배의 첨삭 포인트 (쓰고 나서)
① "전임자가 잘못 넣은 힌트였습니다"는 틀린 보고다. 2018년에 민원이 있었고, 조치했고, 회의에서 승인받았고, 기록도 남겼다. 절차는 다 지켜졌다. 빠진 것은 전제 기록과 재검토 주기다. 사람을 탓하는 보고로는 같은 일이 또 생긴다.
② "175배"를 자랑하기 전에 조건을 쓴다. "M_CUS가 2,000행이고 PGA가 넉넉한 조건에서"가 빠지면 과장이다.
③ 박팀장이 알아듣는 말로 쓴다. "Buffers 1,026,849 → 5,869"로는 뜻이 전달되지 않는다. "같은 화면을 띄우는 데 읽는 데이터가 175분의 1로 줄었습니다"라고 옮겨야 한다.
④ 재발 방지를 같이 쓴다. 이 화의 산출물은 숫자가 아니라 대장이다. "같은 일이 또 생기지 않도록, 힌트마다 언제 어떤 데이터 크기에서 넣었는지와 재검토 시점을 기록하는 대장을 만들었습니다."
⑤ 아직 안 끝났다는 것도 쓴다. 이 화면은 여전히 100만 행을 읽어 3행을 만든다. 그게 맞는 요구인지는 아직 묻지 않았다(→ 08화).
STEP 8 — 파인만 자가설명
절차는 01화와 같다. 1단 백지 설명 → 2단 막힘 태그(최소 1개) → 3단 원천 복귀 → 4단 재설명 순서로 진행한다.
오늘 자주 나오는 태그 세 가지
| 태그 | 전형적인 문장 |
|---|---|
#인과 | "Starts가 왜 인덱스로 안 줄어드는지 설명하다가 막혔다" → 2_파고들기.md §2 |
#용어 | "build와 probe 중 어느 쪽이 LEADING의 첫 테이블인지 헷갈린다" → §4 |
#수치 | "손익분기점 21이 어디서 나온 건지 계산이 안 된다" → §5의 두 식에 직접 대입해 본다 |
4단 되묻기 3문 (오늘 버전)
- 조건 반전: "
M_CUS가 2,000행이 아니라 200만 행이면rev01은 어떻게 되나?" - 반례 요구: "해시 조인이 NL보다 나쁜 경우를 실제 숫자로 하나 들 수 있나?"
- 측정 지목: "'PGA 때문에 느려졌다'를 증명하려면 무엇을 재야 하나?"
태그는 _막힘로그.md로 옮긴다.
완료 체크리스트
- [ ] STEP 0:
Q2_rev00.sql을 열어 봤고,M_CUS가 2,000행인 것을 확인했다 - [ ] STEP 1: 보기 전에 예측 4개를 적었다
- [ ] STEP 2: [A]~[H]를 돌리고 5,869를 확인했다
- [ ] STEP 2: [C]에서
Starts가 줄지 않은 것을 눈으로 짚었다 - [ ] STEP 2: [D]와 [E]의
plan_hash_value가 같은 것을 확인했다 - [ ] STEP 3: 2만 건에서도 해시가 이긴다는 것을 직접 봤다
- [ ] STEP 3: 손익분기점 식을 손으로 세워 21을 구했다
- [ ] STEP 4:
Buffers는 그대로인데 시간이 터지는 것을 봤고,AUTO로 되돌렸다 - [ ] STEP 5: 대장 1호의 3번 행(내 판단의 유효기간)을 채웠다
- [ ] STEP 6: T1 4호를 썼고, 「기각한 대안」과 「환경 의존성」 칸을 채웠다
- [ ] STEP 7: 박팀장 회신을 문장으로 풀어 썼다
- [ ] STEP 8: 막힘 태그를 최소 1개
_막힘로그.md에 옮겼다
완결성 게이트: STEP 5의 3번 행과 STEP 6의 9번 칸이 비어 있으면 이 화는 미완성이다. 이 화의 진짜 산출물은 175배가 아니라 "내 판단에도 유효기간이 있다"는 사실을 문서로 남긴 것이다.
다음 화로 넘기는 상태
| 무엇 | 값 |
|---|---|
T_ORD_BIG 인덱스 | 2개(IX_ORD_BIG_DT, IX_ORD_BIG_DT_AMT). 06화에서는 아무것도 추가하지 않았다 |
M_CUS 인덱스 | PK_M_CUS 하나. [C]의 커버링 인덱스는 그 자리에서 지웠다 |
Q1 현재 리비전 | rev02 · Buffers 91 |
Q2 현재 리비전 | rev01 · Buffers 5,869 |
| 임시 객체 | 없음(T_ORD_SZ는 스크립트가 지운다) |
| 세션 파라미터 | workarea_size_policy = AUTO (복구 완료) |
| 남긴 빚 | 인덱스 2개의 적재 비용 → 14화 |
| 열어 둔 질문 | 스칼라 서브쿼리 캐싱은 언제 깨지나 → 07화 · Used-Tmp → 09화 |
다음 → 07화 「히트율 99퍼센트인데 서버가 탄다」: 오늘 남겨 둔 스칼라 서브쿼리를 연다. M_RGN 쪽 Starts가 8이었던 이유와, 그게 8이 아닐 때 무슨 일이 생기는지를 본다.