ORA-07445Qsmmixcomputeclusteringfactor分析

系统：LINUX 5.6-64数据库版本：11.2.0.2oracle的自动sql tuning 产生发生如下错误：Thu Jan 10 22:00:02 2013Begin automatic S

系统：LINUX 5.6-64
数据库版本：11.2.0.2
Oracle的自动sql tuning 产生发生如下错误：
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.

官方说明：
Error ORA-07445 Qsmmixcomputeclusteringfactor From SQL Tuning [ID 1359148.1]

Applies to:
Oracle Server - Enterprise Edition - Version: 11.2.0.2 to 11.2.0.2 - Release: 11.2 to 11.2
Information in this document applies to any platform.
Reviewed 30-Nov-2011.
Symptoms
When SQL Tuning a query undergoing OR expansion can suffer a dump
under qsmmixComputeClusteringFactor, or get other unpredicatable results.

Failing SQL could be like:

/* SQL Analyze(972,1) */ SELECT /*+ rule */ D.PRGR_UPSELLING_RETAIL, D.CODE_ORDINE_UPSELLING, A.CODE_COMPENSO, D.CODE_TIPO_TECNOLOGIA, D.DATA_VENDITA, B.ID_EVENTO, A.CODE_ACCOUNT, D.CODE_CLUSTER_RETAIL, F.DESC_TIPO_TECNOLOGIA, G.DESC_CLUSTER_RETAIL FROM VISP_COMPENSI A, VISP_COMPENSO_DETT B, VISP_CAUSALI C, VISP_UPSELLING_RETAIL D, VISP_EVENTI E, VISP_TIPI_TECNOLOGIA F, VISP_CLUSTER_RETAIL G WHERE D.CODE_ORDINE_UPSELLING = NVL(:B3 , D.CODE_ORDINE_UPSELLING) AND D.CODE_STATO_RETAIL = NVL(:B2 ,D.CODE_STATO_RETAIL) AND A.CODE_COMPENSO = B.CODE_COMPENSO AND A.ID_CAUSALE = C.ID_CAUSALE AND A.CODE_CONTRATTO = TO_CHAR(D.PRGR_UPSELLING_RETAIL) AND A.ACCOUNT_PROG = D.CODE_ORDINE_UPSELLING AND C.ID_PROCEDURA = :B1 AND B.ID_STATO_MOVIMENTO = 43 AND B.ID_EVENTO = E.ID_EVENTO AND E.DESC_EVENTO LIKE '%ATTIVAZIONE%' AND D.CODE_TIPO_TECNOLOGIA = F.CODE_TIPO_TECNOLOGIA AND D.DATA_VENDITA BETWEEN F.DATA_INIZIO_TIPO_TECNOLOGIA AND F.DATA_FINE_TIPO_TECNOLOGIA AND D.CODE_CLUSTER_RETAIL = G.CODE_CLUSTER_RETAIL

----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0x54ba3f200 11816 package body SYS.DBMS_SQLTUNE_INTERNAL
0x553418948 7 SYS.WRI$_ADV_SQLTUNE
0x54ba735c8 587 package body SYS.PRVT_ADVISOR
0x54ba735c8 2655 package body SYS.PRVT_ADVISOR

STACK:

skdstdst <- ksedst1 <- ksedst <- dbkedDefDump <- ksedmp<- ssexhd <- sighandler <- qsmmixComputeClusteringFactor <- qsmmixGenFakeIdxStats <- qsmmixPopulateIdxStats <- qsmmixSetKkotbixtqsmmixReturnCandToOpt

Cause
This is because of unpublished Bug

Solution
The is fixed in following versions:
* 12.1 (Future Release)
* 11.2.0.3

To resolve the issue:

Either
Upgrade to the following versions

* 12.1 (Future Release)
* 11.2.0.3
Or
Apply Patch 9746210

最终解决方法：打9746210小补丁