ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORA-7445(qerhjWalkHashBucket)(二)

ORA-7445(qerhjWalkHashBucket)(二)

原创 Linux操作系统 作者:yangtingkun 时间:2007-07-25 00:00:00 0 删除 编辑

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

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.

Oraclebug 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:

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处。而且错误日志中并没有包括kpofcrmemcpy函数。

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

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

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10456569