할인 규칙 한 줄을 배포했을 뿐인데 매장 POS 가격 조회가 4분 동안 멈췄다

실전DDL유통700 / 700

상황

화요일 14:01, 개발자가 판매가 패키지 PKG_PRICE 본문의 할인 규칙 한 줄을 고쳐 SQL Developer에서 배포했다(CREATE OR REPLACE PACKAGE BODY). 배포 창이 4분 넘게 멈춰 있는 동안 매장 POS의 「상품 상세」와 본사 ERP 가격 화면이 모두 멈췄다. 14:05:53에 배포가 끝나자 화면이 풀렸고, 이어 1분 동안 ERP와 POS 화면 40곳에서 ORA-04068이 한 번씩 떴다. 박팀장: "원인을 정리하고, 다시는 이런 일이 없게 패키지 구조와 배포 절차를 고쳐 와요. 무엇을 함께 쓸지도 정해 주고요."

테이블과 인덱스

PKG_PRICE 판매가 패키지: CUR_UPRC(itm_no) 현재 판매가(POS 「상품 상세」 하루 3,000번, ERP 가격 화면) · RECALC_MONTH(ym) 월 판매가 재집계(판매상품마다 CUR_UPRC를 불러 점검 테이블에 쓴다, 한 번에 4 ~ 5분)
RECALC_MONTH는 매월 둘째 화요일 14:00에 스케줄러가 돌린다.
본문에는 패키지 변수 g_last_itm, g_last_uprc가 있다(직전에 물은 상품의 답을 기억한다. POS 호출에서 맞힌 비율은 3%다).
CF05 상품가격: 157행 · PK(ITM_NO, UPRC_KN_CD, APL_DT) · 판매가는 UPRC_KN_CD = '01'
ERP와 POS 화면은 WAS 연결 풀(세션 40개)로 붙는다. 배포는 개발자가 업무 시간에 SQL Developer로 하고, 사내 배포 규칙은 없다.

원본 SQL

sql

CREATE OR REPLACE PACKAGE BODY PKG_PRICE IS
  g_last_itm  NUMBER;                          -- 직전에 물은 상품 (2018)
  g_last_uprc NUMBER;                          -- 그 답

  FUNCTION CUR_UPRC(p_itm_no NUMBER) RETURN NUMBER IS
    v NUMBER;
  BEGIN
    IF p_itm_no = g_last_itm THEN
      RETURN g_last_uprc;
    END IF;
    SELECT MAX(ITM_UPRC) KEEP (DENSE_RANK LAST ORDER BY APL_DT) INTO v
      FROM CF05
     WHERE ITM_NO = p_itm_no
       AND UPRC_KN_CD = '01'
       AND APL_DT <= TO_CHAR(SYSDATE, 'YYYYMMDD');
    v := ROUND(v * 0.95);                      -- 할인 규칙 (화요일 14:01에 고친 줄)
    g_last_itm  := p_itm_no;
    g_last_uprc := v;
    RETURN v;
  END;

  PROCEDURE RECALC_MONTH(p_ym VARCHAR2) IS     -- 월 판매가 재집계 (4 ~ 5분)
  BEGIN
    NULL;  -- … 판매상품마다 CUR_UPRC를 불러 점검 테이블에 쓴다 (생략)
  END;
END;
/

실행계획 학습용 예시

xplan

[14:03 V$SESSION — 대기 중인 세션 (요약)]
  SID  누구                               EVENT                BLOCKING_SESSION  SECONDS_IN_WAIT
   87  스케줄러 RECALC_MONTH (14:00 시작)   (CPU · 실행 중)               —                  —
  142  SQL Developer (배포)                library cache pin            87                 85
   33  ERP WAS                            library cache lock          142                 81
   35  ERP WAS                            library cache lock          142                 77
   …   (WAS 세션 31개 더 · 모두 library cache lock · BLOCKING_SESSION 142)

[14:03 V$ACCESS — PKG_PRICE를 쥐고 있는 세션 (ACTIVE만)]
  SID 87 · OBJECT PKG_PRICE · TYPE PACKAGE, PACKAGE BODY

[시간표]
  14:00:02  RECALC_MONTH 시작
  14:01:38  배포 시작 (CREATE OR REPLACE PACKAGE BODY PKG_PRICE)
  14:05:52  RECALC_MONTH 끝
  14:05:53  배포 끝 → 기다리던 화면 세션이 모두 풀림
  14:05 ~ 14:07  화면 오류 40건 (세션마다 한 번)
                 ORA-04068: existing state of packages has been discarded
                 ORA-04061: existing state of package body "PUP.PKG_PRICE" has been invalidated

문제

