월요일 아침부터 정산 기간 조회가 91블록에서 5,822블록이 됐는데 에러는 하나도 없다

중급PLSQL한빛유통464 / 700

상황

야간 체인 ① 재적재는 14화 이후 TRUNCATE → 인덱스 UNUSABLE → APPEND → REBUILD 순서로 돈다. 월요일 09:20, 조민우: "정산 기간 조회가 오늘 아침부터 느려요. 0.02초 걸리던 게 0.3초예요." 같은 시간 DB 서버 CPU는 평소의 두 배였다. 이 조회는 정산 화면에서 시간당 약 1,800번 호출된다. 체인 로그를 보니 REBUILD 단계 하나에 경고가 남아 있었다. 체인은 단계가 실패해도 경고로 적고 다음 단계로 넘어가게 되어 있다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 2024년 6월 27,390행 · 낮에는 읽기만 한다.
인덱스 IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT)(Q1 정산 기간 조회, 리프 3,206블록) · IX_ORD_BIG_DT_SEQ (ORD_DT, ORD_SEQ)(Q4 정산 목록 페이징, 리프 3,344블록)
정산팀의 월마감 집계(별도 스케줄, 일요일 22:00 ~ 22:30)가 같은 시각에 TEMP를 크게 쓴다. TEMP는 2GB다.
인스턴스 파라미터 skip_unusable_indexes는 기본값 TRUE다.

원본 SQL

sql

-- 일요일 22:00 · 체인 ① (단계마다 따로 돈다 · 실패하면 경고를 적고 다음 단계로)
-- [1-1]
TRUNCATE TABLE T_ORD_BIG;
-- [1-2]
ALTER INDEX IX_ORD_BIG_DT_AMT UNUSABLE;
ALTER INDEX IX_ORD_BIG_DT_SEQ UNUSABLE;
-- [1-3]
INSERT /*+ APPEND */ INTO T_ORD_BIG (ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT)
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT FROM STG_ORD_BIG;
COMMIT;
-- [1-4]
ALTER INDEX IX_ORD_BIG_DT_AMT REBUILD;
-- [1-5]
ALTER INDEX IX_ORD_BIG_DT_SEQ REBUILD;
-- [1-6]
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_ORD_BIG')

실행계획 학습용 예시 · 숫자는 교재 02화와 14화 규모

xplan

[체인 로그 — 일요일]
  22:00:04  [1-1] TRUNCATE                       완료
  22:00:04  [1-2] UNUSABLE × 2                   완료
  22:00:05  [1-3] INSERT APPEND 1,000,000행       완료
  22:00:05  [1-4] REBUILD IX_ORD_BIG_DT_AMT       경고  ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
  22:00:07  [1-5] REBUILD IX_ORD_BIG_DT_SEQ       완료
  22:00:08  [1-6] 통계 수집                       완료
  …  체인 종료 03:41 · 상태 「경고 1」

[월요일 09:25 · USER_INDEXES]
  INDEX_NAME           STATUS     LEAF_BLOCKS
  IX_ORD_BIG_DT_AMT    UNUSABLE
  IX_ORD_BIG_DT_SEQ    VALID      3344

[월요일 09:25 · Q1 정산 기간 조회]
SELECT SUM(ORD_AMT) FROM T_ORD_BIG WHERE ORD_DT >= :B1 AND ORD_DT < :B2     -- 2024-06-01 ~ 2024-07-01
-------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |      1 |00:00:00.29 |    5822 |
|   1 |  SORT AGGREGATE    |           |      1 |      1 |      1 |00:00:00.29 |    5822 |
|*  2 |   TABLE ACCESS FULL| T_ORD_BIG |      1 |  27390 |  27390 |00:00:00.28 |    5822 |
-------------------------------------------------------------------------------------------
   2 - filter(("ORD_DT">=:B1 AND "ORD_DT"<:B2))

  (지난 금요일 같은 SQL: INDEX RANGE SCAN IX_ORD_BIG_DT_AMT · Buffers 91)

문제

Q1이 에러 없이 FULL로 바뀐 이유를 위 로그와 딕셔너리로 설명하고, CPU가 두 배가 된 것을 숫자로 설명하시오.
지금 바로 할 조치와, 같은 사고가 다시 나지 않도록 체인 ①을 고치는 방법을 SQL로 작성하시오.
REBUILD가 실패한 원인에 대한 대책을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 지금 바로 — 꺼진 인덱스를 다시 짓는다 (낮에 T_ORD_BIG 에 쓰는 세션은 없다 · 약 2.4초)
ALTER INDEX IX_ORD_BIG_DT_AMT REBUILD;

-- ② 체인 ①의 마지막 단계 [1-7] — 꺼진 인덱스가 남았으면 다시 짓고, 그래도 남으면 체인을 실패로 멈춘다
DECLARE
  v_bad NUMBER;
BEGIN
  FOR i IN (SELECT index_name
              FROM user_indexes
             WHERE table_name = 'T_ORD_BIG'
               AND status = 'UNUSABLE') LOOP
    EXECUTE IMMEDIATE 'ALTER INDEX ' || i.index_name || ' REBUILD';
  END LOOP;

  SELECT COUNT(*) INTO v_bad
    FROM user_indexes
   WHERE table_name = 'T_ORD_BIG'
     AND status <> 'VALID';
  IF v_bad > 0 THEN
    RAISE_APPLICATION_ERROR(-20039, 'T_ORD_BIG 인덱스 ' || v_bad || '개가 꺼진 채 남았다');
  END IF;
