Home » SQL & PL/SQL » SQL & PL/SQL » Materialized view ""Refresh"" taking long time
Materialized view ""Refresh"" taking long time [message #217142] Wed, 31 January 2007 17:10 Go to next message
vish3210
Messages: 4
Registered: August 2006
Location: Portland
Junior Member

Hi,

I've below Materialized views. It is pulling the data from remote which has 5 million rows. How to make the refresh faster. Any input is greatly appreciated. We are in ORACLE 10g. To refresh it is taking 20 minutes now. How to make this refresh faster.

CREATE SNAPSHOT stg_dpr.cdb_sty_reg_cyc_clsfn_org_mv
TABLESPACE stgdpr_dl
BUILD IMMEDIATE
USING INDEX
TABLESPACE stgdpr_il
REFRESH ON DEMAND
WITH PRIMARY KEY
AS
select clsfn_id
, cyc_id
, org_id
, org_ver_id
, reg_id
, sty_id
from sty_reg_cyc_clsfn_org@cdb
where reg_id in (0,5,8,20,21,22,23,18,19,24,25,26,27,6,7,28,29,30,31,51,53,48)

Thanks
--Vimal
Re: Materialized view ""Refresh"" taking long time [message #217154 is a reply to message #217142] Wed, 31 January 2007 19:42 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
What type of refresh? Fast? Force? Complete? PCT?

Materialized views run a LOT of SQL behind the scenes when they refresh, to delete from the MV table, update/delete the MV Log, load the MV. There are dozens of different things that could be slow.

If I suggested something, I would only be guessing. The only way to be sure is to trace the session and run TKPROF to obtain the SQLs run and the time spent.

Ross Leishman
Re: Materialized view ""Refresh"" taking long time [message #217354 is a reply to message #217154] Thu, 01 February 2007 16:05 Go to previous messageGo to next message
vish3210
Messages: 4
Registered: August 2006
Location: Portland
Junior Member

That is the force refresh. Also query executes very fast. Fraction of second. But refresh of the Materialized views taking long time in 10g. So... Any thoughts.

Thsnk
--Vimal
Re: Materialized view ""Refresh"" taking long time [message #217365 is a reply to message #217154] Thu, 01 February 2007 19:17 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Hmmm. Any thoughts.... any thoughts..... let me think....

Ah yes! You could:
rleishman wrote on Thu, 01 February 2007 12:42
trace the session and run TKPROF to obtain the SQLs run and the time spent.

Ross Leishman



But then I'm sure you're already onto that, because you would never just read the first line of my response and ignore the rest. Or would you?

Ross Leishman
Previous Topic: Login as different user inside Procedure?
Next Topic: Types of index?why we use index? with example?
Goto Forum:
  


Current Time: Sat Dec 10 00:58:23 CST 2016

Total time taken to generate the page: 0.11949 seconds