ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORA-600(qkacon:FJswrwo)错误

ORA-600(qkacon:FJswrwo)错误

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

刚说完BUG扎堆,今天就又碰到一个,还是10.2.0.3上的bug


检查alter文件发现ORA-600错误,错误信息为:

ORA-00600: 内部错误代码, 参数: [qkacon:FJswrwo], [3], [], [], [], [], [], []
Current SQL statement for this session:
SELECT SYS_CONNECT_BY_PATH(USR_MENU.NAME, '->')
菜单
, r.role_name
FROM USR_MENU , USR_MENU T1, USR_FUNCTION_LIST, USR_ROLE_FUNCTION, usr_role r
WHERE USR_ROLE_FUNCTION.PARENT_ID = USR_FUNCTION_LIST.ID
AND USR_FUNCTION_LIST.MODULE_ID = T1.ID
AND T1.FATHER_MENUID = USR_MENU.ID
and USR_ROLE_FUNCTION.Role_Id = r.id
START WITH USR_MENU.GRADE = '1'
CONNECT BY PRIOR USR_MENU.ID = USR_MENU.FATHER_MENUID

METALINK上查询发现是Oracle 10.2.0.3bug,是由于树状查询造成的,Oracle给出的解决方法为修改隐含参数"_optimizer_connect_by_cost_based"FALSE

SQL> SET AUTOT TRACE STAT
SQL> SELECT SYS_CONNECT_BY_PATH(USR_MENU.NAME, '->')
菜单, r.role_name
2 FROM USR_MENU , USR_MENU T1, USR_FUNCTION_LIST, USR_ROLE_FUNCTION, usr_role r
3 WHERE USR_ROLE_FUNCTION.PARENT_ID = USR_FUNCTION_LIST.ID
4 AND USR_FUNCTION_LIST.MODULE_ID = T1.ID
5 AND T1.FATHER_MENUID = USR_MENU.ID
6 and USR_ROLE_FUNCTION.Role_Id = r.id
7 START WITH USR_MENU.GRADE = '1'
8 CONNECT BY PRIOR USR_MENU.ID = USR_MENU.FATHER_MENUID
9 ;
WHERE USR_ROLE_FUNCTION.PARENT_ID = USR_FUNCTION_LIST.ID
*
3 行出现错误
:
ORA-00600:
内部错误代码, 参数: [qkacon:FJswrwo], [3], [], [], [], [], [], []


SQL> ALTER SESSION SET "_optimizer_connect_by_cost_based" = FALSE;

会话已更改。

SQL> SELECT SYS_CONNECT_BY_PATH(USR_MENU.NAME, '->') 菜单, r.role_name
2 FROM USR_MENU , USR_MENU T1, USR_FUNCTION_LIST, USR_ROLE_FUNCTION, usr_role r
3 WHERE USR_ROLE_FUNCTION.PARENT_ID = USR_FUNCTION_LIST.ID
4 AND USR_FUNCTION_LIST.MODULE_ID = T1.ID
5 AND T1.FATHER_MENUID = USR_MENU.ID
6 and USR_ROLE_FUNCTION.Role_Id = r.id
7 START WITH USR_MENU.GRADE = '1'
8 CONNECT BY PRIOR USR_MENU.ID = USR_MENU.FATHER_MENUID
9 ;

已选择7091行。

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
498 consistent gets
0 physical reads
0 redo size
156200 bytes sent via SQL*Net to client
5684 bytes received via SQL*Net from client
474 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
7091 rows processed

Oracle计划在1020411.1.0.6中解决这个问题。

更多的详细信息可以参考metalink的文档Doc ID: Note:5119354.8

测试发现11g确实已经解决了这个bug

$ sqlplus test/test@ora11g

SQL*Plus: Release 10.2.0.3.0 - Production on 星期日 8 19 18:43:38 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SET AUTOT TRACE STAT
SQL> SELECT SYS_CONNECT_BY_PATH(USR_MENU.NAME, '->')
菜单, r.role_name
2 FROM USR_MENU , USR_MENU T1, USR_FUNCTION_LIST, USR_ROLE_FUNCTION, usr_role r
3 WHERE USR_ROLE_FUNCTION.PARENT_ID = USR_FUNCTION_LIST.ID
4 AND USR_FUNCTION_LIST.MODULE_ID = T1.ID
5 AND T1.FATHER_MENUID = USR_MENU.ID
6 and USR_ROLE_FUNCTION.Role_Id = r.id
7 START WITH USR_MENU.GRADE = '1'
8 CONNECT BY PRIOR USR_MENU.ID = USR_MENU.FATHER_MENUID
9 ;

