ORA-600(19004)错误

客户数据库出现ORA-600(19004)错误。
错误信息如下:

ORA-00600: internal error code, arguments: [19004], [], [], [], [], [], [], []

对应的详细TRACE为:

*** 2012-03-15 13:10:31.283
ksedmp: internal OR fatal error
ORA-00600: internal error code, arguments: [19004], [], [], [], [], [], [], []
CURRENT SQL statement FOR this SESSION:
SELECT DISTINCT loc.id AS col_0_0_, temp2_.id AS col_1_0_, bloc.routeIndex AS col_2_0_, loc.id AS id32_, loc.createdTime AS createdT2_32_, loc.CREATOR_ID AS CREATOR3_32_, loc.DOMAIN_ID AS DOMAIN4_32_, loc.lockVersion AS lockVers5_32_, loc.reMark AS reMark32_, loc.status_id AS status85_32_, loc.udf1 AS udf7_32_, loc.udf2 AS udf8_32_, loc.udf3 AS udf9_32_, loc.udf4 AS udf10_32_, loc.udf5 AS udf11_32_, loc.udf6 AS udf12_32_, loc.udf7 AS udf13_32_, loc.udf8 AS udf14_32_, loc.updatedTime AS updated15_32_, loc.UPDATOR_ID AS UPDATOR16_32_, loc.fixCreatedTime AS fixCrea17_32_, loc.fixCreatedTimeForDisp AS fixCrea18_32_, loc.fixExpiredTime AS fixExpi19_32_, loc.fixExpiredTimeForDisp AS fixExpi20_32_, loc.fixStatus_id AS fixStatus87_32_, loc.fixStatusForDisp AS fixStat21_32_, loc.lotAtt01 AS lotAtt22_32_, loc.lotAtt01ForDisp AS lotAtt23_32_, loc.lotAtt02 AS lotAtt24_32_, loc.lotAtt02ForDisp AS lotAtt25_32_, loc.lotAtt03 AS lotAtt26_32_, loc.lotAtt03ForDisp AS lotAtt27_32_, loc.lotAtt04 AS lotAtt28_32_, loc.lotAtt04ForDisp AS lotAtt29_32_, loc.lotAtt05 AS lotAtt30_32_, loc.lotAtt05ForDisp AS lotAtt31_32_, loc.lotAtt06 AS lotAtt32_32_, loc.lotAtt06ForDisp AS lotAtt33_32_, loc.lotAtt07 AS lotAtt34_32_, loc.lotAtt07ForDisp AS lotAtt35_32_, loc.lotAtt08 AS lotAtt36_32_, loc.lotAtt08ForDisp AS lotAtt37_32_, loc.lotAtt09 AS lotAtt38_32_, loc.lotAtt09ForDisp AS lotAtt39_32_, loc.lotAtt10 AS lotAtt40_32_, loc.lotAtt10ForDisp AS lotAtt41_32_, loc.lotAtt11 AS lotAtt42_32_, loc.lotAtt11ForDisp AS lotAtt43_32_, loc.lotAtt12 AS lotAtt44_32_, loc.lotAtt12ForDisp AS lotAtt45_32_, loc.lotHeader_id AS lotHeader89_32_, loc.asnTraceId AS asnTraceId32_, loc.cubicEach AS cubicEach32_, loc.cubicUom AS cubicUom32_, loc.customer_id AS customer86_32_, loc.grossWeightEach AS grossWe49_32_, loc.grossWeightUom AS grossWe50_32_, loc.inboundTime AS inbound51_32_, loc.location_id AS location83_32_, loc.manufacturer_id AS manufac88_32_, loc.netWeightEach AS netWeig52_32_, loc.netWeightUom AS netWeig53_32_, loc.org_id AS org82_32_, loc.packCode AS packCode32_, loc.priceEach AS priceEach32_, loc.priceUom AS priceUom32_, loc.qtyAllocatedEach AS qtyAllo57_32_, loc.qtyAllocatedUom AS qtyAllo58_32_, loc.qtyConfirmEach AS qtyConf59_32_, loc.qtyConfirmUom AS qtyConf60_32_, loc.qtyEach AS qtyEach32_, loc.qtyHold4PaEach AS qtyHold62_32_, loc.qtyHold4PaUom AS qtyHold63_32_, loc.qtyLendEach AS qtyLend64_32_, loc.qtyLendUom AS qtyLendUom32_, loc.qtyLockEach AS qtyLock66_32_, loc.qtyLockUom AS qtyLockUom32_, loc.qtyMendEach AS qtyMend68_32_, loc.qtyMendUom AS qtyMendUom32_, loc.qtyOnHoldEach AS qtyOnHo70_32_, loc.qtyOnHoldUom AS qtyOnHo71_32_, loc.qtyPickedEach AS qtyPick72_32_, loc.qtyPickedUom AS qtyPick73_32_, loc.qtyTempEach AS qtyTemp74_32_, loc.qtyTempUom AS qtyTempUom32_, loc.qtyUom AS qtyUom32_, loc.qtyUseEach AS qtyUseEach32_, loc.qtyUseUom AS qtyUseUom32_, loc.sku_id AS sku84_32_, loc.traceCode AS traceCode32_, loc.uom_id AS uom80_32_, loc.warehouse_id AS warehouse81_32_ FROM LOC loc, BLOC bloc, OD od, TEMP temp2_ WHERE loc.location_id=bloc.id AND od.id=temp2_.id AND loc.lotHeader_id=od.lotHeader_id AND (od.fixCreatedTime IS NULL OR od.fixCreatedTime=loc.fixCreatedTime) AND (od.fixExpiredTime IS NULL OR od.fixExpiredTime=loc.fixExpiredTime) AND (od.fixStatus_id IS NULL OR od.fixStatus_id=loc.fixStatus_id) AND (od.lotAtt01 IS NULL OR od.lotAtt01=loc.lotAtt01) AND (od.lotAtt02 IS NULL OR od.lotAtt02=loc.lotAtt02) AND (od.lotAtt03 IS NULL OR od.lotAtt03=loc.lotAtt03) AND (od.lotAtt04 IS NULL OR od.lotAtt04=loc.lotAtt04) AND (od.lotAtt05 IS NULL OR od.lotAtt05=loc.lotAtt05) AND (od.lotAtt06 IS NULL OR od.lotAtt06=loc.lotAtt06) AND (od.lotAtt07 IS NULL OR od.lotAtt07=loc.lotAtt07) AND (od.lotAtt08 IS NULL OR od.lotAtt08=loc.lotAtt08) AND (od.lotAtt09 IS NULL OR od.lotAtt09=loc.lotAtt09) AND (od.lotAtt10 IS NULL OR od.lotAtt10=loc.lotAtt10) AND (od.lotAtt11 IS NULL OR od.lotAtt11=loc.lotAtt11) AND (od.lotAtt12 IS NULL OR od.lotAtt12=loc.lotAtt12) AND CASE WHEN od.picLocation_id IS NULL THEN loc.location_id ELSE od.picLocation_id END=loc.location_id AND od.sku_id=loc.sku_id AND loc.uom_id=od.uom_id AND loc.warehouse_id=od.wh_id AND loc.customer_id=od.customer_id AND loc.qtyUseUom>0 ORDER BY loc.qtyUseUom DESC, bloc.routeIndex ASC, loc.id ASC
----- Call Stack Trace -----
calling              CALL     entry                argument VALUES IN hex      
location             TYPE     point                (? means dubious VALUE)     
-------------------- -------- -------------------- ----------------------------
ksedst()+31          CALL     ksedst1()            000000000 ? 000000001 ?
                                                   7FBFFF0300 ? 7FBFFF0360 ?
                                                   7FBFFF02A0 ? 000000000 ?