본문 한 줄을 바꾼 배포가 4분 넘게 걸리고 그동안 화면이 멈춘 이유를 위 자료로 설명하시오. 배포가 끝난 뒤 ORA-04068이 난 이유도 쓰시오.
같은 일이 생기지 않도록 패키지 구조를 고치고, 배포 직전에 돌릴 점검 SQL을 쓰시오.
배포 시간을 정하는 안, 에디션 기반 재정의(EBR), WAS가 ORA-04068을 받으면 다시 부르는 안을 견주어 무엇을 함께 쓸지와 각각의 대가를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 화면용 패키지에서 상태를 없앤다 (패키지 변수 두 개 삭제) → 다시 컴파일해도 ORA-04068이 나지 않는다
CREATE OR REPLACE PACKAGE BODY PKG_PRICE IS
  FUNCTION CUR_UPRC(p_itm_no NUMBER) RETURN NUMBER IS
    v NUMBER;
  BEGIN
    SELECT MAX(ITM_UPRC) KEEP (DENSE_RANK LAST ORDER BY APL_DT) INTO v
      FROM CF05
     WHERE ITM_NO = p_itm_no
       AND UPRC_KN_CD = '01'
       AND APL_DT <= TO_CHAR(SYSDATE, 'YYYYMMDD');      -- PK 범위로 2 ~ 3블록
    RETURN ROUND(v * 0.95);                             -- 할인 규칙
  END;
END;
/
-- (스펙은 바꾸지 않는다: 스펙을 바꾸면 기대는 프로그램이 모두 무효가 되어 다시 컴파일된다)

-- ② 오래 도는 배치는 화면용 패키지에서 떼어 낸다 (배치만 고치는 배포가 화면을 세우지 않게)
CREATE OR REPLACE PACKAGE PKG_PRICE_BATCH IS
  PROCEDURE RECALC_MONTH(p_ym VARCHAR2);              -- 본문은 PKG_PRICE.RECALC_MONTH를 옮긴다
END;
/

-- ③ 배포 직전 점검 — 5초 넘게 돌고 있는 호출이 PKG_PRICE를 쥐고 있으면 배포하지 않는다
SELECT a.SID, s.MODULE, s.ACTION, s.LAST_CALL_ET
  FROM V$ACCESS a, V$SESSION s
 WHERE a.OWNER  = 'PUP'
   AND a.OBJECT IN ('PKG_PRICE', 'PKG_PRICE_BATCH')
   AND s.SID    = a.SID
   AND s.STATUS = 'ACTIVE'
   AND s.LAST_CALL_ET > 5;

SELECT JOB_NAME, SESSION_ID, ELAPSED_TIME          -- 지금 도는 스케줄러 잡
  FROM DBA_SCHEDULER_RUNNING_JOBS;

-- ④ 배포하다 멈추면: 배치가 아니라 배포 세션을 먼저 끊는다 (줄 맨 앞의 배타 요청이 사라지면 화면이 바로 풀린다)
-- ALTER SYSTEM KILL SESSION '142,<serial#>';

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

xplan

[배포 시험 — 배치가 없는 시간, 고친 판]
  CREATE OR REPLACE PACKAGE BODY PKG_PRICE : 0.08 초
  그 뒤 WAS 세션 40개가 CUR_UPRC 호출 : ORA-04068 0건

[점검 SQL — 14:03에 돌렸다면]
  SID 87 · DBMS_SCHEDULER · RECALC_MONTH · LAST_CALL_ET 181 → 배포 보류

[CUR_UPRC 한 번]  패키지 변수가 맞히던 3%를 잃어 POS 하루 3,000번 중 약 90번이 CF05를 3블록씩 더 읽는다

모범 답안 서술

