가맹점 포털 오픈 점검에서 점주 앱 SQL과 본사 순위 SQL이 모두 주문 테이블을 1,300블록 넘게 읽는다

실전SELECT버거148 / 700

상황

버거원이 가맹점 포털을 연다. 오픈 전 점검에서 SQL 넷이 걸렸다. 넷 다 운영 주문 ORD_RANDOM을 읽는데, 쓸 수 있는 인덱스가 날짜 인덱스 X01 하나뿐이다. 점주 앱(P1 ~ P3)은 매장 200곳이 수시로 열고, 본사 순위 화면(P4)은 10분마다 새로 고친다. 박팀장은 이렇게 주문했다. "인덱스는 최소로. 넷이 각각 무엇을 읽는지, 하루 일량으로 보면 무엇이 급한지, 만들면 POS 적재에 무엇이 붙는지까지 적어 와라."

테이블과 인덱스

ORD_RANDOM(운영본): 1,000,000행 · 22,414블록(블록당 약 45행) · 서버 교체 때 무작위 순서로 다시 적재했다 · 하루 약 1,370건 · 매장 200곳(매장 하나의 하루 주문은 평균 6.9건, 한 달 약 208건) · POS가 판매마다 INSERT하고, 매월 초 정정분 약 4만 건을 다시 넣는다
ORD_DT VARCHAR2(8) · ORD_HMS VARCHAR2(6) · SHOP_NO VARCHAR2(10) 'SH0001' · ORD_NO NUMBER(10)
인덱스 ORD_RANDOM_PK (ORD_NO) · ORD_RANDOM_X01 (ORD_DT, ORD_HMS): BLEVEL 2 · 리프 3,774블록 · CLUSTERING_FACTOR 999,958(교재 04화 실측) · 「일별 주문 조회」(ORD_DT = :d ORDER BY ORD_HMS, 첫 화면 30줄)도 X01을 쓴다
같은 조건을 FULL로 읽으면 22,234블록이다 · 인덱스 하나는 정정분 4만 건 적재에 redo 약 9.4MB를 더한다(교재 04화 실측 비례)
어림: 항목 하나는 '컬럼 값 + 컬럼마다 1바이트 + ROWID 7바이트 + 머리 약 4바이트' · 리프 한 블록에 약 7,200바이트 · SHOP_NO 6바이트, ORD_DT 8바이트, ORD_HMS 6바이트

원본 SQL

sql

-- P1 「시간대별 주문 수」 · 점주 앱 · 하루 약 8,000번
SELECT SUBSTR(ORD_HMS, 1, 2) AS hh, COUNT(*) AS cnt
  FROM ORD_RANDOM
 WHERE SHOP_NO = :v_shop AND ORD_DT = :v_dt
 GROUP BY SUBSTR(ORD_HMS, 1, 2);

-- P2 「매장 주문 목록」 · 점주 앱 · 하루 약 4,000번
SELECT ORD_NO, ORD_HMS
  FROM ORD_RANDOM
 WHERE SHOP_NO = :v_shop AND ORD_DT = :v_dt
 ORDER BY ORD_HMS;

-- P3 「월 주문 추이」 · 점주 앱 · 하루 약 2,000번 · 한 달
SELECT ORD_DT, COUNT(*) AS cnt
  FROM ORD_RANDOM
 WHERE SHOP_NO = :v_shop AND ORD_DT BETWEEN :v_from AND :v_to
 GROUP BY ORD_DT;

-- P4 「오늘 매장 순위」 · 본사 · 하루 약 150번
SELECT SHOP_NO, COUNT(*) AS cnt
  FROM ORD_RANDOM
 WHERE ORD_DT = :v_dt
 GROUP BY SHOP_NO;

실행계획 학습용 예시 · 인덱스 규모와 CF는 교재 04화 실측

xplan

[P3 · SH0042 · 2013년 11월]
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |      1 |        |     30 |00:00:00.06 |   41838 |
|   1 |  HASH GROUP BY                        |                |      1 |     20 |     30 |00:00:00.06 |   41838 |
|*  2 |   FILTER                              |                |      1 |        |    208 |00:00:00.06 |   41838 |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| ORD_RANDOM     |      1 |     20 |    208 |00:00:00.06 |   41838 |
|*  4 |     INDEX RANGE SCAN                  | ORD_RANDOM_X01 |      1 |   4065 |  41666 |00:00:00.01 |     160 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:V_FROM<=:V_TO)
   3 - filter("SHOP_NO"=:V_SHOP)
   4 - access("ORD_DT">=:V_FROM AND "ORD_DT"<=:V_TO)

