Materialized view exception diagnosis

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

This topic describes the diagnosis process and key system views to help you quickly locate and resolve issues.

Exception scenarios and monitoring metrics

Exception scenarios

Incremental refresh of materialized views may slow down or stall for the following reasons:

• Sudden data volume increase: Base table updates exceed expectations.
• Refresh task blocking: SQL execution performance issues or resource contention.
• Scheduler task failure: For example, DBMS_SCHEDULER scheduling exceptions.

Key monitoring metrics

Monitor MV data status by using the following columns of the DBA_MVIEWS system view:

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

  Alert condition: When data_sync_delay exceeds the business-defined threshold, diagnose immediately.

• data_sync_scn: Data position of the materialized view.

  Note

  For OceanBase Database V4.3.5, the data_sync_delay and data_sync_scn columns were added to the DBA_MVIEWS system view starting from V4.3.5 BP2.

Materialized view system views

Materialized view exception diagnosis process

Step 1: Determine whether the alerted MV is a nested materialized view

• If it is a nested materialized view, follow the dependency chain to find the innermost MV with the issue and treat it as the target MV. Then proceed to Step 2.
• If it is not a nested materialized view, this MV is the target MV. Then proceed to Step 2.

Sample query:

SELECT * FROM oceanbase.DBA_MVIEW_DEPS;

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

1. Query running tasks.

   Sample query:

   Find running tasks for the specified MV:

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

2. Determine the status:

   • If a refresh task exists, the current MV refresh is taking too long and has not completed, so data_sync_scn has not been updated in time. Identify the root cause and proceed to Step 4.
   • If no refresh task exists, a program bug may be involved. Proceed to Step 3.

Step 3: Scheduler task failure troubleshooting

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

1. Check the scheduling history to confirm the issue.

   Sample query:

   SELECT * FROM oceanbase.DBA_SCHEDULER_JOBS;
   

   You can query whether DBA_SCHEDULER_JOB_RUN_DETAILS has 16 records with code 5019:

   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.

Step 4: Determine whether the base table had updates beyond the historical average

1. If the volume of modified data has increased significantly, refresh time will also increase. In this case, MV data lag may be expected. If the data volume increase is occasional business fluctuation, you can raise the data_sync_delay threshold to avoid false alerts. If the modification volume will continue to grow, adjust the materialized view refresh cycle, increase refresh parallelism, or add machine resources.
2. If the volume of modified data has not increased significantly and the increased refresh time is unexpected:
   • If the refresh has completed or previous refresh tasks had similar behavior, query the DBA_MVREF_STATS and other views to analyze the completed task. Then proceed to Step 5.
   • If the views do not provide sufficient information, contact technical support for further troubleshooting. Then proceed to Step 6.

Step 5: Real-time stalled task diagnosis

1. Query the overview of the last few refreshes for the materialized view with abnormal refresh duration:

   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:

   Column	Meaning	Description
   REFRESH_ID	Identifier of the refresh operation.
   ELAPSED_TIME	Total duration of the refresh operation, in seconds.
   REFRESH_METHOD	Refresh method.	Check whether the refresh method is correct and whether full refresh was used by mistake.
   PARALLELISM	Refresh parallelism.	Check whether parallelism is too low.
   LOG_PURGE_TIME	Time spent on MLOG cleanup in this refresh.	Check whether much time was spent on Purge.
   INITIAL_NUM_ROWS	Number of rows in the materialized view before refresh.
   FINAL_NUM_ROWS	Number of rows in the materialized view after refresh.	Check whether the row count change is too large.

2. Find the REFRESH_ID of the abnormal refresh from the result and 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 base table data changes. If the number of newly inserted, updated, or deleted rows has increased compared with the historical level, the longer refresh time is expected. Focus on the following information:

   Column	Meaning
   REFRESH_ID	Identifier of the refresh operation.
   TBL_NAME	Base table name.
   NUM_ROWS_INS	Number of rows inserted in the base table since the last refresh.
   NUM_ROWS_UPD	Number of rows updated in the base table since the last refresh.
   NUM_ROWS_DEL	Number of rows deleted from the base table since the last refresh.
   NUM_ROWS	Total number of rows in the base table at the time of this refresh.

Solutions and optimization suggestions

• Sudden data volume increase: Increase 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 speed up incremental data processing.
• Insufficient resources: Add server resources (CPU, memory) or adjust the task scheduling time window.

References

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