PART-040

월 확정 재적재가 도는 3분 동안 정산 화면의 6월 매출이 0원으로 나온다

중급INSERT한빛유통415 / 700

상황

한빛유통 정산팀은 매월 3일 10:00에 「월 확정 재적재」를 돌린다. 정산계가 정정을 모두 반영해 확정한 지난달 주문을 DB 링크로 받아, 주문 파티션 테이블의 그 달 칸을 통째로 다시 채운다. 같은 시각 정산팀 20명이 「월 매출」 화면으로 확정 금액을 맞춰 본다. 재적재가 도는 3분 남짓 동안 화면의 6월 매출은 0원으로 나온다. 7월 3일에는 10:02에 DB 링크가 끊겨 INSERT가 실패했고, 6월 매출은 다시 돌린 11:20까지 0원이었다. 박팀장: "도는 동안에도, 실패해도 화면에는 6월 숫자가 남아 있게 바꿔 줘."

테이블과 인덱스

T_ORD_BIG_P14 주문(파티션 복사본, 교재 14화): 1,000,000행 · PARTITION BY RANGE (ORD_DT) 월별 36칸(P202201 ~ P202412)과 빈 PMAX, 모두 37칸. P202406은 30번째 칸이고 27,390행, FULL로 약 161블록이다.
컬럼은 ORD_SEQ · CUS_ID · ORD_DT · ORD_ST · ORD_AMT다. LOCAL 인덱스 IX_P14_DT_AMT (ORD_DT, ORD_AMT) · IX_P14_CUS_DT (CUS_ID, ORD_DT)가 있고, 제약, GLOBAL 인덱스, 트리거는 없다.
T_ORD_FIX@STLM 확정 주문(정산계 테이블, DB 링크): 그 달 주문 전체를 정정까지 반영해 담는다. 6월분은 27,390행이고, 링크로 다 받는 데 약 3분이 걸린다.
「월 매출」 화면은 ORD_DT 범위로 그 달 칸 하나만 읽는다. 재적재 시각에 이 테이블을 고치는 다른 작업은 없다. 운영은 ARCHIVELOG다.

원본 SQL

sql

-- 매월 3일 10:00 · 지난달 확정 재적재 (7월 3일 = 6월분)
ALTER TABLE T_ORD_BIG_P14 TRUNCATE PARTITION P202406;

INSERT INTO T_ORD_BIG_P14 (ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT)
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_FIX@STLM
 WHERE ORD_DT >= DATE '2024-06-01'
   AND ORD_DT <  DATE '2024-07-01';
COMMIT;

-- 「월 매출」 화면
SELECT COUNT(*), SUM(ORD_AMT)
  FROM T_ORD_BIG_P14
 WHERE ORD_DT >= :v_from
   AND ORD_DT <  :v_to;

실행계획 학습용 예시 · 교재 14화 규모

xplan

[배치 로그 · 7월 3일]
  10:00:01  ALTER TABLE T_ORD_BIG_P14 TRUNCATE PARTITION P202406    0.3초 (DDL · 문장 앞뒤로 커밋)
  10:00:02  INSERT INTO T_ORD_BIG_P14 ... FROM T_ORD_FIX@STLM       시작
  10:02:14  ORA-02068: following severe error from STLM              INSERT 롤백
  11:20:05  (수동) TRUNCATE + INSERT 다시 실행                        3분 08초 · 27,390행

[INSERT · 6월 3일의 정상 실행]
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT             |               |      1 |        |      0 | 00:03:07.92 |   83118 |        |       |
|   1 |  LOAD TABLE CONVENTIONAL     | T_ORD_BIG_P14 |      1 |        |      0 | 00:03:07.92 |   83118 |        |       |
|   2 |   REMOTE                     | T_ORD_FIX     |      1 |  27390 |  27390 | 00:03:04.10 |       0 |        |       |
-------------------------------------------------------------------------------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------
   2 - SELECT "ORD_SEQ","CUS_ID","ORD_DT","ORD_ST","ORD_AMT" FROM "T_ORD_FIX" "T_ORD_FIX"
       WHERE "ORD_DT">=TO_DATE(' 2024-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
       "ORD_DT"<TO_DATE(' 2024-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') (accessing 'STLM')

  세션 통계 (TRUNCATE + INSERT + COMMIT): redo size 약 11.8 MB · undo change vector size 약 3.9 MB

[「월 매출」 화면 · 10:01 재적재 중 · :v_from = 2024-06-01, :v_to = 2024-07-01]
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |      1 |        |      1 | 00:00:00.01 |       3 |        |       |
|   1 |  SORT AGGREGATE              |               |      1 |      1 |      1 | 00:00:00.01 |       3 |        |       |
|*  2 |   FILTER                     |               |      1 |        |      0 | 00:00:00.01 |       3 |        |       |
|   3 |    PARTITION RANGE ITERATOR  |               |      1 |  27390 |      0 | 00:00:00.01 |       3 |    KEY |   KEY |
|*  4 |     TABLE ACCESS FULL        | T_ORD_BIG_P14 |      1 |  27390 |      0 | 00:00:00.01 |       3 |    KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:V_FROM<:V_TO)
   4 - filter(("ORD_DT">=:V_FROM AND "ORD_DT"<:V_TO))

  (실행 때 고른 칸: 30번 P202406 · 평소에는 A-Rows 27,390 · Buffers 161)

