Jan 21 2013

How long did Oracle materialized view refresh run?

Category: Database,Materialized Viewsittichai @ 9:38 am

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]

Tags: , , , ,

3 Responses to “How long did Oracle materialized view refresh run?”

  1. Hassan says:

    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?

  2. Kaushal says:

    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.

Leave a Reply