금액을 부가세 포함으로 바꾸는 UPDATE 한 문장이 redo 1.2GB를 쓰고, DBA는 인덱스를 다 끄자고 한다

중급UPDATE한빛유통477 / 700

상황

다음 달부터 정산 기준이 부가세 포함 금액으로 바뀐다. 토요일 22:00 ~ 24:00 작업 창에 T_ORD_BIG의 ORD_AMT를 모두 부가세 포함 금액(원 단위 반올림)으로 바꾸기로 했다. 운영 복제본 리허설에서 UPDATE 한 문장이 6분 52초 걸렸고 redo가 1,186MB 나왔다. DBA 의견: "인덱스 둘 다 끄고 돌린 다음 다시 짓죠." 정산팀은 작업 창 동안에도 정산 목록 화면(Q4)은 열어 두기를 바란다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 이번 작업은 모든 행의 ORD_AMT를 바꾼다. 트리거와 FK는 없다.
IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT): 리프 3,206블록 · 낮의 Q1(기간 매출, 91블록)이 쓴다.
IX_ORD_BIG_DT_SEQ (ORD_DT, ORD_SEQ): 리프 3,344블록 · Q4(정산 목록 페이징, 15블록)가 쓴다.
운영은 ARCHIVELOG이고 테이블과 인덱스는 LOGGING이다. 온라인 리두 로그는 200MB × 3이다.

원본 SQL

sql

-- 토요일 22:00 작업 창 (리허설)
UPDATE T_ORD_BIG
   SET ORD_AMT = ROUND(ORD_AMT * 1.1);
COMMIT;

실행계획 학습용 예시 · 인덱스 크기와 Q1 · Q4 숫자는 교재 02화, 09화, 14화 규모

xplan

------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |           |      1 |        |      0 |00:06:52.40 |   6312K |
|   1 |  UPDATE            | T_ORD_BIG |      1 |        |      0 |00:06:52.40 |   6312K |
|   2 |   TABLE ACCESS FULL| T_ORD_BIG |      1 |   1000K|   1000K|00:00:00.31 |    5826 |
------------------------------------------------------------------------------------------

[세션 통계 — 리허설 (운영 복제본 · ARCHIVELOG)]
                                ORD_AMT 갱신 (원본)      (비교) ORD_ST를 같은 길이 값으로 갱신 · 같은 100만 행
  바뀐 칸이 든 인덱스            IX_ORD_BIG_DT_AMT          없음
  redo size                      1,186 MB                   236 MB
  undo change vector size          402 MB                    98 MB
  db block changes               4,128,460                 1,021,300
  경과                           6분 52초                   41초
  그중 log file switch 대기      5분 10초                   12초

[UPDATE 뒤 인덱스 크기 (리프 블록)]
  IX_ORD_BIG_DT_AMT  3,206 → 5,310 · IX_ORD_BIG_DT_SEQ  3,344 → 3,344

문제

redo 1,186MB를 테이블 몫과 인덱스 몫으로 나누고, 이 UPDATE가 어느 인덱스를 고치는지 설명하시오.
작업 창 안에 끝나고 Q4도 그대로 쓸 수 있도록 작업을 고치시오. DBA의 '인덱스 둘 다 끄기' 안도 평가하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ORD_AMT 가 든 인덱스는 DT_AMT 하나뿐이다 → 그것만 끄고, 다 고친 뒤 정렬해서 한 번에 짓는다
ALTER INDEX IX_ORD_BIG_DT_AMT UNUSABLE;

UPDATE T_ORD_BIG
   SET ORD_AMT = ROUND(ORD_AMT * 1.1);
COMMIT;

ALTER INDEX IX_ORD_BIG_DT_AMT REBUILD;
-- IX_ORD_BIG_DT_SEQ 는 그대로 둔다 (키가 바뀌지 않아 이 UPDATE 가 건드리지 않는다 · 작업 창 동안 Q4 가 쓴다)

개선 후 실행계획 학습용 예시 · 인덱스 크기와 Q1 · Q4 숫자는 교재 02화, 09화, 14화 규모

xplan

