Refresh materialized view slower in 10g

Yep,  took over existing procedure of a database with several hundreds of dbms_snapshot.refresh in it, and all on Oracle 9i.  Installed a test-database next to it with, and a complete refresh appeared to be 6 times slower, while producing a lot of archive_logs.

Changing to dbms_mview.refresh didn’t help, while the parameter  atomic_refresh is default ‘false’, and as a result of that, records are deleted instead of truncating the table.  Solution: atomic_refresh=>FALSE.

All on 2 Metalink-notes: 365157 en 553464.1

Did some tests  (the  timing is approximately, while there’s some caching):

on the old  9.2 database:

1. Original snapshot-refresh

SQL> exec dbms_snapshot.refresh( list => ‘<mview_name>’, method => ‘C’);

Elapsed: 00:00:01.02

2. De old way on the  10g-database:

SQL>  exec dbms_snapshot.refresh( list => ‘<mview_name>’, method => ‘C’);

Elapsed: 00:00:03.08

3. Using mview_refresh, no change:

SQL> exec dbms_mview.refresh(‘<mview_name>’,’C’,atomic_refresh=>TRUE);

Elapsed: 00:00:03.58

4. Changed to  atomic_refresh=false:

SQL> exec dbms_mview.refresh(‘<mview_name>’,’C’,atomic_refresh=>FALSE);

Elapsed: 00:00:00.90

By | 2008-12-11T14:50:28+00:00 December 11th, 2008|Categories: Database|Tags: |1 Comment

About the Author:

One Comment

  1. Jacco H. Landlust December 31, 2008 at 11:33 am

    Laat ik nou net met dit probleem stoeien, zelfs nu we niet meer dagelijks samenwerken zijn we toch mentaal verbonden 😉

Leave A Comment