PART-033

결제 완료 처리가 ORA-14402로 막혀 ROW MOVEMENT를 켰더니 결제 한 건의 redo가 세 배가 되었다

중급UPDATE한빛유통408 / 700

상황

상담원 화면의 「결제 대기 목록」은 5분마다 새로 고친다. 이 화면을 빠르게 하려고 지난달 주문 테이블을 상태별 LIST 파티션 복사본 T_ORD_LS로 옮겼다. 결제 대기(PENDING) 칸만 읽으면 되기 때문이다. 옮긴 첫날 결제 완료 처리가 모두 ORA-14402로 실패했고, 조민우가 ENABLE ROW MOVEMENT로 급히 막았다. 그 뒤로 에러는 없지만 결제 처리가 1.1ms에서 2.9ms로 늘고 redo가 세 배가 되었다. 금요일 타임세일에는 5분 동안 결제가 약 1만 건 몰린다. 상담원들은 "금액 정정을 눌렀는데 가끔 저장되지 않는다"고 한다. 상담원 화면은 목록을 띄울 때 받아 둔 ROWID로 금액을 고친다(오래된 프레임워크의 기본 동작이다).

테이블과 인덱스

T_ORD_LS 주문(파티션 복사본, 문제 설정): T_ORD 100,000행을 PARTITION BY LIST (ORD_ST) 세 칸으로 나눴다. P_PENDING 8,000행(약 72블록) · P_COMPLETE 90,000행(약 806블록) · P_CANCEL 2,000행(약 18블록)
주문은 PENDING으로 들어와 결제되면 COMPLETE가 되고, 결제되지 않은 채 오래된 주문은 야간 배치가 CANCEL로 바꾼다. ORD_DT는 주문 시각(시분초 있음)이고 2023년 1월 ~ 2024년 12월이다.
PK PK_T_ORD_LS (ORD_SEQ)는 파티션 키가 없어 GLOBAL이다. 다른 인덱스는 없다. 통계는 최신이다.
「결제 대기 목록」은 최근 사흘의 결제 대기 주문(약 33건)을 보여 준다.

원본 SQL

sql

CREATE TABLE T_ORD_LS
PARTITION BY LIST (ORD_ST)
( PARTITION P_PENDING  VALUES ('PENDING'),
  PARTITION P_COMPLETE VALUES ('COMPLETE'),
  PARTITION P_CANCEL   VALUES ('CANCEL') )
AS SELECT * FROM T_ORD;
ALTER TABLE T_ORD_LS ADD CONSTRAINT PK_T_ORD_LS PRIMARY KEY (ORD_SEQ);
ALTER TABLE T_ORD_LS ENABLE ROW MOVEMENT;          -- 옮긴 첫날 ORA-14402 를 막으려고

-- 결제 완료 처리 (결제마다)
UPDATE T_ORD_LS
   SET ORD_ST = 'COMPLETE', PAY_DT = SYSDATE, PAY_TP = :pay_tp, PAY_AMT = :pay_amt
 WHERE ORD_SEQ = :ord_seq;

-- 「결제 대기 목록」 (상담원 화면 · 5분마다 · :v_from = 사흘 전 0시)
SELECT ROWID AS rid, ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
  FROM T_ORD_LS
 WHERE ORD_ST = 'PENDING'
   AND ORD_DT >= :v_from
 ORDER BY ORD_DT;

-- 상담원 금액 정정
UPDATE T_ORD_LS SET ORD_AMT = :amt WHERE ROWID = :rid;

실행계획 학습용 예시

xplan

[옮긴 첫날 09:00 · 결제 완료 처리 · ROW MOVEMENT 를 켜기 전]
  ORA-14402: updating partition key column would cause a partition change

[지금 · 결제 완료 처리 한 건]
--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                     |             |      1 |        |      0 | 00:00:00.01 |      14 |        |       |
|   1 |  UPDATE                              | T_ORD_LS    |      1 |        |      0 | 00:00:00.01 |      14 |        |       |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID | T_ORD_LS    |      1 |      1 |      1 | 00:00:00.01 |       3 |  ROWID | ROWID |
|*  3 |    INDEX UNIQUE SCAN                 | PK_T_ORD_LS |      1 |      1 |      1 | 00:00:00.01 |       2 |        |       |
--------------------------------------------------------------------------------------------------------------------------------

   3 - access("ORD_SEQ"=:ORD_SEQ)

[세션 통계 · 결제 1,000건]
                                옮기기 전 T_ORD    지금 T_ORD_LS (ROW MOVEMENT)
  redo size                          0.41 MB             1.32 MB
  undo change vector size            0.13 MB             0.47 MB
  db block changes                     4,060              11,930
  결제 처리 평균                        1.1 ms              2.9 ms

