PART-047

2022년 주문의 고객번호를 가명으로 바꾸는 UPDATE, 병렬로 돌려도 redo 186MB와 두 배가 된 인덱스는 그대로다

실전UPDATE한빛유통422 / 700

상황

법무팀 지침에 따라 한빛유통은 주문 후 2년이 지난 주문의 고객번호를 가명으로 바꾼다. 처음에는 2022년 한 해(12칸)를 한꺼번에 처리하고, 그 뒤로는 매달 한 칸씩 처리한다. 2022년 주문이 등급별 분석에서 빠져도 된다는 것은 현업이 확인했다. 조민우가 UPDATE 한 문장을 올렸고 EE 시험 DB에서 두 번 리허설했다. 직렬로는 58초, 병렬 DML로는 14초에 끝났지만 redo와 undo는 거의 같았고, 끝난 뒤 고객번호 인덱스의 2022년 칸이 두 배로 커졌다. 박팀장: "UPDATE 말고 칸 단위로 하는 방법까지 비교해서, 2022년 일괄 처리와 매달 처리를 어떻게 할지 정해 와."

테이블과 인덱스

T_ORD_BIG_P14 주문(파티션 복사본, 교재 14화): 1,000,000행 · PARTITION BY RANGE (ORD_DT) 월별 36칸(P202201 ~ P202412)과 빈 PMAX. 2022년은 1 ~ 12번째 칸이고 333,120행, 칸 하나 FULL ≈ 161블록이다. 컬럼은 ORD_SEQ · CUS_ID · ORD_DT · ORD_ST · ORD_AMT다.
LOCAL 인덱스 IX_P14_CUS_DT (CUS_ID, ORD_DT)(2022년 12칸의 리프 합계 약 1,150블록) · IX_P14_DT_AMT (ORD_DT, ORD_AMT). 제약, GLOBAL 인덱스, 트리거는 없다.
T_CUS_PSN 가명 매핑(문제 설정): 2,000행 · 12블록 · CUS_ID → PSN_ID VARCHAR2(12) · PK PK_T_CUS_PSN (CUS_ID). 법무팀이 만들어 넘겼고 2022년 주문의 고객이 모두 들어 있다고 한다.
낮에는 결제 화면이 이번 달 칸의 ORD_ST를 한 건씩 고치고, 고객센터가 가끔 지난 주문의 반품을 처리한다. 운영은 ARCHIVELOG다.

원본 SQL

sql

-- 리허설 ① 직렬
UPDATE T_ORD_BIG_P14 o
   SET o.CUS_ID = (SELECT m.PSN_ID FROM T_CUS_PSN m WHERE m.CUS_ID = o.CUS_ID)
 WHERE o.ORD_DT >= DATE '2022-01-01'
   AND o.ORD_DT <  DATE '2023-01-01';
COMMIT;

-- 리허설 ② 병렬 DML
ALTER SESSION ENABLE PARALLEL DML;
UPDATE /*+ PARALLEL(o 8) */ T_ORD_BIG_P14 o
   SET o.CUS_ID = (SELECT m.PSN_ID FROM T_CUS_PSN m WHERE m.CUS_ID = o.CUS_ID)
 WHERE o.ORD_DT >= DATE '2022-01-01'
   AND o.ORD_DT <  DATE '2023-01-01';
COMMIT;

실행계획 학습용 예시 · EE 시험 DB 기준 · 병렬 수치는 어림값

xplan

