sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle报错ORA-07445[qsmmixComputeClusteringFactor()+386]

时间:2016-06-27 19:34   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净 运维DBA在巡检时发现alert日志文件中出现ORA-07445[qsmmixComputeClusteringFactor()+386]错误,分析原因为Bug 9746210导致

在11.2.0.2版本中sql tuning可能导致ORA-07445[qsmmixComputeClusteringFactor()+386]错误

1.查看Oracle系统版本和平台信息

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)

2.巡检Oracle库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_oracleplus

----- 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

3.Ora错误ora-07445处理建议

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]


--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle报错ORA-07445[qsmmixComputeClusteringFactor()+386]

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/585.html

Oracle研究中心

关键词:

Oracle报错ORA-07445

ORA-07445[qsmmixComputeClusteringFactor()+386]