[「결제 대기 목록」]
----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |      1 |        |     33 | 00:00:00.01 |      72 |        |       |
|   1 |  SORT ORDER BY          |          |      1 |     33 |     33 | 00:00:00.01 |      72 |        |       |
|   2 |   PARTITION LIST SINGLE |          |      1 |     33 |     33 | 00:00:00.01 |      72 |      1 |     1 |
|*  3 |    TABLE ACCESS FULL    | T_ORD_LS |      1 |     33 |     33 | 00:00:00.01 |      72 |      1 |     1 |
----------------------------------------------------------------------------------------------------------------

   3 - filter("ORD_DT">=:V_FROM)

[상담원 금액 정정 · 그사이 결제된 주문]
  UPDATE T_ORD_LS SET ORD_AMT = :amt WHERE ROWID = :rid    → 0 행 (에러 없음)

문제

ORA-14402가 난 까닭과, ROW MOVEMENT를 켠 뒤 결제 한 건이 무거워지고 금액 정정이 가끔 0행을 고치는 까닭을 설명하시오.
「결제 대기 목록」을 빠르게 하려던 목적은 지키면서 이 문제가 생기지 않는 설계를 DDL과 SQL로 쓰시오.
고친 뒤 결제 처리와 「결제 대기 목록」의 숫자가 어떻게 바뀌는지 어림하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 칸 기준을 '한 번 들어오면 바뀌지 않는 값'(주문일)으로 다시 짓는다 (월 정리에도 그대로 쓴다)
CREATE TABLE T_ORD_RP
STORAGE (INITIAL 64K)
PARTITION BY RANGE (ORD_DT) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION P202301 VALUES LESS THAN (DATE '2023-02-01') )
AS SELECT * FROM T_ORD_LS;

ALTER TABLE T_ORD_RP ADD CONSTRAINT PK_T_ORD_RP PRIMARY KEY (ORD_SEQ);      -- GLOBAL

-- 결제 대기 행만 담는 함수 기반 인덱스 (결제되거나 취소되면 식이 NULL 이 되어 키가 빠진다)
CREATE INDEX IX_T_ORD_RP_PEND
    ON T_ORD_RP (CASE WHEN ORD_ST = 'PENDING' THEN ORD_DT END) LOCAL;

-- 「결제 대기 목록」 : 인덱스와 같은 식을 쓰고, 칸을 고를 주문일 조건도 준다
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
  FROM T_ORD_RP
 WHERE CASE WHEN ORD_ST = 'PENDING' THEN ORD_DT END >= :v_from
   AND ORD_DT >= :v_from
 ORDER BY ORD_DT;

-- 결제 완료 처리 : 그대로 (칸이 바뀌지 않으므로 행 이동이 없다)
UPDATE T_ORD_RP
   SET ORD_ST = 'COMPLETE', PAY_DT = SYSDATE, PAY_TP = :pay_tp, PAY_AMT = :pay_amt
 WHERE ORD_SEQ = :ord_seq;

-- 상담원 금액 정정 : ROWID 대신 PK 로 고친다
UPDATE T_ORD_RP SET ORD_AMT = :amt WHERE ORD_SEQ = :ord_seq;

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

xplan

[결제 완료 처리 한 건]
--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                     |             |      1 |        |      0 | 00:00:00.01 |       6 |        |       |
|   1 |  UPDATE                              | T_ORD_RP    |      1 |        |      0 | 00:00:00.01 |       6 |        |       |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID | T_ORD_RP    |      1 |      1 |      1 | 00:00:00.01 |       3 |  ROWID | ROWID |
|*  3 |    INDEX UNIQUE SCAN                 | PK_T_ORD_RP |      1 |      1 |      1 | 00:00:00.01 |       2 |        |       |
--------------------------------------------------------------------------------------------------------------------------------

[세션 통계 · 결제 1,000건]  redo 1.32 → 약 0.52 MB · db block changes 11,930 → 약 5,100 · 평균 2.9 → 약 1.2 ms

[「결제 대기 목록」 · :v_from = 사흘 전 0시]
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name             | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart |   Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                  |      1 |        |     33 | 00:00:00.01 |      33 |        |         |
|   1 |  SORT ORDER BY                               |                  |      1 |     33 |     33 | 00:00:00.01 |      33 |        |         |
|   2 |   PARTITION RANGE ITERATOR                   |                  |      1 |     33 |     33 | 00:00:00.01 |      33 |    KEY | 1048575 |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | T_ORD_RP         |      1 |     33 |     33 | 00:00:00.01 |      33 |    KEY | 1048575 |
|*  4 |     INDEX RANGE SCAN                         | IX_T_ORD_RP_PEND |      1 |     33 |     33 | 00:00:00.01 |       2 |    KEY | 1048575 |
-----------------------------------------------------------------------------------------------------------------------------------------------

   3 - filter("ORD_DT">=:V_FROM)
   4 - access("T_ORD_RP"."SYS_NC00009$">=:V_FROM)

  (실행 때 연 칸 — 이번 달 칸 하나 · 월초 사흘은 두 칸)

