10만 건을 한 건씩 넣고 1,000건마다 커밋하는 정산 적재

중급PLSQL한빛유통426 / 700

상황

야간 정산 배치의 적재 단계에서는 주문 10만 건을 정산 테이블로 옮긴다. 배치 담당 오과장: "04시 마감인데 다른 단계와 겹치면 넘긴다." 소스의 커밋 줄 옆에는 -- 트랜잭션은 짧게 (2020.07 강도현)라는 주석이 있다. 그해 7월 언두 공간 부족 장애 뒤에 넣은 줄이다.

테이블과 인덱스

T_ORD(원천): 100,000행 · 896블록
T_ORD_STLM(대상): 배치를 시작할 때마다 비운다(TRUNCATE). 인덱스는 없고, 다른 세션은 배치가 끝난 뒤에 읽는다.
DB는 NOARCHIVELOG다.

원본 SQL

sql

PROCEDURE P1_LOAD(p_limit NUMBER DEFAULT 100000) IS
  n NUMBER := 0;
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE T_ORD_STLM';
  FOR r IN (SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
              FROM T_ORD
             WHERE ROWNUM <= p_limit) LOOP
    n := n + 1;
    INSERT INTO T_ORD_STLM (STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT)
    VALUES (n, r.ORD_SEQ, r.CUS_ID, r.ORD_DT, r.ORD_AMT);
    IF MOD(n, 1000) = 0 THEN COMMIT; END IF;   -- 트랜잭션은 짧게 (2020.07 강도현)
  END LOOP;
  COMMIT;
END;

실행계획 실측 · 교재 18화(복사본, 세션 통계)

xplan

[세션 통계 — P1_LOAD(100000) 한 번]
  경과 시간                 850 ~ 1,010 ms
  CPU used by this session  430 ~ 470 ms
  session logical reads     110,130 ~ 110,420
  redo size                 28.6 MB
  undo change vector size   6.5 MB
  user commits              101
  INSERT 실행 횟수          100,000

[TKPROF 모양 — 루프 안의 INSERT]
INSERT INTO T_ORD_STLM (STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT) VALUES (:B5, :B4, :B3, :B2, :B1)
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Execute 100000      (CPU 의 대부분)              0      (적음)   (대부분)     100000

문제

이 배치의 일량이 어디서 나오는지 실행 횟수, 논리 읽기, redo, 커밋으로 나눠 설명하시오.
같은 결과(정산 번호 1 ~ 100,000 포함)를 내도록 배치를 고치고, 1,000건 커밋이 그때 왜 들어갔으며 지금은 어떻게 할지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE P1_LOAD(p_limit NUMBER DEFAULT 100000) IS
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE T_ORD_STLM';
  -- 루프 · 행마다 INSERT · 1,000 건 커밋 → 한 문장 · 직접 경로 · 커밋 한 번
  INSERT /*+ APPEND */ INTO T_ORD_STLM (STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT)
  SELECT ROWNUM, ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
    FROM T_ORD
   WHERE ROWNUM <= p_limit;
  COMMIT;
END;

개선 후 실행계획 실측 · 교재 18화(복사본, 세션 통계)

xplan

[세션 통계 — 같은 10만 건]
  경과 시간                 100 ~ 110 ms
  CPU used by this session  70 ms
  session logical reads     2,869 ~ 2,961
  redo size                 0.08 ~ 0.10 MB
  undo change vector size   0.02 ~ 0.03 MB
  user commits              2   (TRUNCATE 의 암시적 커밋 + 마지막 COMMIT)

-------------------------------------------------------------------------
| Id  | Operation                        | Name       | Starts | A-Rows |
-------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |            |      1 |      0 |
|   1 |  LOAD AS SELECT                  | T_ORD_STLM |      1 |      0 |
|   2 |   OPTIMIZER STATISTICS GATHERING |            |      1 |   100K |
|*  3 |    COUNT STOPKEY                 |            |      1 |   100K |
|   4 |     TABLE ACCESS FULL            | T_ORD      |      1 |   100K |
-------------------------------------------------------------------------

모범 답안 서술

일량의 대부분은 INSERT가 10만 번 실행되는 데서 나온다. 행마다 PL/SQL과 SQL 엔진을 오가고(컨텍스트 전환), 매번 블록을 찾아 한 행씩 넣으며 redo와 undo를 만들고, 1,000건마다 커밋한다(101번). 그래서 논리 읽기가 약 11만, redo가 28.6MB에 이른다. 한 문장 INSERT /*+ APPEND */ … SELECT ROWNUM, …으로 바꾸면 정산 번호까지 한 번에 만들고, 직접 경로 적재(LOAD AS SELECT)로 버퍼 캐시를 거치지 않고 HWM 위에 블록을 통째로 쓴다. 대상을 매번 비우는 적재라서 이렇게 할 수 있다. 논리 읽기는 약 11만 → 약 2,900, redo는 28.6 → 0.1MB, 커밋은 101 → 2로 준다. 1,000건 커밋은 2020.07 언두 공간 부족 장애 뒤의 처방이었고 그때는 옳았다. 한 문장 직접 경로 적재는 테이블 데이터의 언두를 거의 만들지 않으므로(0.02MB) 중간 커밋 없이 한 번에 커밋해도 안전하다. 중간에 실패해도 반쯤 들어간 정산이 남지 않는다.

해설

APPEND의 대가: 직접 경로 적재 중에는 테이블에 배타 잠금(TM 6)이 걸려 다른 세션이 DML을 하지 못한다. 같은 세션도 커밋하기 전에는 그 테이블을 읽지 못한다(ORA-12838). 배치 전용 테이블이라 괜찮다.
redo: NOARCHIVELOG라서 테이블 데이터의 redo가 거의 생기지 않는다. ARCHIVELOG 운영계에서는 NOLOGGING이 아니면 redo가 그대로 생기고, NOLOGGING을 쓰려면 백업과 복구 정책을 함께 검토해야 한다.
인덱스가 있다면: 대상에 인덱스가 있으면 직접 경로라도 인덱스 갱신 비용이 따로 든다. 적재 전 UNUSABLE → 적재 → REBUILD 순서로 처리하는 것도 선택지다(교재 14화).
한 문장으로 쓸 수 없는 복잡한 로직이면 BULK COLLECT … LIMIT과 FORALL을 함께 쓰는 것이 차선이다. 왕복은 줄지만 직접 경로 적재는 아니다(교재 15화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 실측 · 교재 18화(복사본, 세션 통계) ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 실측 · 교재 18화(복사본, 세션 통계) ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황