인덱스를 만들었는데 결제 대기 모니터가 여전히 FULL이다

기초SELECT이커머스592 / 700

이 문제는 교재 18화 졸업 과제와 같은 이커머스 데이터베이스를 씁니다. 『튜너의 1년』을 순서대로 읽고 있다면 졸업 과제를 먼저 풀고 오세요. 아래 「테이블과 인덱스」 칸에 과제의 답 일부(제약 조건, 인덱스, 통계의 실제 상태)가 나옵니다.

상황

결제팀 대시보드의 「결제 대기 주문」 칸은 화면 20개가 10초마다 새로 고친다(하루 약 7만 번). 개발팀이 지난주 (status, order_date) 인덱스를 만들었는데, 돌아온 말은 이랬다. "계획이 그대로 FULL이에요." ora-newshop은 적재 직후 GATHER_SCHEMA_STATS를 기본값으로 한 번 돌린 상태다. 데이터셋 설명서에는 이렇게 적혀 있다. "grade · status 같은 편향 컬럼에 히스토그램"

테이블과 인덱스

orders: 34,908행 · 193블록 · status 6종, 이 문제의 분포는 DELIVERED 27,926 · SHIPPED 2,793 · PAID 1,745 · CANCELLED 1,396 · RETURNED 1,034 · PENDING 14
인덱스: PK(order_id) · IX_ORD_ST_DT (status, order_date)(지난주 생성, 높이 2)
통계: 적재 직후 기본값으로 수집(METHOD_OPT = FOR ALL COLUMNS SIZE AUTO) · status NUM_DISTINCT 6 · HISTOGRAM NONE

원본 SQL

sql

SELECT order_id, customer_id, order_date, total_amount
  FROM orders
 WHERE status = 'PENDING'
 ORDER BY order_date

실행계획 학습용 예시

xplan

---------------------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |     14 |00:00:00.01 |     193 |          |
|   1 |  SORT ORDER BY     |        |      1 |   5818 |     14 |00:00:00.01 |     193 | 2048  (0)|
|*  2 |   TABLE ACCESS FULL| ORDERS |      1 |   5818 |     14 |00:00:00.01 |     193 |          |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("STATUS"='PENDING')

[USER_TAB_COL_STATISTICS — ORDERS]
 COLUMN_NAME   NUM_DISTINCT  HISTOGRAM
 STATUS                   6  NONE

문제

E-Rows 5,818이 어디서 나온 숫자인지 손으로 계산하고, 옵티마이저가 새 인덱스를 두고 FULL을 고른 이유를 쓰시오.
SQL은 그대로 두고 통계로 고치시오. 밤마다 도는 자동 수집도 같은 규칙을 따르게 하고, 그 처방의 대가도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- status 하나만 FREQUENCY 히스토그램 — 다른 컬럼은 지금처럼 둔다 (바뀌는 것을 하나로 한정)
-- method_opt 를 그때그때 주지 않고 테이블 선호도로 건다 → 밤의 자동 수집도 이 규칙대로 모은다
BEGIN
  DBMS_STATS.SET_TABLE_PREFS('ECOMMERCE', 'ORDERS', 'METHOD_OPT',
                             'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS SIZE 254 STATUS');
  DBMS_STATS.GATHER_TABLE_STATS('ECOMMERCE', 'ORDERS', no_invalidate => FALSE);
END;
/

-- SQL 은 그대로
SELECT order_id, customer_id, order_date, total_amount
  FROM orders
 WHERE status = 'PENDING'
 ORDER BY order_date;

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

xplan

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |      1 |        |     14 |00:00:00.01 |      16 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ORDERS       |      1 |     14 |     14 |00:00:00.01 |      16 |
|*  2 |   INDEX RANGE SCAN          | IX_ORD_ST_DT |      1 |     14 |     14 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("STATUS"='PENDING')

[USER_TAB_COL_STATISTICS — ORDERS]
 STATUS   6  FREQUENCY

모범 답안 서술

E-Rows 5,818은 34,908 × 1/6이다. status에 히스토그램이 없어서(NONE) 오라클은 여섯 값이 똑같이 많다고(균등, 1/NDV) 셈했다. 실제 PENDING은 14행이니 400배 넘게 크게 봤다. 5,818행을 인덱스로 찾아 테이블에 가면 FULL(193블록)보다 비싸다고 보았으므로, 새 인덱스 IX_ORD_ST_DT를 두고 FULL + 정렬을 골랐다. 그 숫자에서는 옳은 판단이었다. 틀린 것은 숫자다. 적재 직후에 기본값(SIZE AUTO)으로 모은 탓이다. SIZE AUTO는 조건절에 쓰인 적이 있고(컬럼 사용 기록) 값이 치우친 컬럼에만 히스토그램을 만든다. 적재 직후에는 아무 SQL도 돌지 않았으므로 기록이 없었다. 설명서에 적힌 '편향 컬럼에 히스토그램'은 데이터 사전의 실제 상태와 달랐다. status에 FREQUENCY 히스토그램을 모으면 E-Rows가 14가 되고 INDEX RANGE SCAN을 탄다(정렬도 사라진다). Buffers는 193 → 16이고, 하루 7만 번이면 약 1,350만 → 112만 블록이다. method_opt를 그때그때 주지 않고 테이블 선호도(SET_TABLE_PREFS)로 걸어서, 밤의 자동 수집도 같은 규칙으로 모으게 한다. 대가는 세 가지다. ① 이 컬럼을 바인드(:st)로 받는 문장이 생기면 첫 값을 엿보고(바인드 피킹) 계획이 굳는다. 값이 손에 꼽을 만큼 적다면 리터럴로 쓴다. ② FREQUENCY는 모은 순간의 값만 안다. 새 상태값은 가장 적은 값의 절반쯤으로 추정한다. ③ status 조건을 쓰는 다른 SQL의 계획도 바뀔 수 있으므로, 걸기 전후로 영향 분석을 한다.

해설

SIZE AUTO로 다시 모으기만 하면 어떨까? 이 화면이 돌았으니 컬럼 사용 기록이 생겨 히스토그램이 생길 수도 있다. 그러나 '언제, 누가'가 빠진 변화다. 테이블이 STALE이 되는 어느 밤에 저절로 생기므로, 계획이 저절로 바뀌는 길이 하나 더 생긴다(10화).
FOR ALL COLUMNS SIZE 1 …로 고정하는 정책(10화 정책서 1호)도 있다. 그러면 이미 생긴 HYBRID나 FREQUENCY 히스토그램이 사라지므로, 그 컬럼을 쓰는 SQL의 영향 분석과 함께 정한다.
버킷이 값의 수보다 적으면 FREQUENCY 대신 HYBRID나 TOP-FREQUENCY가 되어 희귀 값의 E-Rows가 1로 떨어질 수 있다(10화 SIZE 2에서 CANCEL 1). 값이 6종이면 254로 넉넉하다.
no_invalidate => FALSE로 모아야 공유 풀에 있는 FULL 계획의 커서가 다음 실행 때 바로 다시 파싱된다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황