판매 일보가 작년 상반기 날짜는 금방 뜨는데 하반기 날짜는 80배 블록을 읽는다

기초UPDATE유통473 / 700

상황

본사 레거시 유통은 2023년 7월부터 판매를 ERP로 넘긴다. POS가 판매를 넣을 때 ERP_ST는 'N', ERP_MSG는 비어 있고, 매일 02:00 연동 배치가 ERP 응답을 받아 두 칸을 채운다. ERP_MSG에는 전표 번호와 승인 문구가 약 120바이트 들어간다. 1월 첫 주 MD팀: "「일별 판매 일보」가 작년 상반기 날짜는 금방 뜨는데 하반기 날짜는 느려요." 통계는 매일 DBMS_STATS로 모으고, USER_TABLES.CHAIN_CNT는 0이다.

테이블과 인덱스

CF10 판매: 500,000행 · SLE_DT '20220101' ~ '20231231' · 하루 약 700건 · 날짜순으로 들어온다. 이 문제에서는 ERP_ST VARCHAR2(1)와 ERP_MSG VARCHAR2(200) 칸이 더 있고, 2023-07-01 이후 판매 128,640건에 두 칸이 채워졌다. PCTFREE는 기본값 10이다.
행 길이는 연동 전 약 72바이트, ERP_MSG를 채운 뒤 약 192바이트다.
인덱스: CF10_PK (SLE_NO) · IX_CF10_SLE_DT (SLE_DT)
USER_TABLES(어젯밤 DBMS_STATS): NUM_ROWS 500,000 · BLOCKS 6,214 · AVG_ROW_LEN 103 · CHAIN_CNT 0. 연동 칸이 없던 때의 판매는 4,583블록이었다.
ERP 응답은 ERP_RCV (SLE_NO, ERP_MSG)로 받는다. 일요일 03:00 ~ 06:00에는 POS 입력이 없다.

원본 SQL

sql

-- 매일 02:00 · ERP 연동 결과 반영
UPDATE CF10 s
   SET (s.ERP_ST, s.ERP_MSG) = (SELECT 'Y', r.ERP_MSG FROM ERP_RCV r WHERE r.SLE_NO = s.SLE_NO)
 WHERE s.ERP_ST = 'N'
   AND EXISTS (SELECT 1 FROM ERP_RCV r WHERE r.SLE_NO = s.SLE_NO);

-- 「일별 판매 일보」
SELECT SHP_NO, COUNT(*) AS SLE_CNT, COUNT(CASE WHEN ERP_ST = 'Y' THEN 1 END) AS ERP_CNT
  FROM CF10
 WHERE SLE_DT = :v_dt
 GROUP BY SHP_NO;

실행계획 학습용 예시 · 행 이전의 비율은 교재 05화 실측과 같은 모양

xplan

[일별 판매 일보 — :v_dt = '20231115']
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |      6 |00:00:00.02 |     846 |
|   1 |  HASH GROUP BY                       |                |      1 |      6 |      6 |00:00:00.02 |     846 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |    700 |    698 |00:00:00.02 |     846 |
|*  3 |    INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |    700 |    698 |00:00:00.01 |       4 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("SLE_DT"=:V_DT)

[같은 SQL — :v_dt = '20230615']
  같은 계획 · A-Rows 702 · Buffers 11 (인덱스 4)

[V$MYSTAT 증가분 — 한 번 실행할 때]
  table fetch continued row     '20230615' → 0 · '20231115' → 421

[월 판매 집계 — 같은 테이블 전체 스캔]
  TABLE ACCESS FULL CF10 · Buffers 6,218 (연동 칸이 없던 때 4,583)

[시험 DB 사본 — ANALYZE TABLE CF10 LIST CHAINED ROWS INTO CHAINED_ROWS]
  77,912행 · 전부 SLE_DT >= '20230701'

문제

같은 SQL이 2023년 6월 날짜는 11블록, 11월 날짜는 846블록을 읽는 이유를 설명하시오. CHAIN_CNT가 0인데도 그렇게 판단하는 근거를 쓰시오.
이미 생긴 문제를 고치고, 앞으로 다시 생기지 않도록 연동 배치를 고치시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 앞으로: 연동 결과는 판매 행을 키우지 않는 별도 테이블에 쌓는다
--    (ERP_ST 는 'N' → 'Y' 로 길이가 같아 행이 커지지 않으므로 CF10 에 그대로 둔다)
CREATE TABLE CF10_ERP (
  SLE_NO  VARCHAR2(15)  NOT NULL,
  ERP_MSG VARCHAR2(200),
  ERP_DT  DATE,
  CONSTRAINT CF10_ERP_PK PRIMARY KEY (SLE_NO)
);

-- 매일 02:00
INSERT INTO CF10_ERP (SLE_NO, ERP_MSG, ERP_DT)
SELECT r.SLE_NO, r.ERP_MSG, SYSDATE FROM ERP_RCV r;

UPDATE CF10 s
   SET s.ERP_ST = 'Y'
 WHERE s.ERP_ST = 'N'
   AND EXISTS (SELECT 1 FROM ERP_RCV r WHERE r.SLE_NO = s.SLE_NO);