모범 답안 서술

옮긴 첫날의 ORA-14402는 칸 기준 때문이다. T_ORD_LS는 ORD_ST 값으로 칸이 정해지는데, 결제 완료 처리는 그 값을 PENDING에서 COMPLETE로 바꾼다. 바뀐 값의 행은 P_PENDING이 아니라 P_COMPLETE에 있어야 하므로, 행 이동이 꺼져 있으면 오라클은 UPDATE를 거부한다. ENABLE ROW MOVEMENT를 켜면 에러는 사라지지만 UPDATE가 실제로는 지우고 다시 넣는 일이 된다. P_PENDING에서 행을 지우고 P_COMPLETE에 새로 넣으며, 그 행의 ROWID가 바뀌므로 GLOBAL PK의 키도 지우고 다시 넣는다. 결제 1,000건의 redo가 0.41 → 1.32MB, 블록 변경이 4,060 → 11,930으로 늘고 결제 처리가 1.1 → 2.9ms가 된 것이 그 몫이다(UPDATE 줄 Buffers 14). ROWID가 바뀌는 것은 성능만의 문제가 아니다. 상담원 화면은 목록을 띄울 때 받아 둔 ROWID로 금액을 고친다. 그사이 결제된 주문은 다른 칸으로 옮겨 가서 옛 ROWID 자리에 행이 없으므로, WHERE ROWID = :rid는 에러 없이 0행을 고친다. "금액 정정이 가끔 저장되지 않는다"는 문의가 그것이다. 칸 기준은 행이 들어온 뒤 바뀌지 않는 값으로 고른다. 주문에서는 주문일(ORD_DT)이 그렇고, 월 정리에도 그대로 쓸 수 있다. 결제 대기 목록을 빠르게 하는 일은 칸이 아니라 인덱스에 맡긴다. CASE WHEN ORD_ST = 'PENDING' THEN ORD_DT END에 함수 기반 인덱스를 만들면 PENDING 행만 키가 되고(결제되거나 취소되면 식이 NULL이 되어 키가 빠진다), 인덱스는 결제 대기 행 수만큼만 작다. 목록 SQL은 인덱스와 같은 식으로 쓰고, 칸을 고를 ORD_DT 조건도 함께 준다. 상담원 화면은 ROWID 대신 PK(ORD_SEQ)로 고치게 바꾼다. 고친 뒤 결제 완료 처리는 행이 칸을 옮기지 않는 보통 UPDATE가 되어, 결제 1,000건의 redo가 1.32MB → 약 0.52MB(함수 기반 인덱스의 키를 하나 지우는 몫이 원본보다 더해진다), 처리 시간은 약 1.2ms가 된다. 「결제 대기 목록」은 이번 달 칸 하나에서 인덱스로 33행만 찾아 72 → 약 33블록이다.

해설

상태 값으로 나눠도 되는 경우: 한 번 쓰고 고치지 않는 이력 행(주문상태이력의 상태 코드 같은)은 칸 기준이 되어도 된다. 행이 칸을 옮길 일이 없기 때문이다(PART-038). 고치는 행의 상태는 칸 기준이 될 수 없다.
ROW MOVEMENT는 드물게 키 값을 바로잡을 때 켜는 것이다(PART-023의 월 정정). 모든 결제가 칸을 옮기는 설계를 덮는 데 쓰면 결제마다 행 이동 값을 치른다.
ROWID를 들고 있다가 고치는 프로그램은 행 이동뿐 아니라 SHRINK, MOVE, 칸 DDL 뒤에도 엉뚱하게 0행을 고칠 수 있다(교재 05화의 SHRINK도 ROW MOVEMENT를 켠다). 화면은 PK로 고치게 한다.
함수 기반 인덱스는 SQL의 식이 인덱스의 식과 똑같아야 쓴다. (ORD_ST, ORD_DT) 결합 인덱스도 답이지만, 모든 행을 담으므로 PENDING만 담는 인덱스보다 크고 결제마다 키가 바뀐다.
T_ORD는 10만 행(약 7MB)이라 칸을 나누지 않고 원본에 함수 기반 인덱스만 더해도 된다. 칸을 나눈다면 월 정리 단위인 주문일로 나눈다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황