시험 주문 한 건을 되살린 뒤 금액대별 등급 매출이 어느 구간이든 고객 테이블을 20만 번 읽는다

중급SELECT한빛유통617 / 700

상황

영업기획팀의 「금액대별 등급 매출」은 금액 구간을 고르면 그 구간 주문을 고객 등급별로 묶는다. 구간은 10만 원 단위이고 화면이 리터럴로 넣는다. 지난주부터 어느 구간을 골라도 느리다는 말이 나왔다. 조민우가 보니 지난 일요일에 통계가 다시 모였고, 그 전 주에 감사팀 요청으로 2019년 부하 시험 주문 한 건을 백업에서 되살려 T_ORD_BIG에 넣었다. 주문 금액이 9,999,999,999원이다. 감사가 끝날 때까지 지우지 말라는 요청이 붙어 있다.

테이블과 인덱스

T_ORD_BIG: 1,000,001행 · 5,826블록 · ORD_AMT 10,000 ~ 500,000원(100원 단위, 고르게) + 시험 주문 1건 9,999,999,999원 · 이 SQL에 쓰이는 인덱스는 없다
통계: 지난 일요일 수집 · ORD_AMT NUM_DISTINCT 4,902 · 최솟값 10,000 · 최댓값 9,999,999,999 · 히스토그램 없음 · 선호도 FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 ORD_ST(정책서 1호)
10만 원 이상 20만 원 미만 주문 204,082건(구간마다 약 20만 건)
M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · CUS_GD A · B · C

원본 SQL

sql

SELECT c.CUS_GD, COUNT(*) AS cnt, ROUND(AVG(o.ORD_AMT)) AS avg_amt
  FROM T_ORD_BIG o, M_CUS c
 WHERE o.ORD_AMT >= 100000
   AND o.ORD_AMT <  200000
   AND c.CUS_ID = o.CUS_ID
 GROUP BY c.CUS_GD

실행계획 학습용 예시

xplan

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |      1 |        |      3 |00:00:00.18 |    214K |
|   1 |  HASH GROUP BY               |           |      1 |      3 |      3 |00:00:00.18 |    214K |
|   2 |   NESTED LOOPS               |           |      1 |    214 |    204K|00:00:00.17 |    214K |
|   3 |    NESTED LOOPS              |           |      1 |    214 |    204K|00:00:00.09 |   10112 |
|*  4 |     TABLE ACCESS FULL        | T_ORD_BIG |      1 |    214 |    204K|00:00:00.05 |    5826 |
|*  5 |     INDEX UNIQUE SCAN        | PK_M_CUS  |    204K|      1 |    204K|00:00:00.03 |    4286 |
|   6 |    TABLE ACCESS BY INDEX ROWID| M_CUS    |    204K|      1 |    204K|00:00:00.06 |    204K |
---------------------------------------------------------------------------------------------
   4 - filter(("O"."ORD_AMT">=100000 AND "O"."ORD_AMT"<200000))
   5 - access("C"."CUS_ID"="O"."CUS_ID")

[USER_TAB_COL_STATISTICS · ORD_AMT]  NUM_DISTINCT 4,902 · LOW 10000 · HIGH 9999999999 · HISTOGRAM NONE

문제

4번 줄의 E-Rows 214를 손으로 계산하고, 시험 주문 한 건이 어느 구간에서나 추정을 망가뜨린 이유를 설명하시오.
시험 주문을 지울 수 없다는 조건에서 SQL은 그대로 두고 통계로 고치시오. 감사가 끝난 뒤의 처리도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ORD_AMT 에 히스토그램 : 칸마다 건수를 기억하므로 시험 주문 한 건은 마지막 한 칸만 늘린다
-- 정책서 1호의 ORD_ST FREQUENCY 는 그대로 둔다
BEGIN
  DBMS_STATS.SET_TABLE_PREFS(USER, 'T_ORD_BIG', 'METHOD_OPT',
    'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 ORD_ST FOR COLUMNS SIZE 254 ORD_AMT');
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_ORD_BIG', no_invalidate => FALSE);
END;
/

-- SQL 은 그대로
SELECT c.CUS_GD, COUNT(*) AS cnt, ROUND(AVG(o.ORD_AMT)) AS avg_amt
  FROM T_ORD_BIG o, M_CUS c
 WHERE o.ORD_AMT >= 100000
   AND o.ORD_AMT <  200000
   AND c.CUS_ID = o.CUS_ID
 GROUP BY c.CUS_GD;

