결과 캐시를 붙인 결제 대기 배지가 시험 DB에서는 0블록이었는데 운영에서는 그대로다

중급PLSQL한빛유통661 / 700

상황

주문 접수 화면과 고객센터 화면 맨 위에는 고객의 「결제 대기 n건」 배지가 뜬다. 배지는 함수 FN_PENDING_CNT가 센다. 결과 캐시 교육을 받은 오과장이 지난주 화요일에 이 함수에 RESULT_CACHE를 붙였다. 연습 DB에서 고객 2,000명을 두 번 돌려 보니 두 번째 판은 함수 본문이 한 번도 돌지 않았다. 한 주 뒤에도 운영 화면은 빨라지지 않았고, DB CPU는 조금 늘었다. DBA가 10분 동안의 결과 캐시 통계를 보내왔다.

테이블과 인덱스

T_ORD 주문: 100,000행 · 896블록 · ORD_ST (COMPLETE 90% · PENDING 8% · CANCEL 2%) · 운영에서는 주문 등록, 결제, 취소가 초당 약 2.5번 커밋된다
인덱스 IX_T_ORD_CUS_ST (CUS_ID, ORD_ST): 이 문제에서는 있다 · 함수 안 SQL은 호출당 2블록
배지는 화면을 열 때 한 번 호출되고, 운영에서는 초당 약 30번 호출된다(고객 2,000명 중 아무나)
결과 캐시: 1,984칸 · result_cache_mode = MANUAL · 함수 안 SQL이 읽은 T_ORD가 의존 객체로 자동 등록된다

원본 SQL

sql

CREATE OR REPLACE FUNCTION FN_PENDING_CNT(p_cus_id VARCHAR2) RETURN NUMBER RESULT_CACHE IS
  v_cnt NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_cnt
    FROM T_ORD
   WHERE CUS_ID = p_cus_id
     AND ORD_ST = 'PENDING';
  RETURN v_cnt;
END;
/

-- 배지 (화면을 열 때 한 번)
SELECT FN_PENDING_CNT(:cus_id) AS pending_cnt FROM dual;

실행계획 학습용 예시

xplan

[연습 DB: 고객 2,000명을 두 판 (그동안 T_ORD를 고친 사람 없음)]
 판           함수 본문   찾기 (Find)   만들기 (Create)   논리 읽기
 첫 판           2,000            0             2,000        4,013
 두 번째 판          0        2,000                 0            0

[운영: 10분 동안 V$RESULT_CACHE_STATISTICS 증가분]
 Find Count                        412
 Create Count Success           17,588
 Invalidation Count             17,431
 Delete Count Valid                  0
 (그동안 배지 호출 18,000번 · T_ORD를 고친 커밋 1,503번)

[V$RESULT_CACHE_OBJECTS: 의존 객체 줄 · 10분 증가분]
 TYPE         NAME                    INVALIDATIONS
 Dependency   SQLP.T_ORD                      1,503
 Dependency   SQLP.FN_PENDING_CNT                 0

[배지 한 번 (운영 평균)]
 판                   응답 시간   논리 읽기   결과 캐시 래치 gets
 RESULT_CACHE 전       0.11 ms          2                    0
 RESULT_CACHE 후       0.13 ms          2                    4

문제

연습 DB에서는 두 번째 판이 0블록이었는데 운영에서는 효과가 없는 이유를 위 통계로 설명하시오.
이 함수를 어떻게 할지 SQL로 쓰고, 결과 캐시를 붙이기 전에 무엇을 확인했어야 하는지 쓰시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

-- 결과 캐시를 뗀다: 답이 기대는 T_ORD 는 운영에서 초당 2.5번 커밋된다 (만들자마자 지워진다)
-- 함수 안 SQL 은 IX_T_ORD_CUS_ST 로 호출당 2블록이라 그대로 충분하다
CREATE OR REPLACE FUNCTION FN_PENDING_CNT(p_cus_id VARCHAR2) RETURN NUMBER IS
  v_cnt NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_cnt
    FROM T_ORD
   WHERE CUS_ID = p_cus_id
     AND ORD_ST = 'PENDING';
  RETURN v_cnt;