END;
-- 체인 설정: [1-4] · [1-5] · [1-7] 은 '실패하면 경고' 가 아니라 '실패하면 중단 · 담당자 호출'
-- TEMP: 월마감 집계를 22:30 이후로 옮기거나, 체인 ① 동안 쓸 TEMP 를 확보한다

개선 후 실행계획 학습용 예시 · 숫자는 교재 02화와 14화 규모

xplan

[① 뒤 · Q1]
-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |      1 |        |      1 |00:00:00.01 |      91 |
|   1 |  SORT AGGREGATE    |                   |      1 |      1 |      1 |00:00:00.01 |      91 |
|*  2 |   INDEX RANGE SCAN | IX_ORD_BIG_DT_AMT |      1 |  27390 |  27390 |00:00:00.01 |      91 |
-------------------------------------------------------------------------------------------------
   2 - access("ORD_DT">=:B1 AND "ORD_DT"<:B2)

  REBUILD 한 번: 약 2.4 초 · TEMP 약 30 MB · 리프 3,206블록
  [1-7] 을 넣은 뒤 같은 TEMP 부족이 나면: 체인 「실패」 · 22:01 담당자 호출 · 월요일 아침 전에 다시 지음

모범 답안 서술

[1-4] REBUILD가 TEMP 부족(ORA-01652)으로 실패했고, 체인은 그것을 경고로 적고 넘어갔다. 그래서 IX_ORD_BIG_DT_AMT는 [1-2]에서 끈 UNUSABLE 상태 그대로 월요일 아침을 맞았다. 옵티마이저는 꺼진 인덱스를 에러 없이 건너뛴다(skip_unusable_indexes = TRUE). Q1은 쓸 수 있는 인덱스가 없으니 TABLE ACCESS FULL로 바뀌었고, 화면에는 에러가 하나도 없이 느려지기만 했다. CPU가 두 배가 된 것은 숫자로 설명된다. Q1 한 번에 읽는 블록이 91 → 5,822로 64배가 됐고, 시간당 약 1,800번 호출되므로 시간당 약 16만 블록이던 논리 읽기가 약 1,050만 블록이 됐다. 한 번의 0.3초는 견딜 만해도 호출 빈도가 곱해지면 서버 전체가 무거워진다. 지금 바로 ALTER INDEX IX_ORD_BIG_DT_AMT REBUILD로 다시 짓는다. 낮에 T_ORD_BIG에 쓰는 세션이 없으므로 일반 REBUILD로 충분하고 약 2.4초면 끝난다. Q1은 5,822 → 91블록으로 돌아간다. 재발을 막으려면 체인 ① 끝에 상태 확인 단계를 넣는다. USER_INDEXES에서 STATUS가 UNUSABLE인 인덱스를 찾아 다시 짓고, 그래도 VALID가 아닌 인덱스가 남으면 RAISE_APPLICATION_ERROR로 체인을 실패로 멈춘다. REBUILD 단계들도 '실패하면 경고'가 아니라 '실패하면 중단하고 담당자 호출'로 바꾼다. 끄고 켜기의 대가는 '꺼진 동안'만이 아니라 '꺼진 채 남을 수 있다'는 것이고, 그것을 막는 일은 적재 순서의 일부다. REBUILD가 실패한 원인은 정렬 공간이다. 100만 행의 키를 정렬하려면 TEMP가 약 30MB 필요한데, 같은 시각 월마감 집계가 TEMP를 크게 쓰고 있었다. 월마감 집계를 22:30 이후로 옮겨 두 작업이 겹치지 않게 하거나, 체인 ① 동안 쓸 TEMP를 확보한다.

해설

반대 설정도 있다. 세션에서 skip_unusable_indexes = FALSE로 두면 꺼진 인덱스를 쓰려던 조회가 ORA-01502로 바로 실패한다. 조용히 느려지는 대신 시끄럽게 실패하지만 화면이 에러를 내므로 운영에서 고르기 어렵다. 배치 쪽에서 상태를 확인해 막는 것이 먼저다.
UNUSABLE로 끈 인덱스는 정의가 딕셔너리에 남아 있어 STATUS로 찾을 수 있다. DROP 후 CREATE로 끄고 켰다면 실패한 인덱스는 아예 없어졌을 것이고, 무엇이 빠졌는지 배치가 알 방법이 없다.
낮에 다시 지어야 하는데 그 테이블에 DML이 있다면 REBUILD ONLINE을 쓴다. 이 테이블은 낮에 읽기만 한다.
경고를 남기고 넘어가는 체인은 '단계마다 시각을 남기는' 장점이 있지만, 넘어가도 되는 단계와 안 되는 단계를 구분해 두지 않으면 이런 사고가 난다. 인덱스 재생성, 제약 켜기, 건수 대조는 넘어가면 안 되는 단계다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 숫자는 교재 02화와 14화 규모 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황