OraFAQ Forum: Performance Tuning » Materialized View Refresh Performance

Home » RDBMS Server » Performance Tuning » Materialized View Refresh Performance (Oracle 10g)

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

Materialized View Refresh Performance [message #359638]

Mon, 17 November 2008 15:01

amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US

Member

I have created 12 materialized fetching the data from same table.
All the views are having same query with only different column name and Create MV script is something like this:

CREATE MATERIALIZED VIEW SUMMARY
TABLESPACE T1
   BUILD IMMEDIATE
   REFRESH COMPLETE
   START WITH SYSDATE
   NEXT  SYSDATE + 15/(24*60)
   AS   
  SELECT x.firm, x.user,
  COUNT(*),
  x.date
  FROM detail x
GROUP BY x.firm, x.user,date
/

The referesh time for all the views is 15 minutes and DETAIL table is having more than 50 Millions of records in it

Can anyone guide me what steps should i follow to improve the refresh performance of Materialized views?

Thanks and Regards,

Report message to a moderator

Re: Materialized View Refresh Performance [message #359651 is a reply to message #359638]

Mon, 17 November 2008 19:33

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

You need to make it REFRESH FAST instead of REFRESH COMPLETE.

Ross Leishman

Report message to a moderator

Re: Materialized View Refresh Performance [message #359908 is a reply to message #359638]

Tue, 18 November 2008 13:30

coleing
Messages: 213
Registered: February 2008

Senior Member

If you need a complete refresh each time (for example the detail table moves more than 10% of its records in your refresh period), then you can investigate the following

Depending on your needs (Read the manual on these options).

You can

1. add parallel (hardware supporting), partition the mview and add parallel, create the mview with nologging.

2. when you refresh, you can have the atomic_refresh=false option which does a truncate instead of a delete on the refresh. again read the manual to see if this fits your needs.

Report message to a moderator

Previous Topic:	jobsq slave
Next Topic:	parallel hint

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 28 01:30:46 CDT 2024