Oracle 查询在 .NET 应用程序中运行缓慢（或失败），但在 SQL Developer 中运行很快
我们使用ODP.NET对Oracle数据库进行查询，正常情况下运行良好。但是，有一个特定的数据库以及该数据库中的一个特定视图，我们无法从 .NET 完成查询。例如：
SELECT some_varchar_field FROM the_view WHERE ROWNUM < 5;
如果我从 Oracle SQL Developer 中执行此查询，它会在不到一秒的时间内完成。如果我使用 ODP.NET 从 .NET 应用程序执行相同的查询，它会挂起并最终产生“ORA-03135：连接丢失联系”错误。我认为将其限制为几行可以消除 FetchSize 问题的可能性。
我可以成功执行其他查询，但它们在我们的程序中比在 SQL Developer 中慢。同样，我意识到 SQL Developer 最初只获取前 50 行的数据，但我认为 ROWNUM 条件将其排除在等式之外。
Oracle SQL Developer 使用的连接或命令与我们的应用程序使用的连接或命令可能有什么不同，从而导致速度差异？
不幸的是，我无权访问该服务器（除了对其运行 Oracle 查询之外）。
谢谢。
更新：我已经尝试使用 Microsoft 的 Oracle 提供商执行相同的查询，它执行得非常快。不幸的是，该提供商已被弃用，因此这不是一个长期解决方案。
We use ODP.NET to perform queries on Oracle databases, and normally it works fine. There is a particular database, and a particular view in that database, though, that we just can't complete a query on from .NET. For example:
SELECT some_varchar_field FROM the_view WHERE ROWNUM < 5;
If I execute this query from within Oracle SQL developer, it finishes in less than a second. If I do an identical query from our .NET application using ODP.NET, it hangs and eventually produces an "ORA-03135: connection lost contact" error. I think that limiting it to just a few rows eliminates the possibility that it is as FetchSize issue.
There are other queries I can execute successfully, but they are slower from our program than from SQL Developer. Again, I realize SQL Developer only gets data for the first 50 rows initially, but I think the ROWNUM condition takes that out of the equation.
What might be different about the connection or command that Oracle SQL Developer is using vs the one our application is using that would cause a difference in speed?
Unfortunately, I do not have access to the server (other than to run Oracle queries against it).
Thank you.
UPDATE: I have tried the same query with Microsoft's Oracle provider and it executes very quickly. Unfortunately, that provider is deprecated so this is not a long term solution.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
它与 ODP.NET 提供商无关。问题在于，我们用来为我们创建连接的库（当然，Oracle SQL Developer 不使用该库，而且我在尝试 Microsoft 提供程序时也没有使用该库）总是在执行任何操作之前执行以下语句：
这些使得 Oracle 不区分大小写。但是，它们也使所有传统索引变得毫无用处。因为我们是从视图查询，所以它内置了排序。而且因为我们不拥有数据库，所以我们无法使索引语言化来解决性能问题。
提供一种在这种（罕见）情况下不执行这些语句的方法解决了问题。
It had nothing to do with the ODP.NET provider. The problem was that the library we use to create connections for us (which, of course, is not used by Oracle SQL Developer, and which I did not use when I tried the Microsoft provider) was always executing the following statements before doing anything:
These make Oracle case-insensitive. But, they also render all conventional indexes useless. Because we were querying from a View, it had ordering built in. And because we don't own the database, we can't make the indexes linguistic to fix the performance problem.
Providing a way to not execute those statements in this (rare) scenario fixed the problem.
我立即想到的是
建议
Immediate thoughts are
Suggestions
视图增加了不同程度的复杂性。
“SELECT column FROM table WHERE rownum < 5”可能只有一个解释计划，从单个本地对象中选取数据。
对于视图，您应该首先获取视图文本
SELECT TEXT FROM ALL_VIEWS WHERE VIEW_NAME = ...ODP.NET 和 SQL Developer 会话之间有很多不同之处。我会考虑 NLS 参数（例如日期格式）和字符集设置。
如果您可以在 v$sql 中找到 SQL，则可以执行 DBMS_XPLAN.DISPLAY_CURSOR(sql_id) 来查看不同的计划并查看是否可以识别问题。
A view adds a different magnitude of complexity.
A "SELECT column FROM table WHERE rownum < 5" has probably just a single explain plan, picking data from a single local object.
For a view you should start by getting the view text
SELECT TEXT FROM ALL_VIEWS WHERE VIEW_NAME = ...There's a lot that can be different between an ODP.NET and an SQL Developer session. I'd think about NLS parameters (such as date formats) and character set settings.
If you can locate the SQL in v$sql, you can do a DBMS_XPLAN.DISPLAY_CURSOR(sql_id) to look at the different plans and see if you can identify the problem.
在我在前雇主从事的一个项目中，我们使用 odp.net 与大型零售系统数据库进行通信，并且我们会遇到连接丢失错误。
花了很多精力来证明，但它最终成为 Oracle 数据库中的一个损坏的索引，仅被我们的查询命中。 DBA 最终追踪到执行我们的查询时在 Sun 机器上运行的进程的核心转储。我们没有使用任何类型的查询提示等，但是当我们在 Toad 中运行相同的查询时，它没有命中这个特定的索引。奇怪？？<<
On a project I was working on at my former employer, we were using odp.net to talk to a large retailing system database and we'd get connection lost errors.
It took a lot of effort to prove, but it ended up being a corrupt index inside the Oracle database that was only being hit by our query. The DBA's eventually traced it to a coredump of the process that run on the Sun box when our query was being executed. We didn't use any sort of query hinting etc, but when we ran the same query in Toad, it didn't hit this particular index. strange??<<