매일 밤 4만 6천 행만 다시 채우는 작업 테이블을 읽는 데 아직도 8천 블록이 든다

기초DELETE유통457 / 700

상황

본사 레거시 유통의 「매장 상품 일 판매 집계」 배치는 매일 01:00에 작업 테이블 CF11_WRK를 비우고 최근 30일 판매상품을 다시 채운 뒤 매장별 상품 집계를 뽑는다. 작업 테이블에 들어가는 행은 매일 4만 6천 행 안팎이다. 배치 담당 오과장: "지난달 말에 연간 재집계를 한 번 돌린 뒤로 집계 단계가 느려졌어요. 행 수는 그대로인데요." 통계는 매일 다시 모은다.

테이블과 인덱스

CF11_WRK 작업 테이블(문제 설정, CF10과 CF11을 조인한 사본): SLE_DT · SHP_NO · SLE_NO · ITM_NO · SLE_QTY · SLE_UPRC. 블록당 약 135행이 들어간다. 인덱스, 제약, 트리거는 없고 이 배치만 쓴다.
지난달 말(10월 31일) 연간 재집계 때 판매상품 약 110만 행을 이 테이블에 한 번 넣었다가 다음 날 지웠다.
CF10 판매: 500,000행 · 4,583블록 · SLE_DT VARCHAR2(8) '20220101' ~ '20231231' · 하루 약 700건 · IX_CF10_SLE_DT (SLE_DT)
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · 판매 한 건에 평균 2.2행
배치 계정이 CF11_WRK의 소유자다. 다른 테이블이 CF11_WRK를 참조하지 않는다.

원본 SQL

sql

-- 매일 01:00 · ① 작업 테이블 다시 채우기
DELETE FROM CF11_WRK;

INSERT INTO CF11_WRK (SLE_DT, SHP_NO, SLE_NO, ITM_NO, SLE_QTY, SLE_UPRC)
SELECT h.SLE_DT, h.SHP_NO, d.SLE_NO, d.ITM_NO, d.SLE_QTY, d.SLE_UPRC
  FROM CF10 h, CF11 d
 WHERE d.SLE_NO = h.SLE_NO
   AND h.SLE_DT BETWEEN :v_from AND :v_to;          -- 최근 30일
COMMIT;

-- ② 매장별 상품 집계
SELECT SHP_NO, ITM_NO, SUM(SLE_QTY) AS QTY, SUM(SLE_QTY * SLE_UPRC) AS AMT
  FROM CF11_WRK
 GROUP BY SHP_NO, ITM_NO;

실행계획 학습용 예시

xplan

[① DELETE FROM CF11_WRK — 오늘 밤 · 세션 통계]
  지운 행                     46,180
  redo size                   약 9.1 MB
  undo change vector size     약 5.7 MB
  db block changes            약 93,000
  경과                        1.4 초

[CF11_WRK — USER_TABLES (매일 수집)]
  시점                     NUM_ROWS     BLOCKS (고수위선 아래)
  10월 27일 (재집계 전)       45,902        342
  10월 31일 (재집계 직후)  1,096,708      8,236
  11월 29일 (오늘)            46,180      8,236

[② 매장별 상품 집계 — 오늘]
-------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |      1 |        |    535 |00:00:00.19 |    8240 |
|   1 |  HASH GROUP BY     |          |      1 |    535 |    535 |00:00:00.19 |    8240 |
|   2 |   TABLE ACCESS FULL| CF11_WRK |      1 |  46180 |  46180 |00:00:00.15 |    8240 |
-------------------------------------------------------------------------------------
  (10월 27일 같은 문장: Buffers 346)

문제

행 수는 그대로인데 ② 집계가 346블록에서 8,240블록으로 늘어난 이유를 설명하시오.
① 단계를 고치고, 고친 방법의 대가(쓸 수 없는 경우와 주의할 점)를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 비우기는 TRUNCATE — 고수위선을 처음으로 되돌리고, 행마다 undo · redo 를 남기지 않는다
TRUNCATE TABLE CF11_WRK;

-- 빈 테이블을 통째로 채우고 이 배치만 쓰는 테이블이므로 직접 경로로 넣는다
INSERT /*+ APPEND */ INTO CF11_WRK (SLE_DT, SHP_NO, SLE_NO, ITM_NO, SLE_QTY, SLE_UPRC)
SELECT h.SLE_DT, h.SHP_NO, d.SLE_NO, d.ITM_NO, d.SLE_QTY, d.SLE_UPRC
  FROM CF10 h, CF11 d
 WHERE d.SLE_NO = h.SLE_NO
   AND h.SLE_DT BETWEEN :v_from AND :v_to;
