Mir Sayeed Hassan – Oracle Blog

Efficient Materialized Views Fast Refresh Insights and Troubleshooting Techniques in Oracle Database 19C

Overview 

The main purpose of this article is to understand on Materialized Views (MV) with some advance concepts which will help in troubleshooting issues related to mviews in real-time scenario.

Here i’ll be discussing on a better understanding on what to look for and faced related issue with mviews. 

For your information and for those DBAs familiar with materialized views (MV) for them this blog delivers concise, optimized insights to improve performance and usage as expected.

Materialized View Fast Refresh Concepts.

To begin, let’s examine the key objects and components involved in fast refresh in this mviews 

Additionally, an index named I_SNAP$_<mview_name> is created at the snapshot site and this is an index created on MLOG$_ table. 

therefore you may need to create additional indexes on the snapshot log, depending on the materialized view query. 

Let us understand the Key Concepts of Materialized View Fast Refresh.

The registration process for a a fast refreshing snapshot as shown below.

When we submit a “CREATE SNAPSHOT/materialized view … REFRESH 

FAST refresh, a snapshot ID (SNAPID) and timestamp (SNAPTIME) are generated, with SNAPTIME recorded as the snapshot’s last complete refresh time.

1. All necessary objects at the snapshot site are automatically created, initiating the first full refresh will start.

Once the full refresh completes, four key tables are updated to track and manage the materialized view state.

1. In the master db, a new row is inserted into SYS.SLOG$, capturing the SNAPID & SNAPTIME values generated in the initial step 1. 

2. In the master db, SYS.MLOG$ is updated for the snapshot log; YOUNGEST is set to SNAPTIME, and if it’s the only snapshot, OLDEST_PK (or OLDEST for ROWID) is set to the same value.

3. In the snapshot db, a new row is inserted into SYS.SNAP$ for the materialized view, recording MLINK, MOWNER, MASTER, and SNAPID details.

4. In the snapshot db, a new row is added to SYS.SNAP_REFTIME$, capturing snapshot details and the latest refresh time, with SNAPTIME set to the value generated in step 1. 

Let us understand how Fast Refresh Works in Materialized Views 

A materialized view refresh follows three phases—Setup, Refresh, and Wrap-up; in the Setup phase, the system evaluates fast refresh eligibility by validating the master table’s snapshot log, its freshness, and timestamp consistency across key registration tables (SLOG$, MLOG$, SNAP$, and SNAP_REFTIME$). 

Once fast refresh is confirmed, the Refresh phase begins, where only the required data changes are efficiently fetched and applied into the data in database. 

This process runs in two stages are:

1.Delete Phase
2. Insert/Update Phase

This will ensuring incremental changes are applied efficiently during the refresh.

1. Delete Phase  

1.1 A SQL query captures changed primary keys (or ROWIDs) using USLOG$ and MLOG$_XXX, joined with the master table. 

1.2 It combines data using a UNION ALL between the updateable snapshot log (USLOG$) and the master snapshot log (MLOG$_XXX), joined with the master table. 

1.3 The rows returned by this query are deleted from the snapshot base table to prepare for fresh data.

1.4 Updated rows from the snapshot are sent to the master via deferred transactions and recorded in the master log. 

1.5 Conflict resolution at the master site may modify these rows, requiring them to be re-downloaded during refresh. 

1.6 Rows inserted or updated at the snapshot site are also removed locally. 

1.7 These rows are not lost—they are safely propagated to the master, ensuring consistency and avoiding data conflicts during the refresh process.

2. Insert/Update Phase 

2.1 This phase runs a SQL query on the master to fetch distinct primary keys (or ROWIDs) for rows inserted or updated in the master table. 

2.2 It also includes rows pushed from the snapshot to the master via deferred transactions.

2.3 For each returned row, the system first attempts an update in the snapshot base table. 

2.4 If no matching row exists, a new row is inserted, ensuring the snapshot stays in sync with the master.

How are these timestamps maintained and updated during the refresh process? 

Timestamps are primarily maintained across five key locations: SYS.SLOG$, SYS.MLOG$, SYS.SNAP$, SYS.SNAP_REFTIME$, and the snapshot log table (MLOG$_XXX).

SLOG$.SNAPTIME: This value is set to the most recent refresh time of the snapshot, as known by the master. 

