칸 단위로만 통계를 모은 뒤 버거 데이 매출 비교가 주문을 통째로 읽는다

실전PLSQL버거604 / 700

상황

버거 본사는 7월에 POS 원장을 월별 파티션 테이블 ORD_RANGE로 옮겼다. 빈 테이블로 만들고 칸마다 원장을 날짜순으로 옮겨 실은 뒤, 업체 가이드("파티션 테이블은 바뀐 칸만 모은다")대로 칸 단위로만 통계를 모았다. 지금도 야간 통계 잡은 적재가 있었던 이번 달 칸만 granularity => 'PARTITION'으로 모은다. 마케팅팀의 「버거 데이 매출 비교」(매달 둘째 화요일, 5월부터 8번)가 22,000블록 넘게 읽는다. 오과장은 이렇게 말한다. "테이블 전체를 다시 모으면 밤마다 24칸을 다 읽어요. 칸이 늘수록 더 길어지고요." 박팀장: "어느 방식으로 갈지 근거를 붙여서."

테이블과 인덱스

ORD_RANGE: ORD와 같은 열 + ORD_YM VARCHAR2(6)(파티션 키) · 1,000,000행 · 월별 24칸(P201201 ~ P201312) · 칸마다 약 41,700행, 약 935블록, 합계 약 22,440블록 · 날짜순으로 실었다
로컬 인덱스 ORD_RANGE_X01 (ORD_DT, ORD_HMS) LOCAL · 칸마다 높이 2
하루 주문 약 1,370건 · 매장 200곳
칸 통계는 칸마다 정확하다(ORD_DT NDV 28 ~ 31). 전역 통계는 한 번도 따로 모으지 않았다: GLOBAL_STATS NO(칸 통계를 합친 값) · NUM_ROWS 1,000,000 · ORD_DT NUM_DISTINCT 31(실제 730)
선호도는 기본값(INCREMENTAL FALSE) · 통계 이력 31일

원본 SQL

sql

-- 야간 통계 잡 (업체 가이드 · 적재가 있었던 이번 달 칸만)
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname     => 'DEV',
                                tabname     => 'ORD_RANGE',
                                partname    => 'P' || TO_CHAR(SYSDATE, 'YYYYMM'),
                                granularity => 'PARTITION');
END;
/

-- 마케팅 「버거 데이 매출 비교」 (매달 둘째 화요일)
SELECT ORD_DT, SHOP_NO, COUNT(*) AS ord_cnt
  FROM ORD_RANGE
 WHERE ORD_DT IN ('20130514', '20130611', '20130709', '20130813',
                  '20130910', '20131008', '20131112', '20131210')
 GROUP BY ORD_DT, SHOP_NO

실행계획 학습용 예시

xplan

[버거 데이 매출 비교]
---------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |      1 |        |   1600 |00:00:00.31 |   22440 |       |       |
|   1 |  HASH GROUP BY       |           |      1 |   6200 |   1600 |00:00:00.31 |   22440 |       |       |
|   2 |   PARTITION RANGE ALL|           |      1 |    258K|  10960 |00:00:00.30 |   22440 |     1 |    24 |
|*  3 |    TABLE ACCESS FULL | ORD_RANGE |     24 |    258K|  10960 |00:00:00.29 |   22440 |     1 |    24 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(("ORD_DT"='20130514' OR "ORD_DT"='20130611' OR "ORD_DT"='20130709' OR
              "ORD_DT"='20130813' OR "ORD_DT"='20130910' OR "ORD_DT"='20131008' OR
              "ORD_DT"='20131112' OR "ORD_DT"='20131210'))

[USER_TAB_STATISTICS · USER_TAB_COL_STATISTICS — ORD_RANGE]
 수준        GLOBAL_STATS   NUM_ROWS    ORD_DT NUM_DISTINCT
 전역        NO             1,000,000   31          (칸 통계를 합친 값)
 P201306     YES               41,096   30
 P201312     YES               42,467   31

