매장은 다섯 곳뿐인데 월간 판매 내려받기 한 번에 SQL이 2만 번 실행된다

기초SELECT유통655 / 700

상황

본사 회계팀은 매달 초 「월간 판매 내려받기」로 지난달 판매 전표를 파일로 받는다. 매장명은 공통 함수 FN_SHP_NM이 붙이는데, 이 함수는 화면 40여 곳이 함께 쓰고 있어서 개발팀은 함수를 고치기를 꺼린다. DBA가 내려받기 한 번의 세션 통계를 보내왔다. "전표는 2만 건인데 recursive calls도 2만 번이다. 매장은 다섯 곳뿐인데."

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · SLE_DT VARCHAR2(8) · 2023년 12월 20,864건 · 판매 매장은 5곳(SHP_NO 1001 ~ 1005)
인덱스 IX_CF10_SLE_DT (SLE_DT): 이 문제에서는 있다 · 통계 최신
CF07 매장: 6행(본사 1000과 매장 5곳) · 4블록 · CF07_PK (SHP_NO)
함수 안의 SELECT는 호출마다 따로 실행되는 재귀 SQL이다(dep=1).

원본 SQL

sql

CREATE OR REPLACE FUNCTION FN_SHP_NM(p_shp_no NUMBER) RETURN VARCHAR2 IS
  v_nm CF07.SHP_NM%TYPE;
BEGIN
  SELECT SHP_NM INTO v_nm FROM CF07 WHERE SHP_NO = p_shp_no;
  RETURN v_nm;
END;
/

SELECT h.SLE_NO, h.SLE_DT, h.SLE_HMS, h.SHP_NO,
       FN_SHP_NM(h.SHP_NO) AS shp_nm
  FROM CF10 h
 WHERE h.SLE_DT BETWEEN '20231201' AND '20231231';

실행계획 학습용 예시

xplan

[DISPLAY_CURSOR (ALLSTATS LAST)]
------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |      1 |        |  20864 |00:00:00.24 |     295 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |  20833 |  20864 |00:00:00.02 |     295 |
|*  2 |   INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |  20833 |  20864 |00:00:00.01 |      98 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("H"."SLE_DT">='20231201' AND "H"."SLE_DT"<='20231231')

[세션 통계: 내려받기 한 번 (V$MYSTAT 증가분)]
  경과 시간                 0.24 초
  recursive calls           20,871
  execute count             20,866
  session logical reads     42,023

[V$SQL: 함수 안의 문장 (dep=1)]
  SELECT SHP_NM FROM CF07 WHERE SHP_NO = :B1    EXECUTIONS 20,864 · ROWS 20,864 · BUFFER_GETS 41,728 (한 번에 2블록)

문제

매장이 다섯 곳인데 recursive calls가 2만 번인 이유를 설명하시오. 그 일이 계획의 Buffers(295)에 보이지 않는 이유도 쓰시오.
함수는 고치지 않고 SQL만 고쳐 함수 호출을 매장 수만큼으로 줄이시오. 이 방법이 통하는 조건과 통하지 않는 경우도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 함수 호출을 스칼라 서브쿼리로 감싼다: 매장 번호(입력)마다 결과를 실행 내내 기억한다 → 5번
SELECT h.SLE_NO, h.SLE_DT, h.SLE_HMS, h.SHP_NO,
       (SELECT FN_SHP_NM(h.SHP_NO) FROM DUAL) AS shp_nm
  FROM CF10 h
 WHERE h.SLE_DT BETWEEN '20231201' AND '20231231';

-- (다른 정답) 매장과 조인한다: 함수 호출 0번 · 못 찾으면 NULL 이던 함수와 같게 아우터 조인
-- SELECT h.SLE_NO, h.SLE_DT, h.SLE_HMS, h.SHP_NO, s.SHP_NM
--   FROM CF10 h, CF07 s
--  WHERE h.SLE_DT BETWEEN '20231201' AND '20231231'
--    AND s.SHP_NO(+) = h.SHP_NO;

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

xplan

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |      1 |        |  20864 |00:00:00.03 |     295 |
|   1 |  FAST DUAL                          |                |      5 |      1 |      5 |00:00:00.01 |       0 |
|   2 |  TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |  20833 |  20864 |00:00:00.02 |     295 |
|*  3 |   INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |  20833 |  20864 |00:00:00.01 |      98 |
-------------------------------------------------------------------------------------------------------------
   3 - access("H"."SLE_DT">='20231201' AND "H"."SLE_DT"<='20231231')

[세션 통계]  경과 0.03 초 · recursive calls 12 · session logical reads 305
[V$SQL: 함수 안의 문장]  EXECUTIONS 5 · BUFFER_GETS 10

모범 답안 서술

세션 통계의 recursive calls 20,871은 전표 20,864건과 거의 같다. FN_SHP_NM이 전표마다 한 번씩 호출됐고, 그때마다 함수 안의 SELECT SHP_NM FROM CF07 …가 재귀 SQL로 따로 실행됐다(V$SQL EXECUTIONS 20,864). 한 번에 2블록이니 41,728블록이다. 이 일은 바깥 문장과 따로 파싱되고 실행되므로 계획의 Buffers(295)에는 들어가지 않는다. 세션 전체의 논리 읽기 42,023 = 295 + 41,728이 그 흔적이다. 함수 호출을 스칼라 서브쿼리 (SELECT FN_SHP_NM(h.SHP_NO) FROM DUAL)로 감싸면, 스칼라 서브쿼리 캐시가 입력 값(매장 번호)과 결과를 짝지어 실행이 끝날 때까지 기억한다. 매장 번호는 5종류뿐이라 함수는 5번만 호출되고, 계획의 FAST DUAL 줄에 Starts 5로 보인다. recursive calls는 20,871 → 12, 논리 읽기는 42,023 → 305, 경과는 0.24 → 0.03초가 된다. 함수와 다른 화면 40여 곳은 손대지 않는다. 이 방법은 입력 값의 종류가 적을 때만 통한다. 캐시는 입력을 열쇠로 쓰므로, 전표 번호처럼 행마다 다른 값을 넣으면 매번 새로 호출해 소용이 없다. 종류가 수십 개를 넘으면 캐시 칸이 모자라 일부 값은 매번 호출된다(교재 07화: 64종류만 돼도 6개가 자리를 못 얻었다). 그럴 때는 CF07과 조인하는 편이 낫다. 조인이면 함수 호출이 0번이고 CF07 4블록만 더 읽는다.

해설

캐시는 실행 하나 동안만 산다. 다음 달 내려받기는 다시 5번 호출하고, 다른 세션과도 나눠 쓰지 않는다. 실행과 세션을 넘어 결과를 쓰는 것은 결과 캐시(RESULT_CACHE)이고, 대가가 따로 있다(교재 15b화).
DETERMINISTIC을 붙이는 답은 맞지 않다. 그 기억은 한 번의 페치 안에서만 살고, 함수가 CF07을 읽으므로 '같은 입력이면 같은 답'이라는 약속도 거짓이다. 매장명이 바뀌는 날 틀린 이름이 섞일 수 있다.
조인으로 바꿀 때는 아우터 조인(s.SHP_NO(+) = h.SHP_NO)으로 쓴다. 함수는 매장을 못 찾으면 NULL을 돌려주고(SQL 안의 NO_DATA_FOUND) 전표는 남기기 때문이다.
두 답 모두 결과는 같다(전표 20,864건과 매장명). 바꾼 뒤 건수와 MINUS 양방향으로 대조한다(교재 08화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황