야간 체인에서 부가세 분리 단계만 여전히 INSERT를 10만 번 실행한다

모범 답안 SQL ​

PROCEDURE P3_VAT IS
  CURSOR c IS
    SELECT s.STLM_SEQ, s.STLM_AMT, s.STLM_DT, o.PAY_TP
      FROM T_ORD_STLM s, T_ORD o
     WHERE o.ORD_SEQ = s.ORD_SEQ;
  TYPE t_num IS TABLE OF NUMBER       INDEX BY PLS_INTEGER;
  TYPE t_dt  IS TABLE OF DATE         INDEX BY PLS_INTEGER;
  TYPE t_vc  IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;
  v_seq t_num;  v_amt t_num;  v_dt t_dt;  v_tp t_vc;
  v_sup t_num;  v_vat t_num;  v_rule t_vc;
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE T_STLM_VAT';
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v_seq, v_amt, v_dt, v_tp LIMIT 1000;   -- 천 건씩 받는다
    EXIT WHEN v_seq.COUNT = 0;                                         -- %NOTFOUND 로 바로 나가면 마지막 덩어리를 버린다

    FOR i IN 1 .. v_seq.COUNT LOOP                                     -- 규칙은 회계팀 프로시저 그대로 (PL/SQL 안에서 끝난다)
      PKG_VAT.SPLIT(v_amt(i), v_tp(i), v_dt(i), v_sup(i), v_vat(i), v_rule(i));
    END LOOP;

    FORALL i IN 1 .. v_seq.COUNT                                       -- 천 건을 한 번에 넘긴다 (배열 INSERT)
      INSERT INTO T_STLM_VAT (STLM_SEQ, SUPPLY_AMT, VAT_AMT, RULE_CD)
      VALUES (v_seq(i), v_sup(i), v_vat(i), v_rule(i));
  END LOOP;
  CLOSE c;
  COMMIT;
END;

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

[세션 통계 — 같은 10만 건]
  경과 시간                 0.21 ~ 0.24 초
  CPU used by this session  0.17 초
  recursive calls           약 220
  execute count             약 110
  session logical reads     약 7,900
  redo size                 약 6.1 MB
  undo change vector size   약 0.4 MB

[TKPROF 모양]
INSERT INTO T_STLM_VAT (STLM_SEQ, SUPPLY_AMT, VAT_AMT, RULE_CD) VALUES (:B4, :B3, :B2, :B1)
  Execute 100 · rows 100,000 (한 번에 1,000행)

[DBMS_HPROF]  INSERT 100 부름 0.05초 · PKG_VAT.SPLIT 100,000 부름 0.15초 · 페치 101 부름 0.02초

모범 답안 서술 ​

일량은 정산 10만 건마다 INSERT로 SQL에 한 번씩 건너가는 데서 나온다(INSERT Execute 100,000, recursive calls 101,342). HPROF로 보면 1.05초 중 INSERT 10만 번이 0.78초이고, 회계팀 프로시저 10만 번은 0.15초다. 프로시저 안에는 SQL이 없어서 PL/SQL 안에서 끝나므로 싸다. 비싼 것은 행마다 한 행씩 넣는 INSERT다. 한 행씩 넣으면 redo와 undo도 행마다 따로 생긴다(redo 26.9MB). 한 문장으로 바로 옮기기는 어렵다. PKG_VAT.SPLIT은 OUT 파라미터로 값 셋을 돌려주는 프로시저라서 SQL 문장 안에서 부를 수 없고, 회계팀 규칙이라 SQL로 다시 쓸 수도 없다. 그래서 규칙은 프로시저 그대로 두고 SQL로 가는 왕복만 묶는다. 커서에서 BULK COLLECT … LIMIT 1000으로 천 건씩 받고, 배열을 돌며 프로시저를 부른 뒤, FORALL로 천 건을 한 번에 INSERT한다. FORALL은 천 건을 SQL 엔진에 한 번에 넘기는 배열 INSERT라 왕복이 10만 번 → 100번으로 줄고, 블록 단위로 기록하므로 redo와 undo도 준다. 나갈 때는 v_seq.COUNT = 0으로 판단해 마지막 덩어리를 버리지 않는다. 경과 시간은 1.05초 → 0.22초, recursive calls는 101,342 → 약 220, 논리 읽기는 약 112,600 → 약 7,900, redo는 26.9 → 6.1MB로 준다.

해설 ​

• 다른 답: 프로시저를 감싸 값 셋을 객체 타입으로 돌려주는 함수를 만들면 INSERT … SELECT 한 문장도 된다. SQL에서 PL/SQL 함수를 부르는 비용은 한 번에 1µs 안쪽이라 10만 번이어도 싸다. 다만 타입과 감싸는 함수를 새로 관리해야 하고, 칸마다 함수를 따로 쓰면 행마다 세 번 불린다.
• LIMIT 고르기: 1,000을 넘기면 왕복은 거의 줄지 않고 세션 메모리만 커진다. LIMIT 없이 10만 건을 한 번에 받으면 그만큼 PGA를 쓴다(교재 15화 §5.2: 100,000이면 약 34MB).
• FORALL은 왕복을 묶을 뿐, 원소마다 하는 일을 줄이지는 않는다. 원소마다 MERGE … USING dual처럼 행 단위로 찾아가는 문장을 묶으면 논리 읽기가 거의 그대로다. 묶는 문장은 배열로 처리되는 INSERT … VALUES나 키로 찾는 UPDATE가 맞다.
• 결과가 실패할 수 있는 행(예: NOT NULL 위반)이 섞여 있다면 SAVE EXCEPTIONS로 실패한 원소만 모아 반송함에 남긴다. 예외를 삼키지 않는다(교재 15화 rev01).

관련 교재 ​

• 15화 · SQL은 다 고쳤는데 왜 느린가