[같은 작업 · 고친 순서 (운영 복제본 · ARCHIVELOG)]
  단계                          redo         경과
  DT_AMT UNUSABLE               0.01 MB      0.1 초
  UPDATE 100만 행               241 MB       44 초
  DT_AMT REBUILD                 26 MB       5.3 초
  합계                          267 MB       약 50 초     (원본 1,186 MB · 6분 52초)

[작업 창 동안]
  Q4  IX_ORD_BIG_DT_SEQ · Buffers 15 그대로
  Q1  TABLE ACCESS FULL · Buffers 5,822 (REBUILD가 끝나면 다시 91)
[REBUILD 뒤] IX_ORD_BIG_DT_AMT 리프 3,206 (갓 만든 크기)

모범 답안 서술

같은 100만 행을 고쳐도 어느 인덱스에도 없는 칸을 바꿀 때 redo는 236MB다. 이것이 테이블 몫(행마다 바꾸기 전 값을 undo에, 바꾼 내용을 redo에 남기는 일)이고, 나머지 약 950MB가 인덱스 몫이다. UPDATE는 바뀐 칸이 키에 든 인덱스만 고친다. ORD_AMT가 든 인덱스는 IX_ORD_BIG_DT_AMT 하나다. 인덱스 키가 바뀌면 그 자리에서 값을 고칠 수 없으므로 옛 키를 지우고 정렬된 새 자리에 다시 끼운다. 100만 행이면 지우기 100만 번과 끼우기 100만 번이 생기고 리프 분할까지 더해져 블록 변경이 4,128,460이 됐다. 인덱스도 3,206 → 5,310블록으로 부풀었다. IX_ORD_BIG_DT_SEQ는 키가 ORD_DT와 ORD_SEQ라서 이 UPDATE가 건드리지 않는다(3,344 그대로). 모든 행의 키를 바꾸는 작업이므로 켠 채 행마다 고치는 것보다 끄고 다 고친 뒤 정렬해서 한 번에 짓는 쪽이 싸다. IX_ORD_BIG_DT_AMT만 UNUSABLE로 끄고, UPDATE를 마친 뒤 REBUILD한다. redo는 1,186MB → 약 267MB(UPDATE 241MB + REBUILD 26MB), 경과는 6분 52초 → 약 50초로 줄고, 다시 지은 인덱스는 갓 만든 크기(3,206)다. DBA의 '둘 다 끄기' 안은 절반만 맞다. DT_SEQ는 이 UPDATE가 고칠 일이 없는 인덱스라서 끄면 얻는 것이 없다. 대신 작업 창 동안 Q4가 15블록 대신 전체 스캔으로 가고, 쓸데없는 REBUILD 시간이 늘어난다. 정산팀이 작업 창에 Q4를 쓰겠다고 했으므로 DT_SEQ는 켠 채 둔다. DT_AMT가 꺼진 동안에는 Q1이 전체 스캔(5,822블록)으로 가는데, 작업 창이 토요일 밤이라 감당할 수 있다.

해설

판단 기준: 인덱스를 끄고 다시 짓는 쪽이 이기는 것은 그 인덱스의 키를 대부분의 행에서 바꿀 때다. 일부만 바꾸는 UPDATE라면 켠 채 두는 쪽이 싸다(DML-037과 같은 비율 판단).
다른 답: 새 금액으로 CREATE TABLE … AS SELECT … ROUND(ORD_AMT * 1.1)을 만들어 바꿔 끼우는 방법이다. 직접 경로라 undo가 거의 없고 인덱스도 새로 짓는다. 대신 권한, 통계, 의존 객체를 다시 챙겨야 하고 작업 창 동안 Q4도 멈춘다(DML-003).
ORD_AMT가 10% 커지면 숫자 자릿수가 하나 늘어 행이 1바이트쯤 길어지는 행이 있다. PCTFREE 10이 그 정도는 받아 주므로 행 이전 걱정은 없다.
ARCHIVELOG라 REBUILD도 새로 쓴 인덱스 블록만큼(26MB) redo를 만든다. NOLOGGING으로 줄일 수 있지만 그 인덱스는 다음 백업 전까지 복구할 수 없다(DML-033).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 인덱스 크기와 Q1 · Q4 숫자는 교재 02화, 09화, 14화 규모 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 인덱스 크기와 Q1 · Q4 숫자는 교재 02화, 09화, 14화 규모 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황