바꾸는 주문 수는 지난달과 같은데 7월 1일 정산 확정의 redo가 9.6MB에서 24.3MB가 됐다

중급UPDATE한빛유통141 / 700

상황

매월 1일 03:00 마감 배치는 지난달 주문을 정산 확정으로 바꾼다(STLM_YN을 'N'에서 'Y'로). 정산팀의 「미정산 주문 현황」은 정산 담당 열 명이 수시로 열어 하루 약 300번 실행되는데, 매번 테이블을 통째로 읽어서 6월에 조민우가 (STLM_YN, ORD_DT, ORD_AMT) 인덱스를 만들었다. 화면은 5,826블록에서 50블록 안팎으로 줄었다. 그런데 7월 1일 마감에서 확정 UPDATE의 redo가 9.6MB에서 24.3MB로, 시간이 1.3초에서 3.4초로 늘었다. 배치 담당 오과장이 물었다. "바꾸는 건 지난달처럼 2만 7천 건 남짓인데 왜 두 배 반이냐?"

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 이 문제에서는 STLM_YN CHAR(1)(정산 여부) 컬럼이 있다고 둔다 · 새 주문은 'N'으로 들어오고, 마감 배치가 지난달 주문을 한꺼번에 'Y'로 바꾼다 · 그래서 'N'인 행은 늘 그달 치(최대 약 2만 8천 건)다
5월 주문 28,303건 · 6월 주문 27,390건 · 7월 15일 기준 미정산 13,695건
인덱스 IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT): 「기간 매출」용 · IX_ORD_BIG_YN_DT_AMT (STLM_YN, ORD_DT, ORD_AMT): 6월에 새로 만들었다 · BLEVEL 2 · 리프 약 3,520블록(리프 한 블록에 약 284항목) · 통계 최신
두 인덱스 모두 CLUSTERING_FACTOR가 약 1,000,000이다 · 낮에는 주문이 하루 약 900건 INSERT된다

원본 SQL

sql

-- 매월 1일 03:00 · 정산 확정 (지난달 주문)
UPDATE T_ORD_BIG
   SET STLM_YN = 'Y'
 WHERE ORD_DT >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)
   AND ORD_DT <  TRUNC(SYSDATE, 'MM')
   AND STLM_YN = 'N';

-- 「미정산 주문 현황」 · 하루 약 300번
SELECT ORD_DT, COUNT(*) AS cnt, SUM(ORD_AMT) AS amt
  FROM T_ORD_BIG
 WHERE STLM_YN = 'N'
 GROUP BY ORD_DT
 ORDER BY ORD_DT;

실행계획 학습용 예시 · 행당 비용은 교재 04화 실측 비례

xplan

[정산 확정 UPDATE · 세션 통계]
                           6월 1일 (5월분, 인덱스 전)   7월 1일 (6월분, 인덱스 뒤)
  바뀐 행                  28,303                        27,390
  경과                     1.3초                         3.4초
  redo size                9.6 MB                        24.3 MB
  db block changes         57,148                        168,931
  session logical reads    64,410                        251,302

[7월 1일 · 정산 확정 UPDATE]
----------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |           |      1 |        |      0 |00:00:03.38 |    251K |
|   1 |  UPDATE            | T_ORD_BIG |      1 |        |      0 |00:00:03.38 |    251K |
|*  2 |   TABLE ACCESS FULL| T_ORD_BIG |      1 |  27390 |  27390 |00:00:00.06 |    5826 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("STLM_YN"='N' AND "ORD_DT">=ADD_MONTHS(TRUNC(SYSDATE@!,'fmmm'),-1) AND
              "ORD_DT"<TRUNC(SYSDATE@!,'fmmm')))

(6월 1일에도 같은 모양이었다 · 1번 줄 Buffers 64K)

[「미정산 주문 현황」 7월 15일 · 인덱스 뒤]
-----------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                      |      1 |        |     15 |00:00:00.01 |      51 |
|   1 |  SORT GROUP BY NOSORT|                      |      1 |     31 |     15 |00:00:00.01 |      51 |
|*  2 |   INDEX RANGE SCAN   | IX_ORD_BIG_YN_DT_AMT |      1 |  14000 |  13695 |00:00:00.01 |      51 |
-----------------------------------------------------------------------------------------------------
   2 - access("STLM_YN"='N')
(인덱스 전에는 TABLE ACCESS FULL · Buffers 5,826)

문제

바뀐 행 수는 비슷한데 redo와 블록 변경이 두 배 반 넘게 늘어난 이유를, 행 하나가 바뀔 때 새 인덱스에서 일어나는 일로 설명하시오. 행 하나당 숫자로도 쓰시오.
새 인덱스를 둘지 판단하시오. 마감 배치가 더 내는 비용과 화면이 아끼는 블록을 한 달 기준으로 비교하시오.
화면은 빠르게 두면서 마감 배치의 부담을 줄일 방법이 있다면 그 대가와 함께 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 인덱스는 둔다 — 화면이 하루 약 170만 블록을 아끼고, 마감 배치는 한 달에 한 번 redo 약 15MB · 2초를 더 낸다
-- 정산 확정 UPDATE 도 그대로 (FULL 5,826 블록 · 바뀌는 행마다 인덱스 항목을 지우고 다시 넣는 몫은 이 인덱스를 두는 값이다)
UPDATE T_ORD_BIG
   SET STLM_YN = 'Y'
 WHERE ORD_DT >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)
   AND ORD_DT <  TRUNC(SYSDATE, 'MM')
   AND STLM_YN = 'N';

