고칠 수 없는 본사 판매 분석 패키지, SQL 셋의 추정이 모두 실제와 다르다

실전DDL유통614 / 700

상황

10화에서 본사에 보낸 「통계 수집 정책서」 후보에 유통지원팀이 회신했다. "분석 패키지는 업체 소스라 SQL을 고칠 수 없습니다. 통계 처방은 원본에 걸어도 좋습니다. 단, 무엇이 바뀌는지 목록과 되돌리는 방법을 함께 주세요." 패키지의 리포트들은 아래 세 SQL의 결과 위에서 조인 방법과 순서를 정한다. 선배는 셋의 E-Rows를 손으로 맞혀 보고 처방을 한 묶음으로 고르라고 했다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · SLE_DT VARCHAR2(8) · SHP_NO 매장 5곳(1001 ~ 1005, 곳마다 약 10만 건) · REG_EMP_NO 등록 직원(schemas 표에는 없는 열이다. 매장마다 직원 한 명이 등록하고, 직원 번호가 매장 번호와 같다) · 2023년 6월 판매 20,953건
CF11 판매상품: 1,096,708행 · 8,204블록 · ITM_NO 88종(1001이 275,135줄로 가장 많고, 가장 적은 상품은 3,032줄) · SLE_UPRC 단가 11종 · 상품마다 단가가 하나다(1001의 단가는 2,500원, 2,500원 판매는 모두 533,248줄)
인덱스: CF10_PK (SLE_NO) · CF11_PK (SLE_NO, ITM_NO) · IX_CF10_SLE_DT (SLE_DT)
통계: CF10 선호도 FOR ALL COLUMNS SIZE 1 · CF11 선호도 FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 SLE_UPRC(SLE_UPRC만 FREQUENCY 히스토그램) · 확장 통계 없음

원본 SQL

sql

-- (가) 매장 · 등록 직원별 판매 (점장 화면)
SELECT COUNT(*), MIN(SLE_DT), MAX(SLE_DT)
  FROM CF10
 WHERE SHP_NO = 1003
   AND REG_EMP_NO = 1003;

-- (나) 상품 · 단가별 판매 (MD 화면)
SELECT SUM(SLE_QTY), SUM(SLE_QTY * SLE_UPRC)
  FROM CF11
 WHERE ITM_NO = 1001
   AND SLE_UPRC = 2500;

-- (다) 월 판매 (BI 차트 · 판매일자를 날짜로 바꿔 묻는다)
SELECT SHP_NO, COUNT(*)
  FROM CF10
 WHERE TO_DATE(SLE_DT, 'YYYYMMDD') >= DATE '2023-06-01'
   AND TO_DATE(SLE_DT, 'YYYYMMDD') <  DATE '2023-07-01'
 GROUP BY SHP_NO;

실행계획 학습용 예시 · (가)와 (다)의 추정은 교재 10화 복사본 측정

xplan

[(가)]
------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.05 |    4583 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.05 |    4583 |
|*  2 |   TABLE ACCESS FULL| CF10 |      1 |  20000 |   100K |00:00:00.04 |    4583 |
------------------------------------------------------------------------------------
   2 - filter(("SHP_NO"=1003 AND "REG_EMP_NO"=1003))

[(나)]
------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.08 |    8204 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.08 |    8204 |
|*  2 |   TABLE ACCESS FULL| CF11 |      1 |   6060 |   275K |00:00:00.07 |    8204 |
------------------------------------------------------------------------------------
   2 - filter(("ITM_NO"=1001 AND "SLE_UPRC"=2500))

