Performance of Remote Materialized views in Oracle

Question

I have a question reagrding Oracle materialized views ...

We have two databases:

1. Core Database
2. Reporting database

The reporting database has:

• a database link to the Core database
• a number of synonyms to the tables in the Core database
• a number of Materialized views defined on top of those synonyms.

The views are set up to refresh hourly.

With increased data volume in the source system, we are seeing increased CPU to materialize the views.

Upon closer inspection, it appears that the view refresh process constructs the result set within the Reporting database - and sends individual, smaller SQL statetements to the Core database.

Some of these materialized views are very complex, and have lots of joins between tables. This is resulting in millions of little SQL statements against the Core database.

My question is: would it be better to create a corresponding "complex" view in the Core database, and have a materialized view in the Reporting database, as a simple "SELECT * FROM CORE.MY_MAT_VIEW"

thanks for any pointers,

cheers, Evan

Answer 1

I wouldn't have anything too complex in the Core database. You'd put more load on the core database, plus potentially drag a lot more data around.

Have you considered replicating the Core tables to the Reporting environment (simple replication) with the MVs built against those replicated tables. The SQLs against core should be simpler and data volumes from Core to reporting should be smaller, and the complex MVs are managed in a single database.

Answer 2

If your transaction rate is not great as you say, I would look into decreasing your refresh rate. Many reporting systems use a 24 hour turn-around time for reporting services and the users are usually able to adjust. You could even see significant improvement by using a refresh rate somewhere between 1 hour and 24 hours.