채번 함수를 바꾼 뒤 정산 보정 적재가 0.3초에서 61초로 늘었다

실전PLSQL한빛유통504 / 700

상황

12화의 장애 뒤에 한빛유통 개발팀은 채번 테이블을 그대로 두고, 번호 받기만 자율 트랜잭션 함수 FN_NEXT_NO로 떼어 냈다. 정산 등록 화면은 더 이상 10분씩 기다리지 않는다. 그런데 분기마다 지난 분기 주문 10만 건을 정산으로 다시 적재하는 보정 적재도 이 함수를 쓰도록 바꾸자, 적재가 0.3초에서 61초로 늘었다. 적재가 도는 동안에는 새벽 정산 보정 화면의 번호 받기도 들쭉날쭉 느려진다. 박팀장의 주문이다. "고치는 방법을 둘 이상 비교해서 고르고, 정산번호 규칙과 함께 대가를 적어 와라."

테이블과 인덱스

T_ORD 주문: 100,000행 · 896블록 · 적재 원천(전부 옮긴다) · 적재는 주문이 들어오지 않는 새벽에 돈다
T_ORD_STLM 정산: 적재 대상 · PK_T_ORD_STLM (STLM_SEQ)
정산번호 규칙(교재 12화에서 정한 그대로): 겹치면 안 되고, 결번은 사유를 기록하면 된다
T_STLM_NUM 채번: NUM_TP = 'STLM' 행 하나 · 지금 LAST_NO 412,000
정산 보정 화면은 적재가 도는 새벽에도 하루 수십 번 FN_NEXT_NO('STLM')를 부른다

원본 SQL

sql

-- 12화 뒤에 만든 채번 함수 — 부른 쪽의 트랜잭션과 따로 COMMIT 한다
CREATE OR REPLACE FUNCTION FN_NEXT_NO(p_tp VARCHAR2) RETURN NUMBER IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  v_no NUMBER;
BEGIN
  SELECT LAST_NO + 1 INTO v_no FROM T_STLM_NUM WHERE NUM_TP = p_tp FOR UPDATE;
  UPDATE T_STLM_NUM SET LAST_NO = v_no WHERE NUM_TP = p_tp;
  COMMIT;
  RETURN v_no;
END;

-- 정산 보정 적재 — 행마다 번호
INSERT INTO T_ORD_STLM (STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT)
SELECT FN_NEXT_NO('STLM'), ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
  FROM T_ORD;
COMMIT;

실행계획 학습용 예시

xplan

[적재 INSERT — ALLSTATS LAST]
------------------------------------------------------------------------------------------------
| Id  | Operation                | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |            |      1 |        |      0 |00:01:01.20 |    211K |
|   1 |  LOAD TABLE CONVENTIONAL | T_ORD_STLM |      1 |        |      0 |00:01:01.20 |    211K |
|   2 |   TABLE ACCESS FULL      | T_ORD      |      1 |   100K |   100K |00:00:00.04 |     896 |
------------------------------------------------------------------------------------------------

[TKPROF — 적재 한 번에 함께 돈 재귀 SQL (FN_NEXT_NO 안)]
SQL                                                       Execute       Rows    current
SELECT LAST_NO + 1 FROM T_STLM_NUM WHERE … FOR UPDATE     100,000    100,000    100,000
UPDATE T_STLM_NUM SET LAST_NO = :B2 WHERE …               100,000    100,000    100,000
COMMIT (자율 트랜잭션)                                     100,000

[세션 통계 — 적재 한 번]
  경과 시간          61.2초   (번호 없이 같은 INSERT … SELECT 를 돌리면 0.3초)
  user commits       100,001
  recursive calls    약 400,000
  redo size          58.4MB

[새벽 보정 화면 — 적재가 도는 1분 동안]
  FN_NEXT_NO 한 번   평소 0.002초 → 0.02 ~ 0.4초 · enq: TX - row lock contention 37번

문제

실행계획과 재귀 SQL, 세션 통계를 근거로 61초가 어디서 나오는지 설명하시오. 적재가 도는 동안 보정 화면의 번호 받기가 느려지는 이유도 쓰시오.
고치는 방법을 둘 이상 비교하고 하나를 골라 SQL로 작성하시오. 방법마다 결번이 언제 생기는지와 화면과의 경합이 어떻게 되는지도 함께 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 고른 것: 번호를 한 번에 묶어 예약한다 — 채번 행을 한 번만 고치고, 화면의 FN_NEXT_NO 는 그대로 둔다
DECLARE
  v_cnt  NUMBER;
  v_max  NUMBER;
  v_last NUMBER;
BEGIN
  -- 옮길 범위와 행 수를 먼저 고정한다
  SELECT COUNT(*), MAX(ORD_SEQ) INTO v_cnt, v_max FROM T_ORD;

  -- 번호 v_cnt 개를 한 문장으로 예약한다 (이 트랜잭션 안에서 — 실패하면 함께 되돌아간다)
  UPDATE T_STLM_NUM SET LAST_NO = LAST_NO + v_cnt
   WHERE NUM_TP = 'STLM'
  RETURNING LAST_NO INTO v_last;

  INSERT INTO T_ORD_STLM (STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT)
  SELECT v_last - v_cnt + ROWNUM, ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
    FROM T_ORD
   WHERE ORD_SEQ <= v_max;

  IF SQL%ROWCOUNT <> v_cnt THEN                  -- 센 것과 넣은 것이 다르면 통째로 되돌린다
    RAISE_APPLICATION_ERROR(-20001, 'reserved count and loaded rows differ');
  END IF;
  COMMIT;                                         -- 채번 행은 여기서 풀린다 (0.34 초)