[야간 통계 잡 — 방식별 (복사본 · 학습용 예시)]
 방식                                          읽는 블록    걸린 시간   전역 ORD_DT NDV
 이번 달 칸만 (granularity PARTITION · 지금)      약 935       0.4초       31
 테이블 전체 (granularity AUTO)                 약 22,440      6.1초      730
 INCREMENTAL TRUE · 첫 수집                     약 22,440      7.3초      730   (칸마다 시놉시스를 남긴다)
 INCREMENTAL TRUE · 다음 날부터 (바뀐 칸 하나)     약 935       0.6초      730   (시놉시스를 합친다)

문제

2번 줄의 E-Rows 258K를 손으로 계산하고, 칸 통계가 모두 정확한데도 전역 추정이 틀린 이유를 쓰시오. 조건에 ORD_YM을 더하면 고쳐지는지도 쓰시오.
세 방식(이번 달 칸만, 테이블 전체, INCREMENTAL)을 읽는 양과 전역 통계의 정확도, 부작용으로 비교하시오.
고른 방식으로 통계 설정과 야간 잡을 고쳐 쓰시오. 앞으로 새 칸이 생길 때 할 일도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 전역 통계를 칸마다의 시놉시스로 합치게 한다 (한 번은 전체를 읽어 칸마다 시놉시스를 남긴다 · 7.3초)
BEGIN
  DBMS_STATS.SET_TABLE_PREFS('DEV', 'ORD_RANGE', 'INCREMENTAL', 'TRUE');
  -- ESTIMATE_PERCENT 는 기본값(AUTO_SAMPLE_SIZE) 그대로 둔다 : 비율을 박으면 시놉시스를 쓰지 못한다
  DBMS_STATS.GATHER_TABLE_STATS(ownname       => 'DEV',
                                tabname       => 'ORD_RANGE',
                                granularity   => 'AUTO',
                                no_invalidate => FALSE);
END;
/

-- ② 야간 잡 : 칸 이름도 granularity 도 주지 않는다 → 바뀐 칸만 다시 읽고 전역은 시놉시스로 합친다 (0.6초)
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname => 'DEV', tabname => 'ORD_RANGE');
END;
/

-- ③ 새 칸을 만들 때 : 앞 칸의 통계를 복사해 비어 있는 동안 0 으로 모이지 않게 하고, 적재가 시작되면 ② 가 그 칸을 다시 모은다
-- EXEC DBMS_STATS.COPY_TABLE_STATS('DEV', 'ORD_RANGE', srcpartname => 'P201312', dstpartname => 'P201401');

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

xplan

[INCREMENTAL 첫 수집 뒤 · 전역 ORD_DT NUM_DISTINCT 730]
--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |               |      1 |        |   1600 |00:00:00.02 |     662 |       |       |
|   1 |  HASH GROUP BY                               |               |      1 |   1600 |   1600 |00:00:00.02 |     662 |       |       |
|   2 |   PARTITION RANGE ALL                        |               |      1 |  10959 |  10960 |00:00:00.02 |     662 |     1 |    24 |
|   3 |    INLIST ITERATOR                           |               |     24 |        |  10960 |00:00:00.02 |     662 |       |       |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| ORD_RANGE     |    192 |  10959 |  10960 |00:00:00.01 |     662 |     1 |    24 |
|*  5 |      INDEX RANGE SCAN                        | ORD_RANGE_X01 |    192 |  10959 |  10960 |00:00:00.01 |     416 |     1 |    24 |
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access(("ORD_DT"='20130514' OR "ORD_DT"='20130611' OR "ORD_DT"='20130709' OR
              "ORD_DT"='20130813' OR "ORD_DT"='20130910' OR "ORD_DT"='20131008' OR
              "ORD_DT"='20131112' OR "ORD_DT"='20131210'))

모범 답안 서술

