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?
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.
Diagnosing the Progress of the Refresh
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.
2.2 Determine if a Refresh Group is Being Refreshed
There are two possible ways of refreshing a refresh group:
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:
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.
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:
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.
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:
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:
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.
The following query helps identifying the target site of the propagation:
Or alternatively in Oracle9 and if parallel propagation is in progress.
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.
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 following query query can be used to this end:
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.
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:
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:
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:
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:
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:
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:
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/，如需转载，请注明出处，否则将追究法律责任。