I would like to find the MVIEW Refresh timing - I checked the USER_MVIEW_ANALYS - both INCREFRESHTIM & FULLREFRESHTIM shows as 0
Tried to check if there is any bugs and couldnt find any in metalink. Any ideas?
Edited by: 789826 on Jan 23, 2013 10:34 AM
I do see doc saying that
SUMMARY VARCHAR2(1) Indicates whether this materialized view includes a GROUP BY clause or aggregation (Y) or not (N)
FULLREFRESHTIM NUMBER Approximate refresh time, in seconds, for full refresh (defined only when SUMMARY = Y)
INCREFRESHTIM NUMBER Approximate refresh time, in seconds, for fast refresh (defined only when SUMMARY = Y)
-> But for some of the mviews that have SUMMARY defined as Y the refresh times is still 0
The FULLREFRESHTIM & INCREFRESHTIM is not in ALL_MVIEWS . For this we have to refer to the DBA_MVIEW_ANALYSIS - Metalink ID ID 1513554.1 also suggests the same.
The other question brings an interesting point. I just verified that since the last couple of days, due to some major changes, we had used drop / create. Probably thats why it is all 0's.
I am trying to find out how long the last materialized view refresh run?
SELECT OWNER,mview_name, last_refresh_date, refresh_method, fullrefreshtim, increfreshtim, summary
I tried using above query. But as per oracle doc, FULLREFRESHTIM or INCREFRESHTIM column returns the refresh time only when value of the SUMMARY is defined as “Y”. In our case for all the materialized views the summary column value is N. The value of increfreshtim column is zero for most of the materialized views. Also for some of the materialized views the value for fullrefreshtim column is greater than zero even if the refresh method is fast. For some of the fast refresh Mviews value of fullrefreshtim and increfreshtim is greater than 0.
Why fullrefreshtim is greater than 0 for fast refresh Mviews?
Can some one please help with this issue?