Oracle ORA-03113 and ORA-07445 ACCESS_VIOLATION

Question

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=0 in connection string

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

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

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

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

Thanks, P

ORA-07445: exception encountered: core dump [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

Answer 1

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.

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.

Answer 2

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

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

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

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.

Thanks all!