执行并行查询时碰到的问题。

错误信息如下：

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。除了打补丁外，禁止并行或星形查询同样可以避免这个错误，不过无论禁止并行还是星形查询，都会牺牲一定的性能。