遇见ORA-07445: [opiptp+0040] 发来向刘大学习一下分析过程

ricky

环境：
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /oracle/app/oracle/product/10.2/db
System name:    AIX
Node name:      RESDB1
Release:        1
Version:        6
Machine:        00CD21164C00
Instance name: nrms1



今天在alert里面发现这个错误，ORA-07445: exception encountered: core dump [opiptp+0040] [SIGSEGV] [Address not mapped to object] [0x11113A360]我metlink检查了一下，发现这篇文章描述如下：

Connection Is Lost On Insert or Select Connect by With ORA-7445 [opiptp()+50] [ID 436199.1]
		修改时间 06-APR-2012     类型 PROBLEM     状态 PUBLISHED

In this Document
[size=-1]  Symptoms
  Cause
  Solution
  References



Applies to:Oracle Server - Enterprise Edition - Version: 10.2.0.2 to 10.2.0.5 - Release: 10.2 to 10.2
Information in this document applies to any platform.
***Checked for relevance on 06-APR-2012***SymptomsOn 10.2.0.3 in Production:
When attempting to insert append to global temporary table the following error occurs.

connection is lost with ORA-03113, logged for the client

The following error logged at the alert log file ORA-07445: Exception aufgetreten: CORE Dump [opiptp()+50] [SIGSEGV] [Address not mapped to object] [0xB718CABC] [] []

The stack trace is similar to:
opiptp opiptc opiexe opipls opiodr rpidrus

Users cannot insert append or select from  table.
CauseThe issue is caused by the following Bug 5968363 : ORA-7445 [_OPIPTP+50] FROM A COMPLEX QUERY WITH A CONNECT BY, WITH, FUNCTION CALL .

The Bug is not backport able , This issue is fixed in 11.1.0.7

SolutionPossible workarunds are:

1. set the parameter "_optimizer_connect_by_cost_based" to false , at the database level :
SQL> alter system set "_optimizer_connect_by_cost_based" = false scope=both ;


OR

2.  set the parameter "_optimizer_connect_by_cost_based" to false , at the session level :

SQL> alter session set "_optimizer_connect_by_cost_based" = false;


OR

3. Add  /*+ NO_CONNECT_BY_COST_BASED */ hint to the query, instead of setting the parameter


The effect of this parameter , is it just disables the optimizer to use cost-based transformation for connect by queries only.

----but 这个里面的参数和我的不一样：文章是opiptp()+50。我的是：[opiptp+0040]

问题：我找的这篇文章对不对啊？

我看了trace文件 nrms1_ora_30147244.rar (352.08 KB, 下载次数: 1088)

2012-6-28 14:33 上传

点击文件名下载附件

，是一个查询引起的，我发来是想看看刘大是怎么分析的，学习一下练习读懂trace文件。请刘大在写分析过程的时候多写一点，谢谢。

teapot

这个库好熟悉啊！！！！

chen28

hi
刘大
  我想问下
为什么stack 从stack call rpidrv 这个开始呢?
谢谢

Maclean Liu(刘相兵

10.2.0.4.0 - 64bit +   AIX

ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [opiptp+0040] [SIGSEGV] [Address not mapped to object] [0x11113A360] [] []
Current SQL statement for this session:
SELECT NAME FROM (SELECT * FROM RM.AREA CONNECT BY PRIOR PARENT_AREA = AREA_ID　START WITH AREA_ID = :B1 ) WHERE AREA_LEVEL = :B2
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
700000cdecb1460       592  package body RM.PG_NM_INTERFACE_FUN
700000cb6d7e330      1054  package body RM.PG_NM_IMPORT_RSC
700000c203ebf28         3  anonymous block
----- Call Stack Trace -----




stack call rpidrv= > rpiswu2= > rpidru=> skgmstack=> rpidrus=> opiodr=>opipls=>opiexe=>opiptc=>opiptp=> 报错


SQL语句：

SELECT NAME
  FROM (SELECT *
          FROM RM.AREA
        CONNECT BY PRIOR PARENT_AREA = AREA_ID　START WITH AREA_ID = :B1)
WHERE AREA_LEVEL = :B2


ODM FINDING:

Connection Is Lost On Insert or Select Connect by With ORA-7445 [opiptp()+50]





Answer:


1. 找的没错

2. 我是使用  stack call opiptc + opiptp 在MOS上搜索的