고객번호를 가명으로 바꾸는 UPDATE 한 문장에 2분 47초가 걸리고, 그 뒤로 분석 업체의 기간 조회가 1.5배 블록을 읽는다

기초DDL한빛유통445 / 700

상황

한빛유통은 외부 분석 업체에 주문 데이터를 넘기기 전에, 분석 DB 사본 T_ORD_BIG의 고객번호를 가명(해시값)으로 바꾸기로 했다. 개발자가 칸 길이를 늘리고 UPDATE 한 문장으로 바꾸는 스크립트를 리허설했더니 2분 47초가 걸리고 redo가 약 598MB 나왔다. 끝난 뒤에는 분석 업체가 쓸 기간 조회가 전보다 1.5배 많은 블록을 읽었다. 오과장: "값만 바꿨고 행 수는 그대로인데 조회는 왜 느려지죠?"

테이블과 인덱스

T_ORD_BIG(분석 DB 사본): 1,000,000행 · 5,826블록(블록당 약 172행, 행 평균 약 43바이트) · PCTFREE 10 · CUS_ID VARCHAR2(40) 'CUS_000123'(10자)
가명 고객번호는 RAWTOHEX(STANDARD_HASH(…, 'SHA256'))로 만든 64자다. 행마다 54바이트씩 길어진다.
인덱스는 IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT) 하나다. 분석 업체 계정 ANL_VENDOR에 SELECT 권한이 있고, 통계는 최신이다(ORD_ST에 히스토그램). 트리거와 FK는 없다.
분석 DB는 NOARCHIVELOG다. 매달 1일 운영에서 새로 받고, 업체에 넘기기 전까지는 아무도 쓰지 않는다.

원본 SQL

sql

-- 분석 DB · 업체 전달 전 가명 처리 (리허설)
ALTER TABLE T_ORD_BIG MODIFY (CUS_ID VARCHAR2(64));

UPDATE T_ORD_BIG
   SET CUS_ID = RAWTOHEX(STANDARD_HASH('HBANL' || CUS_ID, 'SHA256'));
COMMIT;

실행계획 학습용 예시

xplan

------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |           |      1 |        |      0 |00:02:47.10 |   3108K |
|   1 |  UPDATE            | T_ORD_BIG |      1 |        |      0 |00:02:47.10 |   3108K |
|   2 |   TABLE ACCESS FULL| T_ORD_BIG |      1 |   1000K|   1000K|00:00:00.43 |    5826 |
------------------------------------------------------------------------------------------

[세션 통계 — UPDATE 한 번]
  redo size                    약 598 MB
  undo change vector size      약 141 MB
  db block changes             약 3,060,000

[UPDATE 뒤 — ANALYZE TABLE T_ORD_BIG LIST CHAINED ROWS]
  블록 5,826 → 12,630 · CHAINED_ROWS 517,300행 (52%)
  업체의 기간 조회 (2024년 6월 · IX_ORD_BIG_DT_AMT → 테이블)
    Buffers 27,481 → 41,720 · table fetch continued row 14,239

문제

값만 바꿨는데 redo와 undo가 이만큼 생기고, 기간 조회까지 블록을 더 읽게 된 이유를 설명하시오.
UPDATE 대신 가명 고객번호를 담은 새 테이블을 만들어 바꿔 끼우도록 다시 작성하고, 바꿔 끼울 때 다시 챙겨야 하는 것을 쓰시오.
같은 테이블이라도 UPDATE가 맞는 경우는 언제인지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 처음부터 최종 길이로 새로 쓴다 (직접 경로 · undo 거의 없음 · 옮겨 갈 행 없음)
CREATE TABLE T_ORD_BIG_N
PCTFREE 10
AS
SELECT ORD_SEQ,
       CAST(RAWTOHEX(STANDARD_HASH('HBANL' || CUS_ID, 'SHA256')) AS VARCHAR2(64)) AS CUS_ID,
       ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG;

-- ② 원래 테이블에 딸려 있던 것을 다시 만든다
CREATE INDEX IX_ORD_BIG_N_DT_AMT ON T_ORD_BIG_N (ORD_DT, ORD_AMT);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_ORD_BIG_N');   -- ORD_ST 히스토그램까지 (CTAS 의 온라인 통계는 히스토그램을 모으지 않는다)
GRANT SELECT ON T_ORD_BIG_N TO ANL_VENDOR;
-- 식으로 만든 칸(CUS_ID)의 NOT NULL · CHECK 제약과 코멘트도 원래 정의와 대조해 다시 건다

-- ③ 바꿔 끼운다 (원본은 확인이 끝날 때까지 남겨 둔다)
ALTER TABLE T_ORD_BIG RENAME TO T_ORD_BIG_OLD;
ALTER TABLE T_ORD_BIG_N RENAME TO T_ORD_BIG;
ALTER INDEX IX_ORD_BIG_DT_AMT RENAME TO IX_ORD_BIG_OLD_DT_AMT;
ALTER INDEX IX_ORD_BIG_N_DT_AMT RENAME TO IX_ORD_BIG_DT_AMT;
-- 확인 뒤: DROP TABLE T_ORD_BIG_OLD PURGE;

개선 후 실행계획 학습용 예시

xplan

