PART-019

월 정리가 돈 날 아침부터 점주 앱의 기간 주문 조회가 몇 초씩 걸린다

기초DDL버거394 / 700

상황

버거 본사는 지난달 점주 앱에 「기간 주문 조회」를 열었다. 점주가 매장과 기간(보통 7일)을 고르면 주문 목록을 보여 준다. 오픈 때 조민우가 이 화면용 인덱스를 하나 더 만들었고, 지난달에는 한 번에 51블록을 읽었다. 이번 달 1일 03:00 월 정리 배치가 가장 오래된 칸을 들어낸 뒤부터 이 화면이 몇 초씩 걸린다. 월 정리 스크립트 맨 위에는 -- ORD_RANGE 인덱스는 전부 LOCAL이라 인덱스 작업 없음 (2013.03 오과장)이라는 주석이 있다.

테이블과 인덱스

ORD_RANGE 주문(파티션 복사본, 교재 14화): 1,000,000행 · PARTITION BY RANGE (ORD_YM) 월별 25칸(P201201 ~ P201312과 빈 P_DEFAULT (MAXVALUE)). 칸 하나를 FULL로 읽으면 약 1,980블록이다. 오늘 새벽 P201201을 들어내서 데이터가 있는 칸은 23개다.
ORD_YM VARCHAR2(6)은 적재할 때 SUBSTR(ORD_DT, 1, 6)으로 채운 파티션 키다. ORD_DT는 VARCHAR2(8) '20130605'이다.
LOCAL 인덱스 ORD_RANGE_PK (ORD_NO, ORD_YM) · ORD_RANGE_X01 (ORD_DT, ORD_HMS)
조민우가 지난달 만든 인덱스는 아래 SQL의 첫 문장이다. 매장 하나의 7일 주문은 약 48건이고, 행마다 다른 블록에 있다(ORD_RANDOM에서 적재).
SKIP_UNUSABLE_INDEXES는 기본값(TRUE)이다.

원본 SQL

sql

-- 지난달 조민우 : 점주 앱 기간 조회용
CREATE INDEX ORD_RANGE_X02 ON ORD_RANGE (SHOP_NO, ORD_DT);

-- 월 정리 (매월 1일 03:00)
ALTER TABLE ORD_RANGE DROP PARTITION P201201;

-- 점주 앱 「기간 주문 조회」
SELECT ORD_NO, ORD_DT, ORD_HMS
  FROM ORD_RANGE
 WHERE SHOP_NO = :shop
   AND ORD_DT BETWEEN :d1 AND :d2
 ORDER BY ORD_DT, ORD_HMS;

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

xplan

[오늘 09:00 · :shop = 'SH0137', :d1 = '20130603', :d2 = '20130609']
----------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |      1 |        |     48 | 00:00:00.42 |   45540 |        |       |
|   1 |  SORT ORDER BY         |           |      1 |     48 |     48 | 00:00:00.42 |   45540 |        |       |
|*  2 |   FILTER               |           |      1 |        |     48 | 00:00:00.42 |   45540 |        |       |
|   3 |    PARTITION RANGE ALL |           |      1 |     48 |     48 | 00:00:00.41 |   45540 |      1 |    24 |
|*  4 |     TABLE ACCESS FULL  | ORD_RANGE |     23 |     48 |     48 | 00:00:00.41 |   45540 |      1 |    24 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:D1<=:D2)
   4 - filter(("SHOP_NO"=:SHOP AND "ORD_DT">=:D1 AND "ORD_DT"<=:D2))

[USER_INDEXES · 09:05]
  INDEX_NAME      PARTITIONED  STATUS
  ORD_RANGE_PK    YES          N/A      (칸마다 USABLE)
  ORD_RANGE_X01   YES          N/A      (칸마다 USABLE)
  ORD_RANGE_X02   NO           UNUSABLE

문제

오늘 아침부터 이 화면이 45,540블록을 읽는 까닭을 인덱스 정의, 월 정리 스크립트, 인덱스 상태로 설명하시오.
다음 달에 같은 일이 되풀이되지 않게 인덱스와 화면 SQL을 고치시오.

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

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

모범답안

모범 답안 SQL

sql

-- X02 는 LOCAL 을 빼고 만들어 파티션하지 않은(GLOBAL) 인덱스였다 → 칸 DROP 이 UNUSABLE 로 만들었다
DROP INDEX ORD_RANGE_X02;
CREATE INDEX ORD_RANGE_X02 ON ORD_RANGE (SHOP_NO, ORD_DT) LOCAL;

-- 화면 : 날짜에서 월을 만들어 파티션 키 조건을 같이 준다 (가공은 값 쪽에서)
SELECT ORD_NO, ORD_DT, ORD_HMS
  FROM ORD_RANGE
 WHERE SHOP_NO = :shop
   AND ORD_DT BETWEEN :d1 AND :d2
   AND ORD_YM BETWEEN SUBSTR(:d1, 1, 6) AND SUBSTR(:d2, 1, 6)
 ORDER BY ORD_DT, ORD_HMS;

