인수한 매장 12곳의 주문을 옮기는 데 5.7초, 올해 세 번 더 해야 하는데 안이 네 개 올라왔다

실전PLSQL버거698 / 700

상황

버거 본사가 다른 브랜드의 매장 12곳을 인수했다. 그 매장들의 2013년 주문은 옛 POS 번호 체계라서, 이관팀이 스테이징 테이블에 받아 두고 본사 ORD와 ORD_ITEM으로 옮긴다. 새 주문 번호는 본사 POS와 같은 시퀀스 SQ_ORD_NO로 매긴다. 리허설에서 이관 프로그램이 5.7초 걸렸다. 올해 인수가 세 번 더 있고 다음 인수는 매장 수가 네 배다. 오과장이 시험 DB에서 안 네 개를 재 왔는데, D는 가장 빨랐지만 결과 건수가 이상했다. 박팀장: "하나를 골라 프로그램을 써 오고, 고른 안의 전제와 부작용까지 적어 줘요."

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_PK (ORD_NO) · ORD_X01 (ORD_DT, ORD_HMS) · ORD_NO는 시퀀스 SQ_ORD_NO(CACHE 20)로 매긴다 · 트리거 없음
ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) · ORD_ITEM_X01 (ORD_DT, ORD_HMS)
STG_ORD 인수 매장 주문(이 문제용): 41,200행 · OLD_ORD_KEY VARCHAR2(20)(옛 POS 번호) · ORD_DT · ORD_HMS · SHOP_NO · PK (OLD_ORD_KEY)
STG_ORD_ITEM 인수 매장 주문상품(이 문제용): 102,957행 · OLD_ORD_KEY · ITEM_ID · ORD_ITEM_QTY · PK (OLD_ORD_KEY, ITEM_ID)
스테이징 두 테이블은 이관팀 것이라 칸을 더해도 된다. 이관은 POS가 닫힌 02:00 ~ 05:00에 돌고, 인수 한 번은 한 트랜잭션으로 옮긴다(실패하면 전부 되돌린다).

원본 SQL

sql

PROCEDURE P_MIG_ACQ IS
  v_no ORD.ORD_NO%TYPE;
BEGIN
  FOR o IN (SELECT OLD_ORD_KEY, ORD_DT, ORD_HMS, SHOP_NO FROM STG_ORD) LOOP
    SELECT SQ_ORD_NO.NEXTVAL INTO v_no FROM dual;                  -- 주문마다 새 번호
    INSERT INTO ORD (ORD_NO, ORD_DT, ORD_HMS, SHOP_NO)
    VALUES (v_no, o.ORD_DT, o.ORD_HMS, o.SHOP_NO);
    FOR i IN (SELECT ITEM_ID, ORD_ITEM_QTY
                FROM STG_ORD_ITEM
               WHERE OLD_ORD_KEY = o.OLD_ORD_KEY) LOOP
      INSERT INTO ORD_ITEM (ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS)
      VALUES (v_no, i.ITEM_ID, i.ORD_ITEM_QTY, o.ORD_DT, o.ORD_HMS);
    END LOOP;
  END LOOP;
  COMMIT;
END;

실행계획 학습용 예시

xplan

[세션 통계 — 리허설 (주문 41,200 · 주문상품 102,957)]
  경과 5.7 초 · recursive calls 311,437 · session logical reads 878,412 · redo size 71 MB

[TKPROF — 루프 안의 문장 (dep=1)]
                                                       call      count    query    current      rows
SELECT SQ_ORD_NO.NEXTVAL FROM DUAL                     Execute   41,200        0          0         0
                                                       Fetch     41,200        0          0    41,200
INSERT INTO ORD (…) VALUES (:B4, :B3, :B2, :B1)        Execute   41,200    1,214    170,388    41,200
SELECT ITEM_ID, ORD_ITEM_QTY FROM STG_ORD_ITEM         Execute   41,200        0          0         0
  WHERE OLD_ORD_KEY = :B1                              Fetch     41,200  164,800          0   102,957
INSERT INTO ORD_ITEM (…) VALUES (:B5, …)               Execute  102,957    3,018    521,646   102,957
update seq$ … (dep=2)                                  Execute    2,060    2,060      4,120     2,060

[오과장의 네 안 — 시험 DB · 같은 41,200건]
  안                                                         경과    recursive calls   redo     결과 (ORD / ORD_ITEM)
  A 지금 판 (주문마다 NEXTVAL, 줄마다 INSERT)                   5.7 초        311,437     71 MB    41,200 / 102,957
  B FORALL INSERT … RETURNING BULK COLLECT (1,000건씩)          0.9 초          2,247     52 MB    41,200 / 102,957
  C 스테이징에 새 번호를 먼저 매기고 INSERT … SELECT 두 문장      0.6 초          2,151     58 MB    41,200 / 102,957
  D INSERT ALL (STG_ORD ⋈ STG_ORD_ITEM이 원천, NEXTVAL과 CURRVAL) 0.5 초          5,231     67 MB   102,957 / 102,957

