ORA-07445: [kkqtutlSetViewCols()+250]

ORA-07445: [kkqtutlSetViewCols()+250]

数据库版本：Oracle 11.2.0.1.0

数据库服务器操作系统：Windows server 2012

问题现象：业务人员在前台执行某些查询操作时，ORA-07445: 出现异常错误: 核心转储 [kkqtutlSetViewCols()+250] [ACCESS_VIOLATION] [ADDR:0x8] [PC:0x3DC175C] [UNABLE_TO_READ] [];

解决方案：查看alert警告日志，查找对应的trace文件;

找到触发此bug的sql语句如下：

----- Current SQL Statement for this session (sql_id=......) -----

SELECT DISTINCT col_1 col_1_a

  FROM (SELECT col_1, col_2, col_3, concat(col_4, col_5) col_6

          FROM tab_1

         WHERE col_7 IN ('0', '1')

           AND col_8 = 0

           AND nvl(col_9, 5) <> '3') tab_2

  LEFT outer JOIN (SELECT sum(col_1),

                          sum(col_2),

                          col_3,

                          concat(col_4, col_5) col_6

                     FROM tab_6

                    WHERE useflag = 1

                    GROUP BY col_1, col_2)

    ON tab_1.col1 = tab_2.col2

 ......

根据MOS查看，和 Bug 9577499比较匹配，当优化器为了提高查询性能，选择SQL进行查询重写query rewrite时，会触发此BUG；

解决方案：

可以禁用query rewrite临时解决此问题，或者升级数据库跳过此BUG；

禁用query rewrite语句如下：

alter system set query_rewrite_enabled=false scope=both;---(动态参数)

其中MOS中查询有关ORA-7445 [kkqtutlSetViewCols]]问题相关信息；

 

ORA-600/ORA-7445/ORA-700 Error Look-up Tool (文档 ID 153788.1)

ORA-7445 [kkqtutlsetviewcols] (文档 ID 1085948.1)

In this Document

Purpose

Scope

Details

References

---

APPLIES TO:

Oracle Database - Enterprise Edition - Version 7.0.16.0 and later
Oracle Database - Personal Edition
Oracle Database - Standard Edition
Information in this document applies to any platform.

PURPOSE

Note: For additional ORA-7445 related information please read Note:211909.1.

  This article provides information on one or more bugs that are 
  known to result in the given ORA-7445 error.

SCOPE

ERROR:
  ORA-7445 failing function "kkqtutlsetviewcols"

DETAILS

SUGGESTIONS:

  If the Known Issues section below does not help in terms of identifying
  a solution, please submit the trace files and alert.log to Oracle 
  Support Services for further analysis.

  Known Issues:

Known Bugs

You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button: 
          

The list below is restricted to show only bugs believed to affect version 11.2.0.1.
Other bugs may affect this version but have not been confirmed as being relevant yet.

There are 4 bugs listed.

NB	Bug	Fixed	Description
	11841048	11.2.0.3.BP06, 11.2.0.4, 12.1.0.1	ORA-600 [qcsvsci1] from rewrite of WITH clause query
	9577499	11.2.0.2, 12.1.0.1	ORA-7445 [kkqtutlSetViewCols] when query fails to rewrite
	9324531	11.2.0.1.BP04, 11.2.0.2, 12.1.0.1	ORA-600 [kkqcscpopn_Int: 0] from ANSI query
	8602840	11.2.0.1.BP07, 11.2.0.2	Dump (in KKQTUTLSETVIEWCOLS) / Bad Cardinality estimation on AntiJoins

·         '*' indicates that an alert exists for that issue.

·         '+' indicates a particularly notable issue / bug.

·         'I' indicates an install issue / bug included for completeness.

·         'P' indicates a port specific bug.

·         "OERI:xxxx" may be used as shorthand for ORA-600 [xxxx].

 

 

Bug 9577499 - ORA-7445 [kkqtutlSetViewCols] when query fails to rewrite (文档 ID 9577499.8)

Bug 9577499  ORA-7445 [kkqtutlSetViewCols] when query fails to rewrite

 This note gives a brief overview of bug 9577499. 
 The content was last updated on: 28-JUN-2013
 Click here for details of each of the sections below.

Affects:

Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected	Versions BELOW 12.1
Versions confirmed as being affected	11.2.0.1 11.1.0.7 11.1.0.6
Platforms affected	Generic (all / most platforms affected)

Fixed:

This issue is fixed in		12.1.0.1 (Base Release) 11.2.0.2 (Server Patch Set) 11.1.0.7 Patch 32 on Windows Platforms
Symptoms:		Related To:
Process May Dump (ORA-7445) / Abend / Abort Dump in or under kkqtutlSetViewCols		Optimizer Query Rewrite (Including Materialized Views) QUERY_REWRITE_ENABLED

Description

A failed attempt to rewrite a query may cause ORA-7445 [kkqtutlSetViewCols]

because the column mapping is not restored correctly. Would usually occur

with inline views.

Workaround

  alter session set query_rewrite_enabled=false;

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.

References

Bug:9577499 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29785807/viewspace-2104975/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29785807/viewspace-2104975/