一家内衣品牌的客户，环境oracle 11.2.0.4 rac on linux, db alert log日志中出现应用查询提示oracle ORA-07445: exception encountered: core dump [kkqteSetSubPartNums()+355] [SIGSEGV] [ADDR:0x7FEFCA3BDFB8] [PC:0x7818D8F] [Invalid permissions for mapped object] [] 和ORA-00600: 内部错误代码, 参数: [kghrst:ds], [0x7FFC26461030]， 简单记录该问题
ORA-07445 [kkqteSetSubPartNums()+355]
ORA-07445: exception encountered: core dump [kkqteSetSubPartNums()+355] [SIGSEGV] [ADDR:0x7FEFCA3BDFB8] [PC:0x7818D8F] [Invalid permissions for mapped object] []
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=6abm1hd5mrdjy) -----
select p.DAY as DAY, p.location, p.TOTALMONEY as SALEMONEY,xxx
...
()... 一个复杂查询
---- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
skdstdst()+41 call kgdsdst() 000000000 ? 000000000 ?
7FEFCAEDAC40 ? 7FEFCAEDAD18 ?
7FEFCAEDF7C0 ? 000000003 ?
ksedst1()+103 call skdstdst() 000000000 ? 000000000 ?
7FEFCAEDAC40 ? 7FEFCAEDAD18 ?
7FEFCAEDF7C0 ? 000000003 ?
ksedst()+39 call ksedst1() 000000001 ? 000000001 ?
7FEFCAEDAC40 ? 7FEFCAEDAD18 ?
7FEFCAEDF7C0 ? 000000003 ?
dbkedDefDump()+2746 call ksedst() 000000001 ? 000000001 ?
7FEFCAEDAC40 ? 7FEFCAEDAD18 ?
7FEFCAEDF7C0 ? 000000003 ?
ksedmp()+41 call dbkedDefDump() 000000003 ? 000000003 ?
7FEFCAEDAC40 ? 7FEFCAEDAD18 ?
7FEFCAEDF7C0 ? 000000003 ?
ssexhd()+2462 call ksedmp() 000000003 ? 000000003 ?
7FEFCAEDAC40 ? 7FEFCAEDAD18 ?
7FEFCAEDF7C0 ? 000000003 ?
__sighandler() call ssexhd() 00000000B ? 7FEFCAEE0BF0 ?
7FEFCAEE0AE8 ? 7FEFCAEDAD18 ?
7FEFCAEDF7C0 ? 000000003 ?
kkqteSetSubPartNums signal __sighandler() 7DFAB9490 ? 7FEFCA3AF0A0 ?
()+355 00000000C ? 7DFAB9620 ?
000000000 ? 000000000 ?
kkqteGeneratePreds( call kkqteSetSubPartNums 7DFAB9490 ? 7FEFCA3AF0A0 ?
)+271 () 00000000C ? 7DFAB9620 ?
000000000 ? 000000000 ?
kkqteTransform()+75 call kkqteGeneratePreds( 7FEFCA35DE20 ? 7FEFCA3AF0A0 ?
7 ) 7FFF079F8450 ? 7DFAB9620 ?
000000000 ? 000000000 ?
kkqctdrvTE()+1531 call kkqteTransform() 7FEFCA35DE20 ? 7FEFCA3AF0A0 ?
7FFF079F8450 ? 7DFAB9620 ?
000000000 ? 000000000 ?
kkqtedtr()+145 call kkqctdrvTE() 7FEFCA35DE20 ? 7FEFCA3AF0A0 ?
7FFF079F8450 ? 7DFAB9620 ?
000000000 ? 000000000 ?
qksqbApplyToQbcLoc( call kkqtedtr() 7FEFCA948EB0 ? 7FFF079F87E8 ?
)+618 7FFF079F8450 ? 7DFAB9620 ?
000000000 ? 000000000 ?
qksqbApplyToQbcLoc( call qksqbApplyToQbcLoc( 7FEFCA948EB0 ? 7FFF079F8740 ?
)+1059 ) 7FFF079F8450 ? 7DFAB9620 ?
000000000 ? 000000000 ?
qksqbApplyToQbcLoc( call qksqbApplyToQbcLoc( 7FEFCA935438 ? 7FFF079F8740 ?
)+1000 ) 7FFF079F8450 ? 7DFAB9620 ?
000000000 ? 000000000 ?
qksqbApplyToQbc()+8 call qksqbApplyToQbcLoc( 7FEFCA939398 ? 7FFF079F8740 ?
70 ) 7FFF079F8450 ? 7DFAB9620 ?
000000000 ? 000000000 ?
kkqctdrvTD()+5211 call qksqbApplyToQbc() 7FEFCA938F18 ? 0023F198A ?
7FFF079F87E8 ? 000000000 ?
000000000 ? 000000000 ?
kkqtedrv()+91 call kkqctdrvTD() 000000000 ? 7B9F3E5E0 ?
000000009 ? 000000000 ?
000000000 ? 000000000 ?
kkqdrv()+2061 call kkqtedrv() 7FEFCAD16938 ? 7B9F3E5E0 ?
000000009 ? 000000000 ?
000000000 ? 000000000 ?
kkqctdrvIT()+764 call kkqdrv() 7FEFCAD16938 ? 000000000 ?
000000009 ? 000000000 ?
000000000 ? 000000000 ?
apadrv()+3059 call kkqctdrvIT() 7FEFCAD16938 ? 7B9F3E5E0 ?
000000009 ? 000000000 ?
000000000 ? 000000000 ?
opitca()+2089 call apadrv() 7B9F3E5E0 ? 7B9F3E5E0 ?
000000009 ? 000000000 ?
000000000 ? 000000000 ?
kksFullTypeCheck()+ call opitca() 7FEFCAD59A80 ? 7B9F3E5E0 ?
69 7FFF079FB500 ? 000000000 ?
对于kkqteSetSubPartNums 全网没有任何信息，KKQTE==>kernel compile query table expansion 似乎和查询转换过程中的table explansion有关。
kkqteSetSubPartNums signal __sighandler() 7DFAB9490 ? 7FEFCA3AF0A0 ?
()+355 00000000C ? 7DFAB9620 ?
000000000 ? 000000000 ?
kkqteGeneratePreds( call kkqteSetSubPartNums 7DFAB9490 ? 7FEFCA3AF0A0 ?
kernel compile query table expansion [partial hit for: kkqte ] => kernel compile query table expansion [partial hit for: kkqte ]
)+271 () 00000000C ? 7DFAB9620 ?
000000000 ? 000000000 ?
kkqteTransform()+75 call kkqteGeneratePreds( 7FEFCA35DE20 ? 7FEFCA3AF0A0 ?
kernel compile query table expansion [partial hit for: kkqte ] => kernel compile query table expansion [partial hit for: kkqte ]
7 ) 7FFF079F8450 ? 7DFAB9620 ?
000000000 ? 000000000 ?
kkqctdrvTE()+1531 call kkqteTransform() 7FEFCA35DE20 ? 7FEFCA3AF0A0 ?
kernel compile query cost based query transformation driver table expansion => kernel compile query table expansion [partial hit for: kkqte ]
7FFF079F8450 ? 7DFAB9620 ?
000000000 ? 000000000 ?
kkqtedtr()+145 call kkqctdrvTE() 7FEFCA35DE20 ? 7FEFCA3AF0A0 ?
kernel compile query table expansion [partial hit for: kkqte ] => kernel compile query cost based query transformation driver table expansion
7FFF079F8450 ? 7DFAB9620 ?
000000000 ? 000000000 ?
qksqbApplyToQbcLoc( call kkqtedtr() 7FEFCA948EB0 ? 7FFF079F87E8 ?
query kernel sql Query compilation for query blocks apply to QB local implementation => kernel compile query table expansion [partial hit for: kkqte ]
)+618 7FFF079F8450 ? 7DFAB9620 ?
000000000 ? 000000000 ?
qksqbApplyToQbcLoc( call qksqbApplyToQbcLoc( 7FEFCA948EB0 ? 7FFF079F8740 ?
query kernel sql Query compilation for query blocks apply to QB local implementation => query kernel sql Query compilation for query blocks apply to QB local implementation
)+1059 ) 7FFF079F8450 ? 7DFAB9620 ?
000000000 ? 000000000 ?
qksqbApplyToQbcLoc( call qksqbApplyToQbcLoc( 7FEFCA935438 ? 7FFF079F8740 ?
query kernel sql Query compilation for query blocks apply to QB local implementation => query kernel sql Query compilation for query blocks apply to QB local implementation
)+1000 ) 7FFF079F8450 ? 7DFAB9620 ?
000000000 ? 000000000 ?
qksqbApplyToQbc()+8 call qksqbApplyToQbcLoc( 7FEFCA939398 ? 7FFF079F8740 ?
query kernel sql Query compilation for query blocks apply to QB => query kernel sql Query compilation for query blocks apply to QB local implementation
70 ) 7FFF079F8450 ? 7DFAB9620 ?
000000000 ? 000000000 ?
kkqctdrvTD()+5211 call qksqbApplyToQbc() 7FEFCA938F18 ? 0023F198A ?
kernel compile query cost based query transformation driver transformation directives => query kernel sql Query compilation for query blocks apply to QB
7FFF079F87E8 ? 000000000 ?
000000000 ? 000000000 ?
kkqtedrv()+91 call kkqctdrvTD() 000000000 ? 7B9F3E5E0 ?
kernel compile query table expansion driver => kernel compile query cost based query transformation driver transformation directives
000000009 ? 000000000 ?
000000000 ? 000000000 ?
kkqdrv()+2061 call kkqtedrv() 7FEFCAD16938 ? 7B9F3E5E0 ?
kernel compile query transformation driver => kernel compile query table expansion driver
000000009 ? 000000000 ?
000000000 ? 000000000 ?
kkqctdrvIT()+764 call kkqdrv() 7FEFCAD16938 ? 000000000 ?
kernel compile query cost based query transformation driver initiate cost-based transformation => kernel compile query transformation driver
000000009 ? 000000000 ?
000000000 ? 000000000 ?
apadrv()+3059 call kkqctdrvIT() 7FEFCAD16938 ? 7B9F3E5E0 ?
SQL Access Path Analysis drive access path selection for a sql command => kernel compile query cost based query transformation driver initiate cost-based transformation
000000009 ? 000000000 ?
000000000 ? 000000000 ?
opitca()+2089 call apadrv() 7B9F3E5E0 ? 7B9F3E5E0 ?
oracle program interface sets up context area => SQL Access Path Analysis drive access path selection for a sql command
解决方法
1, 禁用table expansion
SQL> alter system set "_optimizer_table_expansion"=false;
注意:请确保在生产环境中实现此参数更改之前进行测试，因为它可能会影响某些应用程序的性能。
— or —
2, 查询是否有分区索引失效
SetSubPartNums判断与分区相关，该案例确实存在Invalid partition，重建后恢复正常。
ORA-00600 [kghrst:ds]和 [kghfrempty:ds]
db alert log
Errors in file /oracle/diag/rdbms/anbob/anbob1/trace/anbob1_ora_115410.trc: ORA-00600: internal error code, arguments: [kghrst:ds], [0x7F0B7A19FA10], [], [], [], [], [], [], [], [], [], [] ORA-00600: internal error code, arguments: [kghfrempty:ds], [0x7F0B7A19FA10], [], [], [], [], [], [], [], [], [], []
600 kghrst trace file
----- Error Stack Dump -----
ORA-00600: 内部错误代码, 参数: [kghrst:ds], [0x7FFC26461030], [], [], [], [], [], [], [], [], [], []
----- Current SQL Statement for this session (sql_id=53p5b214b18h4) -----
SELECT sum(t1.RECEIVABLE_MONEY) as TOTALMONEY,
...
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
skdstdst()+41 call kgdsdst() 000000000 ? 000000000 ?
7FFF9002BC20 ? 7FFF9002BCF8 ?
7FFF900307A0 ? 000000002 ?
ksedst1()+103 call skdstdst() 000000000 ? 000000000 ?
7FFF9002BC20 ? 7FFF9002BCF8 ?
7FFF900307A0 ? 000000002 ?
ksedst()+39 call ksedst1() 000000000 ? 000000001 ?
7FFF9002BC20 ? 7FFF9002BCF8 ?
7FFF900307A0 ? 000000002 ?
dbkedDefDump()+2746 call ksedst() 000000000 ? 000000001 ?
7FFF9002BC20 ? 7FFF9002BCF8 ?
7FFF900307A0 ? 000000002 ?
ksedmp()+41 call dbkedDefDump() 000000003 ? 000000000 ?
7FFF9002BC20 ? 7FFF9002BCF8 ?
7FFF900307A0 ? 000000002 ?
ksupop()+3975 call ksedmp() 000000003 ? 000000000 ?
7FFF9002BC20 ? 7FFF9002BCF8 ?
7FFF900307A0 ? 000000002 ?
opiodr()+1009 call ksupop() 000000001 ? 000000000 ?
7FFF9002BC20 ? 7FFF9002BCF8 ?
7FFF900307A0 ? 000000002 ?
ttcpip()+1255 call opiodr() 00000005E ? 00000001C ?
7FFF900342A0 ? 7FFF9002BCF8 ?
7FFF900307A0 ? 000000002 ?
opitsk()+1710 call ttcpip() 00C150970 ? 0099F14B0 ?
7FFF900342A0 ? 000000000 ?
7FFF90033CF8 ? 7FFF9003429C ?
opiino()+969 call opitsk() 00C150978 ? 000000000 ?
7FFF900342A0 ? 000000000 ?
7FFF90033CF8 ? 7FFF9003429C ?
opiodr()+917 call opiino() 00000003C ? 000000004 ?
7FFF90035A98 ? 000000000 ?
7FFF90033CF8 ? 7FFF9003429C ?
opidrv()+570 call opiodr() 00000003C ? 000000004 ?
7FFF90035A98 ? 000000000 ?
7FFF90033CF8 ? 7FFF9003429C ?
sou2o()+103 call opidrv() 00000003C ? 000000004 ?
7FFF90035A98 ? 000000000 ?
7FFF90033CF8 ? 7FFF9003429C ?
opimai_real()+133 call sou2o() 7FFF90035A70 ? 00000003C ?
000000004 ? 7FFF90035A98 ?
7FFF90033CF8 ? 7FFF9003429C ?
ssthrdmain()+265 call opimai_real() 000000002 ? 7FFF90035C60 ?
000000004 ? 7FFF90035A98 ?
7FFF90033CF8 ? 7FFF9003429C ?
main()+201 call ssthrdmain() 000000002 ? 7FFF90035C60 ?
000000001 ? 000000000 ?
7FFF90033CF8 ? 7FFF9003429C ?
__libc_start_main() call main() 000000002 ? 7FFF90035E08 ?
+253 000000001 ? 000000000 ?
7FFF90033CF8 ? 7FFF9003429C ?
_start()+41 call __libc_start_main() 000A2B614 ? 000000002 ?
7FFF90035DF8 ? 000000000 ?
7FFF90033CF8 ? 7FFF9003429C ?
Dumping Event (group=SESSION)
Dumping Event (group=SYSTEM)
DYNAMICALLY MODIFIED PARAMETERS:
nls_language = SIMPLIFIED CHINESE
nls_territory = CHINA
log_archive_dest_state_2 = ENABLE
DDE: Flood control is not active
Incident 55634772 created, dump file: /oracle/diag/rdbms/dslrprd1new/dslrprd13/incident/incdir_55634772/dslrprd13_ora_18969_i55634772.trc
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [kghrst:ds], [0x7FFC26461030], [], [], [], [], [], [], [], [], [], []
kgefec: fatal error 0
可能的原因
23326243 12.2.0.1 ORA-600[17147] when create procedure containing muti-bytes characters
可能的解决方法
内存级corrupted, flush shared_pool， 或查看SQL中是否有使用muti-bytes characters如中文字符。