Asktom: Redo logs are generated by MV.

You Asked

Hi Tom,

I was at your conference in MTL in february.  It was a pleasure to finally meet you. 

My question is regarding the refresh of MVs.  We are using Discoverer with a 9i Database 
in Archivelog mode.   The problem is when we need to refresh our Materialized Views, a 
lot of redo logs are generated.  It is somewhow strange to me since I though the refresh 
was done with an append hint.  The Materialized views have been created with the 
NOLOGGING option.

CREATE MATERIALIZED VIEW ....
AS SELECT /* APPEND */...

We are using the DBMS_MVIEWS.REFRESH procedure:

exec DBMS_MVIEW.REFRESH('TBGADM.TBGADM_MV_44','C','',FALSE,FALSE,0,0,0,TRUE); 

This refresh generated around 679 MB of REDO (the MV weights around 200MB).  How can we 
accomplish this refresh using less ARCHIVE LOGS?  

Can you shed some light on this?

Rich

 

and we said...

If you do incremental refreshes, the MV would be refreshed using conventional SQL.

If this is a single table, complete refresh, it will use:

truncate
insert /*+ append */

which DOES NOT negate redo, redo will be generated -- append bypasses UNDO on the table 
data only (and hence minimizes redo, but does not bypass it).

Only if it was a NOLOGGING object would the append bypass redo and then only for the 
table -- but not any indexes on the table (if this MV has indexes, the will always be 
logged regardless, they have to be)


If this table is part of a refresh group, it'll be done with a DELETE (redo) plus insert 
(redo).


But here is what you should be seeing if this is a single table (not in a refresh group) 
and how you can mimimize the redo generated and what'll happen if you have indexes on 
this:


ops$tkyte@ORA920PC> select count(*) from big_table;
 
  COUNT(*)
----------
   1000000
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> drop materialized view big_table_mv;
 
Materialized view dropped.
 
ops$tkyte@ORA920PC> create materialized view big_table_mv as select * from big_table;
 
Materialized view created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> column value new_val V
ops$tkyte@ORA920PC> set verify off
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and a.name = 'redo size'
  5  /
 
NAME                                VALUE
------------------------------ ----------
redo size                       153976776
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> exec dbms_mview.refresh( 'BIG_TABLE_MV', 'C' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select a.name, b.value, to_char( b.value-&V, '999,999,999,999' ) diff
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and a.name = 'redo size'
  5  /
 
NAME                                VALUE DIFF
------------------------------ ---------- ----------------
redo size                       330108116      176,131,340
 
so, in archivelog mode, about 176m of redo generated for that -- no indexes or 
anything.  we can drastically reduce that:

ops$tkyte@ORA920PC> alter table big_table_mv nologging;
 
Table altered.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> exec dbms_mview.refresh( 'BIG_TABLE_MV', 'C' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select a.name, b.value, to_char( b.value-&V, '999,999,999,999' ) diff
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and a.name = 'redo size'
  5  /
 
NAME                                VALUE DIFF
------------------------------ ---------- ----------------
redo size                       387304376       57,196,260


using nologging on the MV table -- but -- if you have indexes:


ops$tkyte@ORA920PC> create index big_table_mv_idx on 
big_table_mv(owner,object_type,object_name);
 
Index created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and a.name = 'redo size'
  5  /
 
NAME                                VALUE
------------------------------ ----------
redo size                       444214684
 
ops$tkyte@ORA920PC> exec dbms_mview.refresh( 'BIG_TABLE_MV', 'C' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select a.name, b.value, to_char( b.value-&V, '999,999,999,999' ) diff
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and a.name = 'redo size'
  5  /
 
NAME                                VALUE DIFF
------------------------------ ---------- ----------------
redo size                       709972648      265,757,964
 

and the only way to reduce that would be to use a custom job to refresh the MV and the 
job would:

a) disable indexes
b) call refresh
c) rebuild indexes with no logging


But honestly, 6/700meg of redo isn't but a drop in the proverbial bucket (eg: it really 
isn't "that much" in the grand scheme of things) -- but those are the things you can do 
about it.

Bear in mind, you need to backup any files with unrecoverable changes right after doing 
them!