执行并行查询时碰到的问题。
错误信息如下：
Wed Sep 28 18:03:47 2011 Errors IN file /home/oracle/admin/ARIC/bdump/aric_p001_8093.trc: ORA-07445: exception encountered: core dump [SIGSEGV] [Address NOT mapped TO object] [21] [] [] [] Wed Sep 28 18:03:47 2011 Errors IN file /home/oracle/admin/ARIC/bdump/aric_p000_8091.trc: ORA-07445: exception encountered: core dump [SIGSEGV] [Address NOT mapped TO object] [21] [] [] [] |
对应的TRACE文件信息如下：
*** 2011-09-28 18:03:47.537 *** SERVICE NAME:(SYS$USERS) 2011-09-28 18:03:47.530 *** SESSION ID:(82.818) 2011-09-28 18:03:47.530 Exception signal: 11 (SIGSEGV), code: 1 (Address NOT mapped TO object), addr: 0x15 *** 2011-09-28 18:03:47.537 ksedmp: internal OR fatal error ORA-07445: exception encountered: core dump [SIGSEGV] [Address NOT mapped TO object] [21] [] [] [] CURRENT SQL statement FOR this SESSION: SELECT /*+parallel (D_ITEMS_SCD,2)*/ ( D_STR.ID_STR )||' / '||( D_STR.DS_STR_CN ), . . . D_STR_ITEMS_CUR.LAST_RECPT_DATE FROM D_STR, D_DIV_SCD, D_SEC_SCD, D_GRP_SCD, D_SGR_SCD, D_ITEMS_SCD, D_STR_ITEMS_CUR WHERE ( D_DIV_SCD.SID_DIV_H=D_SEC_SCD.SID_DIV_H ) . . . ) ----- Call Stack Trace ----- calling CALL entry argument VALUES IN hex location TYPE point (? means dubious VALUE) -------------------- -------- -------------------- ---------------------------- ksedst()+23 ? 0000000000000001 0017B341C 000000000 0062E5A60 000000000 ksedmp()+636 ? 0000000000000001 0017B1EB1 000000000 00000000B 000000000 ssexhd()+729 ? 0000000000000001 000E90E7E 000000000 0062E5B90 000000000 sigsetjmp()+25 ? 0000000000000001 0FC7C00B6 0FFFFFD7F 0062E5B50 000000000 call_user_handler() ? 0000000000000001 0FC7B53A2 0FFFFFD7F 0062E5EF0 +589 000000000 sigacthandler()+163 ? 0000000000000001 0FC7B5588 0FFFFFD7F 0FDDFB2F0 0FFFFFD7F qerixGetKey()+922 ? 0000000000000001 0FFFFFFFF 0FFFFFFFF 00000000B 000000000 qerbxStart()+1858 ? 0000000000000001 0033E49D7 000000000 0FC5A2930 0FFFFFD7F qerbtStart()+800 ? 0000000000000001 0033DE2BD 000000000 005414760 000000000 qergiStart()+833 ? 0000000000000001 00347718E 000000000 0060B9478 000000000 qerjoFetch()+668 ? 0000000000000001 0033BE169 000000000 000007FFF 000000000 qerpfFetch()+484 ? 0000000000000001 00347F491 000000000 088439128 000000003 qertbFetchByRowID() ? 0000000000000001 0033C4451 000000000 088439128 +660 000000003 . . . ============ Plan TABLE ============ ---------------------------------------------------------------------------+---------------- | Id | Operation | Name | ROWS | Cost | ---------------------------------------------------------------------------+---------------- | 0 | SELECT STATEMENT | | | 68K | | 1 | PX COORDINATOR | | | | | 2 | PX SEND QC (RANDOM) | :TQ10003 | 2497 | 68K | | 3 | HASH JOIN | | 2497 | 68K | | 4 | BUFFER SORT | | | | | 5 | PX RECEIVE | | 1 | 4 | | 6 | PX SEND BROADCAST | :TQ10000 | 1 | 4 | | 7 | TABLE ACCESS FULL | D_STR | 1 | 4 | | 8 | HASH JOIN | | 236K | 68K | | 9 | TABLE ACCESS BY INDEX ROWID | D_ITEMS_SCD | 1377 | 10K | | 10 | NESTED LOOPS | | 42K | 10K | | 11 | BUFFER SORT | | | | | 12 | PX RECEIVE | | | | | 13 | PX SEND BROADCAST | :TQ10001 | | | | 14 | HASH JOIN | | 32 | 20 | | 15 | HASH JOIN | | 4 | 10 | | 16 | MERGE JOIN | | 1 | 6 | | 17 | TABLE ACCESS BY INDEX ROWID | D_DIV_SCD | 1 | 2 | | 18 | INDEX FULL SCAN | PK_D_DIV_SCD | 8 | 1 | | 19 | SORT JOIN | | 52 | 4 | | 20 | TABLE ACCESS FULL | D_SEC_SCD | 52 | 3 | | 21 | TABLE ACCESS FULL | D_GRP_SCD | 489 | 3 | | 22 | TABLE ACCESS FULL | D_SGR_SCD | 3752 | 10 | | 23 | PX BLOCK ITERATOR | | | | | 24 | BITMAP CONVERSION TO ROWIDS | | | | | 25 | BITMAP INDEX SINGLE VALUE | BIX_D_ITEMS_SCD_SID_SGR_H| | | | 26 | BUFFER SORT | | | | | 27 | PX RECEIVE | | 9548K | 58K | | 28 | PX SEND BROADCAST | :TQ10002 | 9548K | 58K | | 29 | TABLE ACCESS FULL | D_STR_ITEMS_CUR | 9548K | 58K | ---------------------------------------------------------------------------+---------------- |
这里去掉了一些不必要的内容。从SQL语句中的HINT以及执行计划都可以看到，这是一个平行SQL，当然报错进程p000和p001也说明了这一点。
查询MOS发现是Bug 5113934 – PQ slave dump in qerixGetKey() when using bitmap index access [ID 5113934.8]，而当前的执行计划中确实出现了BITMAP索引。
这个bug确认影响10.2.0.3，而这正是当前的版本。这个bug在10.2.0.4和11.1.0.6中被fixed。除了打补丁外，禁止并行或星形查询同样可以避免这个错误，不过无论禁止并行还是星形查询，都会牺牲一定的性能。