주문 10만 건을 다시 대사하는 데 recursive calls가 40만 번이다

실전PLSQL한빛유통675 / 700

상황

한빛유통은 매월 1일 PG사가 보내는 정정 파일을 받으면 지난 결제 전체(주문 10만 건)를 다시 대사한다. 대사 규칙(결제 수단별 허용 오차, 등급별 예외)은 결제 화면과 같은 패키지 PKG_PAY_RULE에 있고, 결제팀이 매달 고친다. 지난달에는 규칙 안에 PG사 공지 테이블을 읽는 줄이 들어갔다가 빠졌다. 결과는 대사 결과 테이블에 적고, 맞지 않는 주문은 보류(HOLD)로 바꾼다. 박팀장이 TKPROF를 넘기며 말했다. "한 문장으로 다 바꾸자는 말도 나오는데, 규칙을 SQL로 옮기면 결제 화면과 두 벌이 돼요. 어떻게 할지 숫자로 정리해 줘요."

테이블과 인덱스

T_ORD 주문: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · PAY_TP · ORD_AMT · PAY_AMT · ORD_ST
T_ORD_DET 주문상세: 200,000행 · 896블록 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) · 주문당 1 ~ 3건
M_CUS 고객: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · 모든 주문의 고객이 있다
T_PAY_RECON(대사 결과, 이 문제용): RECON_YM · ORD_SEQ · RES_CD · 인덱스 없음 · 회차마다 그 달 것을 지우고 다시 쓴다(이번이 이달 첫 회차)
PKG_PAY_RULE.CHECK_PAY: 지금은 SQL 없는 PL/SQL 함수 · 이번 달 결과 'OK' 99,588건 · 'HOLD' 412건
배치가 도는 새벽에는 주문을 고치는 다른 세션이 없다.

원본 SQL

sql

PROCEDURE PAY_RECON(p_ym VARCHAR2) IS
  v_gd   M_CUS.CUS_GD%TYPE;
  v_det  NUMBER;
  v_res  VARCHAR2(10);
BEGIN
  DELETE FROM T_PAY_RECON WHERE RECON_YM = p_ym;
  FOR r IN (SELECT ORD_SEQ, CUS_ID, PAY_TP, ORD_AMT, PAY_AMT FROM T_ORD) LOOP
    SELECT CUS_GD INTO v_gd FROM M_CUS WHERE CUS_ID = r.CUS_ID;                  -- ①
    SELECT NVL(SUM(ORD_QTY * UNT_PRC), 0) INTO v_det
      FROM T_ORD_DET WHERE ORD_SEQ = r.ORD_SEQ;                                  -- ②
    v_res := PKG_PAY_RULE.CHECK_PAY(r.PAY_TP, v_gd, r.ORD_AMT, r.PAY_AMT, v_det);
    UPDATE T_ORD
       SET ORD_ST = CASE WHEN v_res = 'OK' THEN ORD_ST ELSE 'HOLD' END
     WHERE ORD_SEQ = r.ORD_SEQ;                                                  -- ③
    INSERT INTO T_PAY_RECON (RECON_YM, ORD_SEQ, RES_CD)
    VALUES (p_ym, r.ORD_SEQ, v_res);                                             -- ④
  END LOOP;
  COMMIT;
END;

실행계획 학습용 예시

xplan

[TKPROF 요약 — PAY_RECON('202412') 한 번 · sys=no]
  문장                                                      Execute     Fetch      rows     query   current   elapsed
  구동 커서 SELECT … FROM T_ORD                                   1     1,001   100,000     1,897         0      0.05
  ① SELECT CUS_GD FROM M_CUS WHERE CUS_ID = :B1              100,000   100,000   100,000   300,000         0      0.41
  ② SELECT NVL(SUM(ORD_QTY*UNT_PRC),0) FROM T_ORD_DET …      100,000   100,000   100,000   300,412         0      0.49
  ③ UPDATE T_ORD SET ORD_ST = CASE … WHERE ORD_SEQ = :B2      100,000         0   100,000   200,000   102,871      1.36
  ④ INSERT INTO T_PAY_RECON … VALUES (:B3, :B2, :B1)         100,000         0   100,000       211   102,946      0.88
  (DELETE는 이달 첫 회차라 0행)

[세션 통계 — 한 번]
  경과 시간 4.6 초 · CPU 4.2 초 · recursive calls 401,038 · session logical reads 1,008,749
  redo size 54.6 MB · undo change vector size 17.9 MB
  결과: OK 99,588건 · HOLD 412건

