Yet another Oracle 11g upgrade aftermath - ORA-07445: exception encountered: core dump [evaopn3()]

Apparently, the Oracle 11gR2 aftermath stories is like never ending story from my part. Yet another aftermath fairy tale for you.

It was brought to our notice a couple of days ago that one of the application query failed to execute of late (perhaps after 11gR2 upgrade) and the following error has been logged in the application log file:

Dynamic Query PreparedStatement SQLException: java.sql.SQLException: No more data to read from socket SQLState: null ErrorCode: 17410

During my initial investigation the following errors have been found in the database alert.log:

Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x1200000000] [PC:0x400000000631B880, evaopn3()+384] [flags: 0x0, coun
t: 1]
Errors in file /u00/app/oracle/diag/rdbms/pmsit/XXXXX/trace/XXXXX1_ora_23652.trc  (incident=9016):
ORA-07445: exception encountered: core dump [evaopn3()+384] [SIGSEGV] [ADDR:0x1200000000] [PC:0x400000000631B880] [Address not mappe
d to object] []
Incident details in: /u00/app/oracle/diag/rdbms/pmsit/XXXXX1/incident/incdir_9016/PMSIT1_ora_23652_i9016.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Mon May 30 11:13:54 2011
Dumping diagnostic data in directory=[cdmp_20110530111354], requested by (instance=1, osid=23652), summary=[incident=9016].
Mon May 30 11:14:10 2011
Sweep [inc][9016]: completed
Sweep [inc][8952]: completed
Sweep [inc][8680]: completed
Sweep [inc2][9016]: completed
Sweep [inc2][8952]: completed
Sweep [inc2][8680]: completed

Additionally, the following has been found in the trace file in the context:

DDE:Problem Key 'ORA 7445[evaopn3()+384]'was flood controlled(0x2)(incident: 8657)
ORA-07445: exception encountered: core dump [evaopn3()+384] [SIGSEGV] 

ORA-07445 error caught my attention and as a immediate fix or thumb of rule, perhaps, I have flushed the SHARED POOL and asked the person to execute the query again. Unfortunately, the issue persists. Since this is a development database, I took the permission from the concerning application team to re-start the database which also never worked in our favor.

After a little bit of research over the MOS (metalink), I found  a potential workaround  to set the initialization parameter _simple_view_merging value to false to get rid of the issue. This indeed did the trick and the statement (query) was able to run without any issues this time. Additionally, found a recommendation to turn of _complex_view_merging initialization parameter too.

Moving on further with my investigation, I used the ORA-07445 look up tool by providing the first argument of the error '[evaopn3()+384]'  that suggests me to have a look at MOS ORA-7445 (evaopn3) [ID 860969.1] and the following is the excerpt from the document:

ERROR:
  ORA-7445 failing function "evaopn3"

VERSIONS:
  versions 10.1 to 11.2

DESCRIPTION:

  ORA-7445 [evaopn3] generally occurs during the execution of a query.

  The error typically only occurs with a specific stream of data being
  fetched from the objects in the execution plan.

  The problem is caused often by an optimization but it is not until a
  row is fetched and evaluated that the error happens.

The error is largely because of  a few Oracle bugs (depends on the version of database). Nevertheless, the document indeed suggests a few strategies to cope up the error. In my case, the above solution worked perfectly, though not really aware of the consequence of disabling the feature. I need to monitor the database behavior for sometime to make the change permanent.

References:
http://www.freelists.org/post/oracle-l/ora3113-related-to-histograms-11gr2,1
MOS ORA-7445 (evaopn3) [ID 860969.1]
MOS Customer Introduction to ORA-7445 Errors [ID 211909.1]
 
Happy reading,

Jaffar