문제

재적재가 도는 3분 동안 화면이 6월을 0원으로 본 까닭과, 실패한 날 0원이 한 시간 넘게 남은 까닭을 TRUNCATE와 INSERT의 트랜잭션 경계로 설명하시오.
재적재가 도는 동안에도, 실패해도 화면에 6월 숫자가 남도록 파티션 교환(EXCHANGE)으로 배치를 다시 작성하시오.
교환 방식의 대가와 교환하기 전에 확인할 것을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- (1) 새 6월을 칸이 아닌 옆 테이블에 다 받는다. 그동안 칸에는 지금의 6월이 그대로 보인다
CREATE TABLE T_ORD_X202406 AS
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_FIX@STLM
 WHERE ORD_DT >= DATE '2024-06-01'
   AND ORD_DT <  DATE '2024-07-01';

-- (2) 확인 : 건수와 금액을 정산계 확정값과 맞춘다 (어긋나면 여기서 멈춘다 · 칸은 그대로다)
SELECT COUNT(*), SUM(ORD_AMT), MIN(ORD_DT), MAX(ORD_DT) FROM T_ORD_X202406;

-- (3) 칸의 로컬 인덱스와 같은 모양의 인덱스를 미리 만든다
CREATE INDEX T_ORD_X202406_I1 ON T_ORD_X202406 (ORD_DT, ORD_AMT);
CREATE INDEX T_ORD_X202406_I2 ON T_ORD_X202406 (CUS_ID, ORD_DT);

-- (4) 칸과 맞바꾼다 : 데이터는 옮기지 않고 사전에서 세그먼트의 주인만 바꾼다
--     WITH VALIDATION (기본값) : 칸 범위 밖 행이 하나라도 있으면 ORA-14099 로 멈추고 칸은 그대로다
ALTER TABLE T_ORD_BIG_P14
  EXCHANGE PARTITION P202406 WITH TABLE T_ORD_X202406
  INCLUDING INDEXES WITH VALIDATION;

-- (5) 확인 : 그 칸의 로컬 인덱스가 모두 USABLE 인가 (0행이어야 한다)
SELECT index_name, status FROM user_ind_partitions
 WHERE partition_name = 'P202406' AND status <> 'USABLE';

-- T_ORD_X202406 에는 이제 바뀌기 전 6월이 들어 있다. 문제가 보이면 한 번 더 교환해 되돌리고,
-- 다음 날 DROP TABLE T_ORD_X202406 PURGE;

개선 후 실행계획 학습용 예시 · 교재 14화 규모

xplan

[배치 로그 · 교환으로 바꾼 뒤]
  10:00:01 ~ 10:03:06  CREATE TABLE T_ORD_X202406 AS SELECT ... @STLM   27,390행 · 이 3분 동안 화면은 지금의 6월을 본다
  10:03:07             건수 · 금액 확인                                 정산계 확정값과 같다
  10:03:08 ~ 10:03:09  인덱스 둘 생성                                   1.1초
  10:03:10             EXCHANGE PARTITION ... INCLUDING INDEXES         0.06초 · redo 약 0.03 MB
  (링크가 끊기면)       CTAS 만 실패한다 · P202406 은 손대지 않은 채 남는다

[「월 매출」 화면 · 10:01 (새 6월을 받는 중)]
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |      1 |        |      1 | 00:00:00.01 |     161 |        |       |
|   1 |  SORT AGGREGATE              |               |      1 |      1 |      1 | 00:00:00.01 |     161 |        |       |
|*  2 |   FILTER                     |               |      1 |        |  27390 | 00:00:00.01 |     161 |        |       |
|   3 |    PARTITION RANGE ITERATOR  |               |      1 |  27390 |  27390 | 00:00:00.01 |     161 |    KEY |   KEY |
|*  4 |     TABLE ACCESS FULL        | T_ORD_BIG_P14 |      1 |  27390 |  27390 | 00:00:00.01 |     161 |    KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------------

  (교환 뒤에도 161블록 · CTAS 는 첫 블록부터 채워 빈 블록을 남기지 않는다)

모범 답안 서술