COMMIT;

-- ② 는 그대로
SELECT SHP_NO, ITM_NO, SUM(SLE_QTY) AS QTY, SUM(SLE_QTY * SLE_UPRC) AS AMT
  FROM CF11_WRK
 GROUP BY SHP_NO, ITM_NO;

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

xplan

[① TRUNCATE TABLE CF11_WRK]
  redo size 약 0.01 MB · undo 거의 0 · 경과 0.05 초 (딕셔너리와 공간 정보만 고친다)

[CF11_WRK — 적재 뒤]
  NUM_ROWS 46,180 · BLOCKS 342

[② 매장별 상품 집계]
-------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |      1 |        |    535 |00:00:00.02 |     346 |
|   1 |  HASH GROUP BY     |          |      1 |    535 |    535 |00:00:00.02 |     346 |
|   2 |   TABLE ACCESS FULL| CF11_WRK |      1 |  46180 |  46180 |00:00:00.01 |     346 |
-------------------------------------------------------------------------------------

모범 답안 서술

② 집계가 커진 것은 행 수가 아니라 고수위선 때문이다. 10월 31일 재집계 때 110만 행을 넣으면서 고수위선이 8,236블록까지 올라갔고, DELETE는 행만 지울 뿐 고수위선을 내리지 않는다. 그 뒤로 매일 4만 6천 행만 다시 넣어도 BLOCKS는 8,236 그대로이고, TABLE ACCESS FULL은 고수위선 아래 블록을 모두 읽으므로 Buffers가 346에서 8,240이 됐다. 게다가 DELETE는 행마다 지우기 전 모습을 undo에 적고 그 변경을 redo에 남긴다. 46,180행을 지우는 데 redo 약 9.1MB, undo 약 5.7MB가 매일 밤 나온다. 테이블의 모든 행을 버리는 일인데 행 하나하나를 기록하는 셈이다. ① 단계를 TRUNCATE TABLE CF11_WRK로 바꾼다. TRUNCATE는 행을 하나씩 지우지 않고 세그먼트를 비운 채 고수위선을 처음으로 되돌린다. redo는 약 0.01MB이고, 다시 채운 뒤 BLOCKS는 342, ② 집계는 8,240 → 346블록으로 준다. 빈 테이블을 통째로 채우고 이 배치만 쓰는 테이블이므로 /*+ APPEND */ 직접 경로 적재도 함께 쓸 수 있다. 대가도 있다. TRUNCATE는 DDL이라 실행하는 순간 암시적 커밋이 일어나고 되돌릴 수 없다. 같은 트랜잭션에서 앞서 고친 내용도 함께 커밋되므로 배치의 첫 단계에 둔다. 다른 테이블이 FK로 이 테이블을 참조하면 ORA-02266으로 막히고, 소유자가 아니면 DROP ANY TABLE 권한이 있어야 한다. 이 테이블은 배치 계정 소유이고 참조하는 테이블이 없으므로 쓸 수 있다.

해설

교재 05화와 같은 모양이다. T_ORD_LOG에서 95%를 지워도 전체 스캔은 3,602 → 3,602였다. 지운 행 수가 아니라 고수위선이 전체 스캔의 일량을 정한다.
기본값 TRUNCATE … DROP STORAGE는 첫 익스텐트만 남기고 공간을 돌려준다. REUSE STORAGE는 고수위선만 내리고 익스텐트를 붙들어 두는데, 한 번 커졌던 8,236블록을 다시 잡아 둘 이유가 없다.
다른 방법: ALTER TABLE … SHRINK SPACE나 MOVE로도 고수위선을 내릴 수 있지만 남은 행을 옮겨 쓰는 비용이 들고, 날마다 할 일은 아니다. 테이블을 날마다 DROP하고 CTAS로 다시 만들면 권한과 통계를 매번 챙겨야 하고, 이 테이블을 쓰는 PL/SQL이 INVALID가 된다.
연간 재집계처럼 크기가 전혀 다른 일은 같은 작업 테이블이 아니라 따로 만든 테이블에서 돌린다. 그래야 평소 배치의 작업 테이블이 그날의 크기를 떠안지 않는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황