RE: materialized views

From: <Paula_Stankus_at_doh.state.fl.us>
Date: Mon, 11 Apr 2005 12:29:23 -0400
Message-ID: <E518A02B320C8D4E97D99A8C546480210C3EAD51@dohsmailhq01.doh.ad.state.fl.us>

I love the standby approach - awesome! We have to have a standby anyway.=20

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark Bole Sent: Monday, April 11, 2005 11:01 AM
Cc: oracle-l_at_freelists.org
Subject: Re: materialized views

Paula_Stankus_at_doh.state.fl.us wrote:
> Guys,

>=20

> We are concerned that if we use materialized views on our OLTP system=20
> to just capture changed or new rows that it will lead to performance=20
> degradation. Any suggestions on setting up materialized views to=20
> prevent this? =3D20
> --
> http://www.freelists.org/webpage/oracle-l
>=20

Why do you want to use MV's? If the performance benefit of using the MV's is greater than the cost of maintaining them, then what's the problem? Obviously you need to do some testing to find out if this is true in your case. Also, if you literally mean "just capture changed or new rows", then consider Change Data Capture, since an MV will include all the rows, not just the changed ones.

Set up your MV's to use fast refresh, and refresh them only during a period of light OLTP usage. Also consider using Resource Manager to prevent the refresh process from consuming too much CPU.

Another approach I have used successfully is to set up a Data Guard logical standby on a different server, create the MV logs in the standby, and then under a different schema, create the MVs. This completely removes any extra load from your primary, except for the supplemental logging required for the logical standby, which is minimal.

--=20
Mark Bole
http://www.bincomputing.com

--
http://www.freelists.org/webpage/oracle-l


BEGIN-ANTISPAM-VOTING-LINKS
------------------------------------------------------
Teach CanIt if this mail (ID 29301582) is spam:
Spam:
https://dohsmsi01.doh.state.fl.us/canit/b.php?c=3Ds&i=3D29301582&m=3D9e8f=
94d5f
784
Not spam:
https://dohsmsi01.doh.state.fl.us/canit/b.php?c=3Dn&i=3D29301582&m=3D9e8f=
94d5f
784
Forget vote:
https://dohsmsi01.doh.state.fl.us/canit/b.php?c=3Df&i=3D29301582&m=3D9e8f=
94d5f
784
------------------------------------------------------
END-ANTISPAM-VOTING-LINKS

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Apr 11 2005 - 12:33:25 CDT