야간 주문 파일 배치에 DETERMINISTIC을 붙였지만 함수 호출은 10만 번 그대로다

중급PLSQL한빛유통662 / 700

상황

야간 배치 「지역별 주문 파일」은 주문 10만 건을 읽어 물류사에 보낼 파일을 한 줄씩 쓴다. 줄마다 고객의 지역명을 함수 FN_RGN_NM으로 붙인다. 지난주 개발자가 이 함수에 DETERMINISTIC을 붙였다. 고객은 2,000명뿐이니 같은 고객의 지역명은 한 번만 계산될 것이라고 봤다. 개발자가 SQL로 시험했을 때(SELECT COUNT(FN_RGN_NM(CUS_ID)) FROM T_ORD)는 함수 본문이 100,000번에서 57,671번으로 줄었다. 그런데 배치의 경과 시간은 1.6초 그대로이고, 함수 본문도 10만 번 그대로 돈다.

테이블과 인덱스

T_ORD 주문: 100,000행 · 896블록 · 고객 2,000명이 돌아가며 섞여 있다(고객 한 명에 약 50건)
M_CUS 고객: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · RGN_ID
M_RGN 지역: 8행 · 4블록 · PK_M_RGN (RGN_ID)
파일 쓰기(UTL_FILE)는 배치 시간 중 약 0.3초를 쓴다 · 주문의 고객은 모두 M_CUS에 있다 · 통계 최신

원본 SQL

sql

