ORA-07445 Qsmmixcomputeclusteringfactor 分析

系统：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

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小补丁