롤백된 정산 정정 10만 건이 감사 기록에는 바뀐 것으로 남았다

실전DDL한빛유통699 / 700

상황

감사팀 요구(2022)에 따라 정산 금액이 바뀌면 바꾼 사람과 시각, 바뀌기 전과 뒤의 금액을 감사 테이블에 남긴다. 개발팀은 T_ORD_STLM에 행 트리거를 달고, 감사 기록이 정산 쪽 오류에 휩쓸려 사라지지 않게 자율 트랜잭션 프로시저로 기록하게 했다. 지난달 말 「PG 수수료 정정」 배치가 이번 달 정산 10만 건의 금액을 고친 뒤, 합계가 PG사 정산 파일과 달라 ROLLBACK했다. 다음 날 고친 파일로 다시 돌려 성공했다. 분기 대조에서 감사팀이 물었다. "감사 기록을 이어 보면 10만 건이 중간에 끊겨요. 바뀐 적 없는 금액이 기록에 있어요." 박팀장은 감사 요구를 그대로 지키는 설계를 안끼리 비교해 정해 달라고 했다.

테이블과 인덱스

T_ORD_STLM 정산: 이번 달 100,000행 · 640블록 · PK_T_ORD_STLM (STLM_SEQ) · STLM_AMT NUMBER(10) NOT NULL
T_STLM_AUD 정산 감사(이 문제용): AUD_SEQ(시퀀스 SQ_STLM_AUD) · STLM_SEQ · OLD_AMT · NEW_AMT · AUD_DT · AUD_USER · AUD_PGM · PK (AUD_SEQ) · 2022년부터 412,300행
감사팀은 분기마다 정산별로 기록을 시간순으로 이어, 앞 기록의 NEW_AMT와 다음 기록의 OLD_AMT가 같은지, 마지막 NEW_AMT가 지금 금액과 같은지 대조한다.
정산 금액을 고치는 곳: 정산 화면(한 건씩, 하루 30건쯤)과 월말 PG 수수료 정정 배치(UPDATE 한 문장, 이번 달 정산 전부).

원본 SQL

sql

CREATE OR REPLACE PROCEDURE P_AUD_WRITE(p_seq NUMBER, p_old NUMBER, p_new NUMBER) IS
  PRAGMA AUTONOMOUS_TRANSACTION;          -- 감사 기록이 정산 쪽 오류에 휩쓸려 사라지지 않게 (2022)
BEGIN
  INSERT INTO T_STLM_AUD (AUD_SEQ, STLM_SEQ, OLD_AMT, NEW_AMT, AUD_DT, AUD_USER, AUD_PGM)
  VALUES (SQ_STLM_AUD.NEXTVAL, p_seq, p_old, p_new, SYSDATE, USER, SYS_CONTEXT('USERENV', 'MODULE'));
  COMMIT;
END;
/

CREATE OR REPLACE TRIGGER TRG_STLM_AUD
AFTER UPDATE OF STLM_AMT ON T_ORD_STLM
FOR EACH ROW
WHEN (NEW.STLM_AMT <> OLD.STLM_AMT)
BEGIN
  P_AUD_WRITE(:OLD.STLM_SEQ, :OLD.STLM_AMT, :NEW.STLM_AMT);
END;
/

-- 월말 PG 수수료 정정 배치
PROCEDURE P_PG_FEE_FIX(p_rate NUMBER, p_pg_total NUMBER) IS
  v_total NUMBER;
BEGIN
  UPDATE T_ORD_STLM SET STLM_AMT = ROUND(STLM_AMT * p_rate);
  SELECT SUM(STLM_AMT) INTO v_total FROM T_ORD_STLM;
  IF v_total <> p_pg_total THEN
    ROLLBACK;                             -- PG사 합계와 다르면 통째로 되돌린다
    RAISE_APPLICATION_ERROR(-20031, 'PG 합계 불일치 ' || v_total || ' / ' || p_pg_total);
  END IF;
  COMMIT;
END;

실행계획 학습용 예시

xplan

[지난달 말 — 정정 1차 (PG 합계 불일치로 ROLLBACK)]
  경과 12.1 초 · user commits 100,000 · redo size 98.2 MB
  T_ORD_STLM : 바뀐 행 0 (전부 되돌아감) · T_STLM_AUD : +100,000행

[다음 날 — 정정 2차 (고친 파일로 성공)]
  경과 12.5 초 · user commits 100,001 · T_STLM_AUD : +100,000행

[감사팀 분기 대조]
  앞 기록의 NEW_AMT ≠ 다음 기록의 OLD_AMT 인 정산 : 100,000건 (1차 기록의 NEW_AMT는 한 번도 존재하지 않은 금액)

[TKPROF — 2차 · 트리거가 부른 프로시저 안 (dep=1)]
  INSERT INTO T_STLM_AUD (…) VALUES (SQ_STLM_AUD.NEXTVAL, :B6, …)    Execute 100,000 · current 412,377
  (+ 자율 트랜잭션 COMMIT 100,000번 · 이 세션의 log file sync 3 · LGWR log file parallel write 4,180)

[시험 DB — 같은 정정 10만 건, 감사 방식만 바꿔서]
  안                                                              경과    user commits   redo     감사 행   1차처럼 ROLLBACK하면 남는 감사 행
  지금 (행 트리거 + 자율 트랜잭션 INSERT, COMMIT)                    12.4 초       100,001   98.7 MB   100,000   100,000
  ① 행 트리거 + 같은 트랜잭션에서 INSERT                              3.1 초             1   62.3 MB   100,000         0
  ② 복합 트리거 (행 구간은 배열에 모으고 1,000건마다 FORALL INSERT)    1.9 초             1   48.6 MB   100,000         0
  ③ 트리거 없이 배치가 UPDATE 전에 감사 행을 INSERT … SELECT           1.5 초             1   43.9 MB   100,000         0
  (비교) 감사 없음                                                    1.2 초             1   29.4 MB         0         —

문제

정정 1차가 롤백됐는데 감사 기록 10만 건이 남은 이유와, 지금 판이 감사 없을 때보다 열 배 느린 이유를 설명하시오.
감사 요구를 지키는 설계를 ① ~ ③과 견주어 하나 고르고 DDL을 작성하시오.
고른 안의 부작용과, 이미 남은 1차 기록 10만 건을 어떻게 할지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 자율 트랜잭션 감사 기록을 걷어 낸다 (감사 기록은 정산 변경과 운명을 같이한다)
DROP PROCEDURE P_AUD_WRITE;

CREATE OR REPLACE TRIGGER TRG_STLM_AUD
FOR UPDATE OF STLM_AMT ON T_ORD_STLM
COMPOUND TRIGGER
  TYPE t_num IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  g_seq t_num;
  g_old t_num;
  g_new t_num;
  g_n   PLS_INTEGER := 0;

  PROCEDURE flush IS
  BEGIN
    FORALL i IN 1 .. g_n                             -- 천 건을 한 번에 · 정산 변경과 같은 트랜잭션
      INSERT INTO T_STLM_AUD (AUD_SEQ, STLM_SEQ, OLD_AMT, NEW_AMT, AUD_DT, AUD_USER, AUD_PGM)
      VALUES (SQ_STLM_AUD.NEXTVAL, g_seq(i), g_old(i), g_new(i), SYSDATE, USER, SYS_CONTEXT('USERENV', 'MODULE'));
    g_n := 0;
  END flush;

  AFTER EACH ROW IS
  BEGIN
    IF :NEW.STLM_AMT <> :OLD.STLM_AMT THEN           -- 금액이 실제로 바뀐 행만 (NOT NULL 칸)
      g_n := g_n + 1;
      g_seq(g_n) := :OLD.STLM_SEQ;
      g_old(g_n) := :OLD.STLM_AMT;
      g_new(g_n) := :NEW.STLM_AMT;
      IF g_n >= 1000 THEN flush; END IF;             -- 배열은 천 건까지만 (메모리를 묶어 둔다)
    END IF;
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    IF g_n > 0 THEN flush; END IF;                   -- 남은 것 (화면의 한 건 정정은 여기서 한 행)
  END AFTER STATEMENT;
END;
/

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

xplan

[시험 DB — 고친 트리거(②)]
  정정 성공 : 경과 1.9 초 · user commits 1 · redo 48.6 MB · T_STLM_AUD +100,000
  정정 실패(PG 합계 불일치 → ROLLBACK) : T_ORD_STLM 그대로 · T_STLM_AUD +0
  TKPROF : INSERT INTO T_STLM_AUD … Execute 100 (FORALL 한 번에 천 건)

[정산 화면 — 한 건 정정]  T_STLM_AUD 1행 (AFTER STATEMENT에서 FORALL 한 번)
[감사팀 대조를 다시]  끊긴 곳 0건 (1차 기록 100,000건을 뺀 뒤)

모범 답안 서술

감사 기록 10만 건이 남은 것은 자율 트랜잭션 때문이다. P_AUD_WRITE는 트리거가 행마다 부를 때마다 정산 쪽 트랜잭션과 따로 INSERT하고 바로 커밋했다. 정정 배치가 합계 불일치로 ROLLBACK했을 때 되돌아간 것은 정산 금액뿐이고, 이미 따로 커밋된 감사 기록 10만 건은 그대로 남았다. 그 기록의 NEW_AMT는 한 번도 확정된 적 없는 금액이다. 다음 날 2차가 같은 정산을 다시 고치며 기록을 또 남기자, 1차 기록의 NEW_AMT와 2차 기록의 OLD_AMT가 맞지 않아 감사팀 대조에서 10만 건이 끊겼다. "오류에 휩쓸려 사라지지 않게"라는 의도가 바로 이 결과를 만들었다. 정산 변경이 사라지면 그 변경의 감사 기록도 사라져야 맞다. 지금 판이 느린 것도 같은 곳에서 나온다. 정산 행마다 트리거가 프로시저를 부르고, 자율 트랜잭션이 INSERT와 COMMIT을 한다. user commits가 100,001, redo가 29.4MB(감사 없음) → 98.7MB가 됐고 경과는 1.2초 → 12.4초다. PL/SQL 안의 COMMIT이라 이 세션의 log file sync는 3번뿐이지만 LGWR는 커밋마다 내려보내느라 log file parallel write가 4,180번이다. ②를 고른다. 자율 트랜잭션을 걷어 내고 복합 트리거로 바꾼다. 행 구간(AFTER EACH ROW)에서는 금액이 바뀐 행의 번호와 전후 금액을 배열에 모으고, 천 건이 차면 FORALL로 한꺼번에 INSERT한다. 남은 것은 문장 구간(AFTER STATEMENT)에서 넣는다. 감사 INSERT가 정산 UPDATE와 같은 트랜잭션에 들어가므로, 배치가 ROLLBACK하면 감사 기록도 함께 되돌아가고 커밋하면 함께 남는다. 시험 DB에서 성공 판은 1.9초에 감사 10만 행, 실패 판은 감사 0행이었고 감사 INSERT의 실행은 100,000 → 100번이 됐다. 다른 안을 고르지 않은 이유는 이렇다. ①(행 트리거에서 같은 트랜잭션으로 INSERT)도 정합성은 맞지만 행마다 INSERT가 10만 번 실행되어 3.1초다. ③(배치가 UPDATE 전에 감사 행을 INSERT … SELECT)은 1.5초로 가장 빠르지만 트리거가 없어서 정산 화면의 한 건 정정이 감사에서 빠진다. 앞으로 금액을 고치는 프로그램이 늘 때마다 감사를 잊을 위험도 생긴다. ②는 어느 경로로 고치든 같은 트리거가 쓰고, 배치에서는 ③과 0.4초 차이다. ②의 부작용도 적어 둔다. 행 구간이 남아 있어 UPDATE는 여전히 한 행씩 처리되고, 감사가 없을 때보다 0.7초가 더 든다. 배열은 천 건마다 비우므로 메모리는 행 수와 상관없이 작다. 문장이 중간에 실패하면 문장 단위 롤백으로 이미 넣은 감사 행도 함께 되돌아간다. 감사 번호(AUD_SEQ)는 롤백된 판의 번호가 결번으로 남는다. 이미 남은 1차 기록 10만 건은 감사 기록이므로 지우지 않는다. 감사팀과 협의해 1차 정정 시각 구간의 AUD_SEQ를 "롤백된 변경"으로 따로 표시하는 표(예: T_STLM_AUD_VOID)에 적고, 대조할 때 빼도록 한다.

해설

함정: "감사 기록은 무슨 일이 있어도 남아야 한다"는 말은 두 뜻으로 읽힌다. 시도까지 남기려는 것이라면 실패한 문장마다 한 줄씩 "시도 로그"를 따로 쓰고(배치의 예외 처리부에서 자율 트랜잭션으로), 변경 기록은 변경과 같은 트랜잭션에 둔다. 둘을 한 표에 섞으면 오늘처럼 사슬이 끊긴다.
행 트리거에서 자율 트랜잭션 프로시저를 부르는 모양은 커밋 비용도 행 수만큼 곱한다(교재 13화 §7). 대기 이벤트의 log file sync가 작아도 LGWR 쪽 쓰기는 커밋 수만큼 는다.
Flashback Data Archive도 후보다. 커밋된 변경만 이력으로 남고 기록은 백그라운드가 쓴다. 다만 버전과 라이선스, 보관 공간, 바꾼 사람(AUD_USER)을 어떻게 남길지를 먼저 확인해야 한다.
복합 트리거에서 SQ_STLM_AUD.NEXTVAL을 행 구간의 PL/SQL 대입으로 받으면 행마다 시퀀스를 따로 부른다. FORALL INSERT의 VALUES 안에 두어 SQL 엔진이 붙이게 한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황