END;

-- 비교한 것: 시퀀스 — 화면과 적재를 함께 옮길 때만
--   CREATE SEQUENCE SQ_STLM_SEQ START WITH 412001 CACHE 1000;
--   INSERT INTO T_ORD_STLM (...) SELECT SQ_STLM_SEQ.NEXTVAL, ORD_SEQ, ... FROM T_ORD;

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

xplan

[고친 뒤 — 묶음 예약 (학습용 예시)]
  경과 61.2초 → 0.34초 · user commits 100,001 → 1 · recursive calls 약 400,000 → 약 300 · redo 58.4MB → 16.1MB
  채번 행 갱신 1번 · 적재가 COMMIT 할 때까지 0.34초 동안만 보정 화면이 그 행에서 기다린다
[비교 — 시퀀스 (CACHE 1000)]
  경과 0.36초 · SEQ$ 갱신 100번 · 화면도 시퀀스로 옮겨야 한다 · 적재가 ROLLBACK 하면 10만 개가 결번

모범 답안 서술

61초는 INSERT 자체가 아니라 행마다 부르는 함수에서 나온다. 실행계획 2번 줄의 TABLE ACCESS FULL T_ORD는 896블록, 0.04초로 끝난다. 그런데 행 10만 개마다 FN_NEXT_NO가 자율 트랜잭션을 열어 채번 행을 FOR UPDATE로 잠그고, UPDATE하고, COMMIT한다. 재귀 SQL 두 개가 10만 번씩 돌고 COMMIT도 10만 번이다(user commits 100,001, recursive calls 약 40만, redo 58.4MB). 번호 없이 같은 INSERT … SELECT를 돌리면 0.3초다. 적재가 도는 1분 동안 채번 행은 10만 번 잠겼다 풀리기를 되풀이한다. 보정 화면의 FN_NEXT_NO는 같은 행을 원하므로 그 사이에 끼어 TX 6을 기다린다(enq: TX - row lock contention 37번). 한 번은 짧지만 적재가 쉬지 않고 행을 다시 잡으므로 0.02 ~ 0.4초로 들쭉날쭉해진다. 고치는 방법은 둘이다. ① 시퀀스: INSERT … SELECT 안에서 NEXTVAL을 쓰면 0.36초로 끝난다. 대신 화면도 같은 시퀀스로 옮겨야 한다. 채번 테이블과 시퀀스를 섞어 쓰면 두 번호원이 서로를 모르므로 번호가 겹친다. 적재가 실패해 ROLLBACK하면 10만 개가 한꺼번에 결번이 된다. ② 묶음 예약: 옮길 행 수를 세고, 채번 행을 LAST_NO = LAST_NO + v_cnt 한 문장으로 한 번만 올린 뒤, ROWNUM으로 번호를 매겨 넣는다. 채번 행을 고치는 것도 COMMIT도 한 번이다(61.2초 → 0.34초). 화면의 함수는 그대로 같은 행에서 번호를 받으므로 겹치지 않는다. 예약을 적재와 같은 트랜잭션에서 하므로 적재가 실패하면 예약도 되돌아가 결번이 생기지 않는다. 대가는 적재가 COMMIT할 때까지(0.34초) 화면이 그 행에서 기다린다는 것이다. ②를 고른다. 화면 코드를 건드리지 않고, 결번도 없으며, 1분 동안 들쭉날쭉하던 화면의 기다림이 0.34초 한 번으로 바뀐다. 조건은 센 행 수와 넣은 행 수가 같아야 한다는 것이다. 그래서 ORD_SEQ 범위를 고정하고, SQL%ROWCOUNT가 다르면 통째로 되돌린다. 넣은 행이 예약보다 많으면 화면이 받은 번호와 겹치기 때문이다.

해설

자율 트랜잭션 채번은 '한 번에 하나'를 위한 도구다. 화면처럼 드문드문 하나씩 받는 곳에서는 기다림을 없애 주지만, 대량 처리에서 행마다 부르면 COMMIT 10만 번이 된다. 같은 함수라도 부르는 곳의 규모에 맞는지 따로 본다(교재 12화 §6, 14화).
묶음 예약을 자율 트랜잭션으로 하면 화면은 0.34초도 기다리지 않지만, 적재가 실패할 때 예약한 10만 개가 결번이 된다. 정산번호는 결번 사유만 기록하면 되므로 그것도 답이 될 수 있다. 그때는 실패한 적재의 번호 범위를 결번 기록에 남긴다.
INSERT /*+ APPEND */를 더하면 적재가 더 빨라지지만, 직접 경로 적재는 대상 테이블에 TM 6을 걸어 그동안 화면의 정산 INSERT를 막는다. 새벽 보정 화면이 같은 테이블에 쓰므로 여기서는 쓰지 않는다.
시퀀스로 옮긴다면 한 번에 옮긴다. 화면, 배치, 수작업 스크립트가 모두 같은 번호원을 쓰게 하고 채번 테이블은 더는 쓰지 못하게 막는다. 시퀀스의 START WITH는 옮기는 순간의 LAST_NO + 1이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황