CREATE OR REPLACE FUNCTION FN_RGN_NM(p_cus_id VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS   -- 지난주 DETERMINISTIC 추가
  v_nm M_RGN.RGN_NM%TYPE;
BEGIN
  SELECT r.RGN_NM INTO v_nm
    FROM M_CUS c, M_RGN r
   WHERE c.CUS_ID = p_cus_id
     AND r.RGN_ID = c.RGN_ID;
  RETURN v_nm;
END;
/

PROCEDURE P_ORD_FILE(p_dir VARCHAR2) IS
  f     UTL_FILE.FILE_TYPE;
  v_rgn M_RGN.RGN_NM%TYPE;
BEGIN
  f := UTL_FILE.FOPEN(p_dir, 'ord_rgn.csv', 'w');
  FOR r IN (SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT FROM T_ORD) LOOP
    v_rgn := FN_RGN_NM(r.CUS_ID);
    UTL_FILE.PUT_LINE(f, r.ORD_SEQ || ',' || r.CUS_ID || ',' || v_rgn || ',' ||
                         TO_CHAR(r.ORD_DT, 'YYYYMMDDHH24MISS') || ',' || r.ORD_AMT);
  END LOOP;
  UTL_FILE.FCLOSE(f);
END;

실행계획 학습용 예시

xplan

[세션 통계: P_ORD_FILE 한 번]
  판                   경과      recursive calls   session logical reads   함수 본문 실행
  DETERMINISTIC 전     1.63 초          101,017                 501,913           100,000
  DETERMINISTIC 후     1.62 초          101,017                 501,911           100,000

[TKPROF 모양: 재귀 문장 둘]
  SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT FROM T_ORD
     Execute 1 · Fetch 1,001 · rows 100,000 · query 1,897
  SELECT R.RGN_NM FROM M_CUS C, M_RGN R WHERE C.CUS_ID = :B1 AND R.RGN_ID = C.RGN_ID
     Execute 100,000 · Fetch 100,000 · rows 100,000 · query 500,000 (한 번에 5블록)

[개발자의 시험: SQL 한 문장 (한 번의 페치)]
  SELECT COUNT(FN_RGN_NM(CUS_ID)) FROM T_ORD    함수 본문 100,000 → 57,671

문제

SQL 시험에서는 줄었던 호출이 배치에서는 하나도 줄지 않은 이유를 설명하시오.
같은 파일을 쓰면서 함수 호출이 사라지도록 배치를 고치시오. 함수의 DETERMINISTIC은 어떻게 할지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 함수: M_CUS 와 M_RGN 을 읽으므로 DETERMINISTIC 을 뗀다 (거짓 약속)
CREATE OR REPLACE FUNCTION FN_RGN_NM(p_cus_id VARCHAR2) RETURN VARCHAR2 IS
  v_nm M_RGN.RGN_NM%TYPE;
BEGIN
  SELECT r.RGN_NM INTO v_nm
    FROM M_CUS c, M_RGN r
   WHERE c.CUS_ID = p_cus_id
     AND r.RGN_ID = c.RGN_ID;
  RETURN v_nm;
END;
/

-- ② 배치: 지역명은 커서 SQL 이 행과 함께 가져온다 (함수 호출 0 · 주문이 한 건도 빠지지 않게 아우터 조인)
PROCEDURE P_ORD_FILE(p_dir VARCHAR2) IS
  f UTL_FILE.FILE_TYPE;
BEGIN
  f := UTL_FILE.FOPEN(p_dir, 'ord_rgn.csv', 'w');
  FOR r IN (SELECT o.ORD_SEQ, o.CUS_ID, o.ORD_DT, o.ORD_AMT, g.RGN_NM
              FROM T_ORD o, M_CUS c, M_RGN g
             WHERE c.CUS_ID(+) = o.CUS_ID
               AND g.RGN_ID(+) = c.RGN_ID) LOOP
    UTL_FILE.PUT_LINE(f, r.ORD_SEQ || ',' || r.CUS_ID || ',' || r.RGN_NM || ',' ||
                         TO_CHAR(r.ORD_DT, 'YYYYMMDDHH24MISS') || ',' || r.ORD_AMT);
  END LOOP;
  UTL_FILE.FCLOSE(f);
END;

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

xplan

[커서 SQL의 계획]
--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |      1 |        |   100K |00:00:00.06 |    1931 |          |
|*  1 |  HASH JOIN RIGHT OUTER |          |      1 |   100K |   100K |00:00:00.06 |    1931 | 1336K (0)|
|   2 |   TABLE ACCESS FULL    | M_RGN    |      1 |      8 |      8 |00:00:00.01 |       4 |          |
|*  3 |   HASH JOIN RIGHT OUTER|          |      1 |   100K |   100K |00:00:00.04 |    1927 | 1452K (0)|
|   4 |    TABLE ACCESS FULL   | M_CUS    |      1 |   2000 |   2000 |00:00:00.01 |      30 |          |
|   5 |    TABLE ACCESS FULL   | T_ORD    |      1 |   100K |   100K |00:00:00.01 |    1897 |          |
--------------------------------------------------------------------------------------------------------
   1 - access("G"."RGN_ID"(+)="C"."RGN_ID")
   3 - access("C"."CUS_ID"(+)="O"."CUS_ID")

[세션 통계]  경과 0.41 초 (파일 쓰기 약 0.3 초) · recursive calls 1,017 · session logical reads 1,951 · 함수 본문 0

모범 답안 서술

DETERMINISTIC은 '같은 입력이면 같은 답'이라는 약속이고, 오라클이 그 약속을 쓰는 곳은 SQL 안이다. SQL 문장이 함수를 부를 때 한 번의 페치 안에서 같은 입력의 호출을 줄인다. 개발자의 시험(SELECT COUNT(FN_RGN_NM(CUS_ID)) FROM T_ORD)은 SQL 한 문장, 한 번의 페치라서 100,000 → 57,671로 줄었다. 배치는 다르다. 함수를 PL/SQL 대입문(v_rgn := FN_RGN_NM(r.CUS_ID))에서 직접 부르므로, DETERMINISTIC이 있어도 기억해 두는 장치가 없어 하나도 줄지 않는다. 함수 본문 100,000번, recursive calls 101,017이 그대로인 이유다. 함수 안 SQL은 고객 PK와 지역 PK를 찾아 한 번에 5블록을 읽으므로, 주문 10만 건에 50만 블록이다. 논리 읽기 501,913의 거의 전부가 여기서 나온다. 지역명은 함수 대신 커서 SQL에서 M_CUS, M_RGN과 조인해 행과 함께 가져온다. 해시 조인으로 고객 30블록과 지역 4블록을 한 번 올리면 되므로 함수 호출은 0번, 논리 읽기는 501,913 → 1,951, 경과는 1.62 → 0.41초가 된다. 남은 0.3초는 파일 쓰기 몫이다. 함수는 테이블을 읽으므로 DETERMINISTIC은 거짓 약속이다. 떼어 낸다. 못 찾을 때의 동작도 맞춘다. PL/SQL에서 직접 부른 함수는 고객이나 지역을 못 찾으면 NO_DATA_FOUND를 던져 배치가 멈춘다(SQL 안에서 부를 때는 NULL이 된다). 이너 조인이면 그 주문의 줄이 조용히 빠지고, 아우터 조인이면 지역명이 빈 줄로 쓰인다. 지금은 주문의 고객이 모두 M_CUS에 있어 세 판의 파일이 같다. 파일에서 주문이 빠지면 안 되므로 아우터 조인으로 쓰고, 지역명이 빈 줄이 생기면 따로 알리는 것이 낫다.

해설

같은 함수, 부르는 자리에 따라 다른 숫자. 교재 15b화의 실측(같은 10만 행, 고객 2,000명)에서 PL/SQL 대입문은 100,000, 커서 루프의 SQL 안(100행 페치)은 99,938, 집계 한 번은 57,664였다. DETERMINISTIC이 줄이는 범위는 한 번의 페치다.
PL/SQL 안에서 기억하려면 고객 번호를 열쇠로 하는 연관 배열(TABLE OF … INDEX BY VARCHAR2(40))에 지역명을 담아 두고 없을 때만 함수를 부른다. 호출은 2,000번이 된다(교재 15화 rev01의 등급표). 조인으로 쓸 수 없는 복잡한 규칙일 때 쓰는 차선이다.
커서 FOR 루프는 레벨 2에서 이미 100행씩 가져온다(Fetch 1,001). 이 배치의 일량은 가져오기가 아니라 루프 안의 함수였다. BULK COLLECT로 바꿔도 함수 호출은 그대로다.
파일의 줄 순서가 정해져 있어야 하면 커서 SQL에 ORDER BY를 넣는다. 원래 SQL에도 순서 보장은 없었다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황