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

今天開啟郵箱，發現很多資料庫報錯資訊：

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期間資料庫非常慢，所以還是值得修改的。