RE: Materialized view create/refresh slowly
- From: "Khedr, Waleed" <Waleed.Khedr@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 5 Aug 2004 23:26:52 -0400
Use PQO on source and Target
Waleed
-----Original Message-----
From: David Fenng [mailto:dbanotes@xxxxxxxxx]=20
Sent: Thursday, August 05, 2004 10:50 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Materialized view create/refresh slowly
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@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 :
*** SESSION ID:(26.6530) 2004-08-04 22:49:36.393
WAIT #1: nam=3D'SQL*Net more data to client' ela=3D 22 p1=3D1413697536 =
p2=3D1989
p3=3D0
WAIT #1: nam=3D'SQL*Net more data to client' ela=3D 33 p1=3D1413697536 =
p2=3D2019
p3=3D0
.....
WAIT #1: nam=3D'SQL*Net more data to client' ela=3D 20 p1=3D1413697536 =
p2=3D2007
p3=3D0
WAIT #1: nam=3D'SQL*Net more data to client' ela=3D 17 p1=3D1413697536 =
p2=3D1997
p3=3D0
WAIT #1: nam=3D'SQL*Net more data to client' ela=3D 18 p1=3D1413697536 =
p2=3D1999
p3=3D0
WAIT #1: nam=3D'SQL*Net more data to client' ela=3D 20 p1=3D1413697536 =
p2=3D2007
p3=3D0
WAIT #1: nam=3D'SQL*Net more data to client' ela=3D 7746560 =
p1=3D1413697536
p2=3D1995 p3=3D0
WAIT #1: nam=3D'SQL*Net more data to client' ela=3D 41 p1=3D1413697536 =
p2=3D2014
p3=3D0
.............
WAIT #1: nam=3D'SQL*Net more data to client' ela=3D 16 p1=3D1413697536 =
p2=3D2000
p3=3D0
*** 2004-08-04 22:49:54.372
WAIT #1: nam=3D'SQL*Net more data to client' ela=3D 9780202 =
p1=3D1413697536
p2=3D2005 p3=3D0
WAIT #1: nam=3D'SQL*Net more data to client' ela=3D 41 p1=3D1413697536 =
p2=3D1999
p3=3D0
WAIT #1: nam=3D'SQL*Net more data to client' ela=3D 17 p1=3D1413697536 =
p2=3D2003
p3=3D0
WAIT #1: nam=3D'SQL*Net more data to client' ela=3D 16 p1=3D1413697536 =
p2=3D2001
p3=3D0
WAIT #1: nam=3D'SQL*Net more data to client' ela=3D 17 p1=3D1413697536 =
p2=3D1997
p3=3D0
WAIT #1: nam=3D'SQL*Net more data to client' ela=3D 16 p1=3D1413697536 =
p2=3D2001
p3=3D0
WAIT #1: nam=3D'SQL*Net more data to client' ela=3D 16 p1=3D1413697536 =
p2=3D2002
p3=3D0
WAIT #1: nam=3D'SQL*Net more data to client' ela=3D 16 p1=3D1413697536 =
p2=3D2000
p3=3D0
WAIT #1: nam=3D'SQL*Net more data to client' ela=3D 16 p1=3D1413697536 =
p2=3D2006
p3=3D0
WAIT #1: nam=3D'SQL*Net more data to client' ela=3D 17 p1=3D1413697536 =
p2=3D2005
p3=3D0
......
WAIT #1: nam=3D'SQL*Net more data to client' ela=3D 7758084 =
p1=3D1413697536
p2=3D2013 p3=3D0
WAIT #1: nam=3D'SQL*Net more data to client' ela=3D 34 p1=3D1413697536 =
p2=3D1993
p3=3D0
WAIT #1: nam=3D'SQL*Net more data to client' ela=3D 16 p1=3D1413697536 =
p2=3D1999
p3=3D0
WAIT #1: nam=3D'SQL*Net more data to client' ela=3D 17 p1=3D1413697536 =
p2=3D2002
p3=3D0
WAIT #1: nam=3D'SQL*Net more data to client' ela=3D 17 p1=3D1413697536 =
p2=3D1992
p3=3D0
WAIT #1: nam=3D'SQL*Net more data to client' ela=3D 17 p1=3D1413697536 =
p2=3D2009
p3=3D0
WAIT #1: nam=3D'SQL*Net more data to client' ela=3D 16 p1=3D1413697536 =
p2=3D2007
p3=3D0
WAIT #1: nam=3D'SQL*Net more data to client' ela=3D 7757175 =
p1=3D1413697536
p2=3D1997 p3=3D0
I know "ela" is about time , in the trace file,"ela=3D 7757175 " ,means
77571750ms ? my GOD!
Would you like give me some advise about this ?
Thanks anyway!!
--=20
David Fenng
http://www.dbanotes.net=20
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