ksedmp()+610         CALL     ksedst()             000000000 ? 000000001 ?
                                                   7FBFFF0300 ? 7FBFFF0360 ?
                                                   7FBFFF02A0 ? 000000000 ?
ksfdmp()+21          CALL     ksedmp()             000000003 ? 000000001 ?
                                                   7FBFFF0300 ? 7FBFFF0360 ?
                                                   7FBFFF02A0 ? 000000000 ?
kgeriv()+176         CALL     ksfdmp()             000000003 ? 000000001 ?
                                                   7FBFFF0300 ? 7FBFFF0360 ?
                                                   7FBFFF02A0 ? 000000000 ?
kgesiv()+119         CALL     kgeriv()             0066876E0 ? 00673CEE0 ?
                                                   000000000 ? 000000000 ?
                                                   7FBFFF02A0 ? 000000000 ?
ksesic0()+209        CALL     kgesiv()             0066876E0 ? 00673CEE0 ?
                                                   000004A3C ? 000000000 ?
                                                   7FBFFF1080 ? 000000000 ?
kkejeq()+5152        CALL     ksesic0()            000004A3C ? 2A97403DB0 ?
                                                   0000000CA ? 2A97403DB0 ?
                                                   000000002 ? 000000000 ?
kkeEqJoinSel()+2602  CALL     kkejeq()             2A973C2930 ? 2A97403DB0 ?
                                                   000000000 ? 2A97403DB0 ?
                                                   000000002 ? 000000000 ?
