콜센터 주문상품 10만 행을 POS 쪽으로 옮기는 데 11.8초, 그중 대부분이 트리거 안의 조회다

중급INSERT버거475 / 700

상황

버거원 본사는 따로 돌던 콜센터(전화 주문) 시스템을 POS 쪽으로 합친다. 콜센터의 지난 1년 주문은 이미 ORD에 옮겼고, 이번에는 그 주문상품 102,416행을 ORD_ITEM에 옮긴다. 리허설에서 이 단계가 11.8초 걸렸다. 반영은 다음 주 화요일 14:00이고, 그 시간에도 매장 POS가 ORD_ITEM에 계속 넣는다. ORD_ITEM의 ORD_DT와 ORD_HMS는 ORD에서 복사해 둔 칸(반정규화)이다. POS 앱은 이 두 칸을 보내지 않으므로, 2015년부터 행 트리거가 ORD를 찾아 채운다.

테이블과 인덱스

ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) · ORD_ITEM_X01 (ORD_DT, ORD_HMS) · 행 트리거 TRG_ORD_ITEM_BI
ORD 주문: 이 문제에서는 콜센터 주문 41,060건이 더 들어가 1,041,060행 · 23,340블록이다. ORD_PK (ORD_NO)
STG_CALL_ITEM 콜센터 주문상품: 102,416행 · 512블록 · ORD_NO · ITEM_ID · QTY. 모든 ORD_NO가 ORD에 있다.
영업 시간에는 200개 매장 POS가 초당 1건 남짓 ORD_ITEM에 넣는다. FK는 없다.

원본 SQL

sql

-- 2015년부터 있는 트리거
CREATE OR REPLACE TRIGGER TRG_ORD_ITEM_BI
BEFORE INSERT ON ORD_ITEM
FOR EACH ROW
BEGIN
  SELECT ORD_DT, ORD_HMS
    INTO :NEW.ORD_DT, :NEW.ORD_HMS
    FROM ORD
   WHERE ORD_NO = :NEW.ORD_NO;
END;

-- 콜센터 주문상품 이관 (리허설)
INSERT INTO ORD_ITEM (ORD_NO, ITEM_ID, ORD_ITEM_QTY)
SELECT ORD_NO, ITEM_ID, QTY
  FROM STG_CALL_ITEM;
COMMIT;

실행계획 학습용 예시

xplan

--------------------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |              |      1 |        |      0 |00:00:11.78 |   1046K |
|   1 |  LOAD TABLE CONVENTIONAL | ORD_ITEM     |      1 |        |      0 |00:00:11.78 |   1046K |
|   2 |   TABLE ACCESS FULL      | STG_CALL_ITEM |      1 |    102K|    102K|00:00:00.02 |     512 |
--------------------------------------------------------------------------------------------------

[세션 통계 — 리허설 한 번]
  경과 시간                 11.78 초
  CPU used by this session   9.62 초
  recursive calls          103,264
  session logical reads    1,046,310
  redo size                 96.4 MB

[TKPROF — 트리거 안의 문장]
SELECT ORD_DT, ORD_HMS FROM ORD WHERE ORD_NO = :B1
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Execute 102416      1.92       2.10          0          0          0           0
Fetch   102416      1.41       1.55          0     307248          0      102416

문제

11.8초 가운데 무엇이 행마다 반복되는지 TKPROF와 세션 통계로 설명하시오.
매장 POS가 계속 넣는 시간에 반영한다는 조건을 지키면서 이관 SQL과 트리거를 고치시오.
고친 뒤에도 남는 비용이 있으면 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 트리거는 POS 처럼 두 칸을 보내지 않는 INSERT 에서만 본문을 돈다 (영업 중이라 끌 수 없다)
CREATE OR REPLACE TRIGGER TRG_ORD_ITEM_BI
BEFORE INSERT ON ORD_ITEM
FOR EACH ROW
WHEN (NEW.ORD_DT IS NULL)
BEGIN
  SELECT ORD_DT, ORD_HMS
    INTO :NEW.ORD_DT, :NEW.ORD_HMS
    FROM ORD
   WHERE ORD_NO = :NEW.ORD_NO;
END;

-- ② 이관은 ORD 를 한 번 조인해 두 칸을 직접 넣는다
INSERT INTO ORD_ITEM (ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS)
SELECT s.ORD_NO, s.ITEM_ID, s.QTY, o.ORD_DT, o.ORD_HMS
  FROM STG_CALL_ITEM s
  JOIN ORD o ON o.ORD_NO = s.ORD_NO;
