ORA-04068 대책을 넣은 뒤로 포인트 적립 API가 네 배 느려지고 야간 대사가 멈췄다

중급PLSQL이커머스696 / 700

이 문제는 교재 18화 졸업 과제와 같은 이커머스 데이터베이스를 씁니다. 『튜너의 1년』을 순서대로 읽고 있다면 졸업 과제를 먼저 풀고 오세요. 아래 「테이블과 인덱스」 칸에 과제의 답 일부(제약 조건, 인덱스, 통계의 실제 상태)가 나옵니다.

상황

이커머스의 포인트 적립 API는 WAS 연결 풀(세션 40개)에서 패키지 pkg_point를 부른다. 10월 8일 14:00에 적립 규칙을 고쳐 패키지 본문을 배포하자, 세션 40개가 처음 부를 때 한 번씩 ORA-04068을 냈다. 그 40건의 적립은 다시 시도되지 않아 고객 문의가 들어왔다. 개발자는 10월 10일 스펙과 본문에 PRAGMA SERIALLY_REUSABLE을 붙였다. 15일 배포 때는 ORA-04068이 나지 않았다. 그런데 API 평균 응답이 1.8ms에서 6.9ms가 됐고, 16일 새벽 야간 포인트 대사가 에러로 멈췄다.

테이블과 인덱스

customers: 5,230행 · 51블록 · PK(customer_id) · grade 4종 · 등급은 매일 새벽 재산정한다
point_rule(이 문제용): 4행 · PK(grade) · earn_rate 등급별 적립률
point_transactions: 45,000행 · 210블록 · PK(tx_id) · tx_id는 시퀀스 seq_tx_id
저녁 피크 1시간에 적립 API가 약 27,400번 불린다. 야간 포인트 대사는 SQL에서 pkg_point.rate_of를 부른다.
WAS 연결 풀의 세션은 WAS를 다시 띄울 때까지(보통 2 ~ 3주) 끊기지 않는다.

원본 SQL

sql

CREATE OR REPLACE PACKAGE pkg_point IS
  PRAGMA SERIALLY_REUSABLE;                      -- 10/10 추가 : ORA-04068 대책
  PROCEDURE earn(p_customer_id NUMBER, p_amount NUMBER);
  FUNCTION rate_of(p_customer_id NUMBER) RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_point IS
  PRAGMA SERIALLY_REUSABLE;
  TYPE t_grade IS TABLE OF customers.grade%TYPE INDEX BY PLS_INTEGER;
  TYPE t_rate  IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
  g_grade  t_grade;                              -- 고객 5,230명의 등급 (처음 부를 때 한 번 채운다)
  g_rate   t_rate;                               -- 등급별 적립률
  g_loaded BOOLEAN := FALSE;

  PROCEDURE load IS
  BEGIN
    FOR c IN (SELECT customer_id, grade FROM customers) LOOP
      g_grade(c.customer_id) := c.grade;
    END LOOP;
    FOR r IN (SELECT grade, earn_rate FROM point_rule) LOOP
      g_rate(r.grade) := r.earn_rate;
    END LOOP;
    g_loaded := TRUE;
  END;

  FUNCTION rate_of(p_customer_id NUMBER) RETURN NUMBER IS
  BEGIN
    IF NOT g_loaded THEN load; END IF;
    RETURN g_rate(g_grade(p_customer_id));
  END;

  PROCEDURE earn(p_customer_id NUMBER, p_amount NUMBER) IS
    v_pt NUMBER;
  BEGIN
    v_pt := ROUND(p_amount * rate_of(p_customer_id));
    INSERT INTO point_transactions (tx_id, customer_id, amount, tx_type, created_at)
    VALUES (seq_tx_id.NEXTVAL, p_customer_id, v_pt, 'EARN', SYSDATE);
  END;
END;
/

실행계획 학습용 예시

xplan

[API earn 한 번 · 평균 (V$MYSTAT 증가분)]
                               10/8 이전 (상태 있음)    지금 (SERIALLY_REUSABLE)
  경과                                 1.8 ms                 6.9 ms
  CPU                                  0.6 ms                 5.4 ms
  recursive calls                          3                     58
  session logical reads                    9                     66

[저녁 피크 1시간 — AWR]
  API 호출 27,400번
  SELECT CUSTOMER_ID, GRADE FROM CUSTOMERS     Executions 0 → 27,400 · Buffer Gets 0 → 1,397,400
  SELECT GRADE, EARN_RATE FROM POINT_RULE      Executions 0 → 27,400
  DB CPU                                       41 초 → 176 초

[10/8 14:00 배포 직후 — WAS 로그 (세션마다 한 번, 40건)]
  ORA-04068: existing state of packages has been discarded
  ORA-04061: existing state of package body "ECOMMERCE.PKG_POINT" has been invalidated

[10/16 02:10 야간 포인트 대사]
  SELECT o.order_id, o.customer_id, pkg_point.rate_of(o.customer_id) AS rate FROM orders o WHERE …
  ORA-06534: Cannot access Serially Reusable package "ECOMMERCE.PKG_POINT"

문제

SERIALLY_REUSABLE을 붙인 뒤 API가 느려진 이유와 야간 대사가 멈춘 이유를 설명하시오. 처음에 ORA-04068이 난 이유도 쓰시오.
본문을 다시 배포해도 ORA-04068이 나지 않으면서 느려지지 않도록 패키지를 고치시오. 고친 뒤의 일량도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

