Oracle ORA-03113 和 ORA-07445 ACCESS_VIOLATION

Oracle ORA-03113 and ORA-07445 ACCESS_VIOLATION

提问by Peter Goras

Environment

环境

• Oracle 10g
• Windows 2003 server
• IIS 6
• .NET 3.5
• Oracle client 10.2.0.1.0
• ODAC 10.2.0.2.21

• odp.net 2.102.2.20

• Validate Connection=true;Min Pool Size=0in connection string

• All odp.net connection, command and parameter objects are closed/disposed after use.

• 甲骨文 10g
• 视窗 2003 服务器
• IIS 6
• .NET 3.5
• Oracle 客户端 10.2.0.1.0
• ODAC 10.2.0.2.21

• odp.net 2.102.2.20

• Validate Connection=true;Min Pool Size=0in connection string

• 所有 odp.net 连接、命令和参数对象在使用后都会关闭/处置。

The issue:

问题：

1. ORA-03113 is returned to asp.net.
2. ORA-07445 ACCESS_VIOLATION is logged on database side. (see below)
3. It is ALWAYS the same select query (in stored procedure).
4. This is NOT a heavy query. Whole table is only 20K rows.
5. The rest of web application is UNAFFECTED. oracle connection and queries working normally.
6. Memory and threads used w3wp.exe are normal

1. ORA-03113 返回到 asp.net。
2. ORA-07445 ACCESS_VIOLATION 记录在数据库端。（见下文）
3. 它总是相同的选择查询（在存储过程中）。
4. 这不是一个繁重的查询。整个表只有 20K 行。
5. Web 应用程序的其余部分不受影响。oracle 连接和查询工作正常。
6. w3wp.exe使用的内存和线程都正常

Our only solution is for DBA to re-allocate table on db server. DBA says this is application issue, I am not so sure but...

我们唯一的解决方案是让 DBA 在 db 服务器上重新分配表。DBA 说这是应用程序问题，我不太确定，但是...

I have read all related posts on SObut please any advice is welcome!

我已阅读有关 SO 的所有相关帖子，但欢迎提出任何建议！

Thanks, P

谢谢，P

ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [evaopn2+2896] [PC:0x15F3876] [ADDR:0x0] [UNABLE_TO_READ] []

ORA-07445：遇到异常：核心转储 [ACCESS_VIOLATION] [evaopn2+2896] [PC:0x15F3876] [ADDR:0x0] [UNABLE_TO_READ] []

SELECT ILRS.ILRS_ID,
  ILRS.EXT_IDENTIFIER RUN_SET_EXTERNAL_IDENTIFIER,
  ILRS.DESCRIPTION RUN_SET_DESCRIPTION,
  ISST.CODE IIL_RUN_SET_STATUS_CODE,
  ILRN.ILRN_ID,
  ILRN.EXT_IDENTIFIER RUN_EXTERNAL_IDENTIFIER,
  ILRN.RUN_DATE,
  IRTY.CODE IIL_RUN_TYPE_CODE,
  PDCT.CODE PRODUCT_CODE,
  ILRN.STOCHASTIC_SCENARIOS STOCHASTIC_SCENARIOS,
  ILRN.PRIORITY PRIORITY,
  ILRN.DESCRIPTION RUN_DESCRIPTION,
  IRLB.CODE IIL_RUN_LABEL_CODE,
  IRST.CODE IIL_RUN_STATUS_CODE,
  ILRN.ACTIVE,
  UPDATE_USER.FIRST_NAME || ' ' || UPDATE_USER.SURNAME UPDATE_USER,
  ILRN.LAST_UPDATED,
  IRSV.TRANS_FROM STATUS_LAST_UPDATED
FROM IIL_RUN_SETS ILRS
INNER JOIN IIL_RN_SET_STA_VALS ISSV ON ILRS.ILRS_ID = ISSV.ILRS_ID
                   AND CURRENT_TIMESTAMP BETWEEN ISSV.TRANS_FROM AND ISSV.TRANS_TO
