How to Monitor the Progress of a Materialized View Refresh (MVIEW)

近期在处理一个MVIEW刷新量巨大的一个Case，特意从MOS搜索到该问题共享给大家参考：

Applies to:

Oracle Database - Enterprise Edition - Version 8.1.7.4 to 11.2.0.2 [Release 8.1.7 to 11.2]
Information in this document applies to any platform.

Purpose

The purpose of this article is to provide the steps to diagnose the refresh. It
addresses the following questions:

Is a refresh currently running?
Is the refresh hanging or moving slowly?
When did the next and last refresh occur?
What is the ongoing phase of the refresh?
Which materialized view in a group is being refreshed?

Scope

This article aims at assisting support analysts and customers to diagnose and
monitor the progress of a materialized view refresh. The terms materialized
view and snapshot are synonymous. The term MVIEW will be used to refer to
materialized view throughout this article.

Details

Diagnosing the Progress of the Refresh 
======================================

Article Contents 
----------------

1. Overview of the Refresh Process
2. Determine if a Refresh is Currently Running
   1. Determine if a Specific MVIEW is Being Refreshed
   2. Determine if a Refresh Group is Being Refreshed
3. Identify the Last and Next Refresh Dates
4. Determine which MVIEW in a Refresh Group is Being Refreshed
5. Determine the Current Phase of a Refresh
   1. Check Outstanding Propagation
   2. Check Outstanding Purge
   3. Check Refresh Subphase
      1. Check for Setup Subphase
      2. Check for Instaniation Subphase
      3. Check for Wrapup Subphase
6. Steps to Determine Whether a Refresh is Hanging, or Moving Slowly
7. Typical Refresh Errors Sections

1. Overview of the Refresh Process 
==================================

The details of the refresh process are presented in NOTE:258252.1 . Briefly; a refresh synchronizes the MVIEW with it's master table. In the case of updateable MVIEWs, changes from the MVIEW site are first propagated to master site if the refresh's push_deferred_rpc parameter is TRUE. Then, changes are pulled from the master site. Pulling rows from master can be either performed using the mview log on the master table (this type of refresh is a FAST refresh), or without using the mview log (this type of refresh is a COMPLETE refresh).

2. Determine if a Refresh is Currently Running 
==============================================

It may be necessary to determine whether an MVIEW is being refreshed by itself, or as part of a refresh group. The following sub-sections address how to determine this. The SID of the session in which the refresh is being executed will be used for further analysis in sections that follow.

2.1 Determine if a Specific MVIEW is Being Refreshed 
=======================================================

This can be accomplished by examining V$LOCK for any JI type lock acquired on the MVIEW in question. 

column owner format a15
column username format a15
column mview format a15
select o.owner, o.object_name mview, username, s.sid
from v$lock l, dba_objects o, v$session s
where o.object_id=l.id1 and
l.type='JI' and
l.lmode=6 and
s.sid=l.sid and
o.object_type='TABLE';


OWNER MVIEW USERNAME SID
--------------- --------------- --------------- ----------
SCOTT DEPT SCOTT 16

2.2 Determine if a Refresh Group is Being Refreshed 
===================================================

There are two possible ways of refreshing a refresh group:

• refresh is being run by a job queue process in the background.
• refresh is being run manually inside Sql*Plus or another tool.

Given the name of the refresh group and its owner, the following query can be used to identify if a refresh is being executed by a job queue process: 

select s.sid, s.username
from dba_jobs_running jr, v$session s, dba_jobs j
where jr.sid=s.sid and
j.job=jr.job and
upper(j.what) like '%REFRESH%%';

SID USERNAME
---------- ---------------
16 SCOTT

Determining if a refresh has been executed manually is a bit tricky. Initially a lock is acquired on the corresponding row in sys.rgroup$, however refresh pre-commits, hence releasing the lock. Later on a JI type lock  is acquired for each MVIEW in the refresh group making it possible to have an idea of whether a refresh is running or not. 

