来自 .NET 应用程序的 Oracle 查询很慢（或失败），但来自 SQL Developer 的查询很快

Oracle query is slow (or fails) from .NET app but is fast from SQL Developer

提问by Greg Smalter

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:

我们使用 ODP.NET 对 Oracle 数据库执行查询，通常它工作正常。但是，有一个特定的数据库和该数据库中的特定视图，我们无法从 .NET 完成查询。例如：

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.

如果我从 Oracle SQL 开发人员中执行此查询，它会在不到一秒钟的时间内完成。如果我使用 ODP.NET 从我们的 .NET 应用程序执行相同的查询，它会挂起并最终产生“ORA-03135：连接丢失联系”错误。我认为将其限制为几行可以消除它作为 FetchSize 问题的可能性。

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.

我可以成功执行其他查询，但它们在我们的程序中比 SQL Developer 慢。同样，我意识到 SQL Developer 最初只获取前 50 行的数据，但我认为 ROWNUM 条件将其排除在外。

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?

Oracle SQL Developer 使用的连接或命令与我们的应用程序使用的会导致速度差异的连接或命令有何不同？

Unfortunately, I do not have access to the server (other than to run Oracle queries against it).

不幸的是，我无权访问服务器（除了针对它运行 Oracle 查询）。

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.

更新：我已尝试使用 Microsoft 的 Oracle 提供程序进行相同的查询，并且执行速度非常快。不幸的是，该提供程序已被弃用，因此这不是一个长期解决方案。

回答by Greg Smalter

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:

它与 ODP.NET 提供程序无关。问题是我们用来为我们创建连接的库（当然，Oracle SQL Developer 没有使用它，我在尝试 Microsoft 提供程序时也没有使用它）在做任何事情之前总是执行以下语句：

ALTER SESSION SET NLS_COMP = LINGUISTIC
ALTER SESSION SET NLS_SORT = BINARY_CI

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.

这些使 Oracle 不区分大小写。但是，它们也使所有常规索引无用。因为我们是从视图查询，它内置了排序。而且因为我们不拥有数据库，所以我们无法使索引语言化来解决性能问题。

Providing a way to not execute those statements in this (rare) scenario fixed the problem.

提供一种在此（罕见）场景中不执行这些语句的方法解决了该问题。

回答by Gary Myers

Immediate thoughts are

即时的想法是

1. CLOB, BLOB or LONG/LONG RAW which requires a lot of bandwidth for just a few rows.
2. Invalid data (eg there are ways to get an invalid date into a date field, which may confuse some clients)
3. "the_table" isn't actually a table but a view or something with a complex derivation or has a VPD/RLS/FGAC security policy on it.
4. Exotic datatype (Spatial or User Defined).

1. CLOB、BLOB 或 LONG/LONG RAW 仅几行就需要大量带宽。
2. 无效数据（例如，有一些方法可以将无效日期放入日期字段，这可能会使某些客户端感到困惑）
3. “the_table”实际上不是表，而是视图或具有复杂派生的东西，或者上面有 VPD/RLS/FGAC 安全策略。
4. 外来数据类型（空间或用户定义）。

Suggestions

建议

1. Explicitly list the columns (eg SELECT a,b,c FROM the_table WHERE ROWNUM < 5). Add columns one by one until it stops working. That assumes there is at least one 'simple' column in the table.
2. Check the session in v$session to see what the wait EVENT is. Either the DB server is burning CPU for this SQL, or it is waiting for something (possibly the client).
3. Check the SQL in v$sql. Is there one or more child cursors. is there one or more PLAN_HASH_VALUEs. Different child cursors can use different plans. Without a WHERE clause other than ROWNUM, this is pretty unlikely.

1. 显式列出列（例如 SELECT a,b,c FROM the_table WHERE ROWNUM < 5）。一一添加列，直到它停止工作。假设表中至少有一个“简单”列。
2. 检查 v$session 中的会话以查看等待事件是什么。数据库服务器正在为此 SQL 消耗 CPU，或者它正在等待某些东西（可能是客户端）。
3. 检查 v$sql 中的 SQL。是否有一个或多个子游标。是否有一个或多个 PLAN_HASH_VALUE。不同的子游标可以使用不同的计划。如果没有 ROWNUM 以外的 WHERE 子句，这是不太可能的。

回答by Gary Myers

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.

视图增加了不同程度的复杂性。“SELECT column FROM table WHERE rownum < 5”可能只有一个解释计划，从单个本地对象中挑选数据。

For a view you should start by getting the view text SELECT TEXT FROM ALL_VIEWS WHERE VIEW_NAME = ...

对于视图，您应该首先获取视图文本 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.

ODP.NET 和 SQL Developer 会话之间有很多不同之处。我会考虑 NLS 参数（例如日期格式）和字符集设置。

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.

如果你能在 v$sql 中找到 SQL，你可以做一个 DBMS_XPLAN.DISPLAY_CURSOR(sql_id) 来查看不同的计划，看看你是否能找出问题。

回答by Paul Farry

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.

在我为前雇主工作的一个项目中，我们使用 odp.net 与大型零售系统数据库进行通信，我们会遇到连接丢失错误。

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??<<

花了很多精力来证明，但它最终成为 Oracle 数据库中的一个损坏索引，仅被我们的查询命中。DBA 最终将其追踪到执行查询时在 Sun 机器上运行的进程的核心转储。我们没有使用任何类型的查询提示等，但是当我们在 Toad 中运行相同的查询时，它没有命中这个特定的索引。奇怪吗？？<<