배달 정보를 소급해 채운 뒤 일별 배달 현황이 12배 블록을 읽는데, 클러스터링 팩터는 그대로다

실전DDL버거482 / 700

상황

버거원 본사는 2014년 1월 배달앱 연동을 시작하면서, 첫 주말에 2013년 배달 주문 약 20만 건의 배달 정보를 ORD에 한 번에 소급해 채웠다. 그 뒤로 본사 「일별 배달 현황」이 2013년 날짜에서만 느려졌다. DBA가 점검했더니 통계는 최신이고 ORD_X01의 클러스터링 팩터도 22,223 그대로였다. 정리 방법으로 넷이 올라왔다. (A) ALTER TABLE ORD MOVE (B) ALTER TABLE ORD MOVE ONLINE (C) 새 테이블에 날짜순으로 다시 쌓아 바꿔 끼우기 (D) DBMS_REDEFINITION 온라인 재정의. 매장 영업은 10:00 ~ 24:00이고, 02:00 ~ 06:00에는 POS 입력이 없다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 배달 칸을 채우기 전 22,422블록 · 날짜순 적재 · 하루 약 1,370건 · 행 길이 약 160바이트 · ORD_PK (ORD_NO) · ORD_X01 (ORD_DT, ORD_HMS) CF 22,223
이 문제에서는 DLV_YN VARCHAR2(1)와 DLV_INFO VARCHAR2(200) 칸이 더 있다. 2013년 배달 주문 200,410건에 DLV_INFO가 평균 약 140바이트 들어갔다. PCTFREE는 기본값 10이다.
지금(ANALYZE): BLOCKS 25,652 · CHAIN_CNT 78,412(전부 2013년 배달 주문)
앞으로도 새 배달 주문은 POS가 넣은 뒤 몇 분 안에 배달앱 서버가 DLV_INFO를 채운다.
POS 연동 계정 POS_IF에 SELECT, INSERT, UPDATE 권한이 있다. 테이블 여유 공간은 테이블 크기의 세 배쯤 남아 있다.

원본 SQL

sql

-- 2014년 1월 첫 주말 · 2013년 배달 정보 소급 (배달앱 연동팀)
UPDATE ORD o
   SET o.DLV_YN   = 'Y',
       o.DLV_INFO = (SELECT d.DLV_INFO FROM DLV_HIST_2013 d WHERE d.ORD_NO = o.ORD_NO)
 WHERE o.ORD_DT BETWEEN '20130101' AND '20131231'
   AND EXISTS (SELECT 1 FROM DLV_HIST_2013 d WHERE d.ORD_NO = o.ORD_NO);
COMMIT;

-- 본사 「일별 배달 현황」
SELECT SUBSTR(ORD_HMS, 1, 2) AS HH, COUNT(*) AS ORD_CNT,
       COUNT(CASE WHEN DLV_YN = 'Y' THEN 1 END) AS DLV_CNT
  FROM ORD
 WHERE ORD_DT = :v_dt
 GROUP BY SUBSTR(ORD_HMS, 1, 2);

실행계획 학습용 예시 · MOVE 뒤 숫자의 비율은 교재 05화 실측(MOVE 후 CF 52,206 · Buffers 10,405)과 같은 모양

xplan

[일별 배달 현황 — :v_dt = '20131105']
----------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |         |      1 |        |     14 |00:00:00.02 |     453 |
|   1 |  HASH GROUP BY                       |         |      1 |     14 |     14 |00:00:00.02 |     453 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORD     |      1 |   1370 |   1372 |00:00:00.02 |     453 |
|*  3 |    INDEX RANGE SCAN                  | ORD_X01 |      1 |   1370 |   1372 |00:00:00.01 |       5 |
----------------------------------------------------------------------------------------------------------

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

  table fetch continued row   214 (한 번 실행할 때)
  (같은 SQL · :v_dt = '20121105' → A-Rows 1,366 · Buffers 36 · continued row 0)

