정산 등록 화면의 예상 보정액 한 줄이 0.4초, 결과 캐시를 붙이자는 안이 올라왔다

실전SELECT한빛유통664 / 700

상황

정산 등록 화면 맨 위의 「이번 주 예상 보정액」은 정산 10만 건에 보정률 함수 FN_ADJ_RATE를 곱해 더한 한 줄이다. 정산팀 10명이 화면을 열 때마다 계산되어 하루 600번쯤 돌고, 한 번에 0.4초가 걸린다. 선배는 함수에 RESULT_CACHE 한 단어를 붙이자고 했고, 박팀장은 대안을 숫자로 견주어 하나를 고르고 무엇을 잃는지도 적어 달라고 했다. 보정률 규칙(등급 A 0.1%, B 0.2%, 그 밖 0.3%)은 정산팀 규정이고, 이 함수는 정산 등록(한 건)과 주간 보정 배치도 함께 쓴다.

테이블과 인덱스

T_ORD_STLM 정산: 100,000행 · 640블록(실측 복사본은 549블록) · 고객 2,000명이 50건씩, 고객 순서가 아니라 섞여 있다
M_CUS 고객: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · CUS_GD A 20명 · B 180명 · C 1,800명 · 등급은 분기마다 다시 매긴다
결과 캐시: 1,984칸(답 하나가 칸 하나) · result_cache_mode = MANUAL · result_cache_integrity = TRUSTED
통계 최신

원본 SQL

sql

CREATE OR REPLACE FUNCTION FN_ADJ_RATE(p_cus_id VARCHAR2) RETURN NUMBER IS
  v_gd M_CUS.CUS_GD%TYPE;
BEGIN
  SELECT CUS_GD INTO v_gd FROM M_CUS WHERE CUS_ID = p_cus_id;
  RETURN CASE v_gd WHEN 'A' THEN 0.001 WHEN 'B' THEN 0.002 ELSE 0.003 END;   -- 정산팀 규정
END;
/

-- 이번 주 예상 보정액 (정산 등록 화면 맨 위)
SELECT SUM(ROUND(STLM_AMT * FN_ADJ_RATE(CUS_ID))) AS adj_sum
  FROM T_ORD_STLM;

실행계획 실측 · 교재 15b화(① ~ ④) · 개선안 수치는 학습용 예시

xplan

[① 그대로: DISPLAY_CURSOR (ALLSTATS LAST) · 복사본]
-----------------------------------------------------------------------------------------
| Id  | Operation          | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |      1 |        |      1 |00:00:00.41 |     549 |
|   1 |  SORT AGGREGATE    |            |      1 |      1 |      1 |00:00:00.41 |     549 |
|   2 |   TABLE ACCESS FULL| T_ORD_STLM |      1 |   100K |   100K |00:00:00.01 |     549 |
-----------------------------------------------------------------------------------------

[네 판 비교: 판마다 두 번 (교재 15b화 실습/02 · 여덟 번 돌린 범위)]
 판                                 함수 본문    찾기     만들기   경과          논리 읽기   예상 보정액
 ① 그대로                            100,000        0         0   39 ~ 43 cs     300,527   73,753,436
 ② 스칼라 서브쿼리로 감싸기           57,664        0         0   23 ~ 26 cs     173,519   73,753,436
 ③ DETERMINISTIC 추가                 57,664        0         0   23 ~ 25 cs     173,519   73,753,436
 ④ RESULT_CACHE 추가 · 첫 판          14,467   85,533    14,467   17 ~ 18 cs      43,950   73,753,436
 ④ RESULT_CACHE 추가 · 두 번째 판     12,551   87,449    12,551   16 ~ 17 cs      38,180   73,753,436
   (④의 밀어내기: 첫 판 12,497 · 두 번째 판 12,551)

문제

