ORA-07445: exception encountered: core dump [qervwRowProcedure()+133]

最新推荐文章于 2023-10-13 19:11:03 发布

转载最新推荐文章于 2023-10-13 19:11:03 发布 · 510 阅读

本文详细记录了解决Oracle数据库中ORA-07445错误的过程，通过修改隐含参数_INDEX_JOIN_ENABLED成功解决了由index join引起的异常崩溃问题。

今天打开邮箱，发现很多数据库报错信息：

2006-11-16 15:31:06 Thu Errors in file /opt/oracle/admin/sc2test/udump/sc2test_ora_25718.trc:
2006-11-16 15:31:06 Thu ORA-07445: exception encountered: core dump [qervwRowProcedure()+133] [SIGSEGV] [Address not mapped to object] [0xC] [] []
2006-11-16 15:31:06 Thu Errors in file /opt/oracle/admin/sc2test/udump/sc2test_ora_25770.trc:
2006-11-16 15:31:06 Thu ORA-07445: exception encountered: core dump [qervwRowProcedure()+133] [SIGSEGV] [Address not mapped to object] [0xC] [] []
2006-11-16 15:32:11 Thu Errors in file /opt/oracle/admin/sc2test/udump/sc2test_ora_25766.trc:
2006-11-16 15:32:11 Thu ORA-07445: exception encountered: core dump [qervwRowProcedure()+133] [SIGSEGV] [Address not mapped to object] [0xC] [] []

看了几个trace文件，发现都是同一个sql造成的：
*** 2006-11-16 15:22:49.528
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [qervwRowProcedure()+133] [SIGSEGV] [Address not mapped to object] [0xC] [] []
Current SQL statement for this session:
select count(distinct product0_.ITEM_ID) as col_0_0_
from ITEM product0_
inner join ITEM_TAG itemtags1_ on product0_.ITEM_ID = itemtags1_.ITEM_ID
where product0_.ITEM_TYPE = 'p'
and (itemtags1_.TAG_ID in (23475))
and (product0_.ITEM_NAME like :1)

手工执行了一下这个sql语句，果然报错。
SQL> var a varchar2(100)
SQL> exec :a:='%sony%'

PL/SQL procedure successfully completed.

SQL> select count(distinct product0_.ITEM_ID) as col_0_0_
2 from ITEM product0_
3 inner join ITEM_TAG itemtags1_ on product0_.ITEM_ID = itemtags1_.ITEM_ID
4 where product0_.ITEM_TYPE = 'p'
5 and (itemtags1_.TAG_ID in (23475))
6 and (product0_.ITEM_NAME like :a);
select count(distinct product0_.ITEM_ID) as col_0_0_
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
根据查看对应的trace文件，报错和邮件报错一样。

查看其执行计划：
SQL> set autotrace trace explain
SQL> var a varchar2(100)
SQL> exec :a:='%sony%'

PL/SQL procedure successfully completed.

SQL> select count(distinct product0_.ITEM_ID) as col_0_0_
2 from ITEM product0_
inner join ITEM_TAG itemtags1_ on product0_.ITEM_ID = itemtags1_.ITEM_ID
where product0_.ITEM_TYPE = 'p'
3 4 5 and (itemtags1_.TAG_ID in (23475))
6 and (product0_.ITEM_NAME like :a);

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3815 Card=1 Bytes=40
)

1 0 SORT (GROUP BY)
2 1 HASH JOIN (Cost=3815 Card=1 Bytes=40)
3 2 INDEX (RANGE SCAN) OF 'UN_ITEM_TAG_TAGID_ITEMID' (UNIQ
UE) (Cost=34 Card=7599 Bytes=68391)

4 2 VIEW OF 'index$_join$_001' (Cost=3778 Card=14375 Bytes
=445625)

5 4 HASH JOIN (Cost=3815 Card=1 Bytes=40)
6 5 HASH JOIN (Cost=3815 Card=1 Bytes=40)
7 6 INDEX (RANGE SCAN) OF 'INDEX_ITEM_NAME' (NON-UNI
QUE) (Cost=194 Card=14375 Bytes=445625)

8 6 INDEX (FAST FULL SCAN) OF 'IDX_ITEM_UPDATE_DATE'
(NON-UNIQUE) (Cost=194 Card=14375 Bytes=445625)

9 5 INDEX (FAST FULL SCAN) OF 'PK_ITEM' (UNIQUE) (Cost
=194 Card=14375 Bytes=445625)

把这个sql拿到另一个数据库执行，可以正常返回结果。查看其执行计划：
SQL> set autotrace trace explain
SQL> var a varchar2(100)
SQL> exec :a:='%sony%'

