The LAST_REFRESH_DATE column of the DBA_MVIEWS or the LAST_REFRESH column of the DBA_MVIEW_REFRESH_TIMES indicates the start refresh time. But what if we’d like to find out how long the refresh of the materialized view really takes. Well, we can query the DBA_MVIEW_ANALYSIS.
For Complete Refresh, the refresh duration will be in the FULLREFRESHTIM column of the DBA_MVIEW_ANALYSIS. For Fast Refresh duration, it will be in the INCREFRESHTIM column.
Both values are in seconds.
SELECT mview_name, last_refresh_date, fullrefreshtim, increfreshtim FROM dba_mview_analysis WHERE owner='JOHN'; MVIEW_NAME LAST_REFRESH_DATE FULLREFRESHTIM INCREFRESHTIM ------------------------ ---------------------- -------------- ------------- MV_CHANGE_HISTORY 07-JAN-13 04.36.58 PM 0 36 MV_ITEM_HISTORY 07-JAN-13 04.36.58 PM 0 9
This shows that the recent refresh of the MV_CHANGE_HISTORY and MV_ITEM_HISTORY are the fast refreshes for 36 and 9 seconds respectively.
Put in one query to calculate and display the end time.
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
WHERE owner='JOHN';
MVIEW_NAME START_TIME END_TIME FULLREFRESHTIM INCREFRESHTIM
----------------------- ---------------------- ---------------------- -------------- -------------
MV_CHANGE_HISTORY 07-JAN-13 04.36.58 PM 07-JAN-13 04.37.34 PM 0 36
MV_ITEM_HISTORY 07-JAN-13 04.36.58 PM 07-JAN-13 04.37.07 PM 0 9
Reference: How To Calculate MVIEW Refresh Duration? What Does DBA_MVIEWS.LAST_REFRESH_DATE and DBA_MVIEW_REFRESH_TIMES.LAST_REFRESH Indicate? [ID 1513554.1]
Hi,
It is not giving me values in milliseconds. I want to know for fast refresh since 0 seconds does not tell anything, also i do not get time with the date only get date. How can these issue be fixed?
Hassan, I have tested this on Oracle 11g. This will give you both date and time.
SELECT
mview_name,
to_char(last_refresh_date, ‘DD-MON-YYYY HH24:MI:SS’) “START_TIME” ,
CASE
WHEN fullrefreshtim 0 THEN
–LAST_REFRESH_DATE + fullrefreshtim/60/60/24
to_char(LAST_REFRESH_DATE + fullrefreshtim/(60*60*24) , ‘DD-MON-YYYY HH24:MI:SS’)
WHEN increfreshtim 0 THEN
to_char(LAST_REFRESH_DATE + increfreshtim/(60*60*24) , ‘DD-MON-YYYY HH24:MI:SS’)
ELSE
to_char(LAST_REFRESH_DATE)
END “END_TIME”,
fullrefreshtim,
increfreshtim
FROM all_mview_analysis
Thanks ittichai for the original solution.
Kaushal, Thanks.
Pingback: 如何查看Oracle物化视图的运行起始时间和结束时间 | 书影博客