MLOG$.OLDEST_PK (or OLDEST in case of ROWID snapshot): This is the “age” of the snapshot log. This value indicates the oldest entry in the master table’s snapshot log. After each snapshot completes a refresh (fast or complete), this value is updated with the current refresh timestamp if no rows having an earlier SNAPTIME exist in SYS.SLOG$ 

MLOG$.YOUNGEST: This is the most recent snapshot refresh time using this snapshot log. 

SNAP$.MTIME-This value is set to the most recent DDL time of this snapshot. This value does not change after a fast refresh. 

SNAP_REFTIME$.SNAPTIME : This value is set to the most recent refresh time of the snapshot. 

MLOG$_XXX.SNAPTIME: This value is initially set to 1/1/4000 when each row is inserted into the master table snapshot log. During the refresh, this value is updated to the current refresh timestamp, for each row downloaded to the snapshot 

what happens internally during a materialized view fast refresh?

In the Setup phase, a snapshot qualifies for fast refresh (log-based refresh) only if the following conditions are satisfied:

a. A snapshot log exists.  

b. SNAP_REFTIME$.SNAPTIME ≥ MLOG$.OLDEST_PK → This indicates that the snapshot log contains all required changes, making the snapshot eligible. 

c. SNAP_REFTIME$.SNAPTIME = SLOG$.SNAPTIME → This ensures the snapshot’s last refresh time is fully synchronized with the master’s recorded timestamp. 

d. Current refresh time ≥ MLOG$.YOUNGEST + 1 second → This ensures the latest changes are fully captured before.

2. In the Refresh phase, the MLOG$_XXX.SNAPTIME$$ column is updated with the current refresh timestamp for all rows processed by the refresh query.

3. Finally, the Wrap-up Phase does the following: 

• SetsSLOG$.SNAPTIME to the current refresh time. 

• SetsSNAP_REFTIME$.SNAPTIME to the current refresh time. 

• Sets MLOG$.OLDEST_PK to current refresh time if current refresh time <

  MIN (SLOG$.SNAPTIME).

• Deletes all rows from MLOG$_XXX where SNAPTIME$$ < current refresh

  time. When deciding whether to purge snapshot log records, Oracle compares SYS.SLOG$.SNAPTIME for the table with MLOG$_.SNAPTIME$$. The rows with a MLOG$_.SNAPTIME$$ equal to or older than the oldest SYS.SLOG$.SNAPTIME for the table are purged from the log.

LOCKS in Mview site during Fast Refresh 

We will see the following locks when mview refresh is going on: 

1. TM lock in SX (Row Exclusive) mode on SNAP$ for update. 

2. TM lock in SX (Row Exclusive) mode on the base table for mview being refreshed.  

3. A JI enqueue lock is applied to each materialized view base table during refresh, ensuring data consistency, and is released only after the refresh process completes.

Write a query to fetch this:

SQL>select type, id1, id2, lmode, request from v$lock where sid=1258; 

TY ID1 ID2 LMODE REQUEST
--------------------------- 
TX 104565371 286865 1
TM 117125 0 3 0 JI 132985 0 6 0 
TM 143 0 3 0 KQ 0 230 6 0  

3 rows selected.

SQL>select object_name from dba_objects where object_id=132985; 

OBJECT_NAME 
----------------------------------------------------------------------------------------------------------- ---
FC_COMPLETED_SHIPMENT_PACKAGES <--This is the mview being refreshed. 

1 row selected.

SQL>select object_name from dba_objects where object_id=178; 

OBJECT_NAME 
-----------
SNAP$ 

Key Insights on Materialized View Refreshes 

1. When a session shows ‘db file sequential read’ on the snapshot site, it is typically idle on ‘SQL*Net message from client’; conversely, if it waits on ‘SQL*Net more data from dblink’, the actual I/O load is usually occurring on the master database as ‘db file sequential read’.

2. If snap refreshes fails due to ORA-1555( due to undo tbs or UNDO_RETENTION at MASTER), the 

ORA-1555 error is written at snapsite alert.log and also in master alert.log 

EG-IN (SNAPSITE) ALERT.LOG: 

ORA-12012: error on auto execute of job 242973
ORA-12048: error encountered while refreshing materialized view “BOOKER”.”ILBO_SRW_DC2″
ORA-01555: snapshot too old: rollback segment number 68 with name “_SYSSMU68$” too small
ORA-02063: preceding line from <SID><—-MASTER SITE ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 5411 

