PART-022

오래된 칸을 압축한 다음 날부터 2022년 주문 이력 조회만 느려졌다

중급DDL한빛유통397 / 700

상황

저장 공간을 아끼려고 오과장이 2022년 칸 12개를 기본 압축으로 다시 쌓았다. 이 칸들은 더 이상 바뀌지 않는다. "월 정리 때 배운 대로 글로벌 인덱스도 챙겼다." 다음 날 고객센터에서 「연도별 주문 이력」(고객 한 명의 한 해 주문)을 2022년으로 고르면 느리다는 말이 나왔다. 2023년과 2024년은 그대로 빠르다. 에러는 없다.

테이블과 인덱스

T_ORD_BIG_P14 주문(파티션 복사본, 교재 14화): 1,000,000행 · PARTITION BY RANGE (ORD_DT) 월별 36칸과 빈 PMAX, 모두 37칸. P202201 ~ P202212가 1 ~ 12번째 칸이다. 칸 하나는 압축 전 FULL로 약 161블록, 압축 뒤 약 105블록이다.
LOCAL 인덱스 IX_P14_CUS_DT (CUS_ID, ORD_DT): 「연도별 주문 이력」용, 칸마다 높이 2
GLOBAL 유니크 인덱스 IX_P14_SEQ (ORD_SEQ): 주문번호 조회용
고객 한 명의 한 해 주문은 약 167건이고 행마다 다른 블록에 있다. 2022년 칸에는 DML이 없다. SKIP_UNUSABLE_INDEXES는 기본값(TRUE)이다.

원본 SQL

sql

-- 오과장의 압축 스크립트 (P202201 ~ P202212, 칸마다 한 줄)
ALTER TABLE T_ORD_BIG_P14 MOVE PARTITION P202201 COMPRESS UPDATE GLOBAL INDEXES;
ALTER TABLE T_ORD_BIG_P14 MOVE PARTITION P202202 COMPRESS UPDATE GLOBAL INDEXES;
-- … P202212 까지

-- 「연도별 주문 이력」 (2022년)
SELECT ORD_SEQ, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG_P14
 WHERE CUS_ID = :v_cus
   AND ORD_DT >= DATE '2022-01-01'
   AND ORD_DT <  DATE '2023-01-01'
 ORDER BY ORD_DT;

실행계획 학습용 예시 · 압축 뒤 칸 크기는 어림값

xplan

[압축 전 · 같은 고객 · 2022년]
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |               |      1 |        |    167 | 00:00:00.01 |     193 |        |       |
|   1 |  SORT ORDER BY                               |               |      1 |    167 |    167 | 00:00:00.01 |     193 |        |       |
|   2 |   PARTITION RANGE ITERATOR                   |               |      1 |    167 |    167 | 00:00:00.01 |     193 |      1 |    12 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | T_ORD_BIG_P14 |     12 |    167 |    167 | 00:00:00.01 |     193 |      1 |    12 |
|*  4 |     INDEX RANGE SCAN                         | IX_P14_CUS_DT |     12 |    167 |    167 | 00:00:00.01 |      26 |      1 |    12 |
------------------------------------------------------------------------------------------------------------------------------------------

[압축 다음 날]
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |      1 |        |    167 | 00:00:00.03 |    1263 |        |       |
|   1 |  SORT ORDER BY             |               |      1 |    167 |    167 | 00:00:00.03 |    1263 |        |       |
|   2 |   PARTITION RANGE ITERATOR |               |      1 |    167 |    167 | 00:00:00.03 |    1263 |      1 |    12 |
|*  3 |    TABLE ACCESS FULL       | T_ORD_BIG_P14 |     12 |    167 |    167 | 00:00:00.03 |    1263 |      1 |    12 |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("CUS_ID"=:V_CUS)

[USER_IND_PARTITIONS · IX_P14_CUS_DT]
  P202201 ~ P202212    UNUSABLE
  P202301 ~ P202412    USABLE
[USER_INDEXES · IX_P14_SEQ]
  VALID

문제

2022년만 느려진 까닭을 두 실행계획과 인덱스 상태로 설명하시오. UPDATE GLOBAL INDEXES를 붙였는데도 왜 이렇게 되었는지 쓰시오.
깨진 인덱스를 되살리는 SQL과, 남은 칸을 압축할 때 쓸 SQL을 작성하시오.

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

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

모범답안

모범 답안 SQL

sql

-- (1) 깨진 로컬 인덱스 칸만 다시 만든다 (12칸 · 칸마다 따로 · 다른 칸과 글로벌 인덱스는 건드리지 않는다)
BEGIN
  FOR p IN (SELECT partition_name
              FROM user_ind_partitions
             WHERE index_name = 'IX_P14_CUS_DT'
               AND status = 'UNUSABLE') LOOP
    EXECUTE IMMEDIATE 'ALTER INDEX IX_P14_CUS_DT REBUILD PARTITION ' || p.partition_name;
  END LOOP;
END;
/

