점주 앱을 빠르게 하려고 인덱스를 고친 다음 날부터 본사 시간대 상황판이 수십 배 무거워졌다

중급DDL버거65 / 700

상황

버거원 점주 앱의 「오늘 시간대별 주문」(IDX-022의 SQL)은 가맹점 200곳이 하루 4만 번쯤 연다. 화면이 무겁다는 민원에 지난 주말 외주 DBA가 인덱스를 손봤다. 작업 메모는 이렇다. "인덱스 개수는 늘리지 않는다. ORD_X01 맨 앞에 SHOP_NO를 넣어 다시 만들었다. 점주 앱 4블록." 월요일부터 본사 상황판 「전 매장 시간대별 주문」과 운영팀의 「점심 피크 주문 목록」이 느려졌다. 박팀장은 인덱스 개수를 늘리지 말고 셋을 다 받치는 안을 가져오라고 했다.

테이블과 인덱스

ORD: 1,000,000행 · 22,422블록 · 주문 일시 순으로 쌓였다 · ORD_DT VARCHAR2(8) 2012-01-01 ~ 2013-12-31 · ORD_HMS VARCHAR2(6) · SHOP_NO VARCHAR2(10) 200곳
2013-03-15 주문 1,344건(매장마다 평균 약 7건) · 그중 12시대(12:00:00 ~ 12:59:59) 186건 · 하루치 주문은 테이블 약 30블록 안에 모여 있다
인덱스 ORD_PK (ORD_NO) · ORD_X01 (SHOP_NO, ORD_DT, ORD_HMS): 지난 주말 다시 만든 것 · BLEVEL 2 · 리프 약 4,050블록(리프 한 블록에 약 250항목) · 통계 최신
고치기 전 ORD_X01 (ORD_DT, ORD_HMS): 리프 3,774블록 · CF 22,223 · S2는 8블록, S3는 13블록이었다
POS가 주문마다 INSERT한다(하루 약 1,370건)

원본 SQL

sql

-- S1 점주 앱 「오늘 시간대별 주문」 (하루 약 4만 번)
SELECT SUBSTR(ORD_HMS, 1, 2) AS hh, COUNT(*) AS cnt
  FROM ORD
 WHERE ORD_DT  = :v_dt
   AND SHOP_NO = :v_shop
 GROUP BY SUBSTR(ORD_HMS, 1, 2)
 ORDER BY hh;

-- S2 본사 상황판 「전 매장 시간대별 주문」 (본사 PC 10대가 5분마다 · 하루 약 2,900번)
SELECT SUBSTR(ORD_HMS, 1, 2) AS hh, COUNT(*) AS cnt
  FROM ORD
 WHERE ORD_DT = :v_dt
 GROUP BY SUBSTR(ORD_HMS, 1, 2)
 ORDER BY hh;

-- S3 운영팀 「점심 피크 주문 목록」 (하루 수백 번)
SELECT ORD_NO, SHOP_NO, ORD_HMS
  FROM ORD
 WHERE ORD_DT = :v_dt
   AND ORD_HMS BETWEEN '120000' AND '125959';

-- 지난 주말 작업
DROP INDEX ORD_X01;
CREATE INDEX ORD_X01 ON ORD (SHOP_NO, ORD_DT, ORD_HMS);

실행계획 학습용 예시

xplan

[S2 · 2013-03-15]
-----------------------------------------------------------------------------------------
| Id  | Operation         | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |      1 |        |     14 |00:00:00.01 |     612 |
|   1 |  SORT GROUP BY    |         |      1 |     14 |     14 |00:00:00.01 |     612 |
|*  2 |   INDEX SKIP SCAN | ORD_X01 |      1 |   1370 |   1344 |00:00:00.01 |     612 |
-----------------------------------------------------------------------------------------
   2 - access("ORD_DT"=:V_DT)
       filter("ORD_DT"=:V_DT)

[S3 · 2013-03-15]
----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |      1 |        |    186 |00:00:00.01 |     618 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ORD     |      1 |    163 |    186 |00:00:00.01 |     618 |
|*  2 |   INDEX SKIP SCAN                   | ORD_X01 |      1 |    163 |    186 |00:00:00.01 |     612 |
----------------------------------------------------------------------------------------------------------
   2 - access("ORD_DT"=:V_DT AND "ORD_HMS">='120000' AND "ORD_HMS"<='125959')
       filter(("ORD_DT"=:V_DT AND "ORD_HMS">='120000' AND "ORD_HMS"<='125959'))

[S1 · SH0042]  INDEX RANGE SCAN ORD_X01 · access("SHOP_NO"=:V_SHOP AND "ORD_DT"=:V_DT) · Buffers 4

문제

S2와 S3이 고치기 전보다 수십 배 읽게 된 이유를 실행계획의 INDEX SKIP SCAN과 선두 컬럼 SHOP_NO의 종류 수로 설명하시오.
인덱스 개수를 늘리지 않고 S1 ~ S3을 모두 받치는 인덱스를 설계하시오. 컬럼 순서를 그렇게 정한 이유를 설명하시오.
새 인덱스에서 S1과 S3의 조건이 각각 어떻게 처리되는지와, 반영 절차를 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 세 SQL 이 모두 = 로 주는 ORD_DT 를 선두로 · S1 의 = 조건 SHOP_NO 를 둘째로 · 시각은 끝에
CREATE INDEX ORD_X02 ON ORD (ORD_DT, SHOP_NO, ORD_HMS) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- 내 세션에서 S1 ~ S3 을 잰다
ALTER INDEX ORD_X02 VISIBLE;
ALTER INDEX ORD_X01 INVISIBLE;      -- 일 마감 배치 같은 다른 SQL 이 견디는지 한동안 지켜본 뒤
DROP INDEX ORD_X01;
-- S1 ~ S3 은 고치지 않는다 · 인덱스 개수는 그대로 하나

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

