Home » RDBMS Server » Performance Tuning » Materialized View- Delay refresh
Materialized View- Delay refresh [message #617036] Tue, 24 June 2014 05:14 Go to next message
preethi_s0
Messages: 3
Registered: June 2014
Junior Member
Hi All,

I have a problem. There is a Materialized View in my database with refresh option as "next refresh = sysdate+(1/48)" so it should execute for every 30 min. but view is taking around 4 hours to refresh.
when i execute the view script it takes around 40 min to execute. now I am not sure where the issue is. please help me.

Thanks,
Preethi
Re: Materialized View- Delay refresh [message #617037 is a reply to message #617036] Tue, 24 June 2014 05:17 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
preethi_s0 wrote on Tue, 24 June 2014 15:44
now I am not sure where the issue is. please help me.


And even we don't know where the issue is, since there is nothing to see. Please help us.
Re: Materialized View- Delay refresh [message #617039 is a reply to message #617037] Tue, 24 June 2014 05:25 Go to previous messageGo to next message
preethi_s0
Messages: 3
Registered: June 2014
Junior Member
Hi Lalit, I am not familiar with materialized view.. let me know if you need any details

Thanks
Re: Materialized View- Delay refresh [message #617040 is a reply to message #617036] Tue, 24 June 2014 05:27 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read

Perhaps (only a guess, because you have provided no information) you are getting a complete refresh rather than a fast refresh.
Re: Materialized View- Delay refresh [message #617041 is a reply to message #617040] Tue, 24 June 2014 05:35 Go to previous messageGo to next message
preethi_s0
Messages: 3
Registered: June 2014
Junior Member
Hi John,

yes, refresh type is 'Complete' with primary Key as 'Yes'

when I query

SELECT mview_name, last_refresh_date, fullrefreshtim, increfreshtim
FROM dba_mview_analysis where MVIEW_NAME like <<VIEW>>
the fullrefreshtim is = 6042.

If the last refresh date is '24/06/2014 08:14:40' and next refresh is with interval of 30 min it has to start refresh by 8:45 AM
and to be refreshed by 10:45 AM. but its not happening.

Please let me know the information you are looking for.

Thanks again
Re: Materialized View- Delay refresh [message #617042 is a reply to message #617041] Tue, 24 June 2014 05:41 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
When you post code, please format it correctly and enclose within [code] tags. You have been asked to do this already. Ignoring this does not encourage responses.

I have no idea of the size of the view or of the detail tables. I do not know the query that defines the view. I do not know whether, and if so how, you have created materialized view logs. I do not know anything. except that you are using complete refresh, not a fast refresh.
Re: Materialized View- Delay refresh [message #617049 is a reply to message #617042] Tue, 24 June 2014 07:29 Go to previous messageGo to next message
pvsarat
Messages: 10
Registered: October 2012
Location: CHENNAI
Junior Member
It is better to provide the explain plan
Re: Materialized View- Delay refresh [message #617054 is a reply to message #617049] Tue, 24 June 2014 07:44 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The explain plan for a mview refresh? /forum/fa/7257/0/

[Updated on: Tue, 24 June 2014 07:44]

Report message to a moderator

Previous Topic: Calculating schema statistics during a huge 10 hour batch job
Next Topic: Debug Procedure
Goto Forum:
  


Current Time: Thu Mar 28 18:37:50 CDT 2024