COMMIT;

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

xplan

----------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |              |      1 |        |      0 |00:00:02.94 |    763K |          |
|   1 |  LOAD TABLE CONVENTIONAL | ORD_ITEM     |      1 |        |      0 |00:00:02.94 |    763K |          |
|*  2 |   HASH JOIN              |              |      1 |    102K|    102K|00:00:00.41 |   23852 | 7340K (0)|
|   3 |    TABLE ACCESS FULL     | STG_CALL_ITEM |      1 |    102K|    102K|00:00:00.02 |     512 |          |
|   4 |    TABLE ACCESS FULL     | ORD          |      1 |   1041K|   1041K|00:00:00.21 |   23340 |          |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("O"."ORD_NO"="S"."ORD_NO")

[세션 통계] 경과 11.78 → 2.94 초 · CPU 9.62 → 2.05 초 · recursive calls 103,264 → 58
            logical reads 1,046,310 → 763,400 · redo 96.4 → 88.1 MB
[같은 시간 POS INSERT] 두 칸 없이 들어온 행은 지금처럼 트리거가 채운다 · NULL 0

모범 답안 서술

11.8초의 대부분은 트리거가 행마다 하는 일이다. 이관 INSERT가 102,416행을 넣는 동안 BEFORE INSERT … FOR EACH ROW 트리거가 행마다 돌며 ORD를 찾아가는 SELECT를 실행했다. TKPROF의 Execute와 Fetch가 102,416이고, ORD_PK와 테이블에서 행마다 3블록씩 307,248블록을 읽었다. 재귀 SQL을 열고 실행하고 PL/SQL로 돌아오는 일이 10만 번이라 recursive calls 103,264, CPU 9.62초가 됐다. 이관 원천은 ORD와 한 번 조인하면 두 칸을 함께 가져온다. 이관 INSERT에 ORD_DT와 ORD_HMS를 넣고, STG와 ORD를 해시 조인해 직접 채운다. 그러면 ORD는 행마다 찾아가지 않고 한 번 훑는다(23,340블록). 트리거는 끄지 않는다. 반영 시간에 200개 매장 POS가 두 칸 없이 ORD_ITEM에 넣고 있어서, 트리거를 끄면 그동안 들어온 POS 행의 날짜가 비어 버린다. 대신 WHEN (NEW.ORD_DT IS NULL)을 붙인다. WHEN 조건은 본문을 부르기 전에 판단하므로, 두 칸을 이미 채워 넣는 이관 행에서는 SELECT가 돌지 않고, POS 행에서는 지금처럼 채운다. 고친 뒤 경과는 11.78 → 2.94초, recursive calls는 103,264 → 58, CPU는 9.62 → 2.05초로 준다. 남는 비용도 있다. 행 트리거가 정의돼 있는 한 INSERT는 여러 행을 한 번에 쓰지 못하고 한 행씩 처리되며 APPEND도 쓸 수 없다(redo 88.1MB 중 일부). 인덱스 둘을 고치는 몫도 그대로다. 이 비용까지 없애려면 POS 앱이 두 칸을 직접 보내게 바꾸고 트리거를 지워야 한다.

해설

함정: 이관 동안만 트리거를 DISABLE하면 이관은 더 빨라지지만, 같은 시간 POS가 넣은 행은 날짜가 비고 아무 에러도 나지 않는다. 공유 테이블의 트리거는 반영 시간대를 함께 보고 판단한다.
스칼라 서브쿼리로 (SELECT ORD_DT FROM ORD WHERE ORD_NO = s.ORD_NO)를 두 번 쓰면 PL/SQL 호출은 사라지지만 행마다 ORD를 두 번 찾아간다. ORD_NO가 유일해 서브쿼리 캐싱도 듣지 않는다. 조인이 맞다.
반정규화 칸은 원천을 고칠 때마다 사본도 고쳐야 한다(DML-002). 칸을 채우는 방법이 트리거 하나뿐이면, 그 트리거가 빠진 경로(직접 경로 적재, 다른 시스템의 INSERT)에서 빈 칸이 생긴다.
트리거가 있으면 여러 행 INSERT가 한 행씩 바뀌는 비용은 교재 14화 §1의 실측(redo 38.55 → 50.95MB, 블록 변경 약 10만 증가)과 같은 모양이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황