xplan

[S2 · 고친 뒤]
-----------------------------------------------------------------------------------------
| Id  | Operation         | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |      1 |        |     14 |00:00:00.01 |       8 |
|   1 |  SORT GROUP BY    |         |      1 |     14 |     14 |00:00:00.01 |       8 |
|*  2 |   INDEX RANGE SCAN| ORD_X02 |      1 |   1370 |   1344 |00:00:00.01 |       8 |
-----------------------------------------------------------------------------------------
   2 - access("ORD_DT"=:V_DT)

[S3 · 고친 뒤]
----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |      1 |        |    186 |00:00:00.01 |      14 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ORD     |      1 |    163 |    186 |00:00:00.01 |      14 |
|*  2 |   INDEX RANGE SCAN                  | ORD_X02 |      1 |    163 |    186 |00:00:00.01 |       8 |
----------------------------------------------------------------------------------------------------------
   2 - access("ORD_DT"=:V_DT AND "ORD_HMS">='120000' AND "ORD_HMS"<='125959')
       filter(("ORD_HMS">='120000' AND "ORD_HMS"<='125959'))

[S1 · 고친 뒤]  INDEX RANGE SCAN ORD_X02 · access("ORD_DT"=:V_DT AND "SHOP_NO"=:V_SHOP) · Buffers 3

모범 답안 서술

S2와 S3의 조건에는 SHOP_NO가 없다. 새 ORD_X01은 SHOP_NO가 선두라 항목이 매장 순으로, 같은 매장 안에서 날짜 순으로 놓여 있다. 2013-03-15의 1,344건은 매장 200곳의 덩어리마다 7건 남짓씩 흩어져 있으므로, 옵티마이저는 매장 200곳마다 한 번씩 들어가 그날 범위를 찾는 SKIP SCAN을 골랐다(2번 줄, access와 filter에 같은 조건이 함께 적힌 모양). 한 번 들어갈 때마다 브랜치 블록과 리프 블록을 읽어 612블록이 된다. 고치기 전 (ORD_DT, ORD_HMS)로는 그날 항목이 리프 몇 블록에 모여 있어 S2는 8블록, S3는 13블록이면 끝났다. DBA는 S1만 재고, 다른 SQL이 선두 컬럼을 잃는다는 것을 보지 못했다. S1의 두 조건은 둘 다 등치 조건이다. 등치 조건 컬럼끼리는 순서를 바꿔도 찾아가는 범위가 같으므로, S1만 보면 (SHOP_NO, ORD_DT)든 (ORD_DT, SHOP_NO)든 상관없다. 그렇다면 세 SQL이 모두 등치로 주는 ORD_DT를 선두에 두고, S1의 SHOP_NO를 둘째에, 시각을 끝에 둔 (ORD_DT, SHOP_NO, ORD_HMS) 하나로 셋을 받칠 수 있다. S1은 두 컬럼이 모두 access라 그 매장의 그날 7건만 읽고 인덱스 안에서 끝난다(3블록). S2는 ORD_DT 하나로 그날 항목 1,344개(리프 6블록 남짓)를 읽어 센다(Buffers 612 → 8). S3는 가운데 SHOP_NO가 비어 ORD_HMS가 범위를 좁히지 못하고 그날 항목 안에서 거르는 filter가 되지만, 하루치가 리프 몇 블록뿐이라 테이블 6블록을 더해도 14블록이다(618 → 14). INSERT 쪽도 나아진다. SHOP_NO가 선두면 새 주문의 키가 매장 200곳의 덩어리마다 흩어져 들어가 리프가 반씩 쪼개지며 커진다. ORD_DT가 선두면 새 주문은 늘 오늘 날짜 끝부분의 리프 몇 블록에만 들어간다. 반영은 새 인덱스를 INVISIBLE로 만들어 내 세션에서 S1 ~ S3을 잰 뒤 VISIBLE로 바꾸고, ORD_X01을 INVISIBLE로 돌려 일 마감 배치 같은 다른 SQL이 견디는지 지켜본 다음 지운다. 인덱스 개수는 그대로 하나다.

해설

함정: "같은 컬럼 셋이면 순서는 상관없다"가 아니다. 순서가 자유로운 것은 등치 조건 컬럼끼리뿐이다. 선두에는 모든 SQL이 가진 컬럼을 둔다(교재 04화 §6의 기준 ②).
(ORD_DT, ORD_HMS, SHOP_NO)도 답이 된다. S3는 시각까지 access로 남고, S1은 그날 1,344항목 안에서 SHOP_NO를 거른다(약 8블록, 인덱스만). 하루 4만 번 도는 S1이 가장 잦으므로 SHOP_NO를 둘째로 두는 쪽을 골랐다.
점주 앱에 「석 달 일별 주문」처럼 매장 등치 조건과 기간 범위를 함께 쓰는 화면이 생기면, 그때는 SHOP_NO가 선두인 인덱스가 따로 필요하다(ACC-004, IDX-060). 그때 인덱스를 하나 더 둘지 저울질한다.
SKIP SCAN은 선두 값이 적을 때나 싸다. 매장이 다섯 곳인 유통이었다면 5번 들어가고 끝났을 것이다(IDX-066). 같은 실수라도 선두 컬럼의 종류 수에 따라 드러나는 크기가 다르다.
리프가 반씩 쪼개지며 커지는 정도는 교재 14화 §2.3의 실측(무작위 순서로 끼워 넣은 인덱스가 +65%)을 참고한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황