매달 1일 아침에만 월마감 등급별 매출 보고서가 느리다

중급DDL한빛유통603 / 700

상황

한빛유통은 6월부터 월마감 주문을 월별 파티션 테이블 T_ORD_CLS에 싣는다. 매달 25일 「칸 추가」 작업이 다음 달 칸을 미리 만들고, 1일 01:00 월마감 배치가 지난달 주문을 그 달 칸에 일반 INSERT로 싣는다. 영업기획팀은 1일 09:00부터 「월마감 등급별 매출」 보고서를 수십 번 연다. 박팀장: "1일에만 느리고 2일부터는 괜찮답니다. 7월 1일 오늘도 그래요."

테이블과 인덱스

T_ORD_CLS: T_ORD_BIG와 같은 열 · 주문일(ORD_DT) 월별 Range 파티션(14화 T_ORD_BIG_P14와 같은 설계, PMAX 없음) · 2022년 1월부터 칸이 있다(6월 칸이 30번째) · 칸마다 약 27,000 ~ 28,000행, 약 160블록 · 인덱스 없음
6월 칸 P202406: 5월 25일 생성 · 7월 1일 01:00에 27,390행 적재
P202406 통계: NUM_ROWS 0 · BLOCKS 0 · 마지막 수집 5월 25일 22:14(자동 수집)
5월 칸 P202405: 28,302행 · 165블록 · 통계 6월 1일 22:09
M_CUS: 2,000행 · 30블록 · PK_M_CUS
자동 통계 수집: 평일 22:00부터 4시간 · 통계가 없거나 STALE인 칸을 모은다

원본 SQL

sql

-- 매달 25일 · 칸 추가 (다음 달 칸을 미리 만든다)
ALTER TABLE T_ORD_CLS ADD PARTITION P202406 VALUES LESS THAN (DATE '2024-07-01');

-- 매달 1일 01:00 · 월마감 적재 (일반 INSERT)
INSERT INTO T_ORD_CLS (ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT)
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG
 WHERE ORD_DT >= DATE '2024-06-01' AND ORD_DT < DATE '2024-07-01';
COMMIT;

-- 1일 09:00부터 · 「월마감 등급별 매출」
SELECT c.CUS_GD, COUNT(*) AS ord_cnt, SUM(o.ORD_AMT) AS amt
  FROM T_ORD_CLS o, M_CUS c
 WHERE o.ORD_DT >= DATE '2024-06-01' AND o.ORD_DT < DATE '2024-07-01'
   AND c.CUS_ID = o.CUS_ID
 GROUP BY c.CUS_GD

실행계획 학습용 예시

xplan

[7월 1일 09:10 · 보고서]
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |           |      1 |        |      3 |00:00:00.09 |   28155 |       |       |
|   1 |  HASH GROUP BY                |           |      1 |      1 |      3 |00:00:00.09 |   28155 |       |       |
|   2 |   NESTED LOOPS                |           |      1 |      1 |  27390 |00:00:00.08 |   28155 |       |       |
|   3 |    NESTED LOOPS               |           |      1 |      1 |  27390 |00:00:00.04 |     765 |       |       |
|   4 |     PARTITION RANGE SINGLE    |           |      1 |      1 |  27390 |00:00:00.01 |     163 |    30 |    30 |
|   5 |      TABLE ACCESS FULL        | T_ORD_CLS |      1 |      1 |  27390 |00:00:00.01 |     163 |    30 |    30 |
|*  6 |     INDEX UNIQUE SCAN         | PK_M_CUS  |  27390 |      1 |  27390 |00:00:00.02 |     602 |       |       |
|   7 |    TABLE ACCESS BY INDEX ROWID| M_CUS     |  27390 |      1 |  27390 |00:00:00.03 |   27390 |       |       |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access("C"."CUS_ID"="O"."CUS_ID")

[USER_TAB_PARTITIONS — T_ORD_CLS · 7월 1일 09:10]
 PARTITION_NAME   NUM_ROWS   BLOCKS   LAST_ANALYZED    실제 COUNT(*)
 P202405            28,302      165   06-01 22:09            28,302
 P202406                 0        0   05-25 22:14            27,390
 P202407                 0        0   06-25 22:11                 0

[7월 2일 09:10 · 같은 보고서]  HASH JOIN · Buffers 193 (7월 1일 22:07 자동 수집이 P202406 을 다시 모은 뒤)

문제

2번 줄이 NL이 된 이유를 4번과 5번 줄의 E-Rows와 칸 통계로 설명하시오. 2일부터는 괜찮아지는 까닭도 쓰시오.
칸을 만들 때와 적재한 뒤에 할 일을 SQL로 쓰고, 두 방법의 대가를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 칸을 만들 때 : 앞 달 칸의 통계를 복사해 둔다 (빈 칸에 0 이 적히기 전에 · ORD_DT 최솟값과 최댓값은 새 칸 범위로 바뀐다)
ALTER TABLE T_ORD_CLS ADD PARTITION P202407 VALUES LESS THAN (DATE '2024-08-01');
BEGIN
  DBMS_STATS.COPY_TABLE_STATS(ownname     => USER,
                              tabname     => 'T_ORD_CLS',
                              srcpartname => 'P202406',
                              dstpartname => 'P202407');
