CODEWITHSUNDEEP
sqlperformanceoracle-databasematerialized-views
Evan
Evan

Reputation: 101

Performance of Remote Materialized views in Oracle

I have a question reagrding Oracle materialized views ...

We have two databases:

  1. Core Database
  2. Reporting database

The reporting database has:

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

Upvotes: 0

Views: 1664

Answers (2)

user396181
user396181

Reputation: 1

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.

Upvotes: 0

Gary Myers
Gary Myers

Reputation: 35401

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.

Upvotes: 4

Related Questions