http://dbakevin.blogspot.mx/2012/04/mv-refresh-cost-long-time-due-to.html

We have a job defined to do mutiple MV refresh. Most MVs can refresh normally, but there are two MVs cost very long time to complete.
No locks/latch contention found during the refresh.

Below is the log:
START_TIME               OBJECT_NAME                                       TIME_COST(s)
---------------------------   --------------------------------------------------  ------------------
2012-01-06 00:45:44   MV_APP_CURR_ST_1_HR_YLD_AVAIL  17.65
2012-01-06 00:46:02   MV_APP_CURR_STATE_YIELD_AVAIL   2.7
2012-01-06 00:46:05   MV_APP_CURRENT_STATE                    220.34
2012-01-06 00:49:45   MV_APP_CURR_POLE_1_HR_Y_AVAIL  22.93
2012-01-06 00:50:08   MV_APP_CURR_STATE_POLE_Y_AVAIL 3.62
2012-01-06 00:50:12   MV_APP_CURRENT_STATE_POLE         125.59

From above we can see MV_APP_CURRENT_STATE and MV_APP_CURRENT_STATE_POLE cost very long time.
When check these MVs, found their definition are simply. I tried to run MVs' SELECT part, both can return all record in 1 second.

But they will take 100+ seconds when refresh the MV with below sentence:
dbms_mview.refresh('"GAPC"."MV_APP_CURRENT_STATE_POLE"', atomic_refresh ='FALSE');

After make an 10046 trace, found during the MV refresh, below internal recursive SQL cost most of time:
********************************************************************************
SQL ID: 6v8ygt9zbxsnh Plan Hash: 2840274064
select count(*), max(scn)
from
( select d.scn scn        from sys.sumdelta$ d, sys.sumdep$ o
  where o.sumobj# = :1 and o.p_obj# = d.tableobj#  and d.spare1 = 0 and d.scn > :2        
  union all        
  select m.commit_scn scn
  from sys.sumdelta$ d, sys.sumdep$ o, sys.snap_xcmt$ m        
  where
  o.sumobj# = :1 and o.p_obj# = d.tableobj#
  and  d.spare1 = 1 and
  d.xid = m.xid and m.commit_scn > :2  )
call       count      cpu   elapsed       disk      query    current        rows
------------ ------  ---------- ------------- ------------- ------------- ----------  ----------
Parse        7       0.00       0.00            0             1          0           0
Execute    7       0.00       0.00            0              0          0           0
Fetch        7     31.25    367.18     99567     127778          0           7
------------- ------  -------- ---------- ---------------- --------------- ----------  ----------
total         21    31.26    367.19     99567     127779          0           7

Notice red parts, above internel recursive SQL cost 367 seconds while total MV refresh time is 370 seconds.

After checking above SQL, find its slowness mainly caused by table sumdelta$ which is 1.5GB big:
SQL> select TABLEOBJ#,count(*) from sys.sumdelta$ group by  TABLEOBJ#;
TABLEOBJ#    COUNT(*)
-----------------  ------------
  11784739    1536910
    2857695      449018
    7506186    8808703

This table contains 10 millions records. And most record are last year's.

SQL> select owner,object_id,object_type,object_name,status from DBA_objects where object_id in (11784739,2857695,7506186);
OWNER       OBJECT_ID OBJECT_TYPE     OBJECT_NAME                              STATUS
-----------------  ---------------- ---------------------- ------------------------------------------------------- -----------------
GAPC           11784739          TABLE       MV_EVENT_METER_ONE_DAY           VALID
GAPC             2857695          TABLE       MV_APP_CURRENT_ALERT_STATE     VALID

These records in sys.sumdelta$ do related to our issue MVs.

Till now the issue is clearly. Due to some bug, expired data not purged normally in sys.sumdelta$.
This table grew bigger and bigger, and make the SQL related to it become rather slow.

After truncate this table sys.sumdelta$.
Those issue MVs finished in a few seconds.

更多案例可以访问个人blog：
http://dbakevin.blogspot.com

这是Oracle的一个bug，以前碰到过。
分析和解决问题的思路还是清晰的。
我们分析问题以后，对于解决方案，可以查看一下metalink站点，也就是现在的support站点。
如果是正版用户，干脆就发一个SR。
我们们主要学习的是解决问题的思路。

不错的案例，thx