column rowner format a15
column rname format a15
column sid format 9999
select username, sid, rowner, rname
from ( select username, s.sid, rc.rowner, rc.rname, count(*)
from v$lock l, dba_objects o, v$session s,  
dba_refresh_children rc
where o.object_id=l.id1 and
l.type='JI' and
l.lmode=6 and
s.sid=l.sid and
o.object_type='TABLE' and
o.object_name=rc.name and
o.owner=rc.owner and
rc.type='SNAPSHOT'
group by username, s.sid, rc.rowner, rc.rname
having count(*)=( select count(*) from dba_refresh_children
where rowner= rc.rowner and rname=rc.rname and
type='SNAPSHOT') );


USERNAME SID ROWNER RNAME
--------------- ----- --------------- ---------------
SCOTT 16 SCOTT DEPT

3. Identify the Last and Next Refresh Dates 
===========================================

If the refresh is done automatically by a job queue process or by manually executing dbms_job.run(), then finding the next and last refresh times of a refresh can be accomplished by querying dba_jobs as follows: 

alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';

column what format a36
select what, last_date, next_date
from dba_jobs
where upper(what) like '%DBMS_REFRESH.REFRESH(%%.%%';

WHAT LAST_DATE NEXT_DATE
------------------------------------ ------------------- -------------------
dbms_refresh.refresh('"SCOTT"."R"'); 10-02-2003 10:48:46 11-02-2003 10:48:46

If the refresh is not performed via dbms_job then the next date can still be determined using the query above. However, the last refresh date will not show the correct date. In this case, last refresh date can be obtained by querying dba_snapshot_refresh_times for each MVIEW of the refresh group.

select rt.owner, rt.name, rt.last_refresh
from dba_refresh_children rc, dba_snapshot_refresh_times rt
where rc.owner=rt.owner and
rc.name =rt.name and
rc.rname='' and
rc.owner='';

OWNER NAME LAST_REFRESH
--------------- ------------------------------ -------------------
SCOTT DEPT 10-02-2003 10:41:19
SCOTT EMP 10-02-2003 10:41:19

4. Determine which MVIEW in a Refresh Group is Being Refreshed 
==========================================================================

The V$MVREFRESH view can be used in versions Oracle9i and later to determine which MVIEW is being refreshed. You can use the following query to identify the MVIEW:

select currmvowner, currmvname
from v$mvrefresh
where sid=;

Prior to Oracle9i, finding the MVIEW that is currently being refreshed is more complicated. If the refresh being diagnosed is in the instantiation phase, then the object_names in the FROM clause of the sql_test string returned by the following query can be used to determine which MVIEW is currently being refreshed:

select sql_text
from v$session ses, v$sqlarea sql
where ses.sql_hash_value = sql.hash_value(+) and
ses.sql_address = sql.address(+) and
ses.sid=;

However, if the refresh is in either the SETUP or WRAPUP phase, then the accompanying session at the master site should be identified first. This can be done using OEM, or selecting username, machine, logon_time columns for v$session to find out that session at the master site.

Once the SID of the session at master site has been determined, the above query can be used to determine which MVIEW is in the SETUP/WRAPUP phase.

5. Determine the Current Phase of a Refresh 
===========================================

It is sometimes required to determine the current phase of a refresh. For more information about the phases of the refresh see Note:258252.1 . Given the SID of the session that performs the refresh, the current phase of the refresh can be determined using the following steps:

5.1 Check Outstanding Propagation 
---------------------------------

The first step of the refresh is pushing defcalls if its push_deferred_rpc parameter is TRUE. The push can be tracked by examining the locks allocated by the session. Note that once the push and purge phases are finished these locks are released. 

column sid format 9999
column state format a26
select l.sid,  
decode( count(*), 0, 'No propagation in progress',  
'Propagation in progress' ) State
from v$lock l, dbms_lock_allocated la
where l.type='UL' and  
l.lmode=4 and
l.id1=la.lockid and
la.name='ORA$DEF$EXE$PushCommonLock'
group by l.sid;

SID STATE
----- --------------------------
16 Propagation in progress

The following query helps identifying the target site of the propagation:

