오라클 DBMS_STATS로 통계 정보 업데이트하기
데이터베이스 성능 최적화는 관리자에게 가장 중요한 과제 중 하나입니다. Oracle Database에서는 DBMS_STATS 패키지를 사용하여 통계 정보를 관리하고 업데이트함으로써 효율적인 쿼리 실행 계획을 수립할 수 있습니다. 이번 글에서는 DBMS_STATS를 활용하여 통계 정보를 업데이트하는 방법과 주의사항, 실무에서의 적용 사례를 상세히 살펴보겠습니다.
1. DBMS_STATS란 무엇인가?
DBMS_STATS는 오라클 데이터베이스의 통계 정보를 수집, 수정, 삭제, 복원하는 기능을 제공하는 패키지입니다.
통계 정보는 옵티마이저가 쿼리 실행 계획을 생성할 때 사용하는 주요 데이터로, 정확한 통계가 제공될수록 더 나은 실행 계획을 수립할 수 있습니다.
주요 기능:
- 테이블, 인덱스, 스키마, 데이터베이스 수준의 통계 수집
- 기존 통계 백업 및 복원
- 히스토그램 생성
- 특정 대상의 통계 삭제
2. DBMS_STATS로 통계 업데이트하는 방법
DBMS_STATS를 활용해 통계를 업데이트하는 기본적인 방법은 DBMS_STATS.GATHER_*_STATS 프로시저를 호출하는 것입니다. 이 중 자주 사용되는 주요 프로시저는 다음과 같습니다.
- 스키마 수준 통계 업데이트: GATHER_SCHEMA_STATS
스키마 내 모든 객체의 통계를 수집합니다.
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');
- 테이블 수준 통계 업데이트: GATHER_TABLE_STATS
특정 테이블과 관련된 통계를 수집합니다.
EXEC DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'SCOTT',
tabname => 'EMP',
estimate_percent => 50,
method_opt => 'FOR ALL COLUMNS SIZE AUTO'
);
- 데이터베이스 수준 통계 업데이트: 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. 통계 수집 시 주의사항
- 성능 영향
통계를 수집하는 동안 시스템 성능에 영향을 줄 수 있으므로, 주요 작업 시간 외에 실행하는 것이 좋습니다. - 옵티마이저와의 연계
잘못된 통계는 오히려 성능을 저하할 수 있습니다. 수집 주기를 설정하거나, 필요할 때만 수집하도록 운영 정책을 수립해야 합니다. - 자동 통계 수집
Oracle 10g 이상 버전에서는 AUTO_TASKS 기능으로 통계를 자동으로 수집할 수 있습니다.
BEGIN
DBMS_SCHEDULER.ENABLE('SYS.AUTO_STATS_JOB');
END;
5. 결론
DBMS_STATS는 Oracle Database에서 옵티마이저가 최적의 실행 계획을 생성할 수 있도록 통계 정보를 제공하는 필수 도구입니다. 정확하고 주기적인 통계 업데이트를 통해 데이터베이스 성능을 극대화할 수 있습니다. 실무에서는 업무 환경에 맞는 통계 수집 전략을 수립하고, 필요한 옵션을 적절히 활용하여 효율적인 관리가 가능하도록 하는 것이 중요합니다.
통계 관리와 관련된 질문이나 추가적인 실무 사례가 있다면 언제든지 댓글로 남겨주세요!