E-Rows: ORD_DT IN (…)은 파티션 키 조건이 아니므로 24칸을 모두 연다(PARTITION RANGE ALL). 칸을 여러 개 읽으면 옵티마이저는 칸 통계가 아니라 전역 통계로 셈한다. 전역 ORD_DT NDV가 31이므로 날짜 하나를 1,000,000 ÷ 31 = 32,258행으로 보고, 8개면 258K다. 실제는 하루 약 1,370건 × 8 = 10,960건이다. 25만 행을 인덱스로 찾느니 통째로 읽는 편이 싸다고 보아 FULL로 22,440블록을 읽었다. 왜 틀렸나: 칸 통계는 모두 정확하다. 그러나 칸마다의 NDV는 더할 수 없다. 6월 칸의 날짜 30개와 7월 칸의 날짜 31개가 겹치는지 오라클은 모르기 때문이다(이 테이블에서는 겹치지 않지만, 매장 번호처럼 칸마다 같은 값이 나오는 열도 있다). 전역 통계를 따로 모은 적이 없어 칸 통계를 합친 값(GLOBAL_STATS NO)이 쓰이고, NDV는 칸 하나의 크기인 31에 머물렀다. 조건에 ORD_YM IN (…)을 더하면 8칸만 열지만, 칸이 둘 이상이면 여전히 전역 통계로 셈하므로 E-Rows는 그대로다. 세 방식 비교: ① 이번 달 칸만 모으면 935블록으로 가볍지만 전역 NDV가 영영 맞지 않는다. ② 테이블 전체를 모으면 전역 NDV가 730으로 정확하지만, 칸 하나가 바뀌어도 밤마다 24칸(22,440블록)을 다 읽고 칸이 늘수록 길어진다. ③ INCREMENTAL은 첫 수집 때 한 번 전체를 읽어 칸마다 시놉시스(값의 지문)를 남기고, 그다음부터는 바뀐 칸만 다시 읽은 뒤 시놉시스를 합쳐 전역 NDV를 정확히 만든다(935블록, 0.6초). 대가는 시놉시스를 저장할 공간(SYSAUX)과, 첫 수집은 전체를 읽는다는 점, 옛 칸에 DML이 있으면 그 칸도 다시 읽는다는 점이다. 공식문서가 드는 조건(ESTIMATE_PERCENT는 AUTO_SAMPLE_SIZE, PUBLISH TRUE)도 지켜야 한다. 고른 것: ③이다. INCREMENTAL을 켜고 한 번 granularity => 'AUTO'로 모은 뒤, 야간 잡에서는 칸 이름과 granularity를 빼서 오라클이 바뀐 칸을 고르게 한다. 전역 NDV가 730이 되면 E-Rows는 10,959로 맞고, 로컬 인덱스로 칸마다 날짜 8개를 찾아 Buffers 22,440 → 662다. 새 칸이 생길 때는 앞 칸의 통계를 COPY_TABLE_STATS로 복사해 두어 비어 있는 동안 0으로 모이지 않게 하고, 적재가 시작되면 야간 잡이 그 칸을 다시 모으게 둔다.

해설

칸 하나만 읽는 SQL은 이 문제를 겪지 않는다. ORD_YM = '201306'처럼 한 칸으로 줄면 그 칸의 통계(정확)를 쓴다. 여러 칸에 걸친 분석 SQL이 전역 통계의 손님이다.
전역 통계를 손으로 넣는 안(SET_COLUMN_STATS로 NDV 730)은 칸이 늘 때마다 다시 넣어야 하고, 넣은 사람만 그 이유를 안다. 기각한다.
granularity => 'PARTITION'의 원래 쓸모는 전역 통계를 이미 정확히 관리하는 테이블에서 칸 하나만 급히 고칠 때다(OPT-013의 적재 직후 수집). 정기 잡의 기본값으로 쓰면 전역이 낡는다.
연습 DB에서는 전체 수집도 몇 초다. 운영에서 칸이 60개, 120개로 늘면 ②와 ③의 차이는 칸 수만큼 벌어진다. 결정에는 그때의 칸 수와 시간을 함께 적는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황