How to Troubleshoot Slow Materialized View Complete Refresh Issues
(Doc ID 1313291.1)
Last updated on FEBRUARY 03, 2019
Oracle Database - Enterprise Edition - Version 22.214.171.124 and later Information in this document applies to any platform.
This article describes how to troubleshoot slow materialized view complete refresh in order to find the cause of the slowness, and it provides directions towards the solution.
In simplified terms complete refresh consists of the following phases:
removing existing rows from the mview,
generating result set of the mview definition query, and inserting the new rows into the mview
maintaining mview logs, deleting rows that are no longer necessary from mview logs, setting value for the snaptime$$ column (provided the master table referenced in the mview definition query has mview log on it), refer to <Note 236233.1> for details about mview log operations.
Several internal/recursive SQLs are involved in the above mentioned phases.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!