테마
15b화 · 파고들기
필요할 때 찾아보는 문서다. 처음 읽을 때는
1_사건.md만으로 충분하다. 이 문서의 숫자는 모두 실측값이다.ora-hanorder(15화 종료 상태)의 복사본(_C15B)에서 쟀고, 스크립트 번호를 함께 적는다. 모든 스크립트를 두 번 돌려 같은 판정이 나오는 것을 확인했다(판마다 달라지는 것은 그렇다고 적었다). 경과 시간(cs = 1/100초)은 그날 장비 사정을 탄다. 판정은 함수 본문 실행 수(body), 결과 캐시 찾기/만들기/밀어내기, 논리 읽기로 하고, 초는 보조로만 읽는다.
| 찾는 것 | 바로 가기 |
|---|---|
| 네 겹의 캐시: 무엇을 · 어디에 · 누가 함께 · 언제 사라지나 | §1 캐싱 4계층 |
/*+ RESULT_CACHE */ · 계획의 RESULT CACHE 연산자 · V$RESULT_CACHE_OBJECTS · Bypass | §2 Server Result Cache |
RESULT_CACHE 함수 · 칸(1,984) · 답 하나 = 칸 하나 · 예외 · MEMORY_REPORT | §3 PL/SQL Function Result Cache |
무효화: 커밋 전 · 뒤 · ROLLBACK · 같은 값 · 한 명 → 전부 · 컴파일 · RELIES_ON | §4 무효화 |
☠️ 비결정적 함수: 시계 · 세션 시간대 · NLS · 세션 상태 · 재현 방법 · TRUSTED / ENFORCED | §5 숨은 입력 |
DETERMINISTIC: 약속 · 한 번의 페치 · 스칼라 서브쿼리와 같은 칸 | §6 DETERMINISTIC |
RESULT_CACHE_MODE MANUAL / FORCE | §7 모드 |
| 결과 캐시의 래치 · 둘이 동시에 찾으면 | §8 경합 |
| 08화 체크리스트에 '시각'이 들어가는 자리 · 숨은 입력 함수를 시험하는 법 | §9 정합성 |
결과 캐시를 붙이기 전에 묻는 것 · SDT_rev01 | §10 판단 |
| 측정값 전부 · 측정 환경 | §11 측정 |
| Free에서 못 잰 것 · 재지 않은 것 | §12 한계 |
| 원천 좌표 | §13 좌표 |
1. 캐싱 4계층 — 무엇을, 어디에, 얼마나 오래
같은 일을 두 번째 할 때 비용이 덜 드는 이유는 넷 중 하나(또는 여럿)다. 넷은 무효화 조건, 공유 범위, 재는 지표가 모두 다르다.
| 계층 | 담는 것 | 어디에 | 누가 함께 쓰나 | 언제 사라지나 | 재는 것 | 이 화 실측 (실습/02) |
|---|---|---|---|---|---|---|
| 버퍼 캐시(← 07화) | 블록(재료) | SGA | 모든 세션 | LRU로 밀릴 때 | Buffers · physical reads | Q2 한 번 5,868 · 열 번 58,670 ~ 58,688. 디스크 0, 매번 다 읽는다 |
| Server Result Cache | 문장의 답 | SGA(결과 캐시) | 모든 세션 | 의존 객체가 바뀌어 커밋 · 밀려남 · FLUSH | V$RESULT_CACHE_* · 계획의 RESULT CACHE | Q2 첫 번 5,867 → 다음 아홉 번 0 · 옆 세션 0 |
| PL/SQL Function Result Cache | 함수의 답(인자마다) | SGA(같은 결과 캐시) | 모든 세션 | 위 + 함수 재컴파일 | 함수 본문 실행 수 · 찾기 · 만들기 · 밀어내기 | 예상 보정액 100,000 → 14,467 / 12,551(칸이 모자람) · 1,500명이면 1,500 / 0 |
| 스칼라 서브쿼리 캐시(← 07 · 08화) | 입력 → 답 | 그 실행의 메모리 | 그 실행 혼자 | 문장이 끝날 때 | Starts | 예상 보정액 100,000 → 57,664(두 번 다) |
(계층이 아니다) DETERMINISTIC | — | 한 번의 페치 동안 같은 장치 | 그 페치 혼자 | 페치가 끝날 때 | 함수 본문 실행 수 | 57,664: 스칼라 서브쿼리와 같은 수(§6) |
한 줄 요약
- 버퍼 캐시는 디스크에서 다시 읽지 않게 해 줄 뿐이다. 계산은 매번 다시 한다.
- 결과 캐시는 계산을 통째로 건너뛴다. 대신 '같은 질문'인지를 인자와 의존 객체만 보고 판단한다(§5).
- 스칼라 서브쿼리 캐시는 한 문장 안에서만 쓰는, 칸 수가 정해진 메모판이다(부딪히면 적지 않는다. 07화 §9).
DETERMINISTIC은 저장소가 아니라 약속이다. 오라클은 그 약속을 믿고 한 페치 안에서만 같은 장치를 쓴다.
비유 — 7층 복도의 화이트보드
| 사무실 | 오라클 |
|---|---|
| 지하 카페 → 탕비실 픽업 선반(캐리어 = 재료, ← 07화) | 버퍼 캐시: 블록 |
| 7층 정산팀 앞 「자주 묻는 것」 화이트보드: 한 번 구한 답을 모두가 지나가며 읽는다 | 결과 캐시: 계산이 끝난 답 |
| 보드 아래 "규정집이 바뀌면 그 칸을 지웁니다" | 자동 무효화: 의존 객체가 바뀌어 커밋되면 지운다 |
| "오늘 등록하면 정산일은? → 6/14 (금)": 달력에 기대는 칸. 지우는 규칙에 달력은 없다 | 비결정적 함수의 답: 시계는 의존 객체가 아니다 |
| 보드 칸은 열두 개라 고객 2,000명의 등급은 다 못 적는다 | 결과 캐시 1,984칸 · 답 하나 = 칸 하나 |
| 규정집 한 줄이 바뀌면 그 규정집에 기대는 칸 전부를 지운다 | 고객 한 명 고쳐 커밋 → 1,500칸 전부 Invalid |
| 보드 앞이 좁아서 둘이 동시에 들여다보면 한 사람은 어깨 너머로 기다린다 | 결과 캐시 래치: 둘이면 각자 두 배 가까이(§8) |
| 내 책상 위 메모판(← 07화) · 책상 위 등급표(← 15화) | 스칼라 서브쿼리 캐시 · 지역 변수 / 패키지 변수 |
| 조민우 씨가 답을 지우고 규칙을 적었다: "18시 전 = 오늘 · 18시 후 = 내일" | SDT_rev01: 답을 저장하지 않고, 입력(시각)을 받아 매번 규칙으로 계산 |
2. Server Result Cache — 문장의 답
sql
SELECT /*+ RESULT_CACHE */ c.CUS_GD, COUNT(*), SUM(o.ORD_AMT), MAX((SELECT r.RGN_NM FROM M_RGN r WHERE r.RGN_ID = c.RGN_ID))
FROM T_ORD_BIG o, M_CUS_C15B c WHERE c.CUS_ID = o.CUS_ID GROUP BY c.CUS_GD;2.1 계획 읽기 (실습/02 [B])
xplan
첫 번째 실행 두 번째 실행
| Id | Operation | Starts | A-Rows | Buffers | | Starts | A-Rows | Buffers |
| 1 | TABLE ACCESS BY INDEX ROWID| 8 | 8 | 12 | | 0 | 0 | |
| 3 | RESULT CACHE | 1 | 3 | 5867 | | 1 | 3 | |
| 4 | HASH GROUP BY | 1 | 3 | 5867 | | 0 | 0 | |
| 7 | TABLE ACCESS FULL T_ORD_BIG 1 | 1000K| 5826 | | 0 | 0 | |RESULT CACHE연산자의Name칸이 결과 캐시 ID다(Result Cache Information섹션이 따라붙는다). Q2의 ID는 판마다(00부터 다시 돌릴 때마다) 달랐다(7vs3vs… · 0h96sv… · au453v… …).M_RGN을 읽는 '초' 판 문장의 ID(8rrsm8…)는 판마다 같았는데, 무엇이 ID를 바꾸는지는 확인하지 못했다. 같은 판 안에서는 주석과 힌트만 다른 두 문장(02[B] ·04[FA])이 일곱 판 모두 같은 ID를 받았다. 주석은 열쇠에 들어가지 않는다.- 두 번째 실행부터는 연산자 아래 줄들의
Starts가 0이다. 아래 연산이 돌지 않았다는 뜻이다. 읽은 블록이 0이라Buffers칸 자체가 출력에서 빠진다. - 세션 수첩으로 보면 첫 실행은 논리 읽기 5,867 ~ 5,898 ·
Create Count Success+1이고, 다음 아홉 번은 0 ·Find Count+9다. - 옆 세션(
일꾼.sql)이 같은 문장을 물으면 논리 읽기 0에 같은 답(A 10000 / 2564322100)이 나온다. V$RESULT_CACHE_OBJECTS에서 본 그 결과:TYPE Result·NAMESPACE SQL·STATUS Published·ROW_COUNT 1·BLOCK_COUNT 1·SCAN_COUNT 10(W_Q2RC_C15B: 답을 한 줄로 접은 모양).
2.2 큰 결과는 조용히 Bypass (실습/02 [G])
| 파라미터 | 이 DB | 뜻 |
|---|---|---|
result_cache_max_size | 8,126,464(보고: 7,936KB · 1,984블록 × 4KB) | 결과 캐시 전체 |
result_cache_max_result | 5(%) | 한 결과의 상한 → 49블록 = 196KB(MEMORY_REPORT) |
정산 복사본 10만 행을 통째로 돌려주는 문장에 힌트를 붙이면 결과는 STATUS Bypass · ROW_COUNT 0이 된다. 두 번 다 논리 읽기는 1,548 그대로였다. 에러도 경고도 없다. 힌트를 붙였다고 캐시되는 것은 아니므로, V$RESULT_CACHE_OBJECTS의 STATUS를 봐야 알 수 있다.
23ai에는 큰 결과를 임시 테이블스페이스에 두는 장치가 있다(
result_cache_max_temp_size81,264,640 ·Temp Block Count Maximum19,840). 그러나 이 정산 10만 행 결과는 그리로 가지 않고 Bypass됐다. 이 장치가 어떤 조건에서 쓰이는지는 재지 않았다(§12).
2.3 08화가 넘긴 질문 — 같은 3행을 실행마다 다시 계산하지 않기
답: 이 연습 DB에서는 5,868 → 0. 그런데 Q2가 기대는 T_ORD_BIG은 운영에서 주문이 들어올 때마다 커밋된다. 기대는 테이블이 커밋되면 결과가 지워진다(§4). 매번 바뀌는 테이블에 결과 캐시를 붙이면 어떻게 되는지는 04 [E]에 있다. 1,000번 만들고 한 번도 못 찾았다. 그래서 Q2에는 붙이지 않는다(보류). 연습 DB의 주문은 움직이지 않으니 0이 나온 것이다. 이 데이터에서 같다고 운영에서 맞는 것은 아니다(← 02 · 08화).
3. PL/SQL Function Result Cache — 함수의 답을 적어 두는 보드
sql
FUNCTION FN_ADJ_RATE(p_cus_id VARCHAR2) RETURN NUMBER RESULT_CACHE IS -- 한 단어
v_gd M_CUS.CUS_GD%TYPE;
BEGIN
SELECT CUS_GD INTO v_gd FROM M_CUS WHERE CUS_ID = p_cus_id; -- 오라클이 M_CUS 를 의존 객체로 스스로 적는다
RETURN CASE v_gd WHEN 'A' THEN 0.001 WHEN 'B' THEN 0.002 ELSE 0.003 END;
END;3.1 열쇠는 인자다 — 그리고 인자뿐이다
- 같은 인자로 부르면 본문을 돌리지 않고 저장된 답을 준다(
실습/01[B]: 첫 호출 본문 1 · 다음 0 · 0). - 모든 세션이 함께 쓴다(
01b·02[D] 옆 세션 본문 0). - 인자 밖에서 들어오는 입력(시계, 세션 설정, 세션 상태)은 열쇠에 들어가지 않는다(§5).
result_cache_execution_threshold= 2(이 DB 기본값). 이 화의 측정에서는 첫 호출에 바로 저장됐다(본문 1 ·Create Count Success+1 · 다음 호출 본문 0). 이 파라미터가 어떤 경우에 저장을 늦추는지는 재지 않았다.
3.2 ☠️ 칸이 있다 — 답 하나 = 칸 하나 (실습/02 [C] [D])
DBMS_RESULT_CACHE.MEMORY_REPORT(고객 2,000명의 예상 보정액을 한 번 돌린 뒤)
Block Size = 4K bytes
Maximum Cache Size = 7936K bytes (1984 blocks)
Maximum Result Size = 196K bytes (49 blocks)
Total Memory = 8392416 bytes [1.667% of the Shared Pool]
....... Cache Memory = 7936K bytes (1984 blocks)
........... Unused Memory = 10 blocks
........... Used Memory = 1974 blocks
............... Dependencies = 4 blocks (4 count)
............... Results = 1970 blocks
................... PLSQL = 1970 blocks (1970 count)보정률(숫자 하나)이 4KB 칸 하나를 통째로 쓴다. 1,984칸에 고객 2,000명은 들어가지 않는다.
예상 보정액 · RESULT_CACHE 함수 | 본문 | 찾기 | 만들기 | 밀어내기 | 경과 | 논리 읽기 |
|---|---|---|---|---|---|---|
| 고객 2,000명 (정산 10만 행) 첫 판 | 14,467 | 85,533 | 14,467 | 12,497 | 17 ~ 18cs | 43,950 ~ 43,988 |
| 고객 2,000명 두 번째 판 | 12,551 | 87,449 | 12,551 | 12,551 | 16 ~ 17cs | 38,180 ~ 38,202 |
| 고객 1,500명 (정산 75,000행) 첫 판 | 1,500 | 73,500 | 1,500 | 0 | 8cs | 5,033 ~ 5,055 |
| 고객 1,500명 두 번째 판 | 0 | 75,000 | 0 | 0 | 7cs | 527 ~ 549 |
| 고객 1,500명 · 옆 세션 | 0 | 564 | ||||
| (비교) 1,500명 그냥 함수 | 75,000 | 29 ~ 32cs | 225,527 ~ 225,549 |
- 2,000명 판의 '밀어내기'는
V$RESULT_CACHE_STATISTICS의Delete Count Valid증가분이다. 아직 쓸 수 있는 답이 자리가 없어 지워진 횟수다. - 칸이 모자라도 결과 캐시 판이 넷 중 가장 빨랐다(16 ~ 18cs, 그냥 함수 39 ~ 43, 스칼라 23 ~ 26). '두 번째는 0'이라는 기대가 틀렸을 뿐이다.
- 운영에 다시 붙이기 전에 볼 것: 운영의
result_cache_max_size, 고객 수, 결과 캐시를 함께 쓰는 다른 결과들.
3.3 같은 보드를 함께 쓴다 — 누가 밀려날지는 고르지 못한다 (실습/02 [E])
Q2 결과 둘(커서로 3행을 받는 모양, 한 줄로 접은 모양)을 올려 두고 고객 2,000명 판을 한 번 돌렸다(밀어내기 12,522 ~ 12,524). 그 뒤 Q2를 다시 물었다. 여덟 번 돌려 세 가지 결과가 나왔다. 커서 모양만 밀려남 4번, 둘 다 밀려남 1번, 둘 다 남음 3번이다. 밀려난 쪽은 5,867블록을 다시 읽었다. 04 [C]에서도 한 번 묻고 마는 조회 2,000개를 돌린 뒤 Q2 결과가 남은 판(열 번 중 5번)과 밀려난 판(5번)이 갈렸다.
3.4 예외는 저장되지 않는다 (실습/03 [H])
마스터에 없는 고객(15화의 탈퇴 고객 모양)을 다섯 번 부르면 본문도 다섯 번, NO_DATA_FOUND도 다섯 번이다. 실패한 질문은 매번 다시 계산된다.
4. 무효화 — 무엇이 지우고, 무엇이 안 지우나
4.1 의존 객체 (실습/01 [A] · 04 [D])
V$RESULT_CACHE_DEPENDENCY는 결과(RESULT_ID)와 그 결과가 기대는 객체(DEPEND_ID)를 잇는다.
| 결과 | 기대는 것 |
|---|---|
| 보정률 함수의 답 | 함수 자신 · 함수 안의 SELECT가 읽은 M_CUS_C15B |
| 정산일 함수의 답 | 함수 자신 하나(SYSDATE는 객체가 아니다) |
FORCE가 바깥 문장째 캐시한 보정 합계(SELECT SUM(… FN_ADJ_RATE_C15B(CUS_ID)) FROM T_ORD_STLM_C15B) | 정산 복사본 · 함수 · 함수가 읽은 고객 마스터 복사본. 고객 한 명을 고쳐 커밋하면 지워지고, 다시 계산한 합계 73,741,702 = MANUAL로 잰 값 |
RELIES_ON (M_CUS)를 직접 적으면 PLW-05019: the language element near keyword RELIES_ON is deprecated beginning with version 11.2 경고가 나온다(실습/03[G]). 함수 안에서 읽은 테이블은 오라클이 스스로 적는다.- 의존 객체 줄이 같은 이름으로 두 줄 나온다(
SQLP.M_CUS_C15B·INVALIDATIONS1과 0). 이 DB의 결과 캐시는 두 묶음(Subcache Count2 ·SUBCACHE_ID0 · 1)으로 나뉘어 있다. 시운전에서 같은 함수의 답이 두 묶음에 따로 생긴 적이 있었는데, 언제 그렇게 되는지는 재현이 일정하지 않아 판정에 쓰지 않았다.
4.2 무엇을 하면 지워지나 (실습/03 · 옆 세션 = 일꾼.sql)
| 고객 마스터 복사본에 한 일 | 결과 | 다음 실행 |
|---|---|---|
CUS_000002 등급 C → A · 커밋 전 | Published 그대로 | 옆 세션: 논리 읽기 0 · 옛 답 A 10000 / 2564322100 · 내 세션: 캐시를 건너뛰고 다시 계산(5,867 · 찾기 0 · 만들기 0) · 새 답 A 10500 / 2688614600 |
| 위를 커밋 | Invalid(의존 객체 INVALIDATIONS +1) | 옆 세션 5,868(다시 계산 · 새 답) → 그다음 내 세션 0 |
고치고 ROLLBACK | Published 그대로 | 0 · 찾기 1 |
같은 값으로 덮어쓰고 커밋(CUS_GD = CUS_GD) | Invalid | 5,867 · 만들기 1. 값이 아니라 '고쳐졌다'는 사실로 지운다 |
| 보정률 1,500칸을 채운 뒤 상관없는 테이블(정산 복사본)에 한 줄 넣고 커밋 | 1,500칸 Published 그대로 | — |
| 보정률 1,500칸을 채운 뒤 고객 한 명의 등급 B → A 커밋 | 1,500칸 전부 Invalid | 본문 1,500(한 명이 아니라 전부) · 그 고객의 보정률 0.001 |
함수를 다시 컴파일(ALTER FUNCTION … COMPILE) | 그 함수의 답 전부 Invalid | — |
DBMS_RESULT_CACHE.INVALIDATE(owner, name) | 그 객체에 기대는 답 전부 Invalid(실습/01 [G]) | — |
읽는 법
- 지우는 신호는 '의존 객체가 바뀌어 커밋됐다'는 사실 하나다. 값이 같아도 지우고, 되돌리면 지우지 않는다.
- 커밋 전에 남의 세션이 옛 답을 받는 것은 맞는 동작이다. 커밋 안 된 변경은 원래 남에게 보이지 않는다. 변경한 세션 자신만 캐시를 건너뛴다.
- 테이블 단위로 기억한다. 행 하나가 바뀌어도 그 테이블에 기대는 답이 전부 지워진다. 그래서 기대는 테이블이 드물게 바뀔 때만 이득이다.
- 시계, 세션 설정, 세션 상태는 의존 객체가 아니다. 여기에 기대는 답은 아무도 지우지 않는다(§5).
5. ☠️ 비결정적 함수 — 숨은 입력이 있는 함수에 캐시를 붙이면
5.1 숨은 입력 넷 — 모두 '처음 부른 세션의 그 순간'이 계속 남는다
| 숨은 입력 | 함수 (실습/00) | 재현 | 캐시 없는 판 | RESULT_CACHE 판 |
|---|---|---|---|---|
| 시계(초) | RETURN SYSDATE | 2초 간격 세 번(01 [B]) | 17:10:22 · 24 · 26 | 17:10:22 · 22 · 22(본문 1 · 0 · 0) |
| 시계(초): 목록 SQL에서 | WHERE STLM_DT <= '초' 판 · 2초 뒤 한 줄 등록(01 [C]) | 100,001 | 100,000(MINUS 1 / 0 · 빠진 것 = 방금 등록한 900002) | |
| 시계(날짜): 세션 시간대 | TRUNC(CURRENT_DATE + 6/24) · -11:00 다음 +13:00(01 [D]) | 같은 순간 24시간 차이 | D-1 → D | D-1 → D-1 |
| 세션 NLS | TO_CHAR(날짜) 형식 없이(01 [E1]) | MM-DD 다음 DD/MM | 06-14 → 14/06 | 06-14 → 06-14 |
| 세션 상태 | SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')(01 [E2]) | 직원 kim 다음 lee | owner=kim → owner=lee | owner=kim → owner=kim |
위 넷: 다른 세션(01b) | 새 세션 · +13:00 · DD.MM · 직원 park | 그 세션의 것(D · 14.06 · owner=park) | 01 세션의 것(얼린 시각 · D-1 · 06-14 · owner=kim) |
5.2 '날짜가 바뀐 상황'을 어떻게 만들었나 — 컨테이너 시계는 바꾸지 않았다
| 방법 | 쓴 곳 | 정직하게 적는 것 |
|---|---|---|
같은 모양 · 단위만 초: 정산일 함수에서 TRUNC만 뺀 RETURN SYSDATE | 01 [B] [C] · 01b | 진짜 SYSDATE, 진짜 시간 경과. 다른 것은 단위 하나뿐이다(하루 대신 2초) |
세션 시간대: ALTER SESSION SET TIME_ZONE을 -11:00 · +13:00으로(24시간 차이) | 01 [D] · 01b [B] | SYSDATE는 세션 시간대를 따르지 않는다(두 세션의 sysdate는 같은 시각, current_date만 24시간 이동). 그래서 이 판은 CURRENT_DATE로 쓴 판이다. 운영 함수(SYSDATE)와 다르다 |
(쓰지 않음) FIXED_DATE: SYSDATE를 고정하는 파라미터 | 시운전 1회 | PDB 수준에서 바꿀 수 있었다(ISPDB_MODIFIABLE TRUE). 그러나 NONE으로 되돌린 뒤에도 PDB 설정(pdb_spfile$)에 fixed_date = 'NONE' 한 줄이 남았고, ALTER SYSTEM RESET은 ORA-32010으로 실패해 그 줄을 지우지 못했다. 그래서 이 교재에서는 쓰지 않는다 |
| (쓰지 않음) 컨테이너 시계 변경 | — | 다른 측정 · AWR 스냅샷 시각이 함께 틀어진다 |
5.3 오라클이 거절하는 것 · 받아 주는 것 (실습/01 [F] · 93)
같은 /*+ RESULT_CACHE */를 단 문장을 2초 간격으로 두 번씩 돌렸다.
| 문장 | TRUSTED (이 DB 기본값) | ENFORCED (93 · SYSDBA · 몇 초) |
|---|---|---|
F1 SYSDATE를 문장에 직접 | 거절: 계획에 RESULT CACHE 없음 · 시각이 간다 | 거절 |
F4 SYSTIMESTAMP를 문장에 직접 · F5 CURRENT_DATE를 문장에 직접 | 거절 | (재지 않음) |
F2 SYSDATE를 숨긴 그냥 함수를 부름 | 받아 줌: RESULT CACHE · 시각이 멈춘다 | 거절: 시각이 간다 |
F3 같은 함수에 DETERMINISTIC 표시를 붙임 | 받아 줌 · 멈춘다 | 받아 줌 · 멈춘다 |
result_cache_integrity의 값은TRUSTED와ENFORCED둘이다(STRICT를 넣으면ORA-00096, 실측). 세션 단위로는 바꿀 수 없다(ISSES_MODIFIABLE FALSE). 그래서93은 PDB를SCOPE = MEMORY로 몇 초 동안 바꾸고, 같은 스크립트 끝에서TRUSTED로 되돌린다(PDB 설정 파일 줄 0 확인).ENFORCED가 믿는 것은DETERMINISTIC이라고 적힌 글자다. 거짓 약속을 막아 주는 스위치는 없다.
5.4 녹이는 법 (실습/01 [G]) — 그리고 녹여도 다시 언다
| 한 일 | 결과 |
|---|---|
| 함수를 다시 컴파일 | 그 함수의 답 Invalid → 다음 호출이 그 순간으로 다시 언다(2초 뒤에도 같은 값) |
DBMS_RESULT_CACHE.INVALIDATE(USER, '함수') | 같다 |
| 테이블 DML | 소용없다. 의존 객체가 아니다 |
그러니 "매일 자정에 INVALIDATE하는 잡"은 처방이 아니다. 18시 마감은 자정이 아니고, 잡을 잊는 날 같은 사고가 난다(게다가 이 DB는 job_queue_processes = 1이다).
6. DETERMINISTIC — 캐시가 아니라 약속
DETERMINISTIC은 '같은 인자면 언제나 같은 답'이라는 작성자의 선언이다. 오라클은 이를 검사하지 않는다(§5.3). 오라클이 그 선언을 믿고 하는 일은 둘이다. 한 번의 페치 안에서 같은 인자의 호출을 줄이고, 함수 기반 인덱스와 결과 캐시(ENFORCED)에서 그 함수를 받아 준다.
6.1 줄이는 범위 — 한 번의 페치 (실습/02 [F] · 같은 10만 행 · 고객 2,000명)
| 부르는 자리 | 함수 본문 | 경과 |
|---|---|---|
PL/SQL에서 직접(n := FN(r.CUS_ID)) | 100,000(하나도 안 준다) | 32 ~ 35cs |
커서 루프(FOR r IN (SELECT FN(CUS_ID) …) · 100행 페치) | 99,938 | 41 ~ 45cs |
집계 한 번(SELECT SUM(FN(CUS_ID)) · 한 번의 페치) | 57,664 | 23 ~ 27cs |
SQL*Plus로 받기 · ARRAYSIZE 15(왕복 6,668) | 100,000 | |
〃 ARRAYSIZE 500(왕복 201) | 97,991 | |
〃 ARRAYSIZE 5000(왕복 21) | 86,143 |
- 기억은 페치가 끝나면 사라진다. 그래서 한 번에 받는 행이 많을수록 호출이 준다.
- 집계 한 번의 57,664는 스칼라 서브쿼리로 감싼 판과 똑같다(
02[C]). 같은 크기의 칸을 쓰기 때문이다(07화의 메모판 · 15화 고객 2,000명 57,679).
6.2 인자가 없는 함수 (실습/01 [H] · 목록 10만 행)
WHERE STLM_DT <= FN()에서 DETERMINISTIC을 붙인 인자 없는 함수는 문장에 한 번(본문 1)만 불렸다. 그냥 함수는 100,000번 불렸다(12cs). 2019년의 DETERMINISTIC은 엄밀히는 거짓(시계를 읽는다)이었다. 하지만 그 기억이 한 문장 안에서만 살아 있는 동안은 '그 문장의 지금' 하나를 돌려줄 뿐이라 틀린 답이 나오지 않았다. 같은 거짓 약속이 결과 캐시를 만나면 인스턴스 전체에, 기한 없이 퍼진다.
6.3 붙여도 되는가
| 묻는 것 | 아니면 |
|---|---|
| 답이 인자만으로 정해지나(테이블도 안 읽나) | 테이블을 읽으면 그 테이블이 바뀔 때 거짓이 된다. 한 페치 안이라도 마찬가지다 |
| 시계 · 세션 설정 · 세션 상태 · 패키지 변수 · 시퀀스를 안 읽나 | 거짓이다. 붙이지 않는다 |
함수 기반 인덱스 · ENFORCED 결과 캐시에 이 함수를 쓰게 될 수 있나 | 그 자리에서는 약속이 한 페치를 넘어 계속 유지된다 |
7. RESULT_CACHE_MODE — MANUAL과 FORCE
이 DB의 기본값은 MANUAL로, 힌트(/*+ RESULT_CACHE */)나 함수의 RESULT_CACHE가 있을 때만 캐시한다. FORCE는 가능한 문장을 모두 캐시한다. 실습에서는 ALTER SESSION만 썼다(시스템 값은 MANUAL 그대로 · 04 [G]).
실습/04 | MANUAL | FORCE (내 세션) |
|---|---|---|
| [A] 힌트 없는 Q2 두 번 | 매번 5,868 | 계획에 RESULT CACHE · 두 번째 아래 줄 Starts 0. 좋은 쪽 |
[B] /*+ NO_RESULT_CACHE */ | — | 빠진다 · 5,868 |
| [C] 한 번 묻고 마는 PK 조회 2,000개 | 결과 캐시 안 건드림 | 만들기 2,001 · 밀어내기 1,011 ~ 1,018 · (Q2 결과가 남은 판 · 밀려난 판이 갈렸다) |
| [D] 함수 안의 SQL: 그냥 보정률 함수 10만 행 첫 판 | 39 ~ 41cs · 논리 읽기 300,527 ~ 300,588 | 61 ~ 69cs · 227,285 ~ 227,323 · 찾기 24,414 · 만들기 75,587 · 밀어내기 74,597 ~ 74,598 |
| 〃 두 번째 판 | 39 ~ 41cs | 0cs · 0. 바깥 문장(합계 한 줄)째 캐시 |
| [E] 채번 복사본 '고치고(커밋) 읽기' 1,000번 | 만들기 0 | 만들기 1,000 · 찾기 0 · 무효화 999 |
| [F] '초' 판을 부르는 문장 (힌트 없음) | 시각이 간다 | 시각이 멈춘다 |
[F] SYSDATE를 직접 쓴 문장 | 시각이 간다 | 시각이 간다(거절) |
MANUAL에서는 붙이는 사람이 '같은 답'을 책임진다. FORCE에서는 고르는 사람이 없어진다. 좋은 문장, 한 번 묻고 마는 문장, 함수 안의 SQL, 매번 바뀌는 테이블, 시계를 숨긴 함수를 부르는 문장이 모두 같은 대접을 받는다.
8. 결과 캐시의 경합 — Free에서 보이는 데까지
실습/05 · 일꾼 하나 = 100만 번 부르기 · RC = 결과 캐시에 이미 있는 보정률 하나(CUS_000001) 찾기 · PLAIN = 캐시도 SQL도 없는 PL/SQL 함수. 둘이 동시에 = 내 세션 + 일꾼.sql(같은 순간 출발: GO_C15B). 여덟 번 돌린 범위다.
| 혼자 | 둘이 동시에 (각자) | |
|---|---|---|
| PLAIN 100만 번 | 7 ~ 8cs | 8 ~ 9cs |
| RC 100만 번 | 20 ~ 21cs | 34 ~ 42cs |
래치 Result Cache: Set (RC) | gets 1,000,001 · misses 0 | gets 약 197만 ~ 199만 · misses 15,445 ~ 58,104 · sleeps 0 |
래치 Result Cache: Blocklist Latch (RC) | gets 1,000,001 · misses 0 | gets 약 200만 · misses 7,681 ~ 58,381 · sleeps 0 |
- 찾기 한 번에 래치 둘을 한 번씩 잡는다. 혼자서도 그냥 함수의 세 배다(한 번에 약 0.13µs 더).
- 둘이 같은 답을 찾으면 각자 두 배 가까이 걸린다(1.6 ~ 2.1배). misses는 수천 ~ 수만이고 sleeps는 0이다. 놓치면 그 자리에서 돌며(spin) 다시 잡았다는 뜻이다. 도는 것도 CPU를 쓴다. PLAIN은 둘이어도 그대로다.
- ⚠️ Free에서 볼 수 있는 것은 여기까지다.
cpu_count가 2라서 세션을 셋 넘게 띄우면 CPU 차례 기다림이 섞여 래치만 따로 읽을 수 없다. 수백 세션이 한 결과를 두드리는 장면은 대규모 EE 환경을 다루는 별권에서 본다. - ← 07화 탕비실 선반의 집게(CBC 래치)와 같은 이야기다. 메모리 읽기도 일이다.
9. 정합성 — 시각의 경계
9.1 이 사건에서 08화 체크리스트가 걸린 자리
| # | 항목 | 이 화에서 |
|---|---|---|
| A | 기준 | 원장 = 규칙을 그대로 옮긴 식(HH24 >= 18이면 다음 날). 직전 판(금요일 판)과 대조하면 같은 날 안에서는 전부 같다 |
| B | 건수 · MINUS | 목록: 경계 행이 있으면 100,001 vs 100,000 · MINUS 1/0 / 없으면 100,000 = 100,000 · 0/0 |
| E | 경계 데이터 | ★ 시각(18시 정각 · 자정 · 금 → 월) · 세션(다른 세션 · 시간대 · NLS · 직원). 08화 경계 목록에 없던 줄 |
| F | 계획 해시 | 그냥 함수 · 2019 원본 · 금요일 판 목록의 계획 해시 셋 다 3721302985 · 4수치도 같다(TABLE ACCESS FULL · Starts 1 · A-Rows 100K · Buffers 549 · filter("STLM_DT"<="FN_STLM_DT_RC_C15B"())) |
| G | 변환 · 자동 장치에 기대지 않기 | "오라클이 자동으로 무효화한다"를 근거로 쓰지 않는다. 무효화는 의존 객체만 본다 |
| H | 영향 | 그 함수를 부르는 곳 전부(USER_DEPENDENCIES · 소스 검색) · RESULT_CACHE가 붙은 함수 전부(USER_PROCEDURES.RESULT_CACHE = 'YES') |
📌 '이 데이터에서 같다'의 네 번째 얼굴
02화의
T_ORD_BIG은 전부 자정이라BETWEEN이 통과했다. 08화의 평가 수는 전부 50이라 순위가 같았다. 15b화에서는 정산 복사본의 정산일이 전부 과거라, 오늘이 6/14든 6/17이든 목록이 같았다. 그리고 연습 DB의 주문은 움직이지 않아서 Q2의 결과 캐시가 0으로 남았다.
9.2 숨은 입력이 있는 함수는 시험할 수 없다 → 입력을 꺼낸다 (실습/01 [I])
rev00(인자 없음)은 경계 시각에 부를 방법이 없다. 그 시각까지 기다리거나 시계를 돌려야 한다. rev01(p_at DATE DEFAULT SYSDATE)은 시각을 넣어 부를 수 있다.
시각 원장 (규칙) rev01 rev00 (금요일 판)
06-14 FRI 17:59:59 06-14 FRI 06-14 FRI D (now)
06-14 FRI 18:00:00 06-15 SAT 06-15 SAT D (now)
06-14 FRI 23:59:59 06-15 SAT 06-15 SAT D (now)
06-15 SAT 00:00:00 06-15 SAT 06-15 SAT D (now)
06-16 SUN 18:30:00 06-17 MON 06-17 MON D (now)
06-17 MON 09:00:00 06-17 MON 06-17 MON D (now)
06-17 MON 18:00:00 06-18 TUE 06-18 TUE D (now)
한 주(월 0시 ~)를 7분 간격 1,440 점 rev01 MINUS 원장 0 · 원장 MINUS rev01 010. 결과 캐시를 붙이기 전에 — 판단 순서와 SDT_rev01
10.1 묻는 순서
| # | 묻는 것 | 아니면 | 이 화의 두 함수 |
|---|---|---|---|
| ① | 그게 정말 같은 답인가: 답이 인자와 테이블에만 기대나(시계 · 세션 설정 · 세션 상태 · 패키지 변수 · 시퀀스 X) | 붙이지 않는다. 숨은 입력을 인자로 꺼낸다 | 정산일 ✗(시계) · 보정률 ○ |
| ② | 지금 몇 번 계산하나: 재 봤나 | 재고 나서 판단 | 정산일: 목록에서 이미 1번(DETERMINISTIC). 얻을 것 없음 · 보정률: 10만 행에 100,000 |
| ③ | 기대는 테이블이 얼마나 자주 바뀌나 | 자주 바뀌면 만들자마자 지워진다(04 [E]) | 보정률: 고객 등급 분기마다 ○ · Q2: 주문 매 건 ✗ → 보류 |
| ④ | 답이 몇 개인가 · 칸에 들어가나 | 안 들어가면 밀어내기가 돈다(02 [C]) | 보정률: 고객 2,000 > 1,984칸(연습 DB) → 운영 크기 확인 뒤 |
| ⑤ | 한 결과가 상한(49칸 · 196KB) 안인가 | 조용히 Bypass(02 [G]) | — |
| ⑥ | 같은 답을 동시에 많이 찾나 | 래치 경합(05) · 대규모는 별권 | 화면 세션 수 |
| ⑦ | 더 좁은 장치로 되나: 한 문장 동안이면 스칼라 서브쿼리, 한 배치 동안이면 지역 변수(← 15화) | 결과 캐시 | 정산일: 한 문장 동안 → 스칼라 서브쿼리 |
| ⑧ | 경계에서 대조했나: 시각 · 세션 | 체크리스트 E | §9 |
10.2 SDT_rev01 — 무엇을 바꿨나 (리비전/SDT_rev01.sql)
diff
- FUNCTION FN_STLM_DT RETURN DATE DETERMINISTIC RESULT_CACHE IS
+ FUNCTION FN_STLM_DT(p_at DATE DEFAULT SYSDATE) RETURN DATE IS
- RETURN TRUNC(SYSDATE + 6/24);
+ RETURN TRUNC(p_at + 6/24);
-- 목록 (부르는 쪽)
- WHERE STLM_DT <= FN_STLM_DT
+ WHERE STLM_DT <= (SELECT FN_STLM_DT FROM dual)목록 '정산일까지 온 정산' (정산 10만 행 · 실습/01 [H] · 두 번) | 정산일 본문 | 결과 캐시 찾기 | 경과 | 논리 읽기 | 계획 해시 |
|---|---|---|---|---|---|
| 그냥 함수(rev01을 인자 없이 · 부르는 쪽 그대로) | 100,000 | 0 | 12 ~ 13cs | 549 ~ 669 | 3721302985 |
2019 원본(DETERMINISTIC) | 1 | 0 | 0 ~ 1cs | 549 ~ 600 | 3721302985 |
rev00 금요일 판(DETERMINISTIC RESULT_CACHE) | 0 | 1 | 0 ~ 1cs | 549 ~ 551 | 3721302985 |
| rev01 + 부르는 쪽 스칼라 서브쿼리 | 1 | 0 | 0cs | 549 | 1560056510 |
(논리 읽기 범위의 큰 값은 각 판의 첫 실행으로, 파싱 몫이다.) Buffers는 그대로 549다. 이 리비전은 블록을 줄이지 않는다. 고친 것은 답이고, 호출 수는 2019 원본과 같다.
10.3 보정률 함수 — 맞는 자리, 그러나 보류
| 답이 기대는 것 | 인자(고객) · M_CUS(자동 의존) · 함수 본문(규정: 분기마다 함수를 다시 만들면 컴파일하면서 캐시가 지워진다) |
| 무효화 | 등급 커밋 → 1,500칸 전부 Invalid → 다시 계산해서 새 보정률(03 [F]) |
| 효과 (연습 DB) | 예상 보정액 39 ~ 43 → 16 ~ 18cs · 함수 100,000 → 14,467 / 12,551(2,000명) · 1,500명이면 두 번째 0 |
| 보류 사유 | 운영의 result_cache_max_size와 고객 수를 모른다. 연습 DB 크기로는 2,000명이 안 들어간다 |
11. 이 화의 측정값 전부
11.1 Q2 · 예상 보정액 (실습/02 · 여덟 번 돌린 범위)
| 판 | 본문 | 찾기 | 만들기 | 밀어내기 | 경과 | 논리 읽기 |
|---|---|---|---|---|---|---|
| Q2 버퍼 캐시만 × 10 | — | 0 | 0 | 0 | 112 ~ 117cs | 58,670 ~ 58,715 |
Q2 RESULT_CACHE 첫 번 | — | 0 | 1 | 0 | 12cs | 5,867 ~ 5,898 |
Q2 RESULT_CACHE 다음 아홉 번 | — | 9 | 0 | 0 | 0cs | 0 |
| 예상 보정액 그냥 함수 (× 2) | 100,000 | 0 | 0 | 0 | 39 ~ 43cs | 300,527 ~ 300,704 |
| 〃 스칼라 서브쿼리 (× 2) | 57,664 | 0 | 0 | 0 | 23 ~ 26cs | 173,519 ~ 173,541 |
〃 DETERMINISTIC (× 2) | 57,664 | 0 | 0 | 0 | 23 ~ 25cs | 173,519 ~ 173,588 |
〃 RESULT_CACHE 첫 판 | 14,467 | 85,533 | 14,467 | 12,497 | 17 ~ 18cs | 43,950 ~ 43,988 |
〃 RESULT_CACHE 두 번째 판 | 12,551 | 87,449 | 12,551 | 12,551 | 16 ~ 17cs | 38,180 ~ 38,202 |
| 예상 보정액 합계 (네 판 모두) | 73,753,436 |
11.2 사건 재현 (실습/01 · 01b)
11.3 무효화 (실습/03)
→ §4.2 표 · 모드 (04) → §7 · 경합 (05) → §8
11.4 측정 환경
Oracle AI Database 26ai Free Release 23.26.2.0.0 · cpu_count 2 · job_queue_processes 1 · result_cache_mode MANUAL · result_cache_max_size 8,126,464(1,984블록 × 4KB) · result_cache_max_result 5(49블록) · result_cache_integrity TRUSTED · result_cache_execution_threshold 2 · result_cache_auto_blocklist ON · Subcache Count 2 · DBMS_RESULT_CACHE.STATUS ENABLED · 결과 캐시 총 메모리 8,392,416바이트(공유 풀의 1.667%). 데이터: M_CUS 복사본 2,000명(A 20 · B 180 · C 1,800) · T_ORD_STLM 복사본 10만(고객마다 50건 · 정산일 전부 과거) · T_ORD_BIG 100만(원본 · 읽기만). 권한: sqlp는 V$RESULT_CACHE_*를 읽을 수 있지만 DBMS_RESULT_CACHE 실행 권한은 없다(PUBLIC 동의어는 있지만 실행은 DBA 롤만 가능). 그래서 실습/91이 권한을 주고 92가 거둔다.
12. Free에서 못 잰 것 · 재지 않은 것
| 못 잰 것 / 재지 않은 것 | 왜 | 대신 한 것 |
|---|---|---|
| 수십 ~ 수백 세션이 한 결과를 두드릴 때의 래치 경합 | cpu_count 2 | 둘까지(05) · 대규모 EE 환경은 별권 |
| RAC에서의 결과 캐시(인스턴스 사이 무효화) | Free는 단일 인스턴스 | 별권 |
| 운영의 결과 캐시 크기 · 운영 주문의 커밋 빈도 | 운영은 보지 않는다 | T1 11호 12번 재측정 계획 · Q2 · 보정률 보류 |
큰 결과의 임시 테이블스페이스 보관(result_cache_max_temp_*) | 이 화의 실험은 그 조건에 닿지 않았다(큰 결과는 Bypass) | §2.2에 이름만 |
result_cache_execution_threshold가 저장을 늦추는 조건 | 이 화의 측정에서는 첫 호출에 저장됐다 | §3.1에 측정 사실만 |
결과 캐시의 두 묶음(SUBCACHE_ID)이 갈리는 규칙 | 재현이 일정하지 않았다 | §4.1에 관찰만 |
result_cache_auto_blocklist가 결과를 막는 조건 | 막힌 결과가 없었다 | — |
| 클라이언트 결과 캐시(OCI) | 이 교재의 클라이언트는 SQL*Plus | — |
ENFORCED에서 SYSTIMESTAMP · CURRENT_DATE 직접 | 93은 F1 · F2 · F3만 | TRUSTED에서 거절됨(01 [F4][F5]) |
| 패키지 변수에 기대는 함수 + 결과 캐시 | 세션 상태는 CLIENT_IDENTIFIER로 쟀다 | §5.1(같은 모양이다: 인자 밖의 입력) |
13. 원천 좌표
전체 지도 →
00_자료준비/06_원서와_공식문서_지도.mdPDF 페이지 = 책 페이지 − 2 ·성능고도화I_목차색인.md(공개 사이트에는 없음)
성능고도화 I권 (주 원천)
| 주제 | 책 페이지 |
|---|---|
| DB 버퍼 캐시 · 버퍼 캐시 구조 · 캐시 버퍼 체인 (← 07화) | 23 · 24 · 26 |
| 함수와 읽기 일관성 | 400 |
| 스칼라 서브쿼리의 캐싱 효과를 이용한 함수 호출 최소화 | 417 |
| Deterministic 함수의 캐싱 효과 활용 | 420 |
| Result 캐시 | 472 |
| Deterministic 함수 사용 시 주의사항 | 512 |
오라클 공식문서
| 주제 | 매뉴얼 | 장 제목 |
|---|---|---|
RESULT_CACHE 절 · 함수 결과 캐시 · 세션 설정에 기대는 함수 · DETERMINISTIC | lnpls | PL/SQL Subprograms (PL/SQL Function Result Cache) · DETERMINISTIC Clause |
/*+ RESULT_CACHE */ · RESULT_CACHE_MODE · Result Cache 개념 | tgsql · tgdba | Query Result Cache Concepts · Tuning the Result Cache |
DBMS_RESULT_CACHE (MEMORY_REPORT · FLUSH · INVALIDATE · STATUS) | arpls | DBMS_RESULT_CACHE |
V$RESULT_CACHE_OBJECTS · _DEPENDENCY · _STATISTICS · _MEMORY · 파라미터 RESULT_CACHE_* · FIXED_DATE | refrn | Dynamic Performance Views · Initialization Parameters |
이 화에서 꼭 확인할 뷰
sql
V$RESULT_CACHE_OBJECTS -- TYPE(Result/Dependency) · STATUS(Published/Invalid/Bypass) · NAMESPACE(SQL/PLSQL) · SCAN_COUNT · ROW_COUNT · BLOCK_COUNT · SUBCACHE_ID
V$RESULT_CACHE_DEPENDENCY -- RESULT_ID → DEPEND_ID : 그 답이 무엇에 기대나
V$RESULT_CACHE_STATISTICS -- Find Count · Create Count Success · Delete Count Valid(밀려남) · Invalidation Count · Block Count Current/Maximum
USER_PROCEDURES -- RESULT_CACHE · DETERMINISTIC 칸 : 이 스키마에서 캐시를 쓰는 함수 · 약속을 한 함수다음 화가 여기서 가져가는 것
| 화 | 가져가는 것 |
|---|---|
| 16 | Q2는 결과 캐시 없이 rev01 그대로 부하에 들어간다(§2.3 보류) · 시스템 전체를 볼 때 '캐시'라는 말이 넷 중 무엇을 가리키는지(§1) |
| 18 | 「SQL 코딩 가이드」 조문 11(결과 캐시 · 숨은 입력 · DETERMINISTIC)의 승격 판정 · 정합성 체크리스트 경계 목록의 '시각 · 세션' 줄 · RESULT_CACHE가 붙은 함수 전수 점검 · 보정률 함수 결과 캐시 재적용 판단(운영 크기 확인 뒤) |