How to Troubleshoot Slow Materialized View Complete Refresh Issues
(Doc ID 1313291.1)
Last updated on SEPTEMBER 20, 2022
Applies to:
Oracle Database Exadata Express Cloud Service - Version N/A and laterGen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
Purpose
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.
Troubleshooting Steps
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Purpose |
| Troubleshooting Steps |
| 1./ Has the complete refresh or any of its parts ever performed better? |
| 2./ Which internal/recursive SQL(s) is (are) the most time consuming |
| 2.1./ DELETE FROM mview is slow |
| 2.2./ INSERT INTO mview SELECT ... is slow |
| 2.3./ SQLs on MLOG$_masterTable tables is slow |
| References |