END;
/

-- 같은 기준으로 다시 볼 함수: 결과 캐시가 붙은 함수 목록
SELECT object_name, procedure_name
  FROM USER_PROCEDURES
 WHERE result_cache = 'YES';

개선 후 실행계획 학습용 예시

xplan

[운영: 뗀 뒤 10분]
 배지 호출 18,000 · 함수 본문 18,000 · 논리 읽기 36,000 (호출당 2) · 평균 응답 0.11 ms
 V$RESULT_CACHE_STATISTICS: Create Count Success +0 · Invalidation Count +0

모범 답안 서술

결과 캐시는 답을 의존 객체와 함께 기억하고, 의존 객체가 바뀌어 커밋되면 그 객체에 기대는 답을 지운다. 이 함수의 답이 기대는 객체는 함수 안 SQL이 읽은 T_ORD다. 지우는 단위는 행이 아니라 테이블이라서, 고객 한 명의 주문이 바뀌어도 T_ORD에 기대는 답이 전부 지워진다. 운영에서는 주문 등록, 결제, 취소가 초당 약 2.5번 커밋되므로(10분에 1,503번) 답은 만들자마자 0.4초 안에 지워진다. 그 사이에 같은 고객의 배지를 다시 열 일은 드물다. 그래서 10분 동안 만들기 17,588번에 찾기는 412번이고, 무효화가 17,431번이다. 호출 대부분이 결과 캐시를 찾아보고, 없어서 본문을 돌리고, 답을 적어 두었다가 곧 지운 것이다. 연습 DB에서는 그동안 아무도 주문을 고치지 않아 무효화가 한 번도 없었다. 그래서 두 번째 판이 0블록이었다. 주문이 움직이지 않는 DB에서 잰 효과는 운영에서도 같다는 근거가 되지 못한다. 게다가 함수 안 SQL은 IX_T_ORD_CUS_ST로 호출당 2블록이면 끝난다. 결과 캐시로 아낄 것이 거의 없고, 오히려 찾기와 만들기, 래치 비용이 붙어 배지 한 번이 0.11 → 0.13ms로 늘었다. RESULT_CACHE를 떼어 원래대로 둔다. 붙이기 전에 확인했어야 할 것은 순서대로 이렇다. 지금 몇 번 계산하고 한 번에 얼마인지 쟀는가(여기는 2블록). 답이 기대는 테이블이 얼마나 자주 바뀌어 커밋되는가(여기는 초당 2.5번). 같은 질문이 되풀이되는가(고객 2,000명 중 아무나). 결과 캐시는 답이 인자와 드물게 바뀌는 마스터 테이블에만 기대고, 같은 입력이 자주 되풀이되는 함수(예: 분기마다 바뀌는 고객 등급으로 정하는 보정률)에 맞는다. 같은 기준으로 USER_PROCEDURES에서 RESULT_CACHE가 붙은 다른 함수도 찾아본다.

해설

무효화는 고장이 아니라 정상 동작이다. 커밋된 변경을 반영하려고 지우는 것이고, 지우지 않으면 배지가 틀린다. 결과 캐시가 맞는지는 지우는 빈도가 정한다(교재 15b화: 고객 한 명을 고쳐 커밋하자 1,500칸이 전부 Invalid가 됐다).
연습 DB에서 효과를 재려면 운영과 비슷한 커밋을 옆 세션에서 함께 흘려 보낸다(교재 15화의 부하 발생기 PKG_LOAD_DRIVER 같은 도구). 데이터가 멈춰 있는 DB에서 잰 캐시 효과는 늘 부풀려진다.
RESULT_CACHE_MODE = FORCE로 바꾸면 이런 문장까지 힌트 없이 캐시된다. 자주 바뀌는 테이블을 읽는 문장이 많으면 만들기와 무효화만 늘어난다(교재 15b화 7절).
배지 호출 자체를 줄이려면 화면이 주문 목록을 읽는 SQL에서 결제 대기 건수를 함께 센다. 다만 화면을 한 번 열 때 한 번 부르는 함수는 그 자체로 문제가 아니다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황