ORA-600(qkacon:FJswrwo)錯誤

剛說完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.3的bug，是由於樹狀查詢造成的，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計劃在10204和11.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