배치 서버를 옮기는 리허설에서 1년 지난 주문만 지우던 배치가 주문 100만 건을 다 지웠다

중급DELETE버거29 / 700

상황

버거원 본사는 월말마다 1년이 지난 주문을 정보계로 넘긴 뒤 운영 DB에서 지운다. 배치 서버를 새 장비로 옮기면서 복제 DB에서 리허설을 했는데, 삭제 단계가 주문 1,000,000건을 지웠다고 찍혔다. 예전 서버에서 같은 기준일로 돌리면 약 50만 건이다. 배치 코드는 한 줄도 바꾸지 않았다. 바뀐 것은 서버의 언어 설정이다. 예전 서버의 배치 계정에는 NLS_DATE_FORMAT=YYYYMMDD가 환경 변수로 걸려 있었고, 새 서버에는 NLS_LANG=AMERICAN_AMERICA.AL32UTF8만 걸려 있어 NLS_DATE_FORMAT이 기본값 DD-MON-RR이다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_DT VARCHAR2(8) ('20120101' ~ '20131231', 731일 · 하루 약 1,368건)
인덱스 ORD_PK (ORD_NO) · ORD_X01 (ORD_DT, ORD_HMS) · 통계 최신
배치는 기준일 :p_base_dt(DATE)를 받는다. 이번 리허설의 기준일은 2013-12-31이다

원본 SQL

sql

-- 기준일로부터 1년 이전의 주문을 지운다 (정보계 이관은 앞 단계에서 끝났다)
DELETE FROM ORD
 WHERE ORD_DT < TO_CHAR(ADD_MONTHS(:p_base_dt, -12))

실행계획 학습용 예시

xplan

[예전 서버 · NLS_DATE_FORMAT = YYYYMMDD]
TO_CHAR(ADD_MONTHS(DATE '2013-12-31', -12))  →  '20121231'
삭제 499,320건 (2012-01-01 ~ 2012-12-30)

[새 서버 · NLS_DATE_FORMAT = DD-MON-RR]
TO_CHAR(ADD_MONTHS(DATE '2013-12-31', -12))  →  '31-DEC-12'
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |      |      1 |        |      0 |00:00:38.70 |    3155K|
|   1 |  DELETE            | ORD  |      1 |        |      0 |00:00:38.70 |    3155K|
|*  2 |   TABLE ACCESS FULL| ORD  |      1 |   1000K|   1000K|00:00:00.61 |   22426 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ORD_DT"<TO_CHAR(ADD_MONTHS(:P_BASE_DT,-12)))

문제

새 서버에서 TO_CHAR(ADD_MONTHS(:p_base_dt, -12))가 만든 문자열이 무엇이고, 그 문자열과 ORD_DT를 비교한 결과가 왜 100만 건이 됐는지 설명하시오.
기준일이 2013-11-15였다면 새 서버에서 몇 건이 지워졌을지 따져 보고, 이 배치가 어느 서버에서든 같은 결과를 내도록 고치시오.
이런 사고를 운영 전에 막는 장치를 제안하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 포맷을 밝힌다 : 어느 서버, 어느 NLS에서든 '20121231'
DELETE FROM ORD
 WHERE ORD_DT < TO_CHAR(ADD_MONTHS(:p_base_dt, -12), 'YYYYMMDD');

-- 지우기 전에 같은 조건으로 센다 : 기대 범위(한 해 약 50만 건)를 벗어나면 배치를 멈춘다
-- SELECT COUNT(*) FROM ORD WHERE ORD_DT < TO_CHAR(ADD_MONTHS(:p_base_dt, -12), 'YYYYMMDD');

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

xplan

TO_CHAR(ADD_MONTHS(DATE '2013-12-31', -12), 'YYYYMMDD')  →  '20121231' (NLS와 상관없다)
-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |      |      1 |        |      0 |00:00:19.40 |    1590K|
|   1 |  DELETE            | ORD  |      1 |        |      0 |00:00:19.40 |    1590K|
|*  2 |   TABLE ACCESS FULL| ORD  |      1 |    499K|    499K|00:00:00.58 |   22426 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ORD_DT"<TO_CHAR(ADD_MONTHS(:P_BASE_DT,-12),'YYYYMMDD'))

모범 답안 서술

새 서버의 NLS_DATE_FORMAT은 DD-MON-RR이다. 포맷 없이 쓴 TO_CHAR(날짜)는 세션의 이 형식을 따르므로 2012-12-31을 '31-DEC-12'로 만들었다. 예전 서버에서는 같은 식이 '20121231'이었다. ORD_DT는 문자이므로 이 비교는 날짜 비교가 아니라 문자 비교다. 문자 비교는 첫 글자부터 하나씩 한다. 모든 주문의 ORD_DT는 '2'로 시작하고 '31-DEC-12'는 '3'으로 시작하므로, '2' < '3'에서 이미 모든 행이 조건을 만족한다. 그래서 2013년 주문까지 100만 건이 다 지워졌다. 에러도 경고도 없다. 기준일이 2013-11-15였다면 문자열은 '15-NOV-12'가 된다. 이번에는 '2' > '1'이라 한 건도 지워지지 않는다. 기준일의 일자에 따라 전부 지우거나 하나도 안 지우는 배치가 된다. 둘 다 조용히 틀린다. 고치는 방법은 포맷을 코드에 밝히는 것이다. TO_CHAR(ADD_MONTHS(:p_base_dt, -12), 'YYYYMMDD')는 어느 서버에서든 '20121231'이다. 서버의 NLS를 예전처럼 맞추는 것은 다음 이전 때 같은 사고를 다시 부르는 임시 처방이다. 운영 전에 막는 장치도 둔다. 지우기 전에 같은 조건으로 건수를 세어 기대 범위(한 해 약 50만 건)를 벗어나면 배치를 멈춘다. 리허설에서는 결과 건수를 예전 서버와 비교하는 단계를 체크리스트에 넣는다.

해설

NLS에 기대는 코드의 세 얼굴: 포맷 없는 TO_CHAR(날짜)와 TO_DATE(문자), 그리고 DATE 컬럼과 문자열 리터럴의 비교(ORD_DT >= '2024-06-01')는 모두 세션 NLS에 따라 답이 바뀐다. 에러가 나면 다행이고, 이번처럼 조용히 틀리는 경우가 더 무섭다(교재 03화 §1.2).
문자 날짜 컬럼과 비교할 문자열은 늘 'YYYYMMDD' 같은 컬럼 형식으로 만든다. 길이가 같고 0으로 채웠고 큰 단위가 왼쪽이어야 문자 순서가 날짜 순서다(교재 03화 §2).
인덱스 관점에서도 이 배치는 FULL이 맞다. 두 해 치 주문의 절반을 지우므로 ORD_X01 범위 스캔보다 테이블을 한 번 읽는 편이 싸다. 대량 삭제를 파티션 단위 작업으로 바꾸는 일은 PART, DML 단원의 주제다.
ALTER SESSION SET NLS_DATE_FORMAT을 배치 맨 앞에 넣는 것도 방어가 되지만, 그 줄을 빠뜨린 다른 프로그램은 막지 못한다. 코드에 포맷을 밝히는 것이 먼저다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황