고객센터 최근 배송 목록 2,000건이 3천 블록을 읽는데, 테이블은 기본값으로 만들었다고 한다

중급DDL이커머스476 / 700

이 문제는 교재 18화 졸업 과제와 같은 이커머스 데이터베이스를 씁니다. 『튜너의 1년』을 순서대로 읽고 있다면 졸업 과제를 먼저 풀고 오세요. 아래 「테이블과 인덱스」 칸에 과제의 답 일부(제약 조건, 인덱스, 통계의 실제 상태)가 나옵니다.

상황

이커머스의 배송(shipping) 행은 주문할 때 한 줄로 생기고, 배송이 진행되면서 같은 줄이 채워진다. 출고 때 송장 번호와 택배사, 출고 시각이 들어가고, 배송 완료 때 완료 시각과 수령 메모가 들어간다. 배송은 보통 사흘 안에 끝난다. 고객센터 「최근 배송 목록」(최근 주문 2,000건)이 0.3초를 넘는다는 개선 요청이 왔다. 테이블은 개발 초기에 PCTFREE를 적지 않고(기본값 10) 만들었다.

테이블과 인덱스

shipping(문제 설정, 운영과 같은 순서로 쌓인 사본): 34,908행 · order_id와 1:1 · shipping_pk (order_id)뿐 · 이 문제에서는 tracking_no VARCHAR2(20), courier_cd VARCHAR2(3), shipped_at DATE, delivered_at DATE, recv_memo VARCHAR2(100) 칸이 더 있다. 지금 1,012블록이다.
행 길이: 주문할 때 약 40바이트 → 출고 뒤 약 67바이트 → 배송 완료 뒤 약 111바이트(수령 메모 평균 40바이트)
시험 DB에서 같은 데이터를 처음부터 최종 길이로 넣은 사본은 548블록이다.
지금 있는 행의 97%는 배송이 끝났다. 하루 새 주문은 수십 건이다.

원본 SQL

sql

CREATE TABLE shipping (
  order_id     NUMBER        NOT NULL,
  address_id   NUMBER,
  status       VARCHAR2(20)  NOT NULL,
  tracking_no  VARCHAR2(20),
  courier_cd   VARCHAR2(3),
  shipped_at   DATE,
  delivered_at DATE,
  recv_memo    VARCHAR2(100),
  CONSTRAINT shipping_pk PRIMARY KEY (order_id)
);                                  -- PCTFREE 를 적지 않았다 (기본값 10)

-- 출고 (택배사 연동)
UPDATE shipping
   SET status = 'SHIPPED', tracking_no = :trk, courier_cd = :cc, shipped_at = SYSDATE
 WHERE order_id = :oid;

-- 배송 완료 (택배사 연동)
UPDATE shipping
   SET status = 'DELIVERED', delivered_at = :dt, recv_memo = :memo
 WHERE order_id = :oid;

-- 고객센터 「최근 배송 목록」
SELECT s.order_id, s.status, s.tracking_no, s.delivered_at
  FROM shipping s
 WHERE s.order_id > :last_id - 2000
 ORDER BY s.order_id DESC;

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

xplan

[고객센터 「최근 배송 목록」 — 최근 주문 2,000건]
-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |      1 |        |   2000 |00:00:00.31 |    2968 |
|   1 |  TABLE ACCESS BY INDEX ROWID | SHIPPING    |      1 |   2000 |   2000 |00:00:00.31 |    2968 |
|*  2 |   INDEX RANGE SCAN DESCENDING| SHIPPING_PK |      1 |   2000 |   2000 |00:00:00.01 |       6 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("S"."ORDER_ID">:LAST_ID-2000)

  table fetch continued row   1,512 (한 번 실행할 때)

[ANALYZE TABLE shipping COMPUTE STATISTICS]
  NUM_ROWS 34,908 · BLOCKS 1,012 · CHAIN_CNT 26,140 (74.9%) · AVG_ROW_LEN 113 · PCT_FREE 10

[같은 SQL — 처음부터 최종 길이로 넣은 사본 (548블록 · CHAIN_CNT 0)]
  Buffers 41 · table fetch continued row 0

문제

목록 2,000건이 2,968블록을 읽는 이유를 ANALYZE 결과와 세션 통계로 설명하시오.
이 테이블의 행이 자라는 모양을 근거로 PCTFREE를 몇으로 할지 계산하시오.
이미 생긴 문제를 고치고 앞으로 새로 채울 블록에 맞는 설정을 하시오. 이미 배송이 끝난 행에도 같은 PCTFREE를 주면 어떻게 되는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 이미 다 자란 행(배송이 끝난 97%)은 여유 없이 다시 펴 쓴다 → 옮겨 간 행 0
ALTER TABLE shipping MOVE PCTFREE 10;
ALTER INDEX shipping_pk REBUILD;          -- MOVE 뒤 UNUSABLE

