Help Center/ Data Warehouse Service/ Developer Guide/ Developer Guide (9.1.1.x)/ DWS Materialized Views/ Materialized View Design and Practice/ Monitoring and O&M of Materialized Views
Updated on 2026-07-02 GMT+08:00
View PDF
Share

Monitoring and O&M of Materialized Views

Key Monitoring Metrics of Materialized Views

Table 1 Key monitoring metrics

Category

Metric

Recommended Threshold

Data timeliness
  • Last refresh time
  • Number of invalid partitions

Report an alarm if the data is invalid and is not refreshed after twice the refresh interval.

Query performance
  • Query rewriting hit ratio
  • Query performance on the materialized view after rewriting

Ensure that the query performance is significantly improved compared with that of the base table.

Refresh performance
  • Refresh duration
  • Number of refresh failures

Compare with historical data and pay attention to sudden increases.

Storage usage

Storage size of the materialized view and auxiliary table

Regularly evaluate the cost-effectiveness of storage overhead.

Common System Views for Monitoring Materialized Views

  1. PG_MATVIEW: attribute information of a materialized view
  2. PG_MATVIEW_PARTITION: partition attribute information of a materialized view
  3. PG_MATVIEW_DEPEND: dependency information between a materialized view and its base table
  4. PV_MATVIEW_DETAIL: comprehensive display of attributes, dependencies, and refresh information

Common SQL Statements for Monitoring Materialized Views

  • Check the status of all materialized views. 
    1
    		 
    SELECT * FROM pv_matview_detail ORDER BY matview;
  • View refresh statistics. 
    1
2
    		 
    SELECT matview,refresh_mode,refresh_method,mapping,active,refresh_count,last_refresh_start,last_refresh_end,(last_refresh_end-last_refresh_start) duration 
FROM pv_matview_detail ORDER BY matview;
  • View query rewriting statistics. 
    1
    		 
    SELECT matview,active,rewrite_count FROM pv_matview_detail ORDER BY matview;
  • View the mapping between materialized views and base table partitions. 
    1
    		 
    SELECT mapping as "base partition -> matview partition"  FROM pv_matview_detail  WHERE matview = 'test_base_mv1';
Parent Topic: Materialized View Design and Practice