[① 직렬 UPDATE]
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT              |               |      1 |        |      0 | 00:00:57.84 |    1421K|        |       |
|   1 |  UPDATE                       | T_ORD_BIG_P14 |      1 |        |      0 | 00:00:57.84 |    1421K|        |       |
|   2 |   PARTITION RANGE ITERATOR    |               |      1 |   333K |   333K | 00:00:00.21 |    1932 |      1 |    12 |
|*  3 |    TABLE ACCESS FULL          | T_ORD_BIG_P14 |     12 |   333K |   333K | 00:00:00.17 |    1932 |      1 |    12 |
|   4 |   TABLE ACCESS BY INDEX ROWID | T_CUS_PSN     |   2377 |      1 |   2377 | 00:00:00.01 |    4754 |        |       |
|*  5 |    INDEX UNIQUE SCAN          | PK_T_CUS_PSN  |   2377 |      1 |   2377 | 00:00:00.01 |    2377 |        |       |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(("O"."ORD_DT">=TO_DATE(' 2022-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."ORD_DT"<TO_DATE(' 2023-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
   5 - access("M"."CUS_ID"=:B1)

[세션 통계 · 리허설 두 번]
                               ① 직렬            ② 병렬 DML (DOP 8)
  경과                          57.8초            14.2초
  redo size                    약 182 MB         약 186 MB
  undo change vector size      약 96 MB          약 97 MB
  db block changes             약 1,340,000      약 1,350,000
  다른 세션                     -                 고객센터의 2022년 주문 반품 처리 2건이 끝날 때까지 enq: TM - contention

[끝난 뒤 · IX_P14_CUS_DT 의 2022년 12칸]
  리프 블록 합계 약 1,150 → 약 2,240 (옛 키를 지운 빈자리는 남고, 새 키는 다른 자리에 끼워 넣느라 블록이 쪼개졌다)

문제

리허설의 redo와 undo, 인덱스 크기가 어디서 나오는지 설명하고, 병렬 DML이 무엇을 줄였고 무엇을 줄이지 못했는지 쓰시오.
칸마다 가명으로 바꾼 행을 새 테이블에 쓰고 그 칸과 맞바꾸는 방법으로 SQL을 작성하시오. 결과가 원본 UPDATE와 같으려면 무엇을 먼저 확인해야 하는지도 쓰시오.
① 직렬 UPDATE, ② 병렬 DML, ③ 칸마다 교환을 경과 시간, redo와 undo, 인덱스, 잠금, 실패했을 때의 상태로 비교하고 2022년 일괄 처리와 매달 처리에 각각 무엇을 쓸지 정하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ③ 칸 하나(P202201)의 순서 · 2022년 12칸은 칸 이름만 바꿔 되풀이하고, 매달 처리도 같은 스크립트를 쓴다

-- (0) 매핑 누락 확인 : 0 이 아니면 멈춘다 (UPDATE 는 누락을 NULL 로 덮어쓰고, 아래 조인은 그 행을 빠뜨린다)
SELECT COUNT(*)
  FROM T_ORD_BIG_P14 PARTITION (P202201) o
 WHERE NOT EXISTS (SELECT 1 FROM T_CUS_PSN m WHERE m.CUS_ID = o.CUS_ID);

-- (1) 칸과 구조가 똑같은 빈 테이블
CREATE TABLE T_P14_X202201 FOR EXCHANGE WITH TABLE T_ORD_BIG_P14;

-- (2) 가명으로 바꾼 행을 직접 경로로 쓴다 (테이블 데이터의 undo 가 거의 없다)
INSERT /*+ APPEND */ INTO T_P14_X202201 (ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT)
SELECT o.ORD_SEQ, m.PSN_ID, o.ORD_DT, o.ORD_ST, o.ORD_AMT
  FROM T_ORD_BIG_P14 PARTITION (P202201) o, T_CUS_PSN m
 WHERE m.CUS_ID = o.CUS_ID;
COMMIT;

-- (3) 칸의 로컬 인덱스와 같은 모양으로 새로 짓는다 (빈자리 없는 인덱스)
CREATE INDEX T_P14_X202201_I1 ON T_P14_X202201 (CUS_ID, ORD_DT);
CREATE INDEX T_P14_X202201_I2 ON T_P14_X202201 (ORD_DT, ORD_AMT);

-- (4) 맞바꾼다 : 그 칸에서 꺼낸 행이라 범위 밖 행이 없다 · 칸을 고치는 트랜잭션이 있으면 30초까지 기다린다
ALTER SESSION SET DDL_LOCK_TIMEOUT = 30;
ALTER TABLE T_ORD_BIG_P14
  EXCHANGE PARTITION P202201 WITH TABLE T_P14_X202201
  INCLUDING INDEXES WITHOUT VALIDATION;

-- (5) 건수 확인 뒤, 원래 고객번호가 든 옛 칸을 지운다 (휴지통에 남기지 않는다)
SELECT COUNT(*) FROM T_ORD_BIG_P14 PARTITION (P202201);       -- 교환 전과 같아야 한다
DROP TABLE T_P14_X202201 PURGE;

개선 후 실행계획 학습용 예시 · EE 시험 DB 기준 · 병렬 수치는 어림값

xplan

[③ 칸 하나 (P202201 · 27,760행)]
  (0) 누락 확인 0건 · (2) INSERT /*+ APPEND */ 0.4초 · (3) 인덱스 둘 0.3초 · (4) EXCHANGE 0.03초
  redo (ARCHIVELOG · LOGGING) 칸마다 약 3 MB · undo 약 0.03 MB

[2022년 12칸 · 세 방법 비교]
                          ① 직렬 UPDATE         ② 병렬 DML              ③ 칸마다 교환
  경과                     57.8초                14.2초                  약 9초 (12칸 차례로)
  redo                     약 182 MB             약 186 MB               약 36 MB
  undo                     약 96 MB              약 97 MB                약 0.4 MB
  IX_P14_CUS_DT 리프        1,150 → 2,240         1,150 → 2,250           1,150 → 1,150
  잠금                     2022년 33만 행 (커밋까지)   2022년 12칸 배타 (커밋까지)   교환하는 순간 그 칸만
  실패하면                  33만 행 롤백 (undo 96 MB 되감기)   같다          그 칸만 그대로 · 다음 칸부터 다시

[교환 뒤 · 2022년 1월 한 고객(가명 ID) 조회]
---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |               |      1 |        |     14 | 00:00:00.01 |      17 |        |       |
|   1 |  PARTITION RANGE SINGLE                     |               |      1 |     14 |     14 | 00:00:00.01 |      17 |      1 |     1 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | T_ORD_BIG_P14 |      1 |     14 |     14 | 00:00:00.01 |      17 |      1 |     1 |
|*  3 |    INDEX RANGE SCAN                         | IX_P14_CUS_DT |      1 |     14 |     14 | 00:00:00.01 |       3 |      1 |     1 |
---------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("CUS_ID"=:V_PSN AND "ORD_DT">=TO_DATE(' 2022-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "ORD_DT"<TO_DATE(' 2022-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

모범 답안 서술

UPDATE의 일은 행마다 생긴다. 333,120행마다 테이블 블록을 고치고, CUS_ID가 선두 컬럼인 IX_P14_CUS_DT에서는 옛 키를 지우고 새 키(PSN_…)를 다른 자리에 끼워 넣는다. 고치기 전 값은 undo에, 그 변경은 다시 redo에 적는다. 그래서 UPDATE 줄의 Buffers가 1,421K이고 redo 약 182MB, undo 약 96MB가 나왔다. 인덱스는 옛 키를 지운 빈자리가 남은 채로 새 키를 넣느라 블록이 쪼개져 2022년 칸의 리프가 약 1,150 → 2,240블록으로 커졌다. 병렬 DML은 이 일을 PX 서버 여덟이 나눠 했을 뿐이다. 경과 시간은 57.8 → 14.2초로 줄었지만 redo(약 186MB), undo(약 97MB), 블록 변경 수와 인덱스 크기는 직렬과 같다. 병렬은 시간을 줄이고 일의 양은 줄이지 않는다. 게다가 병렬 DML은 커밋할 때까지 대상 칸 12개를 배타로 잠가, 그동안 고객센터의 2022년 주문 반품 처리가 기다렸다. ③은 행을 고치지 않고 칸을 새로 쓴다. 칸마다 구조가 같은 빈 테이블(FOR EXCHANGE WITH TABLE)에 그 칸의 행을 가명 매핑과 조인해 INSERT /*+ APPEND */로 쓰고, 칸의 로컬 인덱스 둘과 같은 인덱스를 새로 지은 뒤 EXCHANGE PARTITION … INCLUDING INDEXES로 맞바꾼다. 직접 경로라 테이블 데이터의 undo가 거의 없고, redo는 새 블록과 새 인덱스 몫(칸마다 약 3MB, 12칸 약 36MB)뿐이다. 인덱스는 처음부터 다시 지으므로 빈자리 없이 1,150블록 그대로다. 교환은 사전만 바꾸므로 잠금은 교환하는 순간 그 칸에만 필요하다. 칸을 고치는 트랜잭션이 열려 있으면 DDL_LOCK_TIMEOUT만큼 기다린다. 실패해도 그 칸은 손대지 않은 채 남고 다음 칸부터 다시 돌리면 된다. 결과가 원본 UPDATE와 같으려면 먼저 매핑 누락을 확인해야 한다. 스칼라 서브쿼리 UPDATE는 매핑이 없는 고객을 에러 없이 NULL로 덮어쓰고, ③의 조인은 그 주문을 빠뜨린다. 둘 다 조용히 틀리므로 (0)의 누락 건수가 0인지 보고, 교환 전후 칸 건수를 맞춘다. 교환 뒤 교환용 테이블에는 원래 고객번호가 든 옛 칸이 들어 있으므로 건수를 확인한 뒤 DROP TABLE … PURGE로 휴지통에 남기지 않고 지운다. 그래서 2022년 일괄 처리는 ③을 칸마다 되풀이한다(약 9초, redo 182 → 36MB, undo 96 → 0.4MB). 매달 처리도 칸 하나에 같은 스크립트를 쓴다. 한 칸의 대부분 행을 고치는 일이므로 UPDATE보다 교환이 싸고, 해마다 같은 방식이면 운영도 단순하다. UPDATE는 칸의 일부 행만 고칠 때(예를 들어 탈퇴 고객 몇 명만 가명 처리) 쓴다.

해설

병렬의 쓰임새: 병렬 DML은 작업 창이 모자랄 때 시간을 사는 방법이다. redo, undo, 인덱스 갱신 같은 일의 양은 그대로이고, PX 서버만큼 CPU를 한꺼번에 쓴다. 켜는 법과 부작용은 DML-043을 본다.
③이 맞는 조건: 칸 하나의 대부분 행을 바꿀 때다. 고칠 행이 칸의 몇 %뿐이라면 칸 전체를 다시 쓰는 비용이 더 크다.
교환하면 그 칸의 통계도 교환용 테이블의 통계로 바뀐다. 빈 테이블에 APPEND로 넣을 때 모은 통계가 칸 통계가 되고, 테이블 전체 통계는 따로 다시 모은다(INCREMENTAL 통계를 쓰면 바뀐 칸만 다시 계산한다).
가명 처리라는 목적: UPDATE는 원래 고객번호를 undo와 아카이브된 redo(182MB)에 남긴다. ③은 원래 값을 다시 기록하지 않고, 옛 칸은 PURGE로 지운다. 백업에 남은 옛 블록은 보관 정책으로 따로 다룬다.
큰 UPDATE의 undo가 커지면 같은 시각의 조회가 ORA-01555를 만나기도 한다(교재 13화). ③은 칸 단위로 끊겨 이 위험도 작다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · EE 시험 DB 기준 · 병렬 수치는 어림값 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · EE 시험 DB 기준 · 병렬 수치는 어림값 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황