kkepsl()+10561       CALL     kkeEqJoinSel()       7FBFFF1BB0 ? 7FBFFF1BD0 ?
                                                   7FBFFF1BC0 ? 7FBFFF1BE0 ?
                                                   2A971E68F8 ? 2A00000002 ?
kkeasl()+2125        CALL     kkepsl()             2A971E68F8 ? 7FBFFF2438 ?
                                                   000000002 ? 2A971EFB30 ?
                                                   2A971E68F8 ? 2A00000002 ?
kkeosl()+1447        CALL     kkeasl()             2A971E6970 ? 7FBFFF3DB8 ?
                                                   000000002 ? 000000000 ?
                                                   000000000 ? 2A971EFB30 ?
kkejcd()+1466        CALL     kkeosl()             2A971E6BF0 ? 7FBFFF4010 ?
                                                   000000002 ? 000000000 ?
                                                   000000000 ? 2A971EFB30 ?
kkojnp()+3920        CALL     kkejcd()             2A973CF670 ? 2A973D3628 ?
                                                   2A974407E8 ? 000000016 ?
                                                   2A973D5008 ? 000000003 ?
kkocnp()+166         CALL     kkojnp()             2A973D5008 ? 000000000 ?
                                                   2A974407E8 ? 000000016 ?
                                                   2A973D5008 ? 000000000 ?
kkooqb()+2258        CALL     kkocnp()             2A973D5008 ? 000000001 ?
                                                   2A974407E8 ? 000000016 ?
                                                   2A973D5008 ? 000000000 ?
kkoqbc()+2083        CALL     kkooqb()             000000000 ? 000000001 ?
                                                   0000007D0 ? 000000004 ?
                                                   2A00000000 ? 2A00000006 ?
apakkoqb()+170       CALL     kkoqbc()             7FBFFF7D80 ? 2A97393428 ?
                                                   0000007D0 ? 000000001 ?
                                                   2A00000000 ? 000000000 ?
apaqbdDescendents()  CALL     apakkoqb()           7FBFFF7D80 ? 2A97393428 ?
+399                                               1D23E4740 ? 000000001 ?
                                                   2A00000000 ? 000000000 ?
apaqbdListReverse()  CALL     apaqbdDescendents()  7FBFFF7D80 ? 2A97393428 ?
+68                                                1D23E4740 ? 000000001 ?
                                                   2A00000000 ? 000000000 ?
apadrv()+586         CALL     apaqbdListReverse()  7FBFFF7D80 ? 2A97393428 ?
                                                   1D23E4740 ? 000000001 ?
                                                   2A00000000 ? 000000000 ?
opitca()+1512        CALL     apadrv()             1D23E4740 ? 2A97393428 ?
                                                   1D23E4740 ? 000000001 ?
                                                   2A00000000 ? 000000000 ?
kksFullTypeCheck()+  CALL     opitca()             2A972335A8 ? 1D23E4740 ?
30                                                 1D23E4740 ? 000000001 ?
                                                   2A00000000 ? 000000000 ?
rpiswu2()+420        CALL     kksFullTypeCheck()   7FBFFF9328 ? 1D23E4740 ?
                                                   1D23E4740 ? 000000001 ?
                                                   2A00000000 ? 000000000 ?
kksLoadChild()+9500  CALL     rpiswu2()            1F736B950 ? 00000003A ?
                                                   1DFC84F24 ? 000000009 ?
                                                   1DFAD88E0 ? 10000003A ?
kxsGetRuntimeLock()  CALL     kksLoadChild()       0066876E0 ? 1FAB8C5C0 ?
+1353                                              7FBFFFA7C0 ? 000000000 ?
                                                   1DFC84DC0 ? 2A972335A8 ?
kksfbc()+15084       CALL     kxsGetRuntimeLock()  0066876E0 ? 2A972335A8 ?
                                                   7FBFFFA7C0 ? 000000000 ?
                                                   1DFC84DC0 ? 2A972335A8 ?
