在之前的文章中提到过一个有趣的绘制五角星的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;
Leave a Reply