column sid format 9999

select l.sid, 'Currently propagating to ' || substr(la.name, 13)
from v$lock l, dbms_lock_allocated la
where l.type='UL' and  
l.lmode=6 and
l.id1=la.lockid and
la.name like 'ORA$DEF$EXE$%';

Or alternatively in Oracle9 and if parallel propagation is in progress.

select sid, 'Currently propagating to ' || dblink
from v$replprop;

5.2 Check Outstanding Purge 
---------------------------

After pushing the deferred transactions the next step of the refresh is to purge the propagated transactions if refresh is invoked with purge_option>0 and parallelism>0. 

column sid format 9999
select l.sid,  
decode( count(*), 0, 'No purge in progress',  
'Purge is in progress' ) State
from v$lock l, dbms_lock_allocated la
where l.type='UL' and  
l.lmode=6 and
l.id1=la.lockid and
la.name='ORA$DEF$EXE$PurgeCommonLock'
group by l.sid;

SID STATE
----- --------------------
16 Purge is in progress

It is also possible to monitor the progress of the purge by viewing v$replqueue. See Section 4.2 of Note:1035874.6.

5.3 Check Refresh Subphase 
--------------------------

Once refresh finishes the pushing and purging of defcalls it continues with the refresh. The refresh is done in three subphases, SETUP, INSTATNIATION and WRAPUP. For more information on the refresh phase, please see NOTE:258252.1

In Oracle9i it is very easy to determine - the type of the ongoing refresh

• the phase of the ongoing refresh
• the number of DMLs performed by the refresh

The following query query can be used to this end: 

connect / as sysdba
column "MVIEW BEING REFRESHED" format a30
column INSERTS format 9999999
column UPDATES format 9999999
column DELETES format 9999999
select CURRMVOWNER_KNSTMVR || '.' || CURRMVNAME_KNSTMVR  
"MVIEW BEING REFRESHED",
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
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);

Prior to Oracle9i, finding the phase of the refresh is more complicated. The output of the following query can be used to determine what subphase the refresh is in.

column sql_text format a77

select sql_text
from v$session ses, v$sqlarea sql
where ses.sql_hash_value = sql.hash_value(+) and
ses.sql_address = sql.address(+) and
ses.sid=;

Given the output of this query you can determine whether the refresh is in SETUP, WRAPUP or INSTANTIATION phase as described in the following sections.

5.3.1 Check for Setup Subphase 
------------------------------

Review the output from the above query: 

SQL_TEXT
-----------------------------------------------------------------------------
begin sys.dbms_snapshot_utl.set_up@EE817.US.ORACLE.COM (:mown_col,  
:mas_col, :masobj_col, :rollseg_col, :flag_col, :snaptime_col,  
:loadertime_col, :num_sid, :snapid_tab, :su_scn_col, :nr_flag); end;

If the output contains sys.dbms_snapshot_utl.set_up@ then the refresh is in SETUP phase.

5.3.2 Check for Instaniation Subphase 
-------------------------------------

The next phase after the SETUP is instantiation. In this phase several statements are executed on MVIEW base table and dictionary tables. Hence it is not trivial to determine the INSTANTIATION phase. If sql_text from v$sqlarea shows one of the following statements then the refresh can be said to be pulling the rows from the master site:

