리포팅 도구로 내려받는 고객별 주문 원장이 인덱스 순서대로 100만 번 테이블을 찾아간다

중급DDL한빛유통118 / 700

상황

마케팅팀은 매달 초 리포팅 도구로 「고객별 주문 원장」을 CSV로 내려받는다. 고객 번호, 주문일 순으로 100만 행 전부다. 다운로드는 바로 시작되는데 끝나기까지 오래 걸리고, 그동안 서버 CPU 하나가 계속 바쁘다. 리포팅 도구는 접속할 때마다 세션을 OPTIMIZER_MODE = FIRST_ROWS_10으로 바꾼다. 공급사는 이렇게 설명한다. "첫 화면을 빨리 띄우기 위한 기본값이다." 같은 SQL을 SQL*Plus(ALL_ROWS)에서 돌리면 계획이 다르다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록(블록당 약 172행) · ORD_SEQ 순으로 쌓였다 · CUS_ID NOT NULL 2,000명 · 한 고객의 주문은 ORD_SEQ 2,000건 간격이라 테이블 전체에 흩어져 있다
인덱스 IX_ORD_BIG_CUS_DT (CUS_ID, ORD_DT): 「고객 주문 이력」(CUS_ID = :c) 화면용 · BLEVEL 2 · 리프 약 3,600블록 · CLUSTERING_FACTOR 약 1,000,000 · 통계 최신 · 다른 인덱스는 이 SQL과 상관없다
세션의 정렬 작업 영역은 약 20MB까지다(PGA 자동 관리)
주문은 운영에서 계속 INSERT되고, 금액 정정 UPDATE가 하루 수십 건 있다(교재 04화 실측: 인덱스 하나에 5만 건당 redo 약 11.8MB)

원본 SQL

sql

-- 리포팅 도구 세션: ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10;
SELECT CUS_ID, ORD_DT, ORD_AMT
  FROM T_ORD_BIG
 ORDER BY CUS_ID, ORD_DT

실행계획 학습용 예시 · 인덱스 규모는 교재 08화의 (CUS_ID, ORD_AMT) 리프 3,624블록에서 어림

xplan

[리포팅 도구 — FIRST_ROWS_10]
----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |      1 |        |   1000K|00:00:04.62 |    1003K|
|   1 |  TABLE ACCESS BY INDEX ROWID| T_ORD_BIG         |      1 |   1000K|   1000K|00:00:04.62 |    1003K|
|   2 |   INDEX FULL SCAN           | IX_ORD_BIG_CUS_DT |      1 |   1000K|   1000K|00:00:00.41 |    3603 |
----------------------------------------------------------------------------------------------------------
(0번 줄 Buffers 정확한 값 1,003,603 · 버퍼 캐시에 테이블이 다 올라와 있을 때)

[SQL*Plus — ALL_ROWS]
-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads | Writes | Used-Mem | Used-Tmp |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |   1000K|00:00:01.71 |    5826 |  4102 |   4102 |          |          |
|   1 |  SORT ORDER BY     |           |      1 |   1000K|   1000K|00:00:01.71 |    5826 |  4102 |   4102 | 2142K (1)|    33M   |
|   2 |   TABLE ACCESS FULL| T_ORD_BIG |      1 |   1000K|   1000K|00:00:00.09 |    5826 |     0 |      0 |          |          |
-------------------------------------------------------------------------------------------------------------------------------

문제

같은 SQL이 리포팅 도구에서는 1,003K블록, SQL*Plus에서는 5,826블록과 TEMP 33MB를 쓰는 이유를 두 실행계획의 스캔 방식과 OPTIMIZER_MODE로 설명하시오.
세션 설정을 건드리지 않고 이 원장이 정렬 없이, 테이블에도 가지 않고 나오도록 인덱스를 설계하시오. 기존 인덱스를 어떻게 할지와 그 대가도 쓰시오.
INDEX FAST FULL SCAN으로는 왜 이 일을 할 수 없는지 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 정렬 키 (CUS_ID, ORD_DT) 뒤에 내려받는 컬럼 ORD_AMT 까지 — 인덱스를 순서대로 읽기만 하면 끝난다
CREATE INDEX IX_ORD_BIG_CUS_DT_AMT ON T_ORD_BIG (CUS_ID, ORD_DT, ORD_AMT) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- 원장과 「고객 주문 이력」 을 세션에서 재고 → T2

ALTER INDEX IX_ORD_BIG_CUS_DT_AMT VISIBLE;
ALTER INDEX IX_ORD_BIG_CUS_DT INVISIBLE;     -- 앞부분이 같다 · 관찰 뒤 DROP
-- DROP INDEX IX_ORD_BIG_CUS_DT;

SELECT CUS_ID, ORD_DT, ORD_AMT               -- SQL 은 그대로
  FROM T_ORD_BIG
 ORDER BY CUS_ID, ORD_DT;

