인덱스를 더 만들 수 없는 주문 테이블에서 주간 주문번호 대사 파일이 9천 블록을 읽는다

실전SELECT버거115 / 700

상황

버거원 본사 회계팀은 매주 월요일 지난주 주문의 주문번호와 주문 일시를 내려받아 PG사 정산 파일과 맞춘다. 감사 기간에는 한 달 치를 받는다. 주간 파일 하나에 6초 넘게 걸리고, 그동안 POS 적재 세션의 응답이 늦어진다는 민원이 들어왔다. 본사 DBA 규정에는 이런 줄이 있다. "ORD_RANDOM에는 인덱스를 더 만들지 않는다(2013년 POS 장애 뒤 결정)." 박팀장은 이렇게 주문했다. "인덱스 없이 할 수 있는 것부터, 대가까지 적어 와라."

테이블과 인덱스

ORD_RANDOM(운영본): 1,000,000행 · 22,414블록(블록당 약 45행) · 서버 교체 때 무작위 순서로 다시 적재했다 · ORD_DT VARCHAR2(8) 2012 ~ 2013(하루 약 1,370건) · 2013-12-02 ~ 12-08 주문 9,589건 · ORD_NO NUMBER(10)은 PK라 NOT NULL
인덱스 ORD_RANDOM_PK (ORD_NO): BLEVEL 2 · 리프 약 2,090블록 · ORD_RANDOM_X01 (ORD_DT, ORD_HMS): BLEVEL 2 · 리프 3,774블록 · CLUSTERING_FACTOR 999,958(교재 04화 실측) · 통계 최신
같은 조건을 FULL로 읽으면 22,234블록이다(교재 04화 실측) · db_file_multiblock_read_count = 128
보관본 ORD: 내용이 같고 주문 시각 순으로 쌓였다 · ORD_X01의 CLUSTERING_FACTOR 22,223
ORD_DT가 문자라서 옵티마이저는 기간의 행 수를 실제보다 열 배쯤 낮게 본다(교재 04화 §5)

원본 SQL

sql

SELECT ORD_NO, ORD_DT, ORD_HMS
  FROM ORD_RANDOM
 WHERE ORD_DT BETWEEN :v_from AND :v_to
 ORDER BY ORD_DT, ORD_HMS

실행계획 학습용 예시 · CF와 FULL 블록 수는 교재 04화 실측

xplan

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |      1 |        |   9589 |00:00:06.31 |    9631 |  9418 |
|*  1 |  FILTER                      |                |      1 |        |   9589 |00:00:06.31 |    9631 |  9418 |
|   2 |   TABLE ACCESS BY INDEX ROWID| ORD_RANDOM     |      1 |    950 |   9589 |00:00:06.30 |    9631 |  9418 |
|*  3 |    INDEX RANGE SCAN          | ORD_RANDOM_X01 |      1 |    950 |   9589 |00:00:00.02 |      42 |    38 |
---------------------------------------------------------------------------------------------------------------

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

[세션 통계] physical read total IO requests 9,418 (multi block 0) · db file sequential read 9,418회 · 5.9초

문제

9,631블록의 대부분이 어디서 나오는지 실행계획과 CLUSTERING_FACTOR로 설명하시오.
인덱스를 만들지 않는 처방과 인덱스를 바꾸는 처방을 합쳐 넷 이상 들어 Buffers, I/O 방식, 대가를 비교하시오. 그중 지금 적용할 것을 골라 SQL을 작성하시오.
고른 처방이 테이블 액세스보다 유리해지는 기간이 대략 며칠부터인지 구하시오. 같은 처방을 보관본 ORD에 쓰면 어떻게 되는지도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 지금: 인덱스를 만들지 않고 두 인덱스를 ROWID 로 맞춘다 (테이블에 가지 않는다)
SELECT /*+ INDEX_JOIN(o ORD_RANDOM_X01 ORD_RANDOM_PK) */
       o.ORD_NO, o.ORD_DT, o.ORD_HMS
  FROM ORD_RANDOM o
 WHERE o.ORD_DT BETWEEN :v_from AND :v_to
 ORDER BY o.ORD_DT, o.ORD_HMS;

-- 다음 점검 시간 안건: X01 을 (ORD_DT, ORD_HMS, ORD_NO) 로 바꿔 단다 (인덱스 수는 그대로) → 약 45 블록 · 정렬 없음

개선 후 실행계획 학습용 예시 · CF와 FULL 블록 수는 교재 04화 실측

xplan

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                  |      1 |        |   9589 |00:00:00.41 |    2147 |  2131 |          |
|   1 |  SORT ORDER BY          |                  |      1 |    950 |   9589 |00:00:00.41 |    2147 |  2131 |  443K (0)|
|*  2 |   FILTER                |                  |      1 |        |   9589 |00:00:00.39 |    2147 |  2131 |          |
|*  3 |    VIEW                 | index$_join$_001 |      1 |    950 |   9589 |00:00:00.38 |    2147 |  2131 |          |
|*  4 |     HASH JOIN           |                  |      1 |        |   9589 |00:00:00.37 |    2147 |  2131 | 1586K (0)|
|*  5 |      INDEX RANGE SCAN   | ORD_RANDOM_X01   |      1 |    950 |   9589 |00:00:00.01 |      42 |    38 |          |
|   6 |      INDEX FAST FULL SCAN| ORD_RANDOM_PK   |      1 |   1000K|   1000K|00:00:00.19 |    2105 |  2093 |          |
--------------------------------------------------------------------------------------------------------------------------
   2 - filter(:V_FROM<=:V_TO)
   3 - filter(("ORD_DT">=:V_FROM AND "ORD_DT"<=:V_TO))
   4 - access(ROWID=ROWID)
   5 - access("ORD_DT">=:V_FROM AND "ORD_DT"<=:V_TO)