-- ② 이미 옮겨 간 행: 지난 메시지를 CF10_ERP 로 옮긴 뒤, 일요일 03:00 작업 창에서 판매 행을 다시 펴 쓴다
INSERT INTO CF10_ERP (SLE_NO, ERP_MSG, ERP_DT)
SELECT SLE_NO, ERP_MSG, NULL FROM CF10 WHERE ERP_MSG IS NOT NULL;
COMMIT;

ALTER TABLE CF10 MOVE;                   -- 행을 새 세그먼트에 다시 쓴다 → 옮겨 간 행 0
ALTER INDEX CF10_PK REBUILD;             -- MOVE 뒤 인덱스는 UNUSABLE 이 된다
ALTER INDEX IX_CF10_SLE_DT REBUILD;
-- (CF10.ERP_MSG 칸은 화면이 CF10_ERP 로 옮겨 간 뒤 정기 점검 때 지운다)

개선 후 실행계획 학습용 예시 · 행 이전의 비율은 교재 05화 실측과 같은 모양

xplan

[MOVE · REBUILD 뒤 — 일별 판매 일보 :v_dt = '20231115']
  TABLE ACCESS BY INDEX ROWID BATCHED · A-Rows 698 · Buffers 846 → 23
  table fetch continued row 421 → 0
[ANALYZE] CHAIN_CNT 77,912 → 0 · BLOCKS 6,214 → 6,050
[연동을 바꾼 뒤 한 달] 새 판매의 행 길이 약 73바이트 그대로 · CHAIN_CNT 증가 0

모범 답안 서술

하반기 판매에서 행 이전(row migration)이 생겼다. 판매는 72바이트로 들어와 PCTFREE 10의 블록을 90%까지 채운다. 그런데 02:00 연동 배치가 ERP_MSG 약 120바이트를 채우자 행이 192바이트로 커졌고, 블록에 남겨 둔 빈자리로는 그 블록의 행들이 커지는 몫을 감당하지 못했다. 자리가 없는 행은 몸통을 다른 블록으로 옮기고 원래 자리에는 다음 주소만 남긴다. ROWID는 바뀌지 않으므로 인덱스는 여전히 원래 자리를 가리킨다. 그래서 ROWID로 찾아가는 인덱스 경유 조회만 손해를 본다. 11월 15일 698행 중 421행은 원래 블록에서 주소를 읽고 다른 블록으로 한 번 더 가야 했다(table fetch continued row 421). 날짜순으로 모여 있던 블록 7개를 읽으면 끝날 일이 846블록이 됐다. 6월 날짜는 연동 전 판매라 11블록 그대로다. 전체 스캔은 어차피 모든 블록을 읽으므로 행 이전이 거의 드러나지 않고, 월 집계가 4,583 → 6,218블록으로 커진 것은 행이 길어진 몫이다. CHAIN_CNT가 0인 것은 근거가 되지 못한다. 이 칸은 ANALYZE로만 채워지고 DBMS_STATS는 채우지 않는다. 시험 DB 사본에서 ANALYZE … LIST CHAINED ROWS로 세어 보니 77,912행이 모두 2023-07-01 이후 판매였고, continued row 증가분과도 맞는다. 다시 생기지 않게 하려면 행이 커지지 않게 해야 한다. 연동 결과 메시지는 판매 행에 덧붙이지 말고 CF10_ERP에 새 행으로 쌓는다. ERP_ST는 'N'에서 'Y'로 길이가 같아 행을 키우지 않으므로 CF10에 둔다. PCTFREE를 올리는 방법도 있지만 72 → 192바이트를 받으려면 60%가 넘는 빈자리가 필요하고, 그만큼 테이블이 처음부터 뚱뚱해진다. 이미 옮겨 간 행은 POS 입력이 없는 일요일 새벽에 지난 메시지를 CF10_ERP로 옮긴 뒤 ALTER TABLE CF10 MOVE로 행을 새 세그먼트에 다시 쓴다. MOVE 뒤에는 인덱스가 UNUSABLE이 되므로 두 인덱스를 REBUILD한다. 일보는 846 → 약 23블록으로 돌아간다.

해설

함정: DBMS_STATS만 보고 "CHAIN_CNT 0이니 행 이전은 없다"고 결론 내리기 쉽다. 행 이전은 ANALYZE … LIST CHAINED ROWS나 세션 통계 table fetch continued row로 확인한다(교재 05화 §6).
MOVE는 지금 놓인 순서대로 다시 쓴다. 옮겨 간 행이 많으면 날짜순이 흐트러져 클러스터링 팩터가 나빠질 수 있다. 순서까지 되살리려면 ORDER BY SLE_DT로 새 테이블을 만들어 바꿔 끼운다(교재 05화 §4 MOVE가 안 고치는 것).
교재 05화의 실측(72 → 270바이트, PCTFREE 10이면 73.5%가 이사, 인덱스 경유 735 → 30,549)과 같은 모양이다. 이 문제의 192바이트도 PCTFREE 10으로는 턱없이 모자란다.
ALTER TABLE … MOVE ONLINE(12.2 이상)은 인덱스를 살린 채 옮겨 POS를 멈추지 않아도 되지만, 작업 시간과 공간이 더 든다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 행 이전의 비율은 교재 05화 실측과 같은 모양 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 행 이전의 비율은 교재 05화 실측과 같은 모양 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황