物化视图日志(materialized view log)引起大量Dfs Lock Handle等待

APPLIES TO:

Oracle Server - Enterprise Edition - Version 10.2.0.3 to 10.2.0.4 [Release 10.2]
Information in this document applies to any platform.

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."

Note: This bug was still open with development at the time this note was published (Oct. 2009).

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.
   

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15747463/viewspace-1184683/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15747463/viewspace-1184683/