Transaction Performance Impact of Materialized View Logs Transaction Performance Impact of Materialized View Logs oracle oracle

Transaction Performance Impact of Materialized View Logs


Yes, there will be an impact. The materialized log needs to be maintained synchronously so the transactions will need to insert a new row into the materialized view log for every row that is modified in the base table. How great an impact depends heavily on the system. If your system is I/O bound and you've optimized it so that physically writing the changes to the base table is a significant fraction of the wait time, the impact will be much greater than if your system is CPU bound and most of your wait time is spent reading data or performing computations.

If you are really concerned about the performance of the OLTP system, it would make sense to offload reporting to a different database on a different server. You can replicate the data to the reporting server using Streams (or GoldenGate if you can afford the additional licensing) which will have less of an impact on the source than materialized views because the redo information can be read asynchronously (and can be read on the reporting server rather than putting that workload on the production server). You could then define materialized views on the reporting server where they won't have any impact on the OLTP server. Or you could create a logical standby database as your reporting server and create the materialized views there. Either way, moving the reporting workload off the production server and reading the redo data asynchronously will protect the performance of the production server.