联系：手机/微信(+86 17813235971) QQ(107644445)
标题：SQL TUNING导致ORA-07445[qsmmixComputeClusteringFactor()+386]
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在11.2.0.2版本中sql tuning可能导致ORA-07445[qsmmixComputeClusteringFactor()+386]错误
系统版本和平台信息
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /oracle/product/11.2.0/dbhome_1 System name: Linux Node name: FPMS01DB Release: 2.6.18-238.el5 Version: #1 SMP Sun Dec 19 14:22:44 EST 2010 Machine: x86_64 VM name: VMWare Version: 6 Instance name: tis Redo thread mounted by this instance: 1 Oracle process number: 41 Unix process pid: 16822, image: oracle@FPMS01DB (J003)
alert日志信息
Thu Jan 10 22:00:02 2013 Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK" Thu Jan 10 22:02:39 2013 Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x4] [PC:0x597FEB4, qsmmixComputeClusteringFactor()+386] [flags: 0x0, count: 1] Errors in file /oracle/diag/rdbms/tis/tis/trace/tis_j001_11073.trc (incident=80033): ORA-07445: exception encountered: core dump [qsmmixComputeClusteringFactor()+386] [SIGSEGV] [ADDR:0x4] [PC:0x597FEB4] [Address not mapped to object] [] Incident details in: /oracle/diag/rdbms/tis/tis/incident/incdir_80033/tis_j001_11073_i80033.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Thu Jan 10 22:02:51 2013 Dumping diagnostic data in directory=[cdmp_20130110220251], requested by (instance=1, osid=11073 (J001)), summary=[incident=80033].
通过这里可以看出来，出现ORA-07445错误的原因很可能和SQL TUNING相关
trace文件信息
Dump continued from file: /oracle/diag/rdbms/tis/tis/trace/tis_j003_16822.trc
ORA-07445: exception encountered: core dump [qsmmixComputeClusteringFactor()+386]
[SIGSEGV] [ADDR:0x4] [PC:0x597FEB4] [Address not mapped to object] []
========= Dump for incident 80008 (ORA 7445 [qsmmixComputeClusteringFactor()+386]) ========
----- Beginning of Customized Incident Dump(s) -----
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x4]
[PC:0x597FEB4, qsmmixComputeClusteringFactor()+386] [flags: 0x0, count: 1]
Registers:
%rax: 0x00002b0b684593d0 %rbx: 0x0000000000000003 %rcx: 0x0000000000000000
%rdx: 0x00002b0b684593d0 %rdi: 0x00007fff758ceff8 %rsi: 0x0000000000000000
%rsp: 0x00007fff758ced00 %rbp: 0x00007fff758cf5d0 %r8: 0x00002b0b684593a8
%r9: 0x00002b0b683c8e60 %r10: 0x0000000000000017 %r11: 0x0000000000000000
%r12: 0x00002b0b684197a0 %r13: 0x00002b0b68419928 %r14: 0x00002b0b66ad2430
%r15: 0x00002b0b66d95590 %rip: 0x000000000597feb4 %efl: 0x0000000000010202
qsmmixComputeClusteringFactor()+366 (0x597fea0) call 0x597fc62
qsmmixComputeClusteringFactor()+371 (0x597fea5) lea -0x5d8(%rbp),%rdi
qsmmixComputeClusteringFactor()+378 (0x597feac) mov 0x60(%rax),%rsi
qsmmixComputeClusteringFactor()+382 (0x597feb0) mov 0x60(%rax),%rcx
> qsmmixComputeClusteringFactor()+386 (0x597feb4) movzwl 0x4(%rcx),%edx
qsmmixComputeClusteringFactor()+390 (0x597feb8) add $6,%rsi
qsmmixComputeClusteringFactor()+394 (0x597febc) mov %rax,-0x20(%rbp)
qsmmixComputeClusteringFactor()+398 (0x597fec0) call 0xa056f0
qsmmixComputeClusteringFactor()+403 (0x597fec5) mov -0x8(%rbp),%edx
*** 2013-01-09 22:50:24.205
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=durgh9p25f6bb) -----
/* SQL Analyze(788,1) */ SELECT distinct
null as isdistribution,
null as seqno,
to_char(T1.SEQNO) as TRADEID
…………
from T_XIFENFEI
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0xc5b644e8 11816 package body SYS.DBMS_SQLTUNE_INTERNAL
0xc54ca918 7 SYS.WRI$_ADV_SQLTUNE
0xd55aec98 587 package body SYS.PRVT_ADVISOR
0xd55aec98 2655 package body SYS.PRVT_ADVISOR
0xc5f66c70 241 package body SYS.DBMS_ADVISOR
0xc5418dc8 821 package body SYS.DBMS_SQLTUNE
0xd50d38c0 4 anonymous block
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
skdstdst()+36 call kgdsdst() 000000000 ? 000000000 ?
2B0B65B6C098 ? 000000001 ?
000000001 ? 000000003 ?
ksedst1()+98 call skdstdst() 000000000 ? 000000000 ?
2B0B65B6C098 ? 000000001 ?
000000000 ? 000000003 ?
ksedst()+34 call ksedst1() 000000001 ? 000000001 ?
2B0B65B6C098 ? 000000001 ?
000000000 ? 000000003 ?
dbkedDefDump()+2741 call ksedst() 000000001 ? 000000001 ?
2B0B65B6C098 ? 000000001 ?
000000000 ? 000000003 ?
ksedmp()+36 call dbkedDefDump() 000000003 ? 000000003 ?
2B0B65B6C098 ? 000000001 ?
000000000 ? 000000003 ?
ssexhd()+2366 call ksedmp() 000000003 ? 000000003 ?
2B0B65B6C098 ? 000000001 ?
000000000 ? 000000003 ?
__sighandler() call ssexhd() 00000000B ? 2B0B65B74D70 ?
2B0B65B74C68 ? 000000001 ?
000000000 ? 000000003 ?
qsmmixComputeCluste signal __sighandler() 7FFF758CEFF8 ? 000000000 ?
ringFactor()+386 2B0B684593D0 ? 000000000 ?
2B0B684593A8 ? 2B0B683C8E60 ?
qsmmixGenFakeIdxSta call qsmmixComputeCluste 2B0B68419928 ? 7FFF758CEFF8 ?
ts()+1025 ringFactor() 2B0B66D95590 ?
BFF0000000000000 ?
000000000 ? 2B0B683C8E60 ?
qsmmixPopulateIdxSt call qsmmixGenFakeIdxSta 2B0B66AD2430 ? 2B0B68419928 ?
ats()+71 ts() 2B0B684197A0 ? 2B0B66B92630 ?
40BAD30000000000 ?
2B0B683C8E60 ?
qsmmixSetKkotbixt() call qsmmixPopulateIdxSt 2B0B66B92630 ? 2B0B66AD2430 ?
+479 ats() 2B0B68419928 ? 2B0B66B92630 ?
40BAD30000000000 ?
2B0B683C8E60 ?
qsmmixReturnCandToO call qsmmixSetKkotbixt() 2B0B66B92630 ? 2B0B66AD2430 ?
pt()+656 2B0B683F0090 ? 2B0B6845DCC8 ?
2B0B6845C2B8 ? 2B0B68462F08 ?
qsmmixOptimizerGenI call qsmmixReturnCandToO 2B0B66B92630 ? 2B0B66AD2430 ?
dxCand()+601 pt() 2B0B68462F60 ? 2B0B6845DCC8 ?
2B0B6845C2B8 ? 2B0B68462F08 ?
qsmmixOptimizerSetu call qsmmixOptimizerGenI 0913F73A0 ? 2B0B6845C2B8 ?
pIdxCand()+351 dxCand() 000000009 ? 000000001 ?
2B0B6845C2B8 ? 2B0B68462F08 ?
kkoiqb()+13730 call qsmmixOptimizerSetu 0913F73A0 ? 2B0B683EEFD8 ?
pIdxCand() 000000009 ? 000000001 ?
2B0B6845C2B8 ? 2B0B68462F08 ?
kkooqb()+632 call kkoiqb() 2B0B66B6FF88 ? 000000000 ?
000000000 ? 000000001 ?
2B0B6845C2B8 ? 2B0B68462F08 ?
kkoqbc()+2359 call kkooqb() 2B0B66B6FF88 ? 000000000 ?
000000000 ? 000000004 ?
2B0B00000000 ? 2B0B00000000 ?
apakkoqb()+166 call kkoqbc() 7FFF758D2DF0 ? 2B0B66B6FF88 ?
000000000 ? 000000004 ?
2B0B00000000 ? 2B0B00000000 ?
apaqbdDescendents() call apakkoqb() 7FFF758D2DF0 ? 2B0B66B6FF88 ?
+457 0913F73A0 ? 000000004 ?
2B0B00000000 ? 2B0B00000000 ?
apaqbdList()+71 call apaqbdDescendents() 7FFF758D2DF0 ? 2B0B66B6FF88 ?
0913F73A0 ? 000000004 ?
2B0B00000000 ? 2B0B00000000 ?
apaqbdDescendents() call apaqbdList() 7FFF758D2DF0 ? 2B0B66B6FF88 ?
+710 0913F73A0 ? 000000004 ?
2B0B00000000 ? 2B0B00000000 ?
apaqbdList()+71 call apaqbdDescendents() 0913F73A0 ? 2B0B66C975C8 ?
0913F73A0 ? 000000004 ?
2B0B00000000 ? 2B0B00000000 ?
apaqbd()+9 call apaqbdList() 0913F73A0 ? 2B0B66C975C8 ?
0913F73A0 ? 000000004 ?
2B0B00000000 ? 2B0B00000000 ?
apadrv()+861 call apaqbd() 0913F73A0 ? 2B0B66C975C8 ?
0913F73A0 ? 000000004 ?
2B0B00000000 ? 2B0B00000000 ?
opitca()+1971 call apadrv() 0913F73A0 ? 2B0B66C975C8 ?
0913F73A0 ? 000000004 ?
2B0B00000000 ? 2B0B00000000 ?
kksSetBindType()+76 call opitca() 2B0B671A77F8 ? 0913F73A0 ?
05 7FFF758D4D10 ? 000000004 ?
100000000 ? 2B0B00000000 ?
kksfbc()+10664 call kksSetBindType() 7FFF758D4D10 ? 2B0B671A77F8 ?
7FFF758D4DA0 ? 000000102 ?
7FFF0000001F ? 000000000 ?
opiexe()+2268 call kksfbc() 2B0B671A77F8 ? 000000003 ?
000000102 ? 000000000 ?
000000000 ? 7FFF758D5D70 ?
kpoal8()+2226 call opiexe() 000000049 ? 000000003 ?
7FFF758D6310 ? 000000000 ?
000000000 ? 7FFF758D5D70 ?
opiodr()+910 call kpoal8() 00000005E ? 000000000 ?
7FFF758DA288 ? 000000000 ?
000000000 ? 000000001 ?
kpoodrc()+31 call opiodr() 00000005E ? 000000000 ?
7FFF758DA288 ? 000000000 ?
00989C970 ? 000000001 ?
rpiswu2()+1618 call kpoodrc() 7FFF758D75C0 ? 000000000 ?
7FFF758DA288 ? 000000000 ?
00989C970 ? 000000001 ?
kpoodr()+617 call rpiswu2() 0D3C52C80 ? 000000023 ?
2B0B65EDFAAC ? 000000004 ?
2B0B6C6B7340 ? 000000023 ?
upirtrc()+2417 call kpoodr() 2B0B65EBF170 ? 00000005E ?
7FFF758DA288 ? 000000000 ?
2B0B6C6B7340 ? 000000023 ?
kpurcsc()+93 call upirtrc() 2B0B65EBF170 ? 00000005E ?
7FFF758DA288 ? 7FFF758DA400 ?
7FFF758DB420 ? 2B0B65C7D308 ?
kpuexec()+10804 call kpurcsc() 2B0B65EBF170 ? 00000005E ?
7FFF758DA288 ? 7FFF758DA400 ?
7FFF758DB420 ? 2B0B65C7D308 ?
OCIStmtExecute()+34 call kpuexec() 2B0B65E3E5B8 ? 2B0B65E3E538 ?
7FFF758DA280 ? 000000000 ?
E0C28C4F00000000 ?
000000000 ?
qksanExecSql()+743 call OCIStmtExecute() 2B0B65E3E5B8 ? 2B0B65E3E538 ?
7FFF758DA280 ? 000000000 ?
E0C28C4F00000000 ?
000000000 ?
qksanAnalyzeSql()+2 call qksanExecSql() 7FFF758DDCA8 ? 2B0B65C7D308 ?
363 2B0B65C7D3E8 ? 2B0B65E3E538 ?
7FFF758DDC28 ? 7FFF758DE3A9 ?
qsmmixProcessQuery( call qksanAnalyzeSql() 7FFF758DDCA8 ? 2B0B67E90DA8 ?
)+1089 00000B3AB ? 2B0B65FBD218 ?
100000023 ? 7FFF758DE3A9 ?
qsmmixSqlTuneAnalyz call qsmmixProcessQuery( 2B0B65FBD218 ? 000000005 ?
eIdx()+449 ) 2B0B67E90DA8 ? 2B0B65F37BF4 ?
2B0B65FBD218 ? 7FFF00000023 ?
kestsiIndexAnalyzeD call qsmmixSqlTuneAnalyz 7FFF758DE370 ? 000000005 ?
rv()+794 eIdx() 2B0B67E90DA8 ? 2B0B65F37BF4 ?
2B0B65FBD218 ? 7FFF00000023 ?
kestsTuneSqlDrv()+3 call kestsiIndexAnalyzeD 7FFF758E0548 ? 7FFF758E08C0 ?
83 rv() 7FFF758DFC78 ? 7FFF758E09B0 ?
7FFF758DED50 ? 7FFF758DED58 ?
kesaiExecAction()+9 call kestsTuneSqlDrv() 7FFF758E0540 ? 7FFF758E08C0 ?
81 7FFF758DFC78 ? 7FFF758E09B0 ?
7FFF758DED50 ? 7FFF758DED58 ?
kesaiTuneSqlDrv()+6 call kesaiExecAction() 7FFF758DF420 ? 7FFF758E08C0 ?
258 7FFF758E09B0 ? 7FFF758E05E0 ?
7FFF758E0A68 ? 7FFF758E0A60 ?
spefcifa()+225 call kesaiTuneSqlDrv() 7FFF758E21B8 ? 2B0B65CA3180 ?
000000000 ? 7FFF758E05E0 ?
0C5FB0033 ? 2B0B65C362B8 ?
spefmccallstd()+421 call spefcifa() 7FFF758E1CE0 ? 000000004 ?
2B0B65CA3108 ? 7FFF758E0F30 ?
0C5FB0033 ? 2B0B65C362B8 ?
pextproc()+36 call spefmccallstd() 7FFF758E20E0 ? 7FFF758E1D88 ?
7FFF758E1AA0 ? 7FFF758E1CE0 ?
000000000 ? 2B0B65C362B8 ?
__PGOSF589_peftrust call pextproc() 7FFF758E20E0 ? 7FFF758E1D88 ?
ed()+145 7FFF758E1AA0 ? 7FFF758E1CE0 ?
000000000 ? 2B0B65C362B8 ?
__PGOSF633_psdexsp( call __PGOSF589_peftrust 7FFF758E20E0 ? 7FFF758E1D88 ?
)+255 ed() 7FFF758E1AA0 ? 7FFF758E1CE0 ?
000000000 ? 2B0B65C362B8 ?
rpiswu2()+1618 call __PGOSF633_psdexsp( 7FFF758E18D0 ? 7FFF758E20E0 ?
) 7FFF758E1AA0 ? 000020003 ?
0037500E0 ? 7FFF758E3100 ?
psdextp()+695 call rpiswu2() 0D3C52C80 ? 000000000 ?
7FFF758E1910 ? 000000002 ?
7FFF758E1950 ? 000000000 ?
pefccal()+726 call psdextp() 7FFF758E3100 ? 7FFF758E1D88 ?
7FFF758E1AA0 ? 000000000 ?
000020003 ? 7FFF758E20E0 ?
pefcal()+219 call pefccal() 7FFF758E20E0 ? 000A324C2 ?
00B7C8EA0 ? 000000000 ?
000020003 ? 7FFF758E20E0 ?
pevm_FCAL()+164 call pefcal() 7FFF758E20E0 ? 00B7C9050 ?
2B0B65C835B8 ? 000000000 ?
000020003 ? 7FFF758E20E0 ?
pfrinstr_FCAL()+70 call pevm_FCAL() 2B0B65C835B8 ? 0A6FB80D8 ?
2B0B65C835B8 ? 0A6FB8030 ?
000020003 ? 7FFF758E20E0 ?
pfrrun_no_tool()+63 call pfrinstr_FCAL() 2B0B65C835B8 ? 0A5405930 ?
2B0B65C83628 ? 0A6FB8030 ?
000020003 ? 7FFF758E20E0 ?
pfrrun()+622 call pfrrun_no_tool() 2B0B65C835B8 ? 0A5405930 ?
2B0B65C83628 ? 0A6FB8030 ?
000020003 ? 7FFF758E20E0 ?
plsql_run()+644 call pfrrun() 2B0B65C835B8 ? 000000000 ?
2B0B65C83628 ? 7FFF758E3100 ?
000020003 ? 0928C7098 ?
peicnt()+296 call plsql_run() 2B0B65C835B8 ? 000000001 ?
000000000 ? 7FFF758E3100 ?
000020003 ? 000000000 ?
kkxexe()+521 call peicnt() 7FFF758E3100 ? 2B0B65C835B8 ?
2B0B65CA4FD8 ? 7FFF758E3100 ?
2B0B65CA2F30 ? 000000000 ?
opiexe()+17478 call kkxexe() 2B0B65C8BD58 ? 2B0B65C835B8 ?
000000000 ? 7FFF758E3100 ?
2B0B65CA2F30 ? 000000000 ?
kpoal8()+2226 call opiexe() 000000049 ? 000000003 ?
7FFF758E4730 ? 7FFF758E3100 ?
2B0B65CA2F30 ? 000000000 ?
opiodr()+910 call kpoal8() 00000005E ? 000000000 ?
7FFF758E81A8 ? 7FFF758E3100 ?
2B0B65CA2F30 ? 7FFF00000001 ?
kpoodr()+648 call opiodr() 00000005E ? 000000000 ?
7FFF758E81A8 ? 000000000 ?
00989C970 ? 7FFF00000001 ?
upirtrc()+2417 call kpoodr() 2B0B65C796A8 ? 00000005E ?
7FFF758E81A8 ? 000000000 ?
00989C970 ? 7FFF00000001 ?
kpurcsc()+93 call upirtrc() 2B0B65C796A8 ? 00000005E ?
7FFF758E81A8 ? 7FFF758E8320 ?
7FFF758E9340 ? 2B0B65C7D308 ?
kpuexec()+11692 call kpurcsc() 2B0B65C796A8 ? 00000005E ?
7FFF758E81A8 ? 7FFF758E8320 ?
7FFF758E9340 ? 2B0B65C7D308 ?
OCIStmtExecute()+34 call kpuexec() 2B0B65C88290 ? 2B0B65C88210 ?
7FFF758E81A0 ? 000000001 ?
7FFF00000000 ? 000000000 ?
__PGOSF529_jslvec_e call OCIStmtExecute() 2B0B65C88290 ? 2B0B65C88210 ?
xeccb()+2207 7FFF758E81A0 ? 000000001 ?
7FFF00000000 ? 000000000 ?
jslvswu()+54 call __PGOSF529_jslvec_e 7FFF758EC39C ? 2B0B65C88210 ?
xeccb() 2B0B65C88210 ? 000000001 ?
7FFF00000000 ? 000000000 ?
jslve_execute0()+22 call jslvswu() 000000000 ? 7FFF00000000 ?
17 000000000 ? 000000001 ?
7FFF00000000 ? 000000000 ?
jslve_execute()+327 call jslve_execute0() 7FFF758EE2B4 ? 000005946 ?
000000002 ? 7FFF758EE2A0 ?
000000000 ? 0FFFFFFFF ?
rpiswu2()+1618 call jslve_execute() 7FFF758EE150 ? 000000002 ?
7FFF758EE2B4 ? 000005946 ?
7FFF758EE2A0 ? 0FFFFFFFF ?
kkjex1e()+374 call rpiswu2() 0D3C52C80 ? 000000000 ?
7FFF758EE170 ? 000000002 ?
7FFF758EE190 ? 000000000 ?
kkjsexe()+705 call kkjex1e() 7FFF758EE2B4 ? 000005946 ?
000000002 ? 7FFF758EE2A0 ?
0D57A5F08 ? 7FFF758EE208 ?
kkjrdp()+689 call kkjsexe() 7FFF758EE2B4 ? 000005946 ?
000000001 ? 7FFF758EE2A0 ?
0D57A5F08 ? 7FFF758EE208 ?
opirip()+953 call kkjrdp() 7FFF758EE2B4 ? 000005946 ?
000000001 ? 7FFF758EE2A0 ?
0D57A5F08 ? 7FFF758EE208 ?
opidrv()+598 call opirip() 000000032 ? 000000004 ?
7FFF758EFA28 ? 7FFF758EE2A0 ?
0D57A5F08 ? 7FFF758EE208 ?
sou2o()+98 call opidrv() 000000032 ? 000000004 ?
7FFF758EFA28 ? 7FFF758EE2A0 ?
0D57A5F08 ? 7FFF758EE208 ?
opimai_real()+261 call sou2o() 7FFF758EFA00 ? 000000032 ?
000000004 ? 7FFF758EFA28 ?
0D57A5F08 ? 7FFF758EE208 ?
ssthrdmain()+252 call opimai_real() 000000000 ? 7FFF758EFBF0 ?
000000004 ? 7FFF758EFA28 ?
0D57A5F08 ? 7FFF758EE208 ?
main()+196 call ssthrdmain() 000000003 ? 7FFF758EFBF0 ?
000000001 ? 000000000 ?
0D57A5F08 ? 7FFF758EE208 ?
__libc_start_main() call main() 000000003 ? 7FFF758EFD90 ?
+244 000000001 ? 000000000 ?
0D57A5F08 ? 7FFF758EE208 ?
_start()+36 call __libc_start_main() 000A077C8 ? 000000001 ?
7FFF758EFD88 ? 000000000 ?
0D57A5F08 ? 000000003 ?
--------------------- Binary Stack Dump ---------------------
分析Stack可以发现，他们和MOS[]中的非常类此,可以断定是该Bug 9746210
skdstdst <- ksedst1 <- ksedst <- dbkedDefDump <- ksedmp<- ssexhd <- sighandler <- qsmmixComputeClusteringFactor <- qsmmixGenFakeIdxStats <- qsmmixPopulateIdxStats <- qsmmixSetKkotbixtqsmmixReturnCandToOpt <- qsmmixOptimizerGenIdxCand <- qsmmixOptimizerSetupIdxCandResponse
处理建议
The is fixed in following versions: 12.1 (Future Release) 11.2.0.3 (Server Patch Set) 11.2.0.2.4 Patch Set Update 11.2.0.2 Bundle Patch 12 for Exadata Database 11.2.0.2 Patch 11 on Windows Platforms To resolve the issue: Either Upgrade to the over versions Or Apply Patch 9746210
补充说明
对于SQL TUNING功能,在觉得多少人的环境中都不需要这个,可以考虑禁用该功能来屏蔽该错误
BEGIN
dbms_auto_task_admin.disable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
参考文档
SQL Tuning query fails with ORA-7445 [qsmmixComputeClusteringFactor] [ID 1483654.1]
Error ORA-07445 Qsmmixcomputeclusteringfactor From SQL Tuning [ID 1359148.1]
Bug 9746210 – ORA-7445 [qsmmixComputeClusteringFactor] from SQL tuning [ID 9746210.8]