작년까지 돌던 매장 수수료 재계산이 어젯밤 ORA-04036으로 죽었다

중급PLSQL버거670 / 700

상황

버거 본사는 분기마다 전체 주문을 다시 읽어 매장별 주문 수수료를 다시 계산한다. 가맹 계약의 시간대별 요율 규칙은 PL/SQL 패키지 PKG_SHOP_FEE에 있다. 2012년분만 있던 첫해에는 문제없이 돌았다. 어젯밤 01:12, 정산 배치 두 개와 시간이 겹치자 이 배치가 ORA-04036으로 죽었다. DBA의 말은 이렇다. "이 세션 하나가 PGA를 1.1GB 넘게 잡고 있었어요. 같은 시각 정산 배치의 해시 조인은 TEMP로 넘쳤고요."

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_NO NUMBER(10) · ORD_DT VARCHAR2(8) · ORD_HMS VARCHAR2(6) · SHOP_NO VARCHAR2(10) · 채움 컬럼 UPPER2 · UPPER_CASE · LOWER_CASE · ALPHABET VARCHAR2(400) · ALPHABET_NUMERIC VARCHAR2(400)
PKG_SHOP_FEE.ADD_ORD(shop_no, ord_dt, ord_hms): SQL 없이 매장, 월별 수수료를 패키지 배열에 더한다 · FLUSH가 매장, 월별 4,800행을 넣는다
인스턴스: PGA_AGGREGATE_TARGET 1GB · PGA_AGGREGATE_LIMIT 2GB

원본 SQL

sql

PROCEDURE RECALC_SHOP_FEE IS
  TYPE t_ord IS TABLE OF ORD%ROWTYPE;
  v_ord t_ord;
BEGIN
  SELECT * BULK COLLECT INTO v_ord FROM ORD;            -- 100만 건을 한 번에
  FOR i IN 1 .. v_ord.COUNT LOOP
    PKG_SHOP_FEE.ADD_ORD(v_ord(i).SHOP_NO, v_ord(i).ORD_DT, v_ord(i).ORD_HMS);
  END LOOP;
  PKG_SHOP_FEE.FLUSH;
  COMMIT;
END;

실행계획 학습용 예시

xplan

[세션 통계 — 지난 분기 (혼자 돈 날)]
  경과 시간                     4.6 초
  session pga memory max        1,183 MB
  session logical reads         22,431
  recursive calls               27

[어젯밤 01:12 — 정산 배치 두 개와 겹친 날]
  ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
  그 시각 인스턴스 PGA 할당량 약 2,050 MB (이 세션 1,183 MB)
  같은 시각 정산 배치의 HASH JOIN: Used-Tmp 312M (one-pass)

[2012년분만 있던 첫해 — 같은 프로그램]
  주문 500,124건 · session pga memory max 592 MB · 경과 2.3 초

문제

이 세션 하나가 PGA를 1.1GB 넘게 잡은 이유와, 첫해에는 돌던 배치가 올해 죽은 이유를 설명하시오.
수수료 결과가 바뀌지 않게 배치를 고치시오. 루프를 빠져나가는 조건을 어디에 어떻게 두었는지와 LIMIT 값을 고른 근거도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE RECALC_SHOP_FEE IS
  CURSOR c IS SELECT SHOP_NO, ORD_DT, ORD_HMS FROM ORD;     -- ADD_ORD 가 쓰는 세 컬럼만
  TYPE t_rows IS TABLE OF c%ROWTYPE;
  v t_rows;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v LIMIT 1000;                 -- 천 건씩 (메모리는 덩어리 하나만큼)
    EXIT WHEN v.COUNT = 0;                                  -- 받은 것이 없을 때 나간다 (마지막 덩어리도 처리한다)
    FOR i IN 1 .. v.COUNT LOOP
      PKG_SHOP_FEE.ADD_ORD(v(i).SHOP_NO, v(i).ORD_DT, v(i).ORD_HMS);
    END LOOP;
  END LOOP;
  CLOSE c;
  PKG_SHOP_FEE.FLUSH;
  COMMIT;
END;

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

xplan

[세션 통계 — 고친 판 (LIMIT 1000 · 세 컬럼)]
  경과 시간                     3.4 초
  session pga memory max        3.2 MB
  session logical reads         23,431
  구동 커서 Fetch               1,001
  매장, 월별 결과 4,800행       지난 분기 판과 MINUS 0 / 0

