야간 통계 잡은 석 달째 매일 성공인데 테이블 여섯 개의 통계가 3월에 멈춰 있다

모범 답안 SQL ​

-- ① 오늘 밤 23:00 : 3월에 멈춘 여섯 테이블을 먼저 모은다 (무효화는 기본값 · 한꺼번에 파싱이 몰리지 않게)
BEGIN
  FOR t IN (SELECT TABLE_NAME FROM STATS_TARGET WHERE TABLE_NAME > 'CF05' ORDER BY TABLE_NAME) LOOP
    DBMS_STATS.GATHER_TABLE_STATS('PUP', t.TABLE_NAME);
  END LOOP;
END;
/

-- ② CF05 : 가격 개편(3 ~ 5월)이 끝났다 → 잠금을 풀고 모은다
EXEC DBMS_STATS.UNLOCK_TABLE_STATS('PUP', 'CF05');
EXEC DBMS_STATS.GATHER_TABLE_STATS('PUP', 'CF05');

-- ③ 야간 잡 : 잠긴 테이블은 건너뛰고, 테이블마다 예외를 잡고, 실패가 있으면 잡을 실패로 끝낸다
PROCEDURE NIGHTLY IS
  v_fail PLS_INTEGER := 0;
BEGIN
  FOR t IN (SELECT s.TABLE_NAME
              FROM STATS_TARGET s, USER_TAB_STATISTICS u
             WHERE u.TABLE_NAME = s.TABLE_NAME
               AND u.OBJECT_TYPE = 'TABLE'
               AND u.STATTYPE_LOCKED IS NULL              -- 잠근 이유는 정책서에 (잡이 풀지 않는다)
             ORDER BY s.TABLE_NAME) LOOP
    BEGIN
      DBMS_STATS.GATHER_TABLE_STATS('PUP', t.TABLE_NAME);
    EXCEPTION
      WHEN OTHERS THEN
        PKG_LOG.WRITE('NIGHTLY', t.TABLE_NAME || ' 실패 : ' || SQLERRM);
        v_fail := v_fail + 1;
    END;
  END LOOP;

  IF v_fail > 0 THEN
    RAISE_APPLICATION_ERROR(-20110, '통계 수집 실패 ' || v_fail || '개');   -- 잡이 실패로 끝나야 감시가 본다
  END IF;
END;

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

[고친 잡 · 6월 13일 23:40 (CF05 를 다시 잠가 시험)]
 23:40:01  NIGHTLY 시작 · 대상 8개 (CF05 는 잠겨 있어 건너뜀)
 23:40:09  CF01 · CF02 · CF07 · CF10 · CF12 · CF13 · CF15 · CF18 완료
 DBA_SCHEDULER_JOB_RUN_DETAILS  STATUS SUCCEEDED · 실패가 있던 날은 FAILED 로 남고 알림이 간다

모범 답안 서술 ​

3월 4일부터 CF05는 통계가 잠겨 있다. 잠긴 테이블에 GATHER_TABLE_STATS를 부르면 ORA-20005가 난다. 이 잡은 예외 처리가 루프 바깥에 하나뿐이라, 목록의 세 번째인 CF05에서 예외가 나는 순간 루프 전체가 끝났다. 그래서 그 뒤의 CF07, CF10, CF12, CF13, CF15, CF18 여섯 테이블은 3월 3일 밤 뒤로 한 번도 모이지 않았다. 잠금은 한 테이블에 걸었지만, 예외 하나가 나머지 여섯 테이블까지 멈춘 셈이다. 잡이 100일 동안 성공으로 끝난 이유는 WHEN OTHERS다. 실패를 경고 로그 한 줄로 바꾸고 예외를 다시 던지지 않아서, 스케줄러는 SUCCEEDED로 기록하고 감시 시스템은 알림을 보내지 않았다. 실패가 숨겨지는 바람에 석 달이 지났다. 오늘은 LAST_ANALYZED가 3월 3일이고 STALE_STATS가 YES인 여섯 테이블을 먼저 모은다. 업무가 끝난 23:00에, 무효화는 기본값으로 둔다. CF05는 잠근 이유였던 가격 개편이 5월에 끝났으므로 UNLOCK_TABLE_STATS로 풀고 다시 모은다. 잠금을 남겨야 할 테이블이라면 정책서에 이유와 기한, 푸는 사람을 적는다. 이유가 사라진 잠금을 아무도 풀지 않는 것이 잠금의 가장 흔한 대가다. 야간 잡은 세 가지를 고친다. 잠긴 테이블은 STATTYPE_LOCKED로 걸러 건너뛰고, 테이블마다 따로 예외를 잡아 한 테이블의 실패가 다른 테이블을 막지 않게 하며, 실패가 하나라도 있으면 잡을 실패로 끝내 감시가 보게 한다. 더 나아가 목록에 기대지 않는 방법도 있다. GATHER_SCHEMA_STATS(options => 'GATHER AUTO')는 잠긴 테이블을 건너뛰고 STALE이거나 통계가 없는 테이블만 모으므로, 사람이 고치는 목록이 빠뜨리는 테이블도 생기지 않는다.

해설 ​

• 잠금은 한 테이블에 걸어도 그 테이블을 모으는 모든 절차에 영향을 준다. 잠그기 전에 그 테이블을 누가, 어떤 방식으로 모으는지(야간 잡, 배치 안의 수집, 자동 수집 창) 먼저 찾는다(OPT-001, OPT-007).
• force => TRUE로 잠금을 무시하게 하는 안은 잠근 의미를 없앤다. 잡이 잠금을 존중하고 건너뛰는 것이 맞다.
• WHEN OTHERS THEN 로그는 실패를 성공으로 바꾸는 가장 흔한 문장이다. 로그를 남겼다면 RAISE로 다시 던지거나, 모아서 마지막에 실패로 끝낸다.
• 멈춘 테이블 가운데 CF10처럼 날짜가 자라는 테이블은 석 달 동안 최댓값 밖을 물었다. 다시 모은 뒤에는 그 테이블을 읽는 화면의 계획이 바뀔 수 있으니 핵심 SQL은 미리 보고 공개하는 편이 안전하다(OPT-016).

관련 교재 ​

• 10화 · 통계가 만들어지는 길 (LOCK_TABLE_STATS)
• 10화 · 「통계 수집 정책서」