2년 전에 고쳐 놓은 월마감 집계가 다시 느리다

실전SELECT버거77 / 700

상황

버거원 본사의 월마감 화면 「가맹점별 주문 건수」가 다시 느려졌다. 2년 전 외부 점검 보고서(강도현)에는 "ORD_DT 선두 인덱스 RANGE SCAN으로 변경 완료" 한 줄만 있고 숫자가 없다. 1년 전에는 서버를 바꾸면서 주문을 다시 적재했다. 운영본은 ORD_RANDOM이고, 이관 전 보관본은 ORD다. 같은 SQL을 테이블 이름만 바꿔 양쪽에서 쟀다. 박팀장은 이렇게 주문했다. "월말에 본사 전 부서가 이 화면을 연다. 무엇을 할지, 대가까지 적어 와라."

테이블과 인덱스

ORD_RANDOM(운영본): 1,000,000행 · 22,414블록 · 서버 교체 때 ORDER BY 없이 다시 적재
ORD(이관 전 보관본): 1,000,000행 · 22,422블록 · 주문 시각 순으로 쌓였다 · 내용은 ORD_RANDOM과 같다
ORD_DT VARCHAR2(8) '20120101' ~ '20131231' · 한 달 약 41,666건(4.17%) · SHOP_NO 200개 매장
인덱스 ORD_RANDOM_X01 (ORD_DT, ORD_HMS) · ORD_X01 (ORD_DT, ORD_HMS): 정의가 같다 · 통계 최신

원본 SQL

sql

SELECT /*+ INDEX(o ORD_RANDOM_X01) */
       o.SHOP_NO, COUNT(*) AS ord_cnt
  FROM ORD_RANDOM o
 WHERE o.ORD_DT BETWEEN '20120101' AND '20120131'
 GROUP BY o.SHOP_NO

실행계획 실측 · 교재 04화

xplan

[ORD_RANDOM — 운영본]
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |    200 |00:00:00.05 |   41825 |
|   1 |  HASH GROUP BY                       |                |      1 |    200 |    200 |00:00:00.05 |   41825 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORD_RANDOM     |      1 |   4065 |  41666 |00:00:00.04 |   41825 |
|*  3 |    INDEX RANGE SCAN                  | ORD_RANDOM_X01 |      1 |   4065 |  41666 |00:00:00.01 |     160 |
-----------------------------------------------------------------------------------------------------------------
   3 - access("O"."ORD_DT">='20120101' AND "O"."ORD_DT"<='20120131')

[ORD — 이관 전 보관본 · 같은 SQL (힌트의 인덱스 이름만 ORD_X01)]
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |    200 |00:00:00.02 |    1086 |
|   1 |  HASH GROUP BY                       |                |      1 |    200 |    200 |00:00:00.02 |    1086 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORD            |      1 |   4065 |  41666 |00:00:00.02 |    1086 |
|*  3 |    INDEX RANGE SCAN                  | ORD_X01        |      1 |   4065 |  41666 |00:00:00.01 |     160 |
-----------------------------------------------------------------------------------------------------------------
   3 - access("O"."ORD_DT">='20120101' AND "O"."ORD_DT"<='20120131')

[같은 조건 · FULL 강제]  ORD_RANDOM 22,234 · ORD 22,239

[딕셔너리 — USER_INDEXES]
INDEX_NAME       BLEVEL LEAF_BLOCKS   NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
---------------- ------ ----------- ---------- ------------- -----------------
ORD_RANDOM_X01        2        3774    1000000        986510            999958
ORD_X01               2        3774    1000000        986510             22223

문제

SQL과 인덱스 정의, 행수가 모두 같은데 Buffers가 41,825 대 1,086으로 갈리는 이유를 실행계획의 어느 줄과 딕셔너리의 어느 숫자로 알 수 있는지 설명하시오. 강도현의 보고서가 그 당시에 틀렸는지도 판단하시오.
처방을 셋 이상 들어 효과(Buffers)와 대가, 효과가 오래 가는지를 비교하시오. 그중 하나를 골라 SQL과 DDL을 작성하시오. ALTER INDEX … REBUILD가 처방이 아닌 이유도 설명하시오.
이 테이블에서 ORD_DT 범위가 며칠을 넘으면 인덱스 경로가 FULL보다 불리해지는지, 그 값을 어떻게 구하는지 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 처방 ① (고른 것) 커버링 — 이 SQL 이 쓰는 ORD_DT · SHOP_NO 를 다 담아 테이블에 가지 않는다 (CF 를 피한다)
CREATE INDEX ORD_RANDOM_X02 ON ORD_RANDOM (ORD_DT, SHOP_NO) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- 내 세션에서만 재고 · T2 승인 뒤 VISIBLE

SELECT o.SHOP_NO, COUNT(*) AS ord_cnt          -- 2년 전의 INDEX 힌트를 뗀다
  FROM ORD_RANDOM o
 WHERE o.ORD_DT BETWEEN '20120101' AND '20120131'
 GROUP BY o.SHOP_NO;

-- 처방 ② (비교) 정렬 재적재 — CF 를 고친다 · 복사본에서 재고 · 점검 시간에 바꿔 단다
--   CREATE TABLE ORD_SORTED AS SELECT * FROM ORD_RANDOM ORDER BY ORD_DT, ORD_HMS;   → CF 22,223
-- 처방 ③ (비교) 힌트만 뗀다 — 옵티마이저가 FULL 을 고른다 (22,234)

개선 후 실행계획 실측 · 교재 04화

xplan