문제

일량이 어디서 나오는지 TKPROF의 문장별로 나누어 설명하시오. 그 가운데 엔진 왕복이 아니라 하지 않아도 되는 일인 것도 찾으시오.
규칙 함수는 그대로 두고 배치를 다시 작성하시오.
함수를 SQL 안에서 부르는 한 문장(INSERT … SELECT와 UPDATE) 판과 비교해 이 판을 고른 근거와, 고친 판의 부작용을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE PAY_RECON(p_ym VARCHAR2) IS
  CURSOR c IS                                              -- ①② : 구동 커서가 등급과 상세 합계를 같이 가져온다
    SELECT o.ROWID AS rid, o.ORD_SEQ, o.PAY_TP, o.ORD_AMT, o.PAY_AMT, m.CUS_GD,
           NVL(d.det_amt, 0) AS det_amt
      FROM T_ORD o
      JOIN M_CUS m ON m.CUS_ID = o.CUS_ID
      LEFT JOIN (SELECT ORD_SEQ, SUM(ORD_QTY * UNT_PRC) AS det_amt
                   FROM T_ORD_DET
                  GROUP BY ORD_SEQ) d
        ON d.ORD_SEQ = o.ORD_SEQ;
  TYPE t_rows IS TABLE OF c%ROWTYPE;
  TYPE t_vc   IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;
  TYPE t_rid  IS TABLE OF ROWID        INDEX BY PLS_INTEGER;
  v      t_rows;
  v_res  t_vc;
  h_rid  t_rid;                                            -- 보류로 바꿀 주문만 모은다
  n      PLS_INTEGER := 0;
BEGIN
  DELETE FROM T_PAY_RECON WHERE RECON_YM = p_ym;
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v LIMIT 1000;
    EXIT WHEN v.COUNT = 0;
    FOR i IN 1 .. v.COUNT LOOP                             -- 규칙은 PL/SQL에서 그대로 (결제 화면과 한 벌)
      v_res(i) := PKG_PAY_RULE.CHECK_PAY(v(i).PAY_TP, v(i).CUS_GD, v(i).ORD_AMT, v(i).PAY_AMT, v(i).det_amt);
      IF v_res(i) <> 'OK' THEN
        n := n + 1;
        h_rid(n) := v(i).rid;
      END IF;
    END LOOP;
    FORALL i IN 1 .. v.COUNT                               -- ④ : 결과 천 건을 한 번에
      INSERT INTO T_PAY_RECON (RECON_YM, ORD_SEQ, RES_CD)
      VALUES (p_ym, v(i).ORD_SEQ, v_res(i));
    v_res.DELETE;
  END LOOP;
  CLOSE c;
  FORALL i IN 1 .. n                                       -- ③ : 값이 바뀌는 412건만
    UPDATE T_ORD SET ORD_ST = 'HOLD' WHERE ROWID = h_rid(i);
  COMMIT;
END;

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

xplan

[구동 커서 — DISPLAY_CURSOR (ALLSTATS LAST)]
---------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |      1 |        |    100K|00:00:00.21 |    1923 |          |
|*  1 |  HASH JOIN RIGHT OUTER |           |      1 |    100K|    100K|00:00:00.21 |    1923 | 6170K (0)|
|   2 |   VIEW                 |           |      1 |    100K|    100K|00:00:00.09 |     896 |          |
|   3 |    HASH GROUP BY       |           |      1 |    100K|    100K|00:00:00.08 |     896 | 7420K (0)|
|   4 |     TABLE ACCESS FULL  | T_ORD_DET |      1 |    200K|    200K|00:00:00.02 |     896 |          |
|*  5 |   HASH JOIN            |           |      1 |    100K|    100K|00:00:00.07 |    1027 | 1350K (0)|
|   6 |    TABLE ACCESS FULL   | M_CUS     |      1 |   2000 |   2000 |00:00:00.01 |      30 |          |
|   7 |    TABLE ACCESS FULL   | T_ORD     |      1 |    100K|    100K|00:00:00.02 |     997 |          |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("D"."ORD_SEQ"(+)="O"."ORD_SEQ")
   5 - access("M"."CUS_ID"="O"."CUS_ID")

[세션 통계 — 고친 판 한 번]
  경과 0.43 초 · recursive calls 232 · session logical reads 5,416 · redo size 4.8 MB · undo 0.7 MB
  INSERT Executions 100 (rows 100,000) · UPDATE Executions 1 (rows 412)
  결과: T_PAY_RECON 100,000행 · HOLD 412건 · 원래 판과 MINUS 0 / 0 · T_ORD의 ORD_ST도 같다

