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

Home -> Community -> Mailing Lists -> Oracle-L -> Materialized view create/refresh slowly

Materialized view create/refresh slowly

From: David Fenng <dbanotes_at_gmail.com>
Date: Fri, 6 Aug 2004 10:50:10 +0800
Message-ID: <6622a16f0408051950c4f2922@mail.gmail.com>


Hi,all,

I meet a question about materialized view:

In a LAN env,remote DB (hp-ux /Oracle 9201 EE) has a table named BASE_TB ( 6 million records ), table's size is bout 20M; at local db(linux / Oracle 9204 EE) ,
I create a MV use the following SQL:

CREATE MATERIALIZED VIEW base_tb_local
BUILD IMMEDIATE
REFRESH fast START WITH SYSDATE NEXT trunc(SYSDATE) + 1 WITH ROWID
AS SELECT * FROM base_tb_at_dblinkname
;

slow-going, took me about one hour . then I ftp a big file to remote ,and download a big file from remote db's site , the speed is OK, very fast. .

I tried to refresh the MV ,slowly......then at remote db ,I do some trace about the session.Such as :

execute dbms_system.set_ev(<sid>,<serial>,10046,12,'');

and found that :

I know "ela" is about time , in the trace file,"ela= 7757175 " ,means 77571750ms ? my GOD!

Would you like give me some advise about this ?

Thanks anyway!!

-- 
David Fenng
http://www.dbanotes.net 
some articles about Oracle RDBMS 8i/9i/10g (in Chinese)
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Thu Aug 05 2004 - 21:46:05 CDT

Original text of this message

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