-- ② 앞으로 새로 채울 블록은 행이 자랄 몫을 남긴다
--    주문 때 40바이트 → 완료 뒤 111바이트 : 필요한 빈자리 = 1 − 40/111 ≈ 64% → 메모 길이 편차를 보고 70
ALTER TABLE shipping PCTFREE 70;

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

xplan

[MOVE 뒤 · 같은 SQL]
  TABLE ACCESS BY INDEX ROWID · Buffers 2,968 → 41 · table fetch continued row 1,512 → 0
  BLOCKS 1,012 → 548 · CHAIN_CNT 26,140 → 0

[PCTFREE 70 뒤 한 달 — 시험 DB에서 같은 흐름을 흉내 냄]
  새 블록 하나에 약 60행 · 배송이 끝난 뒤 블록 사용률 약 82% · CHAIN_CNT 증가 0

[비교 — 실수로 ALTER TABLE shipping MOVE PCTFREE 70 을 했다면]
  BLOCKS 1,012 → 약 1,640 (다 자란 행을 30%만 채운다) · 목록 Buffers 약 70

모범 답안 서술

원인은 행 이전이다. ANALYZE로 보면 34,908행 중 26,140행(74.9%)이 원래 블록에 주소만 남기고 몸통을 다른 블록으로 옮겼다. 목록 2,000건을 PK 순서로 찾아갈 때 1,512번은 원래 블록에서 주소를 읽고 다른 블록으로 한 번 더 갔고(table fetch continued row 1,512), 41블록이면 될 일이 2,968블록이 됐다. 행은 주문할 때 40바이트로 들어와 PCTFREE 10으로 블록의 90%까지 채워지는데, 배송이 진행되며 111바이트로 자라자 블록에 남겨 둔 10%로는 감당하지 못했다. PCTFREE는 행이 자랄 몫이다. 40바이트가 111바이트가 되므로 블록에는 처음 채울 때 최종 크기의 1 − 40/111 ≈ 64%를 비워 두어야 한다. 수령 메모 길이에 편차가 있으니 조금 더 두어 70으로 한다. 새 블록 하나에 약 60행이 들어가고, 배송이 끝나면 블록의 약 82%가 찬다. 이 테이블은 사흘 안에 행이 다 자라므로 빈자리가 오래 남지 않는다. 교재 05화에서 PCTFREE 75가 손해였던 것은 UPDATE가 언제 올지 모르는 데이터였기 때문이다. 설정은 둘로 나눠 한다. 이미 배송이 끝난 97%는 더 자라지 않으므로 ALTER TABLE shipping MOVE PCTFREE 10으로 여유 없이 다시 펴 쓴다. 다 자란 행에 PCTFREE 70을 주고 MOVE하면 행을 블록의 30%만 채워 테이블이 1,012 → 약 1,640블록으로 오히려 커진다. MOVE 뒤에는 PK 인덱스가 UNUSABLE이 되므로 REBUILD한다. 그다음 ALTER TABLE shipping PCTFREE 70으로 앞으로 새로 채울 블록에만 70을 준다. PCTFREE를 바꿔도 이미 채워진 블록의 행은 옮겨지지 않기 때문에 순서가 중요하다. 고친 뒤 목록은 2,968 → 41블록, BLOCKS는 1,012 → 548로 준다.

해설

함정 둘: ALTER TABLE … PCTFREE 70만 하면 이미 옮겨 간 26,140행은 그대로다(새로 채우는 블록에만 쓰인다). 반대로 MOVE PCTFREE 70을 한 번에 하면 다 자란 행으로 테이블을 세 배로 부풀린다.
설계로 푸는 답: 출고와 배송 완료를 shipping_events 같은 이력 테이블에 새 행으로 쌓으면 행이 자라지 않는다. 화면이 '주문당 현재 상태 한 줄'을 원하면 이 문제처럼 PCTFREE로 자랄 몫을 남기는 쪽이 단순하다.
MOVE는 그 시간 동안 테이블에 쓰는 DML을 막는다. 연동이 쉬는 시간에 하거나 MOVE ONLINE(12.2 이상)을 쓴다. 행 수가 적어 몇 초면 끝난다.
PCTFREE를 계산할 때는 평균이 아니라 실제 분포를 본다. 메모가 100바이트까지 들어오는 행이 많다면 몫이 더 커진다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

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

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황