Oracle - 통계 정보 관리

Oracle 통계 정보 관리

오라클 옵티마이저가 최적의 실행 계획을 수립할 수 있도록 통계 정보를 관리하는 방법을 다룹니다.

통계 정보는 옵티마이저가 실행 계획을 결정하는 데 사용하는 핵심 데이터입니다. 정확하고 최신의 통계 정보를 유지하는 것이 쿼리 성능 최적화의 기본입니다.

통계 수집

테이블 통계 수집

-- 테이블 통계 수집
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'HR',
  tabname => 'EMPLOYEES',
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO',
  cascade => TRUE
);

-- 파라미터 설명:
-- ownname: 스키마 이름
-- tabname: 테이블 이름
-- estimate_percent: 샘플링 비율 (AUTO_SAMPLE_SIZE 권장)
-- method_opt: 히스토그램 수집 방법
-- cascade: 인덱스 통계도 함께 수집

스키마 전체 통계 수집

-- 스키마 전체 통계 수집
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(
  ownname => 'HR',
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  cascade => TRUE,
  degree => 4  -- 병렬도
);

-- 데이터베이스 전체 통계 수집
EXEC DBMS_STATS.GATHER_DATABASE_STATS(
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  cascade => TRUE,
  degree => DBMS_STATS.DEFAULT_DEGREE
);

시스템 통계 수집

-- 시스템 통계 수집 시작
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('START');

-- 업무 시간대 동안 실행 (대표적인 워크로드 실행)
-- ... 업무 수행 ...

-- 시스템 통계 수집 종료
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('STOP');

-- 시스템 통계 확인
SELECT * FROM sys.aux_stats$;

통계 정보 확인

테이블 통계 확인

-- 테이블 통계 확인
SELECT table_name,
       num_rows,           -- 총 행 수
       blocks,             -- 블록 수
       avg_row_len,        -- 평균 행 길이
       last_analyzed,      -- 마지막 분석 일시
       stale_stats         -- 통계 정보 stale 여부
FROM user_tables
WHERE table_name = 'EMPLOYEES';

-- 모든 테이블의 통계 상태 확인
SELECT table_name, num_rows, last_analyzed,
       CASE
         WHEN last_analyzed IS NULL THEN 'Never'
         WHEN last_analyzed < SYSDATE - 7 THEN 'Old'
         ELSE 'Recent'
       END as stats_status
FROM user_tables
ORDER BY last_analyzed NULLS FIRST;

인덱스 통계 확인

-- 인덱스 통계 확인
SELECT index_name,
       blevel,              -- B-Tree 깊이
       leaf_blocks,         -- 리프 블록 수
       num_rows,            -- 인덱스 행 수
       distinct_keys,       -- 고유 키 수
       clustering_factor,   -- 클러스터링 팩터
       last_analyzed        -- 마지막 분석 일시
FROM user_indexes
WHERE table_name = 'EMPLOYEES';

컬럼 통계 확인

-- 컬럼 통계 확인
SELECT column_name,
       num_distinct,    -- 고유 값 개수
       density,         -- 밀도
       num_nulls,       -- NULL 개수
       histogram,       -- 히스토그램 유형
       last_analyzed
FROM user_tab_col_statistics
WHERE table_name = 'EMPLOYEES';

-- 히스토그램 상세 정보
SELECT column_name,
       endpoint_number,
       endpoint_value
FROM user_tab_histograms
WHERE table_name = 'EMPLOYEES'
  AND column_name = 'SALARY'
ORDER BY endpoint_number;

히스토그램 관리

-- 특정 컬럼에 히스토그램 생성
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'HR',
  tabname => 'EMPLOYEES',
  method_opt => 'FOR COLUMNS salary SIZE 254'
);

-- 히스토그램 유형:
-- SIZE 1: 히스토그램 없음
-- SIZE AUTO: 오라클이 자동 결정
-- SIZE SKEWONLY: 데이터 편향이 있을 때만
-- SIZE 254: 최대 버킷 수 지정

-- 히스토그램 삭제
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'HR',
  tabname => 'EMPLOYEES',
  method_opt => 'FOR COLUMNS salary SIZE 1'
);

통계 정보 고정 및 잠금

-- 통계 정보 잠금 (변경 방지)
EXEC DBMS_STATS.LOCK_TABLE_STATS('HR', 'EMPLOYEES');

-- 통계 정보 잠금 해제
EXEC DBMS_STATS.UNLOCK_TABLE_STATS('HR', 'EMPLOYEES');

-- 스키마 전체 잠금
EXEC DBMS_STATS.LOCK_SCHEMA_STATS('HR');

-- 잠금 상태 확인
SELECT table_name, stattype_locked
FROM user_tab_statistics
WHERE stattype_locked IS NOT NULL;

통계 정보 백업 및 복원

-- 통계 백업 테이블 생성
EXEC DBMS_STATS.CREATE_STAT_TABLE(
  ownname => 'HR',
  stattab => 'STATS_BACKUP'
);

-- 통계 정보 백업
EXEC DBMS_STATS.EXPORT_TABLE_STATS(
  ownname => 'HR',
  tabname => 'EMPLOYEES',
  stattab => 'STATS_BACKUP'
);

-- 통계 정보 복원
EXEC DBMS_STATS.IMPORT_TABLE_STATS(
  ownname => 'HR',
  tabname => 'EMPLOYEES',
  stattab => 'STATS_BACKUP'
);