3. The snapsite creates a session at master site which can be identified using MACHINE in v$SESSION at master site the machine will have hostname of snapsite. 

5. Troubleshooting Materialized View Fast Refresh Issues

5.1 First identify if the mview can be fast refreshable- check the conditions in the setup phase-especially the first 3: 

a. A snapshot log exists.
b. SNAP_REFTIME$.SNAPTIME>=MLOG$.OLDEST_PK→This signifies that the snapshot log has the rows to fast refresh the snapshot. c. SNAP_REFTIME$.SNAPTIME = SLOG$.SNAPTIME 

5.2 Use DBMS_MVIEW.explain_mview procedure and MV_CAPABILITIES_TABLE 

a) SQL> TRUNCATE TABLE <schema_name>.mv_capabilities_table; 

If it does not exists-Execute utlxmv.sql to create the MV_CAPABILITIES_TABLE .


Ensure the script is executed within the same schema as the materialized view for accurate results.

SQL> @$ORACLE_HOME/rdbms/admin/utlxmv.sql;

b) Execute the dbms_mview.explain_mview () procedure to explain the materialized view: execute it from the schema owner of the mview. 

SQL> EXEC dbms_mview.explain_mview('<schema_owner>.<mview_name>'); 

OR

SQL> EXEC DBMS_MVIEW.EXPLAIN_MVIEW ('<Use SELECT statement of the materialized view >');  

c) Query the MV_CAPABILITIES_TABLE to verify each fast refresh capability, ensuring the POSSIBLE column shows ‘Y’; use the MSGTXT column to identify and resolve any unmet requirements.

SQL> SELECT capability_name, possible, substr(msgtxt,1,60) AS msgtxt FROM scott.mv_capabilities_table WHERE capability_name like '%FAST%'; 

OR use this query

SQL> set linesize 220
SQL> SELECT SUBSTR(capability_name,1,30)AS capability_name , possible, SUBSTR(related_text,1,10)AS related_text, SUBSTR(msgtxt,1,90)AS msgtxt FROM MV_CAPABILITIES_TABLE where capability_name like 'REFRESH%'ORDER BY seq;  

5.3 If fast refresh performance is slow, check whether the snapshot log (MLOG$_<tablename>) has grown excessively large, as this can significantly impact refresh time. 

When deciding whether to purge snapshot log records, Oracle compares SYS.SLOG$.SNAPTIME for the table with MLOG$_.SNAPTIME$$.  

The Rows in the snapshot log with MLOG$_<table>.SNAPTIME$$ less than or equal to the oldest SYS.SLOG$.SNAPTIME are eligible for purge. However, if an orphaned entry remains in SYS.SLOG$ (such as from a dropped snapshot), its SNAPTIME is not updated—preventing log cleanup and causing the snapshot log to grow indefinitely during refresh cycles. 

The following query helps identify stale or orphaned entries in SYS.SLOG$ that are no longer registered and have not been updated for an extended period.

SQL>SELECT r.NAME snapname, snapid, NVL(r.snapshot_site, 'not registered') snapsite, snaptime FROM sys.slog$ s, dba_registered_snapshots r WHERE s.snapid=r.snapshot_id(+)  AND mowner LIKE UPPER(‘&owner') AND MASTER LIKE UPPER('&table_name'); 

After verifying the existing snapshots on the consumer site by querying SYS.SNAP$, It will be easy to determine which entries in SYS.SLOG$ at the master site were no longer being used. 

b) Use DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG to remove the orphaned entries, the old records from the snapshot log were finally purged upon the next refresh. 

An example follows:- 

SNAPSHOT WILL PURGE ONLY. 

Snapshot log rows become redundant when their MLOG$_<table>.SNAPTIME$$ is less than or equal to the oldest SLOG$.SNAPTIME (or OLDEST_PK in SYS.MLOG$), making them eligible for safe cleanup.

MLOG$_<table>.SNAPTIME$$ <= MIN (SLOG$.SNAPTIME)

SQL>select min(snaptime$$) from booker.MLOG$_TAB1; 

MIN(SNAPTIME$$) 
---------------- 
2026-05-01:02:22 

SQL>select min(snaptime) from sys.slog$; 

