客户的11.2.0.2数据库碰到了这个错误。
详细错误信息如下：
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 ORA-00600: 内部错误代码, 参数: [kkoipt:invalid JOIN method], [1], [0], [], [], [], [], [], [], [], [], [] ========= 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 ? 000000000 ? 000000000 ? 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 ? +3604 0000296E8 ? 200000000 ? FFFFFFFFFFED258 ? 00000006A ? 000000000 ? 000000000 ? dbgeExecuteForError CALL dbgexProcessError() 110A597A0 ? 110C221E8 ? ()+72 100000000 ? 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来实现这个目的，无疑代价是最小的。