«
»

 We did see our mview going slow. When we traced the session we found our job is waiting for a while in sumdelta$.

if automic_refresh=false due to direct load operation sumdelta$ don’t purge. There are some bug registered for this.

This is the workaround.


CREATE MATERIALIZED VIEW dummy_mview
TABLESPACE tbs_data
NOLOGGING
refresh complete
start with sysdate
next (sysdate+(1/1440))
WITH ROWID
AS select sysdate,col1 from mview mv10 WHERE 1=2 ;

In the select add “sysdate” so that refresh is assumed as complex and refresh every time.

exec DBMS_MVIEW.REFRESH('DUMMY_MVIEW','C');

select count(*) from sys.sumdelta$ where tableobj# in (select obj# from sys.obj$ where name=upper('DUMMY_MVIEW');

the count of this query should reduce to zero.

convert this post to pdf.

This entry was posted by Borndba on August 23, 2010 at 8:42 pm under Oracle Administration. Both comments and pings are currently closed.