本帖最后由 kevin.zhang 于 2012-4-9 17:58 编辑

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


标签: 暂无标签
kevin.zhang

写了 32 篇文章,拥有财富 308,被 10 人关注

转播转播 分享分享 分享淘帖
回复

使用道具

游客 | 发表于 2012-4-11 09:05:44
不错的案例,thx
回复

使用道具

P6 | 发表于 2012-4-10 09:21:52
这是Oracle的一个bug,以前碰到过。
分析和解决问题的思路还是清晰的。
我们分析问题以后,对于解决方案,可以查看一下metalink站点,也就是现在的support站点。
如果是正版用户,干脆就发一个SR。
我们们主要学习的是解决问题的思路。
回复

使用道具

您需要登录后才可以回帖 登录 | 加入社区

本版积分规则

意见
反馈