在客户的10.2.0.4 RAC for X86-64环境中碰到了这个错误。
错误信息为:
Fri Apr 27 13:40:20 2012
Errors in file /opt/app/oracle/admin/ora/udump/ora1_ora_1851.trc:
ORA-07445: exception encountered: core dump [kkfipbr()+8] [SIGSEGV] [Address
not mapped to object] [0x000000000] [] []
对应的TRACE文件内容为:
*** 2012-04-27 13:40:20.422
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [kkfipbr()+8] [SIGSEGV] [Address
not mapped to object] [0x000000000] [] []
Current SQL statement for this session:
select * from (select pagetable.*,rownum recordid from (select s.id sid,p.id
pid,p.name pname, … where rownum <= 7) where recordid >0
----- Call Stack Trace -----
calling call entry argument values in hex
location type
point (? means
dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst()+31 call ksedst1() 000000000 ? 000000001 ?
2A97153D50 ? 2A97153DB0 ?
2A97153CF0 ? 000000000 ?
ksedmp()+610 call ksedst() 000000000 ? 000000001 ?
2A97153D50 ? 2A97153DB0 ?
2A97153CF0 ? 000000000 ?
ssexhd()+629 call ksedmp() 000000003 ? 000000001 ?
2A97153D50 ? 2A97153DB0 ?
2A97153CF0
? 000000000 ?
__funlockfile()+64 call ssexhd() 00000000B ? 2A97154D70 ?
2A97154C40 ? 2A97153DB0 ?
2A97153CF0 ? 000000000 ?
kkfipbr()+8 signal __funlockfile() 2A976A69B8 ? 000000000 ?
7FBFFF7C10 ? 000000038 ?
000000000 ? 000000000 ?
kkfiPatchQbc()+235 call kkfipbr() 2A976A69B8 ? 000000000 ?
7FBFFF7C10 ? 000000038 ?
000000000 ? 000000000 ?
qkadrv()+554 call kkfiPatchQbc() 2A976A69B8 ? 000000000 ?
7FBFFF7C10 ? 000000038 ?
000000000 ? 000000000 ?
qkadrv()+6219 call qkadrv() 2A972E9D08 ? 000000001 ?
7FBFFF7C10 ? 000000038 ?
000000000 ? 000000000 ?
opitca()+1875 call qkadrv() 2A972EAAB8 ? 000000001 ?
7FBFFF7C10 ? 000000038 ?
000000000 ? 000000000 ?
kksLoadChild()+9360 call opitca() 2A9779FF20 ? 319327A70 ?
7FBFFF7C10 ? 000000038 ?
000000000 ? 000000000 ?
kxsGetRuntimeLock() call kksLoadChild() 0067B4700 ? 259299DD8 ?
+1353 7FBFFFA4D0
? 000000000 ?
30DD70C10 ? 2A9779FF20 ?
kksfbc()+15084 call kxsGetRuntimeLock() 0067B4700 ? 2A9779FF20 ?
7FBFFFA4D0 ? 000000000 ?
30DD70C10 ? 2A9779FF20 ?
kkspsc0()+1548 call kksfbc() 2A9779FF20 ? 000000003 ?
000000108 ? 2A975D2188 ?
00000070D ? 000000000 ?
kksParseCursor()+14 call kkspsc0() 2A97311D08 ? 2A975D2188 ?
2
00000070D ? 000000003 ?
300A400000006 ?
0000300A4 ?
opiosq0()+1641 call kksParseCursor() 7FBFFFAF18 ? 2A975D2188 ?
00000070D ? 000000003 ?
300A400000006 ? 0000300A4 ?
kpooprx()+315 call opiosq0() 000000003 ? 00000000E ?
7FBFFFB108 ? 0000000A4 ?
300A400000006 ? 0000300A4 ?
kpoal8()+799 call kpooprx() 7FBFFFE2B4 ? 2A975D2188 ?
00000070C ? 000000001 ?
000000000 ? 0000300A4 ?
opiodr()+984 call kpoal8() 00000005E ? 000000017 ?
7FBFFFE2B0 ? 000000001 ?
000000001 ? 0000300A4 ?
ttcpip()+1012 call opiodr() 00000005E ? 000000017 ?
7FBFFFE2B0 ? 000000000 ?
0059DF750 ? 0000300A4 ?
opitsk()+1322 call ttcpip() 0067BC3D0 ? 000000003 ?
7FBFFFE2B0 ? 000000000 ?
7FBFFFDDA8 ? 7FBFFFE418 ?
opiino()+1026 call opitsk() 000000003 ? 000000000 ?
7FBFFFE2B0
? 000000001 ?
000000000 ? 683197600000001 ?
opiodr()+984 call opiino() 00000003C ? 000000004 ?
7FBFFFF478 ? 000000000 ?
000000000 ? 683197600000001 ?
opidrv()+547 call opiodr() 00000003C ? 000000004 ?
7FBFFFF478 ? 000000000 ?
0059DF200 ? 683197600000001 ?
sou2o()+114 call opidrv() 00000003C ? 000000004 ?
7FBFFFF478 ? 000000000 ?
0059DF200
? 683197600000001 ?
opimai_real()+163 call sou2o() 7FBFFFF450 ? 00000003C ?
000000004 ? 7FBFFFF478 ?
0059DF200 ? 683197600000001
?
main()+116 call opimai_real() 000000002 ? 7FBFFFF4E0 ?
000000004 ? 7FBFFFF478 ?
0059DF200 ? 683197600000001 ?
__libc_start_main() call main() 000000002 ? 7FBFFFF4E0 ?
+219
000000004 ? 7FBFFFF478 ?
0059DF200 ?
683197600000001 ?
_start()+42 call __libc_start_main() 0007139F8 ? 000000002 ?
7FBFFFF628 ? 0052B4BD0 ?
000000000 ? 000000002 ?
--------------------- Binary Stack Dump ---------------------
导致错误的原因是由于SQL语句中出现了ROWNUM,因此Oracle尝试使用FIRST_ROW_N优化模式来进行CBO执行计划的评估,并导致了错误。Oracle在文档Bug 7378625 - Assorted Dumps and Wrong Results from first_rows_k optimization [ID 7378625.8]对这个问题进行了描述。
该问题确认影响10.2.0.4和11.1.0.7版本,并在11.2.0.1和10.2.0.5中进行了解决。除了按照补丁外,对于出现ROWNUM的查询情况,可以考虑设置隐含参数_optimizer_rownum_pred_based_fkr为FALSE来解决。
来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/4227/viewspace-747856/ ,如需转载,请注明出处,否则将追究法律责任。