Re: Remote MVs.
- From: Mathias Magnusson <mathias.magnusson@xxxxxxxxx>
- To: gurenich@xxxxxxxxx
- Date: Tue, 9 Jun 2009 08:16:23 +0200
Maria,
I have put in solutions with remote MVs and taken out all depending on need
and what solution fits the problem best. The time I put it into a solution
as when we had 12 regional databases, but a lot of "reference" data needed
to be maintained in one place. We ended up with over 100 tables we moved to
the regional databases several times each day. This worked reasonably well
due to the rate of change was fairly low and we didn't need the data to be
current at all times.
When I reworked a solution using MVs to not do it was when a solution had
put in a solution where all reporting data (essentially all data in the
database) was moved to a database on a different server using MVs. The load
on I/O for writing to the logs, reading from the logs, network traffic and
so on caused this to not be practical. It had a much bigger impact on the
operational system than the actual execution of the reports. Part of the
reason was that all data was replaced in this solution over 90 days. So we
saw a rate of change on the largest tables of over 1% per day. With some
volume that becomes a challenge to maintain and write over network.
Especially when it has to be current within 15 minutes. The impact during
the peak of the processing day was just not acceptable.
What I would recommend is that you create a way for the reporting
application to work such that you can change database later and also freedom
to change the way your data is provided. I would do this by using an alias
defined in TNS names as the "SID" used to connect to the database. Then you
can switch it to a different database later. In the schema you put their
objects in (preferably not the same as the schema the application uses),
create views that performs the work you want the MVs to do. Now you can move
the views, and even replace them with MVs in a remote DB if you find that as
needed. You can even make the decision on view by view so you only accept
the complexity for the views that has an unacceptable impact on your
production system.
That is, start with the simple solution of having the views defined in your
current DB (different schema) and move gradually to MVs in remote schema if
you need to. Another option in the middle is to create MVs in the local
database if they do a lot of aggregations that taxes the current database
too much and the data does not have to be current at all times.
Mathias
On Tue, Jun 9, 2009 at 6:18 AM, Maria Gurenich <gurenich@xxxxxxxxx> wrote:
> Yo, ppl.
>
> I was asked if this is possible to have a database that would contain a
> very specific views that are built from the source database objects so that
> our security ppl could run their reports. IMHO, the ideal solution would be
> building a replication site using MVs.
>
> My question to you is the following: I am not quite sure would there be any
> impact on my source database as eventually the umber of MVs can grow if I
> did everything correctly and everybody is satisfied with the result.
>
> The one who uses remote MVs - please, share your experience, pros and cons,
> the speed of building replication environment, etc.. This is all is under
> the investigation yet, so any suggestion would be greatly appreciated.
>
> Thanks a lot before hand,
> M.
>
Other related posts:
- » Remote MVs.- Maria Gurenich
- » Re: Remote MVs. - Mathias Magnusson