INNER JOIN IIL_RN_SET_STATUSES ISST ON ISSV.ISST_ID = ISST.ISST_ID
INNER JOIN IIL_RUNS ILRN            ON ILRS.ILRS_ID = ILRN.ILRS_ID
LEFT OUTER JOIN IIL_RUN_LABELS IRLB ON ILRN.IRLB_ID = IRLB.IRLB_ID
INNER JOIN IIL_RUN_STA_VALS IRSV    ON ILRN.ILRN_ID = IRSV.ILRN_ID
                  AND CURRENT_TIMESTAMP BETWEEN IRSV.TRANS_FROM AND IRSV.TRANS_TO
INNER JOIN IIL_RUN_STATUSES IRST    ON IRSV.IRST_ID = IRST.IRST_ID
INNER JOIN IIL_RUN_TYPES IRTY       ON ILRN.IRTY_ID = IRTY.IRTY_ID
INNER JOIN PRODUCTS PDCT            ON ILRN.PDCT_ID = PDCT.PDCT_ID
INNER JOIN USERS UPDATE_USER        ON ILRN.UPDATE_USER_ID = UPDATE_USER.USER_ID
WHERE ILRS.ILRS_ID     = :B1
ORDER BY ILRN.ILRN_ID

回答by Jon Heller

I don't think you can ever call something like this an "application issue". You'll probably need to convince the DBA that this is a database issue so he can talk to Oracle support, or at least look it up on Metalink or something. You may have scared him off when you started talking about IIS, .NET, etc. See if you can reproduce the error only in SQL*Plus, that should get his attention.

我认为您永远不能将这样的事情称为“应用程序问题”。您可能需要说服 DBA 这是一个数据库问题，以便他可以与 Oracle 支持人员交谈，或者至少在 Metalink 上查找它。当您开始谈论 IIS、.NET 等时，您可能把他吓跑了。看看您是否只能在 SQL*Plus 中重现错误，这应该引起他的注意。

As for actually solving the problem, you'll probably either need to patch, upgrade, or find some way to avoid the issue. Try rewriting your query in some trivial way and it may work. I usually end up working around these issues instead of actually solving them.

至于实际解决问题，您可能需要修补、升级或找到某种方法来避免该问题。尝试以某种简单的方式重写您的查询，它可能会奏效。我通常最终会解决这些问题，而不是实际解决它们。

回答by Peter Goras

We were able to reproduce the same using SQL Developer and so found the issue.

我们能够使用 SQL Developer 重现相同的内容，因此发现了问题。

...INNER JOIN IIL_RUN_STA_VALS IRSV ON ILRN.ILRN_ID = IRSV.ILRN_ID AND CURRENT_TIMESTAMP BETWEEN IRSV.TRANS_FROM AND IRSV.TRANS_TO

...INNER JOIN IIL_RUN_STA_VALS IRSV ON ILRN.ILRN_ID = IRSV.ILRN_ID 和 IRSV.TRANS_FROM 和 IRSV.TRANS_TO 之间的 CURRENT_TIMESTAMP

this line is the culprit. There was an index on the two date columns, TRANS_FROM and TRANS_TO. We droppped this. It worked.

这条线是罪魁祸首。在两个日期列 TRANS_FROM 和 TRANS_TO 上有一个索引。我们放弃了这个。有效。

After a bit more investigation we found that with few rows in the join, the index was not used but as the number of rows in the join increased, the query plan would change and the offending index would be used. This explained why it was an intermittent problem.

经过更多的调查，我们发现连接中的行很少，索引没有被使用，但是随着连接中行数的增加，查询计划会改变，并且会使用有问题的索引。这解释了为什么这是一个间歇性问题。

But clearly a suboptimal query plan shouldn't cause Oracle to die with an ORA-07445... Will log with Oracle support.

但显然，次优查询计划不应该导致 Oracle 因 ORA-07445 而死亡......将在 Oracle 支持下记录。

回答by Christian Shay

An error like this is probably a bug and Oracle support can look up the codes and tell you where the fix is. It goes without saying you should keep up with your patches anyway.

像这样的错误可能是一个错误，Oracle 支持人员可以查找代码并告诉您修复的位置。不用说，无论如何你都应该跟上你的补丁。