[LIMIT별 — 같은 100만 건 · 세 컬럼]
  LIMIT        Fetch   session pga memory max   경과
     10      100,001          2.6 MB            3.8 초
    100       10,001          2.7 MB            3.5 초
  1,000        1,001          3.2 MB            3.4 초
 10,000          101          7.9 MB            3.4 초

모범 답안 서술

LIMIT 없는 SELECT * BULK COLLECT INTO는 결과 100만 행을 한꺼번에 배열에 담아 세션 메모리(PGA)에 올린다. 원소는 ORD%ROWTYPE이라 ADD_ORD가 쓰지도 않는 채움 컬럼(VARCHAR2(400) 두 개 등)까지 원소마다 들어 있다. 그래서 배열 하나가 1,183MB가 됐다. 이 메모리는 미리 잡아 둔 공간이 아니라 행 수에 비례해 늘어난다. 2012년분 50만 건일 때는 592MB였고, 주문이 100만 건이 되자 두 배인 1,183MB가 됐다. 혼자 돌 때는 인스턴스 한도(PGA_AGGREGATE_LIMIT 2GB) 안이었지만, 어젯밤 정산 배치 두 개와 겹치자 인스턴스 합계가 한도를 넘었다. 오라클은 줄일 수 없는 PGA를 가장 많이 쓰는 세션의 호출을 끊으므로 이 배치가 ORA-04036으로 죽었다. 그 전에 다른 배치의 해시 조인은 PGA를 받지 못해 TEMP로 넘쳤다. 명시적 커서에서 FETCH … BULK COLLECT … LIMIT 1000으로 천 건씩 받고, 커서는 ADD_ORD가 쓰는 세 컬럼만 고른다. 메모리는 덩어리 하나만큼이라 1,183MB → 3.2MB가 되고, 채움 컬럼을 옮기지 않으니 경과도 4.6초 → 3.4초로 준다. 데이터가 늘어도 메모리는 늘지 않는다. 루프는 EXIT WHEN v.COUNT = 0으로 빠져나간다. 받은 것이 없을 때 나가므로 마지막 덩어리도 처리한다. EXIT WHEN c%NOTFOUND를 FETCH 바로 뒤에 두면 LIMIT보다 적게 받은 마지막 덩어리를 처리하지 않고 나간다. 오늘은 100만 건이 1,000으로 나누어떨어져서 시험에서는 결과가 맞지만, 주문이 317건 더 들어오면 그 317건이 빠진다. LIMIT는 1,000으로 했다. 표에서 1,000이면 Fetch가 1,001번으로 왕복은 거의 다 줄고 메모리는 3.2MB다. 10,000으로 키워도 경과는 같고 메모리만 커진다. 10으로 줄이면 Fetch가 10만 번이 되어 다시 느려진다.

해설

전체 컬럼을 고른 대가: SELECT *로 채운 %ROWTYPE 배열은 쓰지 않는 컬럼까지 원소마다 담는다. PL/SQL은 짧게 선언한 VARCHAR2(4,000바이트 미만)를 선언 길이만큼 미리 잡으므로, 채움 컬럼이 비어 있어도 메모리는 그만큼 든다. 필요한 컬럼만 고르는 것이 LIMIT 다음으로 효과가 크다.
PGA는 SGA와 달리 세션이 필요할 때 늘린다. 배치 하나를 시험할 때 괜찮았던 메모리가, 같은 시각에 도는 배치들과 합쳐지면 한도를 넘는다. 배치의 메모리는 '혼자 돌 때'가 아니라 '겹치는 배치의 합'으로 본다.
ADD_ORD가 매장, 월별로 더하기만 한다면 구동 커서에서 GROUP BY SHOP_NO, SUBSTR(ORD_DT, 1, 6), 시간대로 먼저 묶어 넘기는 방법도 있다. 시간대 규칙이 PKG_SHOP_FEE 안에만 있다면 그 규칙을 SQL로 옮기는 대가(규칙이 두 곳)를 함께 따진다.
LIMIT 고르기는 교재 15화 §5.2의 측정과 같은 모양이다. 1,000을 넘기면 왕복은 거의 줄지 않고 메모리만 커진다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황