[같은 분석 DB · 고친 순서]
  단계                                  경과       redo        undo
  ① CTAS (1,000,000행 · 직접 경로)      9.6초      약 1.3 MB   약 0.1 MB
  ② CREATE INDEX                        2.9초      약 0.4 MB   —
     통계 수집 (히스토그램 정책 포함)    1.8초      —           —
     GRANT                              0.1초      —           —
  ③ RENAME 네 번                        0.1초      —           —
  합계                                  약 15초    약 2 MB     (UPDATE 판: 2분 47초 · 598 MB · 141 MB)

[새 T_ORD_BIG]
  블록 13,160 · CHAINED_ROWS 0 · 블록마다 PCTFREE 10의 빈자리가 그대로 남아 있다
  업체의 기간 조회 (2024년 6월)  Buffers 27,481 · table fetch continued row 0
  바꿔 끼우기 전까지 필요한 공간: 원본 5,826 + 새 테이블 13,160블록

모범 답안 서술

가명 고객번호는 64자라서 행마다 54바이트씩 길어진다. 한 블록에는 약 172행이 들어 있고, 남겨 둔 빈자리(PCTFREE 10)는 블록당 약 800바이트다. 172행이 모두 54바이트씩 자라려면 블록마다 약 9,300바이트가 더 필요하므로, 블록마다 80여 행만 제자리에서 커지고 나머지는 자리가 없어 다른 블록으로 옮겨 간다(행 이전). 리허설에서 517,300행(52%)이 옮겨 갔고 블록은 5,826 → 12,630이 됐다. 옮겨 간 행은 원래 자리에 새 주소만 남기므로 ROWID는 그대로지만, 인덱스로 찾아온 조회는 원래 블록을 읽은 뒤 옮겨 간 블록을 한 번 더 읽는다. 업체의 기간 조회가 Buffers 27,481 → 41,720(table fetch continued row 14,239)이 된 이유다. UPDATE는 행마다 바꾸기 전 값을 undo에, 바꾼 내용을 redo에 적는다. 100만 행을 모두 고치고, 옮겨 가는 행은 새 블록에 행을 쓰는 일과 원래 자리에 주소를 남기는 일까지 기록하므로 redo가 약 598MB, undo가 약 141MB가 됐다. 행 대부분의 값을 바꾸는 일이므로 고치지 않고 새로 쓴다. 가명 고객번호를 SELECT에서 계산해 CTAS로 새 테이블을 만들면, 처음부터 최종 길이로 블록을 채우므로 옮겨 갈 행이 없다. CTAS는 직접 경로라 버퍼 캐시를 거치지 않고 블록째 쓰며, 테이블 데이터의 undo를 거의 만들지 않고 NOARCHIVELOG에서는 redo도 거의 없다. 경과는 2분 47초 → 약 15초, redo는 598MB → 약 2MB로 줄고, 기간 조회도 27,481블록으로 돌아온다. 대신 새 테이블에는 원래 테이블의 인덱스, 권한(GRANT), 통계 정책(ORD_ST 히스토그램), 식으로 만든 칸의 제약, 코멘트가 따라오지 않는다. 이것들을 다시 만든 뒤 RENAME으로 바꿔 끼운다. 원래 이름을 쓰던 뷰와 PL/SQL은 무효가 되었다가 다시 컴파일된다. 바꿔 끼울 때까지는 원본과 새 테이블을 함께 둘 공간(5,826 + 13,160블록)도 있어야 한다. UPDATE가 맞는 경우도 있다. 바꾸는 행이 일부(몇 %)라면 테이블 전체를 새로 쓰는 비용이 더 크다. 운영 테이블처럼 쓰기를 멈출 수 없으면 복사하는 동안 들어온 변경을 잃으므로 바꿔 끼울 수 없다. 그때는 키 범위로 나눠 UPDATE하고, 행이 길어지는 변경이면 블록의 빈자리(PCTFREE)부터 따진다.

해설

함정: 전체 스캔으로는 행 이전이 잘 드러나지 않는다. 블록이 늘어난 만큼 더 읽을 뿐이고, 인덱스를 거쳐 테이블로 가는 조회에서 크게 드러난다(교재 05화 §6). 그래서 UPDATE가 끝나고 한참 뒤에 '조회가 느려졌다'로 돌아온다.
PCTFREE를 키우면? 54바이트를 받아 주려면 PCTFREE를 60 가까이 줘야 하고, 그러면 UPDATE 전부터 테이블이 두 배 넘게 커진다. 대부분의 행을 바꾸는 일이라면 처음부터 최종 길이로 새로 쓰는 쪽이 맞다.
이미 UPDATE로 옮겨 간 행은 ALTER TABLE … MOVE로 다시 펴 쓸 수 있다. MOVE 뒤에는 인덱스가 UNUSABLE이 되므로 REBUILD한다(DML-048).
CTAS를 PARALLEL로 만들면 병렬 차수가 테이블 속성으로 남는다. 만든 뒤 NOPARALLEL로 되돌린다(DML-041).
쓰기를 멈출 수 없는 운영 테이블인데 대부분의 행을 바꿔야 한다면 DBMS_REDEFINITION(온라인 재정의)으로 새로 쓰면서 바꿔 끼우는 방법이 있다. 작업 중에 들어온 변경을 따라 반영해 주는 대신 준비할 것이 많다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황