[시험 DB 사본에서 넷을 해 본 결과]
  방법                        CHAIN_CNT   BLOCKS    ORD_X01 CF   일별 현황 Buffers   POS 입력        작업 시간
  지금                          78,412    25,652       22,223         453              —               —
  (A) MOVE + REBUILD                 0    26,318       72,540         171              멈춤            약 1분 40초
  (B) MOVE ONLINE                    0    26,318       72,610         171              계속 받음       약 4분 10초
  (C) CTAS ORDER BY + 바꿔 끼우기      0    26,302       26,014          44              멈춤            약 2분 30초
  (D) DBMS_REDEFINITION (ORDERBY_COLS)  0  26,302       26,020          44              계속 받음       약 9분 (준비와 끝맺음 포함)

문제

일별 배달 현황이 느려진 원인을 설명하고, 클러스터링 팩터가 22,223 그대로인데도 느린 이유를 쓰시오.
정리안 넷을 숫자와 부작용으로 비교해 하나를 고르고, 그 SQL을 작성하시오. MOVE가 행 이전을 없애는데도 일별 현황이 171블록에 머무는 이유를 쓰시오.
앞으로 다시 생기지 않게 하는 방법을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 02:00 작업 창 (POS 입력 멈춤 · 본사 공지) · 원본 DDL 과 권한은 미리 DBMS_METADATA 로 뽑아 둔다
-- ① 날짜순으로 새로 쌓는다 → 옮겨 간 행 0 · CF 는 블록 수 가까이
CREATE TABLE ORD_N
AS SELECT * FROM ORD ORDER BY ORD_DT, ORD_HMS;

-- ② 인덱스 · PK
CREATE UNIQUE INDEX ORD_N_PK ON ORD_N (ORD_NO);
ALTER TABLE ORD_N ADD CONSTRAINT ORD_N_PK PRIMARY KEY (ORD_NO) USING INDEX ORD_N_PK;
CREATE INDEX ORD_N_X01 ON ORD_N (ORD_DT, ORD_HMS);

-- ③ 바꿔 끼운다 (옛 테이블은 ORD_OLD 로 한 주 남겨 되돌릴 길을 둔다)
ALTER TABLE ORD RENAME TO ORD_OLD;
ALTER TABLE ORD_N RENAME TO ORD;

-- ④ 따라오지 않는 것 — 권한 · 통계
GRANT SELECT, INSERT, UPDATE ON ORD TO POS_IF;
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORD')

-- ⑤ 다시 생기지 않게: 배달 정보는 주문 행을 키우지 않는 별도 테이블에 쌓는다 (배달앱 서버는 UPDATE 대신 INSERT)
CREATE TABLE ORD_DLV (
  ORD_NO   VARCHAR2(20)  NOT NULL,
  DLV_INFO VARCHAR2(200),
  DLV_DT   DATE,
  CONSTRAINT ORD_DLV_PK PRIMARY KEY (ORD_NO)
);

개선 후 실행계획 학습용 예시 · MOVE 뒤 숫자의 비율은 교재 05화 실측(MOVE 후 CF 52,206 · Buffers 10,405)과 같은 모양

xplan

[(C) 뒤 — 일별 배달 현황 :v_dt = '20131105']
----------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |         |      1 |        |     14 |00:00:00.01 |      44 |
|   1 |  HASH GROUP BY                       |         |      1 |     14 |     14 |00:00:00.01 |      44 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORD     |      1 |   1370 |   1372 |00:00:00.01 |      44 |
|*  3 |    INDEX RANGE SCAN                  | ORD_X01 |      1 |   1370 |   1372 |00:00:00.01 |       5 |
----------------------------------------------------------------------------------------------------------
   3 - access("ORD_DT"=:V_DT)

  table fetch continued row 214 → 0 · CHAIN_CNT 78,412 → 0 · ORD_X01 CF 22,223 → 26,014
  02:00 시작 · 02:03 POS 재개 (CTAS 2분 30초 · 인덱스와 권한 · 통계 포함 약 3분)

모범 답안 서술

