oracle 执行 sql 查询时出现 ORA-03113

ORA-03113 while executing a sql query

提问by Ravi Gupta

I have a 400 line sql query which is throwing exception withing 30 seconds

我有一个 400 行的 sql 查询，它在 30 秒内抛出异常

ORA-03113: end-of-file on communication channel

ORA-03113: 通信通道上的文件结束

Below are things to note:

以下是需要注意的事项：

1. I have set the timeout as 10 mins
2. There is one last condition when removed resolves this error.
3. This error came only recently when I analyzed indexes.

1. 我已将超时设置为 10 分钟
2. 移除时还有最后一个条件可以解决此错误。
3. 这个错误是最近我分析索引时才出现的。

The troubling condition is like this:

麻烦的情况是这样的：

AND UPPER (someMultiJoin.someColumn) LIKE UPPER ('%90936%')

So my assumption is that the query is getting terminated from the server side apparently because its identified as a resource hog.

所以我的假设是查询显然从服务器端终止，因为它被识别为资源猪。

Is my assumption appropriate ? How should I go about to fix this problem ?

我的假设合适吗？我应该如何解决这个问题？

EDIT:I tried to get the explain plan of faulty query but the explain plan query also gives me an ORA-03113 error. I understand that my query is not very performant but why should that be a reason for ORA-03113 error. I am trying to run the query from toad and there are no alert log or trace generated, my db version is Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

编辑：我试图获得错误查询的解释计划，但解释计划查询也给了我一个 ORA-03113 错误。我知道我的查询效率不高，但为什么这会成为 ORA-03113 错误的原因。我正在尝试从 toad 运行查询，但没有生成警报日志或跟踪，我的数据库版本是 Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

回答by Dave Costa

One possible cause of this error is a thread crash on the server side. Check whether the Oracle server has generated any trace files, or logged any errors in its alert log.

此错误的一个可能原因是服务器端的线程崩溃。检查 Oracle 服务器是否生成了任何跟踪文件，或在其警报日志中记录了任何错误。

You say that removing one condition from the query causes the problem to go away. How long does the query take to run without that condition? Have you checked the execution plans for both versions of the query to see if adding that condition is causing some inefficient plan to be chosen?

您说从查询中删除一个条件会导致问题消失。在没有该条件的情况下，查询需要多长时间运行？您是否检查了查询的两个版本的执行计划，以查看添加该条件是否会导致选择某些低效的计划？

回答by jlpp

I've had similar connection dropping issues with certain variations on a query. In my case connections dropped when using rownum under certain circumstances. It turned out to be a bug that had a workaround by adjusting a certain Oracle Database configuration setting. We went with a workaround until a patch could be installed. I wish I could remember more specifics or find an old email on this but I don't know that the specifics would help address your issue. I'm posting this just to say that you've probably encountered a bug and if you have access to Oracle's support site (support.oracle.com) you'll likely find that others have reported it.

我在查询的某些变体中遇到过类似的连接丢失问题。在我的情况下，在某些情况下使用 rownum 时连接断开。结果证明是一个错误，可以通过调整某个 Oracle 数据库配置设置来解决。我们采用了一种解决方法，直到可以安装补丁为止。我希望我能记住更多细节或找到有关此的旧电子邮件，但我不知道这些细节是否有助于解决您的问题。我发布这篇文章只是想说明您可能遇到了一个错误，如果您可以访问 Oracle 的支持站点 (support.oracle.com)，您可能会发现其他人已经报告了它。

Edit: I had a quick look at Oracle support. There are more than 1000 bugs related to ORA-03113 but I found one that may apply:

编辑：我快速浏览了 Oracle 支持。有 1000 多个与 ORA-03113 相关的错误，但我发现了一个可能适用：

Bug 5015257: QUERY FAILS WITH ORA-3113 AND COREDUMP WHEN QUERY_REWRITE_ENABLED='TRUE'

错误 5015257：当 QUERY_REWRITE_ENABLED='TRUE' 时，查询失败，ORA-3113 和 COREDUMP

To summarize:

总结一下：

• Identified in 9.2.0.6.0 and fixed in 10.2.0.1
• Running a particular query (not identified) causes ORA-03113
• Running explain on query does the same
• There is a core file in $ORACLE_HOME/dbs
• Workaround is to set QUERY_REWRITE_ENABLED to false: alter system set query_rewrite_enabled = FALSE;

• 在 9.2.0.6.0 中识别并在 10.2.0.1 中修复
• 运行特定查询（未识别）导致 ORA-03113
• 在查询上运行解释也一样
• $ORACLE_HOME/dbs 中有一个核心文件
• 解决方法是将 QUERY_REWRITE_ENABLED 设置为 false：alter system set query_rewrite_enabled = FALSE;

Another possibility:

另一种可能：

Bug 3659827: ORA-3113 FROM LONG RUNNING QUERY

错误 3659827：来自长时间运行的查询的 ORA-3113

• 9.2.0.5.0 through 10.2.0.0
• Problem: Customer has long running query that consistently produces ORA-3113 errros.
  On customers system they receive core.log files but do not receive any errors in the alert.log. On test system I used I receivded ORA-7445 errors.
