【案例分享3】MV refresh cost long time due to internal recursive call
本帖最后由 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_AVAIL17.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_AVAIL22.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#
andd.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 byTABLEOBJ#;
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
页:
[1]