히스토그램 칸을 다섯 개로 줄인 뒤 2,000원 균일가와 12,000원 세트 분석만 느려졌다

중급PLSQL유통610 / 700

상황

본사 MD팀의 「단가대별 매장 판매」는 단가를 하나 고르면 그 단가로 팔린 판매상품 줄을 매장별로 센다(단가는 화면이 리터럴로 넣는다). MD 20명이 하루 수백 번 누른다. 6월에 유통지원팀 DBA가 판매상품 테이블의 통계 수집을 가볍게 하겠다며 단가 히스토그램의 칸을 다섯 개로 줄였다. 스크립트 주석에는 "단가는 많이 팔리는 다섯 개만 알면 된다"라고 적혀 있다. 그 뒤로 2,500원이나 3,000원은 그대로인데, 2,000원 균일가 상품과 12,000원 선물 세트를 고를 때만 0.06초에서 0.25초로 느려졌다.

테이블과 인덱스

CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · SLE_UPRC 단가 11종(상품마다 단가가 하나다)
단가 분포: 2,500원 533,248 · 3,000원 241,587 · 3,500원 187,601 · 4,500원 24,478 · 8,000원 22,081 · 12,000원 18,789(가장 비싼 값) · 5,000원 15,818 · 4,000원 15,493 · 7,000원 12,631 · 2,000원 12,556(가장 싼 값) · 10,000원 12,426
지금 통계: SLE_UPRC NUM_DISTINCT 11 · HISTOGRAM TOP-FREQUENCY · NUM_BUCKETS 5 · 선호도 FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 5 SLE_UPRC
CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO): BLEVEL 2

원본 SQL

sql

-- 6월 · 유통지원팀 DBA : CF11 통계 수집을 가볍게 ("단가는 많이 팔리는 다섯 개만 알면 된다")
BEGIN
  DBMS_STATS.SET_TABLE_PREFS('PUP', 'CF11', 'METHOD_OPT',
                             'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 5 SLE_UPRC');
  DBMS_STATS.GATHER_TABLE_STATS('PUP', 'CF11');
END;
/

-- (참고) 「단가대별 매장 판매」 : SQL 은 고치지 않는다
SELECT h.SHP_NO, COUNT(*) AS line_cnt, SUM(s.SLE_QTY) AS qty
  FROM CF11 s, CF10 h
 WHERE s.SLE_UPRC = 2000
   AND h.SLE_NO = s.SLE_NO
 GROUP BY h.SHP_NO;

실행계획 학습용 예시 · 단가 분포와 TOP-FREQUENCY 끝점은 교재 10화 복사본 측정

xplan

[USER_TAB_HISTOGRAMS · CF11.SLE_UPRC (SIZE 5)]
 ENDPOINT_NUMBER   ENDPOINT_VALUE
               1             2000
         533,249             2500
         774,836             3000
         962,437             3500
         962,438            12000

[「단가대별 매장 판매」 · SLE_UPRC = 2000]
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |      5 |00:00:00.25 |   45872 |
|   1 |  HASH GROUP BY               |         |      1 |      1 |      5 |00:00:00.25 |   45872 |
|   2 |   NESTED LOOPS               |         |      1 |      1 |  12556 |00:00:00.24 |   45872 |
|   3 |    NESTED LOOPS              |         |      1 |      1 |  12556 |00:00:00.19 |   33316 |
|*  4 |     TABLE ACCESS FULL        | CF11    |      1 |      1 |  12556 |00:00:00.11 |    8204 |
|*  5 |     INDEX UNIQUE SCAN        | CF10_PK |  12556 |      1 |  12556 |00:00:00.06 |   25112 |
|   6 |    TABLE ACCESS BY INDEX ROWID| CF10   |  12556 |      1 |  12556 |00:00:00.04 |   12556 |
-----------------------------------------------------------------------------------------------
   4 - filter("S"."SLE_UPRC"=2000)
   5 - access("H"."SLE_NO"="S"."SLE_NO")

[같은 SQL · SLE_UPRC = 2500]   E-Rows 533K · HASH JOIN · Buffers 12,787 · 0.06초
[같은 SQL · SLE_UPRC = 12000]  E-Rows 1 · NESTED LOOPS · Buffers 61,6xx · 0.31초

문제

2,000원과 12,000원에서만 4번 줄의 E-Rows가 1이 된 이유를 히스토그램 끝점으로 설명하고, 그 1 때문에 무엇을 골랐는지 쓰시오.
SQL은 그대로 두고 통계 설정을 고치시오. 칸을 다섯 개로 줄여서 얻은 것이 있는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 단가는 11종 → 버킷을 값의 수 이상(254)으로 주면 FREQUENCY : 값마다 정확한 건수를 기억한다
-- 선호도로 걸어 밤의 자동 수집도 같은 규칙을 따르게 한다
BEGIN
  DBMS_STATS.SET_TABLE_PREFS('PUP', 'CF11', 'METHOD_OPT',
                             'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 SLE_UPRC');
  DBMS_STATS.GATHER_TABLE_STATS('PUP', 'CF11', no_invalidate => FALSE);
