ORA-7445(qerhjWalkHashBucket)（二）

今天又碰到了这个错误，仔细研究了一下，发现错误信息和Oracle的Metalink上面的描述还是有一些出入的。

ORA-7445(qerhjWalkHashBucket)：http://yangtingkun.itpub.net/post/468/247736

首先看一下出错的SQL语句和执行计划：

SQL> explain plan for
2 select count(*)
3 from
4 (
5 select nvl(deal.temp_flag,0) as temp_flag, b.url,c.member_flag,b.address, b.plat_id, b.used_name, c.code ,
6 c.name ,b.check_flag, b.factory_flag, b.saler_flag, b.send_flag, c.abbr ,c.id, d.plat_name,d.plat_father
7 from cat_enterprise b,cat_org c, plt_plat d ,CAT_DEALER deal
8 where 1=1
9 and b.id = c.id
10 and b.plat_id = d.id
11 and (c.name like '%惠氏%'
12 or c.abbr like '%惠氏%'
13 or b.used_name like '%惠氏%'
14 or
15 (exists
16 (
17 select 1 from cat_manufacture cm, cat_org c
18 where cm.molecule_orgid = c.id
19 and cm.id=b.id
20 and c.abbr || c.code|| c.name_wb|| c.spell_abbr || c.name like :1 )
21 )
22 )
23 and c.ENABLE_FLAG = :2
24 and deal.id(+)=b.id
25 )
26 ;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 169 | 334 |
| 1 | SORT AGGREGATE | | 1 | 169 | |
|* 2 | FILTER | | | | |
| 3 | NESTED LOOPS OUTER | | 3343 | 551K| 334 |
| 4 | NESTED LOOPS | | 3343 | 470K| 334 |
|* 5 | HASH JOIN | | 3343 | 388K| 334 |
|* 6 | TABLE ACCESS FULL | CAT_ORG | 18020 | 1126K| 123 |
| 7 | TABLE ACCESS FULL | CAT_ENTERPRISE | 25055 | 1345K| 183 |
|* 8 | INDEX UNIQUE SCAN | PK_PLT_PLAT | 1 | 25 | |
|* 9 | INDEX UNIQUE SCAN | PK_CAT_DEALER | 1 | 25 | |
| 10 | NESTED LOOPS | | 1 | 121 | 3 |
|* 11 | TABLE ACCESS BY INDEX ROWID| CAT_MANUFACTURE | 1 | 27 | 2 |
|* 12 | INDEX UNIQUE SCAN | PK_CAT_MANUFACTURE | 7849 | | 1 |
|* 13 | TABLE ACCESS BY INDEX ROWID| CAT_ORG | 1 | 94 | 1 |
|* 14 | INDEX UNIQUE SCAN | PK_CAT_ORG | 20 | | |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("C"."NAME" LIKE '%惠氏%' OR "C"."ABBR" LIKE '%惠氏%' OR
"SYS_ALIAS_1"."USED_NAME" LIKE '%惠氏%' OR EXISTS (SELECT /*+ */ 0 FROM "CAT_ORG"
"C","CAT_MANUFACTURE" "CM" WHERE "CM"."ID"=:B1 AND "CM"."MOLECULE_ORGID" IS NOT NULL
AND "CM"."MOLECULE_ORGID"="C"."ID" AND "C"."ABBR"||"C"."CODE"||"C"."NAME_WB"||"C"."SPE
LL_ABBR"||"C"."NAME" LIKE :Z))
5 - access("SYS_ALIAS_1"."ID"="C"."ID")
6 - filter("C"."ENABLE_FLAG"=:Z)
8 - access("SYS_ALIAS_1"."PLAT_ID"="D"."ID")
9 - access("DEAL"."ID"(+)="SYS_ALIAS_1"."ID")
11 - filter("CM"."MOLECULE_ORGID" IS NOT NULL)
12 - access("CM"."ID"=:B1)
13 - filter("C"."ABBR"||"C"."CODE"||"C"."NAME_WB"||"C"."SPELL_ABBR"||"C"."NAME"
LIKE :Z)
14 - access("CM"."MOLECULE_ORGID"="C"."ID")

Note: cpu costing is off

40 rows selected.

Oracle的bug 2514869关于错误的描述为：

Bug 2514869 Dump (qerhjWalkHashBucket->kpofcr) from HASH JOIN OUTER with BINDs

This note gives a brief overview of bug 2514869.

Affects:

Product (Component)	Oracle Server (RDBMS)
Range of versions believed to be affected	Versions >= 8.1 but < 10G
Versions confirmed as being affected	8.1.7.4 9.2.0.2
Platforms affected	Generic (all / most platforms affected)

Fixed:

This issue is fixed in

9.2.0.3 (Server Patch Set) 10G Production Base Release

Symptoms:

• Process may Dump / Abend / Abort
• Dump in or under qerhjWalkHashBucket

Related To:

• Hash Join

Description

Dump (in qerhjWalkHashBucket -> kpofcr -> memcpy) possible

while doing hash join outer if one of the join inputs is a

view and the view includes a bind variable.

从SQL语句中看到了绑定变量，而且绑定变量处于exists语句中，对于整体SQL来说应该属于内部视图，这一点和描述比较类似，不过从执行计划中虽然可以看到HASH JOIN，从错误发生时的错误函数名称也是和HASH JOIN有关，但是外连接发生在NESTED LOOP连接中，没有发生在HASH JOIN处。而且错误日志中并没有包括kpofcr和memcpy函数。

从这一点上看，碰到的问题和bug 2514869的描述只是类似而已。

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

转载于:http://blog.itpub.net/4227/viewspace-69361/