요일 칸 몇천 건을 바로잡는 보정 UPDATE가 매주 50만 건을 고친다

기초UPDATE유통444 / 700

상황

본사 레거시 유통에서는 매주 월요일 새벽에 매장 POS 판매를 CF10으로 이관한다. 매장 두 곳의 POS가 요일 칸(SLE_DOW)을 비워 보내거나 틀리게 보내서, 이관이 끝나면 운영 담당이 요일 보정 SQL을 돌린다. 보정 한 번에 18초가 걸리고 redo가 약 118MB씩 생겨서, 월요일마다 아카이브 로그 공간 경보가 울린다. 운영 담당: "틀린 건 매주 3천 건 남짓인데, 로그는 왜 이렇게 많이 쌓이죠?"

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · SLE_DT VARCHAR2(8) · SLE_DOW VARCHAR2(1)(일요일 '1' ~ 토요일 '7', NULL 허용)
이번 주 이관 뒤 SLE_DOW가 NULL인 행 2,840건, 값이 틀린 행 312건. 나머지 496,848행은 이미 맞다.
인덱스는 CF10_PK (SLE_NO)뿐이다. 트리거는 없다. DB는 ARCHIVELOG 모드다.
보정 세션의 NLS 설정은 이관 프로그램과 같다('D' 형식이 일요일을 1로 낸다).

원본 SQL

sql

-- 매주 월요일 이관 뒤 · 요일 보정 (운영 담당)
UPDATE CF10
   SET SLE_DOW = TO_CHAR(TO_DATE(SLE_DT, 'YYYYMMDD'), 'D');
COMMIT;

실행계획 학습용 예시

xplan

[세션 통계 — 보정 UPDATE 한 번]
  경과 시간                  18.4초
  갱신된 행                  500,000 (값이 실제로 바뀐 행 3,152)
  redo size                  약 118 MB
  undo change vector size    약 41 MB
  db block changes           약 1,010,000

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |      |      1 |        |      0 |00:00:18.40 |    517K |
|   1 |  UPDATE            | CF10 |      1 |        |      0 |00:00:18.40 |    517K |
|   2 |   TABLE ACCESS FULL| CF10 |      1 |    500K|    500K|00:00:00.21 |    4583 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   (없음 — WHERE 절이 없다)

문제

값이 이미 맞는 496,848행에도 redo와 undo가 생기는 이유를 설명하시오.
결과가 바뀌지 않게, 값이 바뀌는 행만 고치도록 SQL을 고치시오. NULL인 행이 빠지지 않게 하시오.

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

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

모범답안

모범 답안 SQL

sql

UPDATE CF10
   SET SLE_DOW = TO_CHAR(TO_DATE(SLE_DT, 'YYYYMMDD'), 'D')
 WHERE SLE_DOW IS NULL                                           -- 비어 온 2,840행
    OR SLE_DOW <> TO_CHAR(TO_DATE(SLE_DT, 'YYYYMMDD'), 'D');      -- 틀린 312행
COMMIT;

-- 같은 답: WHERE DECODE(SLE_DOW, TO_CHAR(TO_DATE(SLE_DT, 'YYYYMMDD'), 'D'), 0, 1) = 1

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

xplan

[세션 통계 — 같은 주]
  경과 시간 0.29초 · 갱신된 행 3,152 · redo 약 0.8 MB · undo 약 0.3 MB

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |      |      1 |        |      0 |00:00:00.29 |    7921 |
|   1 |  UPDATE            | CF10 |      1 |        |      0 |00:00:00.29 |    7921 |
|*  2 |   TABLE ACCESS FULL| CF10 |      1 |  25000 |   3152 |00:00:00.25 |    4583 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("SLE_DOW" IS NULL OR "SLE_DOW"<>TO_CHAR(TO_DATE("SLE_DT",'YYYYMMDD'),'D')))

모범 답안 서술

원본에는 WHERE 절이 없어서 50만 행 전부가 갱신 대상이다(2번 줄 A-Rows 500K). 오라클의 UPDATE는 새 값이 옛 값과 같은지 따지지 않는다. 고칠 행으로 잡히면 행마다 잠금(TX)을 걸고, 바꾸기 전 값을 undo에, 바꾼 내용을 redo에 적는다. 값이 이미 맞는 496,848행도 똑같이 고쳐서 redo 약 118MB, undo 약 41MB, 블록 변경 약 101만이 생긴다. 1번 줄 Buffers 517K 가운데 테이블을 읽은 몫은 4,583블록뿐이고 나머지는 행을 고치는 데 든 읽기다. 그래서 WHERE에 '값이 바뀌는 행' 조건을 더한다. SLE_DOW <> 계산한 요일만 쓰면 NULL인 2,840행이 빠진다. NULL과의 비교는 참도 거짓도 아닌 UNKNOWN이라 조건에 걸리지 않기 때문이다. 그래서 SLE_DOW IS NULL OR …로 따로 챙기거나, NULL끼리 같다고 보는 DECODE로 비교한다. 읽는 양(FULL 4,583블록)은 그대로지만 고치는 행이 500,000 → 3,152로 줄어, Buffers는 517K → 7,921, redo는 118MB → 0.8MB, 경과 시간은 18.4초 → 0.3초가 된다. 결과는 원본과 같다. 원본이 같은 값으로 덮던 행을 이제 건드리지 않을 뿐이다.

해설

함정: WHERE SLE_DOW <> TO_CHAR(…)만 붙이면 틀린 312행만 고치고 NULL인 2,840행은 그대로 둔다. NULL끼리 같다고 보는 비교가 필요하면 DECODE(a, b, 0, 1) = 1이 가장 짧다. 계산한 요일은 NULL이 될 수 없으므로 이 문제에서는 LNNVL(SLE_DOW = …)도 맞다.
'D' 형식은 NLS_TERRITORY를 따른다. 설정이 다른 세션에서 돌리면 한 주의 첫날이 달라져 숫자가 밀리고, 50만 행 전부가 '바뀌는 행'이 되면서 값도 틀어진다. 설정에 기대지 않으려면 MOD(TO_DATE(SLE_DT, 'YYYYMMDD') - DATE '1900-01-07', 7) + 1처럼 일요일 하나를 기준으로 계산한다(1900년 1월 7일은 일요일이다).
인덱스로는 줄일 수 없다. '계산한 값과 다른 행'은 인덱스로 찾을 수 있는 조건이 아니므로 FULL 4,583블록은 남는다. 줄어드는 것은 고치는 일(잠금, undo, redo)이다.
같은 값으로 덮는 UPDATE도 잠금은 똑같이 건다. 보정이 도는 18초 동안 그 행들을 고치려는 다른 세션은 기다린다(교재 13화의 redo와 undo).
근본 처방은 이관 단계에서 요일을 바로 계산해 넣거나, 요일을 저장하지 않고 SLE_DT에서 계산하는 것이다. 보정 SQL이 매주 필요하다면 원천이 계속 틀리고 있다는 뜻이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황