MIN(SNAPTIME)
----------------
2026-05-01:18:04 <----SYS.SLOG$ has the snaptime of the dropped snapshot 321 

SQL>select log_owner,master,log_table,current_snapshots,snapshot_id from dba_snapshot_logs where master ='TAB1'; 

LOG_OWNER MASTER LOG_TABLE CURRENT_SNAPSHOTS SNAPSHOT_ID
------------------------------ ------------------------------ ------------------------------ ------------------
BOOKER MLOG$_TAB1 BOOKER MLOG$_TAB1 BOOKER MLOG$_TAB1 BOOKER 
TAB1 
2026/05/01 18:13:48 
TAB1 2026/05/01 07:44:37 TAB1 
2026/05/01 07:44:37 TAB1 
227 <---orphan snapshot 
6 161 
MLOG$_TAB1
4 rows selected.

NOTE: THE OLD SNAP IS WITH SNAPID 227-WE NEED TO UNREGISTER THIS.

 SQL> exec dbms_snapshot.purge_mview_from_log( 227); 

SQL> select snapshot_id,SNAPSHOT_SITE,name from dba_registered_snapshots where snapshot_id in(227,6,161,22); 

SNAPSHOT_ID             SNAPSHOT_SITE  NAME
----------- -------------------------------
2026/05/01      07:46:36 
22
6 SRW1CN.xxx ILBLP_PEK3 
22 GPI1CN.xxx ILBL_PEK3 161 
SRW1CN.xx.

5.4 Snapshot log was not dropped. 

Here is an example where the snapshot log exists but the registered snapshots do not exist. So the snapshot log is unnecessary and drop it. 

SQL>select log_owner,master,log_table,current_snapshots,snapshot_id from dba_snapshot_logs where master ='DISTRIBUTOR_RETURNS'; 

LOG_OWNER MASTER LOG_TABLE CURRENT_SNAPSHOT SNAPSHOT_ID
------------------------------ ------------------------------ ------------------------------ --------- ------- ----------- 
BOOKER DISTRIBUTOR_RETURNS MLOG$_DISTRIBUTOR_RETURNS 2026-05-01:20:49 246 BOOKER DISTRIBUTOR_RETURNS MLOG$_DISTRIBUTOR_RETURNS 2026-05-01:20:51 132 

SQL>select snapshot_id,SNAPSHOT_SITE,name from dba_registered_snapshots where snapshot_id in(246,132);
no rows selected 

SQL>DROP SNAPSHOT LOG ON BOOKER.DISTRIBUTOR_RETURNS; 

Materialized view log dropped. 

SQL>select log_owner,master,log_table,current_snapshots,snapshot_id from dba_snapshot_logs where master ='DISTRIBUTOR_RETURNS'; 

no rows selected 

5.5 During fast refresh of mview we are getting ORA-00942 (table or view does not exists) 

Scenario:-

1. mviewcreatedbyschematest
2. Mastertableonschemaprod.
3. Mviewlogcreatedinschemaprod
4. select from master table granted to schema test  

Materialized view fast refresh can fail if the MV owner (e.g., TEST) lacks SELECT privileges on the materialized view log (MLOG$) located in another schema.

Solution:- 

Ensure the materialized view owner has SELECT privileges on both the master table and the materialized view (snapshot) log, especially when they reside in a different schema.

5.6 IF WE HAVE SAME SNAPSHOT(SAME SNAP_ID) REGISTERED IN 2 MLOG$ . ONE IS REFRESHING OK. OTHER NOT 

therefore we CREATE A NEW SNAPSHOT FOR THE ONE THAT IS WORKING-THIS WILL GIVE 

NEW SNAP_ID FOR THE CORRECT ONE SO THAT WE CAN PURGE THE WRONG ONE STEPS: 

1. If we have PREBUILT TABLE then we can drop the snapshot by keeping the prebuilt table 

2. Then create the same snapshot 

3. Do complete and then fast refresh 

4. Drop the not working snapshot.

SQL>DROP SNAPSHOT SRW.INV_LEVEL_BY_LOCATION_PHYSICAL PRESERVE TABLE; 
Materialized view dropped. 

SQL>SELECT * FROM MVIEW_REFRESH_GROUPS WHERE REFRESH_GROUP='SNAPG_ROSNAP_DCCVG2_DC_SRW'; 

