Question

如何查看Oracle物化视图的刷新起始时间和结束时间

Answer 1

DBA_MVIEWS的LAST_REFRESH_DATE字段或者DBA_MVIEW_REFRESH_TIMES的LAST_REFRESH字段记录了Oracle物化视图刷新的起始时间。但如果我们想查看物化视图的刷新到底花费了多长时间，应该怎么做呢。其实，我们可以查询DBA_MVIEW_ANALYSIS。

对于完全刷新（Complete）,刷新耗时会记录在DBA_MVIEW_ANALYSIS的FULLREFRESHTIM列中。而对于可快速刷新（Fast）的时间，会记录在INCREFRESHTIM列中。
这两个值的单位都是秒。
SELECT mview_name, last_refresh_date, fullrefreshtim, increfreshtim
FROM dba_mview_analysis
WHERE owner='JOHN';
123
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
1234

上例表明MV_CHANGE_HISTORY与MV_ITEM_HISTORY最近的刷新为快速刷新，分别耗时36秒和9秒。

把它们放在一个查询里计算和显示终止时间。
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';
123456789101112131415
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