개선 후 실행계획 학습용 예시 · 인덱스 규모는 교재 08화의 (CUS_ID, ORD_AMT) 리프 3,624블록에서 어림

xplan

[리포팅 도구 — FIRST_ROWS_10 그대로 · ALL_ROWS 에서도 같은 계획]
---------------------------------------------------------------------------------------------------------
| Id  | Operation        | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                       |      1 |        |   1000K|00:00:00.46 |    4253 |
|   1 |  INDEX FULL SCAN | IX_ORD_BIG_CUS_DT_AMT |      1 |   1000K|   1000K|00:00:00.46 |    4253 |
---------------------------------------------------------------------------------------------------------

모범 답안 서술

리포팅 도구는 세션을 FIRST_ROWS_10으로 연다. 옵티마이저는 '처음 10행을 가장 빨리 내는 계획'을 고르므로, 정렬을 기다리지 않고 바로 행을 낼 수 있는 길을 택한다. IX_ORD_BIG_CUS_DT는 (CUS_ID, ORD_DT) 순으로 정렬되어 있고 CUS_ID가 NOT NULL이라 모든 행이 들어 있다. 그래서 2번 줄처럼 INDEX FULL SCAN으로 인덱스를 처음부터 키 순서대로 읽으면 ORDER BY를 대신할 수 있다. 그런데 ORD_AMT가 인덱스에 없어서 행마다 테이블로 간다. CF가 약 1,000,000이라 100만 행이 거의 모두 다른 블록에 있고, 그래서 1번 줄이 1,003K블록이다. 처음 10행은 빨리 나오지만 100만 행을 다 내려받는 원장에는 가장 나쁜 계획이다. SQL*Plus의 ALL_ROWS는 전체를 다 내는 비용으로 계획을 고르므로 FULL(5,826블록)로 읽고 정렬한다. 100만 행 정렬이 작업 영역(약 20MB)을 넘어 TEMP를 33MB 쓰고, 한 번 나눠 정렬한다(Used-Mem의 (1), one-pass). 세션 설정과 상관없이 싸게 하려면 정렬 키 뒤에 내려받는 컬럼까지 담은 (CUS_ID, ORD_DT, ORD_AMT) 인덱스를 둔다. INDEX FULL SCAN 하나로 정렬된 결과를 내고 테이블에는 가지 않는다. 처음 10행도 빨리 나오고 전체도 싸므로, FIRST_ROWS_10이든 ALL_ROWS든 같은 계획이 된다. 1,003K → 약 4,253블록이고, 정렬과 TEMP 33MB가 사라진다. 기존 (CUS_ID, ORD_DT)는 새 인덱스의 앞부분이므로 「고객 주문 이력」(CUS_ID = :c)도 새 인덱스로 처리된다. 그래서 하나 더 만들지 않고 바꿔 단다(INVISIBLE로 바꿔 관찰한 뒤 DROP). 대가는 항목이 커지는 것(리프 약 3,600 → 4,250블록)과, 주문 금액을 정정하는 UPDATE가 이제 인덱스도 고친다는 것이다. INDEX FAST FULL SCAN은 세그먼트에 블록이 놓인 순서대로 멀티블록으로 읽으므로 키 순서를 보장하지 않는다. FFS로 읽으면 정렬이 다시 붙는다. ORDER BY를 인덱스로 대신하려면 리프를 키 순서로 따라가는 INDEX FULL SCAN이어야 한다.

해설

INDEX FULL SCAN으로 정렬을 대신하는 것이 이득인지는 테이블에 가는지, 간다면 CF가 어떤지로 정해진다. 테이블에 가지 않거나 CF가 좋으면 이득이고, 이 문제처럼 CF가 나쁜 테이블에 행마다 가면 정렬보다 훨씬 비싸다.
원장 SQL에만 /*+ ALL_ROWS */ 힌트를 넣는 것도 답이다(FULL과 정렬, 5,826블록과 TEMP 33MB). 인덱스를 바꿀 수 없을 때 쓴다.
화면용 '첫 10행'과 파일용 '전체'를 한 세션 설정으로 맞추려는 것이 근본 원인이다. 리포팅 도구에서 다운로드용 연결의 옵티마이저 모드를 따로 둘 수 있는지 공급사에 확인한다.
버퍼 캐시에 테이블이 다 올라와 있어서 1,003K블록이 4.6초에 끝났다. 캐시에 없으면 100만 번의 싱글블록 읽기가 되어 몇 분이 걸릴 수 있다(교재 04화 §2).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 인덱스 규모는 교재 08화의 (CUS_ID, ORD_AMT) 리프 3,624블록에서 어림 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 인덱스 규모는 교재 08화의 (CUS_ID, ORD_AMT) 리프 3,624블록에서 어림 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황