주문상세 6만 행 야간 적재가 19초로 늘자 세 사람이 서로 다른 처방을 들고 왔다

실전PLSQL한빛유통481 / 700

상황

한빛유통은 매일 01:00에 제휴몰 주문상세 약 6만 행을 대용량 주문상세 T_ORD_BIG_DET에 더한다. 이 단계가 19초로 늘어 01:00 ~ 01:30 창이 빠듯해졌다. 박팀장이 리허설 사본에서 대상 테이블의 인덱스, 제약, 트리거를 하나씩 더해 가며 6만 행 적재를 재게 했다(교재 14화 실습/01과 같은 방식). 세 사람이 처방을 들고 왔다. (가) 개발팀: "FK 검사 때문이니 FK를 지우죠." (나) DBA: "14화대로 인덱스를 끄고 APPEND로 넣은 뒤 다시 짓죠." (다) 오과장: "트리거를 지우고 수정 일시와 수정자는 적재 SELECT에서 채우죠."

테이블과 인덱스

T_ORD_BIG_DET(문제 설정, T_ORD_BIG 주문의 상세): 2,000,000행 · 8,960블록 · ORD_SEQ · ORD_DET_NO · ORD_DT · ITM_ID · ORD_QTY · UNT_PRC · UPD_DT · UPD_USER. 매일 약 6만 행(3%)이 더해진다.
PK_T_OBD (ORD_SEQ, ORD_DET_NO) · IX_OBD_ITM (ITM_ID)(상품별 판매 화면, 하루 수백 번) · IX_OBD_DT (ORD_DT)(2023년 연간 리포트 때 만들었다)
FK_OBD_ITM (ITM_ID) → M_ITM · 행 트리거 TRG_OBD_UPD(BEFORE INSERT OR UPDATE, UPD_DT와 UPD_USER를 채운다)
낮에는 고객센터 화면이 주문상세 수량을 정정한다(UPDATE, 하루 수백 건). 정정 추적에 UPD_DT와 UPD_USER를 쓴다. 주문 시스템도 낮에 한 건씩 INSERT한다.
01:00 ~ 01:30에는 이 테이블에 쓰는 다른 세션이 없다. 운영은 ARCHIVELOG다.

원본 SQL

sql

-- 매일 01:00 · 제휴몰 주문상세 적재
PROCEDURE P_OBD_LOAD IS
BEGIN
  INSERT INTO T_ORD_BIG_DET (ORD_SEQ, ORD_DET_NO, ORD_DT, ITM_ID, ORD_QTY, UNT_PRC)
  SELECT ORD_SEQ, ORD_DET_NO, ORD_DT, ITM_ID, ORD_QTY, UNT_PRC
    FROM STG_OBD;
  COMMIT;
END;

-- 2019년부터 있는 트리거
CREATE OR REPLACE TRIGGER TRG_OBD_UPD
BEFORE INSERT OR UPDATE ON T_ORD_BIG_DET
FOR EACH ROW
BEGIN
  :NEW.UPD_DT   := SYSDATE;
  :NEW.UPD_USER := USER;
END;

실행계획 학습용 예시 · 부수비용 분해는 교재 14화 §1 · §2.1의 측정 방식과 비율

xplan

[적재 청구서 — 리허설 사본 · 6만 행 · 하나씩 더해 가며 (ARCHIVELOG)]
  대상 테이블 상태                     redo       db block changes   session logical reads   경과
  아무것도 없음                         2.9 MB          2,410               3,380            0.2 초
  + PK_T_OBD                           17.4 MB        122,700             184,600            2.9 초
  + IX_OBD_ITM                         31.9 MB        243,100             365,200            6.8 초
  + IX_OBD_DT                          46.4 MB        363,400             545,900           11.2 초
  + FK_OBD_ITM                         46.4 MB        363,400             659,300           12.1 초
  + TRG_OBD_UPD (= 지금 운영)          58.8 MB        463,600             762,100           19.0 초