②와 ③이 57,664에서, ④가 두 번째 판에도 12,551에서 멈춘 이유를 각각 설명하시오.
보정률 규칙은 함수 하나에만 둔 채, 결과가 바뀌지 않으면서 함수 호출을 고객 수(2,000번) 이하로 줄이는 SQL을 쓰시오.
고른 안과 ② ~ ④, 그리고 규칙을 SQL의 CASE로 옮기는 안을 견주어 각각 무엇을 잃는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 고객마다 보정률을 한 번만 구하고(함수 2,000번) 정산 10만 건에 붙인다
-- NO_MERGE: 이 뷰가 바깥과 합쳐지면 함수가 '조인된 정산 행마다' 호출된다 (교재 15화 [G'] 99,850번)
SELECT SUM(ROUND(s.STLM_AMT * r.ADJ_RATE)) AS adj_sum
  FROM T_ORD_STLM s,
       (SELECT /*+ NO_MERGE */ c.CUS_ID, FN_ADJ_RATE(c.CUS_ID) AS ADJ_RATE
          FROM M_CUS c) r
 WHERE r.CUS_ID = s.CUS_ID;
-- 정산의 고객이 마스터에 없으면 원래 SQL 은 그 행이 NULL 이라 SUM 에서 빠진다 → 이너 조인과 같다

개선 후 실행계획 실측 · 교재 15b화(① ~ ④) · 개선안 수치는 학습용 예시

xplan

----------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |      1 |        |      1 |00:00:00.05 |     579 |          |
|   1 |  SORT AGGREGATE       |            |      1 |      1 |      1 |00:00:00.05 |     579 |          |
|*  2 |   HASH JOIN           |            |      1 |   100K |   100K |00:00:00.04 |     579 | 1542K (0)|
|   3 |    VIEW               |            |      1 |   2000 |   2000 |00:00:00.02 |      30 |          |
|   4 |     TABLE ACCESS FULL | M_CUS      |      1 |   2000 |   2000 |00:00:00.01 |      30 |          |
|   5 |    TABLE ACCESS FULL  | T_ORD_STLM |      1 |   100K |   100K |00:00:00.01 |     549 |          |
----------------------------------------------------------------------------------------------------------
   2 - access("R"."CUS_ID"="S"."CUS_ID")

[세션 통계]  함수 본문 2,000 · 경과 4 ~ 6 cs · 논리 읽기 약 6,600 (함수 안 SQL 2,000번 × 3블록 포함) · 예상 보정액 73,753,436

모범 답안 서술

②와 ③이 같은 57,664에서 멈춘 것은 둘이 같은 크기의 칸을 쓰기 때문이다. 스칼라 서브쿼리 캐시는 실행 하나 동안, DETERMINISTIC은 한 번의 페치 동안 입력 값과 답을 짝지어 적어 두는데(이 집계는 한 번의 페치다), 칸 수가 정해져 있고 이미 다른 고객이 들어 있는 칸에 부딪히면 적지 않는다. 그래서 고객 2,000명 중 자리를 얻지 못한 고객은 나올 때마다 함수를 다시 부른다(교재 07화와 15화의 57,679도 같은 이유다). ④가 두 번째 판에도 12,551에서 멈춘 것은 결과 캐시가 1,984칸이고 답 하나가 칸 하나라서 고객 2,000명이 다 들어가지 않기 때문이다. 자리가 없으면 쓸 만한 답을 밀어내고(두 번째 판 밀어내기 12,551), 밀려난 고객은 다시 계산한다. 결과 캐시는 모든 세션이 함께 쓰므로 하루 600번에 걸쳐 이득이 남는 것은 맞다. 그러나 등급을 다시 매기는 날에는 고객 한 명만 고쳐 커밋해도 M_CUS에 기대는 답이 전부 지워지고, 칸을 함께 쓰는 다른 결과도 밀려난다. ③의 DETERMINISTIC은 M_CUS를 읽는 함수에 붙인 거짓 약속이다. 등급이 바뀌면 같은 고객이라도 답이 바뀐다. 보정률은 고객마다 하나이므로 고객 2,000명의 보정률을 먼저 한 번씩 구하고 정산에 붙이면 함수는 2,000번 호출된다. 규칙은 함수 하나에 그대로 남는다. 정산마다 ROUND(STLM_AMT × 보정률)을 하는 원래 식도 그대로라 예상 보정액은 73,753,436으로 같다. 경과는 39 ~ 43cs → 4 ~ 6cs, 논리 읽기는 300,527 → 약 6,600이다. 정산의 고객이 마스터에 없으면 원래 SQL은 그 행이 NULL이 되어 합계에서 빠지므로 이너 조인과 결과가 같다. 이 안의 대가는 고객 뷰가 병합되지 않아야 한다는 것이다. 옵티마이저가 뷰를 합치면 함수는 '조인된 정산 행마다' 호출되어 10만 번으로 돌아간다(교재 15화: 힌트를 빼자 1,997 → 99,850번). 그래서 NO_MERGE 힌트에 성능이 걸리고, 힌트 유효기간 대장에 적어 두고 회귀 시험에서 함수 본문 수(2,000)를 확인한다. 뷰 안에 ROWNUM을 넣어도 막을 수 있다. 규칙을 SQL의 CASE로 옮기는 안은 함수 호출이 0번으로 가장 빠르지만, 보정률 규칙이 함수와 SQL 두 곳에 생긴다. 정산 등록과 주간 보정 배치가 같은 함수를 쓰므로 규정이 바뀌는 날 한 곳만 고치면 화면과 배치의 보정액이 달라진다. 그래서 고르지 않는다.

해설

숫자가 같은 두 판(②, ③)은 이유도 같다. 교재 15b화의 표에서 DETERMINISTIC과 스칼라 서브쿼리는 57,664로 같았다. 같은 크기의 칸을 쓰기 때문이다. 다만 DETERMINISTIC은 페치가 끝나면 사라지므로, 화면이 여러 번 나눠 받는 SQL에서는 더 적게 준다.
결과 캐시가 맞는 자리는 따로 있다. 정산 등록 한 건이 부르는 보정률은 같은 고객이 되풀이되고 등급이 분기마다 바뀌므로 결과 캐시와 잘 맞는다. 다만 운영의 result_cache_max_size와 고객 수를 먼저 확인한다. 연습 DB 크기로는 2,000명이 들어가지 않는다(교재 15b화 10.3의 '맞는 자리, 그러나 보류').
규칙을 데이터로 바꾸는 길도 있다. 등급별 보정률을 3행짜리 표로 만들고 함수와 SQL이 모두 그 표를 읽게 하면 규칙은 한 곳이고 SQL은 함수를 부르지 않아도 된다. 다만 규정을 바꾸는 절차(정산팀이 함수가 아니라 표를 고친다)가 바뀌므로 정산팀과 정한다.
뷰 병합을 막는 방법으로 WITH 절과 /*+ MATERIALIZE */도 된다(교재 15화 [G] 1,997번). 임시 테이블에 2,000행을 쓰는 일이 더해진다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 실측 · 교재 15b화(① ~ ④) · 개선안 수치는 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 실측 · 교재 15b화(① ~ ④) · 개선안 수치는 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황