[D의 문장]
  INSERT ALL
    INTO ORD      (ORD_NO, ORD_DT, ORD_HMS, SHOP_NO)                 VALUES (SQ_ORD_NO.NEXTVAL, ORD_DT, ORD_HMS, SHOP_NO)
    INTO ORD_ITEM (ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS)  VALUES (SQ_ORD_NO.CURRVAL, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS)
  SELECT o.ORD_DT, o.ORD_HMS, o.SHOP_NO, i.ITEM_ID, i.ORD_ITEM_QTY
    FROM STG_ORD o, STG_ORD_ITEM i
   WHERE i.OLD_ORD_KEY = o.OLD_ORD_KEY;
  → 205,914 rows created (에러 없음)

문제

지금 판의 일량이 어디서 나오는지 TKPROF로 설명하시오.
네 안을 비교해 하나를 고르고 이관 프로그램을 작성하시오. D가 에러 없이 끝났는데 왜 틀렸는지도 쓰시오.
고른 안의 전제 조건과 부작용을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 한 번만: 옛 번호 ↔ 새 번호의 짝을 남길 칸
ALTER TABLE STG_ORD ADD (NEW_ORD_NO NUMBER);

PROCEDURE P_MIG_ACQ IS
  v_ord  PLS_INTEGER;
  v_item PLS_INTEGER;
  v_src  PLS_INTEGER;
BEGIN
  -- ① 스테이징 주문마다 새 번호를 매긴다 (NEXTVAL은 UPDATE의 SET에서 행마다 하나씩, SQL 엔진 안에서)
  UPDATE STG_ORD SET NEW_ORD_NO = SQ_ORD_NO.NEXTVAL WHERE NEW_ORD_NO IS NULL;

  -- ② 주문
  INSERT INTO ORD (ORD_NO, ORD_DT, ORD_HMS, SHOP_NO)
  SELECT NEW_ORD_NO, ORD_DT, ORD_HMS, SHOP_NO
    FROM STG_ORD;
  v_ord := SQL%ROWCOUNT;

  -- ③ 주문상품 — 옛 번호로 스테이징 주문에 조인해 새 번호를 붙인다
  INSERT INTO ORD_ITEM (ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS)
  SELECT o.NEW_ORD_NO, i.ITEM_ID, i.ORD_ITEM_QTY, o.ORD_DT, o.ORD_HMS
    FROM STG_ORD o, STG_ORD_ITEM i
   WHERE i.OLD_ORD_KEY = o.OLD_ORD_KEY;
  v_item := SQL%ROWCOUNT;

  -- ④ 대조 — 원천과 넣은 건수가 같아야 커밋한다 (인수 한 번 = 한 트랜잭션)
  SELECT COUNT(*) INTO v_src FROM STG_ORD_ITEM;
  IF v_item <> v_src THEN
    ROLLBACK;
    RAISE_APPLICATION_ERROR(-20048, '주문상품 건수 불일치 ' || v_item || ' / ' || v_src);
  END IF;
  COMMIT;
END;

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

xplan

[세션 통계 — C (같은 41,200건)]
  경과 0.6 초 · recursive calls 2,151 (SEQ$ 갱신 2,060 포함) · redo size 58 MB
  ORD 41,200 · ORD_ITEM 102,957 · STG_ORD.NEW_ORD_NO 빈 칸 0

[③ 주문상품 INSERT … SELECT]
------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |              |      1 |        |      0 |00:00:00.31 |    9012 |          |
|   1 |  LOAD TABLE CONVENTIONAL | ORD_ITEM     |      1 |        |      0 |00:00:00.31 |    9012 |          |
|*  2 |   HASH JOIN              |              |      1 |    102K|    102K|00:00:00.04 |     684 | 4104K (0)|
|   3 |    TABLE ACCESS FULL     | STG_ORD      |      1 |  41200 |  41200 |00:00:00.01 |     231 |          |
|   4 |    TABLE ACCESS FULL     | STG_ORD_ITEM |      1 |    102K|    102K|00:00:00.01 |     453 |          |
------------------------------------------------------------------------------------------------------------

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

[다시 돌려 보기 — 성공한 뒤 한 번 더]
  ① 0행 (빈 칸이 없다) → ② ORA-00001 (ORD_PK) → 전부 되돌아간다 · 두 벌로 들어가지 않는다

모범 답안 서술