[(다)]
------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      5 |00:00:00.09 |    4583 |
|   1 |  HASH GROUP BY     |      |      1 |      5 |      5 |00:00:00.09 |    4583 |
|*  2 |   TABLE ACCESS FULL| CF10 |      1 |   1250 |  20953 |00:00:00.08 |    4583 |
------------------------------------------------------------------------------------
   2 - filter((TO_DATE("SLE_DT",'YYYYMMDD')>=TO_DATE(' 2023-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              TO_DATE("SLE_DT",'YYYYMMDD')<TO_DATE(' 2023-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

정확한 A-Rows : (가) 100,167 · (나) 275,135 · (다) 20,953

문제

세 SQL의 E-Rows(20,000 · 6,060 · 1,250)를 손으로 계산하고, 옵티마이저가 각각 무엇을 잘못 가정했는지 쓰시오.
SQL을 고치지 않는다는 조건에서 세 SQL을 바로잡는 통계 처방을 DDL로 쓰시오. (나)에서 처방을 만들어도 쓰이지 않는 함정도 쓰시오.
처방이 원본 테이블과 다른 SQL에 주는 영향과, 반영 전에 확인할 것과 되돌리는 방법을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- (가) 매장과 등록 직원은 한 쌍 → 컬럼 그룹 (두 열 모두 히스토그램이 없으므로 그룹도 SIZE 1 로 쓰인다)
SELECT DBMS_STATS.CREATE_EXTENDED_STATS('PUP', 'CF10', '(SHP_NO, REG_EMP_NO)') FROM dual;

-- (다) BI 가 묻는 식 그대로 표현식 통계 (추정만 고친다 · 인덱스는 여전히 못 쓴다)
SELECT DBMS_STATS.CREATE_EXTENDED_STATS('PUP', 'CF10', '(TO_DATE(SLE_DT, ''YYYYMMDD''))') FROM dual;

-- (나) 상품이 단가를 정한다 → 컬럼 그룹 + 그룹 히스토그램
--      SLE_UPRC 에 히스토그램이 있으므로 그룹에도 있어야 그룹을 쓴다 (조합 88개 → FREQUENCY)
SELECT DBMS_STATS.CREATE_EXTENDED_STATS('PUP', 'CF11', '(ITM_NO, SLE_UPRC)') FROM dual;

BEGIN
  DBMS_STATS.SET_TABLE_PREFS('PUP', 'CF11', 'METHOD_OPT',
    'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 SLE_UPRC FOR COLUMNS SIZE 254 (ITM_NO, SLE_UPRC)');
  DBMS_STATS.GATHER_TABLE_STATS('PUP', 'CF10', no_invalidate => FALSE);   -- 만든 뒤 반드시 다시 모은다
  DBMS_STATS.GATHER_TABLE_STATS('PUP', 'CF11', no_invalidate => FALSE);
END;
/

-- 되돌리기 (반영 기록과 함께 정책서에 적는다)
--   EXEC DBMS_STATS.DROP_EXTENDED_STATS('PUP', 'CF10', '(SHP_NO, REG_EMP_NO)');
--   EXEC DBMS_STATS.DROP_EXTENDED_STATS('PUP', 'CF10', '(TO_DATE(SLE_DT, ''YYYYMMDD''))');
--   EXEC DBMS_STATS.DROP_EXTENDED_STATS('PUP', 'CF11', '(ITM_NO, SLE_UPRC)');
--   + CF11 선호도를 원래대로 · 이력에서 통계 되돌리기(RESTORE_TABLE_STATS)

개선 후 실행계획 학습용 예시 · (가)와 (다)의 추정은 교재 10화 복사본 측정

xplan

[처방 뒤 · 복사본 · E-Rows]
 (가) 컬럼 그룹 (SHP_NO, REG_EMP_NO)                       20,000 → 100,000    (실제 100,167)
 (나) 컬럼 그룹만 (그룹 히스토그램 없음)                    6,060 → 6,060      ← SLE_UPRC 히스토그램 때문에 그룹을 쓰지 않는다
      + 그룹 FREQUENCY (SIZE 254 · 조합 88개)               6,060 → 275,135    (실제 275,135)
 (다) 표현식 통계 TO_DATE(SLE_DT,'YYYYMMDD')                 1,250 → 21,261     (실제 20,953)

[USER_STAT_EXTENSIONS]
 TABLE_NAME  EXTENSION_NAME                    EXTENSION
 CF10        SYS_STU…                          ("SHP_NO","REG_EMP_NO")
 CF10        SYS_STU…                          (TO_DATE("SLE_DT",'YYYYMMDD'))
 CF11        SYS_STU…                          ("ITM_NO","SLE_UPRC")

모범 답안 서술

손계산과 잘못된 가정: (가)는 500,000 × 1/5(매장) × 1/5(직원) = 20,000이다. 그러나 매장마다 등록 직원이 한 명이라 (매장, 직원) 쌍은 5개뿐이고, 매장 조건 하나의 몫인 100,167이 실제다. (나)는 1,096,708 × 1/88(상품) × 533,248/1,096,708(2,500원의 비율, 히스토그램) = 6,060이다. 상품마다 단가가 하나라 1001을 고르면 단가는 저절로 2,500원인데, 옵티마이저는 두 조건을 독립으로 보고 곱했다. 실제는 1001의 275,135줄이다. (다)는 SLE_DT에 함수를 씌운 범위 조건 둘이라 통계를 쓸 수 없어 고정 선택도 5% × 5% = 0.25%, 500,000 × 0.25% = 1,250으로 봤다(실제 20,953). 처방: SQL을 고칠 수 없으므로 열과 식에 대한 통계를 더한다. (가)는 (SHP_NO, REG_EMP_NO) 컬럼 그룹을 만들면 쌍의 수(5)로 나눠 100,000이 된다. 두 열 모두 히스토그램이 없어 그룹도 히스토그램 없이 쓰인다. (다)는 BI가 묻는 식 그대로 TO_DATE(SLE_DT, 'YYYYMMDD') 표현식 통계를 만들면 숨은 가상 열의 통계로 계산해 21,261이 된다. (나)는 (ITM_NO, SLE_UPRC) 컬럼 그룹을 만든다. (나)의 함정: 그룹만 만들고 다시 모으면 E-Rows는 여전히 6,060이다. 그룹을 이루는 SLE_UPRC에 히스토그램이 있는데 그룹에는 없으면 오라클은 그룹을 쓰지 않는다(교재 10화 §8 실측 규칙). 그래서 선호도에 그룹 히스토그램(SIZE 254, 조합 88개라 FREQUENCY)을 넣어 다시 모은다. 그러면 1001의 275,135를 그대로 안다. 상품 쏠림까지 잡히는 것은 그룹 FREQUENCY가 조합마다 건수를 기억하기 때문이다. 영향과 확인, 되돌리기: 확장 통계는 테이블에 숨은 가상 열을 하나씩 만드는 DDL이고, 만든 뒤 다시 모아야 쓰인다. 원본의 통계가 바뀌므로 이 열들을 쓰는 다른 SQL(패키지의 리포트 전부)의 계획이 바뀔 수 있다. 반영 전에 복사본에서 핵심 리포트의 계획 해시와 Buffers를 전후로 비교하고, 바뀐 것만 목록으로 유통지원팀에 보낸다. 표현식 통계는 식이 글자까지 같은 SQL에만 쓰인다. BI 관리자가 계산 필드의 형식을 바꾸면 다시 1,250으로 돌아가므로 그 사실도 목록에 적는다. 되돌릴 때는 DROP_EXTENDED_STATS 세 줄과 선호도 원복, 필요하면 RESTORE_TABLE_STATS로 반영 전 통계를 되살린다.

해설

없는 조합은 모른다. 히스토그램 없는 컬럼 그룹은 "이 조합이 없다"는 사실을 모르고 조합 수로 나눌 뿐이다(10화: 매장 1003 · 직원 1004처럼 없는 쌍도 100,000). 화면이 없는 쌍을 자주 묻는다면 그룹에도 히스토그램을 준다.
(다)는 SQL을 고칠 수 있으면 SQL이 먼저다. 표현식 통계는 추정만 고치고 인덱스는 여전히 못 쓴다(FULL 4,583). BI의 필터를 날짜 범위로 바꾸고 가상 열에 인덱스를 두는 길은 IDX-018에 있다.
히스토그램만 켜는 안은 틀린다. (나)에서 ITM_NO에 히스토그램을 더하면 1/88 대신 1001의 비율 25%를 쓰지만, 단가 비율과 다시 곱해 133,777로 여전히 반만 맞는다. 열 하나의 분포로는 열 사이의 관계를 알 수 없다.
컬럼 그룹은 한 테이블의 열끼리만 묶는다. 판매(CF10)의 매장과 판매상품(CF11)의 상품처럼 조인 너머의 관계는 알려 줄 수 없다(OPT-032).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · (가)와 (다)의 추정은 교재 10화 복사본 측정 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · (가)와 (다)의 추정은 교재 10화 복사본 측정 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황