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

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Mview refresh is much slower in 10g

Re: Mview refresh is much slower in 10g

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Thu, 18 Jan 2007 23:02:37 +0100
Message-ID: <10da01c73b4c$5e0074f0$3c02a8c0@JARAWIN>


Hi List,  

> The last post in that thread (from Pavel Ruzicka) implies that

> the default refresh mechanism did indeed change from 9i->10g,

> but that there is a workaround.
 

Despite the fact that it is not documented change of the default behaviour, I thing it is a good feature enabling a 24 hour accessible full refreshable MV.  

My question - are there experiences in 9i how to workaround the gap on full refresh, where the MV is empty (other than simply say there is a loading window, don't query the MV)?  

regards,  

Jaromir

  On 1/17/07, Gints Plivna <gints.plivna_at_gmail.com> wrote:     Refresh method has been changed.
    Previously (<9i) for complete refreshes Oracle did truncate mv and     insert /*+ append */.
    Now (10g) it does delete, normal insert.

    It is discussed for example here
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15695764787749

  I didn't see in the AskTom article cited where it said that the   refresh mechanism changed from truncate/insert append to   delete and insert when moving from 9i to 10g.

  It does say that the if the MV is part of a refresh group, a   complete refresh will be done as delete/insert, rather than   as truncate/append.

  The last post in that thread (from Pavel Ruzicka) implies that   the default refresh mechanism did indeed change from 9i->10g,   but that there is a workaround.

  I ran a small test to create an MV and do a complete refresh   on both 9.2.0.6 and 10.2.0.1 that confirms this behavior.

  From the 9i trace:

  PARSING IN CURSOR #15 len=56 dep=1 uid=45 oct=85 lid=45 tim=1141749498683672 hv=68464594 ad='5cad9640'   truncate table "JS001292"."MVTEST_MV" purge snapshot log   END OF STMT
  ...
  INSERT /*+ APPEND */ INTO "JS001292"."MVTEST_MV"("OWNER","TABLE_NAME","TABLESPACE_NAME") SELECT "MVTEST"."OWNER","MVTEST"."TABLE_NAME","MVTEST"."TABL   ESPACE_NAME" FROM "MVTEST" "MVTEST"
  END OF STMT   From the 10g trace:

  PARSING IN CURSOR #15 len=35 dep=1 uid=56 oct=7 lid=56 tim=1141749792514219 hv=540326182 ad='733ed2d0'    delete from "JS001292"."MVTEST_MV"
  END OF STMT
  ...
  INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "JS001292"."MVTEST_MV"("OWNER","TABLE_NAME","TABLESPACE_NAME") SELECT "MVTEST"."OWNER","MVTEST"."TABLE_NAME   ","MVTEST"."TABLESPACE_NAME" FROM "MVTEST" "MVTEST"   By changing the refresh method to set atomic_refresh = false, the truncate/append   behavior can be restored.

  begin

  Here's the results of doing so in 10g:

  PARSING IN CURSOR #24 len=57 dep=1 uid=56 oct=85 lid=56 tim=1141750173641027 hv=455978900 ad='705be890'    truncate table "JS001292"."MVTEST_MV" purge snapshot log   END OF STMT
  ...
  PARSING IN CURSOR #24 len=208 dep=1 uid=56 oct=2 lid=56 tim=1141750173945788 hv=896677336 ad='6d21e8f0'   INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND */ INTO "JS001292"."MVTEST_MV"("OWNER","TABLE_NAME","TABLESPACE_NAME") SELECT "MVTEST"."OWNER","MVTEST"."TA   BLE_NAME","MVTEST"."TABLESPACE_NAME" FROM "MVTEST" "MVTEST"   END OF STMT   Nice to know.

  --
  Jared Still
  Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jan 18 2007 - 16:02:37 CST

Original text of this message

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