My Oracle Support Banner

How To Troubleshoot Materialized View Fast Log Timestamp-based Refresh Performance Issues (Doc ID 1388637.1)

Last updated on MARCH 12, 2021

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata 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
Information in this document applies to any platform.

Purpose

This note was created to facilitate troubleshooting materialized view fast log timestamp-based refresh performance issues.

MV Fast Refresh includes the following 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./ Verify  that Fast refresh is being used
 2./ What type of fast refresh is running?
 3./ How large is the mview log
 
4./ Which phase / recursive SQL is time consuming
 5./ SETUP phase
 6./ MV Refresh phase - Instantiate
 6.1./ INSERT INTO <MV> SELECT /*+ NO_MERGE("JV$") */ .. is slow due to NO_MERGE hint
 6.2./ INSERT INTO <MV> | DELETE FROM <MV> | UPDATE <MV> single row DML executed many times for single table MVs
 7./ Wrap-up phase
 DELETE FROM MLOG$_<master table >
 8./ Distributed MV refresh specifics
 MV specific parameters that influence refresh SQLs
 Still have questions?
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.