[P1 · SH0042 · 2013-11-15]
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |      1 |        |      6 |00:00:00.01 |    1378 |
|   1 |  HASH GROUP BY                        |                |      1 |      7 |      6 |00:00:00.01 |    1378 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED | ORD_RANDOM     |      1 |      7 |      7 |00:00:00.01 |    1378 |
|*  3 |    INDEX RANGE SCAN                   | ORD_RANDOM_X01 |      1 |   1370 |   1371 |00:00:00.01 |       8 |
--------------------------------------------------------------------------------------------------------------------
   2 - filter("SHOP_NO"=:V_SHOP)
   3 - access("ORD_DT"=:V_DT)

[P2 · P4]  P1과 같은 모양 · Buffers 약 1,378 (P2는 SORT ORDER BY가 붙는다)

문제

지금 P1 ~ P4가 각각 몇 블록을 읽는지 실행계획과 CLUSTERING_FACTOR로 설명하시오. 하루 실행 횟수를 곱해 어느 SQL이 가장 급한지 쓰시오.
최소 인덱스 세트를 설계해 DDL을 작성하시오. 세트로 P1 ~ P4가 어떤 스캔으로 몇 블록을 읽을지 어림하고, 다른 후보 하나와 비교하시오.
고른 세트의 대가, X01을 쓰는 다른 SQL에 주는 영향, 검증 절차를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 점주 앱 P1 · P2 · P3 : 매장 = → 날짜(= 또는 범위) → 시각. P1 · P3 은 테이블에 가지 않고, P2 는 7행만 간다
CREATE INDEX ORD_RANDOM_X05 ON ORD_RANDOM (SHOP_NO, ORD_DT, ORD_HMS) INVISIBLE;

-- ② 본사 순위 P4 : X01 뒤에 SHOP_NO 를 붙여 바꿔 단다 — 인덱스 수는 그대로, 「일별 주문 조회」는 앞부분이 같아 그대로
CREATE INDEX ORD_RANDOM_X01N ON ORD_RANDOM (ORD_DT, ORD_HMS, SHOP_NO) INVISIBLE;

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- P1 ~ P4 와 「일별 주문 조회」를 세션에서 잰다
-- T2 승인 뒤 : X05 · X01N VISIBLE → 옛 X01 INVISIBLE → 한 주 관찰 → DROP INDEX ORD_RANDOM_X01
-- P1 ~ P4 의 SQL 은 그대로

개선 후 실행계획 학습용 예시 · 인덱스 규모와 CF는 교재 04화 실측

xplan

[세트 · 세션에서 잰 값]
        인덱스            스캔                                           Buffers   하루
  P1    ORD_RANDOM_X05    INDEX RANGE SCAN (테이블에 가지 않는다)              3     약 2만 4천
  P2    ORD_RANDOM_X05    INDEX RANGE SCAN + 테이블 7행 (정렬 없음)           10     약 4만
  P3    ORD_RANDOM_X05    INDEX RANGE SCAN (테이블에 가지 않는다)              4     약 8천
  P4    ORD_RANDOM_X01N   INDEX RANGE SCAN (테이블에 가지 않는다)              9     약 1,400

[P3 · SH0042 · 2013년 11월]
---------------------------------------------------------------------------------------------------
| Id  | Operation            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                |      1 |        |     30 |00:00:00.01 |       4 |
|   1 |  SORT GROUP BY NOSORT|                |      1 |     20 |     30 |00:00:00.01 |       4 |
|*  2 |   FILTER             |                |      1 |        |    208 |00:00:00.01 |       4 |
|*  3 |    INDEX RANGE SCAN  | ORD_RANDOM_X05 |      1 |     20 |    208 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------------
   2 - filter(:V_FROM<=:V_TO)
   3 - access("SHOP_NO"=:V_SHOP AND "ORD_DT">=:V_FROM AND "ORD_DT"<=:V_TO)

[크기]  ORD_RANDOM_X05 리프 약 4,740 · ORD_RANDOM_X01N 약 4,740 (옛 X01 3,774)
[「일별 주문 조회」 · X01N]  INDEX RANGE SCAN + 테이블 · 첫 화면 30줄 · 바꾸기 전과 같은 모양

모범 답안 서술

