Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> materialized view refresh in 9i vs. 10g

materialized view refresh in 9i vs. 10g

From: arctic fox <fujitaka_at_comcast.net>
Date: 10 Jun 2006 22:58:54 -0700
Message-ID: <1150005534.530662.139650@m38g2000cwc.googlegroups.com> 





In the process of migrating a database from Oracle 9.0 to 10.2, I came
across a mundane-looking mview that refreshes quickly on the old 9i
database but never finishes a complete-mode refresh under 10g.  It
turns out that in Oracle 9.0 and 9.2, "dbms_mview.refresh(mview_name,
'C')" uses TRUNCATE and direct-path insert, but under 10.2 it uses
DELETE and conventional-path INSERT.  The latter approach has the merit
of not implicitly committing the underlying table in an empty state
(via the truncate), but it costs a great deal more in undo and redo
space (and also potentially in runtime).  The cost is much higher if
the mview has indexes, as mine does.



Does anyone know why the behavior of refresh changed and if there's a
way to invoke the truncate/append logic (other than writing a
home-grown refresh procedure)?



Test cases follow:





Case 1: Oracle 9.0.1.4.0 - Standard Edition





SQL> create table t as select object_id from all_objects where rownum
<= 30000 ;


SQL> alter table t add constraint pk_t primary key ( object_id ) ;
SQL> create materialized view t_mv as select object_id from t ;


SQL> column value new_val V
SQL> set verify off
SQL> column name format a15
SQL> select name, value




 from v$statname join v$mystat using (statistic#)
 where name = 'redo size'


 ;



NAME                 VALUE
--------------- ----------
redo size          1850568





SQL> alter session set events '10046 trace name context forever, level
12' ;


SQL> exec dbms_mview.refresh('T_MV', 'C') ;



Elapsed: 00:00:00.36



SQL> select name, value, to_char(value-&V, '999,999,999') as diff
 from v$statname join v$mystat using (statistic#)
 where name = 'redo size'


 ;



NAME                 VALUE DIFF
--------------- ---------- ------------
redo size          3928916    2,078,348





% less tkprof.out

////////////////////////////////////////////////////////////////////////////////////
///                         Start of trace file excerpts
         ///
////////////////////////////////////////////////////////////////////////////////////


truncate table "FOO"."T_MV" purge snapshot log



call     count       cpu    elapsed       disk      query    current


    rows


------- ------  -------- ---------- ---------- ---------- ----------



Parse        1      0.00       0.00          0          0          1
       0
Execute      1      0.02       0.08          7         39         35
       0
Fetch        0      0.00       0.00          0          0          0
       0




------- ------  -------- ---------- ---------- ---------- ----------



total        2      0.02       0.08          7         39         36
       0





...



INSERT /*+ APPEND */ INTO "FOO"."T_MV"("OBJECT_ID") SELECT

"T"."OBJECT_ID"



  FROM "T" "T"


call     count       cpu    elapsed       disk      query    current


    rows


------- ------  -------- ---------- ---------- ---------- ----------



Parse        1      0.01       0.00          0          0          0
       0
Execute      1      0.23       0.29          2        183       1337
   30000
Fetch        0      0.00       0.00          0          0          0
       0




------- ------  -------- ---------- ---------- ---------- ----------




total        2      0.24       0.29          2        183       1337


   30000

////////////////////////////////////////////////////////////////////////////////////
///                          End of trace file excerpts
         ///
////////////////////////////////////////////////////////////////////////////////////


=============================================


Case 2: Oracle 10.2.0.1.0 - Standard Edition





SQL> create table t as select object_id from all_objects where rownum
<= 30000 ;


SQL> alter table t add constraint pk_t primary key ( object_id ) ;
SQL> create materialized view t_mv as select object_id from t ;


SQL> column value new_val V
SQL> set verify off
SQL> column name format a15
SQL> select name, value from v$statname join v$mystat using


(statistic#) where name = 'redo size' ;


NAME                 VALUE
--------------- ----------
redo size          1847556





SQL> exec dbms_monitor.session_trace_enable();
SQL> exec dbms_mview.refresh('T_MV', 'C') ;



Elapsed: 00:00:02.23



SQL> select name, value, to_char(value-&V, '999,999,999') as diff
 from v$statname join v$mystat using (statistic#)
 where name = 'redo size'


 ;



NAME                 VALUE DIFF
--------------- ---------- ------------
redo size         22864832   21,017,276





% less tkprof.out

////////////////////////////////////////////////////////////////////////////////////
///                         Start of trace file excerpts
         ///
////////////////////////////////////////////////////////////////////////////////////


delete from "FOO"."T_MV"



call     count       cpu    elapsed       disk      query    current


    rows


------- ------  -------- ---------- ---------- ---------- ----------



Parse        1      0.00       0.00          0          2          0
       0
Execute      1      1.87       1.82         62         48      91854
   30000
Fetch        0      0.00       0.00          0          0          0
       0




------- ------  -------- ---------- ---------- ---------- ----------




total        2      1.87       1.83         62         50      91854


   30000


  Event waited on                             Times   Max. Wait  Total


Waited




...



INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "FOO"."T_MV"("OBJECT_ID")

SELECT



  "T"."OBJECT_ID" FROM "T" "T"


call     count       cpu    elapsed       disk      query    current


    rows


------- ------  -------- ---------- ---------- ---------- ----------



Parse        1      0.00       0.00          0          2          0
       0
Execute      1      0.53       0.52          0        180      61404
   30000
Fetch        0      0.00       0.00          0          0          0
       0




------- ------  -------- ---------- ---------- ---------- ----------




total        2      0.53       0.52          0        182      61404


   30000



  (No wait events.)

////////////////////////////////////////////////////////////////////////////////////
///                          End of trace file excerpts
         ///
////////////////////////////////////////////////////////////////////////////////////


================================================================


Case 3: Manually truncate and then refresh in Oracle 10.2.0.1.0




SQL> column value new_val V
SQL> set verify off
SQL> col name format a15
SQL> select name, value from v$statname join v$mystat using


(statistic#) where name = 'redo size' ;


NAME                 VALUE
--------------- ----------
redo size                0





SQL> truncate table t_mv ;



SQL> select name, value, to_char(value-&V, '999,999,999') as diff
 from v$statname join v$mystat using (statistic#)
 where name = 'redo size'


 ;



NAME                 VALUE DIFF
--------------- ---------- ------------
redo size            20032       20,032





SQL> exec dbms_mview.refresh('T_MV', 'C') ;



SQL> select name, value, to_char(value-&V, '999,999,999') as diff
 from v$statname join v$mystat using (statistic#)
 where name = 'redo size'


 ;



NAME                 VALUE DIFF
--------------- ---------- ------------
redo size          7140452    7,120,420


Received on Sun Jun 11 2006 - 00:58:54 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US