오라클 DBMS_STATS로 통계 정보 업데이트하기

 

데이터베이스 성능 최적화는 관리자에게 가장 중요한 과제 중 하나입니다. Oracle Database에서는 DBMS_STATS 패키지를 사용하여 통계 정보를 관리하고 업데이트함으로써 효율적인 쿼리 실행 계획을 수립할 수 있습니다. 이번 글에서는 DBMS_STATS를 활용하여 통계 정보를 업데이트하는 방법과 주의사항, 실무에서의 적용 사례를 상세히 살펴보겠습니다.

---

1. DBMS_STATS란 무엇인가?

DBMS_STATS는 오라클 데이터베이스의 통계 정보를 수집, 수정, 삭제, 복원하는 기능을 제공하는 패키지입니다.
통계 정보는 옵티마이저가 쿼리 실행 계획을 생성할 때 사용하는 주요 데이터로, 정확한 통계가 제공될수록 더 나은 실행 계획을 수립할 수 있습니다.

주요 기능:

• 테이블, 인덱스, 스키마, 데이터베이스 수준의 통계 수집
• 기존 통계 백업 및 복원
• 히스토그램 생성
• 특정 대상의 통계 삭제

---

2. DBMS_STATS로 통계 업데이트하는 방법

DBMS_STATS를 활용해 통계를 업데이트하는 기본적인 방법은 DBMS_STATS.GATHER_*_STATS 프로시저를 호출하는 것입니다. 이 중 자주 사용되는 주요 프로시저는 다음과 같습니다.

1. 스키마 수준 통계 업데이트: GATHER_SCHEMA_STATS
   스키마 내 모든 객체의 통계를 수집합니다.

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');

1. 테이블 수준 통계 업데이트: GATHER_TABLE_STATS
   특정 테이블과 관련된 통계를 수집합니다.

EXEC DBMS_STATS.GATHER_TABLE_STATS( 
    ownname => 'SCOTT', 
    tabname => 'EMP', 
    estimate_percent => 50, 
    method_opt => 'FOR ALL COLUMNS SIZE AUTO' 
);

1. 데이터베이스 수준 통계 업데이트: GATHER_DATABASE_STATS
   데이터베이스의 모든 객체에 대해 통계를 수집합니다.

EXEC DBMS_STATS.GATHER_DATABASE_STATS(estimate_percent => 10);

---

3. 실무에서 유용한 예제

예제 1: 특정 테이블의 통계만 갱신

대규모 데이터가 삽입된 테이블의 통계만 갱신하고 싶다면 GATHER_TABLE_STATS를 사용합니다.

EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'HR', 
    tabname => 'EMPLOYEES', 
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
);

Tip: AUTO_SAMPLE_SIZE는 자동으로 최적의 샘플 크기를 계산해 효율적인 통계 수집을 지원합니다.

예제 2: 파티션 테이블의 통계 갱신

파티션 테이블에서는 각 파티션에 대한 통계를 개별적으로 관리할 수 있습니다.

EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SALES', 
    tabname => 'TRANSACTIONS', 
    partname => 'PARTITION_2023', 
    cascade => TRUE
);

cascade 옵션은 테이블과 연관된 인덱스 통계도 함께 갱신합니다.

예제 3: 특정 히스토그램 생성

데이터 분포가 특정 열에서 비대칭적이라면 히스토그램을 생성해 옵티마이저가 더 정확한 실행 계획을 수립하도록 돕습니다.

EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'HR',
    tabname => 'EMPLOYEES',
    method_opt => 'FOR COLUMNS SALARY SIZE 5'
);

SALARY 열에 대해 5개의 버킷 크기로 히스토그램을 생성합니다.

---

4. 통계 수집 시 주의사항

1. 성능 영향
   통계를 수집하는 동안 시스템 성능에 영향을 줄 수 있으므로, 주요 작업 시간 외에 실행하는 것이 좋습니다.
2. 옵티마이저와의 연계
   잘못된 통계는 오히려 성능을 저하할 수 있습니다. 수집 주기를 설정하거나, 필요할 때만 수집하도록 운영 정책을 수립해야 합니다.
3. 자동 통계 수집
   Oracle 10g 이상 버전에서는 AUTO_TASKS 기능으로 통계를 자동으로 수집할 수 있습니다.

BEGIN 
  DBMS_SCHEDULER.ENABLE('SYS.AUTO_STATS_JOB'); 
END;

---

5. 결론

DBMS_STATS는 Oracle Database에서 옵티마이저가 최적의 실행 계획을 생성할 수 있도록 통계 정보를 제공하는 필수 도구입니다. 정확하고 주기적인 통계 업데이트를 통해 데이터베이스 성능을 극대화할 수 있습니다. 실무에서는 업무 환경에 맞는 통계 수집 전략을 수립하고, 필요한 옵션을 적절히 활용하여 효율적인 관리가 가능하도록 하는 것이 중요합니다.

통계 관리와 관련된 질문이나 추가적인 실무 사례가 있다면 언제든지 댓글로 남겨주세요!