테마
06화 · 100만 번의 왕복
4월 8일 월요일, 드디어 Q2를 연다. 01화에서 전체 부하의 98%를 차지한 바로 그 조각이다. 오늘은 네 번 틀린다. 그리고 마지막에 지우는 것은 29글자다.
오전 9시 00분
지난주 금요일, 박팀장에게서 메신저가 왔다.
박팀장 금 17:40
Q1 건은 잘 봤습니다. 64분의 1이라니 놀랍네요.
그런데 조민우 씨가 말하는 "등급별 집계 화면" 은 아직 그대로라고 합니다.
그게 제일 느린 거 아니었나요?맞는 말이었다. 01화 원장에서 Q2 하나만 1,026,849였다. 나머지 넷을 다 합쳐도 18,480이다.
2주 동안 5,827짜리를 붙들고 있었던 셈이다. 이제야 알 것 같았다. Q1은 연습이었다.
소스를 열었다. 패키지에서 Q2 부분은 이렇게 생겼다.
sql
-- 건드리지 말 것 (2018.11 강도현)
SELECT /*+ LEADING(o c) USE_NL(c) */
c.CUS_GD,
COUNT(*) AS ord_cnt,
SUM(o.ORD_AMT) AS amt,
MAX((SELECT r.RGN_NM FROM M_RGN r WHERE r.RGN_ID = c.RGN_ID)) AS rgn_sample
FROM T_ORD_BIG o, M_CUS c
WHERE c.CUS_ID = o.CUS_ID
GROUP BY c.CUS_GD;-- 건드리지 말 것.
01화에서 패키지 맨 위에서 본 바로 그 주석이었다. 그때는 패키지 전체에 붙어 있었는데, 여기서는 이 SQL 하나에만 붙어 있었다.
선배가 커피를 들고 지나가다 화면을 봤다. "아, 그거 열었구나."
"열었는데… 건드리지 말라고 적혀 있어서요."
"그래서 안 건드릴 거야?"
"그건 아닌데요." 잠깐 생각하다가 말했다. "일단 재 보려고요."
"좋아." 선배가 의자를 끌어왔다. "그게 맞는 순서야."
오전 9시 25분 — 2주 전과 똑같았다
먼저 리비전부터 남겼다. Q2_rev00.sql로 저장하고 돌렸다.
sql
SELECT /*+ GATHER_PLAN_STATISTICS LEADING(o c) USE_NL(c) */
c.CUS_GD, COUNT(*) AS ord_cnt, SUM(o.ORD_AMT) AS amt,
MAX((SELECT r.RGN_NM FROM M_RGN r WHERE r.RGN_ID = c.RGN_ID)) AS rgn_sample
FROM T_ORD_BIG o, M_CUS c
WHERE c.CUS_ID = o.CUS_ID
GROUP BY c.CUS_GD;CUS_GD ORD_CNT AMT RGN_SAMPLE
------ --------- ------------ ----------
C 900000 2.2989E+11 ULSAN
B 90000 2.2949E+10 ULSAN
A 10000 2564322100 SEOUL결과는 세 줄이었고, Buffers는 1,026,849였다. 01화 원장과 숫자 하나 다르지 않았다.
"선배님, 2주 전이랑 똑같은데요. 02화에서 T_ORD_BIG에 인덱스를 두 개나 만들었는데 Q2는 한 블록도 안 바뀌었어요."
"그럼 거기서 뭘 알 수 있어?"
나는 잠깐 멈췄다. "…제가 만든 인덱스가 Q2랑은 아무 상관이 없다는 거요."
"조금 더 세게 말해 봐."
"Q2가 느린 이유는 T_ORD_BIG 쪽에 없다는 거요."
"그게 오늘의 출발점이야."
오전 9시 50분 — 내 첫 번째 답
실행계획을 띄웠다.
xplan
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.65 | 1026K|
| 1 | TABLE ACCESS BY INDEX ROWID | M_RGN | 8 | 1 | 8 |00:00:00.01 | 12 |
|* 2 | INDEX UNIQUE SCAN | PK_M_RGN | 8 | 1 | 8 |00:00:00.01 | 4 |
| 3 | HASH GROUP BY | | 1 | 3 | 3 |00:00:00.65 | 1026K|
| 4 | NESTED LOOPS | | 1 | 1000K| 1000K|00:00:00.63 | 1026K|
| 5 | NESTED LOOPS | | 1 | 1000K| 1000K|00:00:00.31 | 26837 |
| 6 | TABLE ACCESS FULL | T_ORD_BIG | 1 | 1000K| 1000K|00:00:00.05 | 5826 |
|* 7 | INDEX UNIQUE SCAN | PK_M_CUS | 1000K| 1 | 1000K|00:00:00.15 | 21011 |
| 8 | TABLE ACCESS BY INDEX ROWID | M_CUS | 1000K| 1 | 1000K|00:00:00.21 | 1000K|
-----------------------------------------------------------------------------------------------------먼저 눈에 들어온 건 M_CUS 줄의 1000K였다. 100만 블록이었다.
"M_CUS가 100만 블록이에요. 근데 M_CUS는…"
sql
SELECT COUNT(*) FROM M_CUS; -- 20002,000행이었다. 블록으로 치면 28개다.
"28블록짜리 테이블에서 100만 블록을 읽는데요."
"그래. 어떻게 하면 그럴 수 있을까?"
02화에서 배운 대로 답했다. "02화에서 TABLE ACCESS BY INDEX ROWID가 비싸다는 걸 봤잖아요. CUS_ID에 CUS_GD랑 RGN_ID를 붙여서 커버링 인덱스를 만들면…"
"만들어 봐."
오전 10시 05분 — 되긴 됐다
sql
CREATE INDEX IX_M_CUS_COVER ON M_CUS(CUS_ID, CUS_GD, RGN_ID);그리고 같은 SQL을 돌렸다.
xplan
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.49 | 36852 |
| 4 | NESTED LOOPS | | 1 | 1000K| 1000K|00:00:00.50 | 36840 |
| 5 | TABLE ACCESS FULL | T_ORD_BIG | 1 | 1000K| 1000K|00:00:00.09 | 5827 |
|* 6 | INDEX RANGE SCAN | IX_M_CUS_COVER | 1000K| 1 | 1000K|00:00:00.27 | 31013 |
--------------------------------------------------------------------------------------------------------TABLE ACCESS BY INDEX ROWID 줄이 사라졌다. 02화에서 본 장면 그대로였다.
1,026,849 → 36,852. 28배.
좀 우쭐해졌다. "됐네요. 28배요."
선배는 그 숫자에는 눈길도 주지 않고 다른 열을 가리켰다. "Starts 봐."
1,000,000. 그대로였다.
"아까도 100만, 지금도 100만이야. 너 뭘 줄인 거야?"
숫자를 다시 봤다. 블록은 28배 줄었는데 Starts는 하나도 줄지 않았다.
"…한 번 갈 때 드는 비용은 줄였는데, 가는 횟수는 안 줄였네요."
"그게 오늘의 사건이야." 선배가 몸을 바로 세웠다. "28배 벌었다고 좋아하기 전에, 저 숫자가 왜 백만인지부터 알아야 돼. 그걸 모르고 인덱스를 만들면 다음에도 또 인덱스부터 만들게 돼."
나는 방금 만든 인덱스를 지웠다.
sql
DROP INDEX IX_M_CUS_COVER;오전 10시 40분 — 4층
"잠깐 나와 봐."
선배가 프린터에서 종이를 한 장 집어 들었다. 거래처 ID와 금액이 찍힌 주문 전표 양식으로, 스무 줄짜리였다.
"4층 자료실에 거래처 카드함이 있어. 카드 2,000장이 ID 순으로 꽂혀 있지." 선배가 종이를 내밀었다. "여기 전표 스무 장, 하나하나 거래처 등급을 알아 와. 등급은 카드에만 적혀 있어."
종이를 들고 4층으로 내려가 카드함을 열었다. ID 순이라 찾기는 쉬웠다. 02화에서 본 출입대장과 같은 구조였다. 첫 번째 ID를 찾아 등급을 적고 7층으로 올라왔다.
"한 장 했어?" 선배가 쳐다봤다.
"…아."
다시 내려갔다. 두 번째를 찾고 올라왔다. 세 번째도 그렇게 했다. 네 번째쯤에서 멈췄다.
"선배님. 이거 카드함을 통째로 갖고 올라오면 되잖아요. 계단이 열여섯 번 더 남았어요."
"그럼 그렇게 해 봐."
4층에서 카드 2,000장을 복사기에 넣고 복사본을 들고 올라왔다. 그리고 내 책상 위에 등급별로 펼쳐 놓았다. 전표 스무 장을 한 장씩 넘기면서 책상에서 바로 집으니 2분도 안 걸렸다.
선배가 두 방식을 손가락으로 짚었다.
"방금 네가 두 가지를 했어. 첫 번째는 전표 한 장마다 4층에 다녀오기. 오라클에선 이걸 Nested Loops 조인이라고 해. 바깥 묶음(전표) 한 건마다 안쪽(카드함)을 한 번씩 연다는 뜻이야. Starts가 그 왕복 횟수고."
"두 번째는요?"
"카드함을 한 번에 복사해서 책상에 펼치기. 그게 해시 조인이야. 안쪽을 통째로 읽어서 내 자리에 찾기 좋은 모양으로 쌓아 두고(이걸 build라고 해), 바깥 묶음을 흘려보내면서 책상에서 집는 거야(probe)."
책상 위에 쌓인 복사본 더미를 봤다. "그럼 지금 Q2는…"
"전표가 100만 장이야. 그런데 강도현 씨가 거기에 '한 장마다 4층에 다녀와라'라고 못을 박아 놨어."
sql
/*+ LEADING(o c) USE_NL(c) */"LEADING(o c)는 '전표부터 들어라', USE_NL(c)는 '카드는 왕복해서 찾아라'야. 둘을 합치면 100만 번 왕복이 되는 거지."
오전 11시 10분 — 그럼 왜 그래 놨나
자리로 돌아왔다. 답은 뻔해 보였다. 힌트를 지우면 된다.
커서를 힌트 위에 올렸다가, 그 위에 적힌 -- 건드리지 말 것을 보고 손이 멈췄다.
"선배님. 지워도 돼요?"
"지금 왜 망설여?"
"…2년 전에 퇴사한 사람이 '건드리지 말라'고 적어 둔 거라서요. 이유를 모르니까요."
선배가 고개를 끄덕였다. "망설이는 게 맞아. 그냥 지웠다가 장애 나는 것보다 백 배 낫지." 그리고 한마디 덧붙였다. "근데 망설이기만 하다간 또 몇 해가 가. 이유를 찾아."
"어디서요?"
"커밋 로그. 그리고 회의록."
오전 11시 40분 — 2018년 11월 6일
커밋 로그에는 한 줄밖에 없었다.
2018-11-06 hd.kang
등급별 집계 성능개선. 회의 승인건."회의 승인건"이라는 말이 걸려서 공유 드라이브의 옛날 회의록 폴더를 뒤졌다. 20181106_성능개선회의.pdf가 나왔다. 안건 네 개 가운데 세 번째가 그거였다.
3. 등급별 매출 집계 화면 지연 (운영팀 제기)
- 현상 : 조회 시 약 3초. 월말에 민원 집중
- 조치 : 조인 방식을 USE_NL 로 고정
- 결과 : 3초 -> 0.4초
- 결론 : 적용 승인3초 → 0.4초. 7.5배다. 승인받을 만했다.
화면이 3초 걸려 민원이 들어왔고, 누군가 조치를 했고, 0.4초가 됐고, 회의에서 승인했고, 그래서 "건드리지 말 것"이 붙었다. 다들 할 일을 했다.
"선배님, 이거 그때는 맞았던 거네요."
"그런 것 같지. 그럼 그 회의록에 빠진 줄이 뭘까?"
PDF를 다시 읽었다. 현상, 조치, 결과, 결론까지 빠진 게 없어 보였다. "모르겠는데요."
"'그때 데이터가 몇 건이었나'가 없어." 선배가 말했다. "저 0.4초가 몇 건에서 나온 0.4초인지 아무도 안 적었어. 그래서 그 뒤로 아무도 다시 안 쟀고."
T_ORD의 옛날 크기를 찾아봤다. 2018년이면 지금의 T_ORD보다도 작았을 것이다. 회의록 첨부 자료 끝에 한 줄이 있었다.
※ 측정 조건 : 운영 DB 복제본, 주문 20,000 건2만 건.
지금은 100만 건이다. 50배다.
⛳ 오늘은 여기까지(중단 지점 1) 여기까지가 오늘의 절반이다. 왜 100만 번을 왕복하는지, 왜 아무도 손대지 않았는지를 봤다. 이어서 읽으면 2018년을 재현한다. 그리고 방금 납득한 이야기가 뒤집힌다.
오후 1시 20분 — 2018년을 재현한다
점심을 먹고 와서 선배에게 말했다.
"이해했어요. 2만 건에서는 NL이 맞았는데, 100만 건이 되면서 틀린 거죠. 힌트 유효기간이 지난 거요."
"그럴듯하네. 근데 그거 잰 거야, 믿는 거야?"
"…믿는 거요."
"그럼 재." 선배가 말했다. "2018년은 재현할 수 있어. 데이터를 2만 건으로 줄이면 돼."
sql
CREATE TABLE T_ORD_SZ AS SELECT * FROM T_ORD_BIG WHERE 1 = 0; -- 빈 껍데기
INSERT INTO T_ORD_SZ SELECT * FROM T_ORD_BIG WHERE ORD_SEQ <= 20000;2만 건짜리 주문 테이블을 만들고 통계를 수집한 뒤, 같은 조인을 NL로 한 번, 해시로 한 번 돌렸다.
💡 실습 스크립트는 크기를 바꿀 때 테이블을 다시 만들지 않고 행을 쌓아 올린다. 큰 데서 지워 가며 줄이면 지운 자리가 그대로 남아서, 작은 크기에서도 전체 스캔이 옛 블록까지 읽기 때문이다. 그 "남은 자리"가 무엇인지는 05화에서 봤다.
NL : Buffers 20,798
HASH: Buffers 151나는 숫자를 두 번 봤다.
2만 건에서도 해시가 이겼다. 그것도 138배 차이였다.
"……선배님."
"응."
"2018년에도 NL이 지고 있었는데요."
오후 1시 50분 — 그럼 회의록은 거짓말인가
회의록에는 분명히 3초가 0.4초가 됐다고 적혀 있었다.
"회의록이 틀린 거예요?"
"아니. 저 사람들은 초를 잰 거야. 그건 사실이었겠지." 선배가 말했다. "2018년 그 서버, 그 버전, 그 통계 상태에서 옵티마이저가 뭘 골랐는지 우리는 몰라. 지금 재현할 수 있는 건 데이터 크기뿐이야."
"그럼 지금 확인한 건 뭐예요?"
"2만 건에서 NL로 얼마나 손해를 봤는지." 선배가 두 숫자를 가리켰다. "20,798에서 151을 빼면 2만 블록이야. 시간으로는?"
실측값은 이미 있었다. NL이 12.6밀리초, 해시가 2.4밀리초였다.
"…10밀리초쯤이요."
"10밀리초를 누가 느껴?"
나는 아무 말도 못 했다.
"그게 핵심이야." 선배가 말했다. "2018년에 그 힌트는 틀렸지만 손해가 안 보였어. 그래서 아무도 안 건드렸고, 데이터가 50배로 느는 동안 손해도 같이 커진 거야. 10밀리초가 0.5초가 될 때까지."
그러고는 한마디를 보탰다.
"유효기간이 지난 게 아니야. 처음부터 틀렸는데 그 대가가 커진 거야. 이 둘은 달라. 앞의 건 '그때는 옳았으니 어쩔 수 없다'로 끝나고, 뒤의 건 '다시 재야 한다'로 이어져."
나는 그 차이를 수첩에 적었다.
오후 2시 10분 — 그럼 NL은 쓰면 안 되는가
"그럼 NL은 그냥 나쁜 거네요. 항상 해시가 이기면…"
선배가 말을 잘랐다. "또 믿으려고 하네. 재 봐."
"어떻게요?"
"아침에 네가 4층에 몇 번 갔어?"
"네 번이요."
"전표가 세 장이었으면 어떻게 했을 거야? 그래도 카드 2,000장을 복사했을까?"
아니라고 답했다. 세 장이면 세 번 다녀오는 게 훨씬 빠르다. 복사기 앞에 서 있는 시간이 더 길 테니까.
"그럼 몇 장부터 복사하는 게 이득이야? 그게 오늘 네가 재야 할 숫자야."
아침에 만든 스크립트를 고쳐서 크기를 차례로 바꿔 가며 쟀다. 10건, 20건, 25건, 100건, 2천 건, 2만 건, 20만 건이었다.
OUTER_ROWS NL_BUFFERS HJ_BUFFERS VERDICT NL_OVER_HJ
10 25 36 NL 승 .7
20 35 36 NL 승 1
25 40 36 HASH 승 1.1
100 115 36 HASH 승 3.2
2000 2088 45 HASH 승 46.4
20000 20798 151 HASH 승 137.7
200000 211665 1229 HASH 승 172.2NL이 이기는 구간이 있었다. 20건까지였다.
두 열은 성질이 완전히 달랐다.
| 식 | 성질 | |
|---|---|---|
| NL | 15 + N | N에 비례해서 커진다 |
| 해시 | 36(작을 때) | 거의 고정이다 |
"두 식이 만나는 데가 어디야?"
15 + N = 36 → N = 2121건.
"그게 오늘 네가 얻은 숫자야." 선배가 말했다. "21에서 둘이 같아져. 그 아래로 스무 건까지는 NL이 이겨. NL이 나쁜 게 아니라, NL이 이기는 구간이 스무 건 남짓일 뿐이야. 강도현 씨가 붙인 힌트는 2만 건짜리 쿼리에 스무 건짜리 전략을 쓴 거고."
"그럼 2018년에도…"
"그때 이미 138배 지고 있었어. 다만 손해가 2만 블록, 10밀리초라 아무도 몰랐던 거지."
💡 21이라는 숫자는 이 조인에만 들어맞는다. 해시의 상수항 36 가운데 30이
M_CUS를 한 번 훑는 값이다. 그래서 안쪽 테이블이 커지면 손익분기점도 그만큼 뒤로 밀린다. 공식은 외우되 숫자는 매번 다시 재야 한다. 자세한 내용은2_파고들기.md §5에 있다.
오후 2시 40분 — 29글자
이제 지울 차례였다.
Q2_rev00.sql을 복사해 Q2_rev01.sql을 만들고, 힌트와 그 위의 주석을 지웠다. diff는 두 줄이었다.
diff
- -- 건드리지 말 것 (2018.11 강도현)
- SELECT /*+ LEADING(o c) USE_NL(c) */
+ SELECT
c.CUS_GD,SELECT 아래로는 한 글자도 건드리지 않았다. FROM도, WHERE도, GROUP BY도, 스칼라 서브쿼리도 그대로였다.
돌렸다.
xplan
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | Used-Mem |
| 0 | SELECT STATEMENT | | 1 | 3 |00:00:00.17 | 5869 | | |
| 1 | TABLE ACCESS BY INDEX ROWID| M_RGN | 8 | 8 |00:00:00.01 | 12 | | |
|* 2 | INDEX UNIQUE SCAN | PK_M_RGN | 8 | 8 |00:00:00.01 | 4 | | |
| 3 | HASH GROUP BY | | 1 | 3 |00:00:00.17 | 5869 | 1048K| |
|* 4 | HASH JOIN | | 1 | 1000K|00:00:00.20 | 5857 | 1423K| 1679K (0)|
| 5 | TABLE ACCESS FULL | M_CUS | 1 | 2000 |00:00:00.01 | 30 | | |
| 6 | TABLE ACCESS FULL | T_ORD_BIG | 1 | 1000K|00:00:00.09 | 5827 | | |5,869.
NESTED LOOPS가 HASH JOIN으로 바뀌었고, Starts는 전부 1이 됐다. 100만 번이 한 번으로 줄었다.
1,026,849 → 5,869. 175배.
결과 세 줄이 같은지부터 확인했다. 건수 900000 / 90000 / 10000에 금액까지 모두 같았다.
"됐네." 선배가 짧게 말했다. 02화 때와 같은 말이었다.
그리고 바로 물었다. "그런데 하나 확인해 봐. 네가 힌트를 지운 거지, 바꾼 게 아니잖아. 그럼 오라클이 알아서 해시를 고른 거야?"
이번엔 반대로, 해시를 명시하는 힌트를 넣어 봤다.
sql
/*+ LEADING(c o) USE_HASH(o) */xplan
Plan hash value: 2292855265 ← 힌트 명시
Plan hash value: 2292855265 ← 힌트 없음같았다.
"이게 무슨 뜻이야?"
나는 천천히 말했다. "오라클은… 처음부터 해시를 고르고 싶어 했네요."
"그래." 선배가 말했다. "그 몇 해 동안 그 답이 내내 힌트에 막혀 있었던 거야. 네가 한 건 길을 낸 게 아니라 막아 둔 걸 치운 거야."
오후 3시 05분 — 셋째 방식
"방법이 둘뿐이에요?"
"하나 더 있어. 해 봐."
sql
/*+ LEADING(c o) USE_MERGE(o) */xplan
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Used-Mem |
| 0 | SELECT STATEMENT | | 1 | 3 |00:00:00.18 | 5868 | |
| 4 | MERGE JOIN | | 1 | 1000K|00:00:00.24 | 5856 | |
| 5 | SORT JOIN | | 1 | 2000 |00:00:00.01 | 30 |96256 (0)|
| 6 | TABLE ACCESS FULL | M_CUS | 1 | 2000 |00:00:00.01 | 30 | |
|* 7 | SORT JOIN | | 2000 | 1000K|00:00:00.18 | 5826 | 32M (0)|
| 8 | TABLE ACCESS FULL | T_ORD_BIG | 1 | 1000K|00:00:00.05 | 5826 | |5,868. 해시와 거의 같았다.
"이건 뭘 한 거예요?"
"양쪽을 다 정렬해서 나란히 놓고, 지퍼처럼 맞춰 내려가는 거야." 선배가 손으로 두 줄을 그렸다. "전표를 거래처 ID 순으로 정리하면 카드도 ID 순이니까, 둘을 나란히 놓고 위에서부터 같이 내려가는 거지. 한쪽이 앞서면 그쪽만 한 칸 내리면 돼."
아래쪽 SORT JOIN의 Starts가 2,000인 게 마음에 걸렸다.
"여기 2,000이에요. 또 왕복인가요?"
"아니. 그건 읽던 자리를 되감은 횟수야. 같은 거래처 카드에 전표가 여러 장 붙으면 그 자리를 다시 봐야 하니까. 4층에 다시 가는 게 아니라, 이미 책상에 놓인 줄에서 손가락만 위로 올리는 거야. 그래서 비싸지 않아."
선배가 정리해 줬다.
"셋이 달라 보이지? 사실은 한 질문에 세 가지로 답한 거야. 질문은 이거야. 바깥 한 건에 맞는 안쪽을 어디서 찾을 것인가."
| 방식 | 안쪽을 어디서 찾나 | 비용의 성질 |
|---|---|---|
| NL | 매번 원래 자리로 가서 | 바깥 건수 N에 비례 |
| 해시 | 내 자리에 복사해 둔 더미에서 | 안쪽을 한 번 읽는 값 + 바깥 한 번 |
| 소트 머지 | 양쪽을 줄 세워 나란히 놓고 | 양쪽 정렬 비용 + 한 번씩 훑기 |
"세 개를 외우려고 하지 마. '어디서 찾느냐' 하나만 기억하면 나머지는 따라와."
오후 3시 30분 — 책상은 어디 있나
rev01 실행계획에 새로 생긴 열 세 개가 계속 눈에 걸렸다.
| OMem | 1Mem | Used-Mem |
| 1423K| 1423K| 1679K (0)|"이거 뭐예요? Q1 때는 없던 열인데요."
"해시 조인이 생겨서 나타난 거야." 선배가 말했다. "아까 네가 카드 복사본을 어디에 펼쳤지?"
"제 책상이요."
"그 책상이 오라클에서는 PGA야. 프로세스 전용 메모리지. 네 자리니까 남이 안 봐. 그래서 자물쇠를 안 채워도 돼."
"자물쇠요?"
"버퍼 캐시는 모두가 같이 쓰는 공용 캐비닛이라, 꺼낼 때마다 '지금 내가 쓴다'고 표시를 해야 돼. 그게 래치야. PGA엔 그게 없어. 그래서 같은 데이터라도 PGA에서 읽는 게 싸."
💡 래치와 버퍼 캐시의 내부는 07화에서 다룬다. 오늘은 "내 책상에는 자물쇠가 없다"까지만 알아 둔다.
"그럼 이 세 숫자는요?"
선배가 하나씩 짚었다.
| 열 | 뜻 | 책상으로 치면 |
|---|---|---|
OMem | 전부 메모리에서 끝내려면 얼마가 필요한가 | 다 펼치려면 책상이 이만큼 |
1Mem | 한 번만 내렸다 올리는 걸 허용하면 얼마면 되나 | 절반만 펼치려면 이만큼 |
Used-Mem | 실제로 쓴 양. 괄호 안은 패스 수 | 실제로 쓴 책상 넓이 |
"괄호 안이 0이지? 0패스. 한 번도 안 내려놨다는 뜻이야. 이걸 optimal이라고 해."
V$SQL_WORKAREA로도 확인해 봤다.
OPERATION_TYPE POLICY EST_OPT EST_1P LAST_MEM LAST_EXEC
------------------ ------- ---------- ---------- ---------- ----------
HASH-JOIN AUTO 1457152 1457152 1703936 OPTIMALOPTIMAL. 실행계획에서 본 것과 같았다.
⛳ 오늘은 여기까지(중단 지점 2)
Q2_rev01이 나왔고 1,026,849 → 5,869라는 숫자를 얻었다. 오늘 할 몫은 여기까지만 해도 다 했다. 이어지는 오후에는 선배가 이 성과가 무엇에 기대고 있는지 보여 준다. 오늘의 산출물도 거기서 나온다.
오후 4시 10분 — 책상을 좁혀 본다
"그 optimal 말인데." 선배가 다시 찾아왔다. "그거 공짜야?"
"공짜 아니에요?"
"책상이 좁으면 어떻게 돼?"
아침 일을 떠올렸다. 카드 2,000장을 책상에 펼쳤을 때 겨우 다 들어갔다. 책상이 절반만 했다면 일부를 바닥에 내려놨다가 다시 올려야 했을 것이다.
"…내려놨다 올려야죠."
"해 봐. 책상을 64KB로 줄여."
sql
ALTER SESSION SET workarea_size_policy = MANUAL;
ALTER SESSION SET hash_area_size = 65536;그리고 일부러 큰 쪽(100만 행)으로 해시 테이블을 만들게 했다.
xplan
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Reads | Writes | OMem | Used-Mem | Used-Tmp|
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT| | 1 | 3 |00:00:07.68 | 5868 | 11784 | 2952 | | | |
|* 4 | HASH JOIN | | 1 | 1000K|00:00:07.61 | 5856 | 11784 | 2952 | 60M| 244K (4)| 24M|
----------------------------------------------------------------------------------------------------------------------------새 열이 하나 더 생겼다. Used-Tmp 24M. 그리고 괄호 안 숫자는 4였다.
"4패스." 선배가 말했다. "네 번 내렸다 올렸어. 그리고 내려놓은 데가 TEMP야. 디스크지."
Buffers를 보다가 멈칫했다.
optimal : Buffers 5,869 A-Time 0.17초
4 패스 : Buffers 5,868 A-Time 7.68초 <- 재면 5~8초 사이를 오간다블록은 한 개도 늘지 않았다. 시간만 수십 배로 늘었다(실측 28~47배, 초는 잴 때마다 흔들린다).
"……."
"이제 알겠어?" 선배가 말했다. "T0 측정규약은 'Buffers로 말한다'야. 그게 맞아. 근데 그 규약으로는 안 보이는 길이 하나 있어."
Reads와 Writes 열을 봤다. 0이던 값이 각각 11,784와 2,952가 돼 있었다.
"여기 있네요. 블록이 아니라 읽기/쓰기요."
"그래. 일량은 그대로인데 일하는 장소가 바뀐 거야. 책상에서 바닥으로." 선배가 자리에서 일어섰다. "이 길의 이름이 TEMP고, 09화에서 제대로 파고들 거야. 오늘은 여기까지. 지금 더 들어가면 넌 PGA부터 올리려고 들 거야."
나는 설정을 되돌렸다.
sql
ALTER SESSION SET workarea_size_policy = AUTO;그리고 리포트의 「환경 의존성」 칸에 처음으로 쓸 말이 생겼다.
오늘의 175배는 해시 조인이
optimal로 돌 때의 값이다. PGA가 줄면 같은 실행계획이 TEMP를 쓰기 시작하고, 그때는Buffers가 그대로인 채로 수십 배 느려진다.pga_aggregate_target을 바꾸면 다시 측정해야 한다.
오후 4시 50분 — 범인이 하나 더 있는 줄 알았다
마지막으로 걸리는 게 하나 남아 있었다. 스칼라 서브쿼리였다.
sql
MAX((SELECT r.RGN_NM FROM M_RGN r WHERE r.RGN_ID = c.RGN_ID)) AS rgn_sample01화에서 선배는 "두 가지가 겹쳐 있다"고 했다. 하나는 힌트, 다른 하나가 이것이었다.
"이것도 범인이죠? 100만 행마다 M_RGN을 다시 조회하는 거니까, 여기서도 100만 번일 텐데요."
"또 믿네." 선배가 말했다. "떼어 내서 따로 재."
rev00에서 힌트는 그대로 두고 스칼라 서브쿼리만 지운 채 돌렸다.
스칼라 있음 : 1,026,849
스칼라 없음 : 1,026,837
----------
차이 : 1212블록.
전체의 0.0012%밖에 안 됐다.
"…범인이 아니네요."
"그래. 하마터면 엉뚱한 데를 범인으로 몰 뻔했지." 선배가 말했다. "그런데 하나 더 봐. M_RGN 쪽 Starts가 몇이야?"
xplan
| 1 | TABLE ACCESS BY INDEX ROWID | M_RGN | 8 | ... | 12 |8. 100만이 아니라 8이었다.
"100만 행을 훑는데 8번만 봤어요. 왜요?"
"RGN_ID가 몇 종류야?"
8종류였다.
"오라클이 이미 본 답을 기억해 둔 거야." 선배가 말했다. "그게 왜 되는지, 언제 안 되는지는 07화에서 볼 거야. 오늘은 '스칼라 서브쿼리가 100만 번 돌지 않았다'는 사실만 챙겨 가."
⚠️ 이번엔 운이 좋았다. 종류가 8개가 아니라 수십만 개였다면
Starts도 수십만이 됐을 것이다. 그 경계는 07화에서 잰다.
오후 6시 10분 — 그럼 이 힌트는 누구 잘못인가
퇴근 무렵에 리포트를 쓰고 있었다. "1,026,849 → 5,869. 175배."
선배가 의자를 돌려 앉았다.
"오늘 좋은 숫자 나왔네. 근데 하나 묻자. 이 힌트는 누구 잘못이야?"
하루 종일 생각한 걸 말했다.
"강도현 씨는 아니에요. 2018년에 민원이 있었고, 조치를 했고, 회의에서 승인받았고, 기록도 남겼어요. 할 일을 다 했어요."
"그럼?"
"아무도 다시 안 쟀다는 거요. 데이터가 50배로 느는 동안 아무도요."
"그럼 몇 년 뒤에 누가 쟀어야 하지?"
"…그걸 정해 놓은 사람이 없었네요."
선배가 고개를 끄덕였다. "그래. 그래서 오늘 네가 하나 만들 거야."
"또 서류요?"
"힌트 유효기간 대장." 선배가 화이트보드에 칸을 그렸다. "힌트 하나마다 언제, 왜, 어떤 데이터 크기에서 붙였는지 적고, 언제 다시 잴지를 못 박는 거야."
회의록이 떠올랐다. 거기 빠져 있던 그 줄이.
"그 회의록에 '2만 건'이 안 적혀 있었던 거요."
"정확해." 선배가 말했다. "힌트는 데이터를 보고 내린 판단이야. 판단을 기록할 때 전제를 같이 안 적으면, 전제가 바뀌어도 아무도 못 알아채."
마지막으로 한마디가 더 있었다.
"하나만 더. 오늘 네가 만든 rev01 말인데, 그건 유효기간이 없어?"
나는 입을 열다 말았다.
없을 리가 없었다. M_CUS가 2,000행이라서 해시가 이긴 것이다. M_CUS가 200만 행이 되면?
"…재야 알겠는데요."
"바로 그걸 대장에 적어." 선배가 일어섰다. "네가 오늘 내린 판단도 똑같이 늙어. 차이는 하나야. 강도현 씨는 전제를 안 적었지만, 너는 적어 둔다는 거."
오늘 남은 것
| 무엇 | 값 |
|---|---|
Q2_rev01 | 힌트 29글자 제거. 1,026,849 → 5,869(175배) |
| 조인 3종 대조 | NL / 해시 / 소트 머지: 모두 "안쪽을 어디서 찾느냐"의 차이 |
| 손익분기점 | outer 21건에서 뒤집힌다. 20건까지는 NL 승(실측) |
| T1 리포트 4호 | 「환경 의존성」 칸을 처음으로 채운 리포트 |
| 「힌트 유효기간 대장」 | 이 교재의 새 표준 문서 |
오늘 배운 한 문장
조인 3종은 결국 "바깥 한 건에 맞는 안쪽 행을 어디서 찾느냐"만 다르다. NL은 매번 원래 자리로 가고, 해시는 한 번에 내 책상으로 가져오고, 소트 머지는 양쪽을 줄 세워 나란히 놓는다.
하나 더 있다.
힌트는 그 시점의 데이터 크기를 보고 내린 판단이다. 판단을 기록할 때 전제를 같이 적지 않으면, 전제가 바뀌어도 아무도 알아채지 못한다.
다음으로 넘기는 것
- 스칼라 서브쿼리가 8번만 돈 이유 (그리고 안 될 때) → 07화
- 래치와 버퍼 캐시 (PGA에는 왜 없는가) → 07화
- work area가 모자라면 (
Used-Tmp· 외부 정렬) → 09화 rev01의 유효기간 (M_CUS가 커지면) → 대장에 적어 두고 18화에서 다시 잰다
다음 →
3_실습과_기록.md에서 오늘 과정을 직접 재현하고, 손익분기점을 손으로 찾은 뒤 T1 4호와 대장 1호를 쓴다. 개념이 막히면2_파고들기.md를 펼친다.