[(나)를 리허설 사본에서 해 본 결과]
  ALTER INDEX PK_T_OBD UNUSABLE 뒤 INSERT       → ORA-01502: index 'SQLP.PK_T_OBD' … is in unusable state
  트리거와 FK가 켜진 채 /*+ APPEND */            → LOAD TABLE CONVENTIONAL (Note: Direct Load disabled because triggers are defined)
  일반 인덱스 둘만 끄고 넣은 뒤 REBUILD          → 적재 9.8 초 + REBUILD 둘(200만 행 정렬) 21.4 초 = 31.2 초

[DBA_INDEX_USAGE · 최근 90일]
  PK_T_OBD 1,204,330 · IX_OBD_ITM 41,380 · IX_OBD_DT 0

[FK_OBD_ITM이 막은 적재 오류 — 지난 1년]
  3번 (제휴몰이 단종 상품 코드를 보냄 · ORA-02291로 적재 실패 → 제휴몰에 반송)

문제

적재 청구서를 읽고 19초와 redo 58.8MB가 무엇에서 얼마씩 나오는지 정리하시오.
세 처방 (가), (나), (다)를 각각 숫자와 부작용으로 평가하시오.
최종안을 SQL로 작성하고, 고친 뒤의 redo와 경과를 어림하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 쓰는 곳이 없는 인덱스 (90일 사용 0 · 2023년 연간 리포트용) → 한 주 INVISIBLE 로 지켜본 뒤 지운다
ALTER INDEX IX_OBD_DT INVISIBLE;
DROP INDEX IX_OBD_DT;

-- ② INSERT 때의 두 칸은 기본값이 채운다 (적재 · 주문 시스템 모두) → INSERT 에는 트리거가 돌지 않는다
ALTER TABLE T_ORD_BIG_DET MODIFY (UPD_DT DEFAULT SYSDATE, UPD_USER DEFAULT USER);

-- 트리거는 고객센터 정정(UPDATE)에만 남긴다
CREATE OR REPLACE TRIGGER TRG_OBD_UPD
BEFORE UPDATE ON T_ORD_BIG_DET
FOR EACH ROW
BEGIN
  :NEW.UPD_DT   := SYSDATE;
  :NEW.UPD_USER := USER;
END;

-- ③ PK · IX_OBD_ITM · FK_OBD_ITM 은 그대로 두고 켠 채 넣는다 (3% 증분)
PROCEDURE P_OBD_LOAD IS
BEGIN
  INSERT INTO T_ORD_BIG_DET (ORD_SEQ, ORD_DET_NO, ORD_DT, ITM_ID, ORD_QTY, UNT_PRC)
  SELECT ORD_SEQ, ORD_DET_NO, ORD_DT, ITM_ID, ORD_QTY, UNT_PRC
    FROM STG_OBD;
  COMMIT;
END;

개선 후 실행계획 학습용 예시 · 부수비용 분해는 교재 14화 §1 · §2.1의 측정 방식과 비율

xplan

[최종안 — 리허설 사본 · 같은 6만 행]
  대상 테이블 상태                          redo       db block changes   session logical reads   경과
  PK + IX_OBD_ITM + FK (트리거는 UPDATE만)   31.9 MB        243,100             478,600            7.7 초
  (지금)                                     58.8 MB        463,600             762,100           19.0 초

[세 처방을 그대로 했다면]
  (가) FK만 지우기          58.8 → 58.8 MB · 19.0 → 약 18.1 초 · 단종 상품 코드가 그대로 들어온다
  (나) 인덱스 끄고 켜기     PK 는 ORA-01502 · APPEND 는 꺼진다 · 일반 인덱스 둘만 해도 31.2 초 (지금보다 느리다)
  (다) 트리거만 INSERT에서 빼기   58.8 → 46.4 MB · 19.0 → 약 12.1 초

모범 답안 서술

