ORA-600錯誤[kkoipt:invalid join method]

ORA-600錯誤[kkoipt:invalid join method]

今天客戶的11.2.0.2資料庫碰到了ORA-600這個錯誤。

詳細錯誤資訊如下：

Fri Sep 16 15:23:52 2011

Errors in file /u01/diag/rdbms/ora1/ora1/trace/ora1_ora_20382140.trc (incident=169704):

ORA-00600: 内部錯誤代碼,

參數: [kkoipt:invalid join method], [1], [0], [], [], [], [], [], [], [], [], []

Incident details in: /u01/diag/rdbms/ora1/ora1/incident/incdir_169704/ora1_ora_20382140_i169704.trc

Fri Sep 16 15:24:00 2011

Dumping diagnostic data in directory=[cdmp_20110916152400], requested by (instance=1, osid=20382140), summary=[incident=169704].

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

而對應的詳細TRACE如下：

bash-3.2$ more /u01/diag/rdbms/ora1/ora1/incident/incdir_169704/ora1_ora_20382140_i169704.trc

Dump file /u01/diag/rdbms/ora1/ora11/incident/incdir_169704/ora1_ora_20382140_i169704.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

ORACLE_HOME = /u01/product/11.2.0/dbhome_1

System name: AIX

Node name: node1

Release: 1

Version: 6

Machine: 00F6CD264C00

Instance name: ora1

Redo thread mounted by this instance: 1

Oracle process number: 193

Unix process pid: 20382140, image: oracle@s180

*** 2011-09-16 15:23:52.275

*** SESSION ID:(29.7169) 2011-09-16 15:23:52.275

*** CLIENT ID:() 2011-09-16 15:23:52.275

*** SERVICE NAME:(ora1) 2011-09-16 15:23:52.275

*** MODULE NAME:(TOAD 10.5.0.41) 2011-09-16 15:23:52.275

*** ACTION NAME:() 2011-09-16 15:23:52.275

Dump continued from file: /u01/diag/rdbms/ora1/ora1/trace/ora1_ora_20382140.trc

========= Dump for incident 169704 (ORA 600 [kkoipt:invalid join method]) ========

*** 2011-09-16 15:23:52.336

dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)

----- Current SQL Statement for this session (sql_id=7ukzmn3p6zby6) -----

SELECT *

FROM (

SELECT a.childpolicyno, a.policyno, e.exportno corpno, a.effectdate,

TO_CHAR (a.lapsedate, 'yyyy-mm-dd') lapsedate, e.chnname,

e.address, e.keyflag, e.specialflag, g.empname,

g.secdeptname, f.TYPE, ROWNUM AS ID

FROM t_schildpolicy a,

t_spolicy f,

t_exportcorp e,

t_nodeinfo d,

v_employeecustomer g

WHERE f.policyno = a.policyno

AND f.insurantno = e.exportno

AND f.policyno = g.productid(+)

AND d.nodeid = f.nodeid

AND e.chnname LIKE '%%'

AND ((d.corpid = '3502'))

AND ROWNUM <= 10

ORDER BY a.policyno)

WHERE ID BETWEEN 1 AND 10

----- Call Stack Trace -----

calling call entry argument values in hex

location type point (? means dubious value)

-------------------- -------- -------------------- ----------------------------

skdstdst()+40 bl 107b6e01c FFFFFFFFFFECCA8 ? 000002004 ?

000000001 ? 000000003 ?

000000000 ? 000000002 ?

000000001 ? 000000000 ?

ksedst1()+104 call skdstdst() FFFFFFFFFFEBCB0 ? 000002004 ?

110A597A0 ? 10A027B2C ?

110A597A0 ? 000000000 ?

FFFFFFFFFFEBDE0 ? 700000007 ?

ksedst()+40 call ksedst1() 3030000000000 ? 002050033 ?

10A027B20 ? 700000000025C ?

000000000 ? 000000000 ?

10A027180 ? 000000000 ?

dbkedDefDump()+2828 call ksedst() FFFFFFFFFFEBE90 ? 000000000 ?

000000000 ? 300000003 ?

ksedmp()+76 call dbkedDefDump() 310A597A0 ? 1100010C8 ?

FFFFFFFFFFEC490 ?

28444040FFFEC66C ?

100148568 ? 1096635A8 ?

FFFFFFFFFFEC4E0 ? 11064B598 ?

ksfdmp()+88 call ksedmp() 000000000 ? 000000000 ?

0096635C3 ? 109CB2C50 ?

200000000000000 ? 000000000 ?

110C221E8 ? 110A597A0 ?

dbgexPhaseII()+1212 call ksfdmp() 000002004 ? 110A597A0 ?

000000000 ? FFFFFFFFFFEC658 ?

FFFFFFFFFFEC580 ?

FFFFFFFFFFECCA8 ? 1001D04B8 ?

110C221E8 ?

dbgexProcessError() call dbgexPhaseII() 110A597A0 ? 110C203F8 ?

[phone]E8 ? 200000000 ?

FFFFFFFFFFED258 ? 00000006A ?

dbgeExecuteForError call dbgexProcessError()

110A597A0 ? 110C221E8 ?

()[phone] ? 000000000 ?

110D01C88 ? 000000000 ?

110CADC78 ? 110C23F30 ?

dbgePostErrorKGE()+ call dbgeExecuteForError

FFFFFFFFFFF0830 ?

1152 () B7417335409B9B1B ?

FFFFFFFFFFF06F0 ? 0409B9800 ?

10524EA10 ?

2147AE154168E65F ?

10524EA10 ? 000000000 ?

dbkePostKGE_kgsf()+ call dbgePostErrorKGE()

002050000 ? 001160000 ?

64 25810001330 ? 000000000 ?

110001330 ? FFFFFFFFFFF1510 ?

109613CD0 ? 110CB3F18 ?

這個SQL錯誤的奇特之處在于，如果将ORDER BY語句中的a.policyno變成其他列，則不會導緻錯誤，甚至是換成與之相等關聯的f.policyno，同樣不會報錯。

這顯然是一個bug，查詢metalink發現，居然目前唯一确認影響的版本就是11.2.0.2，這個Bug的描述為Bug

12591252 Query with ORDER BY fails with ORA-600 [kkoipt:invalid join method]。而Oracle的解釋是，優化器試圖通過索引來避免排序操作時，可能引發這個bug。這正好說明了為什麼SQL中換成其他的列就不會導緻錯誤。

目前這個bug在Windows平台的11.2.0.2的patch 10中被fixed，其他平台隻能通過單獨的12591252更新檔來解決這個問題。當然如果能通過改寫SQL來實作這個目的，無疑代價是最小的。

 Oracle 專家QQ群：60632593、60618621