APPLIES TO:

SYMPTOMS

After creating materialized view logs on two very active tables, many sessions doing DML on those base tables were showing 'DFS LockHandle' waits.

1. Trace files, generated by a system state dump, show a reference to a sequence.
   
   KGL Sequence Object #535: 
   
   
2. A query of dba_objects for object_id 535 determined this is object SYS.CDC_RSID_SEQ$, which is the same sequence used to insert data into some of the mview logs tables : 
   
   
   INSERT /*+ IDX(0) */ INTO "SBDBA0"."MLOG$_BASETABLE" (dmltype$$,old_new$$, snaptime$$ 
   ,change_vector$$,sequence$$,m_row$$,"COL1","UID","STATUS" ,"ID","PFUID") VALUES 
   (:d,:o,to_date('4000-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS'),:c 
   ,sys.cdc_rsid_seq$.nextval,:m,:1,:2,:3,:4,:5)

CAUSE

This behavior matches Bug 7455343 "DFS LOCK HANDLE WAITS ON SEQ CDC_RSID_SEQ$ THOUGH CT ISN'T USING CDC."

SOLUTION

Workaround: Recreate all MV logs that do not require sequence to be stored in the log.

1. Use dbms_mview.explain mview and query the mv_capabilities_table to determine which
   materialized views do not need a sequence added to the materialzied view logs.
   If sequence is needed you will see something similar to this in the output:
   
   
   SQL> exec dbms_mview.explain_mview('CAL_MONTH_SALES_MV');
   
   PL/SQL procedure successfully completed.
   
   SQL> SELECT capability_name, possible, SUBSTR(related_text,1,8)
   2 AS rel_text, SUBSTR(msgtxt,1,60) AS msgtxt
   3 FROM MV_CAPABILITIES_TABLE where msgtxt like '%sequ%'
   SQL> /
   
   CAPABILITY_NAME               P REL_TEXT MSGTXT
   ----------------------------- - -------- ------------------------------
   REFRESH_FAST_AFTER_ANY_DML    N SH.SALES mv log does not have sequence #
   
   
   Note: If the sequence is required and is missing from the log then it's possible Oracle will
   report error "ORA-32316 REFRESH FAST of %s . %s unsupported after mixed DML."
   
   
   2. Once it is detemined that the sequence is not required, drop and recreate the materialized view log(s).  A complete refresh will be required the first time any fast-refreshable mview uses the newly created log.