원인은 2014년 1월 소급 UPDATE가 만든 행 이전이다. 160바이트 행에 DLV_INFO 140바이트를 한꺼번에 채우자 PCTFREE 10의 빈자리로는 감당하지 못했고, 2013년 배달 주문 78,412행의 몸통이 테이블 끝의 새 블록으로 옮겨 갔다. 원래 자리에는 다음 주소만 남는다. 2013-11-05의 1,372행 중 214행이 그런 행이라, 날짜순으로 모인 블록 31개를 읽으면 끝날 일에서 214번을 테이블 끝으로 한 번 더 다녀왔다(table fetch continued row 214). 그래서 36블록이 453블록이 됐다. 클러스터링 팩터가 22,223 그대로인 것은 행 이전이 ROWID를 바꾸지 않기 때문이다. CF는 인덱스에 적힌 ROWID 순서로 블록을 몇 번 바꿔 타는지 세는데, 인덱스는 여전히 원래 자리를 가리키므로 숫자가 변하지 않는다. CF가 정상이라고 행 이전이 없다는 뜻은 아니다. 정리안을 비교하면 이렇다. (A) MOVE는 행 이전을 없애지만 지금 놓인 순서 그대로 다시 쓴다. 테이블 끝에 몰려 있던 몸통들이 그 자리 순서대로 놓이므로 날짜순이 흐트러져 CF가 72,540이 되고 일별 현황은 171블록에 머문다(교재 05화의 MOVE 뒤 735가 아니라 10,405였던 것과 같다). MOVE 동안 인덱스가 UNUSABLE이 되어 POS도 멈춰야 한다. (B) MOVE ONLINE은 POS를 계속 받지만 순서 문제는 (A)와 같고 시간이 두 배 넘게 든다. (C) CTAS ORDER BY는 날짜순으로 새로 쌓아 CF가 26,014(블록 수 가까이)가 되고 일별 현황이 44블록이 된다. 대가는 작업 창 동안 POS 입력을 멈춰야 하고, 새 테이블에 권한, 제약, 인덱스, 통계가 따라오지 않아 다시 챙겨야 한다는 것이다. (D) 온라인 재정의는 ORDERBY_COLS로 순서까지 바로잡으면서 POS를 계속 받지만, PK가 있어야 하고 중간 테이블과 두 배의 공간, 준비와 끝맺음 절차가 필요해 약 9분이 걸리며 실수할 자리가 많다. 02:00 ~ 06:00에 POS 입력이 없으므로 (C)를 고른다. 약 3분이면 CTAS, 인덱스, 권한, 통계까지 끝나고 옛 테이블을 ORD_OLD로 남겨 되돌릴 길도 둔다. 영업 중에만 할 수 있는 상황이라면 (D)를 고른다. 앞으로 배달 주문도 POS가 넣은 뒤 DLV_INFO를 채우므로, 그대로 두면 새 블록에서 같은 일이 다시 생긴다. 배달 정보는 주문 행을 키우지 않는 별도 테이블 ORD_DLV에 쌓고, 배달앱 서버는 ORD를 UPDATE하는 대신 ORD_DLV에 INSERT하게 바꾼다. 주문 테이블을 바꿀 수 없다면 새 블록의 PCTFREE를 배달 주문 비율(40%)과 140바이트를 보고 30쯤으로 올리는 것이 차선이다.

해설

함정: ALTER TABLE ORD PCTFREE 30만 하면 이미 옮겨 간 78,412행은 그대로다. PCTFREE는 앞으로 새로 채우는 블록에만 쓰인다.
CF가 정상인데 인덱스 경유 Buffers가 A-Rows에 비해 크면 행 이전을 의심하고 table fetch continued row와 ANALYZE … LIST CHAINED ROWS로 확인한다. DBMS_STATS는 CHAIN_CNT를 채우지 않는다(교재 05화 §6).
(C)에서 ORD를 참조하는 PL/SQL은 이름을 바꾸는 순간 INVALID가 됐다가 다시 컴파일된다. 다른 테이블이 ORD를 FK로 참조한다면 RENAME으로 끝나지 않으므로 (D)가 낫다(DML-003의 대가 목록과 같다).
ORDER BY가 들어간 CTAS는 100만 행 정렬에 TEMP를 쓴다. 작업 전에 TEMP 여유를 확인한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · MOVE 뒤 숫자의 비율은 교재 05화 실측(MOVE 후 CF 52,206 · Buffers 10,405)과 같은 모양 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · MOVE 뒤 숫자의 비율은 교재 05화 실측(MOVE 후 CF 52,206 · Buffers 10,405)과 같은 모양 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황