-- (대안 · 아카이브 로그가 빠듯할 때) 'N' 인 행만 담는 함수 기반 인덱스 — 확정은 항목을 지우기만 한다 · 인덱스는 한 달 치뿐
-- CREATE INDEX IX_ORD_BIG_UNSTLM ON T_ORD_BIG (CASE WHEN STLM_YN = 'N' THEN ORD_DT END,
--                                              CASE WHEN STLM_YN = 'N' THEN ORD_AMT END);
--   화면 SQL 도 같은 식을 써야 쓰인다 : WHERE CASE WHEN STLM_YN = 'N' THEN ORD_DT END IS NOT NULL …

모범 답안 서술

바뀐 행은 28,303 → 27,390건으로 오히려 줄었는데 redo는 9.6 → 24.3MB, 블록 변경은 57,148 → 168,931로 늘었다. 행 하나로 나누면 redo는 약 340 → 890바이트, 블록 변경은 약 2 → 6회다. 늘어난 몫은 새 인덱스에서 나온다. 인덱스는 컬럼 값의 순서로 정렬된 목록이라서, STLM_YN이 'N'에서 'Y'로 바뀐 행의 항목은 제자리에서 고칠 수 없다. 'N' 구역에서 옛 항목을 지우고 'Y' 구역의 새 자리에 다시 넣어야 한다. 그래서 행 하나에 인덱스 일이 두 번 생기고(리프 변경 2회와 그 언두 2회), redo도 행마다 약 550바이트가 붙는다. FULL로 읽는 5,826블록은 전과 같고, UPDATE 줄의 Buffers가 64K → 251K로 는 것도 인덱스 리프를 찾아가 고치는 몫이다. 처음부터 있던 IX_ORD_BIG_DT_AMT는 STLM_YN을 담지 않아서 이 UPDATE와는 상관이 없었다. 인덱스를 둘지는 한 달 기준으로 비교한다. 마감 배치는 한 달에 한 번 redo 약 15MB와 2초를 더 내고, 새벽 3시라 다른 작업과 부딪히지도 않는다. 화면은 하루 약 300번 × (5,826 − 약 50) ≈ 173만 블록을 아끼므로, 한 달이면 5천만 블록이 넘는다. 비교가 되지 않으므로 인덱스는 둔다. UPDATE도 그대로 둔다. 바뀌는 행마다 항목을 옮기는 몫은 이 인덱스를 두는 한 피할 수 없다. 부담을 더 줄이고 싶다면 'N'인 행만 담는 함수 기반 인덱스가 있다. 정산이 확정되면 식의 값이 NULL이 되어 인덱스에서 항목을 지우기만 하므로 갱신 일이 절반으로 줄고, 인덱스도 그달 치(리프 100블록 안팎)만 남는다. 대신 화면 SQL을 인덱스와 똑같은 식으로 고쳐야 쓰이고, 식을 모르는 개발자가 SQL을 고치면 조용히 FULL로 돌아간다. 어느 쪽이든 갱신이 잦은 컬럼을 인덱스에 넣을 때는 "이 컬럼이 바뀌는 건수만큼 인덱스 일이 두 번씩 붙는다"는 비용을 승인서에 적어 둔다.

해설

UPDATE를 이 인덱스로 읽게 하면 안 된다: 새 인덱스는 UPDATE의 WHERE와 모양이 똑같아서 INDEX 힌트를 붙이고 싶어진다. 하지만 27,390행을 ROWID로 찾아가면 CLUSTERING_FACTOR 약 1,000,000 때문에 테이블 블록 2만 7천 개를 하나씩 읽는다. FULL 5,826이 싸다(ACC-003).
INSERT에도 붙는다: 새 주문은 'N'으로 들어오므로 INSERT마다 이 인덱스에 항목이 하나 생긴다(블록 변경 약 2회, 교재 04화 §9). 하루 900건이면 작지만, 일요일 재적재 같은 대량 적재에서는 인덱스 하나만큼의 청구서가 따로 나온다(교재 14화).
컬럼 위치는 상관없다: STLM_YN을 맨 뒤로 옮겨 (ORD_DT, ORD_AMT, STLM_YN)으로 만들어도 값이 바뀌면 항목을 지우고 다시 넣는 것은 같다. 줄이려면 그 컬럼을 빼거나, 바뀐 뒤의 값은 항목이 아예 없게(함수 기반 인덱스) 만드는 수밖에 없다.
식을 쓰는 인덱스의 관리: 함수 기반 인덱스는 SQL이 같은 식을 써야만 쓰인다. 승인서에 식과 그 식을 쓰는 SQL 목록을 함께 적어 둔다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 행당 비용은 교재 04화 실측 비례 ​

문제 ​

모범 답안 SQL ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황

테이블과 인덱스

원본 SQL

실행계획 학습용 예시 · 행당 비용은 교재 04화 실측 비례

문제

모범 답안 SQL

모범 답안 서술

해설

관련 교재