모범 답안 서술

TKPROF를 문장별로 나누면 이렇다. ①과 ②는 주문 한 건마다 고객 등급과 상세 합계를 한 건씩 물어 각각 10만 번 실행됐다(query 30만씩). ③과 ④도 10만 번씩이다. 주문마다 SQL이 네 번 나가 recursive calls가 401,038이고, 엔진을 오간 횟수가 그만큼이다. 여기까지는 '묶으면 주는' 일이다. 하지 않아도 되는 일은 ③이다. UPDATE는 10만 건을 고치지만 값이 바뀌는 것은 HOLD 412건뿐이다. 나머지 99,588건은 ORD_ST를 같은 값으로 고친다. 같은 값으로 고쳐도 오라클은 행을 잠그고 redo와 undo를 만든다. ③ 한 문장이 elapsed 1.36초로 가장 크고, redo 54.6MB의 절반 가까이가 여기서 나온다. 고친 판은 넷을 따로 다룬다. ①②는 구동 커서에서 고객을 조인하고, 상세는 주문별로 묶어(GROUP BY ORD_SEQ) 아우터 조인해 처음부터 같이 가져온다. 상세가 없는 주문은 NVL로 0이 되어 원래의 NVL(SUM(…), 0)과 같다. 규칙 함수는 PL/SQL 루프에서 그대로 부른다. ④는 결과를 배열에 모았다가 FORALL로 천 건씩 넣는다. ③은 보류 412건의 ROWID만 모았다가 마지막에 FORALL로 한 번에 고친다. recursive calls는 401,038 → 232, 논리 읽기는 1,008,749 → 5,416, redo는 54.6MB → 4.8MB, 경과는 4.6초 → 0.43초가 된다. 한 문장 판(INSERT … SELECT … PKG_PAY_RULE.CHECK_PAY(…)로 결과를 넣고, UPDATE T_ORD … WHERE ORD_SEQ IN (SELECT … WHERE RES_CD = 'HOLD')로 보류)도 지금 규칙이라면 빠르다. 함수 안에 SQL이 없으므로 SQL에서 10만 번 불러도 0.05초 안팎이다. 그런데도 PL/SQL 판을 고른 근거는 규칙의 주인이 결제팀이라는 데 있다. 지난달처럼 규칙 안에 테이블을 읽는 줄이 들어오면, SQL 안에서 행마다 불리는 함수가 10만 번의 재귀 SQL을 숨긴다. 함수 안의 NO_DATA_FOUND는 SQL 안에서 에러 없이 NULL이 된다. 한 행의 실패가 문장 전체를 되돌린다는 것도 다르다. 규칙을 CASE로 SQL에 옮기면 결제 화면과 규칙이 두 벌이 된다. 고친 판의 부작용도 있다. 구동 커서의 해시 조인과 묶기가 PGA를 약 15MB 쓴다. 보류 ROWID 배열은 보류 건수만큼 커진다(412건이면 무시할 크기). 원래 판은 ①②를 주문마다 그 순간의 데이터로 읽었지만, 고친 판은 구동 커서가 연 시점 하나로 읽는다. 배치 시간에 주문을 고치는 세션이 없으므로 결과는 같고, 오히려 한 시점의 대사가 된다.

해설

함정: ③을 FORALL로 묶기만 하면 왕복은 주지만 99,588건을 같은 값으로 고치는 일은 그대로다. 묶기 전에 그 문장이 해야 하는 일인지부터 본다.
다른 정답: ③에 WHERE ORD_SEQ = :seq AND ORD_ST <> 'HOLD'처럼 바뀌는 행만 고치는 조건을 더해도 된다. 그러면 실행은 10만 번이어도 고치는 행은 412건이 된다. 보류 건만 모아 마지막에 고치는 쪽이 왕복도 줄인다.
결과 테이블에 보류만 적어도 된다면 ④도 412건으로 준다. 대사 결과 10만 건이 필요한지는 결제팀과 정한다.
판단 순서는 교재 15화 §11의 표와 같다. 행마다 판단할 규칙이 있고, 규칙의 주인이 따로 있고, 한 행의 실패를 따로 다뤄야 하면 PL/SQL을 남기되 제대로 쓴다(BULK LIMIT, FORALL, 아는 실패는 이름으로).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황