- SELECT /*+ remote_mapped( ...
- UPDATE "OWNER"."MVIEWNAME" ...
- INSERT INTO "OWNER"."MVIEWNAME" ...
- UPDATE sys.snap$ SET status = :1, rscn = :2, refhnt = :3, flag = :4  
WHERE vname = :5 AND sowner = :6 AND instsite = :7
- UPDATE sys.snap_reftime$ SET snaptime = :1, loadertime = :2, refscn = :3,  
fcmaskvec = :4, ejmaskvec = :5  
WHERE sowner = :6 AND vname = :7 AND tablenum = :8 AND instsite = :9

Another indication of INSTANTIATION is the fact that if the session is not performing a push or purge and it is not in SETUP or WRAPUP phase then it can be assumed to be pulling the rows from the master site.

5.3.3 Check for Wrapup Subphase 
-------------------------------

If the output contains sys.dbms_snapshot_utl.wrap_up@ then the refresh is in WRAPUP phase. Below is a sample output for this phase: 

SQL_TEXT
-----------------------------------------------------------------------------
begin sys.dbms_snapshot_utl.wrap_up@EE817.US.ORACLE.COM (:snapid_col, :sn
aptype_col, :masindx_col, :mow_col, :mas_col, :masobj_col, :rollseg_col, :
flag_col, :snaptime_col, :loadertime_col); end;

6. Steps to Determine Whether a Refresh is Hanging, or Moving Slowly 
====================================================================

Once the phase of the refresh is known, it is easy to determine whether it is hung, or moving slowly. The query outlined in Section 5.3 can also be used here.

If, the refresh is in the propagation phase, the propagation can be diagnosed using the steps described in section 5 of

note:1035874.6 Troubleshooting Guide: Replication Propagation

If the refresh is in SETUP phase for a considerable amount of time, e.g. a couple of minutes, then the corresponding session at master site should be examined for the specific event for which it is waiting. If it waits for an enqueue, then the blocking session should be examined checking what it is doing. Based on the findings the blocking session can be terminated if it will not lead to any undersired ramifications.

You can use the query outlined in section 5.1 of Note:1035874.6 to determine the session that blocks the SETUP at master site. If the wait event at master site is not an enqueue then take errorstack and systemstate traces for the sessions at master and MVIEW site as described in section 5.4 of Note:1035874.6, and file a Service Request to Oracle Support Services.

Similar to the SETUP phase, diagnosing a refresh that is in WRAPUP phase requires examining the accompanying session at the master site. First, query v$session_wait to identify the wait event: 

select event, p1, p2, p3
from v$session_wait
where sid= ;

For an enqueue type wait, follow the steps in section 5.1 of Note:1035874.6 to determine the blocking session. You may consider killing the session that block the WRAPUP will resolve the hang in that case. However, the blocking session should be examined for any undesired ramifications.

The most common events during WRAPUP are db file scattered read and db file sequential read. Consecutive executions of that query will give an idea about the progress of the WRAPUP. The values listed under P1 and P2 should be changing in each run. If the event is IO related then, these values can be used to determine the object that is being accessed by using the following query: 

select owner, segment_name
from dba_extents
where file_id= and
between block_id and block_id+blocks-1;

If the object returned by this query is a MVIEW log, then diagnose it using the steps given in Note:236233.1 .

During INSTANTIATION the rows are pulled from the master site. Diagnosing this phase should start with executing the following query at MVIEW site: 

select event, p1, p2, p3
from v$session_wait
where sid=;

This will give an initial idea of the progress of the refresh. In case of an enqueue type wait, the blocking session can be identified as described above. If the event being waited on is consistently "SQL*Net message from dblink", then the session at master site should be checked for the type of the wait.

Regardless of which subphase the refresh is in, if the wait at both master and MVIEW sites is "SQL*Net message from dblink", then gather errorstacks and systemstate dumps, and file a Service Request.

7. Typical refresh errors 
=========================

ORA-12004: "REFRESH FAST cannot be used for ...

This error indicates a problem with the log at master. See Note:179469.1 for further information.

ORA-12034: "snapshot log on "%s"."%s" younger than last refresh"

This error also indicates a problem with the log at master. See Note:204127.1 for further information.

ORA-23402: refresh was aborted because of conflicts caused by deferred txns

This error is caused by outstanding conflicts logged in the DefError table at the master. This can be workaround by setting refresh_after_errors to true. See Note:1031119.6 and Note:39232.1 for the details.

ORA-23385: replication parallel push heap_size argument not valid

This error is caused if the heap_size value is set to NULL. Query rgroup$ to obtain the current value of heap_size and use dbms_refresh.change to set it to not null value. IE. 0. See Note:49558.1 for the error definition.

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

转载于:http://blog.itpub.net/12974804/viewspace-1062043/