본사 반기 리포트가 새벽에는 31분에 끝나는데 낮에 돌리면 ORA-01555로 죽는다

중급SELECT버거531 / 700

상황

버거원 본사 영업기획팀의 「매장별 상품 판매」 반기 리포트(2013년 하반기)는 원래 새벽 02:00에 돌고 31분 걸린다. 지난주 화요일 급한 보고 때문에 14:00에 돌렸더니 23분 만에 ORA-01555: snapshot too old로 죽었다. 두 번 더 돌렸지만 두 번 다 20분을 넘기고 죽었다. DBA는 언두 테이블스페이스를 두 배로 늘리자고 했다. 리포트 SQL 맨 위에는 "NL 고정. 매장별로 읽어야 빠름(2012.03, 매장 30곳)"이라는 주석과 힌트가 있다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_DT VARCHAR2(8) · 2013년 하반기 약 252,000건 · 인덱스 ORD_X01 (ORD_DT, ORD_HMS), 이 문제에서 더한 ORD_X02 (SHOP_NO, ORD_DT)
ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) · ORD_DT는 주문의 ORD_DT를 그대로 복사한 칸이다 · 2013년 하반기 약 623,000행
SHOP 매장: 200행 · 5블록
영업 중에는 반품과 수량 정정 화면이 지난 주문의 ORD, ORD_ITEM 행을 분당 수십 건 고치고 COMMIT한다. 새벽에는 거의 없다.
언두 테이블스페이스 4GB 고정(AUTOEXTEND OFF) · undo_retention 900 · 버퍼 캐시 1GB

원본 SQL

sql

-- 「매장별 상품 판매」 반기 리포트
-- NL 고정. 매장별로 읽어야 빠름 (2012.03, 매장 30곳)
SELECT /*+ LEADING(s o i) USE_NL(o) USE_NL(i) INDEX(o ORD_X02) */
       s.SHOP_NO, s.SHOP_NM, i.ITEM_ID,
       SUM(i.ORD_ITEM_QTY) AS QTY
  FROM SHOP s, ORD o, ORD_ITEM i
 WHERE o.SHOP_NO = s.SHOP_NO
   AND o.ORD_DT BETWEEN '20130701' AND '20131231'
   AND i.ORD_NO = o.ORD_NO
 GROUP BY s.SHOP_NO, s.SHOP_NM, i.ITEM_ID;

실행계획 학습용 예시

xplan

[새벽 02:00 — 끝까지 돈 실행 · ALLSTATS LAST]
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name        | Starts | E-Rows | A-Rows |    A-Time   | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |             |      1 |        |  18210 | 00:31:04.12 |   1265K |  412K |
|   1 |  HASH GROUP BY                          |             |      1 |  18400 |  18210 | 00:31:04.12 |   1265K |  412K |
|   2 |   NESTED LOOPS                          |             |      1 |   618K |   623K | 00:30:58.40 |   1265K |  412K |
|   3 |    NESTED LOOPS                         |             |      1 |   250K |   252K | 00:09:12.77 |    253K |  121K |
|   4 |     TABLE ACCESS FULL                   | SHOP        |      1 |    200 |    200 | 00:00:00.01 |       5 |     0 |
|   5 |     TABLE ACCESS BY INDEX ROWID BATCHED | ORD         |    200 |   1250 |   252K | 00:09:12.70 |    253K |  121K |
|*  6 |      INDEX RANGE SCAN                   | ORD_X02     |    200 |   1250 |   252K | 00:00:01.90 |    1420 |   410 |
|   7 |    TABLE ACCESS BY INDEX ROWID          | ORD_ITEM    |   252K |      2 |   623K | 00:21:40.10 |   1012K |  291K |
|*  8 |     INDEX RANGE SCAN                    | ORD_ITEM_PK |   252K |      2 |   623K | 00:06:02.30 |    758K |   97K |
--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access("O"."SHOP_NO"="S"."SHOP_NO" AND "O"."ORD_DT">='20130701' AND "O"."ORD_DT"<='20131231')
   8 - access("I"."ORD_NO"="O"."ORD_NO")

