11월 판매상품 4만 5천 행을 다시 넣는 데 SQL이 9만 번 실행되고, 지운 상품 번호가 판매상품에 남아 있다

중급INSERT유통472 / 700

상황

본사 레거시 유통의 판매상품 CF11에는 2016년에 만든 검증 트리거가 있다. 판매 번호와 상품 번호가 실제로 있는지, 수량이 양수인지 행마다 확인한다. 이번 주 매장 POS 서버를 바꾸면서 11월 판매상품 45,370행을 다시 받아 넣었는데(기존 11월분은 지웠다), 이 INSERT 한 문장에서 SQL이 9만 번 넘게 실행됐다. 같은 주 감사 점검에서는 상품 마스터에 없는 상품 번호를 가진 판매상품 2행이 나왔다. 개발팀: "트리거가 다 막는데 어떻게 들어갔는지 모르겠어요."

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO) · 11월 20,712건
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · 이 문제에서는 FK가 없고 트리거 TRG_CF11_CHK가 대신한다. ITM_NO가 맨 앞인 인덱스는 없다.
CF01 상품: 109행 · 5블록 · PK ITM_NO · 분기마다 「미출시 상품 정리」 배치가 판매 이력이 없는 상품을 지운다.
CF11_RCV 수신 테이블: 11월 판매상품 45,370행. 반품은 다른 테이블이라 SLE_QTY는 언제나 양수여야 한다.
POS는 계산 한 건을 트랜잭션 하나로 묶어 넣는다. 매장 6곳, 계산대 14대.

원본 SQL

sql

-- 2016년부터 있는 검증 트리거
CREATE OR REPLACE TRIGGER TRG_CF11_CHK
BEFORE INSERT OR UPDATE ON CF11
FOR EACH ROW
DECLARE
  v_cnt NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_cnt FROM CF10 WHERE SLE_NO = :NEW.SLE_NO;
  IF v_cnt = 0 THEN RAISE_APPLICATION_ERROR(-20101, '판매 없음 ' || :NEW.SLE_NO); END IF;

  SELECT COUNT(*) INTO v_cnt FROM CF01 WHERE ITM_NO = :NEW.ITM_NO;
  IF v_cnt = 0 THEN RAISE_APPLICATION_ERROR(-20102, '상품 없음 ' || :NEW.ITM_NO); END IF;

  IF :NEW.SLE_QTY <= 0 THEN RAISE_APPLICATION_ERROR(-20103, '수량 오류'); END IF;
END;

-- 이번 주 · 11월분 다시 넣기
INSERT INTO CF11 (SLE_NO, ITM_NO, SLE_QTY, SLE_UPRC)
SELECT SLE_NO, ITM_NO, SLE_QTY, SLE_UPRC
  FROM CF11_RCV;
COMMIT;

실행계획 학습용 예시

xplan

[11월분 다시 넣기 — 세션 통계]
  경과 시간                  3.84 초
  CPU used by this session   3.41 초
  recursive calls            91,032
  execute count              90,756
  session logical reads      238,640
  redo size                  14.6 MB

[TKPROF — 트리거 안의 두 문장]
SELECT COUNT(*) FROM CF10 WHERE SLE_NO = :B1
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Execute  45370      0.61       0.66          0          0          0           0
Fetch    45370      0.38       0.41          0     136110          0       45370

SELECT COUNT(*) FROM CF01 WHERE ITM_NO = :B1
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Execute  45370      0.55       0.60          0          0          0           0
Fetch    45370      0.29       0.31          0      45370          0       45370

[감사 점검 — 고아 행 2행의 내력]
  CF11에 있는데 CF01에 없는 ITM_NO      1088 · 2행
  두 행을 넣은 POS 트랜잭션             3월 14일 10:02:41 INSERT (트리거 통과) · 10:03:20 COMMIT
  상품 1088을 지운 정리 배치             3월 14일 10:02:30 DELETE · 10:02:44 COMMIT

문제

INSERT 한 문장에서 SQL이 9만 번 넘게 실행된 이유를 TKPROF로 설명하시오.
트리거가 있는데도 고아 행 2행이 들어간 이유를 두 트랜잭션의 시각으로 설명하시오.
검사를 제약으로 옮기도록 고치고, 그 제약을 켜기 전에 할 일과 켠 뒤 생기는 대가를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① FK 를 켜기 전에 이미 있는 고아 행을 찾아 사유대로 정리한다 (지금은 ITM_NO 1088 · 2행)
SELECT d.SLE_NO, d.ITM_NO
  FROM CF11 d
 WHERE NOT EXISTS (SELECT 1 FROM CF01 i WHERE i.ITM_NO = d.ITM_NO);

-- ② 검사를 선언적 제약으로 옮긴다 — SQL 엔진이 부모 키를 직접 확인한다 (재귀 SQL 없음)
ALTER TABLE CF11 ADD CONSTRAINT CF11_FK_SLE FOREIGN KEY (SLE_NO) REFERENCES CF10 (SLE_NO);
ALTER TABLE CF11 ADD CONSTRAINT CF11_FK_ITM FOREIGN KEY (ITM_NO) REFERENCES CF01 (ITM_NO);
ALTER TABLE CF11 ADD CONSTRAINT CF11_CK_QTY CHECK (SLE_QTY > 0);
DROP TRIGGER TRG_CF11_CHK;

