PART-017

새벽 재적재 배치가 이번 달 칸을 비운 뒤 INSERT에서 멈췄다

기초DDL유통392 / 700

상황

유통 본사의 「당월 재적재」 배치는 매일 02:00에 돈다. 매장 POS의 정정분(반품 취소, 시각 보정)을 반영하려고 이번 달 칸을 비우고 스테이징 테이블에서 다시 넣는다. 지난주까지 잘 돌던 배치가 오늘 새벽 INSERT에서 멈췄고, 아침 9시부터 매장에서 「반품 영수증 조회」가 느리다는 전화가 왔다. 배치 담당 오과장: "어제 DBA가 영수증 조회 때문에 PK를 바꿨다고 했다."

테이블과 인덱스

CF10_P 판매(파티션 복사본, 문제 설정): CF10 500,000행을 PARTITION BY RANGE (SLE_DT) 월별 24칸(P202201 ~ P202312)과 빈 PMAX로 나눠 CTAS로 지었다. 칸 하나는 약 20,800행이고 FULL로 약 191블록이다. 오늘은 2023년 12월 14일이고, P202312(24번째 칸)에는 13일 치 약 9,100행이 있었다.
어제 DBA가 PK를 CF10_P_PK (SLE_NO) GLOBAL 유니크 인덱스(파티션하지 않은 인덱스)로 바꿨다. 판매번호만으로 한 번에 찾게 하려는 것이었다(높이 3, 조회 4블록).
LOCAL 인덱스 CF10_P_X01 (SHP_NO, SLE_DT): 매장 일 매출 화면용
CF10_STG 판매 스테이징: POS가 보낸 이번 달 판매 전체(정정 반영)
다른 테이블이 CF10_P를 FK로 참조하지 않는다. SKIP_UNUSABLE_INDEXES는 기본값(TRUE)이다.

원본 SQL

sql

-- 당월 재적재 (매일 02:00)
ALTER TABLE CF10_P TRUNCATE PARTITION P202312;

INSERT INTO CF10_P (SLE_NO, SLE_DT, SLE_HMS, SLE_DOW, SHP_NO)
SELECT SLE_NO, SLE_DT, SLE_HMS, SLE_DOW, SHP_NO
  FROM CF10_STG
 WHERE SLE_DT BETWEEN '20231201' AND '20231231';
COMMIT;

실행계획 학습용 예시

xplan

[배치 로그 · 2023-12-14]
  02:00:01  ALTER TABLE CF10_P TRUNCATE PARTITION P202312        완료 (0.2초)
  02:00:02  INSERT INTO CF10_P ... SELECT ... FROM CF10_STG
            ORA-01502: index 'PUP.CF10_P_PK' or partition of such index is in unusable state

[인덱스 상태 · 09:10]
  USER_INDEXES          CF10_P_PK    PARTITIONED NO    STATUS UNUSABLE
  USER_IND_PARTITIONS   CF10_P_X01   24칸 모두 USABLE (P202312 칸은 비어 있다)

[반품 영수증 조회 · 09:12 · :sle_no = '20231107-0215']
-----------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name   | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |      1 |        |      1 | 00:00:00.03 |    4396 |        |       |
|   1 |  PARTITION RANGE ALL |        |      1 |      1 |      1 | 00:00:00.03 |    4396 |      1 |    25 |
|*  2 |   TABLE ACCESS FULL  | CF10_P |     24 |      1 |      1 | 00:00:00.03 |    4396 |      1 |    25 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("SLE_NO"=:SLE_NO)

문제

INSERT가 ORA-01502로 멈춘 까닭과, 같은 인덱스 문제인데 영수증 조회는 에러 없이 느려지기만 한 까닭을 설명하시오.
지금 서비스를 되살리는 SQL과, 배치가 다시는 인덱스를 깨뜨리지 않도록 고친 재적재 SQL을 작성하시오.

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

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

모범답안

모범 답안 SQL

sql

-- (1) 지금 : 깨진 글로벌 PK부터 다시 만든다 (그동안 영수증 조회는 FULL, 적재는 멈춰 있다)
ALTER INDEX CF10_P_PK REBUILD;

-- (2) 배치 : 이번 달 칸을 비우면서 글로벌 인덱스를 같이 고친다
ALTER TABLE CF10_P TRUNCATE PARTITION P202312 UPDATE GLOBAL INDEXES;

INSERT INTO CF10_P (SLE_NO, SLE_DT, SLE_HMS, SLE_DOW, SHP_NO)
SELECT SLE_NO, SLE_DT, SLE_HMS, SLE_DOW, SHP_NO
  FROM CF10_STG
 WHERE SLE_DT BETWEEN '20231201' AND '20231231';