[14:00 — 알림 로그]
ORA-01555 caused by SQL statement below (SQL ID: 4hq7d2w9n3c8k, Query Duration=1380 sec, SCN: 0x00000000b71c04e2):
SELECT /*+ LEADING(s o i) USE_NL(o) USE_NL(i) INDEX(o ORD_X02) */ S.SHOP_NO, S.SHOP_NM, I.ITEM_ID, SUM(I.ORD_ITEM_QTY) …
  같은 날 세 번: 14:00 → 14:23 · 14:25 → 14:47 · 14:50 → 15:12

[V$UNDOSTAT — 10분 구간]
구간            UNDOBLKS   TXNCOUNT   MAXQUERYLEN   TUNED_UNDORETENTION   UNXPSTEALCNT   SSOLDERRCNT
02:00 ~ 02:40      3,120        410         1,864                21,400              0             0
14:00 ~ 15:10    418,200     61,400         1,380                 1,040              0             3

문제

같은 SQL이 새벽에는 끝나고 낮에는 ORA-01555로 죽는 이유를 실행계획과 알림 로그, V$UNDOSTAT으로 설명하시오. 이 리포트는 아무것도 고치지 않는데 왜 언두가 필요한지도 쓰시오.
낮에 돌려도 죽지 않도록 고치시오. DBA의 "언두 두 배" 안에도 답하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 2012.03 (매장 30곳)의 NL 고정을 걷어 낸다 — 두 테이블을 한 번씩 훑어 해시 조인
SELECT /*+ LEADING(s o i) USE_HASH(o i) FULL(o) FULL(i) */
       s.SHOP_NO, s.SHOP_NM, i.ITEM_ID,
       SUM(i.ORD_ITEM_QTY) AS QTY
  FROM SHOP s, ORD o, ORD_ITEM i
 WHERE o.SHOP_NO = s.SHOP_NO
   AND o.ORD_DT BETWEEN '20130701' AND '20131231'
   AND i.ORD_NO = o.ORD_NO
   AND i.ORD_DT BETWEEN '20130701' AND '20131231'   -- 복사된 날짜 칸으로 해시 조인에 대는 행을 줄인다
 GROUP BY s.SHOP_NO, s.SHOP_NM, i.ITEM_ID;

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

xplan

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Starts | E-Rows | A-Rows |    A-Time   | Buffers | Reads |  Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |      1 |        |  18210 | 00:00:51.30 |   77697 | 77680 |           |
|   1 |  HASH GROUP BY        |          |      1 |  18400 |  18210 | 00:00:51.30 |   77697 | 77680 | 4211K (0) |
|*  2 |   HASH JOIN           |          |      1 |   618K |   623K | 00:00:50.10 |   77697 | 77680 |   14M (0) |
|*  3 |    HASH JOIN          |          |      1 |   250K |   252K | 00:00:14.20 |   22427 | 22422 | 1590K (0) |
|   4 |     TABLE ACCESS FULL | SHOP     |      1 |    200 |    200 | 00:00:00.01 |       5 |     0 |           |
|*  5 |     TABLE ACCESS FULL | ORD      |      1 |   250K |   252K | 00:00:14.00 |   22422 | 22422 |           |
|*  6 |    TABLE ACCESS FULL  | ORD_ITEM |      1 |   618K |   623K | 00:00:35.40 |   55270 | 55258 |           |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("I"."ORD_NO"="O"."ORD_NO")
   3 - access("O"."SHOP_NO"="S"."SHOP_NO")
   5 - filter(("O"."ORD_DT">='20130701' AND "O"."ORD_DT"<='20131231'))
   6 - filter(("I"."ORD_DT">='20130701' AND "I"."ORD_DT"<='20131231'))

[14:00 에 다시]  51초 · Query Duration 51 sec < TUNED_UNDORETENTION 1,040 sec · ORA-01555 없음

모범 답안 서술

