월초 주문 스냅샷 10만 건이 같은 10만 건을 넣는 정산 적재보다 아홉 배 느리다

중급DDL한빛유통694 / 700

상황

한빛유통은 매월 1일 02:00에 주문 10만 건의 상태를 주문 이력 T_ORD_HIS에 스냅샷으로 남긴다. 이력 번호(HIS_SEQ)는 2016년에 만든 트리거 TRG_ORD_HIS_BI가 시퀀스로 채운다. 고객센터의 주문 상태 변경 화면도 T_ORD_HIS에 한 줄씩 넣는데, 이 화면은 HIS_SEQ 자리에 NULL을 넣는다(트리거가 채운다는 전제다). 스냅샷이 3.6초 걸린다. 오과장이 물었다. "같은 T_ORD 10만 건을 넣는 P1 적재는 0.4초인데 왜 아홉 배죠?"

테이블과 인덱스

T_ORD: 100,000행 · 896블록
T_ORD_HIS 주문 이력(이 문제용): HIS_SEQ NUMBER NOT NULL · ORD_SEQ · ORD_ST · HIS_DT · HIS_TP('SNAP' 월초 스냅샷, 'CHG' 상태 변경) · PK_T_ORD_HIS (HIS_SEQ) · 지금 2,412,000행
시퀀스 SQ_ORD_HIS: CACHE 20(기본값) · 이력 번호는 겹치지만 않으면 되고, 결번은 상관없다
고객센터 화면: INSERT INTO T_ORD_HIS VALUES (NULL, :ord_seq, :st, SYSDATE, 'CHG') · 하루 약 600번 · 화면 소스는 이번에 고치지 않는다
운영 DB는 19c다.

원본 SQL

sql

-- 2016년 트리거 — 이력 번호를 시퀀스로 채운다
CREATE OR REPLACE TRIGGER TRG_ORD_HIS_BI
BEFORE INSERT ON T_ORD_HIS
FOR EACH ROW
BEGIN
  SELECT SQ_ORD_HIS.NEXTVAL INTO :NEW.HIS_SEQ FROM dual;
END;
/

-- 월초 주문 스냅샷 (매월 1일 02:00)
INSERT INTO T_ORD_HIS (ORD_SEQ, ORD_ST, HIS_DT, HIS_TP)
SELECT ORD_SEQ, ORD_ST, TRUNC(SYSDATE), 'SNAP'
  FROM T_ORD;
COMMIT;

실행계획 학습용 예시

xplan

[세션 통계 — 월초 스냅샷 10만 건]
  경과 시간 3.62 초 · CPU 3.31 초
  recursive calls          105,061
  execute count            105,014
  session logical reads    341,207
  redo size                38.9 MB

[TKPROF]
INSERT INTO T_ORD_HIS (ORD_SEQ, ORD_ST, HIS_DT, HIS_TP) SELECT ORD_SEQ, ORD_ST, TRUNC(SYSDATE), 'SNAP' FROM T_ORD   (dep=0)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Execute      1      0.95       1.02          0        904     314377      100000

SELECT SQ_ORD_HIS.NEXTVAL FROM DUAL                                    (dep=1 · 트리거 안)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Execute 100000      1.44       1.52          0          0          0           0
Fetch   100000      0.38       0.41          0          0          0      100000

update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,highwater=:8,... where obj#=:1   (dep=2)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Execute   5000      0.22       0.24          0       5000      10213        5000

[비교 — P1 적재 (T_ORD → T_ORD_STLM · 트리거 없음)]  경과 0.41 초 · recursive calls 31

문제

같은 10만 건인데 스냅샷만 아홉 배 느린 이유를 TKPROF의 세 문장으로 설명하시오.
이력 번호의 번호원(시퀀스 하나)과 고객센터 화면을 그대로 두고, 스냅샷이 행마다 번호를 받으러 가지 않도록 고치시오. 화면이 HIS_SEQ에 NULL을 넣는다는 점을 어떻게 처리했는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 번호는 SQL 엔진이 INSERT 안에서 붙인다 — 컬럼 기본값 (12c 이상)
--    ON NULL : 화면처럼 HIS_SEQ 자리에 NULL을 넣어도 기본값을 쓴다
ALTER TABLE T_ORD_HIS MODIFY (HIS_SEQ DEFAULT ON NULL SQ_ORD_HIS.NEXTVAL);

-- ② 행마다 번호를 받으러 가던 트리거를 지운다 (같은 시퀀스라 번호원은 그대로 하나)
--    순서: 기본값을 먼저 걸고 트리거를 지운다
DROP TRIGGER TRG_ORD_HIS_BI;

-- ③ 번호를 한 번에 1,000개씩 받아 두면 SEQ$ 갱신도 준다 (5,000번 → 100번)
ALTER SEQUENCE SQ_ORD_HIS CACHE 1000;

-- 월초 스냅샷과 고객센터 화면은 그대로 둔다
INSERT INTO T_ORD_HIS (ORD_SEQ, ORD_ST, HIS_DT, HIS_TP)
SELECT ORD_SEQ, ORD_ST, TRUNC(SYSDATE), 'SNAP'
  FROM T_ORD;
