테마
07화 · 히트율 99퍼센트인데 서버가 탄다
4월 15일 월요일, 지난주에 고친 Q2 때문에 운영 경보가 울렸다. 오늘은 세 번 틀린다. 그리고 "메모리에서 읽으니까 빠르다"는 믿음이 깨진다.
오전 8시 50분
출근해서 노트북을 열자마자 메신저가 깜빡였다.
박팀장 08:41
지난주 Q2 건 고맙습니다. 운영팀 말로는 화요일에 적용한 뒤로
DB 서버 CPU 그래프가 눈에 띄게 내려왔답니다.
그런데 운영 대시보드에 노란불이 하나 들어왔어요.
'버퍼캐시 히트율'이 몇 달째 99.9% 초록불이었는데,
적용한 다음 날부터 뚝 떨어져서 경고선 아래로 내려갔답니다.
운영팀에서 "튜닝하면서 뭘 건드린 거 아니냐,
이제 디스크를 더 읽는 거 아니냐"고 묻네요.
내일 아침 운영회의 전에 설명 부탁드립니다.히트율이라는 말은 들어 본 적이 있었다. 찾는 데이터가 이미 메모리에 있었던 비율이고, 높을수록 좋다고 배웠다. 그게 떨어졌다면 메모리에 없어서 디스크까지 갔다는 뜻이다.
그리고 지난주에 운영에 나간 변경은 하나뿐이었다. 내가 지운 힌트 29글자였다.
등이 서늘해졌다.
오전 9시 10분 — 첫 번째 답
선배가 커피를 들고 와서 내 화면을 봤다. 메시지를 끝까지 읽더니 의자를 끌어왔다.
"뭐라고 생각해?"
"06화에서 본 거요." 나는 바로 답했다. 지난주 오후 4시의 장면이 아직 생생했다. "rev01은 해시 조인이라 PGA를 쓰잖아요. 운영 서버에서 책상이 모자라면 TEMP로 내려간다고 하셨고요. 그러면 디스크를 읽으니까 히트율이…"
"그럴듯하네." 선배가 커피를 내려놨다. "근데 그거 잰 거야, 믿는 거야?"
"…믿는 거요."
"재."
무엇으로 재야 할지부터 막혔다. 지금까지 쓴 계기판은 Buffers였다. 그런데 Buffers는 논리적 읽기, 곧 블록을 몇 번 만졌는지를 센 값이다. 디스크에서 몇 개를 가져왔는지는 거기 나오지 않는다.
"히트율은 분자가 따로 있어." 선배가 말했다. "물리적 읽기. 네 세션이 지금까지 한 일을 전부 세는 계기판이 있어. V$MYSTAT이라고."
sql
SELECT n.name, s.value
FROM v$mystat s JOIN v$statname n ON n.statistic# = s.statistic#
WHERE n.name IN ('session logical reads', 'physical reads', 'CPU used by this session');"이건 누적이야. 이 세션이 접속한 뒤로 쌓인 값이지. 그러니까 SQL 하나의 몫을 보려면 앞뒤로 한 번씩 찍어서 빼."
캐시가 데워지도록 몇 번 먼저 돌려 둔 뒤, Q2_rev00과 Q2_rev01을 한 번씩 돌리고 앞뒤 값을 뺐다.
REV LOGICAL_READS PHYSICAL_READS CPU_CS HIT_PCT
------ ------------- -------------- ------- --------
rev00 1026849 0 37 100.00
rev01 5868 0 12 100.00둘 다 물리적 읽기 0이었다.
"디스크를 한 블록도 안 읽는데요. rev01도요."
혹시 몰라서 06화에서 본 실행계획의 Used-Mem도 다시 확인했다. (0), 곧 0 패스였다. 책상은 넉넉했고 TEMP는 근처에도 안 갔다.
"그럼 둘의 히트율은?" 선배가 물었다.
"…둘 다 100이에요."
"근데 운영에서는 떨어졌다며."
오전 9시 40분 — 분수
선배가 화이트보드에 한 줄을 썼다.
히트율 = 1 − 물리적 읽기 ÷ 논리적 읽기"이거 분수야. 분수가 움직이는 방법은 몇 가지지?"
"분자가 늘거나… 분모가 줄거나요."
"rev01이 분자를 늘렸어?"
"아니요. 0이에요."
"그럼?"
숫자를 다시 봤다. 분모가 1,026,849에서 5,868이 됐다. 175분의 1이었다.
"분모요. 근데 이상해요. 분자가 0이면 분모를 아무리 줄여도 100%잖아요."
"그래. 그러니까 운영 대시보드가 떨어지려면 분자가 0이 아닌 순간이 있어야 해. 언제일 것 같아?"
캐시에 없는 블록을 읽을 때, 그러니까 처음 읽을 때다.
"운영 서버는 매주 일요일 밤 점검 때 재기동해." 선배가 말했다. "월요일 아침엔 캐시가 텅 비어 있어. 그날 첫 Q2는 T_ORD_BIG을 어디서 읽겠어?"
"디스크요."
"그 월요일 아침을 만들어 보자."
오전 10시 10분 — 월요일 아침을 만든다
"서버를 재기동할 수는 없잖아요."
"그럴 필요 없어. 캐시에 없는 테이블만 있으면 돼." 선배가 말했다. "방금 만든 테이블은 아직 아무도 안 읽었어. 캐시에 있을 수가 없지."
sql
CREATE TABLE T_ORD_C07 AS SELECT * FROM T_ORD_BIG;"원본에 뭘 써 가면서 실험할 생각은 하지 마." 선배가 덧붙였다. "05화에서 봤지? 한 번 올라간 고수위선은 지우든 롤백하든 안 내려와. 원본에 한 줄만 넣었다 빼도 다음 화의 숫자가 전부 바뀌어. 실험은 복사본에서 해."
그리고 '하루'를 정했다. 캐시가 빈 아침에 Q2가 열 번 도는 하루다. 이 하루를 rev00으로 한 번 돌리고, 복사본을 새로 만들어 rev01로 한 번 더 돌렸다. 실행할 때마다 앞뒤 값을 뺐다.
REV RUN LOGICAL_READS PHYSICAL_READS HIT_PCT
------ --- ------------- -------------- --------
rev00 1 1026853 5813 99.43
rev00 2 1026844 5813 99.43
rev00 3 1026844 0 100.00
... (4 ~ 10 번째도 0)
rev01 1 5872 5813 1.00
rev01 2 5863 5813 0.85
rev01 3 5863 0 100.00
... (4 ~ 10 번째도 0)"두 번째에도 디스크를 읽었네요?"
"첫 번째는 버퍼 캐시를 거치지 않고 바로 읽었어. 두 번째에 캐시로 올렸고." 선배가 말했다. "어느 쪽이든 디스크에서 가져온 거야. 물리적 읽기에 똑같이 찍혀. 근데 그건 지금 중요한 게 아니고, 합계를 봐."
📐 처음 읽는 큰 테이블이 캐시를 우회하는 이유 →
2_파고들기.md §8
| 하루 (캐시가 빈 아침 + Q2 열 번) | 논리적 읽기 | 물리적 읽기 | 히트율 | CPU |
|---|---|---|---|---|
| rev00 (힌트 그대로) | 10,268,450 | 11,626 | 99.89% | 3.7~4.0초 |
| rev01 (힌트 제거) | 58,639 | 11,626 | 80.17% | 약 1.2초 |
대시보드에서 본 초록불과 노란불이 그대로 나왔다. 하나는 99.9, 하나는 경고선 아래였다.
그런데 나는 가운데 열에서 눈을 뗄 수가 없었다.
11,626. 11,626.
"디스크 읽기가… 똑같아요. 한 블록도 안 달라요."
"그럼 운영팀 질문에 답해 봐." 선배가 말했다. "디스크를 더 읽어?"
"아니요."
"그럼 히트율은 왜 떨어졌어?"
"분모가 줄어서요. 1,026만에서 5만 8천으로요."
"그 분모에서 빠진 1,000만은 뭐였는데?"
06화의 실행계획이 떠올랐다. TABLE ACCESS BY INDEX ROWID M_CUS, Starts 1,000,000.
"…M_CUS를 100만 번씩 두드린 거요. 그걸 열 번이나요."
오전 10시 50분 — 99.9의 정체
"그럼 한 문장으로 말해 봐." 선배가 말했다. "몇 달 동안 대시보드가 초록불이었던 이유를."
나는 천천히 말했다.
"Q2가 M_CUS를 쓸데없이 100만 번씩 두드려서 분모를 부풀려 놨기 때문이에요."
"그래." 선배가 고개를 끄덕였다. "99.9는 건강해서 나온 숫자가 아니라 낭비해서 나온 숫자야."
선배가 하나를 더 짚었다. 첫 번째 실행만 떼어 낸 두 줄이었다.
REV LOGICAL_READS PHYSICAL_READS HIT_PCT
rev00 1026853 5813 99.43
rev01 5872 5813 1.00"둘 다 디스크에서 똑같이 5,813블록을 가져왔어. 한 일은 같아. 그런데 하나는 99, 하나는 1이야."
"히트율로 SQL을 평가하면…"
"낭비하는 쪽이 이겨." 선배가 말했다. "필요 없는 걸 메모리에서 많이 읽을수록 점수가 오르는 시험이야. 그런 시험으로 건강을 재면 안 되지."
나는 수첩에 적었다. 히트율은 건강 지표가 아니다.
"그럼 히트율은 쓸모없는 거예요?"
"그렇게까지는 아니야. 낮으면 물어볼 거리는 돼. '디스크를 많이 읽고 있나?' 하고." 선배가 말했다. "근데 높다고 안심하면 안 되고, 떨어졌다고 겁먹을 것도 없어. 지금처럼 말이야. 히트율이 움직이면 분자를 따로, 분모를 따로 봐. 그게 다야."
⛳ 오늘은 여기까지(중단 지점 1) 여기까지가 오늘의 절반이다. 히트율은 떨어졌는데 디스크 읽기는 그대로인 현상과 그 이유를 봤다. 이어서 읽으면 "메모리에서 읽으니까 빠르다"는 믿음이 깨진다. 그리고 06화의
Starts 8이 왜 운이었는지 알게 된다.
오전 11시 20분 — 두 번째 답
운영팀에 보낼 답은 정리됐다. 그런데 나한테는 아직 걸리는 게 하나 있었다.
"선배님, 그래도 하나는 이상해요. rev00은 디스크를 한 번도 안 읽었잖아요. 100만 블록을 전부 메모리에서 읽었고요. 메모리는 빠르니까 100만 번이라도 사실 별거 아닌 거 아니에요?"
"아까 표에 열이 하나 더 있었지."
CPU_CS 열이었다. 단위는 1/100초다.
rev00 CPU 37 → 0.37 초
rev01 CPU 12 → 0.12 초"rev00이 한 번 도는 데 걸리는 시간도 0.37초 남짓이야. 그런데 CPU 시간도 0.37초야. 디스크는 한 번도 안 기다렸고. 그럼 그동안 뭘 한 거야?"
"…처음부터 끝까지 CPU를 쓴 거네요."
"운영팀이 본 CPU 그래프가 내려간 이유가 이거야." 선배가 말했다. "히트율이 99.9인 동안 서버는 타고 있었어. 디스크를 안 기다리니까 대기 이벤트에도 안 찍히고, 히트율은 오히려 올라가고. 숨기 딱 좋은 자리였지."
"메모리에서 블록 하나 읽는 데 대체 뭘 하길래요?"
선배가 일어섰다. "따라와."
오전 11시 40분 — 탕비실 픽업 선반
선배가 향한 곳은 탕비실이었다. 01화에서 캐리어를 봤던 바로 그 자리였다.
지난달부터 지하 카페가 층별 배달을 시작했다. 주문한 커피는 기사가 캐리어째 올려다가 탕비실 픽업 선반에 놓고 간다. 선반에는 00부터 99까지 칸이 100개 있고, 칸마다 번호가 붙어 있었다.
"내 주문번호가 3427이야. 내 커피 찾아 와."
선반 위에 붙은 안내문을 읽었다.
※ 커피는 주문번호 끝 두 자리 칸에 있습니다.
※ 같은 칸에 여러 개가 오면 앞에서부터 줄지어 놓습니다.
※ 칸에 손을 넣기 전에, 그 줄 맨 위의 집게를 집으세요.
(두 사람이 한 칸을 동시에 뒤지면 캐리어가 쏟아집니다 — 총무팀)끝 두 자리가 27이니 27번 칸으로 갔다. 칸 100개를 다 볼 필요가 없었다. 번호만 알면 칸이 정해진다.
27번 칸이 있는 세로줄 맨 위에 빨래집게 같은 게 하나 걸려 있었다. 그걸 집었다. 칸 안에는 캐리어 세 개가 앞뒤로 겹쳐 있었다. 앞에서부터 꼬리표를 봤다. 1127, 아니다. 2227, 아니다. 3427, 이거다.
캐리어를 빼고, 집게를 제자리에 걸고, 돌아왔다.
"방금 몇 단계였어?" 선배가 물었다.
손가락을 하나씩 접었다. 번호로 칸을 정하고, 집게를 집고, 칸 안의 줄을 앞에서부터 훑고, 내 것을 확인하고, 집게를 놓았다.
"다섯이요."
"지하 카페엔 안 내려갔지? 그게 히트야." 선배가 말했다. "근데 일이 없었어?"
"…있었죠."
"rev00은 이걸 100만 번 했어. 그것도 늘 같은 몇십 개 칸에서."
선반을 다시 봤다. 칸 번호를 계산하고, 집게를 잡고, 줄을 훑는 일 하나하나는 금방 끝난다. 하지만 그걸 100만 번 한다.
"하나 더." 선배가 집게를 가리켰다. "이 집게는 뭘 지키는 거야?"
"커피 아니에요? 남이 가져가지 말라고…"
"커피는 아무도 안 훔쳐." 선배가 웃었다. "안내문 다시 읽어 봐. 두 사람이 한 칸을 동시에 뒤지면 캐리어가 쏟아진다잖아. 한 사람은 줄 중간에서 하나를 빼는데 다른 사람은 줄 끝에 하나를 넣고 있으면 줄이 엉켜. 집게가 지키는 건 커피가 아니라 칸 안의 줄이야."
"그래서 줄을 만지는 동안만 잡는 거고요."
"그래. 커피를 마시는 동안엔 집게를 안 잡아. 그건 네 일이니까." 선배가 말했다. "06화에서 '내 책상엔 자물쇠가 없다'고 했던 거 기억나? 책상은 너만 쓰니까 줄이 엉킬 일이 없어. 여기는 층 전체가 같이 쓰니까 집게가 있어야 해. 그게 PGA와 버퍼 캐시의 차이야."
| 탕비실 | 오라클 |
|---|---|
| 캐리어 | 블록 (01화의 그 캐리어 · 8KB) |
| 픽업 선반 | 버퍼 캐시 |
| 주문번호 끝 두 자리 → 칸 | 블록 주소(DBA) → 해시 함수 → 해시 버킷 |
| 한 칸에 앞뒤로 줄지어 놓인 캐리어 | 해시 체인 (버퍼 헤더의 연결 리스트) |
| 세로줄 맨 위의 집게 (한 개가 칸 여러 개를 맡는다) | cache buffers chains 래치 (CBC 래치) |
| 지하 카페에 직접 가기 | 물리적 읽기 (데이터 파일) |
| 선반에서 찾기 | 논리적 읽기 |
| 지하에 안 내려간 비율 | 히트율 |
"이 선반이 오라클에선 몇 칸이고, 집게는 몇 개일 것 같아?"
모르겠다고 했다.
"자리 가서 재 봐." 선배가 커피를 들었다. "그리고 집게를 몇 번 잡았는지도."
📐 해시 버킷과 체인, 래치가 실제로 어떻게 생겼는지 →
2_파고들기.md §2·§3
오후 1시 30분 — 집게를 몇 번 잡았나
점심을 먹고 와서 먼저 칸과 집게를 셌다.
sql
SELECT COUNT(*) FROM v$latch_children WHERE name = 'cache buffers chains'; -- 4096집게(CBC 래치)는 4,096개였다. 칸(해시 버킷) 수는 일반 계정으로는 보이지 않아서 선배가 SYS 화면으로 보여 줬다. 524,288개. 집게 하나가 칸 128개를 맡는 셈이었다. 버퍼 캐시는 864MB로, 블록이 110,592개 들어가는 크기였다.
다음은 집게를 몇 번 잡았는지였다. 래치마다 gets라는 카운터가 있어서, 4,096개를 전부 더한 값을 Q2 앞뒤로 찍어 뺐다. V$MYSTAT에서 같이 볼 만한 숫자도 몇 개 더 골랐다. 아래는 5번씩 돌린 평균이다.
NAME REV00_PER_RUN REV01_PER_RUN
-------------------------------- ------------- -------------
session logical reads 1026856 5868
consistent gets examination 1018012 10
consistent gets pin 8838 5858
CBC latch gets 1035706 11728
CPU used by this session 37 12
논리적 읽기 한 번당 CBC 래치 1.009 1.999이상한 게 하나 있었다.
"선배님, rev00은 블록 하나 읽을 때 집게를 한 번 잡았고, rev01은 두 번 잡았어요. 왜 일을 적게 하는 쪽이 더 자주 잡아요?"
"위의 두 줄이 답이야. examination하고 pin."
"…아까 탕비실에서요?"
"그래. 커피를 확인하는 방법은 두 가지야." 선배가 손을 들어 보였다. "칸 앞에 선 채로 집게 잡고, 꼬리표 하나 보고, 놓고 끝. 이게 examination. 집게를 한 번 잡아."
"rev00이 M_CUS에서 한 게 그거네요. 고객 한 명 등급만 보고 끝."
"그렇지. 다른 하나는 캐리어를 자리로 가져가서 여러 잔을 차례로 마시는 거야. 들고 갈 때 칸에 '가져감' 꼬리표를 달아 둬. 그게 핀이야. 꼬리표를 다는 동안 집게를 잡고, 달고 나면 놔. 다 마시고 돌려놓을 때 다시 집게를 잡고 꼬리표를 떼. 두 번."
"rev01의 전체 스캔은 블록 하나에서 행을 169개씩 읽으니까 그쪽이고요."
"그래. 두 번 잡더라도 한 번 들고 가서 169잔을 마시는 게, 한 잔마다 한 번씩 백만 번 오는 것보다 훨씬 싸."
1,035,706번 대 11,728번. 같은 답을 내는 데 집게를 잡은 횟수가 88배 차이 났다.
📐 examination과 pin, 버퍼 pinning →
2_파고들기.md §4
오후 2시 10분 — 4,096개 중 33개
"그 백만 번이 집게 4,096개에 골고루 퍼졌을까?" 선배가 물었다.
"…아닐 것 같아요. M_CUS는 28블록밖에 안 되잖아요."
"재 봐."
이번엔 래치 4,096개의 gets를 하나하나 받아 적고, rev00을 한 번 돌린 뒤 래치마다 뺐다.
REV TOTAL_GETS LATCHES_TOUCHED LATCHES_OVER_1000 GETS_ON_THOSE PCT_ON_THOSE
------ ---------- --------------- ----------------- ------------- ------------
rev00 1037016 3852 33 1024135 98.8
rev01 12794 3852 033개. 집게 4,096개 중 33개가 rev00이 한 일의 98.8%를 받아 냈다. 가장 바쁜 집게는 한 번 실행하는 동안 3만 7천~3만 9천 번씩 잡혔다.
rev01도 래치 3,852개를 건드렸다. 그런데 1,000번을 넘긴 래치는 하나도 없었다. T_ORD_BIG의 블록 5천여 개를 한 번씩 고르게 읽었기 때문이다.
"이 33개가 무슨 블록을 지키는지 보고 싶어요."
"그건 일반 계정으로는 안 보여." 선배가 SYS로 접속한 창을 하나 띄웠다. 버퍼 헤더를 직접 보여 주는 X$BH에는 hladdr라는 열이 있었다. 이 버퍼가 매달린 줄을 지키는 래치의 주소다. 바쁜 래치 33개의 주소를 여기에 맞춰 보자 답이 나왔다.
CHILD# GETS(1회) 이 래치가 지키는 블록
42 38004 M_CUS block 3474
116 37004 M_CUS block 3502
201 38004 M_CUS block 3479
...
680 4006 PK_M_CUS block 3494
...
그 래치들이 지키는 M_CUS 블록 / PK_M_CUS 블록 : 27 / 6M_CUS 데이터 블록 27개와 PK_M_CUS 블록 6개. 칸 33개 앞에만 하루 종일 사람이 몰려 있었던 셈이다.
"이런 블록을 핫 블록이라고 해." 선배가 말했다. "선반은 텅텅 비었는데 몇몇 칸 앞에만 사람이 몰리는 거야."
"근데 오늘 아침엔 혼자 돌렸잖아요. 줄 설 사람이 없었는데요."
"그러니까 사람을 늘려 보자."
📐 hladdr 역추적 →
2_파고들기.md §5· 직접 하려면실습/90_XBH_SYSDBA.sql
오후 2시 40분 — 네 명이 동시에
"월말이면 조민우 씨 팀이 등급별 집계 화면을 동시에 띄워." 선배가 말했다. "네 명이라고 치고 해 봐."
터미널에서 같은 일꾼 스크립트를 네 개 동시에 띄웠다. 일꾼은 각자 Q2를 5번 돌리고 자기 몫을 수첩 테이블에 적는다. 그 앞뒤로 래치 4,096개의 카운터 합계를 찍었고, 이 실험을 두세 번씩 반복했다.
| 동시에 | 한 명이 5번 도는 데 | CBC misses | sleeps | CPU 차례 기다림 (1인) |
|---|---|---|---|---|
| rev00 × 1 | 1.86~1.94초 | 0 | 0 | 0 |
| rev00 × 2 | 1.88~1.91초 | 11,736~21,512 | 0 | 0~0.02초 |
| rev00 × 4 | 2.29~3.59초 | 19,887~27,698 | 0 | 0.53~1.42초 |
| rev01 × 4 | 0.72~0.98초 | 40~378 | 0 | 0.17초 |
둘이 되자 misses가 생겼다. 집게를 집으러 갔는데 이미 누가 쥐고 있었던 횟수다. 그런데 sleeps는 0이었다.
"집게를 놓쳤는데 안 잤어요. 그럼 뭘 했어요?"
"그 자리에서 손을 몇 번 더 뻗었어." 선배가 말했다. "줄 서서 기다리는 게 아니라, 집게가 금방 풀릴 거라고 보고 제자리에서 다시, 또 다시 뻗는 거야. 그걸 spin이라고 해. 대개 그 사이에 풀려. 그래서 대기 이벤트엔 안 찍혀. 대신 손 뻗는 시간이 전부 CPU야."
네 명이 되자 다른 게 보였다. 각자 CPU를 쓴 시간은 혼자일 때와 거의 같은데(1.87~2.02초), 끝나는 데는 2.29~3.59초가 걸렸다. 그 차이가 resmgr:cpu quantum이라는 대기로 정확히 잡혀 있었다.
"이건 뭐예요?"
"이 컨테이너는 CPU를 두 개만 쓰도록 묶여 있어. Free의 제한이야. 네 명이 동시에 CPU를 달라고 하니까 둘은 차례를 기다리는 거지. 운영 서버였으면 그냥 'CPU 사용률 100%'로 보였을 거고." 선배가 덧붙였다. "서버가 탄다는 게 바로 이런 모습이야. 디스크는 한가하고, 히트율은 99.9고, 다들 CPU 앞에 줄 서 있고."
rev01로 돌린 네 명은 1초도 안 걸렸다. misses도 수십에서 수백 번에 그쳤다.
ℹ️ Free에서는 여기까지만 보인다. CPU가 두 개뿐이라 집게 앞에서 동시에 손을 뻗는 사람도 많아야 둘이다. 그래서 spin이 거의 다 성공하고,
sleeps와latch: cache buffers chains대기는 0으로 남는다. CPU 수십 개에서 수백 세션이 같은 블록을 두드리면 spin으로 버티지 못하고 잠들기 시작하는데, 그런 대규모 EE 환경은 별권에서 다룬다. →2_파고들기.md §5
오후 3시 30분 — 스티커
"하나만 더 보여 줄게." 선배가 SYS 창으로 돌아갔다. "선반이 꽉 차면 어떤 캐리어부터 치울 것 같아?"
"오래 안 찾은 거요."
"그걸 어떻게 알아?"
선배가 X$BH의 열을 하나 더 가리켰다. tch, 곧 touch count였다. 버퍼마다 붙어 있는 '찾음' 스티커의 수다. 선배가 실험을 하나 돌렸다. 이 PDB의 캐시를 비우고 rev00을 딱 한 번 돌린 다음, T_ORD_BIG을 4초 간격으로 세 번, 이어서 쉬지 않고 세 번 더 전체 스캔했다.
STEP OBJECT_NAME BUFFERS MIN_TCH MAX_TCH
2) rev00 1 번 뒤 M_CUS 27 1 1
2) rev00 1 번 뒤 T_ORD_BIG 5818 1 1
3) 4초 간격 스캔 3 번 뒤 T_ORD_BIG 5818 4 4
4) 쉬지 않고 3 번 더 T_ORD_BIG 5818 4 4
4) 쉬지 않고 3 번 더 M_CUS 27 1 1"M_CUS는 블록마다 수만 번을 만졌는데 1이에요. T_ORD_BIG은 일곱 번 만졌는데 4고요."
"스티커는 3초에 한 장이야." 선배가 말했다. "3초 안에 다시 집은 건 안 세. 그래서 한 SQL이 1초 동안 백만 번 두드려도 한 장이고, 띄엄띄엄 여러 번 찾아온 블록이 스티커를 더 받아."
"왜 그렇게 해요? 많이 만진 게 중요한 거 아니에요?"
"큰 쿼리 하나가 한 번 휩쓸고 지나가면서 스티커를 왕창 받으면, 그 블록들이 선반을 독차지하겠지. 정작 매일 조금씩 쓰는 블록은 밀려나고." 선배가 말했다. "그래서 새로 온 캐리어는 선반 맨 앞이 아니라 가운데에 놓고, 선반 끝까지 밀려났어도 스티커가 두 장 이상이면 버리지 않고 앞으로 올려. 이 정리 규칙이 LRU 체인이야."
마지막으로 하나를 더 확인했다. 복사본에 UPDATE를 하고 COMMIT까지 했는데도 그 블록들은 dirty로 남아 있었다. 메모리에서는 바뀌었지만 아직 디스크에 쓰지 않은 버퍼라는 뜻이다.
"커밋했는데 디스크에 안 썼어요?"
"안 썼어. 그래도 안전해. 왜 그런지는 13화에서 볼 거야. 오늘은 선반에 '바뀌었는데 아직 안 내려보낸 캐리어'도 있다는 것까지만 알아 둬."
📐 LRU · 중간점 삽입 · touch count · 버퍼 상태 →
2_파고들기.md §6·§7
오후 4시 20분 — 8의 비밀
자리로 돌아와 06화 문서를 다시 열었다. 거기에 이런 한 줄이 남아 있었다.
⚠️ 이번엔 운이 좋았다. 종류가 8개가 아니라 수십만 개였다면
Starts도 수십만이 됐을 것이다. 그 경계는 07화에서 잰다.
Q2의 스칼라 서브쿼리 이야기다. 100만 행을 지나가는 동안 M_RGN은 8번만 열렸다.
xplan
| 1 | TABLE ACCESS BY INDEX ROWID| M_RGN | 8 | ... | 12 |"선배님, 이것도 캐시죠? 이미 본 답을 기억해 둔 거라고 하셨잖아요."
"응. 근데 탕비실 선반이 아니야." 선배가 말했다. "네 책상 위 메모판이야. 이 SQL이 한 번 도는 동안만 붙여 두고, 너만 봐. RGN03이면 INCHEON, 이렇게 적어 두고 다음에 RGN03이 오면 메모판에서 바로 읽는 거야."
"그럼 지역이 8개라서 8번이었던 거네요."
계산이 다 섰다고 생각했다.
"그럼 고객명을 이렇게 붙이면 2,000번이겠네요. 고객이 2,000명이니까."
T_ORD_BIG에는 CUS_ID 2,000종류가 정확히 500번씩 돌아가며 들어 있었다. M_CUS에는 2,000번만 가고 나머지는 메모판에서 읽을 테니까.
"재 봐."
sql
SELECT COUNT(*), MAX(nm)
FROM (SELECT (SELECT c.CUS_NM FROM M_CUS c WHERE c.CUS_ID = o.CUS_ID) AS nm
FROM T_ORD_BIG o);xplan
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.47 | 779K|
| 1 | TABLE ACCESS BY INDEX ROWID| M_CUS | 752K| 1 | 752K|00:00:00.32 | 774K|
|* 2 | INDEX UNIQUE SCAN | PK_M_CUS | 752K| 1 | 752K|00:00:00.12 | 21011 |
| 3 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.47 | 779K|
| 4 | TABLE ACCESS FULL | T_ORD_BIG | 1 | 1000K| 1000K|00:00:00.05 | 5826 |
---------------------------------------------------------------------------------------------Starts 752K. 정확히는 752,995였다. Buffers는 779,832였다.
같은 답을 조인으로 구하면 5,856이었다.
"2,000이 아니라 75만이에요."
"계산해 봐. 메모판에 올라간 고객은 한 번만 가고, 못 올라간 고객은 500번 다 가."
752,995 = 올라간 수 × 1 + 못 올라간 수 × 500
= 495 + 1,505 × 5002,000명 중 495명만 메모판에 자리를 얻었다.
"메모판이 그렇게 작아요?"
"작기도 하고, 규칙이 하나 있어." 선배가 말했다. "메모판도 칸이 정해져 있어. 값마다 들어갈 칸이 정해지는 것도 탕비실 선반이랑 같아. 그런데 그 칸에 이미 다른 게 적혀 있으면, 그냥 안 적어."
"선반은 같은 칸에 여러 개를 줄지어 놓잖아요."
"그래. 선반은 부딪혀도 줄로 매달아. 줄이 있으니까 엉키지 말라고 집게가 필요한 거고. 메모판은 줄이 없어. 부딪히면 포기해. 대신 집게도 필요 없지. 너 혼자 쓰니까."
종류를 8개부터 늘려 가며 쟀다(일부만 옮긴다. 전체는 실습 [D]).
NDV STARTS PER_VALUE CACHED NOT_CACHED
8 8 125000 8 0
64 93808 15625 58 6
128 132922 7813 111 17
512 357735 1953 329 183
2000 752995 500 495 150564종류만 돼도 6개가 자리를 못 얻었다. 그 6개는 1만 5천 번씩 M_CUS에 갔다.
"M_RGN의 8은…"
"8개가 서로 안 부딪혔을 뿐이야. 운이었지." 선배가 말했다. "그리고 하나 더. 메모판의 열쇠는 결과가 아니라 넣은 값이야. 지역명이 8개뿐이어도 서브쿼리에 넣는 값이 100만 가지면 100만 번 가."
그것도 재 봤다. ORD_SEQ로 RGN_ID를 계산해 넣었더니, 나오는 지역명은 8개뿐인데 M_RGN의 Starts는 1,000,000이었다.
📐 스칼라 서브쿼리 캐시의 크기와 규칙 →
2_파고들기.md §9
오후 5시 00분 — 찾는 것도 일이다
"그럼 rev01은 괜찮은 거죠?" 내가 말했다. "M_RGN은 8개가 안 부딪혔으니까요. Starts 8에 12블록. 이 정도면 공짜잖아요."
선배가 나를 잠깐 봤다.
"오늘 하루 종일 무슨 얘기 했어?"
"…히트율이요."
"히트율이 뭐였는데?"
"찾는 게 이미 메모리에 있던 비율이요."
"그리고 그게 높으면?"
나는 입을 다물었다. 탕비실 선반 앞에서 손가락 다섯 개를 접던 게 생각났다. 지하에 안 내려갔어도 일은 다섯 단계였다.
"…메모판에서 찾는 것도 100만 번이네요."
"재."
rev01을 그대로 두고, 스칼라 서브쿼리만 외부조인으로 바꾼 문장을 나란히 놓았다. 먼저 답이 같은지 양쪽으로 MINUS를 걸어 봤다. 둘 다 0행이었다. 그리고 10번씩 돌려 CPU를 쟀다.
rev01 (스칼라) Buffers/회 5868 CPU 10회 119 ~ 121
스칼라 -> 외부조인 Buffers/회 5862 CPU 10회 60 ~ 63블록은 6개 차이인데 CPU는 절반이었다.
"M_RGN은 8번 열었지. 근데 메모판은 100만 번 들여다봤어." 선배가 말했다. "100만 행마다 '이 값이 메모판에 있나' 하고 칸을 계산해서 확인하는 일. 그게 rev01이 쓰는 CPU의 절반이야."
화면을 오래 봤다. 아침에 본 것과 똑같은 구도였다.
히트율이 99.9인 동안 서버는 탔다. 디스크를 안 읽었으니 아무도 의심하지 않았다. Starts 8인 스칼라도 마찬가지였다. 8번밖에 안 갔으니 아무도 의심하지 않았다.
"캐시에 있다는 것과 일이 없다는 것은 달라." 선배가 말했다. "오늘 하루가 이 한 문장이야. 버퍼 캐시에서도, 메모판에서도."
"그럼 이걸 rev02로 올릴까요? 외부조인으로."
"T0로 말해 봐."
"Buffers 5,869 → 5,862요."
"그걸 리비전이라고 올릴 거야?"
"…아니요."
"CPU가 절반인 건 사실이야. 그건 리포트에 적어. 06화의 TEMP에 이어 T0가 못 보는 두 번째 길이니까." 선배가 말했다. "근데 리비전감은 아니야. 진짜 질문은 따로 있어. 오라클은 왜 이 스칼라를 알아서 조인으로 안 바꿨을까. 그게 쿼리 변환이고, 다음 주에 할 거야."
Q2는 rev01에 그대로 두기로 했다. T_ORD_BIG을 한 번 읽는 5,827이 바닥이고, 남은 42블록을 짜낸다고 달라질 건 없었다.
"고칠 게 없다는 걸 잰 날도 있는 거야." 선배가 일어섰다. "04화처럼."
⛳ 오늘은 여기까지(중단 지점 2) Q2는 고치지 않았다. 대신 "히트율은 건강 지표가 아니다"라는 결론을 숫자로 손에 넣었다. 여기까지 읽었으면 오늘 몫은 다 한 것이다. 이어지는 저녁에는 운영팀에 보낼 답을 쓰고, 선배가 그 답에 한 줄을 더 붙인다.
오후 6시 10분
운영팀에 보낼 회신을 썼다. 처음엔 이렇게 썼다.
"히트율 하락은 튜닝으로 인한 정상 현상입니다. 문제없습니다."
선배가 어깨 너머로 읽더니 고개를 저었다.
"'문제없습니다'는 운영팀이 듣고 싶은 말이지, 증거가 아니야. 그 사람들은 내일 또 노란불을 볼 거야. 그때마다 너한테 전화 오게 할 거야?"
다시 썼다.
"히트율이 떨어진 것은 디스크를 더 읽어서가 아니라, 메모리에서 읽던 낭비가 사라져서입니다.캐시가 빈 아침을 재현해 비교하면 디스크에서 읽은 블록은 변경 전후 모두 11,626개로 같고,메모리에서 읽은 블록은 10,268,450개에서 58,639개로 줄었습니다. 히트율은 이 두 숫자의 비율이어서 99.89%에서 80.17%로 내려갔습니다.그동안의 99.9%는 이 화면이 같은 고객 정보를 100만 번씩 다시 읽던 낭비 때문에 나온 숫자였습니다. CPU 그래프가 내려간 것도 같은 일이 다른 모습으로 드러난 것입니다."
"좋아." 선배가 말했다. "근데 이것만 보내면 반쪽이야. 운영팀은 대시보드를 내일도 봐. 히트율 옆에 뭘 놓으라고 할 거야?"
오늘 잰 숫자들을 떠올렸다. 디스크를 정말 더 읽는지 보려면 비율이 아니라 물리적 읽기의 절대량을 봐야 한다. 서버가 타는지 보려면 CPU를, 누가 태우는지 보려면 SQL별로 실행 한 번에 몇 블록을 만지는지를 봐야 한다.
한 줄을 더 붙였다.
"대시보드에는 히트율 옆에 '물리적 읽기 합계'와 'SQL별 실행당 논리적 읽기 상위 10개'를 함께 두시기를 제안합니다. 히트율이 움직이면 분자와 분모를 따로 보셔야 원인을 알 수 있습니다."
"됐네." 선배가 짧게 말했다. 그리고 하나를 덧붙였다.
"이 얘기는 16화에서 또 나올 거야. AWR 리포트를 처음 열면 앞쪽에 'Instance Efficiency'라는 칸이 있는데, 거기에 히트율이 줄줄이 나와. 그때 오늘을 떠올려."
"오늘 리포트 제목은요?"
"네가 수첩에 적은 거."
히트율은 건강 지표가 아니다.
오늘 남은 것
| 무엇 | 값 |
|---|---|
| 운영 경보의 정체 | 디스크 읽기 11,626 → 11,626(변화 없음) · 논리적 읽기 10,268,450 → 58,639 · 히트율 99.89% → 80.17% |
| 논리적 읽기의 값 | rev00 한 번에 CPU 0.37초 · CBC 래치 1,035,706번 (디스크 0) |
| 핫 블록 | 래치 4,096개 중 33개가 98.8%: M_CUS 27블록 + PK_M_CUS 6블록 |
| 스칼라 서브쿼리 캐시 | 입력 8종류 → Starts 8 · 2,000종류 → 752,995 (495개만 자리를 얻음) |
| Q2 | rev01 유지. Buffers로는 더 고칠 게 없다(5,869 → 5,862) |
| T1 리포트 5호 | 「히트율은 건강 지표가 아니다」 |
오늘 배운 한 문장
히트율은 건강 지표가 아니다. 히트율은
1 − 물리적 읽기 ÷ 논리적 읽기라는 분수이고, 쓸데없이 많이 읽을수록 올라간다.
그리고 하나 더.
캐시에 있다는 것과 일이 없다는 것은 다르다. 메모리에서 찾을 때도 칸을 계산하고, 집게를 잡고, 줄을 훑는다. 100만 번 찾으면 일도 100만 번이다.
다음으로 넘기는 것
- Q2 rev01의 CPU 절반은 스칼라 서브쿼리 캐시 탐색: 오라클은 왜 스칼라를 조인으로 안 바꿨나 → 08화
- 커밋했는데 왜 dirty인가, 그런데도 왜 안전한가 (DBWn · 체크포인트 · Redo) → 13화
- 래치 말고 다른 줄 서기: 래치는 spin하고, 락은 줄을 세운다 → 12화
- AWR의 Instance Efficiency에 줄줄이 나오는 히트율들 → 16화
- 수백 세션이 같은 블록을 두드리는 대규모 환경의 CBC 경합 → 별권
다음 →
3_실습과_기록.md에서 오늘 과정을 직접 재현하고, 운영팀 회신과 T1 리포트 5호를 쓴다. 개념이 막히면2_파고들기.md를 펼친다.