배포가 멈춘 것은 스케줄러 세션 87이 14:00부터 PKG_PRICE.RECALC_MONTH를 실행 중이었기 때문이다. 패키지를 실행하는 세션은 그 패키지의 핀을 쥐고 있고, 다시 컴파일하려면 아무도 핀을 쥐지 않을 때까지 기다려야 한다. 배포 세션 142가 library cache pin으로 87을 기다린 4분 15초가 그 시간이다. 그런데 컴파일은 먼저 그 패키지에 배타적인 요청을 걸어 둔다. 그 뒤에 CUR_UPRC를 부르려던 화면 세션들은 배포 세션 뒤에 줄을 섰다. V$SESSION에서 ERP와 POS 세션 33개가 library cache lock으로 BLOCKING_SESSION 142를 가리킨 것이 그 모습이다. 배치 하나가 배포를 막고, 배포가 화면 전체를 막았다. 14:05:52에 배치가 끝나자 1초 뒤 배포가 끝났고 화면이 풀렸다. ORA-04068은 PKG_PRICE에 상태가 있어서 났다. 패키지 변수 g_last_itm, g_last_uprc는 세션마다 따로 살아 있고, 본문이 다시 컴파일되면 그 상태를 쥔 세션이 다음 호출에서 ORA-04068을 한 번 받고 상태를 버린다. WAS 세션 40개가 저마다 한 번씩 받았다. 그 변수가 맞힌 비율은 3%뿐이었다. 패키지 구조는 두 가지를 고친다. ① 패키지 변수를 없애 상태 없는 패키지로 만든다. 그러면 본문을 다시 컴파일해도 ORA-04068이 나지 않는다. 잃는 것은 POS 호출 하루 약 90번이 CF05를 3블록씩 더 읽는 정도다. 스펙은 바꾸지 않고 본문만 바꾼다. 스펙을 바꾸면 기대는 프로그램이 모두 무효가 되어 다시 컴파일되므로 멈춤이 더 넓어진다. ② 오래 도는 RECALC_MONTH를 PKG_PRICE_BATCH로 떼어 낸다. 배치 코드만 고치는 배포가 화면을 세우지 않게 된다. 다만 배치가 CUR_UPRC를 부르는 동안에는 여전히 PKG_PRICE의 핀이 잡히므로, 할인 규칙처럼 둘이 함께 쓰는 코드의 배포는 절차로 막아야 한다. 그래서 배포 직전에 점검 SQL을 돌린다. V$ACCESS와 V$SESSION으로 그 패키지를 쥔 채 5초 넘게 도는 호출이 있는지, DBA_SCHEDULER_RUNNING_JOBS로 도는 잡이 있는지 본다. 하나라도 나오면 배포를 미룬다. 그래도 배포가 멈추면 배치가 아니라 배포 세션을 먼저 끊는다. 줄 맨 앞의 배타 요청이 사라지면 화면은 바로 풀리고 배치는 계속 돈다. 세 안은 함께 쓴다. 배포 시간을 배치 일정 밖(예: 화·목 19:00, 둘째 화요일 14:00 ~ 14:10 금지)으로 정하는 안은 돈이 들지 않고 가장 확실하지만, 급한 수정도 그 시간까지 기다려야 한다. WAS가 ORA-04068을 받으면 같은 호출을 한 번 다시 부르는 안은 상태가 남은 다른 패키지까지 사용자에게 에러를 보이지 않게 해 주지만, 멈춤(핀 대기)은 막지 못한다. 에디션 기반 재정의(EBR)는 새 판을 새 에디션에 컴파일하므로 쓰고 있는 판의 핀을 기다리지 않고, 옛 세션은 옛 판을 계속 쓴다. 무중단 배포가 되는 대신 스키마를 에디션 사용으로 바꾸고, 테이블이 바뀔 때는 에디셔닝 뷰와 교차 에디션 트리거까지 갖춰야 하는 큰 작업이다. 지금은 앞의 둘과 점검 SQL을 쓰고, 화면을 24시간 세울 수 없게 되면 EBR을 검토한다.

해설

함정: DDL_LOCK_TIMEOUT을 걸면 배포가 오래 기다리지 않을 것 같지만, 이 파라미터는 테이블의 DML 락을 기다리는 DDL에 쓰인다. 패키지 컴파일의 핀 대기에는 듣지 않는다. 배포 도구에 시간 제한을 두고, 넘으면 배포 세션을 끊는 쪽이 확실하다.
같은 세션이 자기가 실행 중인 프로그램을 다시 컴파일하면 자기 자신을 기다린다(교재 15화 §6.3, library cache pin 800초). 배포 스크립트 안에서 그 패키지를 부르는 익명 블록과 컴파일을 섞지 않는다.
V$LOCK부터 보면 아무것도 안 나온다. 라이브러리 캐시의 락과 핀은 TM이나 TX 같은 enqueue가 아니라서 V$LOCK에 줄이 생기지 않고, BLOCK이 1인 줄도 없다. 이런 기다림은 V$SESSION의 EVENT(library cache pin, library cache lock)와 BLOCKING_SESSION, P1RAW(객체 핸들 주소)로 사슬을 따라가고, 누가 그 패키지를 쥐고 있는지는 V$ACCESS나 DBA_DDL_LOCKS로 본다. DBA_DDL_LOCKS는 기본으로 없어서 catblock.sql로 미리 만들어 둔다.
장애 대응 첫 5분은 블로킹 트리에서 줄 맨 앞을 찾는 일이다(교재 12화 §5). 화면을 막은 것은 배포 세션(142)이고, 배포를 막은 것은 배치(87)다. 배치를 끊어도 화면은 풀리지만 재집계를 버린다. 배포 세션을 끊으면 잃는 것이 없다. 끊을 세션은 줄의 구조를 보고 고른다.
상수 하나로도 상태가 생길 수 있다는 측정이 교재 15화 §6.2에 있다. 상태를 없앴다고 판단했으면 다시 컴파일한 뒤 다른 세션에서 불러 ORA-04068이 나지 않는지 확인한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황