Oracle ORA-07445 [evaopn3()+384] 錯誤分析

1.OS 和 DB 版本

Oracle Version： 11.2.0.2

Operation System：HP-UXItanium 11.31

2.Alert log中資訊

Tue Oct 16 22:27:31 2012

Exception [type: SIGSEGV,Address not mapped to object] [ADDR:0xC00000000] [PC:0x400000000631B880,evaopn3()+384] [flags: 0x0, count: 1]

Errors in file/oracle/app/oracle/diag/rdbms/wzpartdb/wzpartdb/trace/wzpartdb_j004_19591.trc  (incident=56673):

ORA-07445: exceptionencountered: core dump [evaopn3()+384] [SIGSEGV] [ADDR:0xC00000000][PC:0x400000000631B880] [Address not mapped to object] []

Incident details in:/oracle/app/oracle/diag/rdbms/wzpartdb/wzpartdb/incident/incdir_56673/wzpartdb_j004_19591_i56673.trc

Use ADRCI or Support Workbench to packagethe incident.

See Note 411.1 at My Oracle Support forerror and packaging details.

Tue Oct 16 22:27:56 2012

Dumping diagnostic data indirectory=[cdmp_20121016222756], requested by (instance=1, osid=19591 (J004)),summary=[incident=56673].

Tue Oct 16 22:27:58 2012

Sweep [inc][56673]: completed

Sweep [inc2][56673]: completed

3.問題定位

與ORA-7445 相關的錯誤多與bug相關，一般我們需要關注第一個參數的類型，這裡是evaopn3。 MOS上專門的文章介紹這個，參考：

ORA-7445[evaopn3] (Doc ID 860969.1)

這裡與我們的錯誤：

ORA-07445: exceptionencountered: core dump [evaopn3()+384] [SIGSEGV] [ADDR:0xC00000000][PC:0x400000000631B880] [Address not mapped to object] []

可能與encryptedcolumns, function-based index 和 Xquery 相關。 一些已知的可能的bug如下：

Bug 13369579 - dump on evaopn3 withfunction base index and ORDER BY (Doc ID 13369579.8)

Bug 12724375 - ORA-7445 [evaopn3] fromXQuery with GROUP BY (Doc ID 12724375.8)

Bug 12672969 - Assorted Dumps withaggregate expression in ORDER BY (Doc ID 12672969.8)

Bug 13817586 - Dump on evaopn3 withEncrypted Columns (Doc ID 13817586.8)

通過排除，我這裡是bug：12672969。

Bug 12672969 - Assorted Dumps withaggregate expression in ORDER BY (Doc ID 12672969.8)

查看trace，確定了某SQL在order by中使用了aggregation function。執行該SQL時就會觸發ORA-7445的錯誤。

可以通過如下方式驗證：

create table test1 ( col1 number, col2number );

 insert into test1 values( 1, 1 );

 commit;

 select count(*)

 from(select col2

      from test1

      group by col2 order by (sum(col2)+1));

Rediscovery Notes:

 1.ORA-7445 in aggregation evaluation function including evaopn3

 2.Order by elimination is performed

 3. Set function exists in the middle of theoperator tree in order by

    clause

 4.The fix for bug 8772028 is present

4.解決方案

MOS 上解決方案是修改參數：

alter sessionset "_optimizer_order_by_elimination_enabled"=false;

但是這個是個隱含參數，修改可能會引起其他的問題，Oracle 爭對這個bug 提供了Patch：12672969.

可以在應用Patch 之後，在執行觸發bug的SQL，確認bug 修複情況。