테마
07화 · 파고들기
필요할 때 찾아보는 문서다. 처음 읽을 때는
1_사건.md만으로 충분하다.
| 찾는 것 | 바로 가기 |
|---|---|
| 히트율의 식 · 왜 건강 지표가 아닌가 | §1 히트율 |
| 버퍼 캐시는 어떻게 생겼나 (버킷 · 체인 · 버퍼 헤더) | §2 해시 테이블 |
| CBC 래치는 무엇을 지키나 | §3 CBC 래치 |
| 논리적 읽기 한 번에 일어나는 일 · examination과 pin | §4 한 번의 값 |
| 핫 블록 · 래치 경합 · Free의 한계 | §5 경합 |
| LRU · 중간점 삽입 · touch count | §6 LRU |
| 버퍼의 상태 (free / xcur / cr / dirty / pinned) | §7 상태 |
| 처음 읽는 큰 테이블이 캐시를 우회하는 경우 | §8 direct path |
| 스칼라 서브쿼리 캐시는 언제 깨지나 | §9 스칼라 캐시 |
| 히트율 대신 무엇을 보나 | §10 대신 볼 것 |
| 측정값 전부 | §11 측정 |
| 공식문서 좌표 | §12 좌표 |
1. 히트율 — 분수의 함정
식
히트율 = 1 − 물리적 읽기 ÷ 논리적 읽기
= 1 − physical reads ÷ session logical reads ← 이 화의 실습이 쓰는 식통계 (V$MYSTAT · V$SYSSTAT) | 뜻 |
|---|---|
session logical reads | 논리적 읽기. 버퍼 캐시에서 블록을 찾은 횟수 = consistent gets + db block gets |
consistent gets | 읽기 일관성 모드로 읽은 횟수 (조회는 대부분 여기에 든다) |
db block gets | 현재(current) 모드로 읽은 횟수 (주로 변경할 때) |
physical reads | 디스크(데이터 파일)에서 가져온 블록 수 = physical reads cache + physical reads direct |
CPU used by this session | 이 세션이 쓴 CPU (1/100초) |
대시보드나 도구마다 식이 조금씩 다르다. direct path 읽기(§8)를 빼는 식도 있고,
db block gets와consistent gets를 따로 더하는 식도 있다. 어느 식이든 "디스크에서 읽은 양 ÷ 메모리에서 찾은 양"으로 만든 분수라는 점은 같다. 그래서 아래 함정도 똑같이 생긴다.
실측 — 같은 디스크 읽기, 다른 히트율 (실습/00 [B])
캐시가 빈 '하루'는 복사본 T_ORD_C07(방금 만들어 아직 아무도 안 읽었다)에 Q2를 10번 돌린 것이다.
| 논리적 읽기 | 물리적 읽기 | 히트율 | CPU (1/100초) | |
|---|---|---|---|---|
| rev00 (NL 힌트) | 10,268,450 | 11,626 | 99.89% | 368~403 |
| rev01 (힌트 제거) | 58,639 | 11,626 | 80.17% | 117~121 |
첫 번째 실행만 떼어 내면 이렇다.
| 논리적 읽기 | 물리적 읽기 | 히트율 | |
|---|---|---|---|
| rev00 #1 | 1,026,853 | 5,813 | 99.43% |
| rev01 #1 | 5,872 | 5,813 | 1.00% |
📌 이 표가 07화에서 가장 중요하다
두 SQL은 디스크에서 정확히 같은 블록 수를 가져왔다. 다른 것은 분모뿐이다. rev00은
M_CUS를 실행마다 100만 번 두드리는 낭비 때문에 분모가 크다(← 06화의Starts1,000,000).히트율은 건강 지표가 아니다.
왜 건강 지표가 아닌가 — 세 가지
| 무엇이 일어나나 | 07화에서 본 것 | |
|---|---|---|
| ① 낭비가 점수가 된다 | 필요 없는 블록을 메모리에서 많이 읽을수록 분모가 커져 히트율이 오른다 | rev00의 99.89% |
| ② 개선이 경보가 된다 | 낭비를 없애면 분자는 그대로인데 분모가 줄어 히트율이 내려간다 | rev01 적용 뒤 노란불(80.17%) |
| ③ 평균이 낭비를 덮는다 | 시스템 전체 히트율은 모든 SQL을 합쳐 계산하므로, SQL 하나의 거대한 분모가 전체를 99%대로 끌어올린다 | 몇 달 동안의 99.9% |
그래도 쓸 데
히트율이 갑자기 움직이면 물어볼 거리가 생긴 것이다. 그때는 비율 말고 분자(물리적 읽기 절대량)와 분모(논리적 읽기 절대량)를 따로 본다(§10).
2. 버퍼 캐시는 해시 테이블이다
블록 주소 (DBA = 파일# + 블록#)
│ 해시 함수
▼
┌──────────────────── 해시 버킷 524,288 개 ────────────────────┐
│ [0] [1] [2] ... [k] ... │
└──────────────────────│──────────────────────────────────────┘
▼ 해시 체인 (버퍼 헤더의 연결 리스트)
[헤더]──[헤더]──[헤더]
│ │ │
[8KB] [8KB] [8KB] ← 버퍼 본문 (블록 내용)
CBC 래치 4,096 개 — 래치 하나가 버킷 128 개(= 체인 128 개)를 맡는다| 항목 | 이 컨테이너의 값 | 어디서 보나 |
|---|---|---|
| 버퍼 캐시 크기 | 864MB (DEFAULT buffer cache) | V$SGA_DYNAMIC_COMPONENTS |
| 버퍼 수 (8KB) | 110,592 | 위 ÷ 8192 |
| 해시 버킷 | 524,288 | _db_block_hash_buckets (SYS · 실습/90 [A]) |
| CBC 래치 | 4,096 | V$LATCH_CHILDREN (일반 계정도 보인다) |
| 래치 하나당 버킷 | 128 | 524,288 ÷ 4,096 |
버퍼 헤더와 버퍼 본문
체인에 매달리는 것은 8KB 블록 자체가 아니라 버퍼 헤더(블록 주소, 상태, touch count, 핀 목록 등)다. 헤더가 본문을 가리킨다. X$BH의 한 행이 버퍼 헤더 하나이고, V$BH는 그 일부를 보여 주는 뷰다.
블록 하나를 찾는 순서
1. DBA 에 해시 함수를 걸어 버킷을 정한다 ← 524,288 개 중 하나. 전부 훑지 않는다
2. 그 버킷을 맡은 CBC 래치를 잡는다 ← §3
3. 체인을 따라가며 같은 DBA 의 버퍼 헤더를 찾는다
4-a. 있으면: 들여다보고 끝(examination) 또는 핀을 꽂는다(pin) ← §4
4-b. 없으면: 빈 버퍼를 얻어(LRU, §6) 디스크에서 읽어 와 체인에 매단다 ← 이것이 물리적 읽기
5. 래치를 놓는다논리적 읽기 한 번은 1~5를 한 번 거치는 것이다. 메모리에서 끝나더라도 이 일은 매번 일어난다.
3. CBC 래치 — 데이터가 아니라 구조를 지킨다
무엇을 지키나
해시 체인은 SGA(공유 메모리)에 있고, 모든 서버 프로세스가 동시에 드나든다. 한 프로세스가 체인을 따라가는 도중에 다른 프로세스가 그 체인에서 헤더를 떼거나(버퍼 재사용) 붙이면(새로 읽어 온 블록) 포인터가 끊긴다.
cache buffers chains 래치는 체인을 따라가거나 바꾸는 그 짧은 순간을 직렬화한다.
| 지키는 것 | 잡는 시간 | |
|---|---|---|
| CBC 래치 | 구조: 체인(헤더들의 연결) | 체인을 찾고 핀을 꽂거나 빼는 순간만 |
| 버퍼 핀(버퍼 Lock) | 데이터: 버퍼 본문 | 블록을 읽거나 바꾸는 동안 |
탕비실 비유로 말하면, 집게(래치)는 칸 안의 줄을 지키고 커피를 마시는 동안에는 '가져감' 꼬리표(핀)가 지킨다. 커피를 마시는 동안에는 집게를 잡지 않는다.
06화가 남긴 질문 — PGA에는 왜 래치가 없나
| 버퍼 캐시 (SGA) | PGA | |
|---|---|---|
| 누가 쓰나 | 모든 세션이 공유 | 그 서버 프로세스 혼자 |
| 동시에 구조를 바꿀 수 있나 | 있다 → 래치 필요 | 없다 → 래치 불필요 |
| 07화에서 본 예 | 버퍼 체인 | 해시 조인의 build 테이블(06화) · 스칼라 서브쿼리 캐시(§9) |
래치는 줄을 세우지 않는다
래치를 못 잡으면 프로세스는 대개 그 자리에서 다시 시도(spin)한다. 줄(큐)을 만들지 않는다. spin으로도 못 잡으면 잠깐 잠든다(sleep). 그때 비로소 latch: cache buffers chains 대기가 찍힌다. 줄을 세워 순서를 보장하는 것은 락(enqueue)이다. 락은 12화에서 다룬다.
4. 논리적 읽기 한 번의 값 — examination과 pin
두 가지 방식
| 방식 | 하는 일 | CBC 래치 | 언제 |
|---|---|---|---|
| examination | 래치를 잡은 채로 버퍼를 들여다보고 바로 놓는다 | 1회 | 한 번 보고 끝나는 접근 (rev00이 M_CUS에서 한 행을 찾을 때) |
| pin | 래치 잡고 핀 꽂고 놓기 → 읽기 → 래치 잡고 핀 빼고 놓기 | 2회 | 한 블록에서 여러 행을 읽는 접근 (전체 스캔) |
실측 (실습/01 [A] · 5회 평균)
| 통계 | rev00 | rev01 |
|---|---|---|
session logical reads | 1,026,856 | 5,868 |
consistent gets examination | 1,018,012 | 10 |
consistent gets pin | 8,838 | 5,858 |
CBC 래치 gets (V$LATCH_CHILDREN 합계) | 1,035,706 | 11,728 |
| 논리적 읽기 한 번당 래치 | 1.009 | 1.999 |
buffer is not pinned count | 2,000,026 | 22 |
buffer is pinned count | 14,999 | 4 |
CPU used by this session (1/100초) | 37~38 | 11~12 |
physical reads | 0 | 0 |
session logical reads5,868과 06화의Buffers5,869가 한 블록 다른 것은 세는 자리가 달라서다. 앞은 세션 통계(V$MYSTAT)의 차이이고, 뒤는 실행계획 0번 줄의 값이다. 같은 실행을 잰 것이다.
📌 읽는 법
- rev00은 논리적 읽기 한 번에 래치를 한 번만 잡지만, 그 읽기를 100만 번 한다. rev01은 한 번에 두 번 잡지만 읽기 횟수가 5,868번이다. 래치를 잡은 총횟수는 88배 차이다(1,035,706 ÷ 11,728).
buffer is pinned count는 이미 핀을 꽂아 둔 버퍼를 래치 없이 다시 읽은 횟수다(버퍼 pinning). rev00은 14,999, rev01은 4다. 이 값을 오퍼레이션별로 나눠 보는 일은 이 화에서 하지 않았다.- 디스크 읽기는 둘 다 0이다. 0.37초는 전부 CPU 시간이다.
그럼 1블록에 CPU가 얼마인가
외워 둘 값이 아니다. 같은 일을 하는 두 SQL의 CPU를 나란히 재 보면 충분하다. rev00과 rev01은 같은 3행을 만드는데, CPU는 약 3배, 래치는 88배, 논리적 읽기는 175배 차이가 났다. CPU 배수가 블록 배수보다 작은 이유: rev01도 100만 행을 해시하고 집계하는 데 CPU를 쓰기 때문이다. CPU를 쓰는 곳이 블록만은 아니다(→ §9의 스칼라 캐시 탐색).
5. 핫 블록과 래치 경합 — Free에서 보이는 데까지
일이 몇 개 래치에 몰렸나 (실습/01 [B])
| 래치 gets 합계 (1회) | 건드린 래치 | 1,000번 넘게 잡힌 래치 | 그 래치들의 몫 | |
|---|---|---|---|---|
| rev00 | 약 1,037,000 | 3,852 | 33 | 1,024,135 (98.8%) |
| rev01 | 약 12,800 | 3,852 | 0 | — |
(rev01의 합계에는 래치 4,096개의 값을 받아 적는 INSERT에서 생긴 잡음이 1,000 남짓 섞여 있다. [A]의 11,728과 비교해 보자.)
그 33개는 무엇을 지키나 (실습/90 [C] · SYSDBA)
X$BH.hladdr는 이 버퍼 헤더가 매달린 체인을 지키는 래치의 주소다. 이 값을 바쁜 래치의 주소와 맞춰 본다.
| 대상 | 개수 | 래치 하나가 1회에 받은 gets |
|---|---|---|
M_CUS 데이터 블록 | 27 | 대부분 37,004~39,002 (한 블록은 23,504) |
PK_M_CUS 블록 | 6 | 약 4,006 |
| 그 래치 33개 아래 매달린 버퍼 전체 (다른 객체 포함) | 96~97 | — |
핫 블록은 짧은 시간에 접근이 몰리는 블록이다. 여기서는 M_CUS 2,000행이 들어 있는 27블록이 그랬다. 같은 래치 아래 다른 객체의 버퍼도 매달려 있다는 점에 주의해야 한다. 핫 블록 하나 때문에 그 래치를 함께 쓰는 무관한 블록까지 기다릴 수 있다.
이 화의 처방은 '덜 두드리기'였다. rev01은
M_CUS를 한 번 훑고 끝내므로 1,000번 넘게 잡히는 래치가 하나도 없다. 블록 구조를 바꿔 핫 블록을 흩는 기법(역순 키 인덱스, 해시 파티션, 블록당 행 수 줄이기 등)은 동시성이 큰 환경의 처방이고 이 교재에서는 다루지 않는다.
동시에 돌리면 (실습/03 · 한 세션이 Q2를 5번 · 두세 번씩 측정)
| 동시에 | 한 세션 경과 | 한 세션 CPU | CBC misses | spin_gets | sleeps | resmgr:cpu quantum (1인 평균) | CBC 래치 대기 |
|---|---|---|---|---|---|---|---|
| rev00 × 1 | 1.86~1.94초 | 1.86~1.94초 | 0 | 0 | 0 | 0 | 0 |
| rev00 × 2 | 1.88~1.91초 | 1.88~1.89초 | 11,736~21,512 | = misses | 0 | 0~0.02초 | 0 |
| rev00 × 4 | 2.29~3.59초 | 1.87~2.02초 | 19,887~27,698 | = misses | 0 | 0.53~1.42초 | 0 |
| rev01 × 1 | 0.61~0.63초 | 0.60~0.62초 | 0 | 0 | 0 | 0 | 0 |
| rev01 × 4 | 0.72~0.98초 | 0.61~0.65초 | 40~378 | = misses | 0 | 0.17초 | 0 |
📌 읽는 법
- misses가 생겼는데 sleeps는 0이다. 놓친 래치를 전부 spin으로 잡았다는 뜻이다. 대기 이벤트에는 아무것도 안 찍힌다. 그 대신 CPU를 쓴다.
- 경과 − CPU =
resmgr:cpu quantum(측정값이 1/100초 단위까지 일치했다). 래치가 아니라 CPU 차례를 기다린 시간이다.- misses 값은 실행마다 크게 흔들린다(세션이 뜨는 시각이 조금씩 어긋나기 때문이다). 판정 기준은 "0이 아니다"와 "sleeps는 0이다" 두 가지다.
Free에서 보이는 데까지 ★
| Free (이 교재) | 대규모 환경 | |
|---|---|---|
| CPU | 2개로 묶여 있다 (cpu_count 2. 넘치는 요구는 resmgr:cpu quantum 대기로 나타난다) | 수십 개 |
| 동시에 같은 래치를 노리는 프로세스 | 많아야 둘 | 수십~수백 |
| 래치를 놓치면 | 거의 다 spin으로 잡는다 → sleeps 0 | spin이 실패해 sleep → latch: cache buffers chains 대기 |
| CPU가 모자라면 | resmgr:cpu quantum 대기로 보인다 | OS 실행 대기열 (오라클 대기 이벤트에 안 잡힐 수 있다) |
Free에서는 misses와 spin, 그리고 CPU 차례 기다림까지 재현된다. sleep과 래치 대기 이벤트가 쌓이는 대규모 EE 환경은 별권에서 다룬다.
동시 세션을
DBMS_SCHEDULER로 띄우지 않는 이유: 이 컨테이너는job_queue_processes = 1이라 잡이 하나씩만 돈다. 그래서 셸에서sqlplus를 여러 개 띄운다.
6. LRU · touch count · 중간점 삽입
선반이 꽉 차면
빈 버퍼가 필요하면(§2의 4-b) 오라클은 LRU 체인의 차가운 끝에서 오래 안 쓴 버퍼를 골라 재사용한다. 순수한 LRU라면 큰 테이블을 한 번 전체 스캔하는 것만으로 캐시 내용이 통째로 바뀐다. 오라클은 두 가지 장치로 이를 막는다.
| 장치 | 동작 |
|---|---|
| 중간점 삽입 (mid-point insertion) | 새로 읽어 온 블록을 LRU의 맨 앞(뜨거운 끝)이 아니라 가운데에 넣는다. 한 번 읽고 마는 블록은 거기서 차가운 끝으로 밀려나 먼저 재사용된다 |
touch count (X$BH.tch) | 버퍼마다 '찾아온 횟수'를 센다. 차가운 끝에 다다른 버퍼의 touch count가 기준(_db_aging_hot_criteria = 2) 이상이면 버리지 않고 뜨거운 쪽으로 옮긴다 |
ℹ️ 위 두 장치가 실제로 동작하는 장면(밀려나고 살아남는 장면)은 이 화에서 재현하지 않았다. 버퍼 캐시 864MB를 넘칠 만큼 큰 테이블이 필요하기 때문이다. 이 화에서 잰 것은 아래의 touch count가 오르는 규칙과 기준값(파라미터)까지다. 동작의 근거는 성능고도화 I권 Ch1 「캐시 버퍼 LRU 체인」(p.28)과
cncptDatabase Buffer Cache다.
touch count는 '만진 횟수'가 아니다 (실습/90 [B] · SYSDBA)
3초 안에 다시 만진 것은 세지 않는다(_db_aging_touch_time = 3).
| 순서 | M_CUS 데이터 블록 tch | T_ORD_BIG 데이터 블록 tch |
|---|---|---|
캐시를 비우고 rev00 한 번 (M_CUS 블록마다 수만 번 · T_ORD_BIG 블록마다 1번 만짐) | 1 (27블록) | 1 (5,818블록) |
T_ORD_BIG을 4초 간격으로 3번 전체 스캔 | 1 | 4 |
| 이어서 쉬지 않고 3번 더 | 1 | 4 (그대로) |
블록마다 수만 번 만진
M_CUS는 1이고, 일곱 번 만진T_ORD_BIG은 4다. touch count는 '얼마나 세게'가 아니라 '얼마나 자주 다시 찾아왔나'를 잰다. 그래야 SQL 하나가 1초 동안 몰아친 접근으로 캐시 자리를 독차지하지 못한다.
7. 버퍼의 상태 — free / xcur / cr / dirty / pinned
V$BH.status와 dirty
| 상태 | 뜻 |
|---|---|
free | 비어 있다. 어느 블록에도 쓰이지 않는다 |
xcur | 현재(current) 버전. 이 인스턴스가 독점하는 최신 블록 |
cr | 읽기 일관성을 위해 만든 과거 시점의 사본 (→ 13화) |
read | 디스크에서 읽어 오는 중 |
dirty = 'Y' | 메모리에서 바뀌었는데 아직 데이터 파일에 쓰지 않았다 (상태가 아니라 xcur에 붙는 표시) |
책(성능고도화 I권)은 버퍼를 Free, Dirty, Pinned 셋으로 요약한다. V$BH에서 Free는 free, Dirty는 dirty='Y'로 보인다. Pinned는 순간적인 상태라 뷰에 남지 않는다. 대신 V$MYSTAT의 buffer is pinned count로 흔적을 본다(§4).
실측 — 복사본 M_CUS_C07 (실습/01 [D] · 실습/90 [D])
| 단계 | xcur 깨끗 | xcur dirty | cr |
|---|---|---|---|
CREATE TABLE ... AS SELECT 직후 | 0 | 4 (헤더 블록) | 0 |
| 전체 스캔 뒤 | 27 | 4 | 0 |
UPDATE 뒤 (미커밋) | 0 | 31 | 27 |
COMMIT 뒤 | 0 | 31 | 27 |
ALTER SYSTEM CHECKPOINT 뒤 (SYSDBA) | 31 | 0 | 27 |
- CTAS는 캐시를 거치지 않고 데이터 블록을 쓴다. 그래서 CTAS 직후에는 데이터 블록이 캐시에 없다(§8과 같은 원리).
COMMIT은 데이터 블록을 디스크에 쓰지 않는다. 체크포인트가 걸리면 DBWn이 블록을 써서 dirty가 사라진다.- 커밋할 때 무엇을 쓰길래 그래도 안전한가(Redo · WAL) → 13화
8. 처음 읽는 큰 테이블 — direct path read
실습/00 [B]에서 복사본 T_ORD_C07(5,915블록)을 처음 읽을 때는 이렇다.
| 실행 | physical reads direct | physical reads cache | 캐시에 남나 |
|---|---|---|---|
| 1번째 | 5,813 | 0 | 남지 않는다 (PGA로 바로 읽었다) |
| 2번째 | 0 | 5,813 | 남는다 |
| 3번째부터 | 0 | 0 | — |
- 전체 스캔하는 테이블이 일정 크기(
_small_table_threshold= 2,114블록, SYS ·실습/90[A])보다 크면, 오라클은 버퍼 캐시를 거치지 않고 디스크에서 프로세스 메모리로 바로 읽기도 한다. 이것이 direct path read다. - 크기가 같아도 늘 그런 것은 아니다.
실습/90[B]에서 캐시를 비운 직후T_ORD_BIG을 처음 스캔했을 때는 캐시를 거쳤다(버퍼 5,818개가 캐시에 올라와 있다). 오라클은 테이블 크기와 캐시 상태 등을 보고 방식을 고른다. 그 결정 규칙은 이 교재의 범위 밖이다. - 어느 쪽이든 디스크에서 가져온 것이고,
physical reads에 똑같이 찍힌다. 히트율의 분자로 들어간다. - 1번째에 direct로 읽은 블록은 캐시에 남지 않으므로, 2번째 실행에서 디스크를 또 읽었다. 그래서 '하루'의 물리적 읽기가 5,813이 아니라 11,626이다.
9. 스칼라 서브쿼리 캐시 — 칸이 정해진 메모판
규칙
| 스칼라 서브쿼리 캐시 | 버퍼 캐시 | |
|---|---|---|
| 어디에 | 그 SQL을 한 번 실행하는 동안 쓰는 실행 메모리 | SGA |
| 누가 쓰나 | 그 실행 혼자 | 모든 세션 |
| 열쇠 | 서브쿼리에 들어간 값 (:B1) | 블록 주소 |
| 크기 | _query_execution_cache_max_size = 131,072바이트 (SYS · 실습/90 [A]) | 864MB |
| 같은 칸에 부딪히면 | 안 적는다(포기) | 체인으로 매단다 |
| 래치 | 없다 (혼자 쓰니까) | 있다 (체인을 지켜야 하니까) |
실측 — 입력 값의 종류를 늘려 가며 (실습/02 [D])
T_ORD_BIG 100만 행에 CUS_ID가 정확히 N종류 돌아가며 나오게 하고, M_CUS를 스칼라 서브쿼리로 조회한다.
| 입력 NDV | Starts | Buffers | 값 하나가 나오는 횟수 | 자리를 얻은 값 | 못 얻은 값 |
|---|---|---|---|---|---|
| 8 | 8 | 5,843 | 125,000 | 8 | 0 |
| 64 | 93,808 | 99,643 | 15,625 | 58 | 6 |
| 128 | 132,922 | 138,757 | 7,813 | 111 | 17 |
| 256 | 215,038 | 220,873 | 3,906 | 201 | 55 |
| 512 | 357,735 | 390,922 | 1,953 | 329 | 183 |
| 1,000 | 568,432 | 595,269 | 1,000 | 432 | 568 |
| 2,000 | 752,995 | 779,832 | 500 | 495 | 1,505 |
Starts = 자리를 얻은 값 × 1 + 못 얻은 값 × (값 하나가 나오는 횟수)
자리를 얻은 값 = (1,000,000 − Starts) ÷ (값 하나가 나오는 횟수 − 1)
N = 2,000 : 752,995 = 495 × 1 + 1,505 × 500📌 읽는 법
- 64종류만 돼도 6개가 자리를 못 얻는다. 그 6개는 나올 때마다
M_CUS에 간다(6 × 15,625 = 93,750).- 06화의
M_RGN이Starts8이었던 것은 8개가 서로 부딪히지 않은 운 덕분이다. "종류가 적으면 캐시된다"는 반쪽짜리 규칙이다.- 같은 고객명을 조인으로 가져오면
Buffers가 5,856이다(실습/02[B]). 스칼라의 779,832보다 133배 적다.
칸 수가 원인인가 — 메모판을 키워 보면 (실습/90 [E] · SYSDBA · 세션 한정)
_query_execution_cache_max_size | 입력 NDV 2,000의 Starts |
|---|---|
| 131,072 (기본값) | 752,995 |
| 1,048,576 (8배) | 197,608 |
⚠️ 밑줄 파라미터는 운영에서 돌리는 손잡이가 아니다. 원인이 '칸 수'인지 확인하려는 실험일 뿐이다. 8배로 키워도 0이 되지 않는다는 점도 눈여겨보자. 부딪히면 포기하는 규칙은 그대로이기 때문이다.
열쇠는 결과가 아니라 입력이다 (실습/02 [C])
sql
(SELECT r.RGN_NM FROM M_RGN r WHERE r.RGN_ID = 'RGN0' || (MOD(o.ORD_SEQ, 8) + 1))나오는 지역명은 8개뿐이지만 서브쿼리에 들어가는 값은 ORD_SEQ 100만 개다. Predicate에는 MOD(:B1, 8)로 보인다. M_RGN의 Starts는 1,000,000이다.
히트도 공짜가 아니다 (실습/02 [E])
Q2 rev01의 스칼라(Starts 8)를 외부조인으로 바꾼 문장과 비교한다. 결과는 양방향 MINUS가 0행이라 같다.
Buffers / 회 | CPU (10회 합계, 1/100초) | |
|---|---|---|
| rev01: 스칼라 서브쿼리 | 5,868 | 119~121 |
스칼라 → 외부조인 (M_RGN r ... r.RGN_ID(+) = c.RGN_ID) | 5,862 | 60~63 |
M_RGN은 8번 열렸지만 메모판은 100만 번 들여다봤다. 그 탐색이 rev01 CPU의 약 절반을 차지한다.Buffers는 6개 차이라 T0(Buffers로 말한다) 기준으로는 개선이 아니다. 06화의 TEMP에 이어 T0가 못 보는 두 번째 길이다. 이 화에서는 Q2의 리비전을 만들지 않는다. "오라클은 왜 이 스칼라를 조인으로 안 바꿨나"는 쿼리 변환에 관한 질문이고, 08화에서 다룬다. (실측: 이 버전의 옵티마이저는 힌트가 없어도 이 스칼라 서브쿼리들을 조인으로 바꾸지 않았다.실습/02[B]의 실행계획을 보면 알 수 있다.)
결과 캐시와 다르다
스칼라 서브쿼리 캐시는 한 번 실행하는 동안만 살아 있다. 실행이 끝나면 사라지고, 다른 세션과 나눠 쓰지 않는다. 실행이 끝난 뒤에도 결과를 재사용하는 Result Cache와 그 대가는 15b화에서 다룬다.
10. 히트율 대신 무엇을 보나
| 묻고 싶은 것 | 볼 숫자 | 어디서 |
|---|---|---|
| 디스크를 정말 더 읽나 | physical reads의 절대량 (구간 차이) | V$SYSSTAT · V$MYSTAT |
| 서버가 타나 | CPU (CPU used by this session 합계 · DB CPU) | V$SYSSTAT · V$SYS_TIME_MODEL (→ 16화) |
| 누가 태우나 | SQL별 실행당 논리적 읽기 (buffer_gets / executions) | V$SQL · V$SQLSTATS |
| 왜 그 SQL이 많이 읽나 | Starts · Buffers | DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST') |
sql
-- 실행당 논리적 읽기 상위 10 개 (이 PDB)
SELECT * FROM (
SELECT sql_id, executions, buffer_gets,
ROUND(buffer_gets / GREATEST(executions, 1)) AS gets_per_exec,
ROUND(cpu_time / 1e6 / GREATEST(executions, 1), 3) AS cpu_sec_per_exec,
SUBSTR(sql_text, 1, 60) AS sql_text
FROM v$sql
WHERE executions > 0
ORDER BY buffer_gets DESC)
WHERE ROWNUM <= 10;운영 대시보드에서 히트율을 지울 필요는 없다. 옆에 분자와 분모를 따로 놓으면 된다. 시스템 전체를 한 장으로 보는 법(AWR의 Load Profile, Instance Efficiency, SQL ordered by Gets)은 16화에서 다룬다.
11. 이 화의 측정값 전부
모두 ora-hanorder에서 잰 값이다. 환경: Oracle AI Database 26ai Free 23.26.2.0.0 · cpu_count 2 · db_block_size 8192 · 버퍼 캐시 864MB · pga_aggregate_target 512M · 히스토그램 없음(FOR ALL COLUMNS SIZE 1). 데이터: T_ORD_BIG 1,000,000 / M_CUS 2,000 / M_RGN 8 · 데이터 지문 SUM(ORD_AMT) = 255,398,623,700. 각 스크립트를 두 번 이상 돌려 같은 값이 나오는지(또는 아래 적은 범위 안인지) 확인했다.
캐시가 따뜻할 때 (실습/00 [A] · 1회)
| 논리적 읽기 | 물리적 읽기 | CPU (1/100초) | 히트율 | |
|---|---|---|---|---|
| rev00 | 1,026,849 | 0 | 36~44 | 100.00% |
| rev01 | 5,868~5,870 | 0 | 11~13 | 100.00% |
캐시가 빈 하루 (실습/00 [B] · 복사본 · 10회)
| #1 | #2 | #3~#10 | 하루 합계 | 히트율 | |
|---|---|---|---|---|---|
| rev00 논리 / 물리 | 1,026,853 / 5,813 (direct) | 1,026,844 / 5,813 (cache) | 1,026,844 / 0 | 10,268,449~10,268,451 / 11,626 | 99.89% |
| rev01 논리 / 물리 | 5,872~5,874 / 5,813 (direct) | 5,863 / 5,813 (cache) | 5,863 / 0 | 58,639~58,641 / 11,626 | 80.17% |
#1만의 히트율: rev00 99.43% · rev01 1.00~1.04%.
래치 (실습/01 · 실습/90)
| 항목 | 값 |
|---|---|
| CBC 래치 수 | 4,096 |
| 해시 버킷 수 | 524,288 |
| rev00 · rev01 1회 CBC 래치 gets | 1,035,706~1,035,747 · 11,727~11,728 |
| 논리적 읽기당 래치 | 1.009 · 1.998~1.999 |
| rev00 1회에 1,000번 넘게 잡힌 래치 | 33개 · 1,024,135 gets (98.8~98.9%) |
| 그 래치가 지키는 블록 | M_CUS 27 · PK_M_CUS 6 |
동시 실행 (실습/03)
→ §5 표
touch count · 버퍼 상태 (실습/90 · 실습/01)
스칼라 서브쿼리 (실습/02)
Starts | Buffers | |
|---|---|---|
[A] 입력 RGN_ID 8종류 (Q2 rev01) | 8 | 12 (전체 5,869) |
[B] 입력 CUS_ID 2,000종류 | 752,995 | 779,832 (M_CUS 774,006 · PK_M_CUS 21,011) |
| [B] 같은 답을 조인으로 | — | 5,856 |
[C] 입력 ORD_SEQ 100만 종류 (결과 8종류) | 1,000,000 | 1,005K |
| [D] 스윕 | → §9 표 | |
| [E] rev01 vs 외부조인 | — | 5,868 vs 5,862 · CPU 119~121 vs 60~63 (10회) |
Q2를 더 줄일 수 있나 — 리비전을 만들지 않은 근거
| 문장 | Buffers | 비고 |
|---|---|---|
| rev01 그대로 | 5,869 | T_ORD_BIG 5,827 + M_CUS 30 + M_RGN 12 |
| 스칼라 → 외부조인 | 5,862 | 7블록 (0.12%) · CPU는 절반 → 08화 |
T_ORD_BIG 전체 스캔 한 번 | 5,826~5,827 | 바닥. 100만 행을 읽어야 하는 요구 자체 → 08화 |
12. 원천 좌표
전체 지도 →
00_자료준비/06_원서와_공식문서_지도.mdPDF 페이지 = 책 페이지 − 2 ·성능고도화I_목차색인.md(공개 사이트에는 없음)
성능고도화 I권 (주 원천)
| 주제 | 권-장-절 | 책 페이지 |
|---|---|---|
| DB 버퍼 캐시: 블록 단위 I/O · 버퍼 캐시 구조 | I권 Ch1 | 23 · 24 |
| 캐시 버퍼 체인 (해시 체인과 CBC 래치) | I권 Ch1 | 26 |
| 캐시 버퍼 LRU 체인 | I권 Ch1 | 28 |
| 버퍼 Lock · 버퍼 핸들 · 버퍼 Lock의 필요성 | I권 Ch1 | 30~32 |
| 버퍼 Pinning | I권 Ch1 | 33 |
| 래치와 대기 이벤트 개념 명확화 | I권 Ch1 (대기 이벤트) | 92 |
| 스칼라 서브쿼리의 캐싱 효과 | I권 (PL/SQL 함수 호출 부하 해소) | 417 |
| Memory vs. Disk I/O · 버퍼 캐시 히트율 | I권 (I/O 효율화 원리) | 440 · 441 |
| Direct Path I/O | I권 (I/O 효율화 원리) | 457~459 |
성능고도화 II권 Ch2 07 「스칼라 서브쿼리를 이용한 조인 · 캐싱 효과」(책 p.284 · 285)도 같은 주제를 다룬다(← 06화가 가리킨 좌표).
오라클 공식문서
| 주제 | 매뉴얼 | 장 제목 |
|---|---|---|
| 버퍼 캐시의 구조와 상태 | cncpt | Memory Architecture: Database Buffer Cache |
| 버퍼 캐시 튜닝 · 히트율 | tgdba | Tuning the Database Buffer Cache |
래치 대기 · buffer busy waits | tgdba | Instance Tuning Using Performance Views: Table of Wait Events and Potential Causes |
V$BH · V$LATCH_CHILDREN · V$MYSTAT · 통계 이름 | refrn | Dynamic Performance Views · Statistics Descriptions |
이 화에서 꼭 확인할 뷰
sql
V$MYSTAT / V$STATNAME -- 내 세션의 누적 통계. 앞뒤 차이가 그 SQL 몫
V$SYSSTAT -- 이 PDB(컨테이너) 전체의 누적 통계
V$LATCH_CHILDREN -- 'cache buffers chains' 4,096 개 각각의 gets · misses · sleeps · spin_gets
V$BH -- 버퍼 한 칸 = 한 행. objd · status · dirty
V$SESSION_EVENT -- 세션별 대기 (resmgr:cpu quantum · latch: cache buffers chains)
X$BH -- (SYSDBA) 버퍼 헤더. tch · hladdr · state
V$LATCH의cache buffers chains한 줄(부모)은 이 버전에서 실시간으로 갱신되지 않았다(실측: 부모 줄의 값이 Q2 실행 전후로 그대로였다). 그래서 측정은V$LATCH_CHILDREN의 합계로 한다.
다음 화가 여기서 가져가는 것
| 화 | 가져가는 것 |
|---|---|
| 08 | Q2 rev01의 CPU 절반은 스칼라 캐시 탐색(5,868 vs 5,862 · CPU 119~121 vs 60~63). 고객명 스칼라(입력 NDV 2,000)의 Starts 752,995. 오라클이 스칼라를 조인으로 바꾸는 조건, 곧 쿼리 변환. 그리고 06화가 넘긴 "100만 행을 읽어 3행" |
| 12 | 래치는 spin하고 락은 줄을 세운다(Latch vs Enqueue). 버퍼 Lock과 buffer busy waits |
| 13 | COMMIT 뒤에도 남은 dirty 31 · cr 27 · 체크포인트 · DBWn · Redo와 WAL |
| 15b | 캐싱 계층: 스칼라 서브쿼리 캐시(한 번 실행하는 동안)와 Result Cache(실행이 끝난 뒤에도) |
| 16 | AWR의 Instance Efficiency에 줄줄이 나오는 히트율 · Load Profile의 logical reads/s · physical reads/s |