[세션 통계] I/O 요청 약 55번 (PK 멀티블록 17 · X01 싱글블록 38) · 6.31초 → 0.41초
[비교] FULL 22,234 · 한 달(41,666건)이면 인덱스 조인 약 2,265 / 테이블 액세스 41,825

모범 답안 서술

3번 줄 X01 범위 스캔은 42블록뿐이고, 나머지 9,589블록은 2번 줄 TABLE ACCESS BY INDEX ROWID에서 나온다. X01의 CLUSTERING_FACTOR가 999,958로 행 수와 같아서, 인덱스 순서대로 읽은 9,589행이 행마다 다른 테이블 블록에 있다. 그래서 행마다 한 블록씩 싱글블록 읽기를 했고(I/O 요청 9,418번), 6.31초의 대부분이 그 대기다. 테이블에서 가져오는 컬럼은 ORD_NO 하나뿐이다. 처방을 비교한다. ① 인덱스 조인 INDEX_JOIN(o ORD_RANDOM_X01 ORD_RANDOM_PK): X01 범위 스캔으로 (ORD_DT, ORD_HMS, ROWID)를 얻고, PK를 INDEX FAST FULL SCAN으로 읽어 (ORD_NO, ROWID)를 얻은 뒤 둘을 ROWID로 해시 조인한다(4번 줄 access(ROWID=ROWID), 3번 줄 VIEW index$_join$_001). 테이블에는 가지 않는다. Buffers는 9,631 → 2,147블록, I/O 요청은 9,418 → 약 55번이다(PK는 멀티블록). 대가가 셋 있다. PK FFS 몫 약 2,100블록은 기간이 짧아도 고정으로 든다. 결과가 X01 순서로 나오지 않아 SORT ORDER BY가 붙는다(9,589행, 메모리 안). 해시 조인 메모리를 쓴다(1.6MB). 또 SELECT에 두 인덱스에 없는 컬럼(예: SHOP_NO)이 하나라도 늘면 인덱스 조인을 할 수 없어 9,631블록으로 돌아간다. 힌트로 고정하므로 이 전제를 힌트 대장에 적는다. ② FULL: 22,234블록을 멀티블록으로 읽는다(약 180번 요청). 블록 수는 가장 많고, POS 적재와 같은 시간에 테이블을 통째로 훑는다. ③ X01을 (ORD_DT, ORD_HMS, ORD_NO)로 바꿔 단다: 인덱스 수가 늘지 않아 규정에 어긋나지 않는다. 범위만 읽고 끝나 약 45블록이고 정렬도 없다. 대가는 100만 행 인덱스를 다시 만드는 시간(ONLINE이라도 점검 시간에 한다)과 항목이 조금 커지는 것이다. ④ 정렬 재적재(CTAS … ORDER BY ORD_DT, ORD_HMS): CF가 22,223으로 돌아와 테이블 액세스가 약 255블록이 되고, 날짜로 찾는 다른 SQL도 좋아진다. 대가는 재적재 동안의 테이블 잠금과 시간이다(교재 04화, ACC-002). 지금 적용할 것은 ①이다. DDL 없이 SQL 하나만 고치면 되고, POS 적재와 부딪치지 않는다. ③은 다음 점검 시간 안건으로 올린다. 손익분기를 구한다. 인덱스 조인의 비용은 'X01 범위 + PK FFS 약 2,105블록'이고, 테이블 액세스의 비용은 'X01 범위 + 행 수'다. 그러므로 행 수가 약 2,100건을 넘으면 인덱스 조인이 유리하다. 하루가 약 1,370건이므로 하루 반 안팎이 손익분기다. 주간(9,589건)은 이미 네 배 넘게 지났고, 한 달(41,666건)이면 2,265 대 41,825다. 보관본 ORD는 CF가 22,223이라 한 주를 테이블로 찾아가도 약 255블록(9,589행 ÷ 블록당 45행 + 인덱스 42)이다. 같은 인덱스 조인을 쓰면 PK FFS 몫 2,100블록을 더 내는 셈이라 손해다. 인덱스 조인은 CF가 나빠 테이블 랜덤 액세스가 비쌀 때만 값을 한다.

해설

인덱스 조인이 되려면 SQL에 나오는 컬럼이 전부 조인할 인덱스들 안에 있어야 한다. 여기서는 ORD_DT, ORD_HMS(X01)와 ORD_NO(PK)다. 커버링 인덱스 하나로 할 일을 인덱스 둘로 나눠 하는 셈이다.
PK는 NOT NULL이라 모든 행이 들어 있어서 FFS 결과를 그대로 맞출 수 있다. NULL 허용 컬럼 하나만 담은 인덱스라면 그 컬럼이 NULL인 행이 빠지므로, 조건이 NULL을 걸러 주지 않는 한 옵티마이저는 그 인덱스로 인덱스 조인을 하지 않는다.
옵티마이저가 인덱스 조인을 스스로 고르지 않은 것은 ORD_DT가 문자라서 한 주의 행 수를 950건으로(실제 9,589건) 낮게 봤기 때문이다. 950건이면 테이블 액세스가 더 싸 보인다. 추정을 바로잡는 일은 통계의 문제이고 OPT 단원에서 다룬다.
병렬 FULL로 시간을 줄이는 방법도 있지만, POS 적재와 같은 서버에서 22,000블록을 여러 프로세스가 읽게 되어 민원의 원인(적재 응답 지연)을 키운다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · CF와 FULL 블록 수는 교재 04화 실측 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · CF와 FULL 블록 수는 교재 04화 실측 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황