디버깅을 마친 다음 날부터 판매 전표 검증이 두 배 넘게 걸린다

모범 답안 SQL ​

-- ① 레벨을 되돌린다 (구동 커서가 다시 100행씩 가져온다)
ALTER PROCEDURE CHK_SLE_SLIP COMPILE PLSQL_OPTIMIZE_LEVEL = 2;

-- ② 확인 · 디버깅하다 레벨 1로 남은 객체가 더 있는지
SELECT NAME, TYPE, PLSQL_OPTIMIZE_LEVEL
  FROM USER_PLSQL_OBJECT_SETTINGS
 WHERE PLSQL_OPTIMIZE_LEVEL < 2;

-- ③ 다음 배포에서는 묶음을 코드에 적는다 (컴파일 설정이 바뀌어도 천 건씩)
PROCEDURE CHK_SLE_SLIP IS
  CURSOR c IS SELECT SLE_NO, SLE_DT, SLE_HMS, SLE_DOW, SHP_NO FROM CF10;
  TYPE t_rows IS TABLE OF c%ROWTYPE;
  TYPE t_err  IS TABLE OF CF10_ERR%ROWTYPE INDEX BY PLS_INTEGER;
  v     t_rows;
  v_err t_err;
  n     PLS_INTEGER := 0;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v LIMIT 1000;
    EXIT WHEN v.COUNT = 0;
    FOR i IN 1 .. v.COUNT LOOP
      IF NOT PKG_SLE_RULE.IS_VALID(v(i).SLE_NO, v(i).SLE_DT, v(i).SLE_HMS, v(i).SLE_DOW) THEN
        n := n + 1;
        v_err(n).SLE_NO := v(i).SLE_NO;
        v_err(n).ERR_CD := PKG_SLE_RULE.LAST_ERR;
      END IF;
    END LOOP;
  END LOOP;
  CLOSE c;
  FORALL i IN 1 .. n
    INSERT INTO CF10_ERR VALUES v_err(i);
  COMMIT;
END;

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

[① 뒤 — USER_PLSQL_OBJECT_SETTINGS]   CHK_SLE_SLIP   PROCEDURE   PLSQL_OPTIMIZE_LEVEL 2

[세션 통계 — 한 번]
                    경과      recursive calls   구동 커서 Fetch   session logical reads   오류 기록
  ① 레벨 2로 되돌림  0.41 초          5,012              5,001                 9,602             37건
  ③ BULK LIMIT 1000  0.38 초            512                501                 5,104             37건

모범 답안 서술 ​

TKPROF의 구동 커서를 보면 지난 화요일은 Fetch 5,001에 rows 500,000으로 한 번에 100행씩 가져왔고, 오늘은 Fetch 500,001로 한 건씩 가져온다. 커서 FOR 루프를 100행씩 묶어 가져오는 것은 PL/SQL 컴파일러의 최적화이고, PLSQL_OPTIMIZE_LEVEL 2 이상에서만 켜진다. 레벨 1로 컴파일하자 자동 배열 페치가 꺼져 한 건씩 가져오게 됐다. 페치마다 SQL 엔진으로 건너가므로 recursive calls가 5,012 → 500,012로 늘었고, 같은 블록을 페치마다 다시 읽어 논리 읽기도 9,602 → 500,021이 됐다. 루프 안에는 SQL이 없어서 이 차이가 경과 시간(0.41초 → 0.97초)에 고스란히 드러났다. 소스가 같아도 컴파일 설정은 객체에 따로 저장된다. USER_PLSQL_OBJECT_SETTINGS에서 CHK_SLE_SLIP만 레벨 1로 남아 있는 것이 그 증거다. ALTER PROCEDURE CHK_SLE_SLIP COMPILE PLSQL_OPTIMIZE_LEVEL = 2로 되돌리면 recursive calls는 500,012 → 5,012, 경과는 0.97초 → 0.41초로 돌아온다. 같은 날 디버깅한 다른 객체도 같은 뷰에서 레벨이 2보다 낮은 것을 찾아 함께 되돌린다. 다시 생기지 않게 하려면 레벨이 1로 들어오는 길을 막는다. ALTER … COMPILE REUSE SETTINGS는 저장된 설정(레벨 1)을 그대로 쓰므로 배포 스크립트가 이 옵션으로 다시 컴파일하면 레벨 1이 따라간다. CREATE OR REPLACE는 그 세션의 PLSQL_OPTIMIZE_LEVEL로 컴파일되므로, 디버깅 세션에서 ALTER SESSION으로 1을 걸어 두었다면 그 세션에서 배포해도 1이 된다. 그리고 다음 배포에서는 BULK COLLECT … LIMIT 1000으로 묶음을 코드에 적어, 컴파일 설정이 바뀌어도 천 건씩 가져오게 한다(recursive calls 512).

해설 ​

• 디버깅할 때 레벨 1로 컴파일하는 것 자체는 맞다. 최적화가 켜져 있으면 변수가 사라지거나 줄 순서가 바뀌어 디버거가 엉뚱한 줄에 선다. 끝나면 되돌리는 일을 릴리스 체크리스트에 넣어 둔다.
• 레벨 1은 자동 배열 페치만 끄는 것이 아니라 컴파일러 최적화를 대부분 끈다. 계산이 많은 패키지라면 그 몫도 느려진다(지역 서브프로그램 인라인은 레벨 3, 교재 15화 §2.2).
• 루프 안에 INSERT나 SELECT INTO가 있는 프로시저였다면 레벨 차이는 덜 드러난다. 루프 안의 SQL은 레벨과 상관없이 행마다 나가기 때문이다(교재 15화 §2.1, P1 rev00은 레벨 2와 3에서도 recursive calls 10만).
• 명시적 커서의 FETCH … INTO는 레벨 2에서도 한 건씩이다. 코드를 고칠 때 커서 FOR 루프를 명시적 커서로 바꾸면 같은 일이 생긴다.

관련 교재 ​

• 15화 · PLSQL_OPTIMIZE_LEVEL 1 · 2 · 3