MView Refresh Performance

Troubleshooting Materialized Views (MViews) Delay in Oracle

Objective:

Document the procedures required to troubleshoot delays related to materialized views (MViews).

In this scope, MViews are used to replicate data from the primary database to both contingency environments and the reporting database.

• Source host (where changes are captured for replication).
• Target hosts (where data is replicated).

Summary

This is my little guide that provides a step-by-step guide to troubleshoot issues related to delayed Materialized Views (MViews) in Oracle databases. It covers how to identify delayed MViews, check their status and MLogs, isolate problematic MViews into dedicated refresh groups, gather statistics, safely kill refresh jobs, move large MLogs to improve performance, and when necessary, recreate a critical MView.

Procedure Index

1. Identify Delayed MViews
2. Check MView State and MLog Size
3. Isolate Delayed MView into Its Own Refresh Group
4. Gather Statistics on MLogs (no_invalidate = false)
5. Kill a Refresh Job (Pre-checks Required)
6. Move (Shrink) Large MLogs
7. Recreate an MView (Critical)

1) Identify Delayed MViews

Run the following on the target host to find MViews that are delayed. You will also see the refresh group, the last refresh type, and timestamp.

set lines 200 pages 2000
col OWNER for a20
col MVIEW_NAME for a30
col GROUP_OWNER for a20
col GROUP_NAME for a30
col LAST_REFRESH_DATE for a10
col LAST_REFRESH_TYPE for a20
select m.OWNER, m.MVIEW_NAME, c.REFGROUP, g.OWNER GROUP_OWNER,  g.NAME GROUP_NAME, m.FAST_REFRESHABLE, m.LAST_REFRESH_TYPE, to_char(m.LAST_REFRESH_DATE, 'dd/mm/yyyy hh24:mi:ss') LAST_REFRESH_DATE
from dba_rchild c, dba_rgroup g , dba_mviews m
where c.refgroup = g.refgroup(+)
and c.owner(+) = m.owner
and c.name(+) = m.mview_name
order by m.LAST_REFRESH_DATE, m.LAST_REFRESH_TYPE;

With this, you can identify which refresh group the MView belongs to, the last refresh type performed, and when it last refreshed—giving you a quick view of its current state.

2) Check MView State and MLog Size

2.1 Check MLog size (run on the source host)

select * from (
  select owner,segment_name,segment_type,tablespace_name,round(bytes/1024/1024/1024,1) "size_in_GB" 
  from dba_segments 
  where SEGMENT_NAME LIKE 'MLOG$_%'
  order by bytes/1024/1024/1024 desc
) order by 5 desc;

2.2 See which MViews are refreshing now (run on the target host)

This shows the session (SID/SERIAL#), the MView name/owner, refresh type, current phase, and the amount of processed DML.

set lines 200 pages 2000
col "MVIEW BEING REFRESHED" format a45
col REFTYPE for a10
col STATE for a15
col INSERTS format 9999999
col UPDATES format 9999999
col DELETES format 9999999
col data_agora for a25
select SID_KNST, SERIAL_KNST, CURRMVOWNER_KNSTMVR || '.' || CURRMVNAME_KNSTMVR "MVIEW BEING REFRESHED",
       to_char(sysdate,'dd/mm/yyyy hh24:mi:ss') DATA_AGORA,
       decode(REFTYPE_KNSTMVR, 1, 'FAST', 2, 'COMPLETE', 'UNKNOWN') REFTYPE,
       decode(GROUPSTATE_KNSTMVR, 1, 'SETUP', 2, 'INSTANTIATE', 3, 'WRAPUP', 'UNKNOWN') STATE,
       TOTAL_INSERTS_KNSTMVR INSERTS,
       TOTAL_UPDATES_KNSTMVR UPDATES,
       TOTAL_DELETES_KNSTMVR DELETES,
       (TOTAL_INSERTS_KNSTMVR+TOTAL_UPDATES_KNSTMVR+TOTAL_DELETES_KNSTMVR) TOTAL
from X$KNSTMVR X
where type_knst=6
  and exists (
    select 1 from v$session s
    where s.sid = x.sid_knst
      and s.serial# = x.serial_knst
  );

FAST refresh phases:
1. Setup — reads from the source MLog.
2. Refresh — applies DML on the target.
3. Wrap-up — finalization while waiting for MLog purge.

2.3 Track long operations during Setup (run on the target host)

Use gv$session_longops to estimate progress. Replace the schema name.

select  
  logon_time, s.sid, s.serial#, s.machine, s.osuser, s.username, s.event, sl.OPNAME,
  sl.ELAPSED_SECONDS, sl.TIME_REMAINING, sl.SOFAR, sl.TOTALWORK, s.inst_id,
  ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct,
  (select sql_text from gv$sql sq where sq.sql_id = s.sql_id and sq.inst_id = s.inst_id and rownum =1) sql
from gv$session s
join gv$session_longops sl
  on s.sid = sl.sid and s.serial# = sl.serial#
where s.username = 'SCHEMA_NAME'
  and sl.TIME_REMAINING > 0;

3) Isolate the Delayed MView into Its Own Refresh Group

