테마
07화 · 히트율 99퍼센트인데 서버가 탄다
🗓️ 4월 15일 (월) · 06화에서 일주일 뒤 · 무대
ora-hanorder오늘 여는 것: 운영 경보. 지난주에 고친 Q2를 적용한 뒤로 운영 대시보드의 '버퍼캐시 히트율'이 떨어졌다. 오늘은 세 번 틀린다. 그리고 "메모리에서 읽으니까 빠르다"는 믿음이 깨진다.
파일 세 개
| 파일 | 무엇을 하나 | 시간 | |
|---|---|---|---|
| 읽는다 | 1_사건.md | 경보를 받고 디스크 읽기가 그대로인 것을 잰 뒤, 탕비실 선반에서 '찾는 일'을 세어 보다가 06화의 Starts 8이 운이었다는 걸 알게 되는 하루 | 35분 |
| 한다 | 3_실습과_기록.md | 히트율의 분모 · 래치 세기 · 스칼라 캐시 스윕 · (선택) 동시 4세션 · (선택) X$BH · T1 5호 | 60~90분 |
| 찾는다 | 2_파고들기.md | 히트율의 식 · 해시 버킷과 체인 · CBC 래치 · examination/pin · LRU와 touch count · 버퍼 상태 · 스칼라 캐시 | 필요할 때 |
1을 읽고 3을 따라 한다. 2는 막힐 때 펼친다. 시간이 없는 주에는 1_사건을 중단 지점 1까지 읽고 3_실습은 STEP 0, 1, 2, 7만 한다(약 40분).
지난 화에서 들고 오는 것
Q2 = 5,869블록 | 06화에서 힌트 29글자를 지워 1,026,849 → 5,869(175배)로 줄였다. 지난주 화요일 운영에 적용 |
Q2의 스칼라 서브쿼리 | M_RGN 쪽 Starts 8. 06화에 "이번엔 운이 좋았다. 그 경계는 07화에서 잰다"라고 적어 두었다 |
| 06화가 남긴 또 하나 | "PGA에는 래치가 없다". 그렇다면 버퍼 캐시에는 왜 있나 |
Q1 = 91블록 | 02화의 커버링 인덱스. 오늘은 건드리지 않는다 |
그리고 박팀장에게서 메시지가 하나 와 있다.
"'버퍼캐시 히트율'이 몇 달째 99.9% 초록불이었는데, 적용한 다음 날부터 경고선 아래로 내려갔답니다. 운영팀에서 '튜닝하면서 뭘 건드린 거 아니냐, 이제 디스크를 더 읽는 거 아니냐'고 묻네요."
오늘의 전개 — 세 번 틀리고, 고치지 않는다
| 내가 믿은 것 | 측정 | |
|---|---|---|
| 1차 | "해시 조인이 TEMP로 내려가 디스크를 더 읽는다" (06화에서 본 장면에 끌려서) | 물리적 읽기 0. rev00도 rev01도 0이다. 캐시가 빈 하루를 재현해도 11,626 = 11,626 |
| — | 그럼 히트율은 왜? | 분모가 10,268,450 → 58,639로 줄었다. 히트율은 99.89% → 80.17% |
| 2차 | "메모리에서 읽으니까 100만 번이라도 공짜" | rev00 한 번에 0.37초, 전부 CPU. CBC 래치 1,035,706번 · 4,096개 중 33개에 98.8% |
| 3차 | "고객명(2,000종류)을 스칼라로 붙이면 Starts 2,000" | 752,995. 2,000종류 중 495개만 캐시에 자리를 얻었다. 64종류만 돼도 6개가 자리를 못 얻는다 |
| — | "그럼 Starts 8인 rev01은 공짜" | Buffers는 5,869 대 5,862인데 CPU는 절반이다. 메모판을 100만 번 들여다봤기 때문이다 |
이 화에서는 왜 Q2를 고치지 않나?
Buffers로 재면 rev01은 이미 바닥(T_ORD_BIG을 한 번 읽는 5,827)에 닿아 있다. 남은 것은 7블록뿐이다. CPU 절반의 정체(스칼라 캐시 탐색)는 T0가 못 보는 두 번째 길로 기록해 두고, "오라클은 왜 스칼라를 조인으로 안 바꿨나"라는 질문은 08화로 넘긴다. 04화의 Q1처럼, 오늘은 고칠 게 없다는 것을 잰 날이다.
오늘 배우는 한 문장
히트율은 건강 지표가 아니다.
1 − 물리적 읽기 ÷ 논리적 읽기라는 분수라서, 쓸데없이 많이 읽을수록 올라가고 낭비를 없애면 내려간다.
그리고 하나 더.
캐시에 있다는 것과 일이 없다는 것은 다르다. 메모리에서 찾을 때도 칸을 계산하고, 래치를 잡고, 체인을 훑는다. 100만 번 찾으면 일도 100만 번이다.
🔑 오늘의 비유 — 탕비실 픽업 선반
배달 온 커피 캐리어(01화의 그 캐리어, 곧 블록)는 탕비실 픽업 선반에 놓인다. 칸은 주문번호 끝 두 자리로 정해지고, 같은 칸에 여러 개가 오면 앞뒤로 줄지어 놓는다. 칸에 손을 넣기 전에는 그 줄 맨 위의 집게부터 집는다. 두 사람이 한 칸을 동시에 뒤지면 캐리어가 쏟아지기 때문이다.
| 탕비실 | 오라클 |
|---|---|
| 캐리어 | 블록 (8KB) |
| 픽업 선반 | 버퍼 캐시 |
| 주문번호 끝 두 자리 → 칸 | 블록 주소 → 해시 함수 → 해시 버킷 (524,288개) |
| 한 칸에 줄지어 놓인 캐리어 | 해시 체인 |
| 줄 맨 위의 집게 (하나가 칸 여러 개를 맡는다) | CBC 래치 (4,096개 · 하나가 버킷 128개를 맡는다) |
| 집게가 지키는 것은 칸 안의 줄(커피가 아니다) | 래치는 구조(체인)를 지킨다. 데이터는 핀이 지킨다 |
| 지하 카페에 안 내려간 비율 | 히트율 |
| 캐리어에 붙는 '찾음' 스티커 (3초에 한 장) | touch count |
| 내 책상 위 메모판 (칸이 정해져 있고, 부딪히면 안 적는다. 집게는 없다) | 스칼라 서브쿼리 캐시 |
(01화는 커피 캐리어(블록), 02화는 방문객 출입대장(인덱스), 06화는 4층 거래처 카드함(조인)이었다.)
☠️ 오늘의 함정 — 같은 디스크 읽기, 99와 1
캐시가 빈 복사본에 Q2를 딱 한 번 돌려 보면 이렇다.
| 논리적 읽기 | 물리적 읽기 | 히트율 | |
|---|---|---|---|
| rev00 (힌트 그대로) | 1,026,853 | 5,813 | 99.43% |
| rev01 (힌트 제거) | 5,872 | 5,813 | 1.00% |
디스크에서 가져온 블록 수는 같다. 히트율로 SQL을 평가하면 낭비하는 쪽이 이긴다. 그래서 리포트의 「환경 의존성」 칸에 "히트율은 캐시 상태에 따라 같은 SQL에서도 1%~100%를 오간다"라고 적는다.
실습 준비
bash
docker start ora-hanorder06화를 마친 상태(T_ORD_BIG 인덱스 2개 · M_CUS 2,000건 · M_RGN 8건)에서 시작한다. 원본 테이블에는 쓰지 않는다. 캐시가 빈 테이블이 필요하거나 쓰기 실험을 할 때는 복사본(T_ORD_C07, M_CUS_C07)을 만들었다가 스크립트 끝에서 지운다. 그래서 몇 번을 돌려도 결과가 같다.
| 스크립트 | 무엇 | 소요 |
|---|---|---|
실습/00_히트율의_분모.sql | [A]~[C]. 오늘 실습의 본체. 디스크 읽기는 같은데 히트율이 99.89 → 80.17로 떨어진다 | 약 10초 |
실습/01_논리읽기의_값.sql | 래치/LR 1.0 대 2.0 · 래치 33개 · V$BH · 버퍼 상태 | 약 5초 |
실습/02_스칼라_캐시의_칸.sql | Starts 8 / 752,995 / 1,000,000 · NDV 스윕 · 히트의 CPU | 약 8초 |
실습/03a_동시_준비.sql · 03b_일꾼_rev00.sql · 03b_일꾼_rev01.sql · 03c_동시_결과.sql | 선택. 일꾼을 동시에 여러 개 띄워 래치 misses와 CPU 차례 기다림을 본다 | 회당 약 3초 |
실습/90_XBH_SYSDBA.sql | 선택 · SYSDBA. 해시 구조 숫자 · touch count · 래치 → 블록 · 체크포인트 · 스칼라 캐시 크기 | 약 15초 |
SYSDBA가 필요한 것은
90_뿐이다. Git Bash에서는MSYS_NO_PATHCONV=1을 꼭 붙여야 한다. 이 스크립트는 이 PDB의 버퍼 캐시를 비우므로 연습 컨테이너에서만 돌린다.
ℹ️ 이 교재의 실습은 Docker로 띄운 Oracle Database Free 한 대로 모두 끝난다. Free는 CPU를 2개로 묶어 두므로, 래치 경합은 misses → spin 성공과 CPU 차례 기다림(
resmgr:cpu quantum)까지만 재현된다(sleeps0). 수백 세션이 같은 블록을 두드려 래치 대기가 쌓이는 대규모 EE 환경은 별권에서 다룬다.
이 화의 산출물
| 무엇 | 어디 |
|---|---|
| T1 리포트 5호: 「히트율은 건강 지표가 아니다」. SQL을 고치지 않은 진단 리포트 | 3_실습과_기록.md STEP 7 |
| 운영팀 회신: 히트율 옆에 물리적 읽기 절대량과 SQL별 실행당 논리적 읽기를 함께 두자는 제안 | 3_실습과_기록.md STEP 8 |
리비전/ | 없다. Q2는 rev01 그대로 다음 화로 넘어간다 |
이 화가 다음으로 넘기는 것
| 무엇 | 어디로 |
|---|---|
Q2 rev01의 CPU 절반 = 스칼라 캐시 탐색 · 고객명 스칼라 Starts 752,995 · 오라클은 왜 스칼라를 조인으로 안 바꿨나 | 08화: 쿼리 변환 |
래치는 spin하고 락은 줄을 세운다 · 버퍼 Lock과 buffer busy waits | 12화 |
COMMIT 뒤에도 남은 dirty 31 · 체크포인트 · DBWn · Redo | 13화 |
| 스칼라 서브쿼리 캐시(한 번 실행하는 동안)와 Result Cache | 15b화 |
| AWR의 Instance Efficiency에 줄줄이 나오는 히트율들 | 16화 |
rev01의 유효기간(M_CUS가 커지면) | ← 06화 대장에 적은 그대로 · 18화에서 재측정 |
Q2는 왜 제자리인가
07화에서는 Q2를 rev02로 올리지 않는다. 재 보니 그럴 이유가 없었다. rev01의 5,869는 T_ORD_BIG 5,827 + M_CUS 30 + M_RGN 12이고, 스칼라를 외부조인으로 바꿔도 5,862(7블록 차이)다. CPU가 절반으로 준다는 사실은 기록해 둔다. 다만 이것은 "오라클이 SQL을 어떻게 고쳐 쓰는가"의 문제라서 08화의 몫이다.
← 06화 「100만 번의 왕복」 · 교재 전체 안내 · 다음 → 08화 「금액이 50배로 찍힌다」