[처방 ① 커버링 — 세션에서만 켜고]
-----------------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |      1 |        |    200 |00:00:00.01 |     160 |
|   1 |  HASH GROUP BY     |                |      1 |    200 |    200 |00:00:00.01 |     160 |
|*  2 |   INDEX RANGE SCAN | ORD_RANDOM_X02 |      1 |   4065 |  41666 |00:00:00.01 |     160 |
-----------------------------------------------------------------------------------------------
   2 - access("O"."ORD_DT">='20120101' AND "O"."ORD_DT"<='20120131')

[비교]  ② 정렬 재적재(복사본 ORD_SORTED) 41,825 → 1,086 · ③ 힌트만 떼면 FULL 22,234
        ① 에 컬럼 하나를 더하면 (MAX(o.ORD_NO)) TABLE ACCESS 가 돌아와 41,819

모범 답안 서술

3번 줄(INDEX RANGE SCAN)은 양쪽 다 160블록, 41,666행으로 같다. 갈린 것은 2번 줄 TABLE ACCESS BY INDEX ROWID로, 41,825 대 1,086이니 38.5배 차이다. 같은 행을 같은 순서로 찾았는데 테이블 블록을 찾아가는 횟수가 다르다. 원인은 딕셔너리의 CLUSTERING_FACTOR에 있다. CF는 인덱스 순서대로 읽을 때 테이블 블록을 몇 번 바꿔 타는지를 센 값이다. ORD는 22,223으로 테이블 블록 수(22,422)와 비슷해서, 한 블록에서 평균 45행을 연달아 읽는다. ORD_RANDOM은 999,958로 행수와 비슷해서, 행마다 다른 블록으로 간다. 서버를 교체하면서 ORDER BY 없이 다시 적재한 순간 생긴 차이다. 강도현의 보고서는 그때(ORD의 순서)에는 옳았다(1,086 대 FULL 22,239). 다만 숫자와 조건을 적지 않아서, 재적재 뒤에 틀린 문서가 된 것을 아무도 몰랐을 뿐이다. REBUILD는 처방이 아니다. 인덱스를 다시 베껴 쓸 뿐이고 테이블 행이 어느 블록에 있는지는 그대로이므로 CF도 999,958 그대로다. CF는 인덱스 뷰에 붙어 있지만 테이블이 쌓인 순서에서 나오는 성질이다. MOVE도 읽은 순서대로 다시 쓸 뿐이고, 인덱스를 UNUSABLE 상태로 만든다. 처방을 비교하면 다음과 같다. ① 커버링 (ORD_DT, SHOP_NO): 테이블에 가지 않으므로 CF만큼의 비용이 들지 않는다(41,825 → 160). 대가는 인덱스 하나(31MB)만큼의 적재 비용이다. 또 SELECT에 컬럼이 하나만 늘어도(MAX(ORD_NO)) 테이블 액세스가 돌아와 41,819로 무너지므로, 전제하는 컬럼 목록을 T2에 적어 둔다. ② 정렬 재적재(CTAS … ORDER BY ORD_DT, ORD_HMS): CF가 22,223으로 돌아와 41,825 → 1,086이 되고, ORD_DT로 찾는 다른 SQL까지 좋아진다. 대가는 재적재하는 동안 테이블이 잠긴다는 것이다(100만 행에 약 5초, 운영 규모라면 훨씬 길다). 다음 이관이나 백필이 행을 다시 흩어 놓기도 한다(5%를 섞어 넣자 CF가 23,387 → 122,193). ③ 힌트만 뗀다: 옵티마이저가 FULL을 골라 22,234가 된다. 가장 싸고 안전하지만 효과는 약하다. 월말에 전 부서가 여는 고정 SQL이므로 ①을 INVISIBLE로 만들어 재 본 뒤 고르고, ②는 다음 점검 시간의 과제로 올린다. 손익분기는 인덱스 경유 비용 BLEVEL + (LEAF × s) + (CF × s)와 FULL의 테이블 블록 수를 같게 놓고 푼다. (3,774 + 999,958) × s = 22,234 → s ≈ 2.2%이므로, 한 달의 절반인 16 ~ 17일이다(실측 16일 21,458, 17일 22,917). 같은 식으로 풀면 ORD는 85.5%다. 손익분기는 책에 적힌 숫자가 아니라 그 테이블의 CF가 정한다.

해설

옵티마이저는 2.2%가 아니라 4 ~ 6%에서 갈아탄다. ORD_DT가 문자(VARCHAR2)라서 한 달의 E-Rows를 4,065로(실제 41,666) 10배 낮게 보기 때문이다. 힌트를 떼도 한 달 근처에서는 인덱스를 고를 수 있다(교재 03화, 04화 §5).
커버링 인덱스 X02의 CF는 999,850으로 여전히 최악이다. CF를 고친 것이 아니라 피한 것이다. 길은 셋이다. 고치거나(재적재), 피하거나(커버링), 받아들인다(FULL).
정렬 재적재는 한 번 하고 끝낼 일이 아니라 적재 규칙이 되어야 효과가 오래 간다. 이관과 백필 절차서에 ORDER BY ORD_DT, ORD_HMS를 넣는다. POS 실시간 적재는 시각 순이라 CF가 유지된다(5만 건에 22,223 → 23,387).
한 테이블에서 CF가 좋을 수 있는 정렬 순서는 하나뿐이다. 날짜순으로 쌓으면 매장이 선두인 인덱스의 CF는 나빠진다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 실측 · 교재 04화 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 실측 · 교재 04화 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황