Materialized view exception diagnosis

Materialized views (MVs) in OceanBase Database may become out of sync with base tables due to delayed or stuck incremental refresh.

This topic provides diagnostic procedures and key system views to help you quickly locate and resolve exceptions.

Exception scenarios and monitoring metrics

Exception scenarios

Incremental refresh of materialized views may slow down or get stuck due to the following reasons:

• Data surge: The update volume of base tables far exceeds expectations.
• Refresh task blocking: SQL execution performance issues or resource contention.
• Scheduled task failure: For example, DBMS_SCHEDULER scheduling exception.

Key monitoring metrics

Monitor the MV data status through the following fields of the DBA_MVIEWS system view:

• data_sync_delay: The data synchronization delay of the materialized view, in seconds.

  Alarm trigger condition: When data_sync_delay exceeds the threshold set by the business, immediate diagnosis is required.

• data_sync_scn: The data checkpoint of the materialized view.

  Note

  For OceanBase Database V4.3.5, the `data_sync_delay` and `data_sync_scn` fields were introduced in the `DBA_MVIEWS` system view starting from V4.3.5 BP2.

System views related to materialized views

Materialized view exception diagnosis procedure

Procedure 1: Determine whether the alarm MV is a nested materialized view

• If it is a nested materialized view, trace the dependency chain to find the lowest-level MV where the problem occurs and designate it as the target MV. Then proceed to procedure 2.

• If it is not a nested materialized view, this MV is the target MV. Then proceed to procedure 2.

Example query:

SELECT * FROM oceanbase.DBA_MVIEW_DEPS;

Procedure 2: Check whether a refresh task exists for the target MV

1. Query running tasks.

   Example query:

   Find the running tasks of a specified MV.

   SELECT * FROM oceanbase.DBA_MVIEW_RUNNING_JOBS
   WHERE table_name LIKE '%MVIEW%';
   

2. Determine the status:

   • If a refresh task exists, it means that this MV refresh is taking too long and has not been completed, causing data_sync_scn to not be updated in time. The specific cause needs to be determined. Then proceed to procedure 4.

   • If no refresh task exists, a program bug may have been encountered. Then proceed to procedure 3.

Procedure 3: Scheduled task fault diagnosis

If DBMS_SCHEDULER scheduling fails (for example, the task is deregistered after 16 consecutive failures):

1. View the scheduling history to confirm the issue.

   Example query:

   SELECT * FROM oceanbase.DBA_SCHEDULER_JOBS;
   

   You can query whether there are 16 failure records with code 5019 in DBA_SCHEDULER_JOB_RUN_DETAILS. The reference statement is as follows:

   SELECT OWNER, JOB_NAME, count(*) > 16 FROM oceanbase.DBA_SCHEDULER_JOB_RUN_DETAILS WHERE CODE = '-5019';
   

2. Upgrade the version: If the issue is caused by a known bug, upgrade to the latest version to fix it.

Procedure 4: Determine whether the base table has experienced update operations exceeding the historical average level

1. If the amount of modified data has increased significantly, the refresh time will also be correspondingly longer. In this case, the MV data lagging behind may be expected. If the data volume growth is an occasional business fluctuation, you can appropriately increase the threshold of data_sync_delay to avoid false alarms. If the modification volume continues to grow, you need to solve the problem by adjusting the materialized view refresh interval, increasing the materialized view refresh parallelism, increasing machine resources, and other solutions.

2. If the amount of modified data has not increased significantly, the increase in refresh time is not expected:

   1. If this refresh operation has ended, or previous refresh tasks have similar behavior, you can query views such as DBA_MVREF_STATS to analyze the information of completed tasks. Then proceed to procedure 5.
   2. If the views cannot provide information, contact technical support for further investigation. Then proceed to procedure 6.

Procedure 5: Real-time stuck task diagnosis

1. Query the overview of the last several refreshes of the materialized view with abnormal refresh time.

   SELECT * 
   FROM oceanbase.DBA_MVREF_RUN_STATS run_stats, oceanbase.DBA_MVREF_STATS stats
   WHERE run_stats.refresh_id = stats.refresh_id
   AND run_stats.MVIEWS LIKE '%mv1%'
   ORDER BY run_stats.start_time DESC
   LIMIT 10;
   

   Focus on the following information:

   Field	Meaning	Description
   REFRESH_ID	The identifier of this refresh operation.
   ELAPSED_TIME	The total time of this refresh operation, in seconds.
   REFRESH_METHOD	The refresh method.	Check whether the refresh method is correct and whether a full refresh was mistakenly used.
   PARALLELISM	The refresh parallelism.	Check whether the parallelism is too low.
   LOG_PURGE_TIME	The time spent on cleaning up MLOG during this refresh.	Check whether a large amount of time was spent on Purge.
   INITIAL_NUM_ROWS	The number of rows before this materialized view refresh.
   FINAL_NUM_ROWS	The number of rows after this materialized view refresh.	Check whether the change in the number of rows before and after is too large.

2. Find the REFRESH_ID of the abnormal refresh operation from the above results and further view its refresh details.

   SELECT *
   FROM oceanbase.DBA_MVREF_CHANGE_STATS
   WHERE refresh_id = xxx;
   

   Query the oceanbase.DBA_MVREF_CHANGE_STATS view to confirm the data change situation of the base table. If the number of newly inserted, updated, or deleted rows has increased compared to the previous levels, the longer refresh time is expected. Query the data change situation of the base tables related to the materialized view and focus on the following information:

   Field	Meaning
   REFRESH_ID	The identifier of this refresh operation.
   TBL_NAME	The base table name.
   NUM_ROWS_INS	The number of rows newly inserted into the base table since the last refresh.
   NUM_ROWS_UPD	The number of rows modified in the base table since the last refresh.
   NUM_ROWS_DEL	The number of rows deleted from the base table since the last refresh.
   NUM_ROWS	The total number of rows in the base table at the time of this refresh.

Solutions and optimization suggestions

• Data surge scenarios:

  • Increase the refresh parallelism.
  • Split large base tables or optimize the MV architecture.

• SQL performance issues:

  • Optimize base table indexes or MV query statements.
  • Use partitioned tables to accelerate incremental data processing.

• Insufficient resources:

  Increase server resources (CPU, memory) or adjust the task scheduling time window.

References

For more information about refreshing materialized views, see Refresh a materialized view (MySQL-compatible mode) or Refresh a materialized view (Oracle-compatible mode).