[ORACLE] ALL_MVIEW_AGGREGATES 완벽 분석 : 물리적 뷰 집계 함수 추적과 최적화 전략

[Oracle] ALL_MVIEW_AGGREGATES

 

Oracle의 물리적 뷰(Materialized View, MV)는 반복적인 계산 비용이 높은 쿼리를 사전 집계 및 저장함으로써 성능을 획기적으로 향상시키는 핵심 기능입니다. 특히 SUM, COUNT, AVG 등 집계 함수(aggregate functions)가 포함된 MV의 성능 분석 및 유지 관리를 위해 ALL_MVIEW_AGGREGATES 뷰는 매우 중요한 역할을 합니다. 본 문서에서는 ALL_MVIEW_AGGREGATES의 구조와 활용 사례, 다른 MV 관련 뷰와의 비교, 실무에서의 활용 전략까지 전문적으로 정리합니다.

ALL_MVIEW_AGGREGATES란?

ALL_MVIEW_AGGREGATES는 사용자가 접근할 수 있는 모든 물리적 뷰 중에서 집계 함수가 포함된 컬럼에 대한 정보를 보여주는 Oracle 데이터 딕셔너리 뷰입니다. 이 뷰를 통해 특정 MV에 어떤 집계 함수가 포함되어 있는지를 명확하게 확인할 수 있으며, MV 성능 튜닝이나 리프레시 전략 수립 시 유용하게 활용됩니다.

주요 컬럼 설명

• OWNER: MV를 소유한 사용자
• MVIEW_NAME: 물리적 뷰의 이름
• AGGREGATE: 사용된 집계 함수 (예: SUM, COUNT)
• EXPRESSION: 집계 함수가 적용된 표현식 또는 컬럼
• QUERY_TEXT: 해당 집계 함수가 포함된 원본 쿼리 일부

활용 예제

예제 1: 특정 MV에 사용된 집계 함수 확인

SELECT aggregate, expression
FROM all_mview_aggregates
WHERE mview_name = 'SALES_SUMMARY_MV'
AND owner = 'SALES';

예제 2: SUM 또는 COUNT만 포함된 MV 필터링

SELECT mview_name, aggregate, expression
FROM all_mview_aggregates
WHERE aggregate IN ('SUM', 'COUNT');

예제 3: 사용자 기준 집계 MV 목록 조회

SELECT DISTINCT mview_name
FROM all_mview_aggregates
WHERE owner = 'HR';

관련 뷰 비교

뷰 이름	설명	주요 용도
ALL_MVIEW_AGGREGATES	MV에 사용된 집계 함수 정보	성능 튜닝, 집계식 확인
ALL_MVIEWS	MV 정의 및 리프레시 전략 정보	MV 메타데이터 관리
ALL_MVIEW_LOGS	FAST 리프레시용 MV 로그 정보	증분 리프레시 구성 확인

실무 최적화 전략

• 집계 함수 단순화: COUNT(DISTINCT) 또는 복잡한 CASE문은 MV 성능 저하 요인이 될 수 있음
• 분산 키 기반 집계 구성: GROUP BY 절의 컬럼이 파티션 키와 일치하도록 설계
• MV 쿼리 분리 전략: 단일 MV에 과도한 집계를 포함하지 말고, 업무 목적별로 분리 구성
• EXPRESSION 모니터링: ALL_MVIEW_AGGREGATES로 각 표현식을 주기적으로 검토하여 비효율 구조 사전 차단
• 리프레시 정책 맞춤 설정: 고빈도 집계일수록 FAST 또는 ON COMMIT 리프레시 방식 권장

실제 적용 사례

한 보험회사는 고객 청구 건수와 지불금액에 대한 통계 보고를 위해 MV를 도입했으며, 내부 감사 기준상 매일 2회 리프레시가 필수였습니다. 초기에는 여러 복잡한 집계가 포함된 단일 MV를 사용했지만, 리프레시 시간이 과도하게 소요되는 문제가 발생했습니다.

ALL_MVIEW_AGGREGATES 뷰를 활용하여 MV 내 SUM, AVG, COUNT 함수의 표현식을 모두 분석하고, 복잡한 표현식을 단순화한 뒤 다음과 같은 방식으로 개선했습니다.

1. 단일 MV를 3개의 주제별 MV로 분할
2. 복잡한 식을 사전 가공한 중간 테이블로 치환
3. 리프레시 방식을 ON COMMIT에서 FAST ON DEMAND로 변경
4. MV 로그 구성 최적화

결과적으로 리프레시 시간이 60% 이상 단축되었고, 보고서 생성 시간도 대폭 개선되었습니다.

결론

ALL_MVIEW_AGGREGATES 뷰는 Oracle에서 물리적 뷰의 성능을 정밀하게 제어하고 최적화하기 위한 핵심 도구입니다. 집계 함수가 MV 내 성능 병목이 되는 경우가 많기 때문에 이 뷰를 활용한 진단과 분석은 필수적입니다. 주기적인 점검과 구조 최적화를 통해 데이터 웨어하우스, 분석 시스템의 운영 안정성과 효율성을 높일 수 있습니다.

출처

• Oracle Database Reference: https://docs.oracle.com/en/database/
• Oracle Data Warehousing Guide 21c
• 고객 사례 기반 실무 튜닝 경험