recursive calls는 4,162에서 19가 됐는데 정산 수수료 반영은 여전히 1.4초다

모범 답안 SQL ​

PROCEDURE APPLY_PG_FEE(p_ym VARCHAR2) IS
  CURSOR c IS
    SELECT s.ROWID AS rid, s.STLM_AMT, o.PAY_TP, m.CUS_GD      -- 고칠 행의 주소를 같이 가져온다
      FROM T_ORD_STLM s, T_ORD o, M_CUS m
     WHERE s.STLM_DT >= TO_DATE(p_ym, 'YYYYMM')
       AND s.STLM_DT <  ADD_MONTHS(TO_DATE(p_ym, 'YYYYMM'), 1)
       AND o.ORD_SEQ = s.ORD_SEQ
       AND m.CUS_ID  = s.CUS_ID;
  TYPE t_rid IS TABLE OF ROWID        INDEX BY PLS_INTEGER;
  TYPE t_num IS TABLE OF NUMBER       INDEX BY PLS_INTEGER;
  TYPE t_vc  IS TABLE OF VARCHAR2(40) INDEX BY PLS_INTEGER;
  v_rid t_rid;  v_amt t_num;  v_tp t_vc;  v_gd t_vc;  v_fee t_num;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v_rid, v_amt, v_tp, v_gd LIMIT 1000;
    EXIT WHEN v_rid.COUNT = 0;
    FOR i IN 1 .. v_rid.COUNT LOOP
      v_fee(i) := PKG_PG_FEE.CALC(v_tp(i), v_gd(i), v_amt(i));
    END LOOP;
    FORALL i IN 1 .. v_rid.COUNT
      UPDATE T_ORD_STLM
         SET FEE_AMT = v_fee(i)
       WHERE ROWID = v_rid(i);                                  -- 원소마다 FULL 640블록 → 그 행 한 블록
  END LOOP;
  CLOSE c;
  COMMIT;
END;

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

[UPDATE 원소 하나 — 실행계획]
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT            |            |      1 |        |      0 |00:00:00.01 |       2 |
|   1 |  UPDATE                     | T_ORD_STLM |      1 |        |      0 |00:00:00.01 |       2 |
|   2 |   TABLE ACCESS BY USER ROWID| T_ORD_STLM |      1 |      1 |      1 |00:00:00.01 |       1 |
------------------------------------------------------------------------------------------------

[세션 통계 — 고친 판]
  경과 0.04 초 · recursive calls 19 · UPDATE Executions 5 (rows 4,110) · session logical reads 9,851
  (FEE_AMT 4,110건이 지금 판과 같다: MINUS 0 / 0)

모범 답안 서술 ​

V$SQL에서 UPDATE는 Executions 5, ROWS_PROCESSED 4,110, BUFFER_GETS 2,634,510이다. FORALL이 원소 천 개를 한 번에 넘겨 실행 호출은 5번이 됐지만, SQL 엔진은 받은 문장을 원소마다 실행한다. 원소 하나의 계획은 TABLE ACCESS FULL T_ORD_STLM(filter STLM_SEQ = :B1)이다. 정산 테이블에 인덱스가 없어서 한 행을 고치려고 640블록을 다 읽는다. 641 × 4,110 = 2,634,510블록이 거의 전부이고, 이 일은 루프 판에서도 FORALL 판에서도 똑같다. 엔진 왕복은 원래 4,162번뿐이라 원인이 아니었다. FORALL은 왕복을 묶을 뿐 원소마다의 일을 묶지 않는다. 고칠 행은 구동 커서가 방금 읽은 바로 그 행이다. 그래서 구동 커서에서 s.ROWID를 같이 가져와 WHERE ROWID = v_rid(i)로 고친다. 원소 하나가 640블록 → 1블록(테이블 행 하나)이 되어 논리 읽기는 2,636,141 → 9,851, 경과는 1.37초 → 0.04초로 준다. STLM_SEQ에 인덱스를 만들어도 원소마다 3블록 남짓으로 줄지만, 정산 배치가 매번 10만 건을 적재할 때마다 인덱스도 고쳐야 한다. 14화에서 인덱스를 두지 않기로 한 이유가 그 적재 비용이었으므로 ROWID 쪽을 고른다. 전제 조건은 구동 커서가 읽은 뒤 UPDATE할 때까지 행이 옮겨지지 않는 것이다. 이 단계가 도는 동안 다른 세션은 정산을 고치지 않고, 테이블을 재구성(MOVE, SHRINK)하거나 행 이동이 생기는 파티션 키 변경도 없다. 같은 배치 안에서 읽고 바로 고치는 지금 모양에서는 이 조건이 지켜진다. ROWID를 테이블에 저장해 두었다가 다음 날 쓰는 것은 안 된다.

해설 ​

• 함정: "FORALL로 바꾸면 빨라진다"는 원소마다 하는 일이 작을 때의 이야기다. 바꾸기 전에 문장 한 번의 계획(Buffers)부터 본다. 왕복 비용(교재 15화 실측 약 2.5µs)보다 원소 하나의 일(640블록)이 수천 배 컸다.
• 다른 정답: 한 문장 MERGE INTO T_ORD_STLM s USING (정산 ⋈ 주문 ⋈ 고객) … UPDATE SET FEE_AMT = PKG_PG_FEE.CALC(…)도 된다. 해시 조인으로 한 번 읽고, 함수는 SQL에서 4,110번 불린다. 함수 안에 SQL이 없으므로 그 비용은 작다(SQL에서 PL/SQL로 건너가는 비용 약 0.53µs씩).
• 교재 15화 §5.3에서도 같은 일이 있었다. FORALL 안의 MERGE … USING dual은 원소마다 대상 행을 찾아 고치는 행 단위 MERGE라서 논리 읽기와 redo가 루프 판과 거의 같았다.
• ROWID로 고치는 방법은 SELECT … FOR UPDATE 없이도 이 배치에서는 안전하다. 다른 세션이 같은 행을 고칠 수 있는 테이블이라면 구동 커서에 FOR UPDATE를 붙이거나 UPDATE 조건에 원래 값을 함께 건다.

관련 교재 ​

• 15화 · FORALL은 왕복을 묶을 뿐 일을 묶지 않는다
• 14화 · DML의 부수비용 다섯