-- 월 정리 스크립트 첫 줄 : 글로벌 인덱스가 하나라도 있으면 멈추고 알린다
SELECT index_name FROM user_indexes
 WHERE table_name = 'ORD_RANGE' AND partitioned = 'NO';

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

xplan

-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |               |      1 |        |     48 | 00:00:00.01 |      51 |        |       |
|   1 |  SORT ORDER BY                                |               |      1 |     48 |     48 | 00:00:00.01 |      51 |        |       |
|*  2 |   FILTER                                      |               |      1 |        |     48 | 00:00:00.01 |      51 |        |       |
|   3 |    PARTITION RANGE ITERATOR                   |               |      1 |     48 |     48 | 00:00:00.01 |      51 |    KEY |   KEY |
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | ORD_RANGE     |      1 |     48 |     48 | 00:00:00.01 |      51 |    KEY |   KEY |
|*  5 |      INDEX RANGE SCAN                         | ORD_RANGE_X02 |      1 |     48 |     48 | 00:00:00.01 |       3 |    KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter((:D1<=:D2 AND SUBSTR(:D1,1,6)<=SUBSTR(:D2,1,6)))
   4 - filter(("ORD_YM">=SUBSTR(:D1,1,6) AND "ORD_YM"<=SUBSTR(:D2,1,6)))
   5 - access("SHOP_NO"=:SHOP AND "ORD_DT">=:D1 AND "ORD_DT"<=:D2)

  (실행 때 고른 칸: P201306 하나 · 기간이 두 달에 걸치면 두 칸)

모범 답안 서술

USER_INDEXES를 보면 ORD_RANGE_X02만 PARTITIONED = NO다. 파티션 테이블에 CREATE INDEX를 하면서 LOCAL을 빼면 칸으로 나뉘지 않은 GLOBAL 인덱스 하나가 생긴다. 지난달까지는 이 인덱스로 7일 치 48건을 51블록에 찾았다. 월 정리 스크립트는 "인덱스가 전부 LOCAL"이라는 전제로 UPDATE GLOBAL INDEXES 없이 DROP PARTITION을 했다. 글로벌 인덱스에는 지운 칸 4만 행을 가리키던 키가 남아 있으므로 오라클은 이 인덱스를 UNUSABLE로 표시했다. SKIP_UNUSABLE_INDEXES = TRUE라서 옵티마이저는 에러 없이 깨진 인덱스를 건너뛰고, 파티션 키 조건도 없으니 데이터가 있는 23칸을 FULL로 읽었다(PARTITION RANGE ALL, Starts 23, 51 → 45,540블록). 처방은 인덱스를 LOCAL로 다시 만드는 것이다. LOCAL 인덱스는 칸과 함께 생기고 함께 사라지므로 월 정리 DDL이 건드리지 않는다. 다만 로컬 인덱스는 칸마다 따로 있어서, 조건에 파티션 키가 없으면 23칸마다 한 번씩 뒤진다(약 94블록). 그래서 화면 SQL에 ORD_YM BETWEEN SUBSTR(:d1, 1, 6) AND SUBSTR(:d2, 1, 6)을 더해 기간에 걸친 칸만 열게 한다. 7일이면 칸 하나 또는 둘이고 51블록으로 돌아온다. 글로벌 인덱스를 그대로 두는 방법도 있다. 그때는 월 정리를 DROP PARTITION … UPDATE GLOBAL INDEXES로 바꿔야 한다. 이 화면은 언제나 기간을 넘기므로 LOCAL로 충분하고, 월 정리 스크립트의 전제도 지킬 수 있다. 전제가 다시 깨지지 않도록 스크립트 첫 줄에서 PARTITIONED = 'NO'인 인덱스가 있는지 확인한다.

해설

기본값이 GLOBAL이다. 파티션 테이블에 만드는 인덱스는 LOCAL을 적어야 칸으로 나뉜다. 이관 스크립트나 신규 인덱스 요청서에 LOCAL / GLOBAL을 적는 칸을 따로 두면 이런 실수가 줄어든다.
글로벌 인덱스가 깨져도 조회는 에러가 나지 않는다. 유니크가 아닌 인덱스라 INSERT도 멈추지 않아서, 화면이 느려졌다는 전화가 처음 신호가 된다. 칸 DDL 뒤에는 인덱스 상태를 확인하는 단계를 둔다.
ORD_YM 조건을 더할 때 가공은 바인드 쪽(SUBSTR(:d1, 1, 6))에서 한다. SUBSTR(ORD_DT, 1, 6) = …처럼 컬럼을 가공하면 칸을 고르지 못한다.
글로벌로 두면 조건에 월이 없어도 인덱스를 한 번에 찾는다는 장점이 있다(51블록). 화면이 월을 넘길 수 없는 조회라면 글로벌 인덱스와 UPDATE GLOBAL INDEXES가 맞는 답이 된다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

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

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황