在包含远程数据库的Oracle上测试SQL查询 [英] Testing SQL query on Oracle which includes a remote database

问题描述

我们的开发数据库(Oracle 9i)使用到远程共享数据库的远程数据库链接.

Our development databases (Oracle 9i) use a remote database link to a remote shared database.

这项决定是在多年前做出的，当时将一些数据库模式放在开发机器上是不切实际的-它们太大了.

This decision was made years ago when it wasn't practical to put some of the database schemas on a development machine - they were too big.

我们在开发机器上有某些架构，并且通过使用Oracle的数据库链接以及在开发机器上的一些同义词，使远程架构看起来是本地的.

We have certain schemas on the development machines and we make the remote schemas look local by using Oracle's database links, together with some synonyms on the development machines.

我遇到的问题是，我想测试一段SQL，该SQL将数据库链接两侧的模式中的表联接在一起.

The problem I have is that I would like to test a piece of SQL which joins tables in schemas on either side of the database link.

例如(一种简化的情况):

e.g. (a simplified case):

   select a.col, b.col
   from a, b
   where a.b_id = b.id

• a在本地数据库上
• b在删除数据库上
• 我在语言环境数据库上有一个同义词，所以'b'实际上指向b @ remotedb.

由于存在链接，因此在开发环境中运行查询会花费很多时间.这些查询在生产中运行良好(我认为基于Oracle成本的优化器无法很好地应对数据库链接).

Running the query takes ages in the development environment because of the link. The queries run fine in production (I don't think the Oracle cost based optimiser can cope very well with database links).

过去，我们不太擅长为此类查询编写单元测试-可能是由于性能不佳所致-因此，我想开始为它们创建一些测试.

We have not been very good at writing unit tests for these types of queries in the past - probably due to the due to the poor performance - so I'd like to start creating some tests for them.

有人为编写这种查询而编写任何单元测试的策略，以避免使用数据库链接的性能问题吗?

Does anyone have any strategies for writing a unit test for such a query, so as to avoid the performance problems of using the database link?

我通常会研究尝试模拟远程服务的方法，但是由于所有这些都在SQL查询中，因此无论如何我都看不到容易模拟出删除数据库的方法.

I'd normally be looking at ways of trying to mock out remote service, but since all this is in a SQL query, I can't see anyway of easily mocking out the remove database.

推荐答案

您应该为开发中的生产创建所需的所有架构的精确副本，但不要包含所有数据.您应该用足够的数据填充架构，以便可以进行适当的测试.您还可以通过从生产服务器中导出统计信息并将其导入到要复制的模式的开发数据库中，来操作优化器使其在测试系统上的行为类似于生产系统.这样，查询将使用您创建的数据集运行，但查询将使用与生产计划相似的计划进行优化.然后，您可以从理论上估计其在生产中的规模.

You should create exact copies of all the schema you need from production on development but without all the data. You should populate the schema with enough data so you can do a proper test. You can also manipulate the optimizer to behave on the test system to be like production by exporting the statistics from the production server and importing them to the development database for the schemas you are duplicating. That way the query will run with the data set you've made but the query will optimize with plans that is similar to that of production. Then you can estimate theoretically how it will scale on production.

这篇关于在包含远程数据库的Oracle上测试SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！