지금 판은 주문마다 SQL이 세 번 나간다. 번호를 받는 SELECT SQ_ORD_NO.NEXTVAL FROM DUAL, 주문 INSERT, 그 주문의 주문상품을 찾는 SELECT가 41,200번씩 실행되고, 주문상품 줄마다 INSERT가 102,957번 실행된다. 실행과 페치를 더하면 recursive calls 311,437번 SQL로 건너간다. 한 번은 가볍지만(주문상품 조회 4블록, 주문 INSERT는 인덱스 둘을 고쳐 current 4블록 남짓) 횟수가 곱해져 논리 읽기 878,412, 5.7초가 됐다. 시퀀스 캐시가 20이라 번호 20개마다 SEQ$도 고친다(2,060번). 다음 인수는 네 배라 같은 방식이면 20초가 넘는다. D는 0.5초로 가장 빨랐지만 틀렸다. 다중 테이블 INSERT에서 NEXTVAL은 INTO 절이 몇 개든 원천 행마다 한 번 오른다. 원천이 주문과 주문상품의 조인, 곧 주문상품 줄(102,957행)이라 줄마다 새 주문 번호가 생겼고, CURRVAL은 그 줄의 번호라서 주문상품마다 주문이 하나씩 생겼다. 그래서 ORD도 102,957행이다. 주문 41,200건이 102,957건으로 쪼개졌는데, 번호가 모두 달라 PK가 겹치지 않으니 에러도 없다. 결과 건수를 대조하지 않았다면 그대로 운영에 들어갔을 것이다. C를 고른다. ① UPDATE STG_ORD SET NEW_ORD_NO = SQ_ORD_NO.NEXTVAL로 스테이징 주문마다 새 번호를 매긴다. NEXTVAL은 UPDATE 안에서 SQL 엔진이 행마다 붙이므로 PL/SQL로 건너가지 않는다. ② 주문을 INSERT … SELECT로 넣고, ③ 주문상품은 옛 번호로 스테이징 주문에 해시 조인해 새 번호를 붙여 넣는다. 문장 세 개로 끝나 0.6초, recursive calls 2,151(거의 SEQ$ 갱신)이다. B(0.9초)도 맞는 답이지만 FORALL 덩어리마다 돌려받은 번호를 옛 번호에 짝지어 주문상품 배열을 다시 짜야 해서 코드가 길고, 짝은 메모리에만 있다가 사라진다. C는 옛 번호와 새 번호의 짝이 스테이징에 남아, 인수 매장의 문의("옛 영수증 번호 A-3381은 지금 몇 번이냐")와 대사, 추적에 그대로 쓸 수 있다. 전제는 셋이다. 옛 번호(OLD_ORD_KEY)가 유일해야 조인에서 주문상품이 늘지 않는다(PK가 있다). 인수 한 번을 한 트랜잭션으로 옮기고, 넣은 주문상품 수가 원천 수와 같을 때만 커밋한다. 다시 돌려도 두 벌이 되지 않아야 한다. 성공한 뒤 다시 돌리면 ①은 빈 칸이 없어 0행이고 ②가 ORD_PK에서 ORA-00001로 멈춰 전부 되돌아간다. 실패해 롤백된 판은 새 번호도 함께 비워지므로 처음부터 다시 돌리면 된다. 부작용도 있다. 새 번호는 UPDATE가 행을 읽는 순서대로 붙으므로 주문일 순서가 아니고, 2013년 주문이 2014년 번호대를 받는다. ORD_NO의 크기로 주문 순서를 추정하는 화면이 있으면 확인해야 한다. 시퀀스 캐시가 20이라 SEQ$를 2,060번 고치고, 롤백하면 받은 번호 41,200개는 결번이 된다(주문 번호는 결번을 허용한다). STG_ORD를 UPDATE하는 몫으로 redo가 B보다 6MB 많다.

해설

함정: 다중 테이블 INSERT의 NEXTVAL과 CURRVAL은 1:1로 나뉘는 행(주문 한 건이 두 테이블에 한 행씩)에는 맞지만 1:N(주문 하나에 상품 여러 줄)에는 맞지 않는다. 원천이 N쪽 줄이면 줄마다 부모가 생긴다. 이런 실수는 에러 없이 건수로만 드러나므로 건수 대조를 이관 프로그램 안에 넣는다.
번호를 주문일 순서로 매기고 싶다면, ①을 MERGE INTO STG_ORD USING (SELECT ROWID rid FROM STG_ORD ORDER BY ORD_DT, ORD_HMS) …처럼 바꿔도 순서는 보장되지 않는다. 순서가 꼭 필요하면 시퀀스 대신 ROW_NUMBER() OVER (ORDER BY ORD_DT, ORD_HMS)에 미리 받아 둔 번호 구간의 시작값을 더하는 방식을 쓰고, 그 구간을 POS가 쓰지 않게 막아야 한다.
시퀀스 캐시를 키우면 SEQ$ 갱신은 줄지만, 인스턴스가 다시 뜰 때 받아 둔 번호가 사라져 결번이 늘어난다(교재 12화 §6). POS도 같은 시퀀스를 쓰므로 캐시 변경은 POS 담당과 함께 정한다.
같은 일을 한 문장으로 처리하는 이유는 교재 15화와 같다. 루프를 FORALL로 묶은 B는 왕복을 줄이고, C는 왕복 자체를 없앤다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황