-- (2) 남은 칸 : 로컬까지 같이 고친다 (GLOBAL 이 아니라 INDEXES)
ALTER TABLE T_ORD_BIG_P14 MOVE PARTITION P202301 COMPRESS UPDATE INDEXES;
--   DML 이 들어오는 칸이면 ONLINE 을 더한다 : … MOVE PARTITION P202412 COMPRESS ONLINE UPDATE INDEXES;

-- (3) 압축 스크립트 끝 : 깨진 인덱스가 남았는지 확인한다
SELECT index_name, partition_name, status
  FROM user_ind_partitions
 WHERE status <> 'USABLE';

개선 후 실행계획 학습용 예시 · 압축 뒤 칸 크기는 어림값

xplan

[로컬 인덱스 칸 12개 REBUILD 뒤 · 같은 조회]
------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |               |      1 |        |    167 | 00:00:00.01 |     191 |        |       |
|   1 |  SORT ORDER BY                               |               |      1 |    167 |    167 | 00:00:00.01 |     191 |        |       |
|   2 |   PARTITION RANGE ITERATOR                   |               |      1 |    167 |    167 | 00:00:00.01 |     191 |      1 |    12 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | T_ORD_BIG_P14 |     12 |    167 |    167 | 00:00:00.01 |     191 |      1 |    12 |
|*  4 |     INDEX RANGE SCAN                         | IX_P14_CUS_DT |     12 |    167 |    167 | 00:00:00.01 |      24 |      1 |    12 |
------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("CUS_ID"=:V_CUS AND "ORD_DT">=TO_DATE(' 2022-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "ORD_DT"<TO_DATE(' 2023-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

[P202301 · MOVE … COMPRESS UPDATE INDEXES 뒤]
  IX_P14_CUS_DT P202301   USABLE
  IX_P14_SEQ              VALID

모범 답안 서술

MOVE PARTITION은 칸의 행을 압축해 새 세그먼트로 옮겨 쓴다. 행이 옮겨지면 ROWID가 바뀌므로, 그 칸을 가리키던 인덱스 키는 모두 옛 주소를 들고 있게 된다. UPDATE GLOBAL INDEXES는 이름 그대로 글로벌 인덱스만 고친다. 그래서 글로벌 인덱스 IX_P14_SEQ는 VALID로 남았지만, 로컬 인덱스 IX_P14_CUS_DT의 2022년 칸 12개는 UNUSABLE이 되었다. 2023년과 2024년 칸은 옮기지 않았으므로 멀쩡하다. 옵티마이저는 SKIP_UNUSABLE_INDEXES = TRUE라서 에러 없이 깨진 칸의 인덱스를 건너뛰고, 2022년 12칸을 FULL로 읽었다(Starts 12, 칸마다 약 105블록). 그래서 같은 조회가 193 → 1,263블록이 되었다. 2022년을 고를 때만 느린 것은 깨진 칸이 그 해에만 있기 때문이다. 되살리기는 깨진 칸만 ALTER INDEX IX_P14_CUS_DT REBUILD PARTITION …으로 다시 만드는 것이다. 로컬 인덱스는 칸마다 따로 있으므로 칸 단위로 다시 만들 수 있고, 다른 칸과 글로벌 인덱스는 건드리지 않는다. 다시 만든 뒤에는 191블록으로 돌아온다(압축으로 테이블 블록이 조금 줄었다). 남은 칸은 MOVE PARTITION … COMPRESS UPDATE INDEXES로 압축한다. UPDATE INDEXES는 글로벌과 로컬 인덱스를 모두 DDL 안에서 고친다. 대가는 MOVE가 더 오래 걸리고 인덱스 키를 다시 적는 만큼 redo가 늘어나는 것이다. DML이 들어오는 칸이면 ONLINE을 더해 그동안의 DML을 막지 않게 한다. 압축 스크립트 끝에는 USER_IND_PARTITIONS에서 USABLE이 아닌 칸이 없는지 확인한다.

해설

DROP과 MOVE는 다르다. DROP PARTITION은 칸을 없애므로 로컬 인덱스의 그 칸도 같이 사라져 고칠 것이 없고, 글로벌 인덱스만 챙기면 된다. MOVE · SPLIT · MERGE처럼 행을 옮기는 칸 DDL은 로컬 인덱스의 칸까지 깨뜨린다. 그래서 UPDATE INDEXES를 쓴다.
다른 답: 압축은 그대로 두고 깨진 칸만 REBUILD PARTITION하는 것도 맞다. 칸마다 MOVE와 REBUILD 두 번을 하는 대신 한 번에 끝내려면 UPDATE INDEXES가 낫다.
깨진 로컬 인덱스 칸이 있으면 그 칸만 FULL로 읽고 나머지 칸은 인덱스로 읽는 계획(테이블 확장)이 나오기도 한다. 그래서 "어떤 기간만 느리다"는 증상이 칸 단위 인덱스 문제의 신호가 된다.
압축 칸에 UPDATE가 들어오면 행이 커져 옮겨 다니게 된다(로우 마이그레이션). 더 바뀌지 않는 칸만 압축한다(교재 05화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 압축 뒤 칸 크기는 어림값 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 압축 뒤 칸 크기는 어림값 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황