CREATE OR REPLACE PACKAGE pkg_point IS               -- SERIALLY_REUSABLE을 뗀다
  PROCEDURE earn(p_customer_id NUMBER, p_amount NUMBER);
  FUNCTION rate_of(p_customer_id NUMBER) RETURN NUMBER;
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_point IS
  -- 패키지 수준 변수를 두지 않는다 → 상태 없는 패키지 (본문을 다시 컴파일해도 ORA-04068이 나지 않는다)

  FUNCTION rate_of(p_customer_id NUMBER) RETURN NUMBER IS
    v_rate NUMBER;
  BEGIN
    SELECT r.earn_rate INTO v_rate                  -- 그 고객 한 명만 PK로 (지금의 등급)
      FROM customers c, point_rule r
     WHERE c.customer_id = p_customer_id
       AND r.grade = c.grade;
    RETURN v_rate;
  END;

  PROCEDURE earn(p_customer_id NUMBER, p_amount NUMBER) IS
    v_pt NUMBER;
  BEGIN
    v_pt := ROUND(p_amount * rate_of(p_customer_id));
    INSERT INTO point_transactions (tx_id, customer_id, amount, tx_type, created_at)
    VALUES (seq_tx_id.NEXTVAL, p_customer_id, v_pt, 'EARN', SYSDATE);
  END;
END;
/

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

xplan

[API earn 한 번 · 평균 (상태 없는 판)]
  경과 1.9 ms · CPU 0.7 ms · recursive calls 5 · session logical reads 14

[저녁 피크 1시간]
  CUSTOMERS 전체 읽기 27,400번 → 0 (PK로 한 명씩 27,400번 · 한 번 3블록)
  DB CPU 176 초 → 46 초

[배포 시험 — 본문을 다시 컴파일한 뒤 WAS 세션 40개가 earn 호출]  ORA-04068 0건
[야간 포인트 대사]  정상 (ORA-06534 없음)

모범 답안 서술

처음의 ORA-04068은 pkg_point에 상태가 있어서 났다. 본문의 패키지 변수(g_grade, g_rate, g_loaded)는 세션이 처음 부를 때 만들어져 세션이 끝날 때까지 남는다. 본문을 다시 컴파일하면 그 상태를 쥔 세션이 다음에 부를 때 ORA-04068을 한 번 받고 상태를 버린다. WAS 세션 40개가 저마다 한 번씩 받았고, 앱이 다시 부르지 않아 그 40건의 적립이 사라졌다. SERIALLY_REUSABLE은 패키지 상태를 서버 호출 한 번 동안만 두고 호출이 끝날 때마다 버린다. 다음 호출은 늘 빈 상태로 시작하니 ORA-04068은 사라졌지만, g_loaded도 매번 FALSE로 돌아간다. 그래서 API를 부를 때마다 load가 고객 5,230명(51블록)과 적립률을 다시 읽어 배열을 채운다. AWR에서 CUSTOMERS 전체를 읽는 문장의 Executions가 0 → 27,400으로 API 호출 수와 같고, 호출 한 번의 recursive calls가 3 → 58, 논리 읽기가 9 → 66, 경과가 1.8 → 6.9ms가 됐다. 피크 1시간의 DB CPU는 41초 → 176초다. 게다가 SERIALLY_REUSABLE 패키지는 SQL에서 부를 수 없다. 야간 대사는 SELECT 안에서 pkg_point.rate_of를 부르므로 ORA-06534로 멈췄다. 고친 판은 SERIALLY_REUSABLE을 떼고 패키지 수준 변수를 모두 없앤다. 상태 없는 패키지는 본문을 다시 컴파일해도 ORA-04068을 내지 않는다. rate_of는 호출마다 그 고객 한 명의 등급과 적립률을 PK로 읽는다(한 번 3블록). 고객 5,230명을 통째로 읽던 일이 한 명을 읽는 일로 바뀌어 API는 6.9ms → 1.9ms로 처음 판(1.8ms)과 거의 같고, 피크 1시간의 DB CPU는 176초 → 46초가 된다. 매일 새벽 바뀌는 등급도 그날의 값으로 읽는다. 처음 판은 세션이 2 ~ 3주 살아 있는 동안 처음 읽은 등급을 계속 썼다.

해설

SERIALLY_REUSABLE은 ORA-04068의 처방이 아니라 상태를 버리는 시점을 바꾸는 장치다. 상태가 꼭 필요한 패키지에 붙이면 그 상태를 호출마다 다시 만드는 값을 치른다. 상태가 필요 없으면 아예 두지 않는 것이 맞다(교재 15화 §6).
캐시가 꼭 필요하다면 세션 메모리 대신 결과 캐시(RESULT_CACHE) 함수에 둔다. 인스턴스가 함께 쓰는 SGA에 있어 세션 상태가 아니고, point_rule이나 customers가 바뀌어 커밋되면 지워진다. 다만 등급처럼 매일 바뀌는 답이 5,230개라 효과는 따져 보고 쓴다(교재 15b화).
WAS 쪽도 고친다. 상태가 남아 있는 다른 패키지를 위해 ORA-04068을 받으면 같은 호출을 한 번 다시 부른다. 이 에러는 호출이 실행되기 전에 나므로 다시 불러도 적립이 두 번 되지 않는다.
야간 대사처럼 한 번에 수만 행을 처리하는 SQL은 행마다 rate_of를 부르지 말고 customers, point_rule과 조인한다. 상태 없는 판에서는 함수가 부를 때마다 SQL을 한 번씩 실행하기 때문이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황