From step 1, you know the current refresh group. MViews that frequently delay should live in dedicated refresh groups. Oracle refreshes MViews in a group sequentially (one by one). If one MView stalls, the entire group gets delayed.

Procedure:

1. Remove the slow MView from the current refresh group.
2. Kill the running refresh job (read step 5 first).
3. Create a new refresh group.
4. Attach the MView to the new group and verify.
5. Monitor the new refresh.

3.1 Remove the MView from the current group (TARGET)

BEGIN
  DBMS_REFRESH.SUBTRACT (
    name => 'OWNER.CURRENT_REFRESH_GROUP',
    list => 'OWNER.MVIEW_NAME',
    lax  => TRUE
  );
END;
/

3.2 Kill the running job on each TARGET

ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

3.3 Create a new refresh group

BEGIN
  DBMS_REFRESH.MAKE (
    name => 'OWNER.NEW_REFRESH_GROUP',
    list => '',
    next_date => SYSDATE,
    interval => 'SYSDATE+20/24/60/60', -- every 20 seconds
    implicit_destroy => FALSE,
    rollback_seg => '',
    push_deferred_rpc => TRUE,
    refresh_after_errors => FALSE
  );
END;
/

3.4 Attach the MView to the new group

BEGIN
  DBMS_REFRESH.ADD(
    name => 'OWNER.NEW_REFRESH_GROUP',
    list => 'OWNER.MVIEW_NAME',
    lax  => TRUE
  );
END;
/

3.5 Monitor using steps 1 and 2

Confirm the group assignment and track the new refresh.

4) Gather Statistics on MLogs (no_invalidate = false)

Because MLogs change constantly, their statistics get stale quickly. It is advisable to gather stats before restarting any delayed refresh.

EXEC DBMS_STATS.GATHER_TABLE_STATS(
  OwnName => 'OWNER',
  TabName => 'MLOG$_YOUR_MLOG_NAME',
  Cascade => TRUE,
  no_invalidate => FALSE
);

5) Kill a Refresh Job (Pre-checks Required)

Before killing a refresh job, ensure it is not at the MLog DELETE step. Aborting there can generate significant UNDO and force large rollbacks on the source, risking contention or even application impact.

5.1 Find the refresh session (TARGET)