-- ③ 11월분 다시 넣기 (문장은 그대로)
INSERT INTO CF11 (SLE_NO, ITM_NO, SLE_QTY, SLE_UPRC)
SELECT SLE_NO, ITM_NO, SLE_QTY, SLE_UPRC
  FROM CF11_RCV;
COMMIT;

-- ④ 대가: 정리 배치가 CF01 을 지우면 CF11 을 확인해야 한다
--    → 상품은 지우지 않고 사용 중지 표시로 막거나, CF11 (ITM_NO) 인덱스를 만든다 (적재 비용 하나 추가)

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

xplan

[11월분 다시 넣기 — 세션 통계]
  경과 시간                3.84 → 0.44 초
  CPU used by this session 3.41 → 0.38 초
  recursive calls          91,032 → 14
  session logical reads    238,640 → 196,820   (FK 확인은 행마다 부모 PK 인덱스를 직접 본다)
  redo size                14.6 → 11.9 MB

[같은 시각의 정리 배치를 흉내 낸 시험]
  POS 세션이 1088 을 쓰는 행을 넣고 커밋 전 → 정리 배치의 DELETE FROM CF01 은 기다린다
  POS 커밋 뒤 → DELETE 는 ORA-02292 (자식 행이 있다) 로 실패 · 고아 행 0

모범 답안 서술

INSERT 한 문장이 4만 5천 행을 넣는 동안 트리거가 행마다 돌면서 SELECT를 두 번씩 실행했다. TKPROF의 두 SELECT가 각각 Execute 45,370이고, 합쳐서 recursive calls 91,032다. 한 번 한 번은 몇 블록이지만, 행마다 PL/SQL 트리거를 부르고 SQL을 열어 실행하고 결과를 돌려받는 일이 9만 번 쌓여 CPU 3.41초가 됐다. 행 트리거가 있으면 여러 행을 한 번에 쓰던 INSERT도 한 행씩 쓰게 된다. 고아 행 2행은 트리거 검사의 구멍이다. 정리 배치는 10:02:30에 상품 1088을 지웠지만 커밋은 10:02:44에 했다. 그 사이 10:02:41에 POS 트리거가 CF01을 읽었는데, 다른 세션이 커밋하지 않은 삭제는 보이지 않으므로 1088이 아직 있는 것으로 보고 통과시켰다. 두 트랜잭션이 각자 커밋하자 부모 없는 자식 행이 남았다. SELECT로 하는 검사는 동시에 도는 트랜잭션을 막지 못한다. 검사를 선언적 제약으로 옮긴다. CF11 (SLE_NO) → CF10, CF11 (ITM_NO) → CF01 FK와 CHECK (SLE_QTY > 0)을 걸고 트리거를 지운다. FK는 SQL 엔진 안에서 부모 PK 인덱스를 직접 확인하므로 재귀 SQL이 없고(91,032 → 14), 경과는 3.84 → 0.44초로 준다. 무엇보다 부모를 지우려는 트랜잭션과 자식을 넣는 트랜잭션이 서로를 기다리게 만들어, 같은 시각에 돌아도 고아 행이 생기지 않는다(POS가 커밋하면 정리 배치의 DELETE가 ORA-02292로 실패한다). FK를 켜기 전에는 NOT EXISTS로 이미 있는 고아 행을 찾아 사유대로 정리한다. 그대로 두면 FK를 켜는 순간 ORA-02298로 실패한다. 켠 뒤의 대가도 있다. 정리 배치가 부모(CF01)를 지울 때마다 오라클은 CF11에 그 상품을 쓰는 행이 있는지 확인해야 하는데, ITM_NO가 맨 앞인 인덱스가 없으면 상품 하나마다 CF11 8,204블록을 통째로 읽고 그동안 CF11에 잠금을 건다. 상품을 지우지 않고 사용 중지 표시로 막거나, CF11 (ITM_NO) 인덱스를 만들어 적재 비용 하나를 더 치르는 것 중에서 골라야 한다.

해설

FK 확인도 공짜는 아니다. 행마다 부모 PK 인덱스를 보므로 논리 읽기가 행당 약 2 ~ 4블록 붙는다(교재 14화 §1: FK 하나로 5만 행 논리 읽기 453,243 → 547,221). 줄어드는 것은 재귀 SQL과 PL/SQL 호출, 그리고 검사의 구멍이다.
함정: 트리거 안에서 SELECT … FOR UPDATE로 부모 행을 잠그면 구멍은 막을 수 있지만, POS 계산대 14대가 같은 인기 상품 행을 두고 줄을 선다. 제약이 하는 일을 손으로 다시 만들지 않는다.
FK를 ENABLE NOVALIDATE로 켜면 기존 행은 건너뛰고 새 행만 검사한다. 고아 행 사유를 정리할 시간이 필요할 때 쓸 수 있지만, 감사 지적이 '이미 있는 고아 행'이라면 VALIDATE까지 해야 끝난다.
부모를 지울 때의 잠금과 전체 스캔은 교재 12화의 '인덱스 없는 FK' 모양이다. FK를 거는 날 부모를 지우는 배치가 있는지 함께 확인한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황