청구서를 한 줄씩 빼 보면 몫이 보인다. 아무것도 없는 테이블에 넣는 일은 redo 2.9MB, 0.2초뿐이다. 인덱스 셋이 redo 14.5MB와 블록 변경 약 12만씩, 합쳐 43.5MB를 붙인다(행마다 인덱스마다 제자리를 찾아 키를 끼운다). FK는 redo와 블록 변경을 하나도 늘리지 않고 논리 읽기만 113,400(행당 약 2블록, 부모 PK 확인), 경과 0.9초를 더한다. 트리거는 redo 12.4MB, 블록 변경 약 10만을 더하고 경과를 6.9초 늘린다. 트리거 본문은 두 칸을 채울 뿐이지만, 행 트리거가 있으면 여러 행을 한 번에 쓰던 INSERT가 한 행씩 처리되기 때문이다(교재 14화 §1과 같은 모양). (가) FK 지우기는 숫자로 기각한다. FK의 몫은 논리 읽기 약 11만, 0.9초이고 redo는 그대로다. 그 대가로 지난 1년 3번 막아 낸 단종 상품 코드가 그대로 들어오게 된다. 교재 08화와 18화에서 본 고아 키가 생기는 길이다. (나) 인덱스 끄고 켜기는 이 적재에 맞지 않는다. PK는 유니크 인덱스라 UNUSABLE로 끄면 INSERT가 ORA-01502로 실패한다. 트리거와 FK가 있으면 APPEND도 에러 없이 꺼진다. 일반 인덱스 둘만 끄고 다시 지어도 3%를 더하려고 200만 행을 정렬하는 REBUILD에 21.4초가 들어 합계 31.2초로 지금보다 느리다. 14화의 처방은 테이블을 통째로 다시 채우는 재적재의 것이다. (다) 트리거 정리는 방향이 맞다. 다만 트리거를 통째로 지우면 고객센터 정정의 UPD_DT, UPD_USER가 비고, 적재 SELECT에서만 채우면 낮에 주문 시스템이 넣는 행이 빈다. 그래서 두 칸에 기본값(DEFAULT SYSDATE, DEFAULT USER)을 주어 어떤 INSERT든 채우게 하고, 트리거는 정정에 필요한 UPDATE에만 남긴다. 여기에 쓰는 곳이 없는 IX_OBD_DT(90일 사용 0)를 INVISIBLE로 지켜본 뒤 지운다. 최종안은 PK, IX_OBD_ITM, FK를 켠 채 두고, IX_OBD_DT와 INSERT 트리거를 없앤 상태다. 청구서의 'PK + IX_OBD_ITM' 줄에 FK 몫을 더하면 redo는 58.8 → 31.9MB, 블록 변경은 463,600 → 243,100, 경과는 19.0 → 약 7.7초로 어림된다. 남은 비용의 대부분은 꼭 필요한 인덱스 둘이고, 이것은 켠 채 넣는 쪽이 3% 증분에서 가장 싸다.

해설

판단 순서: 부수비용을 하나씩 떼어 숫자로 본 뒤, 각 부품이 지켜 주는 것(무결성, 조회, 정정 추적)과 맞바꿀 만한지 따진다. 가장 크다고 먼저 떼는 것도, 가장 의심스럽다고 떼는 것도 아니다(교재 14화 §2 적재 청구서).
칸 기본값은 INSERT 목록에서 그 칸을 빼야 들어간다. 프로그램이 NULL을 명시해서 넣는다면 DEFAULT ON NULL(12c 이상)을 쓴다.
UPDATE에만 남긴 트리거도 UPDATE OF ORD_QTY, UNT_PRC처럼 정정 칸으로 좁히고 WHEN으로 값이 바뀐 행만 거르면 다른 배치의 UPDATE에서 돌지 않는다(PLSQL-041).
ARCHIVELOG에서는 REBUILD도 새로 쓴 인덱스 블록만큼 redo를 만든다. (나)를 지금보다 느리게 만든 것은 시간만이 아니다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 부수비용 분해는 교재 14화 §1 · §2.1의 측정 방식과 비율 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 부수비용 분해는 교재 14화 §1 · §2.1의 측정 방식과 비율 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황