아무도 고치지 않은 테이블을 읽는 아침 추출이 ORA-01555로 죽는다

실전SELECT한빛유통532 / 700

상황

한빛유통의 DW 추출은 매일 06:00에 T_ORD_BIG 100만 행을 파일로 내린다. 네트워크와 파일 쓰기 때문에 약 9분 걸린다. 이번 주 월요일과 목요일에 추출이 06:07 무렵 ORA-01555로 죽었다. 조민우가 확인한 사실은 이렇다. "06시 이후로 T_ORD_BIG을 고친 세션은 하나도 없어요. 다른 사람이 언두를 덮을 일이 없는데요." 두 날 모두 05:20에 「주문 구간 재계산」 배치가 돌았고, 06:00에는 아침 특가가 열렸다.

테이블과 인덱스

T_ORD_BIG 대용량 주문: 1,000,000행 · 5,826블록 · 인덱스 없음 · 이 문제에서는 주문 구간 ORD_GRP(H, M, L)를 더했다
「주문 구간 재계산」 배치(월요일과 목요일 05:20): 최근 1년 주문 약 33만 행의 ORD_GRP를 한 문장으로 다시 계산한다. 5,790블록을 고치고 05:39에 한 번 COMMIT한다.
버퍼 캐시 256MB(32,768블록) · 언두 테이블스페이스 100MB 고정(AUTOEXTEND OFF) · undo_retention 900
06:00에 아침 특가가 열리면 주문과 결제, 적립 트랜잭션이 T_ORD, T_ORD_DET, M_CUS를 고치며 10분에 4만 번 넘게 COMMIT한다. T_ORD_BIG은 건드리지 않는다.
배치가 없는 화요일, 수요일, 금요일에는 추출이 늘 성공한다.

원본 SQL

sql

-- 05:20 「주문 구간 재계산」 배치 (월 · 목)
UPDATE T_ORD_BIG
   SET ORD_GRP = CASE WHEN ORD_AMT >= 100000 THEN 'H'
                      WHEN ORD_AMT >= 30000  THEN 'M' ELSE 'L' END
 WHERE ORD_DT >= ADD_MONTHS(TRUNC(SYSDATE), -12);
COMMIT;

-- 06:00 DW 추출 (커서 하나로 100만 행을 읽어 파일로 쓴다 · 약 9분)
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT, ORD_GRP
  FROM T_ORD_BIG;

실행계획 학습용 예시 · 지연 클린아웃의 통계 모양은 교재 13화 실측

xplan

[시간표 — 목요일]
05:20:02   재계산 배치 UPDATE 시작 (약 33만 행 · 5,790블록)
05:39:47   COMMIT — 커밋하면서 ITL 을 닫은(커밋 SCN 을 적은) 블록은 그때 캐시에 남아 있던 일부뿐
06:00:00   아침 특가 오픈 — T_ORD · T_ORD_DET · M_CUS 트랜잭션 10분에 44,870번 COMMIT
06:00:05   DW 추출 OPEN (읽기 시점 고정) · FULL 로 흘려 읽으며 파일로 쓴다
06:07:31   712,000행째 FETCH → ORA-01555: snapshot too old: rollback segment number 4 with name "_SYSSMU4_1180424173$" too small

[추출 세션 — V$SESSTAT · OPEN 뒤 증가분 (06:07:30)]
  consistent gets                                                  9,214
  redo size                                                      161,240   ← 읽기만 하는데 redo 를 쓴다
  cleanouts only - consistent read gets                            1,786
  immediate (CR) block cleanout applications                       1,786
  transaction tables consistent reads - undo records applied      24,610
  transaction tables consistent read rollbacks                       118
  data blocks consistent reads - undo records applied                  0   ← T_ORD_BIG 데이터를 되돌린 일은 없다

[V$UNDOSTAT — 06:00 ~ 06:10]
  UNDOBLKS 11,920 · TXNCOUNT 44,870 · MAXQUERYLEN 446 · TUNED_UNDORETENTION 300 · UNXPSTEALCNT 214 · SSOLDERRCNT 1

