Fast refresh takes eternity

From: News <Contact_404_at_hotmail.com>
Date: 27 Apr 2006 01:49:21 -0700
Message-ID: <1146127761.563170.166590@y43g2000cwc.googlegroups.com>

I have very serious problem with implementing materialized views with 10gr2 on AIX 5.2.

The MV are joins only between fact table and dimensions.

For instance, the complete build of the smallest MV takes 20mn. size: 15 488 MB. 16 825 331 rows. it involves joins between fact and 7 dimension tables. The names of tables are followed by the size of MV log after running daily ETL jobs.

DECL.F_LGN_DECL_1_8, (5 MB)
DECL.A_COM_ASSUJ_TRANS,  (2MB)
DECL.A_PERIODE_DECL, (2M)
DECL.A_PJ_DEBIT,  (7M)
DECL.A_TYP_PRSL, (768 K)
IMMAT.ACAL_DECL ACAL_DECL_DEB_VALID_LGN, not changed

IMMAT.ACAL_DECL ACAL_DECL_FIN_VALID_LGN, IMMAT.A_RISQ (640 K) MV are created this way:

1. create MV logs
2. create MV
3. analyze MV estimate statistics
4. create indexes on rowid
5. analyze indexes estimate statistics

Refresh is done using
dbms_mview.refresh(mv_name, '?', '', TRUE, FALSE, 0,0,0, FALSE);

'?' is used because some views are defined with complete refresh and others with fast. Any way no matter '?' or 'f' as parameter, the fast refresh takes many hours and doesn't end. The refresh session is always active and involves many i/o.

Another very serious problem that results from this situation: it's not possible to cancel refresh. killing the session or shutting abort messes up sysaux or undo tablespaces and the whole database (2.5 TB of size) needs to be restored witch takes 8 hours.

Fast refresh works fine only when MV logs are empty or very small. Received on Thu Apr 27 2006 - 03:49:21 CDT