[ORACLE] ALL_MVIEW_ANALYSIS 완전 정복 : MV 리라이팅 분석과 활용 전략

[Oracle] ALL_MVIEW_ANALYSIS

 

Oracle 데이터베이스의 물리적 뷰(Materialized View, MV)는 집계, 조인, 복잡한 쿼리의 결과를 사전 저장함으로써 질의 성능을 획기적으로 개선할 수 있습니다. 그러나 성능 향상의 핵심은 단순히 MV를 만드는 데 있지 않고, 실제 쿼리 수행 시 해당 MV가 자동으로 재작성(query rewrite)되어 사용될 수 있느냐에 달려 있습니다. ALL_MVIEW_ANALYSIS 뷰는 바로 이러한 MV 리라이팅 가능 여부를 사전 분석하고, 어떤 제약 조건으로 인해 MV가 사용되지 않는지를 확인할 수 있는 중요한 도구입니다.

ALL_MVIEW_ANALYSIS란?

ALL_MVIEW_ANALYSIS는 사용자가 접근 가능한 물리적 뷰에 대해, 해당 뷰가 질의 재작성(Query Rewrite)에 사용 가능한지 여부와 사용 불가능한 경우의 사유를 알려주는 Oracle 데이터 딕셔너리 뷰입니다.

주요 컬럼 설명

• QUERY: MV에 적용 가능한 원본 SQL 또는 테스트 쿼리
• MVIEW_NAME: 분석 대상 물리적 뷰 이름
• REWRITE_ENABLED: MV가 리라이팅에 사용 가능한지 여부 (Y/N)
• REWRITE_CAPABILITY: 리라이팅 가능 유형 (FULL, PARTIAL, TEXT_MATCH, 등)
• REWRITE_REASON: MV가 리라이팅에 사용되지 못하는 이유

활용 예제

예제 1: 현재 사용자 MV 중 리라이팅 가능한 목록 조회

SELECT mview_name, rewrite_enabled, rewrite_capability
FROM all_mview_analysis
WHERE rewrite_enabled = 'Y';

예제 2: 특정 MV가 리라이팅되지 않는 사유 분석

SELECT mview_name, rewrite_enabled, rewrite_reason
FROM all_mview_analysis
WHERE mview_name = 'SALES_SUMMARY_MV';

예제 3: PARTIAL 수준의 리라이팅이 가능한 MV 필터링

SELECT mview_name, rewrite_capability
FROM all_mview_analysis
WHERE rewrite_capability = 'PARTIAL';

관련 뷰 비교

뷰 이름	설명	주요 활용
ALL_MVIEW_ANALYSIS	MV가 쿼리 리라이팅에 사용 가능한지 여부 분석	MV 활용 가능성 평가
ALL_MVIEWS	MV 정의 및 리프레시 속성 조회	MV 메타데이터 확인
ALL_REWRITE_EQUIVALENCES	리라이팅에서 사용되는 동등 식별 규칙	동등 조건 식별 및 디버깅

실무 최적화 전략

• REWRITE ENABLED 설정 확인: MV 생성 시 ENABLE QUERY REWRITE 옵션 필수
• 리라이팅 사유 분석: REWRITE_REASON 값이 'JOIN BACK' 또는 'GROUP BY MISMATCH'일 경우 MV 쿼리 수정 필요
• QUERY_REWRITE_ENABLED 파라미터 확인: 시스템 및 세션 레벨에서 TRUE로 설정
• 실제 실행 계획 확인: MV가 사용되었는지 확인하려면 EXPLAIN PLAN 또는 AUTOTRACE 사용
• 정규화된 뷰 구조 구성: 복잡한 조인, 서브쿼리를 피하고 단순화된 구조 권장

실제 적용 사례

한 글로벌 제조사는 제품별 월간 판매 실적을 사전 계산한 MV를 운영 중이었으나, 예상과 달리 SELECT 쿼리 실행 시 MV가 사용되지 않아 성능 저하가 계속 발생했습니다. ALL_MVIEW_ANALYSIS 뷰를 통해 MV 리라이팅이 실패하는 사유를 분석한 결과, 다음과 같은 문제가 발견되었습니다:

1. MV 생성 시 ENABLE QUERY REWRITE 옵션 누락
2. 쿼리에서 사용한 JOIN 조건이 MV 정의와 불일치
3. GROUP BY 순서와 컬럼명이 MV와 상이

이후 MV를 재생성하고, SELECT 쿼리도 MV 구조에 맞춰 리팩토링한 결과, 동일 쿼리의 처리 시간이 8초에서 0.4초로 줄어드는 성과를 얻었습니다.

결론

ALL_MVIEW_ANALYSIS는 Oracle의 쿼리 리라이팅 기능을 제대로 활용하기 위해 반드시 점검해야 할 뷰입니다. MV가 존재한다고 해서 자동으로 사용되는 것이 아니며, 실제로 활용되기 위해선 여러 조건이 충족되어야 합니다. 이 뷰를 통해 리라이팅 가능성을 사전 진단하고, 불필요한 MV를 제거하거나 리팩토링하는 것이 MV 전략의 핵심입니다.

출처

• Oracle Database Reference: https://docs.oracle.com/en/database/
• Oracle Data Warehousing Guide 21c
• 고급 DBA 및 성능 튜닝 실무 사례