Oracle 中远程物化视图的性能
我有一个有关 Oracle 物化视图的问题...
我们有两个数据库：
- 核心数据库
- 报告数据库
报告数据库具有：
- 到核心数据库的数据库链接
- 核心数据库中表的许多同义词
- 定义在其上的许多物化视图这些同义词的顶部。
视图设置为每小时刷新一次。
随着源系统中数据量的增加，我们发现用于具体化视图的 CPU 也有所增加。
经过仔细检查，视图刷新过程似乎在报告数据库中构建了结果集，并将单个较小的 SQL 语句发送到核心数据库。
其中一些物化视图非常复杂，并且表之间有很多联接。 这导致针对 Core 数据库产生数百万条小 SQL 语句。
我的问题是：在核心数据库中创建一个相应的“复杂”视图，并在报告数据库中创建一个物化视图，作为一个简单的“SELECT * FROM CORE.MY_MAT_VIEW”会更好吗？
感谢您的指点，
干杯， 埃文
I have a question reagrding Oracle materialized views ...
We have two databases:
- Core Database
- 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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我不会在核心数据库中有任何太复杂的东西。 您会给核心数据库带来更多负载，并且可能会拖曳更多数据。
您是否考虑过使用针对这些复制表构建的 MV 将核心表复制到报告环境（简单复制）。
针对核心的 SQL 应该更简单，从核心到报告的数据量应该更小，并且复杂的 MV 在单个数据库中进行管理。
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.
如果您的交易率不如您所说的那么好，我会考虑降低您的刷新率。 许多报告系统使用 24 小时的报告服务周转时间，用户通常可以进行调整。 通过使用 1 小时到 24 小时之间的刷新率，您甚至可以看到显着的改进。
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.