통계정보와 옵티마이저 관리

SELECT table_name,
       num_rows,
       blocks,
       last_analyzed,
       ROUND(SYSDATE - last_analyzed, 1) AS days_ago
FROM   user_tables
ORDER BY last_analyzed NULLS FIRST;

-- 실제 건수와 비교해 봅니다
SELECT COUNT(*) FROM orders;

last_analyzed 가 NULL이면 통계가 아예 없다는 뜻입니다. num_rows 와 실제 COUNT 가 크게 다르면 통계가 낡은 것입니다. 느린 쿼리를 만났을 때 가장 먼저 볼 두 가지입니다.

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => USER,
    tabname          => 'ORDERS',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    cascade          => TRUE,        -- 인덱스 통계도 함께
    degree           => 4            -- 병렬로 빠르게
  );
END;
/

AUTO_SAMPLE_SIZE 를 쓰면 Oracle이 표본 크기를 알아서 정합니다. 예전처럼 퍼센트를 직접 지정하는 것보다 정확하고 빠릅니다. degree 는 서버 부하를 봐 가며 정합니다.

-- 실제 분포를 먼저 봅니다
SELECT status_cd, COUNT(*),
       ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100, 2) AS pct
FROM   orders
GROUP BY status_cd
ORDER BY 2 DESC;

-- 특정 컬럼에만 히스토그램 수집
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname    => USER,
    tabname    => 'ORDERS',
    method_opt => 'FOR COLUMNS SIZE 254 STATUS_CD'
  );
END;
/

-- 히스토그램이 생겼는지 확인
SELECT column_name, num_distinct, histogram, num_buckets
FROM   user_tab_col_statistics
WHERE  table_name = 'ORDERS';

SELECT sql_id,
       child_number,
       plan_hash_value,
       executions,
       ROUND(elapsed_time / GREATEST(executions, 1) / 1000, 1) AS avg_ms,
       buffer_gets,
       first_load_time
FROM   v$sql
WHERE  sql_id = :sql_id
ORDER BY child_number;

plan_hash_value 가 서로 다르면 같은 SQL에 여러 계획이 존재한다는 뜻입니다. 평균 시간이 크게 차이 나는 계획이 있으면 그 사이에 무엇이 바뀌었는지 추적합니다.

-- 통계 이력이 얼마나 보관되는지 (기본 31일)
SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL;

-- 이 테이블의 통계가 언제 바뀌었는지
SELECT table_name, stats_update_time
FROM   user_tab_stats_history
WHERE  table_name = 'ORDERS'
ORDER BY stats_update_time DESC;

-- 문제가 생기기 전 시점으로 복원
BEGIN
  DBMS_STATS.RESTORE_TABLE_STATS(
    ownname   => USER,
    tabname   => 'ORDERS',
    as_of_timestamp => SYSTIMESTAMP - 2
  );
END;
/

통계 수집 후 성능이 무너졌을 때 쓸 수 있는 응급 처치입니다. 다만 근본 원인을 덮는 것이므로, 되돌려 급한 불을 끈 뒤 왜 새 통계에서 계획이 나빠졌는지 확인해야 합니다.