set lines 200 pages 2000
col "MVIEW BEING REFRESHED" format a45
col REFTYPE for a10
col STATE for a15
col INSERTS format 9999999
col UPDATES format 9999999
col DELETES format 9999999
col data_agora for a25
select SID_KNST, SERIAL_KNST, CURRMVOWNER_KNSTMVR || '.' || CURRMVNAME_KNSTMVR "MVIEW BEING REFRESHED",
       to_char(sysdate,'dd/mm/yyyy hh24:mi:ss') DATA_AGORA,
       decode(REFTYPE_KNSTMVR, 1, 'FAST', 2, 'COMPLETE', 'UNKNOWN') REFTYPE,
       decode(GROUPSTATE_KNSTMVR, 1, 'SETUP', 2, 'INSTANTIATE', 3, 'WRAPUP', 'UNKNOWN') STATE,
       TOTAL_INSERTS_KNSTMVR INSERTS,
       TOTAL_UPDATES_KNSTMVR UPDATES,
       TOTAL_DELETES_KNSTMVR DELETES,
       (TOTAL_INSERTS_KNSTMVR+TOTAL_UPDATES_KNSTMVR+TOTAL_DELETES_KNSTMVR) TOTAL
from X$KNSTMVR X
where type_knst=6
  and exists (
    select 1 from v$session s
    where s.sid = x.sid_knst
      and s.serial# = x.serial_knst
  );

5.2 Check rollback segments on the source before killing

Use the GB column as an estimate of rollback volume. Track performance with queries, OEM, or oratop. If there is > 4 GB to roll back, expect high contention.

SELECT s.inst_id,
       s.username,
       s.sid,
       s.serial#,
       t.used_ublk,
       (((t.used_ublk*8)/1024)/1024) gb,
       t.used_urec,
       rs.segment_name,
       r.rssize,
       r.status
FROM   gv$transaction t,
       gv$session s,
       gv$rollstat r,
       dba_rollback_segs rs
WHERE  s.saddr = t.ses_addr
AND    s.inst_id = t.inst_id
AND    t.xidusn = r.usn
AND    t.inst_id = r.inst_id
AND    rs.segment_id = t.xidusn
AND    SID = NUMERO_SID  -- fill SID
ORDER BY t.used_ublk DESC;

6) Move (Shrink) Large MLogs

MLogs can grow exponentially, causing fragmentation and wasted space that may hurt performance. To move MLogs safely, ensure no replication is running during the operation. Two options:

1. No downtime: set JOB_QUEUE_PROCESSES to 0 on all TARGET hosts and kill running refresh sessions.
2. Faster (requires downtime): shutdown and start the DB in restricted mode, perform the move, then disable restricted mode.

6.1 Generate MOVE and STATS for MLogs > 100 MB

select d.*, '|' as " ", 'alter table '||segment_name||' move;' as "MOVE",
       '|' as "   ",
       'EXEC DBMS_STATS.GATHER_TABLE_STATS(OwnName => '''||owner||''',TabName => '''||segment_name||''',Cascade => TRUE, no_invalidate=>false);' as "STATS"
from (
  select owner,segment_name,segment_type,tablespace_name,round(bytes/1024/1024/1024,1) "size_in_GB" 
  from dba_segments 
  where segment_name like 'MLOG$_%'
    and segment_type = 'TABLE'
  order by bytes/1024/1024/1024 desc
) d
where "size_in_GB" > 0.1
order by 5 desc;

Copy the MOVE and STATS columns to a scratch editor and execute them.

6.2 Validate Indexes

After the MOVE, run the check below. If it returns indexes, consider rebuilding them. If it returns nothing, proceed.

with lista as (
  select d.*
  from (
    select owner,segment_name,segment_type,tablespace_name,round(bytes/1024/1024/1024,1) "size_in_GB" 
    from dba_segments 
    where segment_name like 'MLOG$_%'
      and segment_type = 'TABLE'
    order by bytes/1024/1024/1024 desc
  ) d
  where "size_in_GB" > 0.1
  order by 5 desc
)
select *
from dba_indexes
where table_name in (select segment_name from lista)
  and status != 'VALID';

6.3 Gather Stats & Resume

If the DB is in restricted mode, disable it; if replication was paused, re-enable it. Then gather statistics for the relevant MLogs (see step 4).

7) Recreate an MView (Critical)

Drop the current MView and recreate it.

Final Considerations:

Oracle 23ai has many improvements in terms of configuration, troubleshooting, and performance. I'll write another post about these improvements.