OraFAQ Forum: Performance Tuning » Materialized View- Delay refresh

Home » RDBMS Server » Performance Tuning » Materialized View- Delay refresh

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Materialized View- Delay refresh [message #617036]

Tue, 24 June 2014 05:14

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

Report message to a moderator

Re: Materialized View- Delay refresh [message #617037 is a reply to message #617036]

Tue, 24 June 2014 05:17

Lalit Kumar B
Messages: 2777
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.

Report message to a moderator

Re: Materialized View- Delay refresh [message #617039 is a reply to message #617037]

Tue, 24 June 2014 05:25

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

Report message to a moderator

Re: Materialized View- Delay refresh [message #617040 is a reply to message #617036]

Tue, 24 June 2014 05:27

John Watson
Messages: 5032
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.

Report message to a moderator

Re: Materialized View- Delay refresh [message #617041 is a reply to message #617040]

Tue, 24 June 2014 05:35

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

Report message to a moderator

Re: Materialized View- Delay refresh [message #617042 is a reply to message #617041]

Tue, 24 June 2014 05:41

John Watson
Messages: 5032
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.

Report message to a moderator