已选择7091行。

统计信息
----------------------------------------------------------
344 recursive calls
0 db block gets
742 consistent gets
33 physical reads
0 redo size
88119 bytes sent via SQL*Net to client
3550 bytes received via SQL*Net from client
474 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
7091 rows processed

SQL> SET AUTOT OFF
SQL> SELECT * FROM V$VERSION;

BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 -
Production PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

不过升级到11g显然不是解决问题的方法,添加隐含参数确实可以解决问题,但是使用隐含参数很可能会带来其他的问题。

其实解决这个错误最好的方法是改写SQL语句,以上面的这个SQL为例,将SQL改写一下,将树型查询和连接查询分隔开来:

SQL> ALTER SESSION SET "_optimizer_connect_by_cost_based" = TRUE;

会话已更改。

SQL> SELECT SYS_CONNECT_BY_PATH(A.NAME, '->') 菜单, B.ROLE_NAME
2 FROM USR_MENU A,
3 (
4 SELECT A.FATHER_MENUID, D.ROLE_NAME
5 FROM USR_MENU A, USR_FUNCTION_LIST B, USR_ROLE_FUNCTION C, USR_ROLE D
6 WHERE C.PARENT_ID = B.ID
7 AND B.MODULE_ID = A.ID
8 AND C.ROLE_ID = D.ID
9 ) B
10 WHERE B.FATHER_MENUID = A.ID
11 START WITH A.GRADE = '1'
12 CONNECT BY PRIOR A.ID = A.FATHER_MENUID;
WHERE C.PARENT_ID = B.ID
*
6 行出现错误
:
ORA-00600:
内部错误代码, 参数: [qkacon:FJswrwo], [3], [], [], [], [], [], []

改写SQL后,仍然报错这说明虽然SQL格式变了,但是执行计划并未改变,要彻底断开CONNECT BY和连接语句的关系,还需要进一步改写SQL

SQL> SELECT SYS_CONNECT_BY_PATH(A.NAME, '->') 菜单, B.ROLE_NAME
2 FROM USR_MENU A,
3 (
4 SELECT A.FATHER_MENUID, D.ROLE_NAME, ROWNUM
5 FROM USR_MENU A, USR_FUNCTION_LIST B, USR_ROLE_FUNCTION C, USR_ROLE D
6 WHERE C.PARENT_ID = B.ID
7 AND B.MODULE_ID = A.ID
8 AND C.ROLE_ID = D.ID
9 ) B
10 WHERE B.FATHER_MENUID = A.ID
11 START WITH A.GRADE = '1'
12 CONNECT BY PRIOR A.ID = A.FATHER_MENUID;

已选择7091行。

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
635 consistent gets
0 physical reads
0 redo size
159093 bytes sent via SQL*Net to client
5684 bytes received via SQL*Net from client
474 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
7091 rows processed

在内层查询中添加一个ROWNUM,确保这个查询不被优化器拆开,这样避免了错误的产生,或者将树状查询单独写到一个子查询中,也可以避免这个错误:

SQL> SELECT A.菜单, B.ROLE_NAME
2 FROM
3 (
4 SELECT ID, SYS_CONNECT_BY_PATH(NAME, '->')
菜单

5 FROM USR_MENU
6 START WITH GRADE = '1'
7 CONNECT BY PRIOR ID = FATHER_MENUID
8 ) A,
9 (
10 SELECT A.FATHER_MENUID, D.ROLE_NAME
11 FROM USR_MENU A, USR_FUNCTION_LIST B, USR_ROLE_FUNCTION C, USR_ROLE D
12 WHERE C.PARENT_ID = B.ID
13 AND B.MODULE_ID = A.ID
14 AND C.ROLE_ID = D.ID
15 ) B
16 WHERE B.FATHER_MENUID = A.ID
17 ;

已选择7091行。

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1507 consistent gets
0 physical reads
0 redo size
156200 bytes sent via SQL*Net to client
5684 bytes received via SQL*Net from client
474 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
7091 rows processed

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

下一篇: ORA-7445(opidsa)错误
请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10352740