PL/SQL procedure successfully completed.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8157 Card=1 Bytes=43
)

1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'ITEM' (Cost=2 Card=1 B
ytes=32)

3 2 NESTED LOOPS (Cost=8157 Card=1 Bytes=43)
4 3 INDEX (RANGE SCAN) OF 'UN_ITEM_TAG_TAGID_ITEMID' (UN
IQUE) (Cost=47 Card=8086 Bytes=88946)

5 3 INDEX (RANGE SCAN) OF 'PK_ITEM' (UNIQUE) (Cost=1 Car
d=1)

上google查询，发现有这么一个信息：
This is the case with Oracle 9.2.0.3 also on windows platform.
_INDEX_JOIN_ENABLED Use this to disable use of index joins. An execution plan with a bitmap access on the top of a full b*tree index scan can produce a wrong result, if all the columns of the b*tree index are nullable. This was considered a bug and the patches were released with patchsets 8.1.7.4, 9.0.1.4 and 9.2.0.1.

This parameter is set to TRUE by default. This is session modifiable. Hence, as a work around one may set it to false for that session.

alter session set "_INDEX_JOIN_ENABLED"=FALSE;

Some have experienced instance crashes with the following error:

ORA-07445: exception encountered: core dump[qervwRowProcedure()+123] [SIGSEGV] [Address not mapped to object] [0xC] [] []

ORA-7445 [qervwRestoreViewBufPtrs] they are possible from queries against clustered tables if an index join access path is used.

.........

对比两个执行计划，发现在出错的执行计划有index join，看来是这个原因了。这显然是一个bug，下面就是如何解决问题了。
接着修改隐含参数_INDEX_JOIN_ENABLED，然后在原来出问题的数据库执行相同的查询：
SQL> alter session set "_INDEX_JOIN_ENABLED"=FALSE;

Session altered.

SQL> var a varchar2(100)
SQL> exec :a:='%sony%'

PL/SQL procedure successfully completed.

SQL> select count(distinct product0_.ITEM_ID) as col_0_0_
2 from ITEM product0_
3 inner join ITEM_TAG itemtags1_ on product0_.ITEM_ID = itemtags1_.ITEM_ID
where product0_.ITEM_TYPE = 'p'
4 5 and (itemtags1_.TAG_ID in (23475))
6 and (product0_.ITEM_NAME like :a);

COL_0_0_
----------
0
查看目前的执行计划：
SQL> set autotrace trace explain
SQL> select count(distinct product0_.ITEM_ID) as col_0_0_
2 from ITEM product0_
inner join ITEM_TAG itemtags1_ on product0_.ITEM_ID = itemtags1_.ITEM_ID
where product0_.ITEM_TYPE = 'p'
and (itemtags1_.TAG_ID in (23475))
and (product0_.ITEM_NAME like :a); 3 4 5 6

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6922 Card=1 Bytes=40
)

1 0 SORT (GROUP BY)
2 1 HASH JOIN (Cost=6922 Card=1 Bytes=40)
3 2 INDEX (RANGE SCAN) OF 'UN_ITEM_TAG_TAGID_ITEMID' (UNIQ
UE) (Cost=34 Card=7599 Bytes=68391)

4 2 TABLE ACCESS (BY INDEX ROWID) OF 'ITEM' (Cost=6885 Car
d=14375 Bytes=445625)

5 4 INDEX (RANGE SCAN) OF 'INDEX_ITEM_NAME' (NON-UNIQUE)
(Cost=60 Card=1)

index join已经消失。系统也正常了。

后来了解到出问题的数据库最近增加了大量的数据，而分析没有跟上。
解决方法有两种：
1、分析表
这种方法治标不治本，对当前的系统能够避免index join，但不担保以后不会出现
2、修改隐含参数
_INDEX_JOIN_ENABLED是一个只能在session一级修改的隐含参数，因此，需要建立一个登录触发器来动态修改每一个会话的参数值：
CREATE OR REPLACE TRIGGER LOGON_TEST
AFTER LOGON ON DATABASE WHEN (USER='TEST_TAG_GROUP')
BEGIN
execute immediate 'alter session set "_INDEX_JOIN_ENABLED"=FALSE';
END;

但是，这样做也有隐患。因为禁用了index join，会使得某些走index join更快的sql不得不改变执行计划，从而降低了性能。

在本例中，由于引问题的sql执行很频繁。
这个bug会导致每一个执行该sql的会话与数据库断开连接，并每次产生85m的trace文件，并且在执行sql期间数据库非常慢，所以还是值得修改的。

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

转载于:http://blog.itpub.net/231499/viewspace-63743/