COMMIT;

-- (3) 배치 끝 : 인덱스 상태를 확인하고 하나라도 VALID 가 아니면 알린다
SELECT index_name, status
  FROM user_indexes
 WHERE table_name = 'CF10_P';

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

xplan

[재적재 · TRUNCATE PARTITION … UPDATE GLOBAL INDEXES]
  TRUNCATE        0.2초 (23ai 는 지운 칸의 키를 고아 항목으로 남겼다가 나중에 정리한다)
  INSERT          9,1xx행 · 오류 없음
  CF10_P_PK       VALID

[반품 영수증 조회 · 같은 번호]
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |      1 |        |      1 | 00:00:00.01 |       4 |        |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID | CF10_P    |      1 |      1 |      1 | 00:00:00.01 |       4 |  ROWID | ROWID |
|*  2 |   INDEX UNIQUE SCAN                 | CF10_P_PK |      1 |      1 |      1 | 00:00:00.01 |       3 |        |       |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SLE_NO"=:SLE_NO)

모범 답안 서술

CF10_P_PK는 어제부터 파티션하지 않은 GLOBAL 인덱스다. 인덱스 하나에 24칸 전체의 키가 들어 있고, 그중에는 P202312 칸 9,100행을 가리키는 키도 있다. TRUNCATE PARTITION은 그 칸만 비우는 DDL이라서, 글로벌 인덱스 안에 남은 키가 가리킬 행이 사라진다. 그래서 UPDATE GLOBAL INDEXES 없이 칸을 비우면 오라클은 글로벌 인덱스 전체를 UNUSABLE로 표시한다. LOCAL 인덱스 CF10_P_X01은 칸마다 따로 있어서 그 칸의 인덱스도 같이 비워질 뿐이고, 다른 23칸은 멀쩡하다. 문제는 글로벌 쪽이다. INSERT가 멈춘 까닭은 깨진 인덱스가 유니크라는 데 있다. 조회는 SKIP_UNUSABLE_INDEXES = TRUE라서 옵티마이저가 깨진 인덱스를 건너뛰고 다른 길(FULL)을 고른다. 그러나 INSERT는 유니크 인덱스를 건너뛸 수 없다. 건너뛰면 중복을 막을 장치가 없어지기 때문이다. 그래서 ORA-01502로 멈췄다. 반대로 영수증 조회는 에러 없이 24칸을 FULL로 읽어 4 → 4,396블록이 되었고, 하루 3,000번이면 약 1,300만 블록이다. 지금은 ALTER INDEX CF10_P_PK REBUILD로 인덱스를 되살리고 재적재를 다시 돌린다. 배치는 ALTER TABLE CF10_P TRUNCATE PARTITION P202312 UPDATE GLOBAL INDEXES로 바꾼다. 이 절을 붙이면 DDL이 글로벌 인덱스를 같이 고쳐서 인덱스가 VALID로 남는다(23ai는 지운 키를 고아 항목으로 두었다가 나중에 정리하므로 DDL 자체는 싸다). 배치 끝에는 USER_INDEXES의 상태를 확인하는 단계를 넣는다.

해설

원인은 어제의 변경이다. 배치는 그대로였고, PK가 LOCAL에서 GLOBAL로 바뀌면서 칸 DDL의 뜻이 달라졌다. 인덱스를 GLOBAL로 바꿀 때는 그 테이블에 칸 DDL(TRUNCATE · DROP · SPLIT · MOVE)을 하는 배치를 모두 찾아 함께 고친다.
다른 답: 판매번호 앞 8자리가 판매일자이므로 PK를 LOCAL (SLE_NO, SLE_DT)로 되돌리고 영수증 조회에 SLE_DT = SUBSTR(:sle_no, 1, 8)을 더하면 글로벌 인덱스 자체가 필요 없다(3블록). 칸 DDL이 인덱스를 깨뜨릴 일도 없어진다.
UPDATE GLOBAL INDEXES의 비용은 버전마다 다르다. 예전 버전은 DDL이 그 칸의 키를 하나하나 지워 redo가 커지고 DDL이 길어진다. 다른 버전이면 다시 재 본다.
TRUNCATE도 DDL이라 롤백이 없다. 재적재 INSERT가 실패하면 이번 달 칸이 빈 채로 남으므로, 재적재 뒤 건수를 스테이징과 맞춰 보는 단계도 둔다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황