ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORA-07445: exception encountered: core dump [qervwRowProcedure()+133]

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

原创 Linux操作系统 作者:space6212 时间:2019-04-06 12:12:06 0 删除 编辑
今天打开邮箱,发现很多数据库报错信息:

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.

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=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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2005-01-25

  • 博文量
    245
  • 访问量
    168782