• Workaround: set "_complex_view_merging"=false at session level or instance level.

• 9.2.0.5.0 到 10.2.0.0
• 问题：客户长时间运行的查询始终产生 ORA-3113 错误。
  在客户系统上，他们收到 core.log 文件，但未收到 alert.log 中的任何错误。在我使用的测试系统上，我收到了 ORA-7445 错误。
• 解决方法：在会话级别或实例级别设置“_complex_view_merging”=false。

回答by Dubas

You can safely remove the "UPPER" on both parts if you are using the likewith numbers (that are not case sensitive), this can reduce the query time to check the like sentence

如果您将like与数字一起使用（不区分大小写），则可以安全地删除两个部分的“UPPER” ，这可以减少检查like 句子的查询时间

AND UPPER (someMultiJoin.someColumn) LIKE UPPER ('%90936%')

Is equals to:

等于：

AND someMultiJoin.someColumn LIKE '%90936%'

Numbers are not affected by UPPER (and % is independent of character casing).

数字不受 UPPER 的影响（并且 % 与字符大小写无关）。

回答by Alex Poole

From the information so far it looks like an back-end crash, as Dave Costa suggested some time ago. Were you able to check the server logs?

正如 Dave Costa 前段时间所建议的那样，从目前的信息来看，这似乎是一次后端崩溃。你能检查服务器日志吗？

Can you get the plan with set autotrace traceonly explain? Does it happen from SQL*Plus locally, or only with a remote connection? Certainly sounds like an ORA-600 on the back-end could be the culprit, particularly if it's at parse time. The successful run taking longer than the failing one seems to rule out a network problem. I suspect it's failing quite quickly but the client is taking up to 30 seconds to give up on the dead connection, or the server is taking that long to write trace and core files.

你能得到这个计划set autotrace traceonly explain吗？它是通过本地 SQL*Plus 发生的，还是仅发生在远程连接上？当然听起来后端的 ORA-600 可能是罪魁祸首，尤其是在解析时。成功的运行时间比失败的运行时间长似乎排除了网络问题。我怀疑它很快就失败了，但是客户端最多需要 30 秒来放弃死连接，或者服务器花费那么长时间来编写跟踪和核心文件。

Which probably leaves you the option of patching (if you can find a relevant fix for the specific ORA-600 on Metalink) or upgrading the DB; or rewriting the query to avoid it. You may get some ideas for how to do that from Metalink if it's a known bug. If you're lucky it might be as simple as a hint, if the extra condition is having an unexpected impact on the plan. Is someMultiJoin.someColumnpart of an index that's used in the successful version? It's possible the UPPERis confusing it and you could persuade it back on to the successful plan by hinting it to use the index anyway, but that's obviously rather speculative.

这可能会让您选择修补（如果您可以在 Metalink 上找到特定 ORA-600 的相关修复程序）或升级数据库；或重写查询以避免它。如果这是一个已知的错误，您可能会从 Metalink 那里得到一些关于如何做到这一点的想法。如果你很幸运，它可能就像一个提示一样简单，如果额外的条件对计划产生了意想不到的影响。是someMultiJoin.someColumn成功版本中使用的索引的一部分吗？这可能UPPER会混淆它，您可以通过暗示它无论如何都使用索引来说服它回到成功的计划，但这显然是推测性的。

回答by andrem

This is often a bug in the Cost Based Optimizer with complex queries.

这通常是具有复杂查询的基于成本的优化器中的错误。

What you can try to do is to change the execution plan. E.g. use WITHto pull some subquerys out. Or use the SELECT /*+ RULE */ hint to prevent Oracle from using the CBO. Also dropping the statistics helps, because Oracle then uses another execution plan.

您可以尝试做的是更改执行计划。例如使用WITH拉出一些子查询。或者使用 SELECT /*+ RULE */ 提示来阻止 Oracle 使用 CBO。删除统计信息也有帮助，因为 Oracle 然后使用另一个执行计划。

If you can update the database, make a test installation of 9.2.0.8 and see if the error is gone there.

如果可以更新数据库，请进行 9.2.0.8 的测试安装，看看错误是否消失了。

Sometimes it helps to make a dump of the schema, drop everything in it and import the dump again.

有时它有助于转储模式，删除其中的所有内容并再次导入转储。

回答by Daniel

It means you have been disconnected. This not likely to be due to being a resource hog.

这意味着您已断开连接。这不太可能是因为是一个资源猪。

I have seen where the connection to the DB is running over a NAT and because there is no traffic it closes the tunnel and thus drops the connection. Generally if you use connection pooling you won't get this.

我已经看到与数据库的连接通过 NAT 运行的位置，并且由于没有流量，它关闭了隧道并因此断开了连接。通常，如果您使用连接池，您将不会得到这个。

回答by Bob Jarvis - Reinstate Monica

As @Daniel said, the network connection to the server is being broken. You might take a look at End-of-file on communication channelto see if it offers any useful suggestions.

正如@Daniel 所说，与服务器的网络连接正在中断。您可以查看通信频道上的文件结尾，看看它是否提供了任何有用的建议。

Share and enjoy.

分享和享受。