ora-600[qesmmCValStat4]一例

在之前的文章中提到过一个有趣的绘制五角星的SQL,具体SQL语句如下： with a as (select distinct round(sum(x) over(order by n)) x, round(sum(y) over(order by n)) y from (select n, cos(trunc(n / 20) * (1 - 1 / 5) * 3.1415926) * 2 x, sin(trunc(n / 20) * (1 - 1 / 5) * 3.1415926) y from (select rownum - 1 n from all_objects where rownum <= 20 * 5))) select replace (sys_connect_by_path(point, '/'), '/', null) star from (select b.y, b.x, decode(a.x, null, ' ', '*') point from a, (select * from (select rownum - 1 + (select min(x) from a) x from all_objects where rownum <= (select max(x) - min(x) + 1 from a)), (select rownum - 1 + (select min(y) from a) y from all_objects where rownum <= (select max(y) - min(y) + 1 from a))) b where a.x(+) = b.x and a.y(+) = b.y) where x = (select max(x) from a) start with x = (select min(x) from a) connect by y = prior y and x = prior x + 1; 在10.2.0.1版本输入以上SQL可能会出现ORA-00600: internal error code, arguments: [qesmmCValStat4], [3], [1], [], [],[], [], []错误,g10r21_ora_15473.trc。 其调用栈为:qercoFetch->qerhjFetch->qerhjInitializeManagementComponents-> qesmmCStartWorkArea->qesmmCValidateStatus->kgeasnmierr(报错) metalink文档ID 360811.1对该 Bug 4926357进行了描述，该bug可能在9.2.07~10.2.0.1版本中出现，一般由带start with ... ... connect by prior ..子句的查询语句引起；典型的调用栈为：qesmmCValidateStatus<- qesmmCStartWorkArea <-qerhjInitializeManagementComponents <-qerhjFetch ... 未公布的Bug 4401437是Bug 4926357的一个复制品avatar，该Bug已在10.1.0.5, 10.2.0.2, 11.1等版本中修复了。 Oracle support建议的四种解决方式： 1. 打上该Bug的one-off补丁; 2.升级到该Bug已修复的大版本中，例如从10.2.0.1升级到10.2.0.2; 3.设置参数hash_join_enabled(9i中),_hash_join_enabled(10g中),实例级别的或者会话级别的均可，如：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> alter session set "_hash_join_enabled"=false;

Session altered.

SQL> set pagesize 1400;
SQL> with a as
2   (select distinct round(sum(x) over(order by n)) x,
3                    round(sum(y) over(order by n)) y
4      from (select n,
5                   cos(trunc(n / 20) * (1 - 1 / 5) * 3.1415926) * 2 x,
6                   sin(trunc(n / 20) * (1 - 1 / 5) * 3.1415926) y
7              from (select rownum - 1 n from all_objects where rownum <= 20 * 5)))
8  select replace (sys_connect_by_path(point, '/'), '/', null) star
9    from (select b.y, b.x, decode(a.x, null, ' ', '*') point
10            from a,
11                 (select *
12                    from (select rownum - 1 + (select min(x) from a) x
13                            from all_objects
14                           where rownum <= (select max(x) - min(x) + 1 from a)),
15                         (select rownum - 1 + (select min(y) from a) y
16                            from all_objects
17                           where rownum <= (select max(y) - min(y) + 1 from a))) b
18           where a.x(+) = b.x
19             and a.y(+) = b.y)
20   where x = (select max(x) from a)
21   start with x = (select min(x) from a)
22  connect by y = prior y and x = prior x + 1;

STAR
--------------------------------------------------------------------------------
.................
20 rows selected.

4.设置'no_filtering' 提示，如: with a as (select distinct round(sum(x) over(order by n)) x, round(sum(y) over(order by n)) y from (select n, cos(trunc(n / 20) * (1 - 1 / 5) * 3.1415926) * 2 x, sin(trunc(n / 20) * (1 - 1 / 5) * 3.1415926) y from (select rownum - 1 n from all_objects where rownum <= 20 * 5))) select  /*+ no_filtering */ replace (sys_connect_by_path(point, '/'), '/', null) star from (select b.y, b.x, decode(a.x, null, ' ', '*') point from a, (select * from (select rownum - 1 + (select min(x) from a) x from all_objects where rownum <= (select max(x) - min(x) + 1 from a)), (select rownum - 1 + (select min(y) from a) y from all_objects where rownum <= (select max(y) - min(y) + 1 from a))) b where a.x(+) = b.x and a.y(+) = b.y) where x = (select max(x) from a) start with x = (select min(x) from a) connect by y = prior y and x = prior x + 1;

但是该hint在9.2.0.7版本中可能因为Bug 4752555 'Wrong results from CONNECT BY query'而导致返回错误行数；并且这是一个undocumented hint，所以强烈不推荐使用！