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

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.

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.

ERROR:
  ORA-7445 failing function "kkqtutlsetviewcols"

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;

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/，如需轉載，請註明出處，否則將追究法律責任。