COMMIT;

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

xplan

[세션 통계 — 고친 판]
  경과 0.46 초 · recursive calls 131 (SEQ$ 갱신 100 포함) · execute count 18
  session logical reads 27,604 · redo size 21.4 MB

[TKPROF]
  SELECT SQ_ORD_HIS.NEXTVAL FROM DUAL : 없음 (NEXTVAL은 INSERT 문장 안에서 행마다 붙는다)
  update seq$ … : Execute 100

[고객센터 화면 시험]
  INSERT INTO T_ORD_HIS VALUES (NULL, 412, 'CANCEL', SYSDATE, 'CHG')  → HIS_SEQ가 시퀀스 번호로 채워진다

모범 답안 서술

TKPROF의 세 문장이 일을 나눠 보여 준다. 바깥 INSERT(dep=0)는 T_ORD를 904블록 읽고 10만 행을 넣었다. 그런데 행 트리거 TRG_ORD_HIS_BI 때문에 10만 행마다 트리거가 돌았고, 그때마다 트리거 안의 SELECT SQ_ORD_HIS.NEXTVAL FROM DUAL(dep=1)이 재귀 SQL로 실행됐다(Execute 100,000, Fetch 100,000). 한 번은 블록을 읽지 않는 FAST DUAL이지만 PL/SQL과 SQL을 10만 번 오간다. 또 시퀀스 캐시가 20이라 번호 20개를 다 쓸 때마다 사전의 SEQ$를 고친다. dep=2의 update seq$ 5,000번이 그것이다(100,000 ÷ 20). 게다가 행 트리거가 붙은 테이블에는 INSERT … SELECT도 여러 행을 묶어 넣지 못하고 한 행씩 넣으므로 바깥 INSERT의 current 읽기가 314,377로 커졌다. 트리거가 없는 P1과 같은 10만 건인데 recursive calls 105,061, 논리 읽기 341,207, 3.62초가 된 이유다. 번호를 붙이는 일을 트리거에서 테이블 정의로 옮긴다. HIS_SEQ의 기본값을 SQ_ORD_HIS.NEXTVAL로 걸면 SQL 엔진이 INSERT 문장 안에서 행마다 번호를 붙이므로 PL/SQL로 건너가지 않는다. 번호원은 같은 시퀀스 하나라 화면과 스냅샷의 번호가 겹치지 않는다. 그런데 고객센터 화면은 HIS_SEQ 자리에 NULL을 직접 넣는다. 그냥 DEFAULT는 컬럼을 INSERT에서 뺐을 때만 쓰이므로, NULL을 넣어도 기본값을 쓰는 DEFAULT ON NULL로 건다. 화면 소스는 고치지 않아도 된다. 기본값을 먼저 걸고 트리거를 지운다. 반대로 하면 그 사이에 들어온 화면 INSERT가 ORA-01400으로 실패한다. 트리거가 사라지자 INSERT … SELECT가 다시 여러 행을 묶어 넣는다. 시퀀스 캐시를 1,000으로 키워 SEQ$ 갱신도 5,000번 → 100번으로 줄인다. 경과는 3.62초 → 0.46초, recursive calls는 105,061 → 131, 논리 읽기는 341,207 → 27,604, redo는 38.9 → 21.4MB가 된다. P1보다 조금 더 드는 것은 PK_T_ORD_HIS 인덱스를 함께 고치는 몫이다.

해설

다른 정답(12c 이전이거나 컬럼 정의를 바꿀 수 없을 때): 스냅샷 SELECT에 SQ_ORD_HIS.NEXTVAL을 직접 넣고, 트리거에 WHEN (NEW.HIS_SEQ IS NULL)을 달아 화면처럼 NULL로 넣을 때만 본문이 돌게 한다. 다만 행 트리거가 남아 있어 한 행씩 넣는 비용은 남는다.
IDENTITY 컬럼(GENERATED BY DEFAULT ON NULL AS IDENTITY)도 같은 일을 한다. 기존 컬럼을 IDENTITY로 바꿀 수는 없어서 새 컬럼으로 옮기는 작업이 필요하고, 시작 번호를 지금 최댓값 뒤로 맞춰야 한다.
CACHE를 키우는 대가: 인스턴스가 다시 뜨거나 공유 풀에서 시퀀스가 밀려나면 받아 둔 번호가 사라져 결번이 생긴다(교재 12화 §6). 이력 번호는 겹치지만 않으면 되는 번호라 받아들일 수 있다. 결번에 사유가 필요한 번호라면 따로 판단한다.
PL/SQL에서 v := SQ_ORD_HIS.NEXTVAL;로 대입해도 행마다 시퀀스를 따로 부르는 것은 같다. 시퀀스는 SQL 문장 안(INSERT … SELECT의 SELECT 목록, VALUES, 컬럼 기본값)에서 붙여야 행마다의 왕복이 없다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황