REFRESH_GROUP OWNER MVIEW_NAME REFRESH_ME RETRIES
------------------------------ -------- ---------
SNAPG_HOGASD_DCCVG2_DC_SRW
2
SNAPG_DFASNAP_DCCVG2_DC_SRW
FAST 2 SNAPFA_SANAP_DCCVG2_DC_SRW
FAST 2 SNAPAF_ASDSNAP_DCCVG2_DC_SRW
2

4 rows selected. 

SQL> CREATE MATERIALIZED VIEW "MSH"."INV_LEV_BY_LOC_PHY"
ON PREBUILT TABLE WITHOUT REDUCED PRECISION 
USING INDEX
REFRESH FAST ON DEMAND 
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT 6 DISABLE QUERY REWRITE 7 AS SELECT ...... 
Do COMPLETE REFRESH
Do FAST REFRESH
NOW PURGE THE SNAP:-DCCVG2>exec dbms_snapshot.purge_mview_from_log(10); 

PL/SQL procedure successfully completed.  

Resolving Slow Performance in Materialized View Complete Refresh

Complete refresh consists of the following phases: 

1. removing existing rows from the mview,- by default does a DELETE 

2. generating result set of the mview definition query, and inserting the new rows into the mview- Does INSERT INTO <Mview> SELECT ..<Mview definition query) 

3. 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)..Does DELETE and UPDATE on MLOG$_  

If a complete refresh is running slowly, consider the following optimization steps:

1. Get 10046 trace of the session doing complete refresh at snap site and run tkprof 

2. Look into the execution plan in tkprof and depending on where the slowness is (where elapsed time or query/current time is high) do the following:- 

a) deletes from mview (mview base table) is SLOW ATOMIC_REFRESH=>TRUE (by default) it will do delete and it can be slow if the mview is large and lots of redo get generated ATOMIC_REFRESH=>FALSE , then TRUNCATEs happen which is faster but any query selecting from mview will fail So if the slowness is due to DELETEand the Mview is LARGE and users are not selecting from mview, set ATOMIC_REFRESH=>FALSE in dbms_mview.refresh. 

b) Inserts into mview using INSERT INTO <Mview> select……is slow Check the original CTAS or SELECT query in create mview statement-if that is slow then query tuning needs to be done 

c) If the master table referenced in the mview definition query has mview log on it, then the complete refresh executes a recursive / internal DELETE command to delete rows from the mview log that are no longer necessary for any involved fast refreshable mview refreshes , and a recursive UPDATE can turn up as well to set snaptime$$ value for the mview log record that has been downloaded to the mview. 

In most cases, the DELETE operation is executed using a statement similar to the following:

SQL> delete from MLOG$_masterTable where snaptime$$ <= <oldest_refresh>; 

By default, Oracle does not create an index on the SNAPTIME$$ column of the materialized view log during creation, which often forces the optimizer to rely on a full table scan for such queries.

->If the table has plenty of rows while the above SQL deletes only few rows, then it is worth considering creating index on the snaptime$$ column of the MLOG$_masterTable 

->collect statistics on MLOG$_ table
->Shrink MLOG$_<masterTable> if it is huge. 

7. IMPORTANT QUERIES

Note: Can vary from DB version to version 1. To find progress of mview (mview site) 

2. Togetallsnapshotlogsandrefreshtimeforamastertable(mastersite) 

SQL> select log_owner,master,log_table,current_snapshots,snapshot_id from dba_snapshot_logs where master = '&master_table_name'; 

3. To get snapshot log table details for a Master table (master site)

SQL> SELECT mtime,OLDEST,OLDEST_PK,OLDEST_SEQ,YOUNGEST from SYS.MLOG$ where MASTER= '&master_table_name'; 

4. SYS.SLOG$ details (master site)

SQL> select snapid,snaptime from sys.slog$ where master= '&master_table_name'; 

5. To get registered snapshots (master site) select snapshot_id,SNAPSHOT_SITE,name from

   dba_registered_snapshots; 

6. To get refresh group for a snapshot (snapsite)

   select name,rname from dba_refresh_children where name= ‘&snapshot_name’; 

7. To get details of a snapshot ( snapsite) 

SQL> select name,owner,prebuilt, last_refresh,next,ERROR from dba_snapshots where name=‘&snapshot_name';

=====Hence tested and verified in our test env=====