리포트는 아무것도 고치지 않지만, 읽기 일관성 때문에 시작한 순간(14:00)의 데이터를 봐야 한다. 리포트가 23분 뒤에 읽은 블록을 그사이 반품이나 수량 정정 화면이 고치고 COMMIT했다면, 오라클은 언두로 그 블록을 14:00의 모습으로 되돌린 사본(CR 블록)을 만들어 읽는다. 알림 로그의 Query Duration=1380 sec는 23분 전의 과거가 필요했다는 뜻이고, 낮의 TUNED_UNDORETENTION은 1,040초까지 내려가 있었다. 1,040초보다 오래된 언두는 덮어써도 되는 칸이라 그사이 다른 트랜잭션이 재사용했고, 그래서 ORA-01555가 났다. 새벽에는 고치는 세션이 거의 없어 되돌릴 블록이 없었고 언두도 21,400초를 버텨서, 31분이 걸려도 끝났다. 23분이 걸린 것은 힌트가 고정한 NL 조인 때문이다. 매장 200곳을 하나씩 돌며(5번 줄 Starts 200) 그 매장의 주문을 인덱스로 찾아 테이블을 한 건씩 읽고, 주문 252K건마다 ORD_ITEM을 다시 찾는다(7번과 8번 줄 Starts 252K). 한 블록에는 여러 매장의 주문이 섞여 있으므로 같은 블록을 31분에 걸쳐 여러 번 다시 읽고, 버퍼 캐시에서 밀려난 블록은 디스크에서 다시 읽는다(Reads 412K). 오래 걸릴수록, 같은 블록을 늦게 다시 읽을수록 더 먼 과거로 되돌려야 한다. 매장이 30곳이던 2012년에는 맞았을 판단이 지금은 리포트를 길게 만드는 원인이 되었다. 고친 판은 NL 고정을 걷어 내고 ORD와 ORD_ITEM을 FULL로 한 번씩 훑어 해시 조인한다. ORD_ITEM에도 복사된 날짜 칸 ORD_DT 조건을 주어 해시 조인에 대는 행을 247만에서 62만으로 줄인다. 블록마다 한 번씩만 읽으므로 Buffers는 1,265K → 77,697, 시간은 31분 → 51초로 준다. 51초면 낮에도 언두가 버티는 1,040초 안에 넉넉히 끝나므로 ORA-01555가 나지 않는다. DBA의 "언두 두 배" 안은 근본 해결이 아니다. 보존 시간이 2,000초 남짓으로 늘면 23분짜리 리포트는 살아남겠지만, 낮의 변경량이 늘거나 리포트가 조금만 더 길어지면 다시 죽는다. 언두를 늘려 ORA-01555를 미룰 수는 있어도, 리포트가 31분 동안 같은 블록을 되풀이해 읽는 구조는 그대로이기 때문이다. 리포트를 51초로 줄이면 언두는 지금 크기로 충분하다.

해설

ORA-01555의 피해자는 오래 도는 읽기다. 리포트 자신이 커밋하지 않아도, 같은 테이블을 고치는 다른 세션이 많을 때 오래 돌면 죽는다. 처방의 순서는 리포트를 짧게 만들기, 고치는 일이 적은 시간으로 옮기기, 그다음이 언두 보존 시간이다(교재 13화 §5).
NL 조인과 인덱스를 경유한 테이블 액세스는 같은 블록을 긴 시간에 걸쳐 여러 번 찾아간다. 늦게 다시 찾아갈수록 더 먼 과거로 되돌려야 하므로 ORA-01555에 약하다. 해시 조인과 FULL은 블록을 한 번씩 일찍 읽고 끝낸다.
ALTER TABLESPACE … RETENTION GUARANTEE로 보존 시간을 보장하면 리포트는 살지만, 언두 공간이 모자랄 때 반품 화면의 DML이 ORA-30036으로 실패한다. 리포트를 살리려고 업무를 멈추게 하는 셈이라 마지막 수단이다.
해시 조인은 PGA를 쓴다(14M). 리포트 여러 개를 동시에 돌리면 work area가 모자라 TEMP로 넘칠 수 있으므로 Used-Mem을 함께 본다(교재 06화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황