-- 스키마 전체 백업/복원
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS(
  ownname => 'HR',
  stattab => 'STATS_BACKUP'
);

통계 정보 삭제

-- 테이블 통계 삭제
EXEC DBMS_STATS.DELETE_TABLE_STATS('HR', 'EMPLOYEES');

-- 컬럼 통계만 삭제
EXEC DBMS_STATS.DELETE_COLUMN_STATS(
  ownname => 'HR',
  tabname => 'EMPLOYEES',
  colname => 'SALARY'
);

-- 인덱스 통계 삭제
EXEC DBMS_STATS.DELETE_INDEX_STATS(
  ownname => 'HR',
  indname => 'IDX_EMP_DEPT'
);

자동 통계 수집

-- 자동 통계 수집 작업 확인
SELECT client_name, status
FROM dba_autotask_client
WHERE client_name = 'auto optimizer stats collection';

-- 자동 통계 수집 활성화
BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE(
    client_name => 'auto optimizer stats collection',
    operation => NULL,
    window_name => NULL
  );
END;
/

-- 자동 통계 수집 비활성화
BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'auto optimizer stats collection',
    operation => NULL,
    window_name => NULL
  );
END;
/

-- 자동 통계 수집 이력 확인
SELECT operation_name, target, start_time, end_time, status
FROM dba_optstat_operations
ORDER BY start_time DESC
FETCH FIRST 10 ROWS ONLY;

증분 통계 (파티션 테이블)

-- 증분 통계 활성화
EXEC DBMS_STATS.SET_TABLE_PREFS(
  ownname => 'HR',
  tabname => 'SALES',
  pname => 'INCREMENTAL',
  pvalue => 'TRUE'
);

-- 새 파티션에 대해서만 통계 수집
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'HR',
  tabname => 'SALES',
  granularity => 'AUTO',
  incremental => TRUE
);

Stale 통계 관리

-- Stale 통계 확인
SELECT table_name, stale_stats, last_analyzed
FROM user_tab_statistics
WHERE stale_stats = 'YES';

-- Stale 통계 모니터링 활성화
EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

-- 테이블 변경 모니터링 확인
SELECT table_name, inserts, updates, deletes, truncated
FROM user_tab_modifications
ORDER BY timestamp DESC;

통계 환경설정 관리

-- 테이블 통계 환경설정 확인
SELECT preference_name, preference_value
FROM user_tab_stat_prefs
WHERE table_name = 'EMPLOYEES';

-- 전역 통계 환경설정 설정
EXEC DBMS_STATS.SET_GLOBAL_PREFS(
  pname => 'ESTIMATE_PERCENT',
  pvalue => 'DBMS_STATS.AUTO_SAMPLE_SIZE'
);

-- 테이블별 통계 환경설정
EXEC DBMS_STATS.SET_TABLE_PREFS(
  ownname => 'HR',
  tabname => 'EMPLOYEES',
  pname => 'METHOD_OPT',
  pvalue => 'FOR ALL COLUMNS SIZE AUTO'
);

통계 정보 튜닝 체크리스트

• 정기적인 통계 수집 스케줄 설정
• 대량 DML 후 통계 수집
• 히스토그램 필요 컬럼 식별
• Stale 통계 모니터링
• 파티션 테이블 증분 통계 활용
• 시스템 통계 수집 (I/O 특성 반영)
• 통계 잠금 필요 테이블 관리
• 자동 통계 수집 작업 모니터링

실전 예제

-- 예제 1: 주간 통계 수집 스크립트
BEGIN
  -- 1. Stale 통계가 있는 테이블만 수집
  DBMS_STATS.GATHER_SCHEMA_STATS(
    ownname => 'HR',
    options => 'GATHER STALE',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO',
    cascade => TRUE,
    degree => 4
  );

  -- 2. 통계 수집 이력 로깅
  INSERT INTO stats_collection_log
  VALUES (SYSDATE, 'HR', 'SUCCESS');
  COMMIT;
END;
/

-- 예제 2: 특정 테이블 상세 통계 수집
BEGIN
  -- 통계 백업
  DBMS_STATS.EXPORT_TABLE_STATS(
    ownname => 'HR',
    tabname => 'EMPLOYEES',
    stattab => 'STATS_BACKUP'
  );

  -- 새 통계 수집
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'HR',
    tabname => 'EMPLOYEES',
    estimate_percent => 100,  -- 전체 샘플링
    method_opt => 'FOR ALL INDEXED COLUMNS SIZE 254',
    cascade => TRUE,
    no_invalidate => FALSE
  );
END;
/

-- 예제 3: 통계 정보 검증
SELECT t.table_name,
       t.num_rows,
       t.last_analyzed,
       ROUND((SYSDATE - t.last_analyzed), 1) as days_old,
       m.inserts,
       m.updates,
       m.deletes,
       CASE
         WHEN t.num_rows > 0 THEN
           ROUND((m.inserts + m.updates + m.deletes) / t.num_rows * 100, 2)
         ELSE 0
       END as change_percent
FROM user_tables t
LEFT JOIN user_tab_modifications m ON t.table_name = m.table_name
WHERE t.num_rows > 1000
ORDER BY change_percent DESC NULLS LAST;

관련 문서

• 쿼리 튜닝 가이드
• 실행 계획 분석
• 인덱스 최적화