10:00:01의 TRUNCATE PARTITION은 DDL이라 문장 앞뒤에서 커밋이 일어나고, 칸이 비었다는 사실이 그 순간 모든 세션에 보인다. 뒤따르는 INSERT는 3분 넘게 DB 링크로 행을 받는데, 넣은 행은 커밋하기 전까지 다른 세션에 보이지 않는다(읽기 일관성). 그래서 「월 매출」 화면은 그 3분 동안 6월 칸을 열어도 0행을 읽었다(PARTITION RANGE ITERATOR, A-Rows 0, Buffers 3). 7월 3일에는 10:02에 링크가 끊겨 INSERT가 ORA-02068로 실패했고, INSERT만 롤백되었다. 이미 커밋된 TRUNCATE는 되돌릴 수 없으므로 6월 칸은 다시 돌린 11:20까지 빈 채로 남았다. 처방은 칸을 먼저 비우지 않는 것이다. 새 6월을 칸과 상관없는 테이블 T_ORD_X202406에 CTAS로 모두 받고, 건수와 금액을 정산계와 맞춘 뒤, 칸의 로컬 인덱스 둘과 같은 모양의 인덱스를 만든다. 그다음 EXCHANGE PARTITION P202406 WITH TABLE T_ORD_X202406 INCLUDING INDEXES로 칸과 테이블을 맞바꾼다. 교환은 데이터를 옮기지 않고 사전에서 두 세그먼트의 주인만 바꾸므로 0.06초에 끝난다(redo 약 0.03MB). 받는 3분 동안 화면은 지금의 6월을 그대로 보고, 링크가 끊겨도 CTAS만 실패할 뿐 칸은 손대지 않은 채 남는다. 교환 뒤 T_ORD_X202406에는 바뀌기 전 6월이 들어 있어서, 문제가 보이면 한 번 더 교환해 되돌릴 수 있다. 그래서 바로 지우지 않고 다음 날 지운다. 대가와 확인할 것은 이렇다. 첫째, 교환용 테이블은 칸과 컬럼 구성(순서, 타입, 크기)이 같아야 한다. 정산계 테이블에서 CTAS로 받으면 타입이 달라질 수 있으므로, 교환이 거부되면 구조부터 맞춘다. 둘째, 인덱스도 모양이 같아야 INCLUDING INDEXES로 함께 바뀐다. 이 절을 빼면 그 칸의 로컬 인덱스가 UNUSABLE이 된다. 셋째, 기본값인 WITH VALIDATION은 교환할 때 테이블을 한 번 읽어 모든 행이 칸 범위 안에 있는지 확인한다(약 161블록). 이 크기에서는 싼 확인이라 켜 둔다. 넷째, 교환도 DDL이라 그 순간 칸에 잠금이 필요하다. 그 칸을 고치는 트랜잭션이 열려 있으면 교환이 기다리거나 실패하므로 정산 시각에 다른 쓰기가 없는지 확인한다. 한 달 치를 잠시 두 벌 들고 있는 공간도 대가지만 칸 하나(약 1.3MB)라 작다.

해설

WITHOUT VALIDATION: 검증을 끄면 교환이 조금 더 빨라지지만, 칸 범위 밖의 행이 섞여도 그대로 들어간다. 그런 행은 칸을 고르는 조회에서 보이지 않는다(PART-042). 칸이 커서 검증을 꺼야 한다면 교환 전에 범위 밖 행을 세는 쿼리를 따로 둔다.
칸을 비우고 다시 넣는 일을 가볍게 하는 방법(그 칸의 로컬 인덱스만 끄고 다시 짓기, 비운 칸에 APPEND할 때의 빈 블록)은 DML-042에서 다룬다. 그 방법도 실패하면 칸이 빈 채로 남는다는 점은 같다. 도는 동안이나 실패한 뒤에도 옛 데이터가 보여야 하면 교환을 쓴다.
GLOBAL 인덱스가 있는 테이블이면 교환에 UPDATE GLOBAL INDEXES를 붙인다. 빼면 GLOBAL 인덱스가 UNUSABLE이 되어 그 인덱스를 쓰던 조회가 에러 없이 느려진다(PART-017과 같은 일).
교환하면 칸의 통계도 교환용 테이블의 통계로 바뀐다. CTAS는 만들면서 통계를 모으므로 따로 할 일이 없지만, 일반 INSERT로 채운 교환용 테이블이라면 교환 전에 통계를 모은다.
같은 일을 TRUNCATE 없이 DELETE 뒤 INSERT로 한 트랜잭션에 묶어도 화면은 옛 6월을 보지만, 행마다 지우고 넣는 redo와 undo를 그대로 치른다. 교환은 '보이는 것'과 '비용'을 함께 해결한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 교재 14화 규모 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 교재 14화 규모 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황