kkspsc0()+1548       CALL     kksfbc()             2A972335A8 ? 000000003 ?
                                                   000000108 ? 2A97203D18 ?
                                                   0000016A6 ? 000000000 ?
kksParseCursor()+14  CALL     kkspsc0()            2A97211788 ? 2A97203D18 ?
2                                                  0000016A6 ? 000000003 ?
                                                   E77100A400000006 ?
                                                   1E77100A4 ?
opiosq0()+1641       CALL     kksParseCursor()     7FBFFFB208 ? 2A97203D18 ?
                                                   0000016A6 ? 000000003 ?
                                                   E77100A400000006 ?
                                                   1E77100A4 ?
kpooprx()+315        CALL     opiosq0()            000000003 ? 00000000E ?
                                                   7FBFFFB3F8 ? 0000000A4 ?
                                                   E77100A400000006 ?
                                                   1E77100A4 ?
kpoal8()+799         CALL     kpooprx()            7FBFFFE5A4 ? 2A97203D18 ?
                                                   0000016A5 ? 000000001 ?
                                                   000000000 ? 1E77100A4 ?
opiodr()+984         CALL     kpoal8()             00000005E ? 000000017 ?
                                                   7FBFFFE5A0 ? 000000001 ?
                                                   000000001 ? 1E77100A4 ?
ttcpip()+1012        CALL     opiodr()             00000005E ? 000000017 ?
                                                   7FBFFFE5A0 ? 000000000 ?
                                                   0059B1310 ? 1E77100A4 ?
opitsk()+1322        CALL     ttcpip()             00668F3B0 ? 000000003 ?
                                                   7FBFFFE5A0 ? 000000000 ?
                                                   7FBFFFE098 ? 7FBFFFE708 ?
opiino()+1026        CALL     opitsk()             000000003 ? 000000000 ?
                                                   7FBFFFE5A0 ? 000000001 ?
                                                   000000000 ? 67502D200000001 ?
opiodr()+984         CALL     opiino()             00000003C ? 000000004 ?
                                                   7FBFFFF768 ? 000000000 ?
                                                   000000000 ? 67502D200000001 ?
opidrv()+547         CALL     opiodr()             00000003C ? 000000004 ?
                                                   7FBFFFF768 ? 000000000 ?
                                                   0059B0DC0 ? 67502D200000001 ?
sou2o()+114          CALL     opidrv()             00000003C ? 000000004 ?
                                                   7FBFFFF768 ? 000000000 ?
                                                   0059B0DC0 ? 67502D200000001 ?
opimai_real()+163    CALL     sou2o()              7FBFFFF740 ? 00000003C ?
                                                   000000004 ? 7FBFFFF768 ?
                                                   0059B0DC0 ? 67502D200000001 ?
main()+116           CALL     opimai_real()        000000002 ? 7FBFFFF7D0 ?
                                                   000000004 ? 7FBFFFF768 ?
                                                   0059B0DC0 ? 67502D200000001 ?
__libc_start_main()  CALL     main()               000000002 ? 7FBFFFF7D0 ?
+219                                               000000004 ? 7FBFFFF768 ?
                                                   0059B0DC0 ? 67502D200000001 ?
_start()+42          CALL     __libc_start_main()  000713988 ? 000000002 ?
                                                   7FBFFFF918 ? 005288D70 ?
                                                   000000000 ? 000000002 ?
--------------------- Binary Stack Dump ---------------------

这个SQL语句很长,查询了大量的列,且使用了DISTINCT,而且关联条件显得十分复杂,所以出现ORA-600错误也不奇怪。
检查MOS发现,问题和Bug 9022470 – Query optimization fails with OERI[19004] [ID 9022470.8]比较类似,除了当前的10204版本符合这个bug影响的版本外,堆栈信息错误完全符合这个bug的报错信息,而且在这个查询中,其中一个列确实在连接查询中出现了多次,这也同样符合bug的描述。
这个错误影响的版本包括10.2.0.4、11.1.0.7和11.2.0.1,Oracle在11.2.0.2中修复了这个bug。
对于这个错误,如果无法升级解决,那么只能通过修改SQL语法,或者尝试降低OPTIMIER_FEATURES_ENABLE的值来解决。

This entry was posted in BUG and tagged , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *