「최종 갱신 시각」 표시를 넣은 뒤로 정산 적재가 0.4초에서 3.2초가 됐다

기초DDL한빛유통690 / 700

상황

정산팀이 정산 목록 화면 맨 위에 "정산 최종 갱신 시각"을 띄워 달라고 해서, 지난달 개발자가 T_ORD_STLM에 트리거를 붙여 갱신 시각을 T_LOAD_STAMP에 적게 했다. 정산 등록 화면이 한 건을 넣을 때도, 야간 P1 적재가 10만 건을 넣을 때도 시각이 바뀐다. 그 뒤로 P1 적재가 0.4초에서 3.2초로 늘었다. 오과장이 고개를 갸웃했다. "트리거는 UPDATE 한 줄짜리인데요."

테이블과 인덱스

T_ORD(원천): 100,000행 · 896블록
T_ORD_STLM 정산: P1이 비우고(TRUNCATE) 10만 건을 다시 넣는다 · 낮에는 정산 등록 화면이 하루 20건쯤 한 건씩 넣는다 · 인덱스 없음
T_LOAD_STAMP 갱신 시각(이 문제용): 테이블마다 한 행 · PK (TBL_NM) · LAST_DT · LAST_PGM
화면은 T_LOAD_STAMP에서 TBL_NM = 'T_ORD_STLM'인 행 하나만 읽는다. 정산팀이 원한 것은 "마지막으로 정산이 들어온 시각"이다.

원본 SQL

sql

-- 지난달에 붙인 트리거
CREATE OR REPLACE TRIGGER TRG_STLM_STAMP
AFTER INSERT ON T_ORD_STLM
FOR EACH ROW
BEGIN
  UPDATE T_LOAD_STAMP
     SET LAST_DT  = SYSDATE,
         LAST_PGM = SYS_CONTEXT('USERENV', 'MODULE')
   WHERE TBL_NM = 'T_ORD_STLM';
END;
/

-- P1 적재 (야간)
TRUNCATE TABLE T_ORD_STLM;
INSERT INTO T_ORD_STLM (STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT)
SELECT ROWNUM, ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
  FROM T_ORD;
COMMIT;

실행계획 학습용 예시

xplan

[세션 통계 — P1 적재 한 번]
                              트리거 붙이기 전     트리거 붙인 뒤
  경과 시간                          0.41 초            3.22 초
  recursive calls                        31           100,047
  execute count                          12           100,013
  session logical reads              12,118           317,604
  db block changes                   10,962           216,031
  redo size                          7.9 MB           41.3 MB
  undo change vector size            0.3 MB           10.9 MB

[TKPROF — 트리거 안의 UPDATE (dep=1)]
UPDATE T_LOAD_STAMP SET LAST_DT = SYSDATE, LAST_PGM = SYS_CONTEXT('USERENV', 'MODULE') WHERE TBL_NM = 'T_ORD_STLM'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 100000      1.71       1.86          0     100000     102461      100000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   100001      1.71       1.86          0     100000     102461      100000

문제

UPDATE 한 줄짜리 트리거 때문에 적재가 여덟 배 느려진 이유를 세션 통계와 TKPROF로 설명하시오.
화면에 보이는 갱신 시각의 뜻이 바뀌지 않게 트리거를 고치고, 고친 뒤의 일량을 숫자로 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 갱신 시각은 '문장마다 한 번' 이면 된다 — 행 구분(FOR EACH ROW)을 뗀 문장 트리거
CREATE OR REPLACE TRIGGER TRG_STLM_STAMP
AFTER INSERT ON T_ORD_STLM
BEGIN
  UPDATE T_LOAD_STAMP
     SET LAST_DT  = SYSDATE,
         LAST_PGM = SYS_CONTEXT('USERENV', 'MODULE')
   WHERE TBL_NM = 'T_ORD_STLM';
END;
/

-- P1 적재는 그대로 둔다 (10만 건을 넣는 문장 하나에 트리거도 한 번)

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

xplan

[세션 통계 — P1 적재 한 번 (문장 트리거)]
  경과 시간 0.43 초 · recursive calls 33 · execute count 13
  session logical reads 12,126 · db block changes 10,966
  redo size 7.9 MB · undo change vector size 0.3 MB

[TKPROF — 트리거 안의 UPDATE]  Execute 1 · query 1 · current 3 · rows 1

[정산 등록 화면 — 한 건]  트리거 한 번 · LAST_DT가 저장 시각으로 바뀐다 (전과 같다)

모범 답안 서술

세션 통계의 recursive calls 100,047과 TKPROF의 Execute 100,000은 P1이 넣은 행 수와 같다. 트리거가 FOR EACH ROW 행 트리거라서 10만 행을 넣는 INSERT 한 문장 안에서 트리거가 행마다 돌았고, 그때마다 트리거 안의 UPDATE가 재귀 SQL로 실행되어 T_LOAD_STAMP의 같은 한 행을 10만 번 고쳤다. 한 번은 인덱스 1블록과 그 행 하나로 가볍지만, 10만 번이 모이자 논리 읽기가 12,118 → 317,604, redo가 7.9 → 41.3MB, undo가 0.3 → 10.9MB로 늘었다. 게다가 행 트리거가 붙으면 INSERT … SELECT도 여러 행을 묶어 넣지 못하고 한 행씩 넣는다(교재 14화). 트리거 안 SQL의 일은 바깥 INSERT의 실행계획에 나오지 않아서 "UPDATE 한 줄"로만 보였다. 화면이 보는 값은 "마지막으로 정산이 들어온 시각" 하나다. 10만 건을 넣는 문장이든 한 건을 넣는 화면이든, 문장이 끝날 때 시각을 한 번 적으면 뜻이 같다. 그래서 FOR EACH ROW를 떼어 문장 트리거로 바꾼다. 트리거는 INSERT 문장마다 한 번만 돌고, 트리거 안 UPDATE의 Execute는 100,000 → 1이 된다. recursive calls는 100,047 → 33, 경과는 3.22초 → 0.43초, redo는 41.3 → 7.9MB로 트리거를 붙이기 전과 거의 같아진다. 정산 등록 화면의 한 건 등록도 전처럼 저장 시각을 남긴다. 부작용도 적어 둔다. 문장 트리거는 넣은 행이 0건이어도 돈다. 지금 INSERT하는 곳은 P1과 정산 등록 화면 두 곳이고 둘 다 늘 한 건 이상 넣지만, 0건 적재가 생길 수 있다면 복합 트리거의 행 구간에서 건수만 세고 문장 구간(AFTER STATEMENT)에서 0보다 클 때만 시각을 고친다.

해설

행 트리거는 일을 행 수만큼 곱한다. 트리거 본문이 한 줄이어도 10만 행을 넣는 문장 안에서는 10만 번 돈다. 트리거 안 SQL은 재귀 SQL이라 바깥 문장의 계획에는 보이지 않고, recursive calls와 TKPROF의 dep=1 문장으로 드러난다(교재 15화).
락도 길어진다. 트리거 안 UPDATE는 T_LOAD_STAMP 한 행의 락을 적재가 커밋할 때까지 쥔다. 그동안 정산 등록 화면의 INSERT는 트리거에서 그 행을 기다린다. 적재가 3.22초 → 0.43초로 줄면 그 기다림도 함께 준다.
IF로 "마지막 행일 때만" 고치려 해도 행 트리거는 지금 행이 마지막인지 모른다. 행마다 할 일과 문장마다 할 일을 먼저 가르고, 문장마다 할 일은 문장 트리거나 복합 트리거의 문장 구간에 둔다.
트리거가 있는 테이블은 /*+ APPEND */를 붙여도 직접 경로로 적재되지 않을 수 있다(교재 14화 §4.3). P1에 APPEND를 붙일 계획이라면 실행계획 첫 줄이 LOAD AS SELECT인지 확인하고, 아니라면 P1이 갱신 시각을 직접 적고 트리거를 없애는 설계와 견준다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황