END;
/

-- ② 월마감 적재 직후 : 그 칸만 실제 행수로 다시 모은다 (보고서 커서도 바로 다시 파싱)
INSERT INTO T_ORD_CLS (ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT)
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG
 WHERE ORD_DT >= DATE '2024-06-01' AND ORD_DT < DATE '2024-07-01';
COMMIT;
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname       => USER,
                                tabname       => 'T_ORD_CLS',
                                partname      => 'P202406',
                                granularity   => 'PARTITION',
                                no_invalidate => FALSE);
END;
/
-- ③ 오늘 (7월 1일) 아침 응급 : ② 의 수집만 지금 돌린다 (1초 남짓)

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

xplan

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |      1 |        |      3 |00:00:00.02 |     193 |       |       |
|   1 |  HASH GROUP BY           |           |      1 |      3 |      3 |00:00:00.02 |     193 |       |       |
|*  2 |   HASH JOIN              |           |      1 |  27390 |  27390 |00:00:00.01 |     193 |       |       |
|   3 |    TABLE ACCESS FULL     | M_CUS     |      1 |   2000 |   2000 |00:00:00.01 |      30 |       |       |
|   4 |    PARTITION RANGE SINGLE|           |      1 |  27390 |  27390 |00:00:00.01 |     163 |    30 |    30 |
|   5 |     TABLE ACCESS FULL    | T_ORD_CLS |      1 |  27390 |  27390 |00:00:00.01 |     163 |    30 |    30 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C"."CUS_ID"="O"."CUS_ID")

모범 답안 서술

보고서는 6월만 묻기 때문에 칸 하나(30번)만 읽는다(PARTITION RANGE SINGLE). 칸 하나만 읽을 때 옵티마이저는 그 칸의 통계로 셈한다. 6월 칸은 5월 25일에 만들어져 7월 1일 01:00까지 비어 있었고, 통계가 없는 칸을 자동 수집이 그날 밤 0행으로 모았다. 01:00에 27,390행을 일반 INSERT로 실어도 통계는 그대로라 5번 줄 E-Rows가 1이다. 한 행이라면 고객을 한 명씩 찾아가는 NL이 싸므로 2번 줄이 NL이 되었고, 6번과 7번 줄을 27,390번 돌아 Buffers가 28,155다. 2일부터 괜찮아지는 까닭은 자동 수집이다. 27,390행이 들어와 STALE이 된 칸을 1일 22:07 자동 수집이 다시 모았다. 그 뒤로는 해시 조인으로 193블록을 읽는다. 결국 1일 01:00부터 22:00까지, 보고서를 가장 많이 여는 하루 동안만 틀린 통계가 쓰인다. 칸을 만들 때 앞 달 칸의 통계를 COPY_TABLE_STATS로 복사해 둔다. 새 칸은 처음부터 27,000행 안팎으로 보이고, 복사할 때 파티션 키 ORD_DT의 최솟값과 최댓값은 새 칸의 범위로 바뀐다. 통계가 있고 바뀐 행이 없으니 자동 수집이 빈 칸을 0으로 덮어쓰지도 않는다. 그리고 월마감 적재 직후에 그 칸만 granularity => 'PARTITION'으로 다시 모아 실제 행수로 맞추고 커서를 무효화한다. 그러면 7월 1일 아침에도 해시 조인으로 Buffers 28,155 → 193이다. 대가도 있다. 복사한 통계는 앞 달의 숫자라 크기가 크게 다른 달(명절이 낀 달)에는 대략의 값일 뿐이다. 그래서 적재 직후 수집을 함께 둔다. 칸 단위로만 모으면 여러 칸을 함께 읽는 SQL이 쓰는 전역 통계는 맞춰지지 않는다. 전역 통계는 INCREMENTAL 같은 방법으로 따로 챙긴다(OPT-014).

해설

'빈 칸 + 자동 수집'은 빈 테이블 통계의 파티션판이다. 칸을 미리 만드는 운영이면 칸이 비어 있는 동안 자동 수집 창이 반드시 한 번은 열린다.
적재 직후 수집만으로도 이 보고서는 고쳐진다. 다만 적재와 보고서 사이에 다른 칸을 읽는 SQL이 있거나, 적재가 실패해 다시 도는 날에는 복사해 둔 통계가 안전판이 된다.
칸 단위 교환(EXCHANGE PARTITION)으로 싣는다면 통계를 미리 모은 스테이징 테이블의 통계가 칸으로 함께 들어온다. 스테이징을 직접 경로로 지으면 온라인 통계가 그 역할을 한다(14화).
통계를 잠가 0을 막는 안은 칸이 다 차고 난 뒤에도 0을 굳힌다. 칸마다 크기가 달라지는 테이블에는 맞지 않는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황