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: David Boyd <davidb158_at_hotmail.com>
Date: Wed, 17 Jan 2007 12:29:19 -0500
Message-ID: <BAY101-F378926D49D7EBB836E26D1EFAB0@phx.gbl>


Thanks very much for your info. It helps me to explain why the refresh is much slower in 10g. I’ll create a job to refresh it.

Dave

>From: "Gints Plivna" <gints.plivna_at_gmail.com>
>Reply-To: gints.plivna_at_gmail.com
>To: davidb158_at_hotmail.com
>CC: oracle-l_at_freelists.org
>Subject: Re: Mview refresh is much slower in 10g
>Date: Wed, 17 Jan 2007 17:54:07 +0200
>
>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
>
>Gints Plivna
>http://www.gplivna.eu
>
>
>2007/1/17, David Boyd <davidb158_at_hotmail.com>:
>>Hi List,
>>
>>Recently we upgraded our database from 9.2.0.6 to 10.2.0.2. We have a
>>materialized view that joins two tables in the same database. It takes 19
>>min to refresh in 9i, but it takes more than 1 hr in 10g. The select
>>statement that creates the mview takes 300msec in 9i and 400msec in 10g.
>>Our system is Solaris 9 for Oracle 9i and Solaris 10 for Oracle 10g. We
>>use
>>exec dbms_refresh.refresh('"my_schema"."TEST_MV"') to refresh the mview.
>>Please advise how to speed up the refresh in 10g. Also do we have to
>>regenerate stats every time after we refresh the mview?
>>
>>Dave
>--
>http://www.freelists.org/webpage/oracle-l
>
>



From photos to predictions, The MSN Entertainment Guide to Golden Globes has it all. http://tv.msn.com/tv/globes2007/?icid=nctagline1
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jan 17 2007 - 11:29:19 CST

Original text of this message

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