April 9, 2012

MV refresh cost long time due to internal recursive call

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.

0 Comments:

Post a Comment