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.
GE, AGFA. experienced in 9i/10g/11g/12c, AdvanceReplication, Stream, GoldenGate, RAC, DataGuard, ODA, EXADATA, GridControl. In my career I have always been assigned to handle most complicated and critical oracle issues without solution online, and I hope to record some of them via this blog.
April 9, 2012
MV refresh cost long time due to internal recursive call
Subscribe to:
Post Comments (Atom)
0 Comments:
Post a Comment