[화요일 — 배치가 없는 날 · 같은 추출]
  redo size 0 · cleanouts only - consistent read gets 0 · transaction tables consistent reads - undo records applied 0 · 9분 12초 성공

문제

06:00 이후 아무도 고치지 않은 T_ORD_BIG을 읽는데 ORA-01555가 난 이유를 세션 통계와 V$UNDOSTAT, 배치가 있는 날만 죽는다는 사실로 설명하시오.
대안을 셋 이상 비교하고 하나를 골라 SQL로 작성하시오. 대안마다 대가와, 고른 대안이 정말 효과가 있는지 확인하는 방법을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 05:20 「주문 구간 재계산」 배치 — 끝에 한 줄을 더한다
UPDATE T_ORD_BIG
   SET ORD_GRP = CASE WHEN ORD_AMT >= 100000 THEN 'H'
                      WHEN ORD_AMT >= 30000  THEN 'M' ELSE 'L' END
 WHERE ORD_DT >= ADD_MONTHS(TRUNC(SYSDATE), -12);
COMMIT;

-- 커밋 직후, 아침 특가 전에 테이블을 한 번 훑어 열린 ITL 을 닫는다 (1 ~ 2초 · redo 조금)
SELECT /*+ FULL(t) */ COUNT(*) FROM T_ORD_BIG t;
-- 확인: 한 번 더 돌려 'cleanouts only - consistent read gets' 증가분이 0 인지 본다
--       0 이 아니면 직접 경로 읽기로 읽은 것이다 → 추출을 스냅샷 사본(CTAS)으로 나누는 대안으로 간다

-- 06:00 DW 추출은 그대로
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT, ORD_GRP
  FROM T_ORD_BIG;

개선 후 실행계획 학습용 예시 · 지연 클린아웃의 통계 모양은 교재 13화 실측

xplan

[고친 뒤 — 다음 목요일 (학습용 예시)]
05:39:47   재계산 배치 COMMIT
05:39:49   청소 읽기 COUNT(*) · 1.6초 · redo size 233,800 · cleanouts only - consistent read gets 2,514
05:39:51   같은 읽기 한 번 더 · redo size 0 · cleanouts only 0   ← 블록이 버퍼 캐시를 거쳐 청소됐다
06:00:05   DW 추출 OPEN
06:09:17   추출 성공 · redo size 0 · cleanouts only 0 · transaction tables consistent reads - undo records applied 0

모범 답안 서술