① 지금은 넷 다 X01 (ORD_DT, ORD_HMS)로 날짜 범위를 읽은 뒤 테이블에서 SHOP_NO를 확인한다. ORD_RANDOM은 무작위 순서로 쌓여 X01의 CLUSTERING_FACTOR가 999,958이므로, 인덱스에서 찾은 행 하나가 테이블 블록 하나다. 하루 치면 리프 약 6블록 + 테이블 1,370블록이라 P1, P2, P4가 각각 약 1,378블록을 읽고 매장 하나의 7행만 남긴다(P2는 정렬도 붙는다). P3은 한 달 41,666행을 테이블에서 하나씩 읽고 208행만 남긴다(41,838). ORD_DT가 문자라 옵티마이저가 한 달을 4,065행으로 낮게 보고 FULL(22,234)보다 싸다고 판단한 탓이다. 하루로 곱하면 P3이 41,838 × 2,000 ≈ 8,370만 블록으로 넷 합계(약 1억)의 대부분이고, 그다음이 P1(약 1,100만)이다. 가장 급한 것은 P3이다. ② 세트는 둘이다. X05 (SHOP_NO, ORD_DT, ORD_HMS)는 점주 앱 셋을 받는다. =인 매장을 앞에, 날짜(= 또는 범위)를 다음에, 시각을 뒤에 두었다. P1은 그 매장의 그날 7항목을 리프 한 블록에서 읽고 시각을 인덱스에서 바로 얻으므로 테이블에 가지 않는다(약 3). P2는 같은 7항목을 시각순으로 읽어 정렬 없이 ORD_NO만 테이블에서 7번 읽는다(약 10). P3은 그 매장의 한 달 208항목을 리프 1 ~ 2블록에서 세어 테이블에 가지 않는다(41,838 → 약 4). P4는 매장 조건이 없으므로 X01 뒤에 SHOP_NO를 붙인 (ORD_DT, ORD_HMS, SHOP_NO)로 바꿔 단다. 하루 1,370항목을 리프 7블록에서 세어 테이블에 가지 않는다(1,378 → 약 9). 세트로 하루 약 7만 블록이다. 다른 후보로 (ORD_DT, SHOP_NO, ORD_HMS) 하나만 새로 만드는 안이 있다. P1, P2, P4는 비슷하게 줄지만, P3은 날짜가 앞이라 한 달 치 41,666항목을 다 읽고 매장으로 걸러 약 200블록이다(하루 약 40만 블록). 인덱스 수는 두 안이 같으므로 P3이 4블록인 X05 쪽이 낫다. ③ 대가는 이렇다. X05는 항목 하나가 약 34바이트라 리프 약 4,740블록(약 37MB)이고, X01은 SHOP_NO가 붙어 3,774 → 약 4,740블록으로 커진다. PK를 빼면 인덱스가 하나에서 둘이 되므로 POS INSERT마다 고칠 인덱스가 하나 는다(하루 1,370건에 블록 변경 약 2,700회, redo 약 0.3MB, 월초 정정분 4만 건에 redo 약 9.4MB). 새 항목은 매장 200곳 각자의 맨 끝에 붙으므로 한 블록에 몰리지도 않는다. X01을 쓰는 「일별 주문 조회」(ORD_DT = … ORDER BY ORD_HMS)는 바꾼 인덱스의 앞부분이 그대로 (ORD_DT, ORD_HMS)라 첫 화면 30줄에서 멈추는 모양이 바뀌지 않는다. 검증은 두 인덱스를 INVISIBLE로 만들어 세션에서 P1 ~ P4와 「일별 주문 조회」를 재고, VISIBLE로 연 뒤 옛 X01을 INVISIBLE로 돌려 한 주 지켜보고 지운다.

해설

P2에 ORD_NO까지 넣을지: X05에 ORD_NO를 붙이면 P2도 테이블에 가지 않아 약 3블록이 되지만 리프가 약 20% 커진다. P2는 7행뿐이라 테이블 7블록을 받아들였다. CF가 나쁜 테이블에서도 몇 행이면 테이블 액세스는 싸다. 비싼 것은 넓은 범위를 테이블에서 읽을 때다.
X01을 바꾸지 않는 안: P4는 하루 150번뿐이라 X05의 SKIP SCAN(매장 200곳마다 2 ~ 3블록, 약 500블록)이나 지금의 1,378블록으로 견딜 수도 있다(하루 약 7만 ~ 20만 블록). 운영 인덱스를 바꿔 다는 위험이 더 크다고 보면 X05만 만드는 것도 정답이다.
E-Rows를 믿지 않는다: P3이 FULL보다 두 배 무거운 인덱스 경로를 탄 것은 VARCHAR2 날짜의 선택도 오추정 때문이다(교재 03화, 04화 §5). 인덱스를 만든 뒤에도 실행계획의 E-Rows와 A-Rows를 함께 본다.
정렬 재적재와 비교: 테이블을 (SHOP_NO, ORD_DT) 순으로 다시 쌓으면 매장 쪽 CF는 좋아지지만 날짜로 읽는 SQL(X01)의 CF가 나빠진다. 한 테이블에서 좋은 순서는 하나뿐이라 커버링으로 피하는 쪽이 낫다(ACC-002, ACC-032).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 인덱스 규모와 CF는 교재 04화 실측 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 인덱스 규모와 CF는 교재 04화 실측 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황