-- 감사가 끝나면 : 시험 주문을 보관 테이블로 옮기고(INSERT … SELECT) 원장에서 지운 뒤 다시 모은다
--   → 최댓값이 500,000 으로 돌아가면 히스토그램 없이도 구간 추정이 맞는다 (정책서에서 이 줄을 뺄지 함께 정한다)

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

xplan

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |      1 |        |      3 |00:00:00.12 |    5856 |          |
|   1 |  HASH GROUP BY      |           |      1 |      3 |      3 |00:00:00.12 |    5856 |          |
|*  2 |   HASH JOIN         |           |      1 |    201K|    204K|00:00:00.11 |    5856 | 1685K (0)|
|   3 |    TABLE ACCESS FULL| M_CUS     |      1 |   2000 |   2000 |00:00:00.01 |      30 |          |
|*  4 |    TABLE ACCESS FULL| T_ORD_BIG |      1 |    201K|    204K|00:00:00.05 |    5826 |          |
--------------------------------------------------------------------------------------------------
   2 - access("C"."CUS_ID"="O"."CUS_ID")
   4 - filter(("O"."ORD_AMT">=100000 AND "O"."ORD_AMT"<200000))

[USER_TAB_COL_STATISTICS · ORD_AMT]  HISTOGRAM HYBRID · NUM_BUCKETS 254 (마지막 끝점 9999999999 · 반복 1)

모범 답안 서술

히스토그램이 없으면 범위 조건의 선택도는 (상한 − 하한) / (최댓값 − 최솟값)에 닫힌 끝 몫(1/NDV)을 더한 값이다. 지금 최댓값은 시험 주문의 9,999,999,999라 100,000 / 9,999,989,999 = 0.00001이고, 여기에 1/4,902 = 0.000204를 더해 1,000,001 × 0.000214 = 214가 됐다. 실제 10만 ~ 20만 원 주문은 204,082건이다. 분모가 시험 주문 한 건 때문에 2만 배로 늘어서, 50만 원 아래의 어느 구간을 골라도 거의 0으로 본다. 214건이라고 믿은 옵티마이저는 주문마다 고객을 찾아가는 NL을 골랐다. 5번과 6번 줄의 Starts가 204,082이고, M_CUS 테이블에서만 20만 블록을 읽었다. 고객 2,000명을 메모리에 올려 두는 해시 조인이면 5,856블록이다. 시험 주문은 감사가 끝날 때까지 지울 수 없으므로 ORD_AMT에 히스토그램을 모은다. 값이 4,902종이라 254칸의 HYBRID가 되고, 칸마다 건수를 기억한다. 9,999,999,999는 마지막 한 칸의 끝점으로만 남고(반복 1), 10,000 ~ 500,000은 나머지 칸이 고르게 나눠 가지므로 구간 추정이 실제에 가까워진다(201K / 204K). 해시 조인으로 돌아가 Buffers 214K → 5,856이다. 선호도에 넣어 밤의 자동 수집도 같은 규칙을 따르게 한다. 대가도 있다. HYBRID는 표본으로 만들어 수집할 때마다 숫자가 조금씩 다르고, 이 열을 바인드 변수로 받는 화면이 생기면 첫 값이 계획을 정한다. 근본 처리는 데이터다. 감사가 끝나면 시험 주문을 보관 테이블로 옮기고 원장에서 지운 뒤 다시 모은다. 최댓값이 500,000으로 돌아오면 히스토그램 없이도 구간 추정이 맞으므로, 그때 이 히스토그램을 남길지 정책서에서 다시 정한다.

해설

반대 방향도 틀린다. 지금 ORD_AMT >= 450000(고액 주문)은 (9,999,999,999 − 450,000) / 분모로 거의 100%로 본다(실제 10%). 이 SQL은 어차피 FULL이라 티가 나지 않을 뿐이다.
같은 일이 날짜에도 생긴다. '날짜 미정'을 9999-12-31로 넣는 습관이 있으면 최댓값이 수천 년 뒤로 늘어 모든 기간 조건이 0에 가까워진다. 모르는 값은 NULL로 두는 것이 통계에도 맞다.
SET_COLUMN_STATS로 최댓값을 500,000으로 덮어쓰는 안은 다음 수집 때 사라지고, 손으로 넣은 통계가 실제와 다르다는 사실을 아무도 모르게 된다. 감사 기간이 길면 히스토그램이 더 안전하다.
통계를 바꾼 뒤에는 ORD_AMT를 조건으로 쓰는 다른 SQL의 계획 해시도 확인한다(교재 10화 SOP ⑦).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황