END;
/

-- SQL 은 그대로
SELECT h.SHP_NO, COUNT(*) AS line_cnt, SUM(s.SLE_QTY) AS qty
  FROM CF11 s, CF10 h
 WHERE s.SLE_UPRC = 2000
   AND h.SLE_NO = s.SLE_NO
 GROUP BY h.SHP_NO;

개선 후 실행계획 학습용 예시 · 단가 분포와 TOP-FREQUENCY 끝점은 교재 10화 복사본 측정

xplan

[USER_TAB_COL_STATISTICS · SLE_UPRC]  NUM_DISTINCT 11 · FREQUENCY · NUM_BUCKETS 11

---------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      5 |00:00:00.06 |   12787 |          |
|   1 |  HASH GROUP BY      |      |      1 |      5 |      5 |00:00:00.06 |   12787 |          |
|*  2 |   HASH JOIN         |      |      1 |  12556 |  12556 |00:00:00.06 |   12787 | 1335K (0)|
|*  3 |    TABLE ACCESS FULL| CF11 |      1 |  12556 |  12556 |00:00:00.03 |    8204 |          |
|   4 |    TABLE ACCESS FULL| CF10 |      1 |    500K|    500K|00:00:00.02 |    4583 |          |
---------------------------------------------------------------------------------------------------
   2 - access("H"."SLE_NO"="S"."SLE_NO")
   3 - filter("S"."SLE_UPRC"=2000)

모범 답안 서술

단가는 11종인데 칸을 5개로 주자 FREQUENCY를 만들 수 없어서 오라클은 TOP-FREQUENCY를 만들었다. TOP-FREQUENCY는 많이 팔린 값을 칸에 넣되, 최솟값과 최댓값은 많든 적든 억지로 칸에 포함한다. 끝점을 보면 2,000원(최솟값)은 0 → 1, 12,000원(최댓값)은 962,437 → 962,438로 누적 차이가 1이다. 칸 자리만 차지하고 건수는 1건으로 기억된 것이다. 그래서 4번 줄의 E-Rows가 1이 됐다(실제 12,556). 중간의 4,000원이나 10,000원은 '나머지'로 묶여 (1,096,708 − 962,438) / (11 − 5) = 22,378로 비슷하게 맞는다. 1건이라고 믿은 옵티마이저는 NL을 골랐다. CF11을 읽으며 2,000원 줄마다 CF10_PK를 찾아가서 5번과 6번 줄의 Starts가 12,556이고, 인덱스와 테이블에서만 37,668블록을 읽었다. 판매 테이블을 한 번 읽는 해시 조인이면 12,787블록이다. 처방은 칸을 값의 종류 이상으로 주는 것이다. SIZE 254(값이 11종이므로 실제로는 11칸)면 FREQUENCY가 되어 값마다 정확한 건수를 기억한다. 선호도로 걸면 밤의 자동 수집도 같은 규칙으로 모은다. 그러면 2,000원의 E-Rows가 12,556이 되고 해시 조인으로 돌아가 Buffers 45,872 → 12,787이 된다. 12,000원도 61,6xx → 약 13,000으로 돌아온다. 칸을 줄여서 얻은 것은 거의 없다. 값이 11종이면 FREQUENCY든 TOP-FREQUENCY든 테이블을 한 번 읽을 때 값별 건수를 함께 세므로 수집 시간은 거의 줄지 않는다. 줄인 것은 딕셔너리에 적히는 끝점 여섯 줄뿐이고, 그 대가로 가장 싼 값과 가장 비싼 값의 추정을 잃었다.

해설

함정: TOP-FREQUENCY는 '많이 팔리는 값은 정확하다'는 점에서 그럴듯하다. 그러나 최솟값과 최댓값을 억지로 넣는 규칙 때문에 양 끝 값이 1로 떨어진다. 화면에서 양 끝 값을 자주 고른다면 가장 나쁜 선택이다(교재 10화 §4 실측 ②).
칸이 3개라면 최솟값과 최댓값, 상위 1종을 합쳐도 문턱(1 − 1/3)에 못 미쳐 HYBRID가 된다. 그때는 표본으로 만들어 수집할 때마다 숫자가 조금씩 바뀐다. 값의 종류보다 칸이 적으면 어느 쪽이든 추정을 잃는다.
단가를 바인드 변수로 받는 화면이 생기면 첫 값을 엿보고 계획이 굳을 수 있다(바인드 피킹). 이 화면은 리터럴이라 값마다 맞는 계획을 탄다.
히스토그램 설정을 바꾸면 SLE_UPRC 조건을 쓰는 다른 SQL의 계획도 바뀔 수 있다. 바꾸기 전후로 그 열을 쓰는 SQL의 계획 해시를 비교해 둔다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 단가 분포와 TOP-FREQUENCY 끝점은 교재 10화 복사본 측정 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 단가 분포와 TOP-FREQUENCY 끝점은 교재 10화 복사본 측정 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황