추출 세션의 통계가 원인을 가리킨다. 읽기만 하는데 redo를 161,240바이트 썼고, cleanouts only - consistent read gets가 1,786이다. 추출이 블록을 읽으면서 남의 트랜잭션이 남긴 열린 ITL을 닫는 일(블록 클린아웃)을 하고 있었다는 뜻이다. 반면 data blocks consistent reads - undo records applied는 0이다. 06:00 이후 T_ORD_BIG의 데이터를 되돌릴 일은 정말 없었다. 배치가 없는 화요일에는 이 숫자들이 모두 0이고 추출도 성공한다. 열린 ITL은 05:20 재계산 배치가 남겼다. 배치는 5,790블록을 고쳤는데, COMMIT할 때 커밋 SCN을 적어 ITL을 닫는 일(커밋 클린아웃)은 그때 버퍼 캐시에 남아 있는 블록에만, 그것도 캐시의 약 10%까지만 한다. 256MB 캐시에서 20분 동안 고친 블록의 상당수는 이미 디스크로 밀려나 있었고, 그 블록들은 ITL이 열린 채 남았다. 이것이 지연된 블록 클린아웃이다. 열린 ITL을 만난 추출은 그 트랜잭션이 자기 읽기 시점(06:00:05)보다 먼저 COMMIT했는지 확인해야 하므로, 언두 세그먼트 헤더의 트랜잭션 테이블을 찾아본다. 그런데 06:00부터 아침 특가가 10분에 44,870번 COMMIT하면서 트랜잭션 테이블의 슬롯을 계속 재사용했다. 그래서 오라클은 트랜잭션 테이블 자체를 언두로 06:00:05의 모습까지 되돌려 보려 했다(transaction tables consistent reads - undo records applied 24,610). 언두가 100MB 고정이라 보존 시간이 300초까지 내려가고 아직 만료되지 않은 언두까지 빼앗긴 상황에서(UNXPSTEALCNT 214) 그 언두를 찾지 못해 ORA-01555가 났다. 아무도 T_ORD_BIG을 고치지 않았어도 다른 테이블의 COMMIT이 언두와 트랜잭션 테이블을 돌린 것이다. 대안은 넷이다. ① 언두를 늘리거나 AUTOEXTEND를 켜거나 RETENTION GUARANTEE를 건다. 트랜잭션 테이블을 되돌릴 언두가 오래 남아 01555는 드물어지지만, 디스크가 늘고 GUARANTEE는 공간이 모자랄 때 아침 특가의 DML을 ORA-30036으로 실패하게 한다. 원인인 열린 ITL은 그대로다. ② 배치 직후에 테이블을 한 번 훑어 청소한다. COUNT(*) 한 번에 1 ~ 2초와 약간의 redo가 들 뿐이고, 06:00 전에 모든 ITL이 닫히므로 추출은 트랜잭션 테이블을 찾아볼 일이 없다. ③ 추출을 두 단계로 나눈다. 06:00:05에 CTAS로 몇 초 만에 스냅샷 사본을 만들고 9분짜리 파일 쓰기는 사본을 읽는다. 읽기 시점을 지켜야 하는 시간이 몇 초로 줄지만 45MB 공간과 단계 하나가 는다. ④ 추출을 배치 직후, 아침 특가 전으로 옮긴다. 하루 일정에 여유가 없으면 쓸 수 없다. ②를 고른다. 원인을 만든 배치 쪽에서 가장 적은 비용으로 원인을 없앤다. 효과는 숫자로 확인한다. 청소 읽기를 한 번 더 돌려 cleanouts only 증가분이 0이면 블록이 버퍼 캐시에서 청소된 것이다. 큰 테이블의 FULL이 직접 경로 읽기(direct path read)로 가면 PGA로 읽은 사본만 청소되고 블록에는 남지 않으므로 두 번째 읽기에서도 숫자가 그대로 나온다. 그때는 ③으로 간다. 추출 세션의 redo size와 transaction tables consistent reads 증가분이 0인지도 함께 본다.

해설

교재 13화가 정리한 ORA-01555의 세 원인 가운데, 지연 클린아웃 중에 트랜잭션 테이블을 되돌리지 못한 경우다. 데이터 블록을 되돌린 통계(data blocks consistent reads)가 0인데 01555가 났다면 이쪽을 의심한다.
직접 경로로 쓴 블록은 깨끗하다. 배치가 UPDATE 대신 CTAS나 INSERT /*+ APPEND */로 새로 쓰는 구조라면 열린 ITL이 남지 않는다(교재 13화의 T_ORD_BIG 적재 재측정). 33만 행 UPDATE를 그렇게 바꾸는 것은 비용이 크므로 여기서는 청소 읽기가 낫다.
배치 끝에 통계 수집을 붙여 청소를 겸하려는 생각도 흔하다. 하지만 표본만 읽거나 직접 경로로 읽으면 모든 블록을 청소하지 못하므로, 청소가 목적이면 버퍼 캐시를 거치는 FULL 읽기를 따로 두고 숫자로 확인한다.
지연 클린아웃은 01555가 아니어도 비용이 있다. 배치 다음 날 아침 첫 조회가 유난히 느리고 SELECT가 redo를 쓰는 현상이 같은 원인이다(교재 13화 실측: 첫 읽기 redo 152,888바이트, 둘째 2,076바이트).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 지연 클린아웃의 통계 모양은 교재 13화 실측 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 지연 클린아웃의 통계 모양은 교재 13화 실측 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황