Let me briefly explain what is Materialized View before going into details of how to find refresh details.
We all know that VIEW is a logical table based on one of more tables or views from either local or remote database. A View does not use any segments in database and contain no data by itself. We see real time data in a VIEW. But Materialized view is a database object that stores the results of a query and it can be from local database or from remote database and use data segment to store the data. Earlier it was called as snapshots. This object gets refreshed on a periodical basis either through a DBMS refresh job or via command line. Advantage with Materialized View is faster query response and drawback is slightly old data.
I am not going into details on how to create a Materialized view as it is not in current scope of this article.
Following query will help us to find out last time Materialized view was refreshed and what type of refresh ( ex: COMPLETE, FAST, FORCE) was used.
SQL> select owner,mview_name,refresh_mode,refresh_method,last_refresh_type,last_refresh_date,staleness from dba_mviews;
OWNER MVIEW_NAME REFRES REFRESH_ LAST_REF LAST_REFRESH_DATE STALENESS
------------------------------ ------------------------------ ------ -------- -------- ------------------- -------------------
SYSMAN MGMT_ECM_MD_ALL_TBL_COLUMNS DEMAND FORCE COMPLETE 2012-12-13 09:15:33 FRESH
MV_ADMIN ALL_LISTS DEMAND FAST FAST 2013-05-24 08:24:29 FRESH
MV_ADMIN AUDIT_LOG DEMAND FAST COMPLETE 2013-05-24 08:25:09 STALE
MV_ADMIN USER_CONTACT_INFO DEMAND FAST FAST 2013-05-24 08:24:40 NEEDS_COMPILE
MV_ADMIN ENV_REQ_BUG DEMAND FAST FAST 2013-05-24 08:24:29 NEEDS_COMPILE
MV_ADMIN PRODUCT_BUG DEMAND FAST FAST 2013-05-24 08:24:34 NEEDS_COMPILE
MV_ADMIN PRODUCT_CYCL_FOLD DEMAND FAST FAST 2013-05-24 08:24:39 FRESH
MV_ADMIN PRODUCT_CYCLE DEMAND FORCE FAST 2013-05-24 08:24:34 FRESH
MV_ADMIN PRODUCT_REQ DEMAND FAST FAST 2013-05-24 08:24:34 FRESH
MV_ADMIN PRODUCT_TEST DEMAND FORCE FAST 2013-05-24 08:24:39 FRESH
MV_ADMIN PRODUCT_TESTCYCL DEMAND FAST FAST 2013-05-24 08:24:39 FRESH
MV_ADMIN CYCLE DEMAND FAST FAST 2013-05-24 08:24:40 COMPILATION_ERROR
MV_ADMIN REQ DEMAND FAST FAST 2013-05-24 08:24:40 NEEDS_COMPILE
MV_ADMIN TESTCYCL DEMAND FAST FAST 2013-05-24 08:24:50 COMPILATION_ERROR
MV_ADMIN COMPONENT DEMAND FAST FAST 2013-05-24 08:24:34 FRESH
MV_ADMIN CYCL_FOLD DEMAND FAST FAST 2013-05-24 08:24:39 FRESH
MV_ADMIN DATA_REQ_BUG DEMAND FAST FAST 2013-05-24 08:24:24 NEEDS_COMPILE
MV_ADMIN AUDIT_PROPERTIES DEMAND FAST FAST 2013-05-24 08:26:16 FRESH
MV_ADMIN TEST DEMAND FAST FAST 2013-05-24 08:24:14 FRESH
MV_ADMIN BUG DEMAND FAST FAST 2013-05-24 08:24:40 NEEDS_COMPILE
MV_ADMIN USERS DEMAND FAST FAST 2013-05-24 08:25:40 FRESH
MV_ADMIN GROUPS DEMAND FAST FAST 2013-05-24 08:27:15 FRESH
Another query to find time taken for refresh
SQL> SELECT mview_name, last_refresh_date "START_TIME",
CASE
WHEN fullrefreshtim <> 0 THEN
LAST_REFRESH_DATE + fullrefreshtim/60/60/24
WHEN increfreshtim <> 0 THEN
LAST_REFRESH_DATE + increfreshtim/60/60/24
ELSE
LAST_REFRESH_DATE
END "END_TIME",
fullrefreshtim, increfreshtim
FROM all_mview_analysis;
MVIEW_NAME START_TIME END_TIME FULLREFRESHTIM INCREFRESHTIM
------------------------------ ------------------- ------------------- -------------- -------------
MGMT_ECM_MD_ALL_TBL_COLUMNS 2012-12-13 09:15:33 2012-12-13 09:15:36 3 0
ALL_LISTS 2013-05-24 08:29:31 2013-05-24 08:29:32 0 1
AUDIT_LOG 2013-05-24 08:28:55 2013-05-24 08:30:17 82 17
USER_CONTACT_INFO 2013-05-24 08:29:41 2013-05-24 08:29:42 0 1
ENV_REQ_BUG 2013-05-24 08:29:31 2013-05-24 08:29:31 0 0
PRODUCT_BUG 2013-05-24 08:29:36 2013-05-24 08:29:36 0 0
PRODUCT_CYCL_FOLD 2013-05-24 08:29:41 2013-05-24 08:29:41 0 0
PRODUCT_CYCLE 2013-05-24 08:29:36 2013-05-24 08:29:36 0 0
PRODUCT_REQ 2013-05-24 08:29:36 2013-05-24 08:29:36 0 0
PRODUCT_TEST 2013-05-24 08:29:41 2013-05-24 08:29:42 0 1
PRODUCT_TESTCYCL 2013-05-24 08:29:41 2013-05-24 08:29:41 0 0
CYCLE 2013-05-24 08:29:41 2013-05-24 08:29:42 0 1
REQ 2013-05-24 08:29:41 2013-05-24 08:29:41 0 0
TESTCYCL 2013-05-24 08:29:51 2013-05-24 08:29:53 0 2
COMPONENT 2013-05-24 08:29:36 2013-05-24 08:29:37 0 1
CYCL_FOLD 2013-05-24 08:29:41 2013-05-24 08:29:41 0 0
DATA_REQ_BUG 2013-05-24 08:29:25 2013-05-24 08:29:25 0 0
AUDIT_PROPERTIES 2013-05-24 08:26:16 2013-05-24 08:26:47 0 31
TEST 2013-05-24 08:24:14 2013-05-24 08:24:56 42 25
BUG 2013-05-24 08:29:41 2013-05-24 08:29:55 0 14
USERS 2013-05-24 08:25:40 2013-05-24 08:25:40 0 0
GROUPS 2013-05-24 08:27:15 2013-05-24 08:27:15 0 0
Following command can be used to perform a manual refresh
SQL> exec dbms_snapshot.refresh('MV_ADMIN.AUDIT_LOG','C');
C or c for Complete, f for Fast refresh, ? for Force Refresh (Will try Fast Refresh first and if issues will use Complete)
A or a is for Always Refresh which is same as Complete.
If we don’t specify any refresh option it uses default method used while creating this MVIEW.
Hello,
ReplyDeleteThank you very much you for this article
It's possible to have the history of refresh times for Materialized view in order to compare these durations between two days ?
Kohan
That would be very helpful if there was a history of refresh times for a materialized view so you could look for aberrations, see trends, etc.
ReplyDelete